When are non-parameterized, non-trivial, adhoc query plans reused

Question

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:

1. In which cases are non-parameterized, non-trivial, Adhoc query plans reused?
2. 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)

Answer 1

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.