Auto-Parameterization in SQL Server

I've known about Auto-Parameterization in SQL Server for a long time, but just recently got into a situation when it magically worked or did not work for me.

I've done a research and got some very interesting facts on how SQL Server determines to auto-parameterize a query or not.

I've used following script to create and populate a test table:

DROP TABLE IF EXISTS tbl_TestPlanCache;
GO
CREATE TABLE tbl_TestPlanCache(
    SearchKey INT,
    SearchValue NVARCHAR(100)
);
GO
INSERT INTO tbl_TestPlanCache(SearchKey, SearchValue)
SELECT message_id, LEFT(text,10) FROM sys.messages
WHERE language_id = 1033;GO

I used following list of cases to come up with different indexing scenarios:

-- Cases
-- 1
-- No Indexes
-- 2
--CREATE INDEX IX_TestPlanCache_SearchKey ON tbl_TestPlanCache(SearchKey)
-- 3
--CREATE INDEX IX_TestPlanCache_SearchKey ON tbl_TestPlanCache(SearchKey)
--CREATE INDEX IX_TestPlanCache_SearchValue ON tbl_TestPlanCache(SearchValue)
-- 4
--CREATE INDEX IX_TestPlanCache_SearchKey ON tbl_TestPlanCache(SearchKey) INCLUDE (SearchValue)
-- 5
--CREATE INDEX IX_TestPlanCache_SearchKey ON tbl_TestPlanCache(SearchKey) INCLUDE (ID)
-- 6
--CREATE INDEX IX_TestPlanCache_SearchKey ON tbl_TestPlanCache(SearchKey) INCLUDE (ID, SearchValue)
-- 7
--CREATE INDEX IX_TestPlanCache_SearchKey ON tbl_TestPlanCache(SearchKey, SearchValue)
-- 8
--CREATE INDEX IX_TestPlanCache_SearchKey ON tbl_TestPlanCache(SearchKey, ID)
-- 9
--CREATE INDEX IX_TestPlanCache_SearchKey ON tbl_TestPlanCache(SearchKey, ID, SearchValue)
-- 10
--CREATE INDEX IX_TestPlanCache_SearchKey ON tbl_TestPlanCache(SearchKey, ID, SearchValue)
--CREATE INDEX IX_TestPlanCache_SearchValue ON tbl_TestPlanCache(SearchValue)
-- 11
--CREATE UNIQUE INDEX UIX_TestPlanCache_SearchKey ON tbl_TestPlanCache(SearchKey)
-- 12
--ALTER TABLE tbl_TestPlanCache ADD CONSTRAINT UQE_TestPlanCache_SearchKey UNIQUE (SearchKey)
-- 13
--CREATE CLUSTERED INDEX CLIX_TestPlanCache_SearchKey ON tbl_TestPlanCache(SearchKey)
-- 14
--CREATE CLUSTERED INDEX CLIX_TestPlanCache_SearchKey ON tbl_TestPlanCache(SearchKey)
--CREATE INDEX IX_TestPlanCache_SearchValue ON tbl_TestPlanCache(SearchValue)
-- 15
--CREATE CLUSTERED INDEX CLIX_TestPlanCache_SearchKey ON tbl_TestPlanCache(SearchKey)
--CREATE INDEX IX_TestPlanCache_SearchValue ON tbl_TestPlanCache(SearchValue) INCLUDE (ID)

Then I used following script to generate and capture generated plans:
(DO NOT TRY THIS ON YOUR PRODUCTION SERVER)

DBCC FREEPROCCACHE;
GO
SELECT * FROM dbo.tbl_TestPlanCache WHERE SearchValue = N'The select';
GO
SELECT * FROM dbo.tbl_TestPlanCache WHERE SearchKey = 126;
GO
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns)
SELECT cp.objtype
    , RIGHT(st.TEXT, LEN(st.TEXT)-PATINDEX('%WHERE%',st.TEXT)-5) AS Query
    , CAST(qp.query_plan AS XML).value('max(//ns:StmtSimple/@StatementSubTreeCost)','float') AS StatementCost
    , CAST(qp.query_plan AS XML)
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
LEFT JOIN sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
    ON qs.[plan_handle] = cp.[plan_handle]
CROSS APPLY sys.dm_exec_sql_text(qs.[plan_handle]) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.[plan_handle], qs.statement_start_offset,qs.statement_end_offset) AS qp
WHERE st.TEXT like '%tbl_TestPlanCache%'
ORDER BY qs.creation_time OPTION (RECOMPILE);
GO

And here is the result of these test cases:

Based on the results I could make following assumptions for a single table query with single equality column in a WHERE clause:

SQL Server will Auto-Parameterize a query in following cases:
1. If Clustered Index Seek is performed.
2. If Clustered Index Scan is performed.
3. If Index Seek is performed for covering index (No RID or Key Lookup).
4. If Index Seek is performed for Unique Index or UniqueConstraint
5. If Table Scan is performed for a column, which included in any other index.
6. If Table Scan is performed with no indexes in a table at all.

SQL Server will NOT Auto-Parameterize a query in following cases:
1. If Index Seek is performed for not covering and not unique index resulting RID or Key Lookup.
2. If Table Scan is performed for a column, which not included in any index and other indexes exist.


As you can see, Auto-Parameterization in SQL Server, does not depend on query cost at all and it is very hard to tell if there are any benefits for it in some instances. So, I'd strongly recommend to explicitly parameterize/prepare all your production queries.

If you are really sure you want ALL your queries must be parameterized and you can't change the application code you can use "FORCE" parametrization setting for your database like this:

ALTER DATABASE <YourDatabase>
SET PARAMETERIZATION FORCED;