{"id":71156,"date":"2017-06-06T13:14:35","date_gmt":"2017-06-06T13:14:35","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=71156"},"modified":"2026-03-18T13:01:43","modified_gmt":"2026-03-18T13:01:43","slug":"query-store-parameterization-problems","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/query-store-parameterization-problems\/","title":{"rendered":"Parameter Sniffing: Fix It with Query Store"},"content":{"rendered":"

Parameter sniffing becomes a problem when SQL Server caches a query plan optimized for one set of parameter values that performs terribly for others – common with skewed data distributions. Query Store (available since SQL Server 2016) makes diagnosing this straightforward: it captures all plan variations for parameterized queries, letting you compare execution stats and force the optimal plan. This article demonstrates the problem practically using AdventureWorks, shows how to identify affected queries in Query Store, and walks through three fix strategies: plan forcing, OPTIMIZE FOR hints, and RECOMPILE.<\/p>\n

Introduction<\/h2>\n

Although parameterization problems can have a serious impact on the performance of a SQL Server database, it isn\u2019t easy to determine which queries have the problem so that you can fix the issues that are causing them to run slowly. Although it is relatively easy to determine whether a specific query has a parameterization problem, it is more difficult to then identify all the other queries that have this kind of problem in the server. If we can do this, and resolve the problem for each query, it will improve the overall performance of the database.<\/p>\n

As well as being a very important new tool in SQL Server 2016, Query Store can also help us to find all the queries with parameterization problems in a SQL Server, and to fix them. In this article I will show you how that\u2019s done.<\/p>\n

What is the Parameterization Problem?<\/h2>\n

When SQL Server receives a new query, the query is compiled and the resulting query plan is stored in memory, in the query plan cache, so the plan can, if possible, be reused.: The query itself is also stored with it as a string. Every time the same query, with the same text, is executed again, the query plan will be re-used from the cache.<\/p>\n

However, if the text was saved unaltered, all queries that have predicates seeking for specific information, such as the client record for customer 135, would have this specific information stored in the cache. In consequence, a query that, for example, was seeking for a particular client record would only be reused if a new query was made seeking for the same client. If a new query asked for a different client, it would be compiled again and stored, with no reuse. This would result in excessive query compilations, thereby affecting server performance.<\/p>\n

To get around this problem, SQL Server \u2018parameterizes\u2019 the query string. The way it does this is controlled by a database property called Parameterization<\/strong>. The default configuration, ‘Simple’<\/em>, allows SQL Server to judge whether to parameterize a query or not, so that some but not all queries are parameterized.<\/p>\n

When SQL Server decides to parameterize a query, the information in the query are replaced by parameters, and this parameterized query is stored in cache along with its plan. If another query is made that after this parameter substitution resolves to the same query, then the plan can be reused, thereby potentially saving considerable time and CPU.<\/p>\n

<\/p>\n

You might think that all such queries should be parameterized, and so be tempted to change the Parameterization<\/strong> option to ‘<\/strong>Forced<\/em>‘<\/strong>; but there is a catch: Some queries have a terrible behavior when parameterized. When allowed to, with \u2018simple\u2019 parameterization, SQL Server parameterizes just a small percentage of queries that it caches, using a complex heuristic, to avoid parameterizing a query that shouldn\u2019t be parameterized.<\/p>\n

As well as using the \u2018forced\u2019 Parameterization<\/strong> configuration, a query can also be parameterized by the developer. The developer can call sp_execute_sql<\/strong> procedure to send a parameterized query to the database. In fact, several ORM (Object-Relational Mapping)<\/strong> frameworks, such as Entity Framework<\/strong>, uses this procedure automatically.<\/p>\n

Batches of queries inside stored procedures are also parameterized by the developer, using the stored procedure parameters, or the batches provided to sp_execute_sql<\/strong>. This means that they can also be subject to parameterization problems.<\/p>\n

The problem itself happens when we parameterize a query or batch that shouldn\u2019t be parameterized. If the data distribution for the column used in the predicate is particularly skewed, some queries would have a terrible execution plan for some values of the predicate: Everything goes well if the values are equally distributed, but if they aren\u2019t, then we are likely to have a problem because the strategy in the cached query plan would be poor. Sometimes, the first time that the query is used, it uses an atypical parameter so that the stored plan is inappropriate for the majority of subsequent queries. This can lead to a query running uncharacteristically slowly on occasion. (see Parameter Sniffing<\/a>)<\/p>\n

All this seems a bit nebulous when just described: So, in the first part of this article, we will illustrate the problem practically, and then we\u2019ll show how to investigate it and solve it. First. We must set up a test environment to demonstrate these points.

Read also:<\/strong> SQL Server triggers and performance impact<\/span><\/a><\/p>\n

Demonstration Environment<\/h2>\n

I will do all the demonstrations of parameterization using the sample database \u2018AdventureWorks2016\u2019<\/em>, which you can download at https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=49502<\/a>.<\/p>\n

I will also use a function that I\u2019ve developed called \u2018plancachefromdatabase\u2019<\/em>, which allow us to check the content of the plan cache of a specific database. I explained about this function in a previous article I wrote on Simple-Talk called https:\/\/www.simple-talk.com\/sql\/t-sql-programming\/checking-the-plan-cache-warnings-for-a-sql-server-database\/<\/a><\/p>\n

Execute the code below in the \u2018AdventureWorks2016\u2019<\/em> database to create the function, preparing the environment for the demonstrations.<\/p>\n

-- =============================================\n  -- Author:           Dennes Torres\n  -- Create date: 01\/23\/2015\n  -- Description:      return the query plans in cache for a specific database\n  -- =============================================\n  CREATE FUNCTION [dbo].[planCachefromDatabase] \n  (      \n         -- Add the parameters for the function here\n         @DatabaseName varchar(50)\n  )\n  RETURNS TABLE \n  AS\n  RETURN \n  (\n    with xmlnamespaces\n    (default 'http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan')\n  select qp.query_plan,qt.text, \n    statement_start_offset, statement_end_offset,\n    creation_time, last_execution_time,\n    execution_count, total_worker_time,\n    last_worker_time, min_worker_time,\n    max_worker_time, total_physical_reads,\n    last_physical_reads, min_physical_reads,\n    max_physical_reads, total_logical_writes,\n    last_logical_writes, min_logical_writes,\n    max_logical_writes, total_logical_reads,\n    last_logical_reads, min_logical_reads,\n    max_logical_reads, total_elapsed_time,\n    last_elapsed_time, min_elapsed_time,\n    max_elapsed_time, total_rows,\n    last_rows, min_rows,\n    max_rows \n  from sys.dm_exec_query_stats\n    CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt\n    CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp\n    where qp.query_plan.exist('\/\/ColumnReference[fn:lower-case(@Database)=fn:lower-case(sql:variable(\"@DatabaseName\"))]')=1\n  )\n  GO<\/pre>\n
I will also use the tables generated by the script called \u2018Make_Big_Adventure\u2019<\/em>, created by Adam Machanic. You can download the script here (http:\/\/sqlblog.com\/blogs\/adam_machanic\/archive\/2011\/10\/17\/thinking-big-adventure.aspx) and execute it in \u2018AdventureWorks2016\u2019<\/em>. You need to change the first line of the script, changing the \u2018Use\u2019<\/em> instruction to \u2018Use Adventureworks2016\u2019<\/em>.<\/p>\n
Finally, we also will use a new index over the table \u2018BigProduct\u2019. Below you can see the \u2018create index\u2019<\/strong> instruction, execute it in \u2018AdventureWorks2016\u2019<\/em> database.<\/p>\n
CREATE NONCLUSTERED INDEX indPrice\n  ON [dbo].[BigProduct] ([ListPrice])\n  GO<\/pre>\n
Demonstrating Parameterization<\/h2>\n
These four queries below will illustrate the simple parameterization: two of them will be parameterized and the other two will not. We can check this by using queries over the system tables to look at what is being stored in the query plan cache.<\/p>\n
Let\u2019s execute them step-by-step:<\/p>\n
    \n
  1. Select the database and clear the procedure cache\n
    use AdventureWorks2016\n  go\n  alter database scoped configuration clear procedure_cache<\/pre>\n
    This method of clearing the procedure cache of a single database is new to SQL Server 2016. However, you need to take care with it, and avoid using this procedure in a production database, because it will affect the database performance for a while.<\/p>\n<\/li>\n
  2. Select and execute the following query:\n
    select * from person.Address where city='Bellflower'<\/pre>\n
    It\u2019s important to select the query precisely. If you select a single extra space before or after the query the query optimizer will understand as a different query and the demonstration will fail.<\/p>\n
    Correct:<\/p>\n
    <\/p>\n
    Wrong: (extra white space after the string)<\/p>\n
    <\/p>\n
    Wrong again: (preceding white space in the CR\/LF sequence)<\/p>\n
    <\/p>\n<\/li>\n
  3. Do the same, select and execute, each one of the following queries, one by one:\n
    select * from person.Address where city='Sammamish'\n  SELECT * FROM Sales.CreditCard WHERE CreditCardID = 11\n  SELECT * FROM Sales.CreditCard WHERE CreditCardID = 12<\/pre>\n<\/li>\n
  4. Use the query below to check the plan cache. We are using the custom function we created in the database while preparing the demo:\n
    select * from dbo.plancachefromdatabase('AdventureWorks2016')<\/pre>\n<\/li>\n<\/ol>\n
    The result will be similar to the image below. You may notice that one of the queries was compiled for each different value in the predicate while the other wasn\u2019t. Using parameterization \u2018Simple\u2019<\/strong> only a small class of queries are parameterized, while using \u2018Forced\u2019<\/strong> all the queries will be parameterized.<\/p>\n
    <\/p>\n
    Should we use \u2018Forced\u2019<\/strong> parameterization to avoid plan cache bloat? No, we shouldn\u2019t. \u2018Forced\u2019<\/strong> parameterization will parameterize queries that shouldn\u2019t be parameterized and this will become a bigger problem than plan cache bloat. Let\u2019s see an example.<\/p>\n
    In the next code section, there are two similar queries. These queries are the same, except for the parameter. However, the optimal execution plans of the queries are different. When we use the value 245.01 the plan uses an index seek, but it uses clustered index scan when we use the value 0.<\/p>\n
    To demonstrate this, select both queries below in SSMS and click the button \u2018<\/strong>Display Estimated Execution Plan<\/em>\u2019<\/strong>. I\u2019m using \u2018<\/strong>option (recompile)<\/em>\u2019<\/strong> hint, so the queries will be recompiled and we can check the plan of each query:<\/p>\n
    \/*\n  You don't need to execute the queries below. \n  You just need to select both of them and click the button\n  'Display Estimated Execution Plan' in SSMS\n  *\/\n  select * from bigproduct where listprice=245.01\n  option (recompile)\n  select * from bigproduct where listprice=0.00\n  option (recompile)<\/pre>\n
    The query plans of these queries are below:<\/p>\n
    <\/p>\n
    These queries are using a predicate over a field whose data isn’t well distributed in the table. There are ten thousand rows with the value \u20180.00\u2019<\/em> while only fifty with the value \u2018245.1\u2019<\/em>. By using the option (recompile)<\/strong> hint, we can see that there is a different query plan for each value. That’s the catch: with badly-distributed values, each value will need its own query plan. In this case, if the query is parameterized, which query plan will SQL Server use?<\/p>\n
    These troublesome queries could be parameterized for one of several reasons, and the configuration \u2018Parameterization:Forced\u2019<\/strong> is only one of them. There are several developer tools, such as ORMs, that will parameterize the queries and some of them will do this by default.<\/p>\n
    This is the nature of the problem that we are investigating: Some queries work fine with parameterization, some don\u2019t. We need to identify the queries with parameterization problems and solve the problem.<\/p>\n
    Identifying the Queries with Parameterization Problems<\/h2>\n
    Let\u2019s first try the same queries that we used to demonstrate parameterization. We\u2019ll use \u2018Forced\u2019<\/strong> parameterization and check what happens when these queries are parameterized. Let\u2019s do it step-by-step.<\/p>\n
      \n
    1. First, change the parameterization configuration and clear the database procedure cache:\n
      alter database AdventureWorks2016\n     set parameterization forced;\n  go\n  alter database scoped configuration clear procedure_cache\n  go<\/pre>\n<\/li>\n
    2. Click the \u2018<\/strong>Include Actual Execution Plan\u2019<\/em> button in SSMS toolbar.<\/li>\n
    3. Select the query below and execute it. You should be careful: If you select any extra whitespace before or after the query, the sample will not work.\n
      select * from bigproduct where listprice=245.01<\/pre>\n
      <\/p>\n<\/li>\n
    4. Repeat the same action for the second query:\n
      select * from bigproduct where listprice=0.00<\/pre>\n
      The resulting query plan is exactly the same, the second query is re-using the query plan of the firstt<\/sup> one, because they are parameterized.<\/p>\n<\/li>\n
    5. Clear the procedure cache again:\n
      alter database scoped configuration clear procedure_cache\n  go<\/pre>\n<\/li>\n
    6. Repeat the same procedure, changing the query order. First, execute the following query:\n
      select * from bigproduct where listprice=0.00<\/pre>\n
      <\/p>\n<\/li>\n
    7. Repeat the same action for the other query:\n
      select * from bigproduct where listprice=245.01<\/pre>\n
      Again, the second query plan is the same than the first, the second query is re-using the plan of the first query.<\/p>\n<\/li>\n<\/ol>\n
      As you have noticed in the demonstration, SQL Server uses the value of the first executed query to build the query plan. The resulting cached plan will be good for some values and terrible for others. We still can use some query hints to force a good enough plan, however the best solution would be to prevent the query being parameterized and for SQL Server to then use the best plan for each value.<\/p>\n
      We can solve this problem using plan guides, but let’s go a step back. First, we need a way to identify which queries in our server are suffering with parameterization problems. We can do this using query store.<\/p>\n
      Query Store is a new and very useful tool in SQL Server 2016.<\/p>\n
      Query Store<\/strong><\/h2>\n
      The query plan cache only keeps the current plan for each query. Query Store, on the other hand, is able to keep a history of query plans that are used for each query and the execution statistics for each plan. Using this information, we can answer several questions, such as \u2018which queries are suffering performance regression?\u2019.<\/p>\n
      The execution information that Query Store collects can tell us which query is suffering from parameterization problems.<\/p>\n
      Query Store needs to be enabled in all the databases in which we would like to collect historic query plans and execution statistics. When it is enabled, it stores all the query information in system tables within the database.<\/p>\n
      Query Store also has some pre-built graphics about the database activity. We can use these graphics whenever we experience regression problems or we can choose to query the information in query store system views (DMVs) to find useful data. However, what I\u2019m proposing here is far from the main objective of query store, but still a very interesting approach to solve an old problem. Due to that, we will use query store system views.<\/p>\n
      Query Store Schema<\/h2>\n
      Query Store collects information from two different phases of the query processing: The compilation and the execution. From the compilation phase, Query Store collects the query text and query plan, while from the execution phase it collects the execution statistics.<\/p>\n
      All this information is kept in memory and written asynchronously to the disk, so that the query execution performance isn\u2019t affected by the query store.<\/p>\n
      The image below illustrates the data capture process used by query store. Since the information is partially in memory until the asynchronous write happens, the query store views join the information in memory and in disk.<\/p>\n
      <\/p>\n
      The main DMVs in the QS schema are used to store queries, plans and run-time statistics. For each query, there are several plans and for each plan there are several statistic records. Each statistic record is a summary of all executions during a time interval.<\/p>\n
      The image below illustrates the query store schema and its DMVs.<\/p>\n
      <\/p>\n
      Identifying Parameterization Problems<\/strong><\/p>\n
      When a query is suffering from parameterization problems, some executions of the query will be far worse than others. By analyzing execution statistics, we can identify average execution numbers and we can also identify the standard deviation from these numbers. Standard Deviation is a difference from the average, to less or more, making a range containing at least 68.26% of the values, in this case, 68.26% of the query executions.<\/p>\n
      If the standard deviation is low, this means most executions are near the average numbers and the query hasn\u2019t a problem. However, if the query suffers from parameterization problems, some executions are way worse than others, making a high standard deviation.<\/p>\n
      That\u2019s the way we can use to identify parameterization problems: The queries with the highest standard deviation have this problem. It\u2019s possible, sure, that other problems with the query will also cause a high standard deviation, however, this method allows us to retrieve a promising list of queries that probably have parameterization problems.<\/p>\n
      Before we go into a step-by-step demonstration, we will need to build the query we will use to find the parameterization problems using the Query Store schema.

      Read also: 
      <\/strong>Extended Events for monitoring query performance<\/a>
      TempDB growth from query spills<\/span><\/a><\/p>\n
      Building the main query<\/h2>\n
        \n
      1. Firstly, we can find, using query store DMVs, all those queries that are parameterized. We can identify this using the \u2018query_parameterization_type\u2019 <\/strong>field\n
        -- Parameterized Queries\n  select * \n  from sys.query_store_query\n  where query_parameterization_type<>0<\/pre>\n<\/li>\n
      2. Now, let\u2019s join the result with the \u2018sys.query_store_query_text\u2019 <\/strong>DMV to retrieve the text of the query. We can include an additional filter to retrieve queries which have \u2018@\u2019 in the query text. This is needed because queries inside a stored procedure are not identified as parameterized, however if they use the stored procedures parameters they can suffer from the same parameterization problems.\n
        -- Parameterized Queries with query text\n  select query_id,qsqt.query_sql_text\n  from sys.query_store_query qsq, \n  \t sys.query_store_query_text qsqt\n  where qsq.query_text_id= qsqt.query_text_id \n  \t  and (query_parameterization_type<>0 or query_sql_text like '%@%')<\/pre>\n<\/li>\n
      3. By joining the query with the sys.query_store_plan <\/strong>DMV, we can retrieve the most recent query plan used by the query. We also need to include a sub-query in the filter to retrieve only the most recent query plans, rather than all of them.\n
        -- Parameterized queries with text and most recent planId\n  select qsq.query_id,qsqt.query_sql_text,qsp.plan_id\n  from sys.query_store_query qsq, \n       sys.query_store_query_text qsqt,\n       sys.query_store_plan qsp\n  where qsq.query_text_id= qsqt.query_text_id \n       and qsp.query_id=qsq.query_id\n       and (qsq.query_parameterization_type<>0\n       or qsqt.query_sql_text like '%@%')\n       and qsp.last_execution_time=(select max(last_execution_time)\n  \t\t\t\t\tfrom sys.query_store_plan qsp2\n  \t\t\t\t\twhere qsp2.query_id= qsp.query_id)<\/pre>\n<\/li>\n
      4. If we join the query with the \u2018sys.query_store_runtime_stats\u2019<\/strong> DMV, we can retrieve all the execution statistic information from each query plan we retrieved earlier.\n
        -- Parameterized queries with text and most recent planId\n  -- related with runtime stats\n  select qsq.query_id,qsqt.query_sql_text,qsp.plan_id, \n  \t\tqsrs.max_duration,\n  \t\tqsrs.max_cpu_time,\n  \t\tqsrs.min_cpu_time,\n  \t\tqsrs.min_duration,\n  \t\tqsrs.stdev_duration,\n  \t\tqsrs.stdev_cpu_time\n  from sys.query_store_query qsq, \n  \t\tsys.query_store_query_text qsqt,\n  \t\tsys.query_store_plan qsp,\n  \t\tsys.query_store_runtime_stats qsrs\n  where qsq.query_text_id= qsqt.query_text_id \n  \tand qsp.query_id=qsq.query_id\n  \tand qsrs.plan_id=qsp.plan_id\n  \tand (qsq.query_parameterization_type<>0 \n         or qsqt.query_sql_text like '%@%')\n  \tand qsp.last_execution_time=(select max(last_execution_time)\n  \t\t\t\t\tfrom sys.query_store_plan qsp2\n  \t\t\t\t\twhere qsp2.query_id= qsp.query_id)\n  order by stdev_cpu_time desc<\/pre>\n<\/li>\n
      5. The previous query retrieves several execution status rows for each query plan. The Query Store creates a new row for each time interval. We need to group the rows and summarize the values.\n
        -- Parameterized queries with text and most recent planId\n  -- related with runtime stats grouped\n  select qsq.query_id,\n  \t\tmax(qsqt.query_sql_text) query_sql_text,\n  \t\tmax(qsp.plan_id) plan_id, \n  \t\tmax(qsrs.max_duration) max_duration,\n  \t\tmax(qsrs.max_cpu_time) max_cpu_time,\n  \t\tmin(qsrs.min_cpu_time) min_cpu_time,\n  \t\tmin(qsrs.min_duration) min_duration,\n  \t\tmax(qsrs.stdev_duration) stdev_duration,\n  \t\tmax(qsrs.stdev_cpu_time) stdev_cpu_time\n  from sys.query_store_query qsq, \n  \t\tsys.query_store_query_text qsqt,\n  \t\tsys.query_store_plan qsp,\n  \t\tsys.query_store_runtime_stats qsrs\n  where qsq.query_text_id= qsqt.query_text_id \n  \tand qsp.query_id=qsq.query_id\n  \tand qsrs.plan_id=qsp.plan_id\n  \tand (qsq.query_parameterization_type<>0\n         or qsqt.query_sql_text like '%@%')\n  \tand qsp.last_execution_time=(select max(last_execution_time)\n  \t\t\t\t\tfrom sys.query_store_plan qsp2\n  \t\t\t\t\twhere qsp2.query_id= qsp.query_id)\n  group by qsq.query_id\n  order by stdev_cpu_time desc<\/pre>\n<\/li>\n
      6. We need to filter the result to exclude all system queries. I included several filters to achieve this, however if your application uses queries against system tables or system databases, these queries will be excluded too.\n
        -- Parameterized queries with text and most recent planId\n  -- related with runtime stats grouped\n  -- Filtering system queries\n  select qsq.query_id,\n  \t\tmax(qsqt.query_sql_text) query_sql_text,\n  \t\tmax(qsp.plan_id) plan_id, \n  \t\tmax(qsrs.max_duration) max_duration,\n  \t\tmax(qsrs.max_cpu_time) max_cpu_time,\n  \t\tmin(qsrs.min_cpu_time) min_cpu_time,\n  \t\tmin(qsrs.min_duration) min_duration,\n  \t\tmax(qsrs.stdev_duration) stdev_duration,\n  \t\tmax(qsrs.stdev_cpu_time) stdev_cpu_time\n  from sys.query_store_query qsq, \n  \t\tsys.query_store_query_text qsqt,\n  \t\tsys.query_store_plan qsp,\n  \t\tsys.query_store_runtime_stats qsrs\n  where qsq.query_text_id= qsqt.query_text_id \n  \tand qsp.query_id=qsq.query_id\n  \tand qsrs.plan_id=qsp.plan_id\n  \tand (qsq.query_parameterization_type<>0 \n         or qsqt.query_sql_text like '%@%')\n  \tand qsq.is_internal_query=0\n         and qsqt.query_sql_text not like '%sys.%' \n  \tand qsqt.query_sql_text not like '%sys[ ].%'\n         and qsqt.query_sql_text not like '%@[sys@].%'  escape '@'\n         and qsqt.query_sql_text not like '%INFORMATION_SCHEMA%'\n         and qsqt.query_sql_text not like '%msdb%' \n  \tand qsqt.query_sql_text not like '%master%'\n  \tand qsp.last_execution_time=(select max(last_execution_time)\n  \t\t\t\t\tfrom sys.query_store_plan qsp2\n  \t\t\t\t\twhere qsp2.query_id= qsp.query_id)\n  group by qsq.query_id\n  order by stdev_cpu_time desc<\/pre>\n<\/li>\n
      7. Finally, let\u2019s create a function to turn easier the use of this query. We can\u2019t include the order by inside the function:<\/li>\n<\/ol>\n
        Create FUNCTION dbo.QueriesWithParameterizationProblems()\n  RETURNS TABLE \n  AS\n  RETURN \n  (\n  -- Parameterized queries with text and most recent planId\n  -- related with runtime stats grouped\n  -- Filtering system queries\n  select qsq.query_id,\n  \t\tmax(qsqt.query_sql_text) query_sql_text,\n  \t\tmax(qsp.plan_id) plan_id, \n  \t\tmax(qsrs.max_duration) max_duration,\n  \t\tmax(qsrs.max_cpu_time) max_cpu_time,\n  \t\tmin(qsrs.min_cpu_time) min_cpu_time,\n  \t\tmin(qsrs.min_duration) min_duration,\n  \t\tmax(qsrs.stdev_duration) stdev_duration,\n  \t\tmax(qsrs.stdev_cpu_time) stdev_cpu_time\n  from sys.query_store_query qsq, \n  \t\tsys.query_store_query_text qsqt,\n  \t\tsys.query_store_plan qsp,\n  \t\tsys.query_store_runtime_stats qsrs\n  where qsq.query_text_id= qsqt.query_text_id \n  \tand qsp.query_id=qsq.query_id\n  \tand qsrs.plan_id=qsp.plan_id\n  \tand (qsq.query_parameterization_type<>0\n         or qsqt.query_sql_text like '%@%')\n  \tand qsq.is_internal_query=0\n         and qsqt.query_sql_text not like '%sys.%' \n  \tand qsqt.query_sql_text not like '%sys[ ].%'\n         and qsqt.query_sql_text not like '%@[sys@].%'  escape '@'\n         and qsqt.query_sql_text not like '%INFORMATION_SCHEMA%'\n         and qsqt.query_sql_text not like '%msdb%' \n  \tand qsqt.query_sql_text not like '%master%'\n  \tand qsp.last_execution_time=(select max(last_execution_time)\n  \t\t\t\t\tfrom sys.query_store_plan qsp2\n  \t\t\t\t\twhere qsp2.query_id= qsp.query_id)\n  group by qsq.query_id\n  )\n  GO<\/pre>\n
        Now It\u2019s time to do a step-by-step walk-through that demonstrates how our query can identify parameterization problems.

        Read also: <\/strong>Blocked process troubleshooting<\/span><\/a><\/p>\n
        Identifying Parameterization Problems Via Query Store<\/h2>\n
          \n
        1. First, let\u2019s create a stored procedure. Parameterization problems can happen with ad-hoc queries and stored procedures, let\u2019s do an example with both.\n
          use AdventureWorks2016\n  go\n  create procedure QueryPrice @p money\n  as\n  \tselect * from bigproduct where listprice=@p\n  go<\/pre>\n<\/li>\n
        2. Let\u2019s turn query store on for \u2018AdventureWorks2016\u2019<\/em> database\n
          ALTER DATABASE [AdventureWorks2016] SET QUERY_STORE = ON\n  GO<\/pre>\n<\/li>\n
        3. We need to execute two queries with parameterization problems several times\n
          select * from bigproduct where listprice=245.01\n  go 50\n  select * from bigproduct where listprice=0.00\n  go 50<\/pre>\n<\/li>\n
        4. It\u2019s also interesting for the example to execute two more queries that have no parameterization problems.\n
          SELECT * FROM Sales.CreditCard WHERE CreditCardID = 11\n  go 50\n  SELECT * FROM Sales.CreditCard WHERE CreditCardID = 12\n  go 50<\/pre>\n<\/li>\n
        5. For a complete example, let\u2019s end off by also executing a stored procedure that has parameterization problems\n
          exec QueryPrice 245.01\n  go 50\n  exec QueryPrice 0\n  go 50<\/pre>\n<\/li>\n
        6. Finally, let\u2019s use our query against query store\n
          select * from dbo.QueriesWithParameterizationProblems()\n  where query_sql_text not like '%plancache%'\n  order by stdev_cpu_time desc<\/pre>\n
          The image below shows the query result:<\/p>\n
          <\/p>\n
          There are some points you may have noticed:<\/p>\n