Queries Needing Params By Database
Overview
This is the instance rollup: which databases are filling the plan cache with literal-differing copies of the same statements, so you know where to open the database-level Top Queries Needing Params report.
When an application builds SQL by concatenating values instead of passing parameters, every distinct value produces a distinct plan. The statements are the same shape; the cache holds one copy per literal. That costs memory, costs compile time, and pushes plans you actually reuse out of the cache.
This page answers which database is doing it. The database-level report answers which statements – double-click a row and you are in it.
What this fixes
The old report was a pie of per-database counts taken from a TOP 1000 sample of the cache, counting any SELECT with a quote in it and fewer than five uses as “needing params”.
That is a guess, sampled, and blind to memory:
- A guess – a quote character in a statement is not evidence of a missing parameter.
- Sampled – a thousand rows off the top of a cache holding hundreds of thousands says very little about the rest.
- Blind to memory – the thing that makes cache bloat matter is how much memory it holds, and the old report never asked.
What replaced it is the same query_hash shape detection the database-level report now uses, rolled up per database over the whole cache, with the memory actually held.
Where to find it
An instance-level report. Right-click the server in the tree and open Instance Reports → Queries Needing Params by Database.
Reading the chart
One row per database, with two lanes:
| Lane | Scale | What it shows |
|---|---|---|
| Duplicate plans | Linear | How many redundant copies that database is responsible for. |
| Cache memory | Logarithmic | How much memory those copies hold. |
The two lanes are scaled differently on purpose. Plan counts across databases are usually within an order of magnitude of each other; the memory they hold is not, because a plan for a complex statement can be thousands of times the size of a simple one. A linear memory lane would draw every database except the worst as a hairline.
Each row’s chip is the copy count of that database’s most duplicated shape – reading worst 238 copies, its single worst offender. That is the number that tells you whether this is one runaway statement or broad, shallow duplication across many:
| Chip | Copies of the worst shape |
|---|---|
| Amber | 20 or more |
| Red | 100 or more |
In the top corner, a gauge gives the instance-wide answer in one line – “40% of this server’s 2.57 GB cache is duplicate plans”. That is the number worth quoting when arguing for the work.
Only ad hoc and prepared statements are counted. Procedures and triggers are already parameterised by definition, so counting them would inflate every database equally and tell you nothing. The subtitle line states this on the page.
A database with a large plan count but no chip has many shapes duplicated a little. A database with a modest count and a red chip has one statement duplicated hundreds of times – and that one is usually much easier to fix.
Reading the grid
| Column | What it is |
|---|---|
| Database | The database. |
| Query Shapes | How many distinct statement shapes have duplicate plans. |
| Duplicate Plans | How many cached plans those shapes account for. |
| Cache Memory | How much memory those plans hold. |
| Redundant | The memory that would be freed if each shape kept one plan – the actual waste. |
Redundant is the column to rank by. Cache Memory includes the one plan per shape you would keep anyway; Redundant is what parameterisation would actually give back.
Requirements
This is a live report reading the plan cache on the instance.
query_hash arrived in SQL Server 2008. Older servers get a message explaining why the page cannot be built rather than an empty grid.
The plan cache is volatile. It empties on restart, on memory pressure, and when someone clears it – so these numbers describe the cache as it stands now, not a historical total. A database that looks clean immediately after a restart may not stay that way.
How to read the report
- Rank by Redundant, not by plan count. That is the memory you would actually recover.
- Look at the chips. A red chip is one statement duplicated hundreds of times, which is the cheapest fix on the page.
- Compare Query Shapes against Duplicate Plans. Few shapes with many plans is one bad code path; many shapes with few plans each is a general parameterisation problem.
- Double-click the worst database to see the statements behind it.
- Check how long the instance has been up. A large number on a server that restarted an hour ago is worse than the same number after a month.
Common patterns
One database with a red chip and everything else clean. One code path building SQL by concatenation. Usually a single query to fix, and the biggest win available.
Many databases, all moderate, no chips. An application framework doing this everywhere. Broader work, and a candidate for OPTIMIZE FOR AD HOC WORKLOADS as a mitigation while the code is fixed.
High Cache Memory but low Redundant. The plans are large but not especially duplicated. Not a parameterisation problem.
A reporting database at the top. Ad hoc query tools generate literal-laden SQL by nature. Worth knowing, but may be expected rather than fixable.
Nothing at all. Either well-parameterised, or the cache was recently cleared. Check instance uptime before celebrating.
Related reports
| Report | Why you would go there |
|---|---|
| Top Queries Needing Params | The statements behind a database’s number – what the double-click opens. |
| Plan Cache | The cache as a whole, and what else is in it. |
| One Time Use Queries | The other half of ad hoc cache bloat: plans compiled and never reused. |
| CPU by Query | Compilation shows up as CPU. |
Frequently asked questions
How is this different from Top Queries Needing Params? That report is database-level and lists statements. This one is the instance rollup and lists databases, so you know which one to open.
Why are the two chart lanes scaled differently? Plan counts across databases are usually comparable; the memory those plans hold is not. A linear memory lane would draw everything except the worst database as a hairline.
What does the chip mean? The copy count of that database’s single most duplicated statement shape – amber at 20, red at 100. It separates one runaway statement from broad, shallow duplication.
Why does Redundant differ from Cache Memory? Cache Memory is everything those shapes hold. Redundant subtracts the one plan per shape you would keep anyway, so it is the memory parameterisation would actually free.
Why did the numbers change so much between refreshes? The plan cache is volatile. Restarts, memory pressure and cache clears all move it.
My server is older than SQL Server 2008. query_hash does not exist there, so the shape detection cannot run. The page says so rather than showing an empty grid.