How to Use HINT and DISABLE_PARAMETER_SNIFFING

Parameter sniffing is an optimization technique that allows using values of module parameters (for example, values of a stored procedure or function) at the time of the first call to estimate the expected amount of rows when building an execution plan.

Parameter sniffing is useful. However, this technique does not work properly if parameter values differ drastically by selectivity.  For example, if you select 99% of table rows for one parameter value and 1% of table rows – for the second one, the server may benefit from using different plans. Thus, one plan will be more efficient for a larger number of rows, while the second one – for a smaller number.

However, if parameter sniffing works properly, the plan is going to be built for the value that was passed at the first call. When the value of the parameter is modified so that the procedure should return more rows, the plan will remain effective for only a small number of rows.

Let’s consider this particular example.

Parameter Sniffing description

In this demo, I create a test table containing 1 million rows with clustered and non-clustered indexes. Then I create a procedure that calculates the total for the rows where the b column is less than the passed parameter. Pay attention to the new CREATE OR ALTER syntax available in the SQL Server starting with SP1 2016 version.

use opt;

go

— create a table containing 1 million of rows

drop table if exists t;

with cte as (select top(1000000) rn = row_number() over(order by (select null)) from sys.all_columns c1,sys.all_columns c2)

select a = rn, b = rn, c = rn into t from cte;

— create clustered and non-clustered indexes

create clustered index cix on t(a);

create index ix_b on t(b);

go

— create a procedure that calculates the total for the rows

create or alter procedure p_sniff

    @b int

as

set nocount on;

select sum(t.c) from dbo.t where t.b <= @b;

go

Now, let’s run the procedure with @b = 1 and then @b = 10000. It is obvious that the higher the parameter value is, the more rows SQL Server will have to read. Then, mark the procedure for the recompilation and repeat calls backward.

— 1.

set statistics io, xml on;

exec p_sniff 1;

exec p_sniff 10000;

set statistics io

, xml off;

go

exec sp_recompile p_sniff;

go

— 2.

set statistics io, xml on;

exec p_sniff 10000;

exec p_sniff 1;

set statistics io, xml off;

go

It is better to use a non-clustered index for a small number of rows. It is not a covered index and it does not contain the all required fields. You can perform Key Lookup to get all necessary fields. This happens at the first call of the procedure. I would like to draw your attention to the Parameter List properties of the SELECT statement. As you can see, Parameter Compiled Value (a value for which the procedure has been compiled) and Parameter Runtime Value (a value with which the procedure has been executed indeed) match.

