A Study in SQL Server Ad hoc Query Plans

SQL Version: 

Microsoft SQL Server 2019 (RTM-CU18)

(KB5017593) - 15.0.4261.1 (X64)

Database Size is 60TB

Application: Bigdata/Data Warehouse/Analytical Reports

CPU and Memory Configuration:

SELECT NAME FROM SYS.DATABASES DBS WHERE  NAME = 'tempdb';

Let’s look at the following example, where the same sql statement is

executed in three different ways to get the same results:

-- Ad hoc query
SELECT NAME FROM sys.databases dbs WHERE  NAME = 'tempdb'; 
GO
-- Prepared SQL statement
EXEC Sp_executesql N'SELECT NAME FROM sys.databases dbs WHERE  NAME = @db_name',
                   N'@db_name sysname',
                   N'tempdb' 
GO
-- Stored Procedure
IF Object_id('sp_test_adhoc_plans_cache', 'P') IS NOT NULL
  DROP PROCEDURE sp_test_adhoc_plans_cache
GO
CREATE PROCEDURE sp_test_adhoc_plans_cache
(@db_name SYSNAME = 'tempdb')
AS
    SELECT NAME FROM sys.databases dbs WHERE  NAME = @db_name;
GO
EXEC sp_test_adhoc_plans_cache
GO

SELECT cp.plan_handle,
       cp.objtype,
       cp.cacheobjtype,
       cp.size_in_bytes,
       cp.usecounts,
       qt.text,
       qs.query_hash,
       qs.query_plan_hash
FROM   sys.dm_exec_cached_plans cp
       inner join sys.dm_exec_query_stats qs on qs.plan_handle = cp.plan_handle
       CROSS apply sys.Dm_exec_sql_text(cp.plan_handle) qt
WHERE  qt.text LIKE '%SELECT NAME FROM sys.databases dbs WHERE  NAME =%'
       AND qt.text NOT LIKE 'SELECT cp.plan_handle%'

EXEC sp_configure 'optimize for ad

hoc workloads', 1;

reconfigure with override;

Re-run the same 3 queries again and look at the cache information:

Notice the size of the ad hoc plan, only 456 bytes, and the cacheobjtype is Compiled Plan Stub. That is right it is only a stub; it doesn’t have an execution plan and therefore technically there is no cached plan to reuse. However, if the same ad hoc query is run again in near future, optimizer will create a full execution plan for it that can be reused by the subsequent invocation of the same query. Let's verify that:

-- Ad hoc query

• The SQL Server optimizer will not cache the full execution plan for the Ad hoc queries at first.  In other words, this setting only affects queries the optimizer deems Ad hoc.

• Optimizer will however cache the plan stub, along with the query and plan hash values so that it will know if the same query is re-executed in which case it will remove the stub and cache the complete execution plan

• Ad hoc query plans
• Plan is not a stub
• With use count less than or equal to 2
• Have not been used by a query in more than 5 hours

;with cte_cached_adhoc_plans as
(
SELECT plan_handle,
       MAX(last_execution_time) last_execution_time,
       SUM(execution_count)     execution_count
FROM   sys.dm_exec_query_stats
GROUP  BY plan_handle 
)
SELECTCOUNT(*) [Plan Count],
SUM(CAST(size_in_bytes AS BIGINT)) / 
              1024 / 1024 [Size MB],
MAX(cte_cached_adhoc_plans.execution_count) [Max Exec Count],
SUM(cte_cached_adhoc_plans.execution_count) [Total Exec Count]
FROM       sys.dm_exec_cached_plans cp 
INNER JOIN cte_cached_adhoc_plans ON cte_cached_adhoc_plans.plan_handle = cp.plan_handle 
WHERE cp.objtype = 'Adhoc'
  AND cte_cached_adhoc_plans.last_execution_time < DATEADD(HOUR, -5, GETDATE())
  AND cp.objtype != 'Compiled Plan Stub'
  AND cte_cached_adhoc_plans.execution_count <= 2

;with cte_cached_adhoc_plans as
(
SELECT plan_handle,
       MAX(last_execution_time) last_execution_time,
       SUM(execution_count)     execution_count
FROM   sys.dm_exec_query_stats
GROUP  BY plan_handle 
)
SELECTTOP 1000
[Remove Cached Plan] = 
'DBCC FREEPROCCACHE (0x' + convert(varchar(max), cte_cached_adhoc_plans.plan_handle, 2) + ');' 
FROM       sys.dm_exec_cached_plans cp 
INNER JOIN cte_cached_adhoc_plans ON cte_cached_adhoc_plans.plan_handle = cp.plan_handle 
WHERE cp.objtype = 'Adhoc'
  AND cte_cached_adhoc_plans.last_execution_time < DATEADD(HOUR, -5, GETDATE())
  AND cp.objtype != 'Compiled Plan Stub'
  AND cte_cached_adhoc_plans.execution_count <= 2