All the cool kids
Know that when you’re trying to figure out why a stored procedure is slow sometimes, you should use a temporary stored procedure instead of pulling the code out and running it ad hoc.
We frequently point people to Erland Sommarskog’s “Slow in the Application, Fast in SSMS“, because it’s an excellent treatise on many of the things that can go wrong with a query.
But you’re here, so you’re cool
You use temporary stored procs, and you use sp_BlitzCache, but you’re having a hard time tracking down and analyzing plans for temporary stored procs.
The good news is: We got you covered.
Pretender
We have this stored procedure, which looks like just about any stored procedure. It accepts some variameterables, there’s a begin and an end; it’s spectacular. It doesn’t even throw errors.
|
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 |
CREATE PROCEDURE dbo.Top1000Somethings @LastActivityDate DATETIME, @UpVotes INT AS BEGIN SELECT TOP 1000 /*Users*/ u.DisplayName, u.Reputation, /*Posts*/ p.Score, p.CreationDate, p.OwnerUserId INTO #no_name FROM dbo.Users AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE p.PostTypeId = 1 AND p.LastActivityDate >= @LastActivityDate AND u.UpVotes >= @UpVotes ORDER BY u.Id; END; GO |
One day the boss-person comes in and says “we also need to filter on user reputation”, and since you’re a well-paid and loyal employee, you get cracking on that. You create a temporary stored procedure to make sure your code works, and examine any potential performance issues. What a little go-getter you are.
|
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 |
CREATE PROCEDURE #Top1000Somethings @LastActivityDate DATETIME, @UpVotes INT, @Reputation INT AS BEGIN SELECT TOP 1000 /*Users*/ u.DisplayName, u.Reputation, /*Posts*/ p.Score, p.CreationDate, p.OwnerUserId INTO #no_name FROM dbo.Users AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id WHERE p.PostTypeId = 1 AND p.LastActivityDate >= @LastActivityDate AND u.UpVotes >= @UpVotes AND u.Reputation >= @Reputation ORDER BY u.Id; END; |
You could use an ad hoc script to look at the plan cache, Extended Events, or Profiler, to capture the plan. But you like us, so you want to use Ye Olde Blytzen Casshe.
One thing we do by default is ignore system databases. They’re often noisy, and lousy with system queries that have nothing to do with real life. We give you the ability to override that, to choose a database to single out for examination, and as of recently, a way to filter by stored procedure. The code to track this one down looks about like so!
|
1 |
EXEC sp_BlitzCache @DatabaseName = 'tempdb', @IgnoreSystemDBs = 0, @StoredProcName = '#Top1000Somethings' |
I get this back, with some warnings, and the ability to open up the plan and gaze upon it’s awfulness.
Sunglasses At Night
There are a few tips in this post, and some really great links to follow and read, if you haven’t already. Hopefully all of them help you become better at performance tuning SQL Server.
Thanks for reading!
2 Comments. Leave new
How about you explain what those warning mean !!
Because that’s not what this blog post is about! Silly goose.