T-SQL & Development
[Video] How to Find Queries Using OPTION RECOMPILE (And Their Parameters)
For years, I hated troubleshooting servers with high CPU usage caused by queries constantly asking for new execution plans. Hated it. SQL Server just doesn’t make it easy to find queries with recompile hints.
Then Erik Darling’s sp_HumanEvents came along.
And now troubleshooting frequent compilations is as easy as this:
|
1 |
EXEC dbo.sp_HumanEvents @event_type = 'compilations', @seconds_sample = 60; |
Which produces this:
ARE YOU KIDDING ME? It gives me the parameters, the number of times the query compiled, how much time was spent doing those compilations, and more! It’s a SINGLE LINE OF T-SQL that sets up an Extended Events session, waits, tears it back down, and then lays out the output in a ridiculously easy-to-consume format!
Or, say you’re facing frequent re-compilations due to stats changes in high-turnover tables. No problem:
|
1 |
EXEC dbo.sp_HumanEvents @event_type = 'recompilations', @seconds_sample = 60; |
Here, I’m running a workload that causes a stats update in the middle of the workload, which triggers a recompile of a normally stable stored procedure:
It doesn’t get any easier than that. These are by no means the only tricks up sp_HumanEvents’ sleeve, either: it has all kinds of parameters to help you track down issues that would usually involve a lot of work.
If you prefer moving pictures with audio gibberish, here’s a live stream of me setting up the demo for this blog post:
Enjoyed this session? Follow me on Twitch, YouTube, or Facebook to be alerted whenever I’m streaming. Here’s the code I used with the Stack Overflow database in the live session:
|
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 |
DropIndexes GO CREATE INDEX Location ON dbo.Users(Location); CREATE INDEX CreationDate ON dbo.Comments(CreationDate); GO CREATE OR ALTER PROC dbo.usp_SearchUsers @Location NVARCHAR(100), @StartDate DATETIME, @EndDate DATETIME AS BEGIN SELECT TOP 1000 * FROM dbo.Users u INNER JOIN dbo.Comments c ON u.Id = c.UserId WHERE u.Location = @Location AND c.CreationDate >= @StartDate AND c.CreationDate <= @EndDate ORDER BY c.Score DESC OPTION (RECOMPILE); END GO EXEC usp_SearchUsers @Location = 'London, United Kingdom', @StartDate = '2010-01-01', @EndDate = '2010-01-02'; GO 50 EXEC usp_SearchUsers @Location = 'Near Stonehenge', @StartDate = '2010-01-01', @EndDate = '2010-12-31'; GO 50 CREATE OR ALTER PROC dbo.usp_SearchUsers_Encrypted @Location NVARCHAR(100), @StartDate DATETIME, @EndDate DATETIME WITH ENCRYPTION AS BEGIN SELECT TOP 1000 * FROM dbo.Users u INNER JOIN dbo.Comments c ON u.Id = c.UserId WHERE u.Location = @Location AND c.CreationDate >= @StartDate AND c.CreationDate <= @EndDate ORDER BY c.Score DESC OPTION (RECOMPILE); END GO EXEC usp_SearchUsers_Encrypted @Location = 'London, United Kingdom', @StartDate = '2010-01-01', @EndDate = '2010-01-02'; GO 50 EXEC usp_SearchUsers_Encrypted @Location = 'Near Stonehenge', @StartDate = '2010-01-01', @EndDate = '2010-12-31'; GO 50 CREATE OR ALTER PROC dbo.usp_SearchUsers_Stable @Location NVARCHAR(100), @StartDate DATETIME, @EndDate DATETIME AS BEGIN SELECT TOP 1000 * FROM dbo.Users u INNER JOIN dbo.Comments c ON u.Id = c.UserId WHERE u.Location = @Location AND c.CreationDate >= @StartDate AND c.CreationDate <= @EndDate ORDER BY c.Score DESC; END GO EXEC usp_SearchUsers_Stable @Location = 'London, United Kingdom', @StartDate = '2010-01-01', @EndDate = '2010-01-02'; GO 10 ALTER TABLE dbo.Users REBUILD; GO EXEC usp_SearchUsers_Stable @Location = 'London, United Kingdom', @StartDate = '2010-01-01', @EndDate = '2010-01-02'; GO 10 |
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Brent, You live in Iceland now? No wonder my DBA skills have diminished! The Ozar Aura can only travel 2400 miles. + it is so dangerous there!! Iceland may be spectacular with beauty but is a MOVING VOLACANO. Y I u and Erika, please be safe.