Friday, April 12, 2013

TSQL to find Cached Plans within SQL Server

The following SQL Statement is based off a number of DMV's and DMF's and allows us to look at the query statistics and cached query plans. This becomes useful when trying to determine how SQL Server is caching the execution query plans for our statements, either Adhoc or Proc, but more on that below.

SELECT qs.last_execution_time
, qs.execution_count
, qs.plan_generation_num
--, qs.plan_handle
--, qs.query_plan_hash
--, qs.query_hash
, cp.refcounts as [QueryPlan_RefCounts]
, cp.usecounts as [QueryPlan_UseCounts]
, cp.size_in_bytes as [QueryPlan_SizeBytes]
, cp.cacheobjtype as [CacheObjType]
, cp.objtype as [ObjType]
--, qs.statement_start_offset
--, qs.statement_end_offset
, st.text as [QueryText]
, SUBSTRING(st.text, qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN (CONVERT (nvarchar(max), st.text)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset)/2) as [StmtText]
, qp.query_plan as [QueryPlan]
FROM sys.dm_exec_query_stats qs
INNER JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
WHERE st.text NOT LIKE N'%sys.dm_%'
AND st.text LIKE N'%'
ORDER BY qs.last_execution_time DESC;

To look for query's related to a specific table or object, then change the where statement as follows:
AND st.text LIKE N'%MyTableName%'

So why would we want to care about the plan cache?

In an environment with lots of adhoc statements a problem can certainly arise where SQL Server does not reuse existing cached plans efficiently. This is because the predicate values are being recorded  along with the plans for adhoc statements within the cache. Say for example you have a query such as:

FROM dbo.ProcCacheTest
WHERE someDate = '19000605'

well this would generate a plan within the cache, but then if the application/user reissues the same statement but using different value such as:

FROM dbo.ProcCacheTest
WHERE someDate = '19000608'

This will result in an entirely new plan being generated and cached for the statement. Of cause if the statement is reissued with the exact same predicate values then it will reuse the existing plan from the cache. However if you have an environment with lots of this type of activity and with a large number of different predicate values in use, then this could cause your plan cache to be consumed by all of these statements and not allow other statements to have their plans cached correctly. In other words cause memory pressure within the plan cache and overall resulting in a degradation of performance.

So how can we avoid this, well if we wrap the adhoc query up into a stored procedure such as:

