Correct SQL Server Parameter Sniffing when using TOP Clause

By: Mehdi Ghapanvari | Updated: 2023-08-22 | Comments | Related: > Performance Tuning

Problem

A query is sometimes super fast and sometimes very slow. One of the main reasons for this problem is Parameter Sniffing. There are several approaches to addressing the Parameter Sniffing issue and this article discusses effective methods for handling Parameter Sniffing in SELECT statements containing a TOP clause.

Solution

The TOP clause is often used in SQL queries to limit the number of rows returned. I will explain how we can leverage indexing techniques and incorporate an ORDER BY clause to resolve the Parameter Sniffing issue.

What is Parameter Sniffing?

The first time a procedure is executed with a particular set of parameters, SQL Server generates a query plan based on those parameters. Once stored in the plan cache, SQL Server will reuse the generated plan for subsequent executions of the procedure. The stored plan is not optimal when the values queried in a table are not evenly distributed. Imagine one parameter value returns 50,000 records and the other returns only 4. If the Parameter Sniffing issue is not present, SQL Server will perform a table scan of 50,000 records. The most efficient way to retrieve four records is to use a non-clustered index seek followed by a key lookup. However, due to Parameter Sniffing, SQL Server may not choose one of these two methods to execute the queries.

Set Up Test Environment

I'll use the StackOverflow database and create an index on the Location column in the Users table. The StackOverflow database is an open-source database from Stack Overflow.com.

CREATE INDEX IX_Location ON dbo.Users (Location)
WITH (DATA_COMPRESSION = PAGE)
GO

To change the compatibility level to SQL Server 2019 (150), use the following command:

Use master
GO
Alter Database StackOverflow Set Compatibility_Level = 150
GO

To get IO statistics, use the command below:

SET STATISTICS IO ON
GO

I will retrieve the top 100 people who reside in India.

Use StackOverflow
GO
Select Top 100 * From dbo.Users Where [Location] = N'India'
GO

Look at the actual execution plan:

SQL Server used a clustered index scan to retrieve the results. Query execution time is a few milliseconds. Note: There is no ORDER BY clause in the query.

The number of logical reads is 1113, as shown in the image below:

The following query retrieves the top 100 people who reside in Bergkamen, Germany:

Select Top 100 * From dbo.Users Where [Location] = N'Bergkamen, Germany'
GO

SQL Server retrieved the results using a non-clustered index seek followed by a key lookup, as depicted in the image below:

SQL Server only read 15 pages to retrieve the results, as there are only four records with the location 'Bergkamen, Germany' in the Users table:

To demonstrate the problem of Parameter Sniffing, I will copy the query above and place it inside a stored procedure:

Create Or Alter Procedure dbo.USP_FindPeopleByLocation
(@Location Nvarchar(100))
AS
Select Top 100 * From dbo.Users Where Location = @Location
GO

After creating the stored procedure, during the first execution, I will pass 'India' as the parameter value.

Exec USP_FindPeopleByLocation N'India'
GO

The image below shows that SQL Server utilized a clustered index scan, completing the execution within a few milliseconds.

There were 1113 logical reads performed, as shown by the number in the output:

What will happen if we pass 'Bergkamen, Germany' as the parameter value to the stored procedure? Look at the following image:

SQL Server employed a clustered index scan operation to retrieve only four records. The TOP operator indicates that the query execution time has increased to over 3 seconds (3.342 s).

Look at the number of logical reads:

SQL Server read over 141 thousand pages to display the four records. This is a Parameter Sniffing issue.

What are the Ways to Address this Problem?

Initially, we can add the 'OPTION(RECOMPILE)' clause to the stored procedure. However, frequent execution of the stored procedure by the application may lead to CPU pressure. So, I would like to include an ORDER BY clause in the stored procedure. For instance, if users wish to view the results sorted by the 'Reputation' column in descending order, we need to modify the stored procedure as follows:

Create Or Alter Procedure dbo.USP_FindPeopleByLocation
(@Location Nvarchar(100))
AS
Select Top 100 * from dbo.Users Where Location = @Location
Order by Reputation Desc
GO

After adding the ORDER BY clause to the stored procedure, I plan to modify the non-clustered index on the Users table:

Drop Index IX_Location On dbo.Users
GO

Create Index IX_Location_Reputation On dbo.Users (Location, Reputation)
With (Data_Compression = Page)
GO

I will run our test again. First, pass 'India' as a parameter value to the stored procedure:

Exec USP_FindPeopleByLocation N'India'
GO

As you can see, SQL Server used a non-clustered index seek followed by a key lookup. Now I will pass 'Bergkamen, Germany' as a parameter value to the stored procedure:

Exec USP_FindPeopleByLocation N'Bergkamen, Germany'
GO

As expected, SQL Server utilized a non-clustered index seek operation followed by a key lookup, resulting in a query execution time of zero seconds.

Look at the number of logical reads – it has been reduced to only 20 pages. That's amazing!

Indexing is one of the best solutions for addressing the Parameter Sniffing issue. However, there are some caveats. For instance, we cannot use the covering index technique when the query returns a large number of columns.

Summary

The Parameter Sniffing problem arises when there is significant unevenness in the data distribution in tables or when users bring in highly uneven parameter values. There are several approaches to addressing the Parameter Sniffing issue. Indexing is one of the best solutions, particularly when the SELECT statement includes a TOP clause or when the query returns a small number of columns that can be covered using a covering index. We cannot always use the 'OPTION(RECOMPILE)' clause, as the procedure is executed frequently, and this would lead to increased CPU usage.

Next Steps

About the author

Mehdi Ghapanvari is an SQL Server database administrator with 6+ years of experience. His main area of expertise is improving database performance. He is skilled at managing high-performance servers that can handle several terabytes of data and hundreds of queries per second, ensuring their availability and reliability.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-08-22

SQL Server Execution Plan Issues when using TOP Clause and Various Parameters