One Time Use Queries
Overview
The One Time Use Queries report shows the plans compiled once and never executed again, and how much cache memory they hold.
The important part is how they are grouped. Single-use plans are grouped by query_hash into query shapes, so the same statement cached repeatedly with different literal values reads as one finding with a copy count rather than as many unrelated rows.
That grouping is the whole point, because it names the fix:
Twenty-three copies of the same statement differing only by literal values is the case for parameterizing it.
Each shape is drawn as a bar split into one segment per cached copy, so the copy count is visible in the shape of the bar rather than only in a number.
What this fixes
- The pie never answered anything. Below the first dozen slices it was sub-degree slivers labelled with 47 characters of SQL, and the colours were dealt by row order, so every refresh reshuffled them – the same failure the Plan Cache pie had.
- The finding the page exists for was invisible. Twenty-three copies of one statement rendered as twenty-three unrelated slices.
- Right after a service restart every plan is single-use, and the report presented that as a crisis.
The restart caveat
Immediately after a restart, every plan in the cache has been used once, because nothing has had a chance to be reused yet. That is not a finding, and the report says so rather than reporting an emergency.
Give the cache time to become representative before drawing conclusions.
Where to find it
A database-level report. Select a database, then open One Time Use Queries. The page title reads One Time Use Queries for <database name>.
Reading the grid
| Column | What it is |
|---|---|
| Copies | How many cached plans share this query shape. This is the number that names the fix. |
| Size | Memory held by this shape, or by this plan in All plans mode. |
| Type | Adhoc, Prepared, and so on. |
| Query | The statement text. |
Clicking a shape in the chart filters the grid to its copies. Double-clicking opens the query in the One Time Use Advisor.
The toolbar
| Group | Buttons |
|---|---|
| Top N | Top 25 · Top 100 · Top 200 |
| Grouping | By query shape · All plans |
Refresh |
By query shape is the default and the useful one. All plans shows the individual cached plans without grouping, and regroups without re-querying.
How to read the report
- Check how long the cache has been up. A fresh cache makes every plan single-use.
- Look at the copy counts. A shape with dozens of copies is un-parameterised SQL, and the count is the evidence.
- Read the query text. If the only difference between copies is a literal value, that statement should be parameterised.
- Look at the memory each shape holds. That is what parameterising it returns.
- Switch to All plans only when you need an individual plan rather than the pattern.
Common patterns
One shape with fifty copies. The clearest finding the report produces. One statement, built by string concatenation, cached fifty times.
Many shapes with one copy each. Genuinely ad hoc activity – often a reporting tool or someone working in SSMS. Less actionable, and less alarming.
Large total memory across few shapes. Big statements rather than many of them. Worth looking at the query text.
Everything single-use just after a restart. Expected. Wait.
Related reports
| Report | Why you would go there |
|---|---|
| Plan Cache | The whole cache, with this as its headline number. |
| CPU by Query | Whether the ad hoc statements are also expensive. |
| CPU by Database | Where its Unattributed tile comes from – the same SQL. |
Frequently asked questions
What is a query shape? A group of cached plans sharing a query_hash – the same statement structure with different literal values. It is the unit the fix applies to.
Why group instead of listing every plan? Because the finding is the repetition. Twenty-three unrelated rows say nothing; one row saying “23 copies” says parameterise this.
Is this the same as the Plan Cache report? Related. Plan Cache shows the whole cache and reports single-use memory as its headline; this page is only the single-use plans, grouped so the cause is visible.
Everything is single-use. Is that bad? Check the uptime first. Right after a restart it is expected and means nothing.
How do I fix it? Parameterise the application so the same statement text is reused. optimize for ad hoc workloads reduces the memory cost but does not remove the compilations.