Jak działa async/await w EF?

Nie ma pojęcia o tym, jak działają asynchroniczne zapytania w EF.

1. Powiedzmy, że mam zapytanie wyszukujące użytkowników spełniających dane kryteria. Wydaje mi się, że aby to zapytanie zostało wykonane, jakiś wątek musi być przez jakiś czas zajęty sprawdzaniem kolejnych rekordów z bazy danych. Co mi da to, że wątek wykonujący żądanie powróci do puli wątków odpowiadających za wykonywanie żądań, skoro inny wątek (spoza puli?) będzie zajęty?

2. Ile zapytań można wykonywać jednocześnie na bazie danych? Ta ilość musi być jakoś ograniczona, co z kolei powinno blokować skalowanie aplikacji uzyskiwane poprzez stosowanie async/await Czytałem, że z Microsoft Azure SQL Database ten problem nie występuje. Dlaczego?

3. Kiedy stosować async/await? Wtedy, gdy ilość wykonywanych jednocześnie żądań przekracza ilość procesorów logicznych?

1. to nie aplikacja filtruje rekordy tylko baza danych (a przynajmniej powinna bo nic nie stoi na przeszkodzie, żeby zaciągnąć do aplikacji wszystkie rekordy i je przefiltrować po stronie programu)
2. dużo, https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option?view=sql-server-2017 i mocno zależy od sprzętu. O ile nie piszesz jakiegoś specyficznego oprogramowania, które uruchamia dużo długich (w sensie długo się wykonujących) zapytań to nie powinieneś się tym martwić. Zauważ, że czym innym jest maksymalna ilość połączeń po stronie bazy danych a czym innym maksymalna ilość połączeń po stronie aplikacji (choćby wspomniane connection pool size dla EF, które domyślnie wynosi 25)
3. async/await nie ma nic wspólnego z ilością zapytań. async/await służy do "prostego" programowania asynchronicznego (bez blokowania głównego wątku). A kiedy go używać - generalnie wtedy jak coś trwa długo a nie chcesz blokować UI. Ma to też swoje wady i trzeba wiedzieć co się robi.

Tu masz opisy i przykłądy https://docs.microsoft.com/en-us/ef/ef6/fundamentals/async

async/await nie ma nic wspólnego z ilością zapytań. async/await służy do "prostego" programowania asynchronicznego (bez blokowania głównego wątku).

Tak się składa, że te dwie rzeczy się łączą. Zarówno w zapytaniach do bazy jak i do serwera HTTP stosuje się kolejkowanie zapytań. Gdy jest ich sporo to pojawia się zwyczajnie Denial of Service - dana pula wątków nie wyrabia z jednoczesnym przetwarzaniem zapytań. I w tym momencie do akcji wkracza programowanie asynchroniczne. Asynchroniczne serwery mogą obsłużyć kolejne zapytania bez blokowania wątku i czekania aż kolejka się zmniejszy operując w dodatku na lżejszych konstrukcjach niż wątki takich jak coroutines czy fibers. Czasem stosuje się też takie abstrakcje (wzorce) jak actor-model czy model zdarzeniowy. Z tego względu powszechnie się uważa, że są wydajniejsze. Z resztą potwierdzają to także benchmarki. Większość, a może i wszystkie czołowe frameworki (górne 10-15) z benchmarku Techempower to frameworki asynchroniczne. https://www.techempower.com/benchmarks/#section=data-r16&hw=ph&test=json

Czyli w przypadku aplikacji o małym ruchu (kilka osób jednocześnie) asynchroniczne zapytania jedynie spowalniają?

Nie, skąd ten pomysł?
Asynchroniczne zapytania sprawiają, że wątek zamiast czekać na odpowiedź z bazy danych, może zająć się inną pracą.

@somekind: A co jest przyczyną takich wyników? https://stackoverflow.com/questions/40423997/entity-framework-6-performance-of-async-methods-vs-sync-methods

Przyczyną takich wyników jest po pierwsze to, że ich autor nie ma pojęcia o benchmarkowaniu. Jeśli chce się sprawdzić wydajność jakiegoś rozwiązania, to nie odpala się metody raz i nie stwierdza, że wykonała się 0 milisekund.
Po drugie, to to jest sztuczny benchmark testujący jedynie komunikację z bazą. W przypadku rzeczywistej aplikacji, to jest niemiarodajne, bo aplikacja zazwyczaj musi robić więcej - przyjąć request, odpalić walidację, wysłać request do innego serwisu, zapisać/odczytać coś z pliku, itd. Dlatego zysk z uwolnienia wątków przez async zazwyczaj może być większy niż pomijalny narzut na jego używanie.

@somekind: A załóżmy, że w odpowiedzi na żądanie aplikacja (baza danych) musi wykonać zapytanie, które będzie szybkie (np. pobranie jakichś enumów z bazy do utworzenia jakiegoś CreateXXXViewModel). Czy wtedy też async ma sens?

Dla jednego szybkiego zapytania nie.