Włączenie opcji „ Optymalizuj pod kątem obciążeń ad hoc ” spowoduje, że zapytania ad-hoc uruchamiane 2. raz będą tak samo wolne jak 1., ponieważ będziesz kompilował plan wykonania i pobierał te same dane ( bez buforowania) te pierwsze 2 razy.
To może nie być wielka sprawa, ale zauważysz to podczas testowania zapytań.
Co się teraz stanie, jeśli ta opcja nie będzie włączona, a pamięć podręczna pełna zapytań Ad-Hoc 1-Off?
Algorytm zarządzania buforowaniem:
Po wprowadzeniu tej funkcji optymalizacji algorytm zarządzania buforowaniem również został zaktualizowany.
Artykuł Kimberly Tripp odwołuje się również do postu Kalena Delaneya na temat tej zmiany algorytmu.
Wyjaśnia to najlepiej:
Zmiana faktycznie oblicza wielkość pamięci podręcznej planu, przy której SQL Server rozpoznaje presję pamięci i rozpocznie usuwanie planów z pamięci podręcznej. Plany do usunięcia to tanie plany, które nie zostały ponownie wykorzystane, a to DOBRA RZECZ.
Oznacza to, że te nieznośne jednorazowe plany będą dostępne jako pierwsze, gdy trzeba zwolnić zasoby.
Zatem teraz pytanie brzmi:
„ Dlaczego POTRZEBUJEMY„ Optymalizować pod kątem obciążeń ad hoc ”, gdy SQL Server zajmuje się usuwaniem nieużywanych planów, gdy jest to konieczne? ”
Moja odpowiedź na to pytanie, jeśli regularnie masz mnóstwo ton generujących dynamiczne sql nieparametryzowanych reklam -hoc wysyła zapytania, więc warto włączyć tę funkcję.
Chcesz uniknąć obciążania zasobów systemowych, tak że wymusza to usunięcie planu buforowanego / danych po zużyciu maksymalnej pamięci w pamięci podręcznej.
Skąd mam wiedzieć, kiedy muszę to włączyć?
Oto zapytanie, które napisałem, aby pokazać, ile aktualnie masz planów buforowanych Ad-Hoc i ile miejsca na dysku zjadają (wyniki będą się zmieniać w ciągu dnia - przetestuj je w czasie dużego obciążenia):
--Great query for making the argument to use "Optimize for Ad Hoc Workloads":
SELECT S.CacheType, S.Avg_Use, S.Avg_Multi_Use,
S.Total_Plan_3orMore_Use, S.Total_Plan_2_Use, S.Total_Plan_1_Use, S.Total_Plan,
CAST( (S.Total_Plan_1_Use * 1.0 / S.Total_Plan) as Decimal(18,2) )[Pct_Plan_1_Use],
S.Total_MB_1_Use, S.Total_MB,
CAST( (S.Total_MB_1_Use * 1.0 / S.Total_MB ) as Decimal(18,2) )[Pct_MB_1_Use]
FROM
(
SELECT CP.objtype[CacheType],
COUNT(*)[Total_Plan],
SUM(CASE WHEN CP.usecounts > 2 THEN 1 ELSE 0 END)[Total_Plan_3orMore_Use],
SUM(CASE WHEN CP.usecounts = 2 THEN 1 ELSE 0 END)[Total_Plan_2_Use],
SUM(CASE WHEN CP.usecounts = 1 THEN 1 ELSE 0 END)[Total_Plan_1_Use],
CAST((SUM(CP.size_in_bytes * 1.0) / 1024 / 1024) as Decimal(12,2) )[Total_MB],
CAST((SUM(CASE WHEN CP.usecounts = 1 THEN (CP.size_in_bytes * 1.0) ELSE 0 END)
/ 1024 / 1024) as Decimal(18,2) )[Total_MB_1_Use],
CAST(AVG(CP.usecounts * 1.0) as Decimal(12,2))[Avg_Use],
CAST(AVG(CASE WHEN CP.usecounts > 1 THEN (CP.usecounts * 1.0)
ELSE NULL END) as Decimal(12,2))[Avg_Multi_Use]
FROM sys.dm_exec_cached_plans as CP
GROUP BY CP.objtype
) AS S
ORDER BY S.CacheType
Wyniki:
Nie powiem „ kiedy masz X MB ” lub „ jeśli X% twoich ad hoc jest jednorazowych ”, aby to włączyć.
Nie wpływa na Sprocs, wyzwalacze, widoki ani sparametryzowany / przygotowany SQL - tylko zapytania Ad-Hoc.
Osobiście zalecam, aby włączyć się w środowisku Prod, ale rozważ pozostawienie tego w środowisku deweloperów.
Mówię to tylko dla deweloperów, ponieważ jeśli optymalizujesz zapytanie, które zajmuje minutę lub dłużej, nie chcesz uruchamiać go 3 razy, zanim zobaczysz, jak szybko pójdzie z nim w pamięci podręcznej - co jednorazowo edytujesz go, aby znaleźć najlepszy projekt optymalizacji.
Jeśli twoja praca nie polega na robieniu tego przez cały dzień, zwariuj i poproś DBA o włączenie go wszędzie.