SQL Server: Optimizing Stored Procedure Performance - Part 2

When using stored procedures, their performance is dependent on creating the optimal plan for the execution environment and making sure that it remains cached. In the second part of this comprehensive course, you'll continue to learn techniques for ensuring that this is applicable to SQL Server developers and anyone who is responsible for ensuring performance of stored procedures, from SQL Server 2005 onward.
Course info
Rating
(78)
Level
Intermediate
Updated
May 21, 2015
Duration
3h 13m
Table of contents
Description
Course info
Rating
(78)
Level
Intermediate
Updated
May 21, 2015
Duration
3h 13m
Description

If you want to use SQL Server databases effectively, you will end up using stored procedures. Their aim is that you have optimized and compiled code that resides in cache to improve workload performance for subsequent executions. However, you might find that it does not always work out that way. The performance of a stored procedure is heavily dependent on how the plan is chosen and cached. If the plan is not optimal for subsequent executions it could cause performance to suffer greatly. In part 2 of this comprehensive course, you'll learn in depth how SET options can affect plan creation and caching, plus how to ensure other features like filtered indexes can be used and still other features like Resource Governor are not causing problems. The course is applicable to SQL Server developers and anyone who is responsible for writing stored procedures that must repeatedly perform well. The demo database provided is compatible with SQL Server 2008 through SQL Server 2014. All course demos are shown on SQL Server 2014 with references to behaviors (where different) on all versions. Optimization strategies you will learn in the course apply to SQL Server 2005 onward, and some even back to SQL Server 2000.

About the author
About the author

Kimberly is a SQL Server MVP, Microsoft Regional Director and President/Founder of SQLskills.com, which she now runs with her husband, Paul Randal. Kimberly’s areas of expertise focus on performance tuning through effective database design and architecture.

More from the author
SQL Server: Indexing for Performance
Beginner
7h 11m
Sep 15, 2017
More courses by Kimberly L. Tripp
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Hello, this is Kimberly Tripp from SQLskills. com and I'm recording this course for Pluralsight. My course is focused on SQL Server and it's titled Optimizing Stored Procedure Performance - Part 2. You're watching module 1, the introduction. What's really important to realize is that performance tuning in SQL Server really takes three parts and I think a lot of people misunderstand what I really mean by this, but I say this in all of my courses and it's really the focus of all of my courses to try to fill in this component that's missing. I think a lot of people know that they need to know about their data in order to do performance tuning. We have to know churn, we have to criticality, we need to know how much the data is changing and that's what I mean by churn. We have to know the inserts, the updates, the deletes. We need to know about the data itself. But we also need to know how much we're using the different data sets, which queries are more critical than others. Because a query that's more critical or a query that's run 10, 000 times an hour is a lot more important to tune than a query that's run once a week from somebody, you know, in a back office. So these two things, I think a lot of people realize that they need to understand to do performance tuning, but the piece that I'm trying to fill in, the piece that's, well, not always intuitive is how SQL Server works. Right? Opening up the hood and looking inside and knowing what SQL Server is doing is really going to help you to solve some of those problems that just don't seem to make sense and so in this course, just like I did in part 1, I'm going to dive in deeper with stored procedures and give you more insight into some of the sometimes very strange things that you have to understand to make sure that you get good consistent performance.

Session Settings
Hello, this is Kimberly Tripp from SQLskills. com and I'm recording this course for Pluralsight. My course is focused on SQL Server and is titled Optimizing Stored Procedure Performance Part 2. You're watching module 2: Session Settings. What this module is going to get into are specific session settings and certain features in SQL Server, some are performance related features that require those session settings to be a certain way. I'll talk about why those session settings are important and how they affect results, although I want to stress, it's not really how they're affecting results that I want to focus on this module, I want to focus more on just the consistency of the session settings and the fact that if you're not consistent, what's going to end up happening to your procedures and your plans. But I will give you some insight into some of the session settings. There are some special considerations around two specific ones, like QUOTED_IDENTIFIER and ANSI_NULLS, so we'll get into those, but you're actually going to see that there's six that have to be on and one that has to be off for certain performance related features and then depending on some of the other session settings, when you execute stored procedures, they might have to be on or you're going to get a recompile event, possibly getting a different plan, and so that's really what I'm going to get at in this module is the best practices around how your session settings are set and what they should be set to consistently across the board, right, at the time your objects are created, at the time your procedures are created, at the time your procedures are executed, in order to get the most efficient cache and the most consistency across your plans.

Session Settings (Section 2)
Hello this is Kimberly Tripp from SQLskills. com and I'm recording this course for Pluralsight. My course is focused on SQL Server and it's titled Optimizing Stored Procedure Performance Part 2. You're watching the module on Session Settings and I'm continuing with Section 2. Now in the first part we talked about the session settings and the performance related features, the session settings that affect results, we talked about some special considerations around QUOTED_IDENTIFIER and ANSI_NULLS and we really focused on what the session settings looked like when we created the object, what the session settings looked like in order to leverage some of the performance related features, and then we looked at maybe what the impact was under certain scenarios for the execution state, but that's really what I want to dive into in this second section of this module is how those session settings affect stored procedures and what are some of the gotchas, and then I want to go through a demo and best practices as well as a stored procedure that will really help you troubleshoot how many procedures and plans you have in the cache and what their state is, which is really cool, that's one of the things that I love about this, so I'll spend a bunch of time on that demo. So you guys you'll love that. And then I'll tie in everything with our best practices, which hopefully you're already starting to figure out, what those best practices are.

Special Considerations
Hello this is Kimberly Tripp from SQLskills. com and I'm recording this course for Pluralsight. My course is focused on SQL Server and it's titled Optimizing Stored Procedure Performance Part 2. You're watching module 3: Special Considerations. This particular module isn't going to be a really long complicated module with a lot of complicated concepts, actually I think this is a pretty straightforward module in terms of what we have to be aware of, but there are some features in SQL Server that might end up causing you grief in terms of when your stored procedure gets compiled, the plan and the estimates that occur, and in fact there are some features where you might not be able to leverage them correctly inside of a bounds of a stored procedure. So I'm going to show you some places where you actually might need to recompile your stored procedure. I'm going to talk about, and this definitely corresponds back to our session settings, the affect of resource governor, so you as a developer might not be thinking at all about resource governor, yet your server uses it, so what does that mean for your stored procedures and the plans and the consistency of the plans. And then I just have a couple of things that I wanted to point out in terms of some coding strategies and things to be aware of. So that's what this module is all about.

Troubleshooting Techniques
Hello this is Kimberly Tripp from SQLskills. com and I'm recording this course for Pluralsight. My course is focused on SQL Server and it's titled Optimizing Stored Procedure Performance Part 2. You're watching module 4: Troubleshooting Techniques. What this module is really going to cover is a nice summary for everything that we've looked at, really even across the different courses. I'll remind you a little bit about parameter sensitivity, not a huge amount, but a little bit, and that was more of our part 1 course, but we're going to look here at demystifying those plans for performance, demystifying plan cache from an inconsistency perspective, you know, looking at making sure that the procedure performance is more optimal from a session setting perspective, and then a couple of pointers on other courses and our summary for this course (Loading).