Pozwól mi podsumować (i zaokrąglić!) Ważne punkty danych w twoim arkuszu kalkulacyjnym:
Total Use Count 1
--------------------------------------- -----------------------
Total Plans Total MBs Avg Use Count Total Plans Total MBs
----------- --------- ------------- ----------- ---------
Adhoc 55,987 3,054 3 38,314 2,036
Proc 709 1,502 1,549 135 527
Więc pierwszy wiersz pokazuje złe rzeczy, zajmując około 2/3 pamięci podręcznej twojego planu (rzeczy, które są w większości używane tylko raz, z kilkoma bardzo małymi wyjątkami). Musisz spróbować pozbyć się ich jak najwięcej. Drugi rząd pokazuje dobre rzeczy. To są rzeczy, które chcesz w pamięci podręcznej planu (plany z dużą ilością ponownego użycia). Reszta danych jest w dużej mierze nieistotna IMHO. Jeszcze jedna uwaga: mówisz, że dostęp odbywa się wyłącznie za pośrednictwem procedur przechowywanych, ale jeśli te procedury używają dynamicznego SQL, instrukcje te są buforowane jako AdHocplany, a nie Procplany.
W wersji 2008 lub nowszej powiedziałbym, że włącz optimize for ad hoc workloadsi przejdź do następnego problemu - to zabrałoby ilość MB, którą twoje plany jednorazowego użytku zajmują prawie do zera. Niestety w 2005 r. Opcje są dość ograniczone, oprócz refaktoryzacji tych procedur przechowywanych w celu użycia OPTION (RECOMPILE)dynamicznego SQL na poziomie instrukcji i / lub mniej / brak, lub włączenia wymuszonej parametryzacji na poziomie bazy danych - która próbuje uzyskać lepszy plan ponownego wykorzystania z podobne zapytania, traktując literały jako parametry do celów dopasowania planu. Waham się nawet wspomnieć o przewodnikach po planach, ponieważ nie są one dla nieśmiałych i - jak omawiam w dalszej części tej odpowiedzi - nie jestem pewien, czy warto pójść tą ścieżką, chyba że wiesz, że pamięć podręczna planu jest zdecydowanie źródłem twojej wydajności kwestia.
Zapytałem o @@VERSIONto, ponieważ przed SP2 algorytm ilości pamięci, którą można było przypisać do pamięci podręcznej planu, był stosunkowo luźny. Począwszy od SP2 dość mocno to zaostrzyły (zmiana jest udokumentowana i wyjaśniona w tym poście i tym poście ). W twoim przypadku pamięć podręczna planu jest względnie pełna, więc nic dziwnego, że brakuje ci pamięci podręcznej. 26 GB = górna granica 5,8 GB; Widzę ~ 4,5 GB w arkuszu kalkulacyjnym, ale mogą występować pewne różnice w obliczeniach lub konfiguracji, o których nie wiem.
Ten MSDN artykuł mówi o optimize for ad hoc workloadsserwerze ustawienia dodane w 2008 roku i wspomina flagę śledzenia 8032, który pozwoli przydzielić więcej pamięci do pamięci podręcznej (prawdopodobnie z powodu braku ustawienie tej opcji na poziomie serwera, co ja teraz polecam wszystkim naszych klientów, a przynajmniej 99%, którzy nie są już w 2005 roku). Nigdy nie testowałem tej flagi śledzenia w 2005 SP3 lub SP4 i szczerze mówiąc, nawet nie jestem pewien, kiedy została wprowadzona. Nie wiem też, czy to rozwiąże problem, czy po prostu go przeniesie, ponieważ myślę, że nawet gdybyś miał więcej% pamięci RAM przydzielonej do pamięci podręcznej, nadal będziesz go zapełniał i miałeś wiele braków pamięci podręcznej z powodu natury twoje procedury składowane.
Lub, oczywiście, jeśli istnieje nawet problem do rozwiązania, który w ogóle dotyczy bezpośrednio pamięci podręcznej planu. To, że współczynnik trafień w pamięci podręcznej nie jest tak wysoki, jak można się spodziewać, nie oznacza, że powoduje on problem, i oczywiście odwrotnie, nawet przy 100% trafieniu w pamięć podręczną - co nie wydaje się realistyczne, biorąc pod uwagę tak wiele z twoich planów są jednorazowe i ad hoc - twoi użytkownicy mogą nadal mieć problemy z wydajnością spowodowane przez coś zupełnie innego.
Sugeruję, aby szukać lepszych pistoletów do palenia niż planować współczynnik trafień w pamięci podręcznej. Uzyskaj więcej informacji na temat skarg dotyczących wydajności użytkowników. Czy wszystkie zapytania są zawsze wolne? Niektóre pytania? Pewne pory dnia / tygodnia / cyklu koniunkturalnego? Czy tylko zapytania dotyczące raportów są wolne? Zapoznaj się z poważnym, suchym i długim dokumentem na temat najlepszych praktyk programu SQL Server - w szczególności z sekcją dotyczącą oczekiwań i kolejek, która może pomóc w sformułowaniu logicznego podejścia do identyfikowania, diagnozowania i rozwiązywania problemów z wydajnością. Poprawienie wyglądu pewnej liczby na desce rozdzielczej - liczby, o której nawet nie wiesz, bezpośrednio przyczynia się do problemu - może być bardzo satysfakcjonujące, ale jeśli nie rozwiąże to problemów z wydajnością użytkowników, to tak naprawdę nie gdziekolwiek.
Mogą być również przydatne podczas czytania kompilacji / rekompilacji i planowania ponownego użycia pamięci podręcznej. Niektóre z nich koncentrują się na 2008 r. (Szczególnie te dotyczące ustawienia obciążeń ad hoc), ale znaczna część informacji jest nadal przydatna w 2005 r. I / lub w celu lepszego zrozumienia korzyści wynikających z aktualizacji (podpowiedź, podpowiedź).