Every batch causes a compile
It's like every
sp_executesqlcall 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?
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.