0

I have two test databases - AdventureWorks2012 and StackOverflow2010 on the same SQL Server:

Microsoft SQL Server 2019 (RTM-CU21) (KB5025808) - 15.0.4316.3 (X64) 
    Jun  1 2023 16:32:31 
    Copyright (C) 2019 Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19044: )

I enable Query Store on both databases like so:

USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET QUERY_STORE = ON
ALTER DATABASE [AdventureWorks2012] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
ALTER DATABASE [StackOverflow2010] SET QUERY_STORE = ON
ALTER DATABASE [StackOverflow2010] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)

And run a query on each that is parameterized by simple parameterization:

USE AdventureWorks2012
UPDATE  Person.Person
SET     FirstName = 'John'
WHERE   LastName = 'Smith'


USE StackOverflow2010
UPDATE  Users
SET     DisplayName = 'John Smith'
WHERE   Location = 'United Kingdom'

The query plan confirms that they have been parameterized:

enter image description here

If I look into the query_store_query_text view, I can see that Stackoverflow2010 shows the query as parameterised and Adventureworks shows the query as a literal

USE AdventureWorks2012
SELECT * FROM sys.query_store_query_text WHERE query_sql_text LIKE '%Firstname%'

USE StackOverflow2010
SELECT * FROM sys.query_store_query_text WHERE query_sql_text LIKE '%DisplayName%'

enter image description here

Why does one database show the query as parameterised in sys.query_store_query_text and the other show the literal query?

4
  • what is the output of SELECT name, is_parameterization_forced FROM sys.databases where name in ('AdventureWorks2012','StackOverflow2010')
    – SergeyA
    Jul 28, 2023 at 16:29
  • Looks like an attempt at simple parameterization Jul 28, 2023 at 16:40
  • Hi @ErikDarling thanks - I can see that the StackOverflow plan has a StatementParameterizationType of 2 and the Adventureworks plan has 0. The trace flag in your linked answer also confirms that the StackOverflow example is marked as safe but the AdventureWorks one is not
    – SE1986
    Jul 31, 2023 at 14:08
  • Furthermore, sys.query_store_query.query_parameterization_type_desc returns NONE and SIMPLE for AW and SO queries respectively
    – SE1986
    Jul 31, 2023 at 14:17

0