Getting Specific Query Plans From Stored Procedures

I Know You


None of your stored procedures are a single statement. They’re long. Miles long. Ages long.

If you’re troubleshooting performance for one of those, you could end up really causing yourself some headaches if you turn on actual execution plans and fire away.

Not only is there some overhead to collecting all the information for those plans, but then SSMS has to get all artsy and draw them from the XML.

Good news, though. If you’ve got some idea about which statement(s) are causing you problems, you can use an often-overlooked SET command.

Blitzing


One place I use this technique a lot is with the Blitz procs.

For example, if I run sp_BlitzLock without plans turned on, it’s done in about 7 seconds.

If I run it with plans turned on, it runs for a minute and 7 seconds.

Now, a while back I had added a bit of feedback in there to help me understand which statements might be running the longest. You can check out the code I used over in the repo, but it produces some output like this:

Why is it always the XML?

If I’m not patient enough to, let’s say, wait a minute for this to run every time, I can just do something like this:

SET STATISTICS XML ON;

    /*Problem queries here*/

SET STATISTICS XML OFF;

That’ll return just the query plans you’re interested in.

Using a screenshot for a slightly different example that I happened to have handy:

click me, click me, yeah

You’ll get back the normal results, plus a clickable line that’ll open up the actual execution plan for a query right before your very eyes.

Thanks for reading!

A Word From Our Sponsors


First, a huge thank you to everyone who has bought my training so far. You all are incredible, and I owe all of you a drink.

Your support means a lot to me, and allows me to do nice stuff for other people, like give training away for free.

So far, I’ve donated $45k (!!!) worth of training to folks in need, no questions asked.

Next year, I’d like to keep doing the same thing. I’d also like to produce a whole lot more training to add value to the money you spend. In order to do that, I need to take time off from consulting, which isn’t easy to do. I’m not crying poor, but saying no to work for chunks of time isn’t easy for a one-person party.

I’m hoping that I can make enough in training bucks to make that possible.

Because this sale is extra sale-y, I’ve decided to name it after the blackest black known to man.

From today until December 31st, you can get all 25 hours of my recorded training content for just $100.00. If you click the link below to add everything to your cart, and use the discount code AllFor100 to apply a discount to your cart.

Everything

Everything

Everything

Some fine print: It only works if you add EVERYTHING. It’s a fixed amount discount code that you need to spend a certain amount to have kick in.

Thank for reading, and for your support.



One thought on “Getting Specific Query Plans From Stored Procedures

Leave a Reply

Your email address will not be published. Required fields are marked *