One of the features I used most is very new

We recently got the ability to click on the background of an execution plan and drag it around. I use this constantly now when I’m exploring an execution plan, and it’s hard for me to imagine life without click and drag now!

Transcript

There are a few shortcuts that I use all the time when it comes to working with execution plans.

I love execution plans in SQL Server. They can help you figure out, hey, what the heck is going on in my query?

Control + k, control + i to get quick info on stored procedure parameters

We are in that WideWorldImporters sample database, and we’re going to look at some execution plans, this time for a stored procedure. Now, this is another time when I can look at my quick info command, which I’m a huge fan of. We saw it in a previous demo, when it came to telling us about the columns in a table. Let’s say we want to run this procedure.

Well, the procedure, if I hover over the name, if I’m using the mouse, I can get little info. But this isn’t even a complete message. It just says, well, you haven’t provided @LastCutoff. Well, are there more parameters?

If I have my mouse anywhere on the procedure name, and I do control + k and then control + i, I can get a tool tip. Let’s see if we can get it and zoom at the right time. There we go. We can use Zoom It at the right time to get it in a readable fashion.

Control + k and control + i, there’s two parameters, @LastCutoff and @NewCutoff, and it even gives me their data types. I love it. I love it.

That control + k, control + i, really, really useful for getting information. Now that I know what parameters to use, I can actually set them up.

Control + l to immediately get an estimated plan

Let’s say I want to know, SQL Server, what kind of execution plan will you make for this without executing it? I can get an estimated execution plan by doing just control + l. Now, this is the equivalent of this button, here at the top. It’s sort of like a little thought bubble and then an execution plan is, I think, what the drawing is.

It says that I’m supposed to use control + shift + alt + l. We’ve always been able to use control + l, and it still works for some reason.

Management Studio’s just started suggesting this more complex one.

I’m just going to do control + l. Now, right away, I’ve said, “what if you were to run?.” Right away it generates that plan and shows it to me.

The estimated plan isn’t completely identical as the actual plan. You’ll notice that they’re going to look kind of different. In the estimated plan, I have an outer block at the top for executing the procedure. Then I get information about the procedure in a second pane. And I have information about Set On/Off.

Click + drag

Well, here’s something cool they gave us in execution plans recently. I can now click and drag. I just clicked on the back of the plan, and I can drag it over and look at different parts of it. I can also zoom in and out by holding down the control key, and using my mouse wheel to zoom in and zoom out. As soon as I hover over anything, I get a tool tip, of course.

This can be kind of cool for very gradual, you know, using a very granular adjustment on your zoom, and then kind of dragging it over. I think that the click and drag works really nicely with the zoom, and I love that they gave us those features.

Control + m to toggle actual execution plans

So, that’s estimated plans, which is control + l. Control + m is a little different. Control + m toggles actual execution plans, but I only get an actual execution plan when I execute a query. My button for actual plans is up here, and it is not currently enabled. Notice that it is not highlighted, it is just to the right of my Intellisense button. There’s no little thought bubble next to this plan. This is the actual plan.

I’m going to do control + m, and there’s a slight delay, then it highlights this. I can hit it again, and it’ll turn it off. I can toggle between On and Off, so control + m, now it’s off, control + m, now it’s on again. Real easy to flip it on and off.

Now nothing happens until I execute, and hit control + e, because I’m not an F5, I’m a control + e, and now I get the results of my query, because, you know, I have to execute it, as well as that execution plan.

It does look different. I don’t have that outer stored procedure [Line] now. I’ve just got the select query in the guts, or the important, internal part of the stored procedure.

Again, I can click and drag, I can control and mouse scroll to zoom in and zoom out.

Very, very cool, and I always try to remember to toggle my execution plans off if I’m not going to use them, because sometimes you can get– especially if you accidentally run something that has looping code inside of it– you could get a crazy explosion of actual plans if you accidentally leave them on. Real easy to control + m and toggle that off again.