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.
PSPO: How SQL Server 2022 Tries to Fix It – there’s a lot to like about this new feature, but one horrible, terrible, no-good, very bad downside that stops folks from using it.
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.
Session, Slides, Resources
Here’s a PDF slide version of the talk that includes the latest improvements in SQL Server 2022.
Here’s an all-demo 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 |
/* Why is the Same Query Sometimes Slow? Introducing Parameter Sniffing v1.1 - 2022-10-18 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 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 /* 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, prior to 2022: */ DECLARE @StringToExec NVARCHAR(4000); SELECT @StringToExec = N'ALTER DATABASE CURRENT SET compatibility_level = ' + CASE WHEN compatibility_level > 150 THEN N'150' ELSE CAST(compatibility_level AS NVARCHAR(10)) END FROM sys.databases WHERE name = 'master'; EXEC(@StringToExec); GO EXEC sys.sp_configure N'show advanced', N'1' GO RECONFIGURE; 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 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 /* How SQL Server decides plans: */ DBCC SHOW_STATISTICS('dbo.Users', 'Reputation'); 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 ALTER TABLE dbo.Users REBUILD; GO EXEC dbo.usp_UsersByReputation @Reputation = 1; GO EXEC dbo.usp_UsersByReputation @Reputation = 2; GO sp_BlitzCache GO /* If you're on SQL Server 2022, you can see how Parameter Sensitive Plan Optimization (PSPO) works: */ ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160; /* 2022 */ GO EXEC dbo.usp_UsersByReputation @Reputation = 2; GO EXEC dbo.usp_UsersByReputation @Reputation = 1; GO sp_BlitzCache 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. */ |