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.
