Esat Erkec
Bad parameter sniffing

How to overcome parameter sniffing problems in ad-hoc queries

June 17, 2022 by

The purpose of this article is to provide insights into how parameter sniffing occurs for an ad-hoc query and how it affects their performance.

Case Study: Untouchable legacy code

Users are beginning to complain about the performance of the e-mail send module of the purchase application. This small piece of code finds the number of rows in the email table by taking different parameters. However, this code starts to run poorly in terms of performance as the email table total rows number gets to very large numbers. After the performance of the application is analyzed by the software development team, they realize a legacy subroutine code affects the application’s performance. This routine is only executing an ad-hoc query and this query causes an extremely long waiting time for some parameter values. After that, they write a simulation code to identify the root problem.

VB.net application code

Finally, they diagnose the problem, the application waits for an unacceptable amount of time when a certain value is passed as a parameter to the query. Firstly, the team tries to overcome this problem on the application code. Unfortunately, the team set back making any changes to the source code of the application because they cannot guess its impacts on the application because of the insufficient documentation and then decided to find a solution on the SQL Server side.

In the next sections of this article, we will try to identify the cause of the problem in this case study. Besides this, we will discuss the pros and cons of some alternative solutions. However, firstly, we’ll take a look at some basic concepts before we get into the details.

Pre-Requirements

We’ll create a sample table to use the next parts of this article and populate it with some synthetic data.

What is an ad-hoc query?

Ad-hoc is a Latin origin word and it means “for this specific purpose” in English. An ad-hoc query is a single query that is not involved in any of the pre-defined queries (stored procedure, functions, view, etc.) and it is also non- parameterized queries. For example, the following query is an ad-hoc query:

How is an ad-hoc query stored in the SQL Server query plan cache?

SQL Server can cache the query plan of an ad-hoc query. However, for a query to reuse a cached execution plan, its syntax must be absolutely the same as the cached query. Such as, adding spaces or any comments will be perceived by the optimizer as a new query and will cause a new query plan to be created. The main disadvantage of this working principle is when a database takes intensive ad-hoc query requests the excessive query compilations may cause a heavy workload on the database engine. The following image illustrates different cached query plans for the same query because of the space character.

Ad-hoc query plan cache

Simple and Forced query parameterization

SQL Server query parameterization setting offers simple and forced parameterization options. We can find this setting in the database properties.

SQL query parameterization option

In the simple parameterization mode, the query optimizer may decide to parametrize some simple queries. To do this, the explicitly defined values are parameterized so that the newly generated query plan can be reused for the different values. If an ad-hoc query contains the following expressions the query does not parametrize in simple mode.

  • JOIN
  • IN
  • BULK INSERT
  • UNION
  • INTO
  • DISTINCT
  • TOP
  • GROUP BY
  • HAVING
  • COMPUTE
  • CROSS/ OUTER APPLY
  • Sub Queries

SQL Server will parameterize this simple query and this case can be seen in the query plan cache.

Execution plan of a single parameterized query

Displays the query plan cache

When we run the same query with a different value, the plan created for the previous query will be used.

Bad parameter sniffing

Shows the query plan cache

Now, we’ll execute a query that filters out a high-density value in the table.

Estimated number of rows

Query optimizer decided to use the cached query plan for this high-density value, but this cached query plan does not show an effective performance. When we use the OPTION (RECOMPILE) hint in a query, the optimizer rebuilds the execution plan for every execution of the query. We add this hint to our query and analyze the recompiled execution plan.

SQL 2019 Batch Mode on Rowstore

As we can see, the optimizer totally generates a different query plan for the same query. First, it decided on a parallel query plan and it also uses a batch mode on rowstore feature. Shortly, the problem occurs because the cached query plan performance is suboptimal for this value.

Using sp_create_plan_guide

The sp_create_plan_guide allows us to add some query hints into the queries without explicitly interfering with the query syntax. OPTION (RECOMPILE) hint can be a solution to overcome the parameter sniffing problems so if we inject this hint into the query we can eliminate the problem for this case study. The syntax of the query will be converted into the following command by the System.Data.SqlClient class before sending the SQL Server.

We can add OPTION(RECOMPILE) hint to this query without changing any code of the application through the sp_create_plan_guide procedure as like the below.

We can display the created plan guides with help of the following query.

SQL Server plan guides

After the creation of the plan guide, the queries which are sent from the purchase application mail module will be recompiled. This state of affairs can be monitored through the sql_statement_recompile event.

Output of the sql_statement_recompile

As we can see, the optimizer recompiles the queries without considering the value of all parameters so that we obtain fresh query plans for every execution of the query. As a result, with this change, the performance of the application fell below the limit value. To drop the created plan guide:

Disabling Parameter Sniffing

SQL Server offers a Parameter Sniffing setting at the database level. When we disable this setting, the optimizer considers the average data distribution while compiling a query. This setting can be found under the Database Scoped Configurations.

SQL Server parameter sniffing setting

After disabling the Parameter Sniffing option optimizer will calculate the estimated number of rows using the density vector rather than the statistics histogram.

SQL Server parameter sniffing and statistics calculation

We can obtain the estimated number of rows to multiple the total number of rows with the density.

DBCC SHOW_STATISTICS command usage

Tip: We can use Google for complicated mathematical calculations.

Google complex calculations

Disabling parameter sniffing is not an effective method to get rid of the bad parameter sniffing and it may cause performance problems on the other ad-hoc queries and stored procedures. However, in some special scenarios, it can resolve the parameter sniffing problems.

Conclusion

In this article, we discussed a case study about the ad-hoc query parameter sniffing problems. It is very difficult to resolve parameter sniffing issues without altering the query structures, but for this case, we were lucky and found a solution method.

Esat Erkec
168 Views