SQL Server Optimize for Ad Hoc Workloads
by Optimize for Ad Hoc Workloads is a configuration option introduced with SQL Server 2008 that can be very helpful in cases where you have a large number of ad hoc queries with low or no possibility of reuse. When this option is used, SQL Server will store a small compiled plan stub in the plan cache when a query is optimized for the first time instead of the full execution plan. Only after a second optimization, the plan stub is replaced with the full execution plan. Avoiding plans that are never reused can help minimize the size of the plan cache and therefore free up system memory. In fact, there is basically no downside to using this option, so you may consider enabling it for every SQL Server installation.
Let’s look at an example using sp_configure to enable this option. Execute the following statements:
At this moment, we have enabled the Optimize for Ad Hoc Workloads configuration option at the instance level, and after the execution of the first SELECT statement, we see the following output:
As you can see, the compiled plan stub is a small object using a small number of bytes (in this case, 352). The usecounts column is always 1 for a compiled plan stub because it is never reused. It is also worth clarifying that a plan stub is not the same as the shell query. Now execute the following statements:
After the query is optimized the second time, the compiled plan stub is replaced with a full execution plan, as shown on the cacheobjtype column. Also notice that the size of the plan is considerably larger than the plan stub (in this case, 16,384 bytes).
However, keep in mind that although this configuration option can be useful in scenarios where you may not have control over the queries submitted to SQL Server, it does not mean that writing a large number of ad hoc queries is recommended or encouraged. Using explicit parameterization (for example, with stored procedures) is recommended instead. Finally, although it is recommended to keep this configuration option enabled in your environments, don’t forget to disable it to continue testing the remaining code in the book using the default configuration.
EXEC sp_configure ’optimize for ad hoc workloads’, 0
|
Statistics Says It All
16
Years of Experience
3000
Gratified Students
100
Training Batches
9600
Training Hours
Subscribe
Please subscribe our technical blog to get recent updates.