Parameter Sniffing

Why is my query sometimes fast,
and sometimes terribly slow?

Even weirder, it just gets slow out of nowhere – even when you swear you haven’t changed anything.

Why Is This Query Sometimes Slow? – I show you how parameter sniffing happens, and why reboots/restarts seem to fix it, but it keeps coming back.

How to Start Troubleshooting Parameter Sniffing – a brief introduction of what parameter sniffing is, how to fix it during an emergency, and how to gather the data you’ll need to fix it right for the long term.

PSPO: How SQL Server 2022 Tries to Fix It – there’s a lot to like about this new feature, but one horrible, terrible, no-good, very bad downside that stops folks from using it.

Optimize for Unknown (Mediocre) – this query hint gives you a predictable plan, but it’s just usually predictably BAD, not predictably good.

Slow in the App, Fast in SSMS – Erland Sommarskog’s epic novel (dozens of pages long) about the causes and fixes for parameter sniffing and different execution plans.

My training classes – I offer a 1-day Fundamentals of Parameter Sniffing class, and then a 3-day Mastering Parameter Sniffing class for folks who have to fix this stuff a lot.

Session, Slides, Resources

Here’s a PDF slide version of the talk that includes the latest improvements in SQL Server 2022.

Here’s an all-demo session at SQLDay Poland:

Here is the latest version of the demo scripts I use in the demo:

/*
Why is the Same Query Sometimes Slow?
Introducing Parameter Sniffing

v1.1 - 2022-10-18

https://www.brentozar.com/go/sniff


This demo requires:
* Any supported version of SQL Server or Azure SQL DB
* Any Stack Overflow database: https://www.brentozar.com/go/querystack

This first RAISERROR is just to make sure you don't accidentally hit F5 and
run the entire script. You don't need to run this:
*/
RAISERROR(N'Oops! No, don''t just hit F5. Run these demos one at a time.', 20, 1) WITH LOG;
GO


/* And this stored procedure drops all nonclustered indexes: */
DropIndexes;
GO
/* If you're on SQL Server, run this to set
the database to the latest compatibility level
that your SQL Server supports, prior to 2022: */
DECLARE @StringToExec NVARCHAR(4000);
SELECT @StringToExec = N'ALTER DATABASE CURRENT SET compatibility_level = '
	+ CASE WHEN compatibility_level > 150 THEN N'150'
		ELSE CAST(compatibility_level AS NVARCHAR(10)) END
	FROM sys.databases
	WHERE name = 'master';
EXEC(@StringToExec);
GO
EXEC sys.sp_configure N'show advanced', N'1'
GO
RECONFIGURE;
GO
EXEC sys.sp_configure N'cost threshold for parallelism', N'50' /* Keep small queries serial */
GO
EXEC sys.sp_configure N'max degree of parallelism', N'4' /* Let queries go parallel */
GO
RECONFIGURE
GO
/* Turn on actual execution plans and: */
SET STATISTICS IO, TIME ON;
GO

CREATE INDEX Reputation ON dbo.Users(Reputation)
GO

SELECT TOP 10000 *
FROM dbo.Users
WHERE Reputation = 2
ORDER BY DisplayName;
GO

SELECT TOP 10000 * 
FROM dbo.Users
WHERE Reputation = 1
ORDER BY DisplayName;
GO


/* How SQL Server decides plans: */
DBCC SHOW_STATISTICS('dbo.Users', 'Reputation');
GO

CREATE OR ALTER PROCEDURE dbo.usp_UsersByReputation
  @Reputation int
AS
SELECT TOP 10000 *
FROM dbo.Users
WHERE Reputation=@Reputation
ORDER BY DisplayName;
GO
DBCC FREEPROCCACHE;
EXEC dbo.usp_UsersByReputation @Reputation = 2;
GO
EXEC dbo.usp_UsersByReputation @Reputation = 1;
GO

ALTER TABLE dbo.Users REBUILD;
GO
EXEC dbo.usp_UsersByReputation @Reputation = 1;
GO
EXEC dbo.usp_UsersByReputation @Reputation = 2;
GO
sp_BlitzCache
GO


/* If you're on SQL Server 2022, you can see how
Parameter Sensitive Plan Optimization (PSPO) works:
*/
ALTER DATABASE CURRENT 
	SET COMPATIBILITY_LEVEL = 160; /* 2022 */
