This is brief – it is an area that I need to follow up on and dive into deeper. Below are my notes on the caching of ad hoc queries developed during recent work for a client.
Ad hoc queries tend to bloat the plan cache, making the identification of plans costly. In addition, SQL Server compares ad hoc queries on the full query text. This means two very similar queries that could use the same execution plan effectively will be treated differently and new plans generated.
Have now got a script which will survey the makeup of the plan cache.
Reviewed the server-level configuration: ‘optimise for ad hoc workloads’. This effectively works by storing a checksum of a stub of the query text. This reduces the size of the plan cache and makes the search for cached plans more efficient. When SQL Server recognises that a stub is being called a second time, it will delete the stub and replace it with a fully cached plan for future reuse. This way only the frequently re-used plans are held in their entirety.
Index usage / missing index DMVs are not particularly useful when there is a high ad hoc workload. There just isn’t enough aggregate data available to be useful. These DMVs are enormously useful when you are looking for metrics about frequently used queries. But by definition, ad hoc queries are not frequently re-used which means it is difficult to find the information you want from the DMVs.
I have some questions as to whether this impacts stored procedures. Or whether the fact that a sproc will be re-used means that this will even out ot ‘normal’ quickly for sprocs. Not sure how to test this, will investigate further.
Good references:
Ad hoc Query Plans don’t reuse Execution Plans: Myth or Fact
Plan cache and optimising for ad hoc workloads
Optimise for ad hoc workloads Server Configuration option
:: NickBurns 