Parameter Sniffing
I’m Brent Ozar, and I like databases. Not yours though.
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.
Video: Fixing Parameter Sniffing Part 1 – a demo of how parameter sniffing happens, and how to detect it in the plan cache.
Video: Fixing Parameter Sniffing Part 2 – I show why you can’t just comment out CREATE PROC in order to test parameter sniffing, show you how to really test your code, and why OPTION RECOMPILE can produce the wrong query results.
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.
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 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; 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. */ |