Podczas badania powolnego zapytania okazało się, że plan wykonania był wyjątkowo nieoptymalny (zagnieżdżona pętla wykonująca 9 milionów wykonań wyszukiwania, gdzie szacowana liczba wykonań wyniosła 1). Po potwierdzeniu, że niektóre istotne statystyki były rzeczywiście nieaktualne, przebudowałem statystyki, a problem z wydajnością został skutecznie rozwiązany.
Ta baza danych ma włączoną funkcję Auto Update Statistics (domyślnie włączona). Rozumiem, że istnieje próg dla automatycznych aktualizacji statystyk oparty na tym, że istnieje 20% + 500 modyfikacji wierszy (aktualizacja / wstawianie / usuwanie). Wydaje się, że próg ten został w znacznym stopniu przekroczony w wielu indeksach, ponieważ wydaje się, że występuje (A) problem z automatycznymi aktualizacjami lub (B) Strategia aktualizacji ma coś więcej niż mogłam znaleźć w Internecie dokumentacja.
Doceniam to, że można skonfigurować zaplanowane zadanie w celu aktualizacji statystyk i takie podejście prawdopodobnie zastosujemy, jeśli nie będzie można znaleźć innego rozwiązania, ale myli nas, dlaczego tak duża liczba modyfikacji nie wywołałaby automatyczna aktualizacja niektórych statystyk - zrozumienie, dlaczego może pomóc nam zdecydować, które statystyki wymagają aktualizacji przez zaplanowane zadanie.
Kilka dodatkowych uwag:
1) Problem został zauważony w bazie danych, w której dane są tworzone za pomocą testu obciążenia i dlatego tak duża ilość danych jest dodawana w krótkim czasie, a więc jeśli automatyczna aktualizacja odbywa się okresowo (np. Raz dziennie o większość), może to tłumaczyć niektóre z zaobserwowanych zachowań. Również nasze testy obciążenia często obciążają bazę danych, dlatego zastanawiam się, czy SQL odracza aktualizacje statystyk, gdy jest duże obciążenie (a następnie z jakiegoś powodu nie aktualizuje statystyk).
2) Podczas próby odtworzenia tego problemu za pomocą skryptu testowego zawierającego kolejne instrukcje INSERT, SELECT i DELETE problem nie wystąpił. Zastanawiam się, czy różnica polega na tym, że każda z tych instrukcji wpływa na wiele wierszy na instrukcję SQL, podczas gdy nasz skrypt testu obciążenia będzie zwykle wstawiał wiersze indywidualnie.
3) Wspomniana baza danych jest ustawiona na „prosty” model odzyskiwania.
Niektóre odpowiednie linki:
- Lista kontrolna do analizy wolno działających zapytań
- Korzystanie ze statystyk w celu poprawy wydajności zapytań
Podniosłem również ten problem przez Microsoft Connect:
AKTUALIZACJA 30.06.2011:
Po dalszym dochodzeniu uważam, że statystyki, które są nieaktualne powyżej poziomów progowych (np. 500 wierszy + 20%), są statystykami, które nie są wykorzystywane przez zapytanie problemowe, dlatego prawdopodobnie zostaną zaktualizowane po uruchomieniu zapytania to ich wymaga. Dla statystyk, które są używane przez zapytanie, te są regularnie aktualizowane. Pozostałym problemem jest to, że statystyki te są rażąco mylące dla optymalizatora planu zapytań po zaledwie kilku względnych wstawkach (np. Powodując wspomniane około 9 milionów poszukiwań, gdzie szacunkowa liczba wynosiła 1).
W tej chwili mam przeczucie, że problem związany jest ze złym wyborem klucza podstawowego, klucz jest unikalnym identyfikatorem utworzonym za pomocą NEWID (), a zatem bardzo szybko tworzy bardzo rozdrobniony indeks - szczególnie jako domyślny współczynnik wypełnienia w SQL Serwer jest w 100%. Mam przeczucie, że w jakiś sposób powoduje to wprowadzanie w błąd statystyk po stosunkowo niewielkiej liczbie wstawek wierszy - mniej niż próg ponownego obliczenia statystyk. To wszystko prawdopodobnie nie jest problemem, ponieważ wygenerowałem wiele danych bez przebudowywania indeksów w połowie, dlatego słabe statystyki mogą być konsekwencją wynikowej bardzo dużej fragmentacji indeksu. Myślę, że muszę dodać cykle konserwacji SQL Server do mojego testu obciążenia, aby uzyskać lepszy pomysł na wydajność w prawdziwym systemie przez długi czas.
AKTUALIZACJA 2012-01-10:
Kolejny czynnik do rozważenia. Dwie flagi śledzenia zostały dodane do SQL Server 2005 (i wydają się być nadal obecne w 2008 r.) W celu usunięcia konkretnych niedociągnięć związanych z występowaniem nieaktualnych i / lub wprowadzających w błąd statystyk. Flagi, o których mowa, to:
DBCC TRACEON(2389)
DBCC TRACEON(2390)MSDN: Dziennik internetowy Iana Jose: Rosnące klucze i statystyki automatycznych szybkich poprawek dla Rosnących kolumn, Fabiano Amorim
Powinieneś oczywiście być bardzo ostrożny, decydując się na włączenie tych flag, ponieważ mogą one mieć szkodliwe skutki.