Repeating the same query text does not automatically mean the application is using the same execution plan. Prepared statements help when their parameter contract and connection lifetime remain stable enough to support reuse.
Distinguish Parameterization From Prepared Statements
Parameterized execution separates values from SQL text. Preparation adds a server-side handle that the client can use for later executions. These are related capabilities, but the handle is not the only way to obtain plan reuse.
sp_executesql executes parameterized text without exposing an application-managed prepared handle. sp_prepare returns a handle, sp_execute uses it, and sp_unprepare releases it. Drivers choose among these mechanisms according to their APIs and configuration.
I inspect the calls reaching SQL Server instead of inferring them from an application's method name. I also compare parameter metadata between executions. A method named Prepare can hide different driver behavior from the simple server-side example.
Plan reuse depends on more than visible text. Database context, relevant SET options, parameter declarations, and other cache-key details matter. Schema changes, invalidation, recompilation, and cache eviction can also interrupt reuse.
Prepare and Execute a Complete Example
Run the setup in an isolated test database on a supported SQL Server version. The example remains applicable to SQL Server 2025. The tiny dataset illustrates handle use rather than supplying meaningful performance measurements.
DROP TABLE IF EXISTS dbo.CatalogItem;
CREATE TABLE dbo.CatalogItem
(
ItemId int NOT NULL PRIMARY KEY,
ItemCode varchar(20) NOT NULL
);
INSERT dbo.CatalogItem(ItemId, ItemCode)
VALUES (1, 'A100'), (2, 'A200'), (3, 'A300');The next batch prepares one statement and executes it with two values. Keep the entire operation on the same physical connection. A prepared handle belongs to its server session and cannot simply be moved to another pooled connection.
DECLARE @Handle int = NULL;
BEGIN TRY
EXEC sys.sp_prepare
@Handle OUTPUT,
N'@ItemId int',
N'SELECT ItemId, ItemCode FROM dbo.CatalogItem
WHERE ItemId = @ItemId;';
EXEC sys.sp_execute @Handle, 1;
EXEC sys.sp_execute @Handle, 2;
EXEC sys.sp_unprepare @Handle;
SET @Handle = NULL;
END TRY
BEGIN CATCH
IF @Handle IS NOT NULL EXEC sys.sp_unprepare @Handle;
THROW;
END CATCH;In SSMS the first result grid comes back empty. That grid is sp_prepare returning the column metadata, before either execution runs.
The cleanup path releases an obtained handle after success or failure. Application code should likewise dispose its prepared command according to the driver contract. Leaving handles behind is not a plan-reuse optimization.
Preparing a statement for one execution introduces work that requires justification. Repeated use can amortize preparation and communication costs. Measure the actual application path rather than assuming explicit preparation is always the faster choice.
Compare Prepared Statements With sp_executesql
The following executions use the same parameter declaration and statement text. Only the supplied value changes. They demonstrate a simpler parameterized execution pattern without retaining a handle in application state.
EXEC sys.sp_executesql
N'SELECT ItemId, ItemCode FROM dbo.CatalogItem
WHERE ItemId = @ItemId;',
N'@ItemId int', @ItemId = 1;
EXEC sys.sp_executesql
N'SELECT ItemId, ItemCode FROM dbo.CatalogItem
WHERE ItemId = @ItemId;',
N'@ItemId int', @ItemId = 2;Stable parameterized text can support cached-plan reuse through this mechanism too. Calling sp_executesql repeatedly does not require compilation on every invocation. Do not describe explicit handles as the sole route to a reusable plan.
Neither mechanism permits parameterizing a table name as an ordinary value. Dynamic identifiers need a separate validated design. Avoid converting user-selected identifiers into concatenated SQL without an allowlist and appropriate identifier quoting.
Prepared statements also do not make arbitrary SQL text safe. The values must remain parameters rather than being concatenated into the statement. Keep the SQL shape controlled by the application and the values bound through its supported API.
Inspect Cached Prepared Plans Carefully
sys.dm_exec_cached_plans identifies cache entries with objtype equal to Prepared. Combine it with cached SQL text to find the relevant statements. These entries can come from multiple parameterized execution mechanisms, rather than exclusively from an explicit sp_prepare call.
SELECT TOP (30)
cp.objtype, cp.cacheobjtype, cp.usecounts,
cp.size_in_bytes, st.text AS CachedText
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'%CatalogItem%'
ORDER BY cp.usecounts DESC, cp.size_in_bytes DESC;On my test run, the query returned three Prepared entries. One held the sp_prepare text, one held the sp_executesql text, and one came from the setup INSERT, which SQL Server had parameterized on its own. The first two differ only in indentation, and that alone kept them apart.
Cache usecounts describe cache-object lookups, not a universal exact query execution counter. Compare execution statistics when execution frequency is the actual question. A cache entry can also disappear before an investigator queries it.
SQL Server 2022 and later require suitable server performance permissions for this inspection. Earlier supported versions use the corresponding server-state permission. Limited visibility and memory pressure both affect the evidence available.
Capture enough context to distinguish apparently identical entries. Review parameter lengths, types, collations, and SET options before labeling multiple entries accidental duplicates. The cache can legitimately retain different compiled shapes for different execution contracts.
Keep Prepared Statements From Fragmenting the Cache
Changing a varchar parameter length according to each supplied value changes its declaration. Sending nvarchar for a varchar predicate introduces a different type contract. Make parameter definitions match the database schema consistently.
Avoid adding request-specific comments or literal values to otherwise stable query text. Those changes can produce separate cache entries. Use application logging for request identity rather than embedding a new identity in every SQL batch.
A parameterized query can still have different runtime behavior for different values. Selectivity and data skew affect plan quality independently of reuse. Reusing one unsuitable plan more efficiently is not the desired result.
Test the application's important parameter range and inspect actual plans. Keep parameter sensitivity, recompilation policy, and prepared-handle management as separate decisions. None of them should be changed solely because the cache contains a large number.
Understand Optimize for Ad Hoc Workloads
The instance option optimize for ad hoc workloads changes first-use caching for eligible ad hoc batches. It retains a small compiled-plan stub initially instead of the complete plan. A subsequent execution can lead to caching the full compiled plan.
This reduces cache memory consumed by many single-use ad hoc statements. It does not eliminate their first compilation cost. It also does not parameterize literals, repair data type mismatches, or directly solve prepared-plan fragmentation.
SELECT name, value, value_in_use
FROM sys.configurations
WHERE name = N'optimize for ad hoc workloads';
SELECT cp.objtype, cp.cacheobjtype,
COUNT_BIG(*) AS CacheEntries,
SUM(CONVERT(bigint, cp.size_in_bytes)) AS CacheBytes
FROM sys.dm_exec_cached_plans AS cp
WHERE cp.objtype IN (N'Adhoc', N'Prepared')
GROUP BY cp.objtype, cp.cacheobjtype
ORDER BY cp.objtype, cp.cacheobjtype;Review the distribution and memory footprint before requesting an instance-wide change. Existing cached plans do not transform instantly into a new policy's entries. Avoid clearing production cache merely to make a before-and-after chart look immediate.
The option concerns cache memory management, while application parameterization concerns query shape. Both can be useful when their specific problem exists. One setting should not become a substitute for fixing an application that generates needless SQL variations.
Verify Reuse Through the Real Application
Does the application reuse the command across enough executions to justify prepared statements? Observe its physical connection lifetime and actual server calls. Include pool behavior, cancellation, failure cleanup, and command disposal in that test.
Measure compilation, reads, CPU, and end-to-end latency with representative concurrency. Preserve the parameter declarations and database context beside the results. A cache screenshot alone does not show whether users experienced a useful improvement.
Choose the simplest supported parameterized execution pattern that preserves correctness and provides suitable reuse. Explicit preparation earns its place through repeated use and predictable lifetime management. Keep the plan evidence tied to that actual workload.
Related reading on this blog: Optimize for Ad Hoc Workloads: SQL in Sixty Seconds #173 and Forced Parameterization: When It Cuts CPU and When It Hurts.
A prepared handle is not a guarantee of a good plan, it is a reusable execution contract that still needs verification.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
