01 November 2012

Plan cache reuse

by Jonathan Allen

6,786 0 Performance

This metric shows the percentage of cached plans that are being used more than once. If a plan is cached but never reused, there may be an opportunity to tune your server to work more effectively.

Install metric...

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

This metric shows the percentage of cached plans that are being used more than once. If a plan is cached but never reused, there may be an opportunity to tune your server to work more effectively by rewriting the TSQL or creating a parameterized plan.

Metric definition

Name

Plan cache reuse

Description

Reuse of plans that are stored in the plan cache is important to the efficiency of SQL Server. Having the cache full of plans that are not reused is wasteful, and it means that there may be lots of queries not using the cache efficiently. If this metric is low then investigate the number of plans in your cache that have a usecount of 1 and see if any can be parameterized.

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			DECLARE @single DECIMAL(18, 2)
DECLARE @reused DECIMAL(18, 2)
DECLARE @total DECIMAL(18, 2)
-- the above variables may need a precision greater than 18 on VLDB instances. This will incur a storage penalty in the RedgateMonitor database however.
SELECT @single = SUM(CASE ( usecounts )
                       WHEN 1 THEN 1
                       ELSE 0
                     END) * 1.0 ,
        @reused = SUM(CASE ( usecounts )
                        WHEN 1 THEN 0
                        ELSE 1
                      END) * 1.0 ,
        @total = COUNT(usecounts) * 1.0
    FROM sys.dm_exec_cached_plans;


SELECT ( @single / @total ) * 100.0;
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
				
						DECLARE @single DECIMAL(18, 2)
DECLARE @reused DECIMAL(18, 2)
DECLARE @total DECIMAL(18, 2)
-- the above variables may need a precision greater than 18 on VLDB instances. This will incur a storage penalty in the RedgateMonitor database however.
SELECT @single = SUM(CASE ( usecounts )
                       WHEN 1 THEN 1
                       ELSE 0
                     END) * 1.0 ,
        @reused = SUM(CASE ( usecounts )
                        WHEN 1 THEN 0
                        ELSE 1
                      END) * 1.0 ,
        @total = COUNT(usecounts) * 1.0
    FROM sys.dm_exec_cached_plans;
 
 
SELECT ( @single / @total ) * 100.0;

Instances to collect from

Select all

Databases to collect from

TempDB

Collection frequency

600

Note: If you suspect that there may be an issue with plan cache reuse, increase the collection frequency to every minute so you can analyze more data.

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

An alert is not required

6,786 Rate

Click to rate this post!

[Total: 0 Average: 0]

Plan cache reuse

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics