SQL Server Plan Cache Bloating

In this post we will see how & why Plan Cache bloating happens – just a few examples.

We are going to use AdventureWorks2016 database for this demo.

Let’s first free the Plan Cache:

DBCC FREEPROCCACHE

Next, let’s fire a simple select statement, with one predicate.

select * FROM Person.Person where LastName = ‘duffy’

We get the result set here, with two records.

Now, lets find out how many execution plans we have in the Plan Cache for the above query. In other words, let’s look at the query statistics as to how many entries do we have for this specific query.

Let’s do a CROSS APPLY with sys.dm_exec_sql_text DMV with an added filter (‘%select * from Person.Person%’) to extract the stats for this specific query.

SELECT qt.text, * FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) qt
WHERE qt.text like ‘%select * from Person.Person%’

Here is the output.

We are interested in something called query_hash. Each query internally inside SQL server relational engine has a unique hash value.

Now, let’s find out how many entries do we have for this specific query hash.

SELECT COUNT(*) FROM sys.dm_exec_query_stats
WHERE query_hash = 0x8EF6697A4A11EE50

There is only one entry. Remember we ran the query only once.

Now, let’s find out how many plans do we have for this query.

SELECT qs.query_hash, qp.query_plan FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_query_plan (qs.plan_handle) qp
WHERE qs.query_hash = 0x8EF6697A4A11EE50

As expected, there is only one plan for the query.

Even if you execute the same SELECT statement multiple times, it’s the same query hash and the same plan will be reused.

Let’s make some trivial changes to the query. Instead of “duffy”, we input ‘duffy1’, (changing the parameter value) and let’s execute the SELECT statement.

select * FROM Person.Person where LastName = ‘duffy1’

Now, in the query stats, the number of entries is 2. The interesting thing is, for the same query hash now we have two entries in query_stats.

SELECT COUNT(*) FROM sys.dm_exec_query_stats
WHERE query_hash = 0x8EF6697A4A11EE50

Let’s see how many plans we have in the plan cache.

SELECT qs.query_hash, qp.query_plan FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_query_plan (qs.plan_handle) qp
WHERE qs.query_hash = 0x8EF6697A4A11EE50

OK, we have 2 plans.

Now, let’s make a small casing change to the query. Instead of “select”, we use “SELECT” this time.

SELECT * FROM Person.Person where LastName = ‘duffy1’

let’s run both the above queries again. You will see that there are three entries in query stats and three query plans. This means SQL Server is treating each query separately even though their query hashes are same.

If you have a query coming in from a client application with a variety of parameter values, and SQL Server is creating one plan for each type of oparameter value – this is probably the worst scenario one could encounter in Plan Cache Bloating. The other is, internally SQL server treats the query (when it is forming the plan) in such a way that the incoming text has to be identical for the relational engine to reuse a plan that is already available in the plan cache. Even changing one character causes it to create another execution plan, because then, both the texts are not textually identical. But the hash value of the query remains the same. If the same query comes with different SET options, that will also trigger a different plan.

For Plan Cache Bloating scenarios, there are a few solutions such as Forced Parameterization (which may lead to parameter sniffing issues). Another solution is fixing the query from the client application which is parameterizing the SQL statements.

Please note that Plan Cache Bloating can have a serious impact on SQL server’s memory performance. It is also affect CPU performance as creating a new plan is a CPU-intensive process.

Watch the demo in action.

Thanks for reading. If you liked the content, do share it. I am on Twitter if you wish to connect.

You can also subscribe to my exclusive newsletter ConnectWithAB.