Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Analyzing SQL Server Plan Cache Performance Using DMVs


By:   |   Read Comments (2)   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

So far I see you've covered persisting SQL Server performance counter values obtained from sys.dm_os_performance_counters and querying the results for buffer pool information. What about the other aspect of SQL Server memory: plan cache?

Solution

If you've been following this tip series you've seen how to collect SQL Server performance counters from within Transact-SQL via querying sys.dm_os_performance_counters without ever needing to launch Performance Monitor. You've also been exposed to how you can persist this information to a physical table for historical querying. In the last tip I also provided you with queries you can run to return historical information about a range of metrics associated with the Buffer Pool. Now I want to turn your attention to the Plan Cache and what information we can look at coming from our counter repository as is associated with that aspect of SQL memory.

The Plan cache is the component of SQL memory that stores query plans for re-use. When a query is issued against SQL, the optimizer will attempt to re-use a cached plan if the traits of the query permit - but can only do so if that plan resides in cache, otherwise it needs to compile a new plan. This is not to say that every plan that is cached is re-used. Changes in schema, a query running parallel that may have run serially before, or changes in parameters may require a new plan to be compiled even if a similar plan exists in cache. Plan compilations are expensive though - if plans are flushed from cache only to be recompiled afterwards because of size constraints it is doubly-so.

Plan Cache counters we are tracking in this multi-tip exercise are presented below:

The Plan cache is the component of SQL memory that stores query plans for re-use

Plan Cache Queries

The following queries cover two areas of interest when it comes to Plan Cache: Cache Hit Ratio - the percentage of plans that are satisfied from the Plan Cache versus being compiled as new or recompiled due to changes in schema or some other reason and Plan Cache size. Just as in the previous tip in this series we will look at this in two different manners: as a detail query returning individual records for each data collection period, and as an aggregate for a supplied time range. For the sake of this article I am using a one hour timeframe for all detail queries and a 24-hour timeframe for all aggregate queries.

Plan Cache hit ratio measure the percentage of plans that are satisfied from the Plan Cache. It requires two counters for computation: Plan Cache / Cache Hit Ratio (which is the number of plans that were satisfied by the Plan Cache) and Cache Hit Ratio Base (which measure the total amount of plans that have been requested). Dividing the former by the later gives us the ratio of success. The closer this number is to 100% the better.

-->Plan Cache Detail<-----------------------------------------
DECLARE @start DATETIME, @end DATETIME
SELECT
@start = DATEADD(hh,-1,GETDATE())
SELECT @end = GETDATE()

SELECT Hits.[instance_name],
CAST((Hits.[cntr_value] * 1.0 / Total.[cntr_value]) * 100.0 AS DECIMAL(5,2)) AS [plan_cache_hit_ratio],
[Pages].[cntr_value] AS [cache_pages],
(
[Pages].[cntr_value] * 8 / 1024) AS [cache_in_mb],
Hits.[date_stamp]
FROM
(
SELECT [instance_name], [cntr_value], [date_stamp]
FROM iDBA.[MetaBOT].[dm_os_performance_counters]
WHERE [counter_name] = 'Cache Hit Ratio'
AND [date_stamp] BETWEEN @start AND @end

) Hits

INNER JOIN

(
SELECT [instance_name], [cntr_value], [date_stamp]
FROM iDBA.[MetaBOT].[dm_os_performance_counters]
WHERE [counter_name] = 'Cache Hit Ratio Base'
AND [date_stamp] BETWEEN @start AND @end
) Total
ON Hits.[date_stamp] = Total.[date_stamp]
AND [Hits].[instance_name] = [Total].[instance_name]

INNER JOIN

(
SELECT [instance_name], [cntr_value], [date_stamp]
FROM iDBA.[MetaBOT].[dm_os_performance_counters]
WHERE [object_name] LIKE '%:Plan Cache%'
AND [counter_name] = 'Cache Pages'
AND [date_stamp] BETWEEN @start AND @end
) Pages
ON Hits.[date_stamp] = [Pages].[date_stamp]
AND Hits.[instance_name] = [Pages].[instance_name]
ORDER BY Hits.[date_stamp] DESC, Hits.[instance_name];

Plan Cache counter values are divided into 6 "instances"