CREATE PROCEDURE dbo.myProcCacheTest (
@pDate varchar(15)
FROM dbo.ProcCacheTest
WHERE someDate = @pDate

now when the stored procedure is executed the SQL Optimiser will create a plan in the cache for the statement but with parameterization (i.e. without tracking the actual values of the search predicate).

We can also use the Optimize For Ad Hoc Workloads advanced system setting to ensure that SQL Server doesn't cache plans for adhoc statements unless they are executed more than once. However as this means the Optimizer will only create Query Plan Stubs in the cache for individual executions and then only create full Query Plans in the cache after a query's execution count is >1 it may not fix your problem as it may simply allow for more space within the Plan Cache to store these single execution queries.

There is also another option which is to use the  FORCE PARAMETERIZATION option on the database, then this will force the Optimizer to convert the search predicate within the adhoc queries to a parameter before generating the plan, thus allowing the plan to be reused. This setting doesn't support all data types so you may still find some adhoc queries being cached. In addition to this be extremely careful about implementing these type of settings in a production environment as while they can provide a vast improvement, in some cases it can have a less desired effect, it all depends on the way the exact queries are being processed.

Still the greatest improvement can be gained from removing the adhoc queries through the use of Stored Procedures and another advantage that a Stored Procedure offers us is that the individual statements with in the procedure are cached with individual plans, and we can see those individual statements/plans using the same statement above. I have commented out the query_hash and query_plan_hash columns, but these are how SQL Server tracks the unique statements and plans within it's cache.

So if we had a stored procedure like:

CREATE PROCEDURE dbo.myProcCacheTest (
@pDate varchar(15)
FROM dbo.ProcCacheTest
WHERE someDate = @pDate;

DECLARE @pDate2 DateTime = DateAdd(Day, 7, @pDate);
FROM dbo.ProcCacheTest
WHERE someDate >= @pDate
AND someDate <= @pDate2;

If we were to review the cache for this stored procedure we would find a plan in the cache for the first select query and then another for the second select query. Each will have the same plan_handle, but will have individual query_plan_hash and query_hash values.

Other aspects which are useful to gain from looking at the Cache is when plans are regenerated through Recompile, either due to data modification, SET options, or referenced object schema changes. Remember though that when you change the schema of a procedure it's associated cached plans are dropped and removed.

Enjoy digging deeper into the plan cache.

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.


  1. Replica longines orologiLongines non è il più antico tra gli orologiai svizzeri, replica longines dolcevita orologi ma ha dimostrato che merita la sua statura. Fondata nel 1832, la sua eredità continua a essere considerata tra i marchi di orologi più affidabili, promettendo sia qualità nella fabbricazione che raffinatezza. Ciò è evidente nel La Grande Classique, fatto per uomini e donne. L'eleganza contenuta non è estranea al marchio, ma La Grande Classique offre questo con una custodia sottile.

  2. Das Kopieren der Uhr ist sehr schön, dies ist ein High-End-Stil.günstigste rolex uhren kaufen Ich mag diese Replik-Uhr wirklich. Der Shop-Service ist sehr gut, die Qualität der Uhr ist sehr gut, und der Preis ist sehr kostengünstig,Kaufen air-king uhren ich denke, es ist sehr billig, gute Qualität und niedriger Preis.

  3. replica louboutin italyFondato nel 1993, repliche donne appartamentigli iconici tacchi a suola rossa di Christian Louboutin sono ancora i tacchi più alla moda nel mondo della moda. Le impareggiabili vendite di Christian Louboutin rendono la tua collezione di scarpe ancora più equilibrata, in cui troverai allettanti tacchi Louboutin e scarpe Louboutin uniche delle dimensioni perfette.

  4. J'ai acheté cette copie de chaussures et de vêtements Gucci dans ce magasin,gucci pas cher la qualité est très bonne, elle a été rachetée plusieurs fois, elle est recommandée à des amis, ils louent tous la qualité des chaussures,gucci ceintures pas cher l'attitude du magasin est bonne.

  5. The store has been solving the problem for me and the service is very patient,cheap Soccer Jerseys this is a perfect shopping experience. When I received the jersey, I thought it was a good copy. I like this online borussia dortmund Jerseys The clothes here are simple and elegant. Very satisfied with this purchase.

  6. Copiar el reloj es muy bonito, este es un estilo de gama alta.Réplica de relojes Rolex Realmente me gusta este reloj réplica. El servicio de la tienda es muy bueno, la calidad del reloj es muy buena y el precio es muy rentable,Réplica de relojes cosmograph daytona creo que es muy barato, de buena calidad y bajo precio.

  7. Gli repliche orologi longines sono stati un punto di riferimento del repliche longines elegant settore da quando il loro marchio è iniziato nel 1832 a Saint-Imier. Oggi il marchio offre una vasta gamma di orologi da uomo e da donna. Gli orologi prodotti da Longines sono stati utilizzati nel corso della storia per tutto, dalle esplorazioni all'espressione dell'eleganza. Oltre ad essere famosi per i loro legami con il mondo delle corse e dei cavalli, gli orologi Longines sono riconosciuti a livello globale per la loro raffinatezza senza tempo e chic. abbiamo una varietà di orologi Longines in vendita, tutti nuovi, belli e garantiti per essere genuini. Ancora meglio, quando acquisti i nostri orologi Longines online, sai che stai ottenendo il miglior valore. Sfoglia la nostra selezione per trovare il miglior prezzo dell'orologio Longines su Internet.

  8. Copiar el reloj es muy bonito, este es un estilo de gama alta.Réplica de relojes Rolex Realmente me gusta este reloj réplica. El servicio de la tienda es muy bueno, la calidad del reloj es muy buena y el precio es muy rentable,Réplica de relojes sea dweller creo que es muy barato, de buena calidad y bajo precio.