Every batch causes a compile

We have a third party application that sends T-SQL statements in batches.

The database is hosted on a SQL Server 2016 Enterprise SP1 CU7, 16 cores and 256GB memory. Optimize for Ad-Hoc is enabled.

This is a dummy example of the queries that are being executed:

exec sp_executesql N'
IF @@TRANCOUNT = 0 SET TRANSACTION ISOLATION LEVEL SNAPSHOT

select field1, field2 from table1 where field1=@1
option(keep plan, keepfixed, loop join)

select field3, field4 from table2 where field3=@1
option(keep plan, keepfixed, loop join)', N'@1 nvarchar(6)',@1=N'test'

When I monitor the database and I look at batches/sec and compiles/sec, I notice they are always the same. Under heavy load, this can be 1000 batches/sec and 1000 compiles/sec. Under average load, there are 150 batches/sec.

I analyze the query cache for recently compiled plans:

SELECT TOP (1000) qs.creation_time
    , DatabaseName = DB_NAME(st.dbid)
    , qs.execution_count
    , st.text
    , qs.plan_handle
    , qs.sql_handle
    , qs.query_hash 
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS st
ORDER BY creation_time DESC;

When I run above query I only see 10-20 new query plans/sec.

It's like every sp_executesql call triggers a compile but the queryplan is not cached.

What can be the cause of batches/sec being equal to compiles/sec ?

It's like every sp_executesql call triggers a compile but the query plan is not cached.

SQL Server does not cache a query plan for batches containing only an sp_executesql call. Without a cached plan, a compilation occurs each time. This is by design, and expected.

SQL Server avoids caching batches with a low cost to compile. The details of what is and is not cached has changed many times over the years. See my answer to Trace flag 2861 and what a 'zero-cost' plan actually means for details.

In short, the probability of reuse (including specific parameter values) is small, and the cost of compiling the ad hoc text containing the sp_executesql call is very small. The inner parameterized batch produced by sp_executesql is of course cached and reused - this is the value of it. The extended stored procedure sp_executesql itself is also cached.

To be cached and reused, the sp_executesql statement would have to be part of a larger batch that is considered worth caching. For example:

-- Show compilation counter
SELECT
    DOPC.[object_name],
    DOPC.cntr_value
FROM sys.dm_os_performance_counters AS DOPC
WHERE
    DOPC.counter_name = N'SQL Compilations/sec'
GO
-- This is only here to make the batch worth caching
DECLARE @TC integer =
(
    SELECT TOP (1) @@TRANCOUNT 
    FROM master.dbo.spt_values AS SV
);

-- Example call we are testing
-- (use anything for the inner query, this example uses the Stack Overflow database
EXECUTE sys.sp_executesql 
    N'SELECT LT.Type FROM dbo.LinkTypes AS LT WHERE LT.Id = @id;', 
    N'@id int', 
    @id = 1;
GO
-- Show compilation counter again
SELECT
    DOPC.[object_name],
    DOPC.cntr_value
FROM sys.dm_os_performance_counters AS DOPC
WHERE
    DOPC.counter_name = N'SQL Compilations/sec'

Run that code several times. On the first time though, many compilations are reported as expected. On the second time, no compilations are reported, unless optimize for ad hoc workloads is enabled (so only a Compiled Plan Stub is cached). On the third time, no compilations are reported in any case, since any stub is promoted to a fully cached ad hoc plan.

Remove the DECLARE @TC statement to see that the sys.sp_executesql statement is never cached without it, regardless of the number of times it is executed.

View the associated plan cache entries with:

-- Show cached plans
SELECT
    DECP.refcounts,
    DECP.usecounts,
    DECP.size_in_bytes,
    DECP.cacheobjtype,
    DECP.objtype,
    DECP.plan_handle,
    DECP.parent_plan_handle,
    DEST.[text]
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE 
    DEST.[text] LIKE N'%sp_executesql%'
    AND DEST.[text] NOT LIKE N'%dm_exec_cached_plans%';

Related Q & A: Do triggers compile each time?

StackExchange Database Administrators Q#202086, answer score: 12