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?

v1.8 - 2019-08-27

I'm going to teach you 4 things:

1. What parameter sniffing is
2. How to react to parameter sniffing emergencies
3. How to test a query with sniffing problems
4. Options to fix the query long term

Learn more later at:
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
* DropIndexes proc: https://www.BrentOzar.com/go/dropindexes
* sp_BlitzCache: https://www.BrentOzar.com/blitzcache
*/
USE StackOverflow2013;
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
EXEC sys.sp_configure N'cost threshold for parallelism', N'50'
GO
EXEC sys.sp_configure N'max degree of parallelism', N'4'
GO
RECONFIGURE
GO
DropIndexes;
GO
/* Turn on actual execution plans and: */
SET STATISTICS IO, TIME ON;
GO

CREATE INDEX IX_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



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

EXEC dbo.usp_UsersByReputation @Reputation =1;
GO
EXEC dbo.usp_UsersByReputation @Reputation =2;
GO

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




DECLARE @Reputation INT = 2

--CREATE PROCEDURE dbo.usp_UsersByReputation
--  @Reputation int
--AS
SELECT TOP 10000 *
FROM dbo.Users
WHERE Reputation=@Reputation
ORDER BY DisplayName;
GO

DBCC SHOW_STATISTICS('dbo.Users', 'IX_Reputation')
GO



CREATE PROCEDURE #usp_UsersByReputation @Reputation int AS
SELECT * FROM dbo.Users
WHERE Reputation= @Reputation
GO

EXEC #usp_UsersByReputation 2
GO




/* "Normal" dynamic SQL: */
CREATE OR ALTER PROCEDURE dbo.usp_UsersByReputation
  @Reputation int
AS
BEGIN
DECLARE @StringToExecute NVARCHAR(4000);
SET @StringToExecute = N'SELECT TOP 10000 * FROM dbo.Users WHERE Reputation=@Reputation ORDER BY DisplayName;';

EXEC sp_executesql @StringToExecute, N'@Reputation INT', @Reputation;
END
GO

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


/* "Bad" dynamic SQL building unparameterized strings: */
CREATE OR ALTER PROCEDURE dbo.usp_UsersByReputation
  @Reputation int
AS
BEGIN
DECLARE @StringToExecute NVARCHAR(4000);
SET @StringToExecute = N'SELECT TOP 10000 * FROM dbo.Users WHERE Reputation= '
	+ CAST(@Reputation AS NVARCHAR(10))
	+ N' ORDER BY DisplayName;';

EXEC sp_executesql @StringToExecute;
END
GO

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



/* Parameterized, but with comment injection: */
CREATE OR ALTER PROCEDURE dbo.usp_UsersByReputation
  @Reputation int
AS
BEGIN
DECLARE @StringToExecute NVARCHAR(4000);
SET @StringToExecute = N'SELECT TOP 10000 * FROM dbo.Users WHERE Reputation=@Reputation ORDER BY DisplayName;';

IF @Reputation = 1
	SET @StringToExecute = @StringToExecute + N' /* Big data */';

EXEC sp_executesql @StringToExecute, N'@Reputation INT', @Reputation;
END
GO

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




/* And for bonus points, this is how real-life complex IF branches
can be way worse than single-query procs: */
CREATE OR ALTER PROC dbo.usp_QueryStuff
    @TableToQuery NVARCHAR(50) = NULL,
    @UserReputation INT = NULL,
    @BadgeName NVARCHAR(40) = NULL AS
BEGIN
IF @TableToQuery = 'Users'
    SELECT TOP 10000 *
        FROM dbo.Users
        WHERE Reputation = @UserReputation
        ORDER BY DisplayName;
ELSE IF @TableToQuery = 'Badges'
    SELECT TOP 200 *
        FROM dbo.Badges
        WHERE Name = @BadgeName
        ORDER BY Date;
END
GO

DBCC FREEPROCCACHE

/* And here's a few sets of parameters that cause wildly different plans */
EXEC dbo.usp_QueryStuff @TableToQuery = 'Users', @UserReputation = 1;
GO
EXEC dbo.usp_QueryStuff @TableToQuery = 'Users', @UserReputation = 2;
GO
EXEC dbo.usp_QueryStuff @TableToQuery = 'Badges', @BadgeName = 'Student';
GO
EXEC dbo.usp_QueryStuff @TableToQuery = 'Badges', @BadgeName = 'dynamic-sql';
GO
EXEC dbo.usp_QueryStuff;
GO





/*
Learn more later at:
https://www.BrentOzar.com/go/sniff


License: Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
More info: https://creativecommons.org/licenses/by-sa/3.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.
* 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

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

Why is the Same Query Sometimes Slow?

v1.8 - 2019-08-27

I'm going to teach you 4 things:

1. What parameter sniffing is

2. How to react to parameter sniffing emergencies

3. How to test a query with sniffing problems

4. Options to fix the query long term

Learn more later at:

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

* DropIndexes proc: https://www.BrentOzar.com/go/dropindexes

* sp_BlitzCache: https://www.BrentOzar.com/blitzcache

USE StackOverflow2013;

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;

EXEC sys.sp_configure N'cost threshold for parallelism', N'50'

EXEC sys.sp_configure N'max degree of parallelism', N'4'

RECONFIGURE

DropIndexes;

/* Turn on actual execution plans and: */

