8

I have noticed execution plans sometimes include a StatementParameterizationType attribute.

Properties window

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

12

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.