Knowing how to optimize stored procedures is important, so stick around and I’ll show top five stored procedure performance tips you can use today.

Let’s dig into some super easy tips you can use to speed up your stored procedures. Before we begin, let me point out that the tips I’m going to show you are specific to stored procedures.

Performance Tip 1: SET NOCOUNT ON

The first thing I want to talk about, the first easy stored procedure performance tip you can implement is to include the command SET NOCOUNT ON, in your stored procedure. What this does is it stops sending those messages that say five rows affected by this command, six row effected by this command…

If you have a stored procedure that’s doing a lot of queries or updates and you see those messages quite a bit when you run the command, this will reduce a lot of that network traffic.

If you think about an application that’s calling a stored procedure many, many times, this can really add up. So use SET NOCOUNT ON.

Also, what you want to do is call your stored procedures with their fully qualified names. So what do I mean by that?

Stored Procedure Performance Tip 2 – Use Fully Qualified Name

Your stored procedures are sorted in schemas. If you don’t do anything at all, it is stored in the dbo schema. By not fully qualifying the name, it takes longer to search for and then execute the stored procedure.

To execute the stored procedure named EmployeeLoad according to best practice, use this command:

EXEC dbo.EmployeeLoad

Do not use EXEC EmployeeLoad. Why? Because by not including the schema, you are making it a little harder for SQL to search through all the database objects to find your stored procedure to run.

Performance Tip 3 – Avoid sp_ prefix

Also, while we’re on the topic of naming stored procedures, do not begin your stored procedure with sp_.

I think people think, “Oh, that’s really cool. I can just use sp_ because that stands for stored procedure.”

Well, actually, it’s a Microsoft reserve word, and if you use that prefix, what will happen is that every time you go to run your stored procedure, the database starts to look through the master database and some other databases before it even gets to your database to look for your stored procedure. You’re just adding more to how it searches to find if stored procedure even exists.

Better yet, why not just name your stored procedure with no prefix. So the old school way of doing it used to be like, “Oh, I got to save my stored procedure as uspEmployeeLoad so everyone know it’s a stored procedure.

Well, we know it’s a stored procedure, you can look in the dictionary or your tool shows it under stored procedures. There’s no need to prefix it. Just call it EmployeeLoad. That is to cool school way of doing it.

Performance Top 4 – Avoid Cursors

The next stored procedure performance tip is to use set based queries over cursors whenever possible. What I mean is, instead of using cursors that iterate over rows and do while I’m not at the end of my record set, go to the next row and do something, preserve this value, and so on…

Use a window function because window functions rock, and you can use window functions to get the previous record, a row in your result set or the next record in your result set. You can do running averages with them, all sorts of things with window functions.

And you’ll find out that many of the reasons why you were using cursors in the first place can now be replaced by using Window functions or maybe a recursive CTE, like a recursive common table expression.

The idea though is to try to move away from cursors because they go step by step through the record set until it gets to the end and it’s very slow. So do not use those.

Performance Tip 5 – Decrease Transaction Scope

Also, one thing that you want to look at is reducing transaction scope. Obviously, when we’re working with our stored procedures, we’re going to need to, at some time update more than one table and that’s where transactions come in handy because I can make sure that during this operation, I only conclude the operation if both tables successfully get updated. So good use for a transaction, but do not put a whole bunch of other garbage in between those statements or add it into that transaction.

Like, “Oh, let’s go look up other things or run along queries,” because when a transaction is running, it’s locking. And the longer the locks are held, the greater the chance that you get deadlocks.

A deadlock is basically like, I’m trying to lock the record that you have locked and you’re trying to lock a record that I have locked. And so, we get into this deadly embrace where we essentially both stop because we’re stuck. I’m waiting for you and you’re waiting for me and we both can’t stop because we each are using resources that each of us need. So dead locking’s bad.

You can reduce dead locking if you can reduce the time that you are actually locking things in your system and a good way to reduce the time of locking is just to make sure that when you’re doing a transaction that you get in, do the transaction and get out. Don’t put a lot of fluff in there.

Conclusion

So those are my tips. They’re real quick. They’re really easy. I hope that you can start to implement them. Some of them are quick commands. Some are more philosophical. There’s no examples of this one. It’s just more for you to think about and I will probably do examples down the road, but this is more just for you to start getting your gears going on what you’d want to do next. So if there’s anything you want me to talk about, first of all, please subscribe to my channel. Yay, do that. That makes me happy. And then write a comment about what you think would be a great topic for me to cover because I pay attention to that and I will add it in.