Optimize for Ad hoc

Ett problem som kan inträffa om man har många olika varianter av ad hoc frågor som aldrig upprepas är att plan cachen kan fyllas upp med en massa planer som aldrig kommer användas. För att undvika detta scenario finns kan man konfigurera SQL Server så att effekten av detta minimeras. Detta kan man dels göra i Management Studio under Server Properties/Advanced där man hittar flaggan “Optimize for Ad hoc Workloads” eller med sp_configure. Denna ser till så att första gången en plan sparas så sparas endast en liten del av planen och därmed utnyttjas cachen bättre. Skulle nu samma fråga komma en gång till så kommer SQL Server att spara en fullständig plan i cachen och frågan behandlas därefter som vanligt. Innan man ger sig på detta måste man ha koll på vad som finns i plan cachen. Detta kan man ta reda på genom att studera sys.dm_exec_cached_plans som visar hur fördelningen ser ut men vad vi egentligen är intresserade av är hur mycket av cachen som upptas av planer för ad hoc frågor som bara körts en gång. 

Här kan man hitta ett skript som visar hur mycket utrymme som upptas av respektive objekttyp i cachen. Om du upptäcker att större delen av cachen upptas av planer för ad hoc frågor som bara körts en gång kan det vara läge att använda sig av “Optimize for Ad hoc Workloads”.

Om du behöver hjälp med att avgöra vad som är bäst för just din miljö så hjälper våra SQL Server konsulter gärna till.