Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [simple-parameterization]

Simple Parameterization is associated with SQL Server execution plans that are produced in the Trivial phase. It results in one or more literal values being replaced by parameters.

Simple Parameterization can only occur in Trivial Plans. The optimizer may attempt to replace a literal value with a parameter to promote plan reuse. The final decision is made after considering if parameterization would be "safe".

The presence of parameter markers like @1 and @2 is not sufficient to conclude that Simple Parameterization was applied, only that it was attempted.

5 questions
10
votes
1 answer

Why does a plan with FULL optimization show simple parameterization?

I read that only Trivial Plans can be Simple Parameterized, and that not all queries (even when the plan is Trivial) can be Simple Parameterized. Then why is this plan showing Full Optimization, and Simple Parameterization at the same time?
Erik Reasonable Rates Darling
  • 45,549
  • 14
  • 145
  • 532
3
votes
1 answer

Set simple parameterization for a json_value query (without using plan guides)

I'm trying to run this query, but it fails it fails with: The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal.` select mt.pk from My_Table mt where json_value(mt.json_data, '$.group_id') != '' As you can…
Factor Mystic
  • 241
  • 2
  • 9
2
votes
1 answer

AlwaysEncrypted error when inserting via stored proc

lets start with the error first: Encryption scheme mismatch for columns/variables . The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '3' expects it to be (encryption_type =…
Armandt Burger
  • 53
  • 1
  • 1
  • 7
0
votes
0 answers

Two Databases using query store - one saves Paramerized Queries as literals in sys.query_store_sql_text , the other saves them as parameterized

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…
SE1986
  • 2,142
  • 4
  • 30
  • 61
0
votes
0 answers

Why additional plans are created under simple parameterization?

I have a table which has 2163 rows and clustered index on id column. I run the following queries: select id from ticket_attach where id = 1 select id from ticket_attach where id = 2 select id from ticket_attach where id = 3 Here is my plan…
Rauf Asadov
  • 1,313
  • 14
  • 36