patternsqlMinor
When are non-parameterized, non-trivial, adhoc query plans reused
Viewed 0 times
nonarequerytrivialwhenreusedadhocparameterizedplans
Problem
I'm currently investigating an application that seems to generate 99% adhoc query plans against the database it is querying. I can verify this by running the following statement to retrieve a summary of objects in the query plan cache:
Sorry couldn't enter code into SE editor, hence the screenshot
Reference: Plan cache and optimizing for adhoc workloads (SQLSkills.com / K. Tripp) with slight modifications
The results of the above query are as follows:
Out of the 158'997 adhoc queries in the plan cache, 126'087 queries have only been executed once.
On further examination of the adhoc queries I have found that some queries are even generated multiple times. I examined the plan cache with the following query to retrieve execution plans that were identical:
```
SELECT SUM(cplan.usecounts) AS [Unique Same Single Plans]
Sorry couldn't enter code into SE editor, hence the screenshot
Reference: Plan cache and optimizing for adhoc workloads (SQLSkills.com / K. Tripp) with slight modifications
The results of the above query are as follows:
CacheType Total Plans Total MBs Avg Use Count Total MBs - USE Count 1 Total Plans - USE Count 1
-------------------- -------------------- --------------------------------------- ------------- --------------------------------------- -------------------------
Adhoc 158997 5749.520042 2 2936.355979 126087
Prepared 1028 97.875000 695 46.187500 576
Proc 90 69.523437 39659 21.187500 21
View 522 75.921875 99 0.453125 3
Rule 4 0.093750 22 0.000000 0
Trigger 1 0.070312 12 0.000000 0Out of the 158'997 adhoc queries in the plan cache, 126'087 queries have only been executed once.
On further examination of the adhoc queries I have found that some queries are even generated multiple times. I examined the plan cache with the following query to retrieve execution plans that were identical:
```
SELECT SUM(cplan.usecounts) AS [Unique Same Single Plans]
Solution
turning on optimize for ad hoc workloads will result in slight reduction in the size of the query plans in the cache
...
Out of the 158'997 adhoc queries in the plan cache, 126'087 have only been executed once.
I wouldn't call removing 79% of the AdHoc plans a slight reduction.
In which cases are non-parameterized, non-trivial, Adhoc query plans reused?
When the exact same query, with the same session settings is run after the plan is in the cache by a client connected to the same database.
So,
Why are there multiple cached query plans for identical statements?
Typically it's sessions with different settings that affect query behavior. Any text differences in the query, including whitespace, can cause this. The user's default schema can cause this, as object name resolution differs. Also, two identical queries submitted at nearly the same time may get independently optimized and cached.
...
Out of the 158'997 adhoc queries in the plan cache, 126'087 have only been executed once.
I wouldn't call removing 79% of the AdHoc plans a slight reduction.
In which cases are non-parameterized, non-trivial, Adhoc query plans reused?
When the exact same query, with the same session settings is run after the plan is in the cache by a client connected to the same database.
So,
Why are there multiple cached query plans for identical statements?
Typically it's sessions with different settings that affect query behavior. Any text differences in the query, including whitespace, can cause this. The user's default schema can cause this, as object name resolution differs. Also, two identical queries submitted at nearly the same time may get independently optimized and cached.
Context
StackExchange Database Administrators Q#252575, answer score: 3
Revisions (0)
No revisions yet.