10

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 ?

12

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?

11

You can approximate what you see in Performance Monitor and Activity Monitor for SQL Compilations/sec and Batch Requests/sec, while running some batches in separate query window as a test, as detailed below.

Query Window 1:

DECLARE @t1 datetime;
DECLARE @t2 datetime;
DECLARE @CompVal1 int;
DECLARE @CompVal2 int;
DECLARE @ReCompVal1 int;
DECLARE @ReCompVal2 int;
DECLARE @BatchVal1 int;
DECLARE @BatchVal2 int;
DECLARE @ElapsedMS decimal(10,2);

SELECT @t1 = GETDATE()
    , @CompVal1 = (
        SELECT spi.cntr_value
        FROM sys.sysperfinfo spi
        WHERE spi.counter_name = 'SQL Compilations/sec                                                                                                            '
        )
    , @ReCompVal1 = (
        SELECT spi.cntr_value
        FROM sys.sysperfinfo spi
        WHERE spi.counter_name = 'SQL Re-Compilations/sec                                                                                                         '
        )
    , @BatchVal1 = (
        SELECT spi.cntr_value
        FROM sys.sysperfinfo spi
        WHERE spi.counter_name = 'Batch Requests/sec                                                                                                              '
        );

WAITFOR DELAY '00:00:10.000';

SELECT @t2 = GETDATE()
    , @CompVal2 = (
        SELECT spi.cntr_value
        FROM sys.sysperfinfo spi
        WHERE spi.counter_name = 'SQL Compilations/sec                                                                                                            '
        )
    , @ReCompVal2 = (
        SELECT spi.cntr_value
        FROM sys.sysperfinfo spi
        WHERE spi.counter_name = 'SQL Re-Compilations/sec                                                                                                         '
        )
    , @BatchVal2 = (
        SELECT spi.cntr_value
        FROM sys.sysperfinfo spi
        WHERE spi.counter_name = 'Batch Requests/sec                                                                                                              '
        );

SET @ElapsedMS = DATEDIFF(MILLISECOND, @t1, @t2);
SELECT  ElapsedTimeMS = @ElapsedMS
    , [SQL Compilations/sec] = (@CompVal2 - @CompVal1) / @ElapsedMS * 1000 
    , [SQL Recompilations/sec] = (@ReCompVal2 - @ReCompVal1) / @ElapsedMS * 1000
    , [Batch Requests/sec] = (@BatchVal2 - @BatchVal1) / @ElapsedMS * 1000;

In Query Window 2, run the following while the above code is running. The code simply executes 100 T-SQL batches:

EXEC sys.sp_executesql N'SELECT TOP(1) o.name FROM sys.objects o;';
GO 100

If you switch back to Query Window 1 you'll see something like this:

╔═══════════════╦══════════════════════╦════════════════════════╦════════════════════╗
║ ElapsedTimeMS ║ SQL Compilations/sec ║ SQL Recompilations/sec ║ Batch Requests/sec ║
╠═══════════════╬══════════════════════╬════════════════════════╬════════════════════╣
║      10020.00 ║       10.07984031000 ║          0.00000000000 ║     10.07984031000 ║
╚═══════════════╩══════════════════════╩════════════════════════╩════════════════════╝

If we look at this query:

SELECT dest.text
    , deqs.execution_count
FROM sys.dm_exec_query_stats deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
WHERE dest.text LIKE 'SELECT TOP(1)%'

We can confirm there were 100 executions of the test query.

In the results above, you can see we're getting compilations each time the sp_executesql statement executes. The plan for that is certainly being cached, yet we see a compilation for it; what gives?

The Microsoft Docs say this about sp_executesql:

sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql.

So, sp_executesql itself is being compiled each time it runs, even if the plan for the command text is already in the plan cache. @PaulWhite shows in his answer that most calls to sp_executesql are not, in fact, cached.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.