I have noticed execution plans sometimes include a StatementParameterizationType
attribute.
What is this, what does it mean, and when does it appear?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI have noticed execution plans sometimes include a StatementParameterizationType
attribute.
What is this, what does it mean, and when does it appear?
The StatementParameterizationType
attribute indicates the type of parameterization applied to the statement.
The values are documented in sys.query_store_query
:
It appears on the root node of post-execution ("actual") plans only.
The Query Store must also be enabled to see this attribute (e.g. in SSMS).
AdventureWorks demo:
USE AdventureWorks2017;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
ALTER DATABASE CURRENT SET PARAMETERIZATION SIMPLE;
GO
-- None (0)
SELECT COUNT_BIG(*)
FROM Production.Product AS P
WHERE P.Color = N'Red';
GO
-- User (1)
EXECUTE sys.sp_executesql
@stmt = N'
SELECT COUNT_BIG(*)
FROM Production.Product AS P
WHERE P.Color = @Color;',
@params = N'@Color nvarchar(15)',
@Color = N'Red';
GO
-- Simple (2)
SELECT A.AddressID
FROM Person.[Address] AS A
WHERE A.AddressLine1 = N'1 Smiling Tree Court';
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
ALTER DATABASE CURRENT SET PARAMETERIZATION FORCED;
GO
-- Forced (3)
SELECT COUNT_BIG(*)
FROM Production.Product AS P
WHERE P.Color = N'Red';