Date Range SQL Server Query Performance Issues with Parameter Sniffing

Problem

I often get asked why SQL Server ignores an index. One of the most interesting reasons for this is Parameter Sniffing. I’m writing this tip to show how to overcome Parameter Sniffing so you can optimize queries.

Solution

Imagine you work with a stored procedure that takes two input parameters, start date and end date. One person passes a small date range to the procedure, whereas another passes a large date range. Indeed, there could be two search modes based on the amount of data, and therefore one index is inefficient for both date range selections. You create another index to support both search modes, but the query optimizer always uses only one.

It is important to understand that when you execute a stored procedure for the first time, SQL Server builds an execution plan based on the values of the input parameters passed during that initial execution. SQL Server puts the created execution plan in the plan cache for the later executions.  Running the stored procedure again, even with different input parameter values, SQL Server will use the plan available in cache, it does not create a new plan. This could result in a Parameter Sniffing concern.

Secondly, note that SQL Server 2022 comes with a feature called Parameter Sensitive Plan Optimization (PSPO), intended to resolve Parameter Sniffing concerns.  Unfortunately, PSPO can’t handle this example. It only tries to mitigate the Parameter Sniffing issue.

For this tip, I will show how the Parameter Sniffing issue occurs and provide a simple solution to solve it.

Set Up Test Environment

Let’s start by setting up our environment.  I’m using SQL Server 2022 and will use the StackOverflow database. To use this open-source database, go to StackOverflow.com or download it from this link.

I want to utilize the features in SQL Server 2022 CU 7, so I’ve set it to compatibility level 160.

ALTER DATABASE StackOverflow SET Compatibility_Level = 160
GO

There is a table called Users in the StackOverflow database that contains user information, like DisplayName, CreationDate, etc. Let’s create a new table using the same structure and contents as the Users table.

DROP TABLE IF EXISTS [dbo].[Users_new];
GO
 
CREATE TABLE [dbo].[Users_new](
   [Id] [int] IDENTITY(1,1),
   [AboutMe] [nvarchar](max) NULL,
   [Age] [int] NULL,
   [CreationDate] [datetime] NOT NULL,
   [DisplayName] [nvarchar](40) NOT NULL,
   [DownVotes] [int] NOT NULL,
   [EmailHash] [nvarchar](40) NULL,
   [LastAccessDate] [datetime] NOT NULL,
   [Location] [nvarchar](100) NULL,
   [Reputation] [int] NOT NULL,
   [UpVotes] [int] NOT NULL,
   [Views] [int] NOT NULL,
   [WebsiteUrl] [nvarchar](200) NULL,
   [AccountId] [int] NULL)
GO
 
SET IDENTITY_INSERT dbo.Users_new ON;
GO
 
INSERT INTO dbo.Users_new With (Tablock) ([Id], [AboutMe], [Age], [CreationDate], [DisplayName], [DownVotes], [EmailHash], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [WebsiteUrl], [AccountId])
SELECT [Id],[AboutMe], [Age], [CreationDate], [DisplayName], [DownVotes], [EmailHash], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [WebsiteUrl], [AccountId]
FROM dbo.Users;
GO
 
SET IDENTITY_INSERT dbo.Users_new OFF;
GO

After creating and populating the table, create a unique clustered index using the CreationDate and Id columns as the keys. Additionally, I created a non-clustered columnstore index on the CreationDate and Reputation columns.

CREATE UNIQUE CLUSTERED INDEX IX_CreationDate_Id ON dbo.Users_new (CreationDate, Id) WITH (DATA_COMPRESSION = PAGE)
GO
 
Create Nonclustered Columnstore Index IXNCI_1 On Users_New(CreationDate, Reputation)
GO

If we execute the following query, SQL Server will perform a clustered index seek operation to retrieve and display the results. This query retrieves the maximum reputation for each month within the date range of ‘2018-03-01’ to ‘2018-06-01’.

