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