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.

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.

Video: Identifying and Fixing Parameter Sniffing

From my 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.0 - 2022-05-10

https://www.BrentOzar.com/go/snifffund


This demo requires:
* Any supported version of SQL Server or Azure SQL DB
* 50GB Stack Overflow 2013 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


USE StackOverflow2013;
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: */
DECLARE @StringToExec NVARCHAR(4000);
SELECT @StringToExec = N'ALTER DATABASE CURRENT SET compatibility_level = '
	+ CAST(compatibility_level AS NVARCHAR(10))
	FROM sys.databases
	WHERE name = 'master';
EXEC(@StringToExec);
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
DBCC SHOW_STATISTICS('dbo.Users', 'Reputation');

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



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

DBCC FREEPROCCACHE
GO
EXEC dbo.usp_UsersByReputation @Reputation = 1;
GO
EXEC dbo.usp_UsersByReputation @Reputation = 2;
GO
sp_BlitzCache
GO


CREATE OR ALTER PROCEDURE dbo.usp_UsersByReputation
  @Reputation int
AS
SELECT TOP 10000 *
FROM dbo.Users
WHERE Reputation=@Reputation
ORDER BY DisplayName
OPTION (OPTIMIZE FOR UNKNOWN);
GO
EXEC dbo.usp_UsersByReputation @Reputation = 1;
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

Why is the Same Query Sometimes Slow?

Introducing Parameter Sniffing

v1.0 - 2022-05-10

https://www.BrentOzar.com/go/snifffund

This demo requires:

* Any supported version of SQL Server or Azure SQL DB

* 50GB Stack Overflow 2013 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;

USE StackOverflow2013;

/* 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: */

DECLARE @StringToExec NVARCHAR(4000);

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

+ CAST(compatibility_level AS NVARCHAR(10))

FROM sys.databases

WHERE name = 'master';

EXEC(@StringToExec);

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)

DBCC SHOW_STATISTICS('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;

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;

DBCC FREEPROCCACHE

EXEC dbo.usp_UsersByReputation @Reputation = 1;

EXEC dbo.usp_UsersByReputation @Reputation = 2;

sp_BlitzCache

CREATE OR ALTER PROCEDURE dbo.usp_UsersByReputation

@Reputation int

SELECT TOP 10000 *

FROM dbo.Users

WHERE Reputation=@Reputation

ORDER BY DisplayName

OPTION (OPTIMIZE FOR UNKNOWN);

EXEC dbo.usp_UsersByReputation @Reputation = 1;

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.

Parameter Sniffing

Video: Identifying and Fixing Parameter Sniffing

Save your cart?