W jaki sposób SQL Server obsługuje dane dla zapytania, w którym nie ma wystarczającej ilości miejsca w buforze pamięci podręcznej?

Moje pytanie brzmi: w jaki sposób SQL Server obsługuje zapytanie, które wymaga pobrania większej ilości danych do pamięci podręcznej bufora niż dostępna jest przestrzeń? Ta kwerenda zawierałaby wiele złączeń, więc zestaw wyników nie istnieje w tym formacie już na dysku i trzeba by skompilować wyniki. Ale nawet po kompilacji nadal wymaga więcej miejsca niż jest dostępne w buforze bufora.

Dam przykład. Załóżmy, że masz instancję programu SQL Server, która ma łącznie 6 GB dostępnego miejsca w buforze pamięci podręcznej. Wykonuję zapytanie z wieloma sprzężeniami, które odczytują 7 GB danych, w jaki sposób SQL Server jest w stanie odpowiedzieć na to żądanie? Czy tymczasowo przechowuje dane w tempdb? Czy to się nie udaje? Czy robi coś, co tylko odczytuje dane z dysku i kompiluje segmenty na raz?

Ponadto, co się stanie, jeśli spróbuję zwrócić 7 GB danych ogółem, czy to zmienia sposób, w jaki obsługuje to SQL Server?

Jestem już świadomy kilku sposobów rozwiązania tego problemu, jestem tylko ciekawy, w jaki sposób SQL Server obsługuje to żądanie wewnętrznie, gdy jest uruchamiane zgodnie z opisem.

Ponadto jestem pewien, że ta informacja gdzieś istnieje, ale nie udało mi się jej znaleźć.

Strony są wczytywane do pamięci zgodnie z wymaganiami, jeśli nie ma dostępnej wolnej pamięci, najstarsza niezmodyfikowana strona jest zastępowana stroną przychodzącą.

Oznacza to, że jeśli wykonasz zapytanie wymagające więcej danych, niż może zmieścić się w pamięci, wiele stron będzie żyło bardzo krótko w pamięci, co spowoduje dużą liczbę operacji we / wy.

Możesz zobaczyć ten efekt, patrząc na licznik „Life Life Expectancy” w Monitorze wydajności systemu Windows. Spójrz na https://sqlperformance.com/2014/10/sql-performance/knee-jerk-page-life-expectancy, aby uzyskać wspaniałe szczegóły na temat tego licznika.

W komentarzach zapytano konkretnie, co dzieje się, gdy wyniki zapytania są większe niż dostępne miejsce w buforze. Weźmy najprostszy przykład, select * from some_very_big_table;- załóżmy, że tabela ma 32 GB i max server memory (MB)jest skonfigurowana na 24 GB . Wszystkie 32 GB danych tabeli zostaną wczytane pojedynczo na strony w buforze stron, zatrzaśnięte, sformatowane w pakiety sieciowe i wysłane za pośrednictwem drutu. Dzieje się to strona po stronie; możesz mieć 300 takich zapytań uruchomionych w tym samym czasie, a przy założeniu, że nie nastąpi blokowanie, dane dla każdego zapytania zostaną wczytane do obszaru bufora strony, strony na raz, i przesłane do przewodu tak szybko, jak to możliwe zażądać i zużyć dane. Po przesłaniu wszystkich danych z każdej strony do drutu strona zostaje odblokowana i bardzo szybko zostanie zastąpiona inną stroną z dysku.

W przypadku bardziej złożonego zapytania, powiedzmy na przykład agregującego wyniki z kilku tabel, strony zostaną wciągnięte do pamięci dokładnie tak jak powyżej, ponieważ są one wymagane przez procesor zapytań. Jeśli procesor zapytań potrzebuje tymczasowej przestrzeni roboczej do obliczenia wyników, będzie wiedział o tym z góry, kiedy skompiluje plan zapytania i zażąda przestrzeni roboczej (pamięci) od SQLOS . SQLOS w pewnym momencie (zakładając, że nie przekroczy limitu czasu ), przyzna tę pamięć procesorowi zapytań, w którym to momencie przetwarzanie zapytania zostanie wznowione. Jeśli procesor zapytań popełni błąd w oszacowaniu ilości pamięci wymaganej od SQLOS, może być konieczne wykonanie „przelewu na dysk”operacja polegająca na tymczasowym zapisaniu danych w tempdb w formie pośredniej. Strony, które zostały zapisane w tempdb, zostaną odblokowane, gdy zostaną zapisane w tempdb, aby zrobić miejsce dla innych stron do odczytu w pamięci. Ostatecznie proces zapytania powróci do danych przechowywanych w tempdb, stronicując to przy użyciu zatrzasku, na strony w buforze, które są oznaczone jako wolne.