Looking at the results you'll see that the Plan Cache counter values are divided into 6 "instances": Bound Trees, Extended Stored Procedures, Object Plans, SQL Plans, Temporary Tables & Table Variables, and _Total. Each are described below: Thank you Jonathan Kehayias (blog|twitter) for helping me flesh these descriptions out better than I originally did in my first draft!

  • Bound Trees - associated with objects that are schema-bound such as views, user tables, check constraints, and computed columns
  • Extended Stored Procedures - plans associated with system and extended stored procedures
  • Object Plans - plans that are generated through the creation of programmability objects within the databases on an instance, such as stored procedures, user-defined functions, and triggers
  • SQL Plans - compiled SQL plans; both ad-hoc and prepared
  • Temporary Tables & Table Variables - are just that, plans associated with temporary table and table variables
  • _Total - completely self-explanatory; the sum of all-of-the-above

We can also aggregate these results similarly to how we did in our previous tip when examining Buffer Pool:

-->Plan Cache Summary<-----------------------------------------
DECLARE @start DATETIME, @end DATETIME
SELECT
@start = DATEADD(hh,-24,GETDATE())
SELECT @end = GETDATE()

SELECT
'Plan Cache' AS [counter],
Hits.[instance_name],
CAST(MIN((Hits.[cntr_value] * 1.0 / Total.[cntr_value]) * 100.0) AS DECIMAL(5,2)) AS [hit_ratio_MIN],
MIN(Pages.[cntr_value]) AS [page_MIN],
(
MIN(Pages.[cntr_value]) * 8/1024) AS [mb_MIN],
CAST(AVG((Hits.[cntr_value] * 1.0 / Total.[cntr_value]) * 100.0) AS DECIMAL(5,2)) AS [hit_ratio_AVG],
AVG(Pages.[cntr_value]) AS [page_AVG],
(
AVG(Pages.[cntr_value]) * 8/1024) AS [mb_AVG],
CAST(MAX((Hits.[cntr_value] * 1.0 / Total.[cntr_value]) * 100.0) AS DECIMAL(5,2)) AS [hit_ratio_MAX],
MAX(Pages.[cntr_value]) AS [page_MAX],
(
MAX(Pages.[cntr_value]) * 8/1024) AS [mb_MAX]
FROM
(
SELECT [instance_name], [cntr_value], [date_stamp], [counter_name]
FROM iDBA.[MetaBOT].[dm_os_performance_counters]
WHERE [counter_name] = 'Cache Hit Ratio'
AND [date_stamp] BETWEEN @start AND @end

) Hits

INNER JOIN

(
SELECT [instance_name], [cntr_value], [date_stamp]
FROM iDBA.[MetaBOT].[dm_os_performance_counters]
WHERE [counter_name] = 'Cache Hit Ratio Base'
AND [date_stamp] BETWEEN @start AND @end
) Total
ON Hits.[date_stamp] = Total.[date_stamp]
AND [Hits].[instance_name] = [Total].[instance_name]

INNER JOIN

(
SELECT [instance_name], [cntr_value], [date_stamp]
FROM iDBA.[MetaBOT].[dm_os_performance_counters]
WHERE [object_name] LIKE '%:Plan Cache%'
AND [counter_name] = 'Cache Pages'
AND [date_stamp] BETWEEN @start AND @end
) Pages
ON Hits.[date_stamp] = [Pages].[date_stamp]
AND Hits.[instance_name] = [Pages].[instance_name]
GROUP BY Hits.[counter_name], Hits.[instance_name];

plan cache _total hit_ratio_min

plan cache _total hit ratio _avg

plan cache _total hit_ratio_max

In my next tip in this continuing series on persisted performance counters we will look at the how to add counters to our watched counter list across all SQL instances in your network.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, June 06, 2013 - 11:24:22 PM - lee Back To Top

Thanks


Tuesday, February 19, 2013 - 1:46:47 PM - Madhavan Back To Top

<p>Dear Sir,

I am getting an error in the below query. Is the date_stamp a valid field in dm_os_performance_counters? Let me know if I am missing something here. This TIP is nice . I just raised this question to make sure  that my understanding is complete.

I provided the section below for your quick verification.

SELECT [instance_name], [cntr_value], [counter_name],[date_stamp]
FROM sys.[dm_os_performance_counters]
WHERE [counter_name] = 'Cache Hit Ratio'

Thanks Regards

Madhavan

</p>


Learn more about SQL Server tools