What is the StatementParameterizationType plan attribute? I 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:


0 = None
1 = User
2 = Simple
3 = Forced


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';