How I Set Up To Tune SQL Server Stored Procedures

I am a heading

---

Video Summary

In this video, I share my setup and process for tuning code, whether it’s stored procedures or functions. I demonstrate how I split the screen to compare the original code with the optimized version side by side, ensuring that any changes are clearly visible and easily reversible if needed. By keeping a results window on one side and an execution plan on the other, I can quickly verify query outcomes and identify performance bottlenecks without interrupting my workflow. This setup allows me to track improvements over time and make informed decisions about breaking down complex queries into more manageable parts. Additionally, I show how to set up this split screen using the Window File menu in SQL Server Management Studio (SSMS), making it a straightforward process for anyone looking to improve their coding efficiency.

Full Transcript

Erik Darling here, Erik Darling Data. Normally I say that I wanted to record a video, but didn’t actually want to record a video. I wanted to go take a nap. So here we are. And this video is decidedly non-technical. This is me dipping my toes into professional development. Kidding. I would never do that. I have no soft skills whatsoever. My softest skill is being nice to bartenders. That’s my soft skill. This video is about how I set up to tune code. Whether it’s a stored procedure or a function or anything else in the world. It’s important. It is muy importante. to have a good setup so that you can be as efficient as possible while people are paying you to fix things for them. So what I always do, and this is an admittedly smushed version of what I do. I usually have a slightly larger screen, but for the purposes of recording, 1920 by 1080 is just what you’re gonna get. Sorry. That’s it. So what I always do is I have a split screen. And I have a split screen for a couple reasons. One is because I want to compare what the code looked like before I made any changes. I’ll usually do that. I’ll usually keep the the virgin version on the right side, the untouched version on the right side.

And I’ll keep that over here so that I know exactly what things looked like before I went and did anything. I can also keep that over here in case something over here gets so screwed up that I just need to start over. That never happens to me though. Too good at my job. So that’s the first thing, right? So you have this thing over here. This is what things looked like before. This is what things looked like after. The other thing that I have in there. The other thing that I have in there is another useful window in this setup is this one right here. And what we get in this window, and what will also be in I believe this window, are the results and query plans. You see, if you have a long or even long-ish running query, the last thing you want to do is have to keep running it to see if the results are right, and see what the execution plan looked like and if it got any better or worse or faster.

So over here, I’ll keep a copy of the version. So over here, I’ll keep a copy of the stored procedure run, the virgin stored procedure run, as is, with the hopefully correct results. There have been several times when working with people when we have been looking at the results of a query and they’ve been like, oh wait, that’s not right. That can’t possibly be. And then we have a different issue to tackle, but that is not really the point here.

The point here is we should assume that the results of this query are correct, and that we are trying to reproduce those results in a faster manner. Crazy. So we have the results over here, so we can easily compare side by side the results, make sure things line up. Does 355 have 1045? Yes, it does. 1045. Look at that. That’s great.

I also like to keep the execution plan up, so that I can see which parts of the query that I want to focus on. Oop, I grabbed the wrong thing there. Story of my life. So I can see which parts of the query I want to focus on. And then, as I get to tuning on this side, I haven’t done anything over here yet, so don’t judge me.

Then I can compare like, okay, well, you know, this video used to take 9.4 or this video, this insert. This insert used to take 9.4 seconds and I got it down on this side to X number of seconds. And then over here, I’ll do the same thing. It’s like, oh, the select took 42 seconds and now I managed to get it this much faster.

So I have a before and after. And I can also kind of see like, you know, there are a lot of times when tuning a query, when it makes a lot of sense to break a query up into multiple pieces, right? Like you don’t want to run, like sometimes having that one big query is like the worst idea.

It’s like, shoot, man, I don’t understand what this query does. And like breaking it up into multiple pieces is a good idea. And then like you have to figure out if those multiple pieces are all faster than the one big one.

So this is just a very helpful general setup for me. Now, another thing that I’ll do is I’ll keep a copy of some notes over on the left side. Let’s say that, you know, I’m running my query and maybe it’s going on for a little bit longer than I thought it would.

I’ll probably have SP who is active open over here so I can see what’s going on when the query runs, get the plan for it, see what’s happening. If I need to make any notes or create any indexes that don’t really make sense to have as a comment, I’ll put that over on this side so that I can reference it pretty easily without having to flip around through a million windows.

And I’ll just remember what I did when. So this is how I set up and get organized to tune code. And I don’t know.

I don’t think there’s anything too ambitious in here. It should be a pretty easy thing for you to reproduce. Oh yeah, I forgot. I forgot to show you how to do this.

There’s always something. Again, if you take, if you have, if you want to get this split screen set up, you just go to the window file menu up here and you choose new, new vertical tab group. And if you choose new vertical tab group, you will get that line in the middle where you can have two sets of queries.

Two sets of queries there. If you, if you don’t, if you don’t already have a vertical tab group set up, you can also set up a, a horizontal tab group. I don’t know how much more helpful that would be though.

That might, that might not be as helpful, but I find the vertical tab group very helpful. I hope that you, my dear watcher, listener, reader, stalker, maybe future drinking buddy if you play your cards right, have enjoyed this video. Hope that maybe you, you learned something like maybe to lead with the instruction.

Maybe not. This is what happens when I record videos sober. It’s your fault you did this to me.

It’s your fault. Never happened again though, I promise. Never happened again. Anyway, thank you for watching. I hope you learned something. And I’m going to go take a nap.

Have a good day.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.