Analyzing Temporary Stored Procedures with sp_BlitzCache

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.

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.

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!

I get this back, with some warnings, and the ability to open up the plan and gaze upon it’s awfulness.

No alibi

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!

, , ,
Previous Post
[Video] Office Hours 2017/01/18 (With Transcriptions)
Next Post
When Always On Isn’t: Handling Outages in Your Application

2 Comments. Leave new

Menu
{"cart_token":"","hash":"","cart_data":""}