Today I want to talk about the server option Optimize for Adhoc Workload and how it affects your Plan Cache in SQL Server. Before we dig into the details, I want to show you how SQL Server can reuse cached execution plans.

Execution Plan Reuse

Every time that you submit a query to SQL Server, SQL Server checks the Plan Cache to see if there is an existing cached execution plan available for reuse. SQL Server calculates a hash value across the submitted SQL statement and uses that hash value to check the Plan Cache for an existing execution plan (in reality it is more complicated because other options – like SET options – can also affect execution plan reuse).

If a cached plan is found, the execution plan is reused. Otherwise a new execution plan is compiled through the Query Optimizer and finally put into the Plan Cache for further reuse. Imagine now the following badly written C# application.

for (int i = 1; i <= 10000; i++)
{
   cmd = new SqlCommand(
   "SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID = " + i.ToString(), cnn);
   SqlDataReader reader = cmd.ExecuteReader();
   reader.Close();
}

This application just calls a simple SELECT statement in a loop 10000 times – with a hard-coded parameter value. With the hard-coded parameter value the hash value of the SQL statement is always different and therefore SQL Server can’t reuse a cached plan. As a side-effect you have 10000 different ( but nearly identical) execution plans stored in the Plan Cache.

Every execution plan also takes some memory (in our case 16 KB), so you “waste” around 160 MB of Plan Cache! Imagine now that you can’t change the implementation of your application, but you still have to improve the memory footprint of the Plan Cache in SQL Server. This is where the server option Optimize for Adhoc Workload comes into the game.

Optimize for Adhoc Workload

This server option was introduced back with SQL Server 2008, and changes the way how SQL Server caches and reuses execution plans. As soon as you have enabled this configuration option, SQL Server doesn’t really cache your complete execution plan anymore. SQL Server only stores a so-called Compiled Plan Stub. This stub value is just a hash value across your SQL statement and needs only 352 bytes on SQL Server 2014.

In our case we now need only 7 MB of memory in the Plan Cache for all 10000 submitted SQL statements. That’s a huge difference compared with the 160 MB previously! With this stub value SQL Server is now able to remember that a specific SQL statement was previously executed.

When you now execute the identical SQL statement again, SQL Server finds the stub value in the Plan Cache, compiles again your execution plan, and finally stores the resulting complete execution plan in the Plan Cache. Therefore SQL Server only stores a complete execution plan in the Plan Cache when that SQL statement was executed at least twice. SQL statements that were executed just once (adhoc SQL statements) just need 352 bytes in the Plan Cache.

Therefore you are able to reduce the memory footprint of the Plan Cache with this configuration option – without making any changes in your application! If you want to know more about this specific problem in SQL Server, you can also watch my SQL Server Quickie #23 where I have talked about Plan Cache Pollution.

Summary

In this blog posting I have shown you a very simple approach to how you can deal with badly written applications that pollute your Plan Cache with identical execution plans that can’t be reused. The configuration option Optimize for Adhoc Workload is also one option that I always recommend be enabled by default. It will lead to better memory management of the Plan Cache.

The side-effect is that you introduce a minor CPU overhead, because every execution plan must be compiled 2 times before the plan is finally stored in the Plan Cache. But normally you should have enough CPU headroom available for this additional compilation.

Thanks for your time,

-Klaus