Introduction

Recently I was able to attend SQL Bits 2019 in Manchester, to acquire some of the latest knowledge on a number of low-level topics in SQL Server.  In this blog article, I will share some knowledge that I gained on one of those topics - plan caching. 

 

When using a relational database management system such as SQL Server, it can be easy to overlook low-level resources such as CPU and memory.  This is partly because the database engine and the SQL language does a good job of abstracting them away from the developer.  However, having some knowledge of how these low-level resources are used in certain situations can be essential to identifying, resolving and avoiding performance pitfalls.  One example of such a situation within SQL Server is that of ad-hoc query plan generation and caching.  It is an expensive process for SQL Server to generate an efficient query plan.  To counter this, SQL Server will try and cache the plan in memory to allow for re-use.  However, this can have implications for memory usage. 

 

It is desirable to have as much memory as possible free for the buffer pool.  The buffer pool holds data pages in memory, avoiding expensive trips to disk where possible.  Having too many cached plans can reduce the amount of memory available to the buffer pool.  This blog article looks at some of the mechanisms SQL Server uses for ad-hoc query plan caching, ending with a comparison between ad-hoc query and stored procedure plan re-use.

 

For reference, this article will use the below SQL to view what is in the plan cache for the example queries.

 

The below command is also executed after each example to clear the plan cache.

 

Simple Parameterization

Simple parameterization is used by SQL Server to generalise cached plans of queries that filter on constants.  This helps to reduce memory consumption.  Let’s execute the below 4 queries (one by one, not as a batch!), which are the same - but filter on a different constant.

 

Now let’s view what is in the plan cache.

The plan cache shows that SQL Server has used simple parameterization to generalise the query.  Each different constant used in the filter produces an entry, but this is just a placeholder for lookup purposes and uses less memory than a full entry.

 

However, SQL Server can only use simple parameterization with the most basic of queries.  Queries containing constructs such as DISTINCT, TOP, UNION and JOIN cannot use simple parameterization.  Most queries do use these constructs, especially JOIN.  The reason SQL Server is very cautious with using simple parameterization is because it needs to ensure that performance will remain the same for any given parameter.

To demonstrate this, let’s add the TOP construct to the original 4 queries and execute them.

 

Now let’s view what is in the plan cache.

The plan cache shows that SQL Server hasn’t been able to use simple parameterization to generalise the query and each entry uses more memory.

 

Optimize For Ad Hoc Workloads

In the last example we saw that for each query executed a plan was cached.  Caching a plan provides the benefit of re-use, but if the query is never executed again then it is simply an unnecessary use of memory.  SQL Server has an instance level configuration option named ‘optimize for ad hoc workloads’ that helps to reduce such memory wastage.

 

First, let’s switch the option on and test it out.

 

Followed by execution of the previous 4 queries.

 

Now let’s view what is in the plan cache.

The plan cache now contains a set of compiled plan stubs, with a much smaller memory footprint.  On the first execution of a query, SQL Server is caching a placeholder rather than the plan.  If the same query is executed again, SQL Server will then replace the stub with the plan.  The advantage of this approach is that only a small amount of memory is used for queries that are executed one time only.

 

The ‘optimize for ad hoc workloads’ setting should always be enabled as best practise.

 

Forced Parameterization

Previously we saw that SQL Server can use simple parameterization to generalise plans and save memory, but only with trivial queries.  However, there is a database level option to force SQL Server to generalise plans for queries deemed too complex for simple parameterization.  This option should only be used as last resort and with careful consideration.  Enabling it can force plans upon queries that then suffer performance issues, similar to what is experienced with parameter sniffing in stored procedures.

 

The below statement enables forced parameterization.

 

Let’s execute those 4 queries again.

 

Now let’s view what is in the plan cache.

The plan cache shows that the forced parameterization setting has forced SQL Server into generalising the query.  This saves memory, as less memory is required to store the placeholders pointing to the generalised query plan.

 

Caching Stored Procedure Plans

So far, this article has looked at the mechanisms used by SQL Server to manage the caching and memory usage of ad-hoc query plans.  SQL Server also caches the plans of objects such as stored procedures, functions and triggers.

 

To compare the plan caching and re-use of stored procedures against ad-hoc queries, the following stored procedure is created that encapsulates the example query already used.

 

Upon calling the stored procedure with 6 different parameters, we can analyse what is in the plan cache.

The plan cache shows that the plan for the stored procedure has been re-used for each different parameter without having to create a placeholder and consume additional memory for each different execution.  This is beneficial for queries or statements that are executed often, but the parameter values differ.

 

Summary

It is an expensive CPU operation for SQL Server to generate a query plan.  SQL Server looks to avoid this cost by caching and re-using plans.  However, this comes at a penalty of additional memory usage.  High memory usage in the plan cache is concerning because SQL Server needs as much memory as possible free for the buffer pool to avoid costly trips to the disk for data pages.  SQL Server uses a few mechanisms to try and cache ad-hoc queries whilst reducing memory footprint.  Simple parameterisation generalises query plans that use constant parameters, but is limited to trivial queries.  The setting ‘optimize for ad hoc workloads’ reduces the memory footprint used by one-time only queries.  Forced parameterisation forces query plan generalisation on queries not eligible for simple parameterisation but with performance risks that resemble parameter sniffing. 

 

Using stored procedures is the most efficient method of running frequently used queries and statements.  An example of where is this can be applied is in application code that builds up a SQL string and executes it on the fly, rather than calling a stored procedure.  SQL Server will re-use a cached stored procedure plan without the additional cost of creating placeholder entries for different parameters and storing them in memory.