Parameter Sniffing Issue with Temporary Tables in SQL Server 2022

By:   |   Updated: 2023-09-20   |   Comments   |   Related: > SQL Server 2022


Problem

Occasionally, there is a need to transfer data between two stored procedures. One technique for doing this is to use temporary tables. In this tip, I will demonstrate that the Parameter Sniffing problem persists in SQL Server 2022 when data is stored in a temporary table. I will also present a workaround for this problem.

Solution

Sometimes a stored procedure or parameterized query is super fast and sometimes very slow. Parameter Sniffing is one of the primary causes of this issue. When a stored procedure is executed for the first time, SQL Server generates an execution plan based on the specific set of parameters used during that execution. The generated plan is cached and stored in the plan cache for subsequent executions. While this feature is beneficial, it can occasionally lead to the Parameter Sniffing issue which can have big impacts on performance.

Because of the parameter sniffing issue, a stored procedure may require two different execution plans. It may be necessary to have one execution plan for handling small amounts of data and another execution plan for handling big amounts of data. SQL Server 2022 tries to solve the problem of Parameter Sniffing but as we will see this can still be an issue.

Using SQL Server 2022 CU7, I will create a stored procedure that returns many records for the first parameter value and a few records for the second parameter value. The procedure stores the results in a temporary table for use in another procedure.

Setup Test Environment

I'll use the StackOverflow database and set the compatibility level to 160:

Use StackOverflow
GO
Alter Database Current Set Compatibility_Level = 160
GO

I want to create an index on the Location column in the Users table:

Create Index IX_Location On dbo.Users (Location)
GO

To get IO statistics, use the command below:

SET STATISTICS IO ON
GO

Below is a script that implements a procedure for retrieving users based on their location:

Create or Alter Procedure USP_FindUsersbyLocation
(@Location Nvarchar(100))
AS
 Begin
  Select * From dbo.Users Where Location = @Location
 End
GO

I will retrieve information on users located in India:

Exec USP_FindUsersbyLocation N'India'
GO

The following image shows that SQL Server used a clustered index scan operation to retrieve data:

IndiaWithoutTempTablePlan

Take note of the count of logical reads:

IndiaWithoutTempTableIO

SQL Server has read over 142,000 pages. Next, I'll execute the procedure using 'California, CA' as the parameter value.

Exec USP_FindUsersbyLocation N'California, CA'
GO

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

CAWithoutTempTablePlan

Pay attention to the image below. SQL Server only read 12 pages to retrieve the results, as there are only three records with the location 'California, CA' in the Users table:

CAWithoutTempTableIO

SQL Server 2022 has been performing exceptionally well thus far.

What would happen if we provided 'USA' as an input to the procedure? 

Exec USP_FindUsersbyLocation N'USA'
GO
USAPlan

As you can see in the image above, SQL Server utilized a clustered index scan operation to retrieve and display the records. As a result of a Parameter Sniffing issue, SQL Server has selected an incorrect execution plan. If we run the following ad hoc query, SQL Server will use a non-clustered index seek followed by a key lookup, as depicted in the image below:

Select * From dbo.Users Where Location = 'USA'
GO
USAAdHocPlan

While this may not be ideal, it is still better than nothing.

As mentioned earlier in this article, the Parameter Sniffing problem persists in SQL Server 2022 when data is stored in a temporary table. To demonstrate, I will modify the procedure to store the results in a temporary table. If another procedure is called within this procedure, it can use the data stored in the temporary table.

Create Or Alter Procedure USP_FindUsersbyLocation
(@Location Nvarchar(100))
AS
Create Table #Users
(
   [Id] [int]  NOT NULL,
   [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
)
 
Insert Into #Users
Select * From dbo.Users Where Location = @Location
/* Another procedure will be executed that will use data from the temporary table. */
GO

I will call the procedure with 'India' as a parameter value:

Exec USP_FindUsersbyLocation N'India'
GO

The following image shows SQL Server used a clustered index scan operation:

IndiaPlanTempTable_1

Now, I will call the procedure again with 'California, CA' as the parameter value.

Exec USP_FindUsersbyLocation N'California, CA'
GO
CAPlanTempTable_1

As you can see in the image above, SQL Server unfortunately used a clustered index scan operation.

Take note of the number of logical reads:

CAIOTempTable

The number of logical reads is about 142,000. Only three records were retrieved despite the number of logical reads.

What's the Solution?

Within this version of the StackOverflow database, there are merely two locations that are densely populated. Let's create a table and insert these locations into it.

Drop Table If Exists CrowdedPlaces

Create Table CrowdedPlaces
(Id Int Not Null Identity Primary Key, Location Nvarchar(100) Not Null UNIQUE )
GO

Insert Into CrowdedPlaces Values (N'India'), (N'Bangalore, Karnataka, India')
GO

The following script creates two stored procedures that contain identical code and embeds them inside another stored procedure.

Create Or Alter Procedure USP_Crowded
(@Location Nvarchar(100))
AS
Insert Into #Users
Select * From dbo.Users Where Location = @Location
GO

Create Or Alter Procedure USP_UnderPopulated
(@Location Nvarchar(100))
AS
Insert Into #Users
Select * From dbo.Users Where Location = @Location
GO

Create Or Alter Procedure USP_FindUsersbyLocation
(@Location Nvarchar(100))
AS
Create Table #Users
(
   [Id] [int]  NOT NULL,
   [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
)
 
If Exists (Select 1 From dbo.CrowdedPlaces Where Location = @Location)
 Begin
  Exec USP_Crowded @Location
 End
Else
 Begin
  Exec USP_UnderPopulated @Location
 End
/* Another procedure will be executed that will use data from the temporary table. */ 
GO

I will now execute my test once more:

Exec USP_FindUsersbyLocation N'India'
GO

The image below shows that SQL Server used a clustered index scan operation to retrieve data related to India.

IndiaPlanTempTable_2

The number of logical reads is about 142,000:

IndiaTempTableIO_2

I will call the procedure again with 'California, CA' as the parameter value:

Exec USP_FindUsersbyLocation N'California, CA'
GO

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

CAPlanTempTable_2

As shown in the image below, the number of logical reads is only 12. That's great.

CaWithTempTableIO_2

This was an example, and I want to tell a true story. There are five overcrowded provinces in a country where people migrate permanently. These provinces need a separate execution plan. In such cases, the technique described in this article can be employed to address the Parameter Sniffing issue.

Summary

SQL Server 2022 tries to solve the problem of Parameter Sniffing. The problem persists in SQL Server 2022 when data is stored in a temporary table. In this article, I presented a solution. However, if the procedure is not executed frequently and CPU usage is not a concern on your server, you can utilize the 'OPTION (RECOMPILE)' query hint.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author 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.

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-09-20

Comments For This Article

















get free sql tips
agree to terms