SQL Server codziennie odtwarza plany

Ten problem występuje w naszym środowisku produkcyjnym.

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) - Enterprise Edition (64-bit) w systemie Windows NT 6.1 (kompilacja 7601: Service Pack 1).

SQL Server usuwa wszystkie (prawie 100%) starych planów wykonania i odtwarza je codziennie w ciągu jednej nocy (od 23:00 do 8:00). Działo się tak nawet wtedy, gdy „statystyki automatycznej aktualizacji” były wyłączone. Włączyliśmy „statystyki automatycznych aktualizacji” na ostatnie 2-3 tygodnie. Ale wciąż się dzieje.

Naprawdę nie wiemy, co powoduje to ponowne generowanie planów, ale jesteśmy pewni, że nie robimy tego ręcznie.

Jedyne, co naprawdę pokrywa się z harmonogramem regeneracji planów, to zadanie polegające na utrzymaniu bazy danych: codzienna reorganizacja indeksu (gdy fragmentacja wynosi 5-30%) i codzienna przebudowa indeksu (gdy fragmentacja wynosi ponad 30% ) praca. Zwykle to codzienne zadanie konserwacyjne przeprowadza tylko reorganizację (ponieważ fragmentacja indeksu nigdy nie przekracza 30% dziennie).

Wpływ:

Te nowo utworzone plany powodują, że niektóre wywołania / zapytania UDF (które są wywoływane ze strony interfejsu użytkownika / stron internetowych) zajmują znacznie więcej czasu (minuty w przeciwieństwie do mniej niż 1 sekundy), więc sesje po prostu się gromadzą, biorąc procesor blisko 90% .

Problem znika w momencie, gdy zablokowane sesje są usuwane (po stronie bazy danych) i 1), gdy wszystkie odpowiednie plany wykonania są czyszczone ręcznie (w przypadku zapytań) lub 2), gdy zmienione są funkcje UDF (w przypadku funkcji). Wszelkie nowe plany tworzone przez SQL Server od tego momentu działają idealnie przez cały dzień, dopóki następnego dnia nie będzie mieć tego samego problemu. Ponadto, to zachowanie nie jest w 100% spójne, tak naprawdę nie widzimy go każdego ranka. Ale były okresy, w których obserwowaliśmy to konsekwentnie przez 4-5 dni z rzędu.

Problem zdarza się w poranki biznesowe, wtedy wydaje się, że dostęp do interfejsu użytkownika / stron internetowych jest bardziej intensywny.

Czy ktoś ma pojęcie, co to powoduje i jak rozwiązać ten problem? Każda pomoc będzie mile widziana.

Mam kilka pomysłów, które mogą spowodować takie zachowanie.

1. Czy monitorujesz swoje ciśnienie pamięci? Być może twoje zapytania zwiększają pewien limit, który spowoduje opróżnienie pamięci podręcznej planu. Nie znam twojej aplikacji, ale czy to odpowiada twoim dziennikom z twoich serwerów frontendowych? Czy w tym czasie jest też presja?
2. Czy masz dedykowany serwer SQL, czy serwer współdzieli swój sprzęt z innymi procesami / usługami? Jeśli nie, spróbuj zamiast tego przenieść serwer SQL Server na dedykowany komputer. Zmniejszy to skutki uboczne innych usług.
3. Możesz użyć optimize for ad hoc workloads, który po prostu zapisze odcinek planu i skompiluje go, jeśli będzie potrzebny. Zmniejszy to obciążenie twojego plancache, co zmniejszy prawdopodobieństwo zaczerwienienia plancache. Możesz włączyć to za pomocą sp_configure 'optimize for ad hoc workloads',1; reconfigure. Można to zrobić, jeśli włączono advanced optionskorzystanie sp_configure 'show advanced options',1; reconfigure.
4. Innym pomysłem mogą być kopie zapasowe. Po prostu proste kopie zapasowe. Jeśli są agresywne, może się zdarzyć, że Twoja maszyna również znajdzie się pod presją. Czas, o którym wspominasz, wydaje się być dobrym czasem na zaplanowanie kopii zapasowej.
5. Może to całkiem prosty błąd w skrypcie konserwacji. Czy sprawdziłeś, czy istnieje logiczny problem, który powoduje, że skrypt odbudowuje wszystkie indeksy zamiast tylko tych, które spełniają kryteria. To może również powodować.

Tuż obok wszystkich tych możliwości, może to być przydatne, aby sprawdzić logi do pewnych zmian do opcji affinity mask, affinity I/O maska ich partnerami x64. Inną rzeczą może być zmiana MAXDOPopcji instancji. Sprawdź także ich dzienniki. Będą również musieli spłukać plancache.

Last but not least, nadal możesz uruchomić śledzenie na serwerze (wystarczy skonfigurować go za pomocą profilera, uruchomić, zatrzymać i użyć polecenia sql, aby uruchomić go ponownie na serwerze). Poza tym perfmonto twój przyjaciel. Może przez pewien czas oglądać i monitorować wartości wydajności. Być może widzisz podobieństwa pod presją z pewnymi działaniami na twoim serwerze, które mogą powodować te spłukiwanie.

Mam nadzieję, że ci to pomoże, nawet jeśli odpowiedź przyjdzie nieco później.