Dlaczego zapytania powodują wyciek do tempdb?

tło

Jestem w trakcie migracji bazy danych 160 GB z MSSQL 2008 (standard) na serwerze Win 2008 z 48 GB pamięci RAM na nowy serwer z MSSQL 2012 (64-bitowa wersja internetowa) na Win 2012 z 64 GB pamięci RAM. Stary serwer działa i jest obciążony; nowy serwer nie jest produkowany. Nowy serwer ma 8 plików tempdb (4 GB każdy).

Problem

Podczas testowania na nowym serwerze widzę kroki w wielu zapytaniach, które powodują alerty o: „operator używał tempdb do rozlewania danych podczas wykonywania”. Udało mi się uniknąć sortowania, przepisując niektóre zapytania, ale tak naprawdę nie rozwiązuje to problemu. Te same zapytania na starym serwerze nie powodują rozlewania. Czytałem, że wycieki występują, gdy MSSQL nie może ukończyć operacji w pamięci i musi przelać / stronę do tempdb. Czy powinienem martwić się wyciekami?

Przykłady

wprowadź opis zdjęcia tutaj

Uruchomiłem sp_updatestats w bazie danych, więc statystyki powinny być aktualne, ale zauważysz, że istnieją pewne rozbieżności między szacowaną a rzeczywistą liczbą wierszy.

Problem z pamięcią

Ustawiłem maksymalne ustawienie pamięci dla MSSQL na 58 z 64 GB. Obecnie MSSQL zużył około 35 GB tej pamięci, ale ma działający zestaw tylko 682 MB. Stary serwer (choć produkcyjny, obsługuje ładowanie) ma 44 GB pamięci przydzielonej do MSSQL, z czego 43,5 GB jest w zestawie roboczym.

wprowadź opis zdjęcia tutaj

Nie wiem, czy wyciek może być związany z ustawieniem pamięci - ktoś ma jakieś pomysły? MSSQL ma obecnie wiele pamięci RAM do oszczędzenia, więc dlaczego rozlewa się do tempdb dla niektórych rodzajów i dopasowań mieszających?

sql-server sql-server-2012

— Andy W.
źródło

Alert w planie wykonania jest nowy w 2012 roku. Czy sprawdziłeś, czy nie rozlewał się cały czas na starym serwerze? Czy monitorowałeś to?

— Martin Smith

@MartinSmith ah, nie zdawałem sobie sprawy, że alert był nowy. Nie monitorowałem pod kątem wycieków na starym serwerze. Zbadam to.

Nieco styczny punkt, ale siedzę przed złączeniem 10 tabel, które domyślnie używa głównie łączenia scalającego z bardzo dobrymi szacunkami wierszy, które powodują wylanie tempdb na poziomie 0 i czas działania 25s. Wymuszanie łączenia skrótów (i porządek) usuwa wycieki i uruchamia się za 9s. Zastanawiam się, czy to scalenie vs skrót lub wyciek powoduje różnicę i czy optymalizator odpowiednio wyważa efekt pozornie znanego nadchodzącego wyciek (ponieważ szacunki wierszy są bardzo dobre).

— crokusek

Nowy serwer ma sprzętową liczbę?

— stacylaray

Tutaj jest kilka różnych pytań:

P: Dlaczego zapytania się nie rozlewały?

Były, ale SQL Server Management Studio nie ujawnił tego jako wyraźnego błędu przed SQL 2012. To świetny przykład tego, dlaczego robiąc dostrajanie wydajności, musisz sięgać głębiej niż graficzny plan wykonania.

P: Dlaczego zapytania rozlewają się na dysk?

Ponieważ program SQL Server nie przyznał im wystarczającej ilości pamięci do ukończenia operacji. Być może plan wykonania nie docenił wymaganej ilości pamięci, a może pudełko jest pod presją pamięci lub są to tylko duże zapytania. (Pamiętaj, że SQL Server używa pamięci do trzech rzeczy - buforowania stron surowych danych, buforowania planów wykonania i obszaru roboczego dla zapytań. Ta pamięć obszaru roboczego jest dość mała).

P: Jak mogę ograniczyć wycieki?

Pisząc rozbudowane instrukcje T-SQL, posiadając aktualne statystyki, umieszczając wystarczającą ilość pamięci na serwerze, budując odpowiednie indeksy i interpretując plany wykonania, gdy sprawy nie działają zgodnie z oczekiwaniami. Sprawdź książkę Granta Fritchey'a Dostrajanie wydajności zapytań SQL Server, aby uzyskać szczegółowe wyjaśnienia wszystkich tych kwestii.

— Brent Ozar
źródło