or
sql-server
Paul White
I have noticed execution plans sometimes include a `StatementParameterizationType` attribute.

[![Properties window][1]][1]

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

  [1]: https://i.stack.imgur.com/nQ7Xp.png
Top Answer
Paul White
The `StatementParameterizationType` attribute indicates the type of [parameterization][1] applied to the statement.

The values are documented in [`sys.query_store_query`][2]:

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

It appears on the root node of **post-execution** ("actual") plans only.

The [**Query Store**][3] must also be **enabled** to see this attribute (e.g. in SSMS).

---

[AdventureWorks][4] 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';

  [1]: https://docs.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide#PlanReuse
  [2]: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-query-transact-sql
  [3]: https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store
  [4]: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
What is the StatementParameterizationType plan attribute?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.