GO
EXEC dbo.usp_UsersByReputation @Reputation = 2;
GO
EXEC dbo.usp_UsersByReputation @Reputation = 1;
GO
sp_BlitzCache
GO

/*
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
More info: https://creativecommons.org/licenses/by-sa/4.0/

You are free to:
* Share - copy and redistribute the material in any medium or format
* Adapt - remix, transform, and build upon the material for any purpose, even 
  commercially

Under the following terms:
* Attribution - You must give appropriate credit, provide a link to the license, 
  and indicate if changes were made. You may do so in any reasonable manner, 
  but not in any way that suggests the licensor endorses you or your use.
* ShareAlike - If you remix, transform, or build upon the material, you must
  distribute your contributions under the same license as the original.
*/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

Why is the Same Query Sometimes Slow?

Introducing Parameter Sniffing

v1.1 - 2022-10-18

https://www.brentozar.com/go/sniff

This demo requires:

* Any supported version of SQL Server or Azure SQL DB

* Any Stack Overflow database: https://www.brentozar.com/go/querystack

This first RAISERROR is just to make sure you don't accidentally hit F5 and

run the entire script. You don't need to run this:

RAISERROR(N'Oops! No, don''t just hit F5. Run these demos one at a time.', 20, 1) WITH LOG;

/* And this stored procedure drops all nonclustered indexes: */

DropIndexes;

/* If you're on SQL Server, run this to set

the database to the latest compatibility level

that your SQL Server supports, prior to 2022: */

DECLARE @StringToExec NVARCHAR(4000);

SELECT @StringToExec = N'ALTER DATABASE CURRENT SET compatibility_level = '

+ CASE WHEN compatibility_level > 150 THEN N'150'

ELSE CAST(compatibility_level AS NVARCHAR(10)) END

FROM sys.databases

WHERE name = 'master';

EXEC(@StringToExec);

EXEC sys.sp_configure N'show advanced', N'1'

RECONFIGURE;

EXEC sys.sp_configure N'cost threshold for parallelism', N'50' /* Keep small queries serial */

EXEC sys.sp_configure N'max degree of parallelism', N'4' /* Let queries go parallel */

RECONFIGURE

/* Turn on actual execution plans and: */

SET STATISTICS IO, TIME ON;

CREATE INDEX Reputation ON dbo.Users(Reputation)

SELECT TOP 10000 *

FROM dbo.Users

WHERE Reputation = 2

ORDER BY DisplayName;

SELECT TOP 10000 *

FROM dbo.Users

WHERE Reputation = 1

ORDER BY DisplayName;

/* How SQL Server decides plans: */

DBCC SHOW_STATISTICS('dbo.Users', 'Reputation');

CREATE OR ALTER PROCEDURE dbo.usp_UsersByReputation

@Reputation int

SELECT TOP 10000 *

FROM dbo.Users

WHERE Reputation=@Reputation

ORDER BY DisplayName;

DBCC FREEPROCCACHE;

EXEC dbo.usp_UsersByReputation @Reputation = 2;

EXEC dbo.usp_UsersByReputation @Reputation = 1;

ALTER TABLE dbo.Users REBUILD;

EXEC dbo.usp_UsersByReputation @Reputation = 1;

EXEC dbo.usp_UsersByReputation @Reputation = 2;

sp_BlitzCache

/* If you're on SQL Server 2022, you can see how

Parameter Sensitive Plan Optimization (PSPO) works:

ALTER DATABASE CURRENT

SET COMPATIBILITY_LEVEL = 160; /* 2022 */

EXEC dbo.usp_UsersByReputation @Reputation = 2;

EXEC dbo.usp_UsersByReputation @Reputation = 1;

sp_BlitzCache

License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)

More info: https://creativecommons.org/licenses/by-sa/4.0/

You are free to:

* Share - copy and redistribute the material in any medium or format

* Adapt - remix, transform, and build upon the material for any purpose, even

commercially

Under the following terms:

* Attribution - You must give appropriate credit, provide a link to the license,

and indicate if changes were made. You may do so in any reasonable manner,

but not in any way that suggests the licensor endorses you or your use.

* ShareAlike - If you remix, transform, or build upon the material, you must

distribute your contributions under the same license as the original.