(image – https://codingsight.com/wp-content/uploads/2017/05/09.png)

When running the query for the second time, the parameter value is 10000 and it is necessary to read more rows. SQL Server can also search in the non-clustered index and access the clustered index for missing values. However, it is a random access operation. As the number of rows increases, more random access operations occur.

Normally SQL Server may refuse this strategy, as the random access is much slower compared to the sequential one, and will simply scan the clustered index. However, due to the parameter sniffing, the plan will remain the same as for the first parameter.

(image – https://codingsight.com/wp-content/uploads/2017/05/10.png)

As you can see, the parameter value differs from the value with which the procedure was executed. Thus, we have many logic reads:

Table ‘t’. Scan count 1, logical reads 6, physical reads 0, …

Table ‘t’. Scan count 1, logical reads 30031, physical reads 0, …

Hereafter, we mark the procedure for recompilation and run calls backward. Now, when executing the plan for the first time, it will be built for the value 10 000, and when running it for the second time, the same plan will be used.

(image – https://codingsight.com/wp-content/uploads/2017/05/11.png)

As you can see, now logic reads match as the volume of data is the same – a full scan of the cix clustered index.

Table ‘t’. Scan count 1, logical reads 4097, physical reads 0, …

Table ‘t’. Scan count 1, logical reads 4097, physical reads 0, …

The second plan is universal and it will always take the same average time to process data, regardless of the parameter value. The first plan will work properly when it returns a small number of rows, rather than a large number of rows.

I would like to add that there are many ways of parameter sniffing. Here are some of them:

• Using different code branches;
• Using dynamic SQL;
• changing parameters with local variables;
• using recompile and optimize for hints;
• using the trace flag 4136, etc.

Starting with SQL Server 2016 SP, we can also disable the parameter sniffing using the DISABLE_PARAMETER_SNIFFING hint.

In addition, it is possible to use the database setting if you want to disable parameter sniffing for the whole database (the setting is also available for a secondary one):

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

GO

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = OFF;

GO

DISABLE_PARAMETER_SNIFFING hint

Let’s check how the DISABLE_PARAMETER_SNIFFING hint works. To do this, we will use the data from the first example:

— create a procedure that calculates the total depending on the parameter

create or alter procedure p_sniff

    @b int

as

set nocount on;

select sum(t.c) from dbo.t where t.b <= @b option(use hint (‘DISABLE_PARAMETER_SNIFFING’));

go

— 1.

set statistics io, xml on;

exec p_sniff 1;

exec p_sniff 10000;

set statistics io, xml off;

go

exec sp_recompile p_sniff;

go

— 2.

set statistics io, xml on;

exec p_sniff 10000;

exec p_sniff 1;

set statistics io, xml off;

go

In all the cases, we see the same plan with the scan of the clustered index. In this case, the optimization is performed as if the value of the @b parameter were unknown (the expected estimation of rows is 30 % of rows in the table (300 000 from 1 000 000).

(image – https://codingsight.com/wp-content/uploads/2017/05/12.png)

It should be noted that there is no Parameter Compiled Value, as the compilation is used for an unknown value. There is a small difference when using the trace flag 4136, Parameter Compiled Value keeps the value that was passed at the first execution. However, it is not used at the compile time.

Difference from OPTIMIZE FOR UNKNOWN

The difference between the DISABLE_PARAMETER_SNIFFING hint from the OPTIMIZE FOR UNKNOWN hint is that using the OPTIMIZE FOR UNKNOWN hint, you can specify the particular parameter, which you need to optimize as if its value were unknown. If there are other parameters in the query, then it will be optimized with the @b parameter as unknown, and the others will be sniffed, while the DISABLE_PARAMETER_SNIFFING hint disables sniffing of all parameters.

There is also another difference when you use these hints together with the RECOMPILE hint. Let’s check it on this particular example:

create or alter procedure p_sniff

    @b int

as

set nocount on;

select sum(t.c) from dbo.t where t.b <= @b option(recompile, optimize for unknown);

select sum(t.c) from dbo.t where t.b <= @b option(recompile, use hint (‘DISABLE_PARAMETER_SNIFFING’));

go

exec p_sniff 1;

go

Despite the fact that in the first query, we use RECOMPILE, the query is nevertheless optimized as if the parameter value were unknown at the recompilation stage. In the second case, despite the fact that parameter sniffing is disabled, the server uses the parameter value at the recompilation stage, as well as it applies Parameter Embedding Optimization. Thus, it replaces the parameters with their values as if there were initial constants in the query.

As you can see, we have different plans:

(image – https://codingsight.com/wp-content/uploads/2017/05/13.png)

Conclusion

dbForge Studio for SQL is a fantastic option if you need a sophisticated SQL Server GUI application for Mac or Linux to deal with SQL Server databases on many operating systems. This complete package of tools is meant to make working with SQL Server databases simpler and quicker and is not confined to Windows. dbForge Studio for SQL is compatible with most operating systems, including Mac and Linux.

The ability to utilize HINT and DISABLE PARAM SNIFFING is one of the features that make dbForge Studio for SQL particularly handy. This feature allows users to adjust the behavior of the SQL Server query optimizer, hence optimizing the performance of SQL Server databases.

Using the values of module parameters at the time of the initial call, parameter sniffing is an optimization method that enables SQL Server to predict the expected amount of rows while constructing an execution plan. Nevertheless, this method is not always effective if parameter values vary significantly by selectivity. In such instances, it may be more efficient to employ alternative strategies.

dbForge Studio for SQL’s HINT and DISABLE PARAMETER SNIFFING capabilities allow users to enhance the performance of their SQL Server databases by regulating the behavior of the query optimizer. Users may execute Key Lookups, which can be more efficient for small amounts of rows, and use non-clustered indexes to accelerate query execution with this feature.

dbForge Studio for SQL is more than simply a Windows SQL Server graphical user interface application. It is a cross-platform application that operates on both Mac and Linux, making it a versatile and potent solution for developers and database administrators that employ multiple operating systems. In addition to its sophisticated SQL editor with code completion, customizable code snippets, and syntax highlighting, the toolset includes, among others, a robust schema comparison tool, a data comparison tool, and a code debugging tool. And with support for many SQL Server versions and interaction with SQL Server Management Studio, dbForge Studio for SQL is a comprehensive set of tools that may facilitate your SQL Server database management.

Read More: World News | Entertainment News | Celeb News
Tech Follows