Plan cache hit ratio

A high plan cache hit ratio does not guarantee that all queries are using the cache as well as they might. Knowing the percentage of queries that are satisfied from cache helps you to better understand the plan cache hit ratio value.

Install metric...

Metrics install automatically if you have SQL Monitor installed.

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

This metric needs to be considered alongside the Plan cache reuse metric that looks at the spread of plan reuse through your cache.

A high plan cache hit ratio does not guarantee that all queries are using the cache as well as they might. It shows the percentage of queries that are being linked to a cached plan rather than generating a new one. Knowing the percentage of queries that are satisfied from cache helps you to better understand the plan cache hit ratio value.

To appreciate the difference between plan cache reuse and plan cache hit ratio, consider the difference between 10 people each asking you the same question, and 10 people each asking you a different question. Once you have got the answer in the first scenario you can answer 9 people from memory. In the second, you can’t do this, and may have to rely on reference materials before you can answer each question correctly.

Plan cache hit ratio, 5.0 out of 5 based on 2 ratings

Metric definition

Name

Plan cache hit ratio

Description

This metric measures how much the plan cache is being used. A high percentage here means that your SQL Server is not building a new plan for every query it is executing so is working effectively and efficiently. A low percentage here means that for some reason, the SQL Server is doing more work than it needs to. This metric needs to be considered alongside the Plan cache reuse metric which looks at the spread of plan reuse through your cache.

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

TempDB

Collection frequency

600

Note: The frequency for this metric should match the collection frequency of the Plan cache reuse metric.

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