Sql-server – When are non-parameterized, non-trivial, adhoc query plans reused

execution-plansql serversql-server-2016

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:

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                                0

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],
       qtext.text
FROM   sys.dm_exec_cached_plans AS cplan
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
             CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
       JOIN sys.databases AS sdb
            ON  sdb.database_id = qplan.dbid
WHERE  1 = 1
       AND cplan.objtype = 'Adhoc'   -- <-- only Adhoc plans
       AND sdb.name = 'DATABASENAME' -- <-- for a certain database
       AND cplan.usecounts = 1       -- <-- with a usecounts of 1
GROUP BY
       qtext.text having sum(cplan.usecounts) > 1
ORDER BY
       1 DESC --,cplan.objtype, cplan.usecounts

^{Reference: Can't remember. Let me know if it was originally yours and I'll attribute it.}

This gives me a list of adhoc queries that have a query plan that is identical to an existing identical query plan and the sum of unique identical query plans in the plan cache.

As you can see from the redacted GUIDs there are a lot of unique adhoc query plans that have been created multiple times.

To prove I'm going in the right direction I took a statement from above which had a unique count of 3 and used the statement as a filter in my plan cache summary statement to retrieve the statements and query plans:

SELECT cplan.usecounts,
       qtext.text,
       qplan.query_plan
FROM   sys.dm_exec_cached_plans AS cplan
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
       CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
       JOIN sys.databases AS sdb
            ON  sdb.database_id = qplan.dbid
WHERE  1 = 1
       AND cplan.objtype = 'Adhoc'
       AND sdb.name = 'DATABASENAME'
       AND qtext.text = 
           'SELECT description,id,name,osguid,profil FROM omitted WHERE osguid IN (SELECT osgroupguid FROM omitted WHERE osuserguid=''81C4B8_REMOVED_SOME_9DD2'')'
ORDER BY
       1 DESC

^{Reference: Can't remember. Let me know if it was originally yours and I'll attribute it.}

This give me a list of unique adhoc queries which have been created and stored in the plan cache:

Now the numbers in the above screenshot show that one query has already been reused again, because it has a count of 3. However, all the queries are identical.

Now from what I have read so far, I assume that:

adhoc queries are queries that have been passed along to the SQL Server Query Optimizer for the first time in their (possibly short) life
statements that have no parameters are considered to be unique and will result in an Adhoc entry being created in the plan cache
adhoc queries can be trivial which results in individual query plans being created for each statement even though they are identical

I am equally aware that:

turning on optimize for ad hoc workloads will result in slight reduction in the size of the query plans in the cache for adhoc plans that are used only once
running ALTER DATABASE [DATABASENAME] SET PARAMETERIZATION FORCED could be a good idea in my case, but that…
- there are restrictions (see BrentOzar's articles)
- parameterization in the program would be better

Questions

After reading through all the articles and some related questions which popped up while typing this question I have the following two questions:

In which cases are non-parameterized, non-trivial, Adhoc query plans reused?
Why are there multiple cached query plans for identical statements?

I realize that my questions are contradicting, because of the fact that non-parameterized query plans are considered to be unique, but why are some non-parameterized adhoc query plans nevertheless being reused again?

In response to @DenisRubashikin 's comment:

Save plans for 'identical' queries in XML format and compare the files, I think there could be some difference (in set options, for instance) – Denis Rubashkin 29 mins ago

The SET option are identical. The only differences in the whole plans are on the second line after the <StatementSetOptions> section in CompileTime and CompileCPU. I've pasted both relevant portions below:

QueryPlan1.xml

      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
      <QueryPlan CachedPlanSize="32" CompileTime="4" CompileCPU="4" CompileMemory="472">

QueryPlan2.xml

      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
      <QueryPlan CachedPlanSize="32" CompileTime="3" CompileCPU="3" CompileMemory="472">

No other differences found.

Reference Material used to curate this question:

Plan cache and optimizing for adhoc workloads (SQLSkills.com)
Query Processing Architecture Guide (Microsoft SQL Docs)
ALTER DATABASE SET options (Transact-SQL) (Microsoft SQL Docs)
Why Multiple Plans for One Query Are Bad (BrentOzar.com)
Can Forced Parameterization Go Wrong? (BrentOzar.com)
Blitz Result: Forced Parameterization (Brentoar.com)

Best Answer

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.

Related Solutions

Sql-server – Why would recompile query hint result in different plan for same adhoc statement after freeproccache

The query you posted contains variables.

SQL Server doesn't do variable sniffing so without OPTION (RECOMPILE) it will compile a general plan as it would for OPTIMIZE FOR UNKNOWN.

I don't really follow your question though. At one point you seem to be saying that the version without the hint is "much much faster" and then later you say the version with the hint is "much better". So which one is it?

Both are explicable however. If you find the version with the hint is better than this is because SQL Server can use statistics to estimate the number of rows that will be matched by the date predicate and choose an appropriate plan for that case.

If the version without the hint is better the statistics themselves may need updating. Perhaps when they were last updated there were few or no rows meeting that predicate and so SQL Server massively underestimates the number of rows that will be returned. See Statistics, row estimations and the ascending date column for more about this potential issue.

Sql-server – How bad is query plan pollution (too many equivalent plans in cache)

"How bad is it?" depends on the degree to which you are suffering now or could suffer with increased workload in the future.

One major point of suffering with plan cache pollution could be too many single use plans bloating your plan cache leading to inefficient cache usage.

Another point of suffering could be high compilations/second - so in an environment with a heavy workload and a lot of activity, there is a cost associated with compiling over and over.

You can see the impact of compilations/sec in perfmon (SQL Server Statistics:Compilations/sec). This can look like CPU pressure. To your performance/applications, this can look like increased query duration waiting for needless compiles each time it runs.

You can see the impact to the plan cache from the memory bloat by this query borrowed from Glenn Berry's Diagnostic scripts. How big is your SQLCP plan cache?

SELECT TOP(10) [type] AS [Memory Clerk Type], 
       SUM(pages_kb)/1024 AS [Memory Usage (MB)] 
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]  
ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);

Also the query that was used in the question to identify the number of plans helps as well.

Is This Ever a Good Thing?

There are some cases where this could be good, but the situation is rare. Basically if you were suffering from parameter sniffing gone bad (nutshell: if the data can vary widely from execution to execution based on parameters, one compilation for one set of parameters ideal may yield an excellent query plan for that one query but poor for others.). My guess is that you likely wouldn't be dealing with that as bad as the implications from poor plan reuse.

What Can You Do About It?

Optimize For Ad Hoc Workloads can certainly help with the memory implications since only a stub of the plan is stored in cache at first execution, and the full plan isn't stored until it is executed a second time with the same plan.
Forced Parameterization could help here also. It can sometimes force parameterization to happen and help solve both the issue of cache bloat and the cost of having to recompile.
Fix The Queries Ideally, you shouldn't have to resort to these options, but instead can be more strict in your database development, encourage plan reuse, consider stored procedures for all of their benefits, and attempt to head off the problem that way. The ways to help fix this through forced parameterization or optimize for ad hoc are good to help, but the best solution is always aimed at the root cause.

There is an excellent resource here that talks about some of the dangers of plan cache pollution and some things you can do. I'd recommend a read here. It is written for SQL Server 2012, but the concepts and solutions apply.