Plan Cache
Overview
The Plan Cache report shows what is in the cache for a database, and how much of it is worth the memory it holds.
The one thing a plan cache report exists to say is how much memory is held by plans compiled once and never executed again. That is the headline here, with the count of ad hoc shapes that differ only by literal values beside it.
Single-use plans are memory spent compiling something nobody will ask for twice. On a database whose application builds SQL by concatenating literals, they can be most of the cache.
What this fixes
The old report was a pie chart of up to five hundred slices weighted by plan size:
- The pie never answered anything. Below the first dozen slices it was sub-degree slivers nobody could read or click, and the colours were dealt by row order, so every refresh reshuffled them.
- The headline number had to be eyeballed out of five hundred Use Count cells.
- The data source was
sys.syscacheobjects, a SQL Server 2000 compatibility view, and the query declared a@dbNameparameter it never used. The DMVs replace it. - Double-clicking a row passed the connection string where the query text goes, so the dialog opened showing the datasource string instead of the plan’s SQL.
Where to find it
A database-level report. Select a database in the tree, then open Plan Cache. The page title reads Plan Cache for <database name>.
The two views
Switched on the toolbar.
By Type
One bar per cache object type, split into reused and single-use memory. Clicking a bar filters the grid to that type.
This is the view that answers “where is my cache memory going, and how much of it is wasted”. The split is the whole point – a large bar that is mostly reused is a working cache; a large bar that is mostly single-use is not.
By Query
The biggest plans one by one, size against times-used. Double-click opens the query.
This is the view for finding the individual offenders once By Type has told you which category to look in.
The restart notice
A notice shows while the cache is still filling after a restart. A plan cache that came up ten minutes ago has not had time to become representative, and an unusually clean report is not the same as a healthy one.
Reading the grid
| Column | What it is |
|---|---|
| Uses | Execution count. 1 is the number that matters – compiled once, never reused. |
| Refs | Reference count. |
| Cache Type | The cache store. |
| Object Type | Adhoc, Prepared, Proc, and so on. |
| Size | Memory this plan holds. |
| Object | Object name where there is one. |
| Query | The statement text. |
The numeric columns keep raw values behind the formatted text so sorting works – a list view sorts as text and would read 1.2M as the number 1 and 96.4 MB as 96.
Double-clicking a row opens the plan’s SQL, which is what it should always have done.
The toolbar
| Button | What it does |
|---|---|
By Type · By Query |
The two chart shapes. |
Top 25 · Top 100 · Top 500 |
How many rows. |
Refresh |
Reload now. |
How to read the report
- Read the headline. How much memory is held by plans used exactly once?
- Look at By Type. Which cache object type holds it, and how much of that bar is single-use?
- If Adhoc dominates the single-use memory, the application is sending un-parameterised SQL. That is the finding.
- Switch to By Query to see the individual plans.
- Sort by Uses ascending to bring the never-reused plans to the top.
- Check the count of ad hoc shapes differing only by literals. A high number is the signature of string-concatenated SQL.
Common patterns
Most of the cache is single-use Adhoc plans. The application is building SQL with literal values instead of parameters. Two fixes: parameterise the application, and consider optimize for ad hoc workloads at the instance level as mitigation.
A handful of enormous plans. Usually very large statements, or plans for queries against many tables. Worth looking at in By Query.
High reuse across the board. A healthy cache. Nothing to do.
A nearly empty cache on a busy database. Memory pressure is evicting plans as fast as they are compiled, or the instance restarted recently. The notice tells you which.
Ad hoc plans here plus ad hoc statements dominating CPU by Query. Same root cause, seen from two directions. Fix the parameterisation and both improve.
Messages you may see
Nothing cached:
There are no plans cached for this database yet. Run a few queries against it and press Refresh – plans appear as soon as SQL Server compiles them.
Timed out:
The plan cache lookup did not finish in time. A very large plan cache can take a while to read; try the Refresh button.
Related reports
| Report | Why you would go there |
|---|---|
| CPU by Query | The same statements, ranked by what they cost. |
| CPU by Database | Where its Unattributed tile comes from – the same ad hoc SQL. |
| Missing Indexes | A very large number of suggestion groups has the same root cause. |
| Memory | How much memory the instance has to spare in the first place. |
Frequently asked questions
What is a single-use plan? A plan compiled for a statement that has been executed exactly once. The compilation cost was paid and the memory is held, and nothing will ever reuse it.
Why do I have thousands of them? Almost always an application concatenating literal values into SQL instead of using parameters, so every call produces a different statement text and therefore a different plan.
Does optimize for ad hoc workloads fix this? It mitigates it – SQL Server stores a small stub on first execution rather than a full plan. It does not fix the application, and it does not help the compilation cost.
Why is the report per database? Because that is the scope of the page. The cache itself is instance-wide; this shows the plans attributed to this database.
Why did double-clicking a row used to show my connection string? A bug in the old report – it passed the connection string where the query text belonged. It opens the SQL now.