Elementy wewnętrzne pamięci obszaru roboczego

Na podstawie moich książek na temat SQL Server 2008 Wewnętrzne i rozwiązywanie problemów (pożyczone z lokalnej biblioteki w Illinois) przez Christiana Boltona, Brenta Ozara itp. Staram się uzyskać zrozumienie i potwierdzenie na serwerze SQL i wiele wyszukiwań w sieci Byłbym wdzięczny, gdyby ktoś mogę potwierdzić lub poprawić moje zrozumienie.

Każde zapytanie lub operacja, które wymagają przyznania pamięci zapytania, będą wymagały pamięci miejsca do pracy. W ogólnym zapytaniu za pomocą Sortowania, Łączenia mieszającego, Równoległości (nie jestem tego pewien), Wstawiania zbiorczego (nie jestem pewien), Przebudowy indeksu itp. Będzie potrzebować pamięci obszaru roboczego zapytania.

Pamięć obszaru roboczego jest częścią puli buforów programu SQL Server (jest przydzielana jako część puli buforów), a maksymalna pamięć obszaru roboczego wynosi 75% pamięci przydzielonej do puli buforów. Domyślnie pojedyncze zapytanie nie może uzyskać więcej niż 25% pamięci obszaru roboczego (w SQL 2008 / SQL 2012 - kontrolowane przez domyślną grupę obciążenia Resource Governor po wyjęciu z pudełka).

Szukam potwierdzenia mojego zrozumienia

1) Biorąc pod uwagę system z 48 GB pamięci RAM i maksymalną pamięcią serwera skonfigurowaną na 40 GB, oznacza to, że maksymalna pamięć obszaru roboczego jest ograniczona do 30 GB, a pojedyncze zapytanie nie może uzyskać więcej niż 10 GB pamięci obszaru roboczego (pamięci zapytań). Więc jeśli masz złe zapytanie z miliardem wierszy, które wykonuje ogromne sprzężenie mieszające i potrzebujesz więcej niż 10 GB pamięci (pamięci obszaru roboczego), czy w ogóle zechce przejść przez tę kolejkę przyznania pamięci lub od razu rozlewać się na dysk?

2) Jeśli zapytanie wykonujące masową operację sortowania zostało przypisane do pamięci obszaru roboczego o wielkości 5 MB i podczas wykonywania zapytania, jeśli optymalizator zapytań zda sobie sprawę, że z powodu złych statystyk lub brakujących indeksów zapytanie to faktycznie potrzebuje 30 MB pamięci obszaru roboczego natychmiast wyleje się do tempdb. Nawet jeśli system ma dużo dostępnej pamięci obszaru roboczego podczas wykonywania, gdy kwerenda przekroczy przyznaną pamięć obszaru roboczego podczas wykonywania, będzie musiała przelać się na dysk. Czy moje rozumowanie jest prawidłowe?

czy w ogóle miałoby ochotę przejść przez tę kolejkę przyznania pamięci lub od razu rozlać się na dysk?

To nie działa w ten sposób. Po wybraniu planu wymagającego przydziału pamięci zapytanie musi uzyskać przydział, aby przejść przez kolejkę. Rozlanie, jeśli występuje, następuje znacznie później w cyklu wykonywania. Kwerenda nie może zdecydować się na kontynuację bez dotacji i zamiast tego „rozlać”. Musi otrzymać dotację i, do diabła, rozpocząć egzekucję. Jeśli dotacja okaże się niewystarczająca (z powodu złej oceny lub z powodu uzyskania znacznie niższej dotacji niż wymagano), zapytanie zostanie zmuszone do rozlania.

jeśli optymalizator zapytań zda sobie sprawę, że z powodu złych statystyk lub brakujących indeksów

Ściśle mówiąc, nie jest optymalizatorem, jest wykonywanie zapytania. Optymalizator zapytań ma tylko wpływ na decyzję w sprawie planu, ale po wybraniu planu i uruchomieniu go optymalizator nie ma obrazu. również „brakujące indeksy” nie odgrywają tutaj żadnej roli. Brakujący indeks może wymusić zły plan, ale nie może wpłynąć na sposób wykonania tego „złego” planu, ponieważ plan ten został zbudowany z uwzględnieniem dokładnie tego, jakie indeksy faktycznie istnieją, więc dokładnie wie, co może, a czego nie może zrobić. Wycieki występują prawie zawsze z powodu złych szacunków, tj. złe statystyki lub złe algorytmy szacowania liczności w optymalizatorze i podczas wykonywania (staje się to bardzo skomplikowane, jeśli zagłębisz się w szczegóły, więc zatrzymam się tutaj).

Nawet jeśli system ma dużo dostępnej pamięci obszaru roboczego podczas wykonywania, gdy kwerenda przekroczy przyznaną pamięć obszaru roboczego podczas wykonywania, będzie musiała przelać się na dysk

Niestety tak. Optymalizator powinien jednak opracować plan wykorzystujący dostępną pamięć RAM.

Polecam lekturę Zrozumienie przyznania pamięci serwera SQL jest najlepszą informacją na ten temat z szerokim marginesem.