How “Optimize for Adhoc Workload” impacts your Plan Cache

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.

A huge amount of unused execution plans...

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.

Optimize for Adhoc Workloads gives you a better memory footprint

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

7 thoughts on “How “Optimize for Adhoc Workload” impacts your Plan Cache”

  1. Hi Klaus,

    Good topic!

    I already have “Optimize for Adhoc Workload” set to ON.

    However, my cache has is full of these entries:
    SELECT * FROM vwComplexView WHERE field1 LIKE ‘%SMITH%’
    SELECT * FROM vwComplexView WHERE field2 LIKE ‘%2127701234%’
    SELECT * FROM vwComplexView WHERE field3 LIKE ‘%ZN1111111111111%’
    SELECT * FROM vwComplexView WHERE field4 LIKE ‘%JOHNS%’
    SELECT * FROM vwComplexView WHERE field5= 62
    SELECT * FROM vwComplexView WHERE field6= 103917

    How would you recommend tackling something like that?

    Thank you!

    1. Klaus Aschenbrenner

      Hello Mark,

      Thanks for your comment.
      It depends on how often the cached plans from these SQL statements are reused, and how large the cached plans are.

      Thanks,

      -Klaus

  2. Darko Martinovic

    This parameter will not reduce the number of records in the cache, rather than their size,as Klaus wrote. If you want to reduce the number of records in the cache, each query run with parametars.

    This parameter was introduced in sql server 2008, so my question is why is not by default set to true?
    It seems that Microsoft is pretty conservative in terms of the initial configuration. There are many examples on this subject. In SQL Server 2016, can be seen some progress regarding the configuration of tempdb. But that’s another topic.

    1. Klaus Aschenbrenner

      Hello Vipul,

      Optimize for AdHoc Workload was introduced with SQL Server 2008. No chance on SQL Server 2005, which now also already unsupported.

      Thanks,

      -Klaus

  3. Hi Klaus,

    I’m really surprised, why doesn’t this kind of query get picked up by simple parameterization? I thought that’s what it was for.

    I was secondly surprised that the query optimizer doesn’t use query fingerprints to determine the plan could be reused.

    Cody

    1. After testing it I’ve answered my own question (at least the first part).

      Simple parameterization kicks in when the SHOWPLAN “Optimization Level” is TRIVIAL. If you do a Select X From Y Where B = 1 query this only appears to be trivial if there’s an index on the column.

      In this AdventureWorks example there’s no index on that column by default and so the Optimization Level is FULL and so simple parameterization didn’t kick in.

      So while the code is a little sucky it isn’t entirely to blame. Obviously there’s been a lack of communication between the imaginary AdventureWorks DBA and imaginary developer 🙂

Leave a Reply to Cody Cancel Reply

Your email address will not be published. Required fields are marked *