Niewątpliwie brakuje mi bardzo szczegółowych informacji w powyższym podsumowaniu, ale myślę, że to odzwierciedla istotę tego, w jaki sposób SQL Server może przetwarzać więcej danych niż mieści się w pamięci.

Nie mogę porozmawiać o tym, co dokładnie zrobiłoby zapytanie w tym scenariuszu, ale SQL Server ma kilka opcji w zależności od tego, ile jest potrzebne.

• Dane mogą „rozlać się” do TempDB, byłoby to użycie twojego dysku
• Stare strony można wypchnąć z bufora pamięci podręcznej
• SQL Server może załadować niektóre strony w celu buforowania pamięci podręcznej, użyć ich, a następnie obrócić nowe strony w

Najlepszym sposobem, aby dowiedzieć się, co by się stało, jest stworzenie scenariusza w środowisku programistycznym i sprawdzenie.

Moje pytanie dotyczy tego, w jaki sposób SQL Server obsługuje zapytanie, które wymaga pobrania większej ilości danych do bufora pamięci podręcznej, niż jest dostępne miejsce

Aby odpowiedzieć na tę konkretną część, pozwól mi powiedzieć, w jaki sposób jest to zarządzane. Strony mają rozmiar 8 KB. Po uruchomieniu zapytania wymagającego dużego zestawu danych, które wymaga wprowadzenia wielu stron do pamięci, SQL Server nie przyniesie wszystkich stron za jednym razem. Lokalizuje określone strony i wprowadza do pamięci pojedynczo strony o wielkości 8 KB, odczytuje z nich dane i daje wynik, a to będzie trwać dalej, przypuśćmy, że napotka on sytuację, w której pamięć jest mniejsza, w takim przypadku stare strony zostaną opróżnione do dysk jak wskazał @Max. Jak prawidłowo zgadłeś, ta niska pamięć może spowolnić proces, ponieważ usunięcie starych stron zajmie trochę czasu. To jest punkt kontrolny i Lazywriterpojawia się na zdjęciu. Lazywriter ma za zadanie upewnić się, że zawsze jest dostępna wolna pamięć, aby przenieść nowe strony na dysk. Po napotkaniu niskiego wolnego bufora jest on wyzwalany i tworzy wolne miejsca na nowe strony.

EDYTOWAĆ

Rozumiem, ale część mnie trochę zaskakuje, co się dzieje, jeśli dołączasz do \ filtrowania danych, a wyniki przekraczają rozmiar pamięci podręcznej.

Pamięć o dołączaniu i filtrowaniu jest ustalana jeszcze przed uruchomieniem zapytania i załóżmy, że tak naprawdę występuje awaria pamięci, a pamięć wymagana do uruchomienia operacji jest niedostępna. Procesor SQL Server przyzna „wymaganą pamięć”, która jest

Wymagana pamięć: minimalna pamięć potrzebna do uruchomienia sortowania i łączenia mieszającego. Nazywa się to wymagane, ponieważ zapytanie nie uruchomiłoby się bez dostępnej pamięci. Serwer SQL używa tej pamięci do tworzenia wewnętrznych struktur danych do obsługi sortowania i łączenia mieszającego.

Zatem przynajmniej zapytanie zacznie działać, ale w czasie wykonywania jest prawdopodobne, że wynik pośredni zostanie rozlany do Tempdb, co spowalnia działanie. Zdecydowanie zalecamy zapoznanie się z Grantem pamięci zapytania