Queries Needing Params
Overview
This report finds the same statement cached over and over, differing only by literal values – which is the case for parameterizing it.
It is the most direct statement of the ad hoc SQL problem in the product. Plan Cache reports single-use memory as a headline; One Time Use Queries groups single-use plans by shape; this page goes straight to the statements whose duplication is worth fixing, and puts the memory that duplication holds next to each one.
What this fixes
The old version was a half-pie of up to thirty rainbow slices, each labelled with the first 47 characters of a query, over a Row/Count/Query grid:
- The pie angles could not answer “how much worse is #1 than #5”.
- The memory those duplicates hold was never shown – so there was no way to size the problem.
- The duplicate detection was a six-CTE recursive string scrubber – nineteen levels of quote stripping and digit replacement – over only the first 1,000 cached plans, SELECTs only.
That last one matters most. A hand-rolled text scrubber over a truncated, filtered sample was never going to find the duplication reliably. Grouping is done properly now.
Where to find it
A database-level report. Select a database, then open Top Queries Needing Params. The page title reads Top Queries Needing Params for <database name>.
Reading the grid
| Column | What it is |
|---|---|
| Copies | How many cached plans are this same statement with different literals. This is the finding. |
| Cache Memory | What those copies hold between them. |
| Redundant | The memory you would get back by parameterizing – everything beyond the first copy. |
| Query | The statement text. |
Redundant is the number to quote when arguing for the change. It is the waste, not the total.
The toolbar
| Button | What it does |
|---|---|
Top 25 · Top 100 · Top 200 |
How many statements. |
Refresh |
Reload now. |
How to read the report
- Look at Copies. A statement with dozens of copies is un-parameterised SQL, and the count is the evidence.
- Read Redundant for what parameterizing returns.
- Read the query text. Confirm the copies differ only by literal values.
- Take it to the application team. This is not fixable in the database – the statement text has to stop changing.
- Consider
optimize for ad hoc workloadsas mitigation while that happens.
Common patterns
One statement with a hundred copies. A WHERE id = 12345 built by string concatenation. The single clearest case for parameterizing.
Several statements from the same code path. One module building all its SQL by concatenation. Fixing the module fixes all of them.
Copies with an IN list of varying length. Genuinely harder to parameterise – each list length is a different statement. Table-valued parameters are the usual answer.
Nothing here but Plan Cache shows heavy single-use memory. The single-use plans are genuinely distinct statements rather than repeats. Different problem, and less fixable.
Related reports
| Report | Why you would go there |
|---|---|
| One Time Use Queries | Single-use plans grouped by shape. |
| Plan Cache | The whole cache and what it holds. |
| CPU by Query | Whether the ad hoc statements also cost CPU. |
Frequently asked questions
How is this different from One Time Use Queries? That page starts from single-use plans and groups them. This one starts from duplication and quantifies the waste. They overlap heavily and often name the same statements.
Why is fixing this worth doing? Each copy cost a compilation and holds memory. Parameterizing removes both, and usually improves plan reuse across the board.
Can I fix this without changing the application? Only partially. optimize for ad hoc workloads reduces the memory held; forced parameterization can help but has real risks around plan choice. The durable fix is in the application.
Why did the old report miss so much? It scanned only the first 1,000 cached plans, only SELECTs, using a recursive string scrubber to guess at duplication.