SET STATISTICS IO, TIME ON;

CREATE INDEX IX_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;

CREATE OR ALTER PROCEDURE dbo.usp_UsersByReputation

@Reputation int

SELECT TOP 10000 *

FROM dbo.Users

WHERE Reputation=@Reputation

ORDER BY DisplayName;

EXEC dbo.usp_UsersByReputation @Reputation =1;

EXEC dbo.usp_UsersByReputation @Reputation =2;

DBCC FREEPROCCACHE

EXEC dbo.usp_UsersByReputation @Reputation =2;

EXEC dbo.usp_UsersByReputation @Reputation =1;

sp_BlitzCache

DECLARE @Reputation INT = 2

--CREATE PROCEDURE dbo.usp_UsersByReputation

-- @Reputation int

--AS

SELECT TOP 10000 *

FROM dbo.Users

WHERE Reputation=@Reputation

ORDER BY DisplayName;

DBCC SHOW_STATISTICS('dbo.Users', 'IX_Reputation')

CREATE PROCEDURE #usp_UsersByReputation @Reputation int AS

SELECT * FROM dbo.Users

WHERE Reputation= @Reputation

EXEC #usp_UsersByReputation 2

/* "Normal" dynamic SQL: */

CREATE OR ALTER PROCEDURE dbo.usp_UsersByReputation

@Reputation int

BEGIN

DECLARE @StringToExecute NVARCHAR(4000);

SET @StringToExecute = N'SELECT TOP 10000 * FROM dbo.Users WHERE Reputation=@Reputation ORDER BY DisplayName;';

EXEC sp_executesql @StringToExecute, N'@Reputation INT', @Reputation;

END

DBCC FREEPROCCACHE;

EXEC usp_UsersByReputation @Reputation = 1;

EXEC usp_UsersByReputation @Reputation = 2;

sp_BlitzCache;

/* "Bad" dynamic SQL building unparameterized strings: */

CREATE OR ALTER PROCEDURE dbo.usp_UsersByReputation

@Reputation int

BEGIN

DECLARE @StringToExecute NVARCHAR(4000);

SET @StringToExecute = N'SELECT TOP 10000 * FROM dbo.Users WHERE Reputation= '

+ CAST(@Reputation AS NVARCHAR(10))

+ N' ORDER BY DisplayName;';

EXEC sp_executesql @StringToExecute;

END

DBCC FREEPROCCACHE;

EXEC usp_UsersByReputation @Reputation = 1;

EXEC usp_UsersByReputation @Reputation = 2;

sp_BlitzCache;

/* Parameterized, but with comment injection: */

CREATE OR ALTER PROCEDURE dbo.usp_UsersByReputation

@Reputation int

BEGIN

DECLARE @StringToExecute NVARCHAR(4000);

SET @StringToExecute = N'SELECT TOP 10000 * FROM dbo.Users WHERE Reputation=@Reputation ORDER BY DisplayName;';

IF @Reputation = 1

SET @StringToExecute = @StringToExecute + N' /* Big data */';

EXEC sp_executesql @StringToExecute, N'@Reputation INT', @Reputation;

END

DBCC FREEPROCCACHE;

EXEC usp_UsersByReputation @Reputation = 1;

EXEC usp_UsersByReputation @Reputation = 2;

sp_BlitzCache;

/* And for bonus points, this is how real-life complex IF branches

can be way worse than single-query procs: */

CREATE OR ALTER PROC dbo.usp_QueryStuff

@TableToQuery NVARCHAR(50) = NULL,

@UserReputation INT = NULL,

@BadgeName NVARCHAR(40) = NULL AS

BEGIN

IF @TableToQuery = 'Users'

SELECT TOP 10000 *

FROM dbo.Users

WHERE Reputation = @UserReputation

ORDER BY DisplayName;

ELSE IF @TableToQuery = 'Badges'

SELECT TOP 200 *

FROM dbo.Badges

WHERE Name = @BadgeName

ORDER BY Date;

END

DBCC FREEPROCCACHE

/* And here's a few sets of parameters that cause wildly different plans */

EXEC dbo.usp_QueryStuff @TableToQuery = 'Users', @UserReputation = 1;

EXEC dbo.usp_QueryStuff @TableToQuery = 'Users', @UserReputation = 2;

EXEC dbo.usp_QueryStuff @TableToQuery = 'Badges', @BadgeName = 'Student';

EXEC dbo.usp_QueryStuff @TableToQuery = 'Badges', @BadgeName = 'dynamic-sql';

EXEC dbo.usp_QueryStuff;

Learn more later at:

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

License: Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)

More info: https://creativecommons.org/licenses/by-sa/3.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.

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

distribute your contributions under the same license as the original.