What is the StatementParameterizationType plan attribute?

Question

I have noticed execution plans sometimes include a StatementParameterizationType attribute.

What is this, what does it mean, and when does it appear?

Answer 1

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