Several connections ask SQL Server to compile the same statement at once. OPTIMIZED_SP_EXECUTESQL makes those matching requests share a serialized compilation path. Test the setting with concurrency before expecting a benefit.
Confirm OPTIMIZED_SP_EXECUTESQL on This Database
SQL Server 2025 adds this database scoped configuration. Its default is OFF. Inspect the current database before changing a test setting.
The feature targets batches submitted through sp_executesql. Matching statement text and compatible execution context matter. Unrelated statements don't share one compilation merely because the setting is enabled.
I look for bursts of matching application calls before considering this change. A single slow query needs its own investigation. A compilation storm is a concurrency pattern.
Run these checks in the database the application uses. A setting in another database doesn't configure this workload. Also record the server version beside the test result.
The query reports current state without changing anything. An older server won't expose a matching configuration row. Don't assume an absent row means the same thing as an explicit OFF value.
SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion;
SELECT DB_NAME() AS DatabaseName, name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name IN (N'OPTIMIZED_SP_EXECUTESQL', N'ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY');
SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on
FROM sys.databases WHERE database_id = DB_ID();Understand What Other Callers Wait For
With the setting enabled, an identical batch tries to obtain a compile lock. The first caller compiles and places the plan in cache. Waiting callers reuse that plan when it becomes available.
Without this serialization, concurrent identical submissions can compile independently. Additional plan copies and compilation work are the pattern being addressed. Existing cached plans already reused efficiently leave less work for the setting to remove.
This doesn't serialize execution of the application query. After compilation, several sessions can execute the plan concurrently. Ordinary data locking and resource limits still apply.
The first compilation also still has a cost. Expensive compilation or statistics refresh can become visible waiting for other callers. Observe that wait rather than hiding it behind a lower compilation count.
OPTIMIZED_SP_EXECUTESQL changes one part of the request lifecycle. It doesn't remove parameter sensitivity or make every cached plan appropriate. Keep execution performance in the same evaluation.
Review Statistics Update Behavior
Automatic statistics updates influence compilation. A synchronous refresh can make other compilation requests wait. Review the current statistics policy before enabling the feature.
Microsoft recommends asynchronous automatic statistics updates with ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY when automatic updates are enabled. Evaluate those options as part of the test. They change statistics freshness behavior beyond this one setting.
The commands below belong in a disposable test database. Record the prior settings before using them. Restore the actual prior values when the experiment ends.
Don't paste a configuration bundle into production without understanding each option. An application that needs freshly updated statistics immediately has a different tradeoff. Test representative changes to its data distribution.
I compare execution plans during that test as well as compilation counts. A reduced compilation burst isn't a victory if the surviving plan causes expensive execution. Both costs belong to the workload.
ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;
ALTER DATABASE SCOPED CONFIGURATION SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = ON;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_SP_EXECUTESQL = ON;
Collect OPTIMIZED_SP_EXECUTESQL Compilation Counters Twice
The SQL Compilations/sec performance counter is a rate counter. Its raw cumulative value isn't an immediate per-second reading. Collect two samples and divide the difference by the elapsed seconds.
SQL Re-Compilations/sec helps distinguish recompilation activity. Batch Requests/sec gives workload context. Compare those counters during the same test interval.
The query below returns the raw values and counter type. Save the collection timestamp with each result. A server restart between samples invalidates the subtraction.
These counters cover the instance, not one database exclusively. An unrelated workload contributes to their changes. Use an isolated test or account for that other work.
Don't read a lower raw number as proof of a faster application. The workload count and elapsed interval must match. A quiet server compiles very efficiently by doing nothing.
SELECT SYSDATETIME() AS SampleAt, object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%:SQL Statistics%'
AND counter_name IN (N'SQL Compilations/sec', N'SQL Re-Compilations/sec', N'Batch Requests/sec');Inspect Matching Cache Entries
Cache inspection helps connect the counters to the test statement. Include a distinctive table name in its text. Examine parameter declarations and use counts alongside the number of entries.
Different SET options can create different cache contexts. Different database contexts can do the same. The feature doesn't merge every statement that looks similar to a person.
The sample workload uses a disposable table. Run its setup once before opening concurrent windows. Each window then submits the same statement through sp_executesql.
The selected key differs between callers without changing the batch text. Keep parameter metadata identical too. Otherwise you are testing several declaration shapes.
CREATE TABLE dbo.CompileStormDemo(ItemId int NOT NULL PRIMARY KEY, ItemName nvarchar(80) NOT NULL);
INSERT dbo.CompileStormDemo VALUES(1, N'One'), (2, N'Two');
EXEC sys.sp_executesql
N'SELECT ItemName FROM dbo.CompileStormDemo WHERE ItemId = @ItemId;',
N'@ItemId int', @ItemId = 1;
SELECT cp.plan_handle, cp.usecounts, cp.size_in_bytes, st.text
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.objtype = N'Prepared'
AND st.text LIKE N'%SELECT ItemName FROM dbo.CompileStormDemo%';Reproduce the Application's Concurrency
One SSMS window cannot demonstrate a concurrent compile burst by itself. Use several coordinated connections on an isolated test system. The application load driver is preferable when it reproduces real connection behavior.
Start with the setting OFF and an equivalent cold plan condition for the specific test. Then repeat with it ON. Avoid clearing the entire production plan cache to create a demonstration.
Keep the statement, parameter distribution and connection count comparable. Capture throughput, caller latency and compilation activity. Those measurements are yours to collect, not assumed results here.
What happens when several callers arrive before the first compilation finishes? That is the important interval. Repeated serial calls against a warm cache answer a different question.
Keep or Reverse OPTIMIZED_SP_EXECUTESQL from Evidence
Compare the execution plans as well as the cache entries. Look for compilation waits and statistics refresh behavior. Check that execution latency remains acceptable under the same load.
I keep the feature when the representative concurrent workload benefits. I reverse the test change when it doesn't address the measured problem. A newer configuration name isn't evidence by itself.
OPTIMIZED_SP_EXECUTESQL deserves an isolated, repeatable test. Record every configuration changed during it. Then make the production decision from the complete workload result.
Record the application connection settings with the test. Different contexts can explain extra cache entries. Matching text alone is only part of the reuse contract.
Related reading on this blog: Brief Note About RESOURCE_SEMAPHORE_QUERY_COMPILE Wait Type Resource and Script to Get Compiled Plan with Parameters From Cache.
Compilation serialization is not faster execution by itself, it is a way to share compilation work between matching callers.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
