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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 |
/* Why is the Same Query Sometimes Slow? v1.9 - 2021-01-27 I'm going to teach you 3 things: 1. What parameter sniffing is 2. How to react to parameter sniffing emergencies 3. How to test a query with sniffing problems And then for self-learning, I give you demo code to start playing around with 7 ways to fix the problem long term. I cover those in detail, plus much more, in my class Mastering Parameter Sniffing. 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 StackOverflow; 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 /* 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. */ |