Select Month(CreationDate) As Monthly, Max(Reputation) AS MaxReputation 
From dbo.Users_New 
Where CreationDate Between '2018-03-01' And '2018-06-01'
Group By Month(CreationDate)
Order By MAX(Reputation)
GO

As you can see in the image below, a clustered index seek operation was performed.

The first query used a date range of three months.

I will re-run the query with a date range of one year. To compare query execution times, I will enable the statistics time feature to track the elapsed time during query execution.

SET STATISTICS TIME ON
GO 
Select Month(CreationDate), Max(Reputation) 
From dbo.Users_New 
Where CreationDate Between '2017-01-01' And '2018-01-01'
Group By Month(CreationDate)
Order By MAX(Reputation) 
GO

SQL Server executed the query with a non-clustered columnstore index scan operation to display the results.

Below, you can see that the execution time of the query was 317 milliseconds.

So far, so good, each query uses the intended index.

Running Test with a Stored Procedure

Keep in mind the potential effects of combining a query into a stored procedure. Below, we create a procedure that takes two parameters and runs the same query.

Create Procedure [dbo].[USP_GetMaxReputation]
(@SDate Datetime, @EDate DateTime)
AS
Select Month(CreationDate), Max(Reputation) From dbo.Users_New 
Where CreationDate Between @SDate And @EDate
Group By Month(CreationDate)
Order By MAX(Reputation)
GO

Once the stored procedure is created, I will execute it using a three-month time frame.

Exec [USP_GetMaxReputation] '2018-03-01', '2018-06-01'
GO

As expected, SQL Server utilized a clustered index seek operation, as demonstrated in the image below.

Next, I will execute the procedure with a date range of one year.

Exec [USP_GetMaxReputation] '2017-01-01', '2018-01-01'
GO

SQL Server did not utilize the columnstore index but instead performed a clustered index seek operation.

Note the procedure execution time below. It is approximately four times slower than when SQL Server utilized the non-clustered columnstore index. This issue is related to Parameter Sniffing.

Let’s run the test again but with the addition of the OPTION RECOMPILE hint in the stored procedure.

Alter Procedure [dbo].[USP_GetMaxReputation]
(@SDate Datetime, @EDate DateTime)
AS
Select Month(CreationDate), Max(Reputation) From dbo.Users_New 
Where CreationDate Between @SDate And @EDate
Group By Month(CreationDate)
Order By MAX(Reputation)
Option (Recompile)
GO

Now, let’s execute it using a three-month time frame and then with a one-year date range.

Exec [USP_GetMaxReputation] '2018-03-01', '2018-06-01'
GO
Exec [USP_GetMaxReputation] '2017-01-01', '2018-01-01'
GO

For the second execution, SQL Server is now using the non-clustered columnstore scan operation when given a one-year time period.

The execution time has decreased to 329 milliseconds.

Summary

This solved the issue by having SQL Server create a new query plan for each execution of the stored procedure and therefore using the most appropriate index. Using the OPTION (RECOMPILE) hint is not necessarily the best choice for heavily used stored procedures. When a stored procedure is frequently executed with this hint, an increase in CPU usage is common. So, this may be a good solution for some cases, but maybe not every instance. You will need to test in your environment to see what works best.

Next Steps

Check out the following tips:

• Parameter Sniffing Issue with Temporary Tables in SQL Server 2022
• Improve SQL Server Query Performance when Clustered Index Seek Operation is Slow
• SQL Server Execution Plan Issues when using TOP Clause and Various Parameters

Mehdi Ghapanvari

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. To enhance the performance of the database, he has implemented various performance-tuning techniques, including query optimization and index tuning. He has also developed backup and recovery strategies to protect critical data in case of disasters. Mehdi currently manages the SQL Server databases for a large organization.  Mehdi is actively seeking new opportunities to apply his expertise and contribute to impactful projects. You can reach him at SQLDBA.Mehdi@Gmail.com.

• MSSQLTips Awards: Rookie of the Year – 2023
• MSSQLTips Trendsetter Award: 2026