SQL Server: Optimizing Stored Procedure Performance

When using stored procedures, their performance is dependent on creating the optimal plan for the execution environment and making sure that it remains cached. This comprehensive course will show you all of that and more, and is applicable to SQL Server developers and anyone responsible for ensuring performance of stored procedures, from SQL Server 2005 onward.
Course info
Rating
(271)
Level
Intermediate
Updated
Aug 28, 2014
Duration
7h 11m
Table of contents
Introduction
Why Use Stored Procedures?
Creation, Compilation, and Invalidation Section 1
Creation, Compilation, and Invalidation Section 2
Optimization and Recompilation Section 1
Optimization and Recompilation Section 2
Optimization and Recompilation Section 3
Optimization and Recompilation Section 4
Other Concerns and Considerations
Description
Course info
Rating
(271)
Level
Intermediate
Updated
Aug 28, 2014
Duration
7h 11m
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 a 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 the end, you might suffer from what is known as parameter-sniffing-problems where the optimal plan for a procedure varies based on the parameters supplied. This comprehensive course will teach you how stored procedure plan caching works and how to get SQL Server to produce the optimal plan for your stored procedure performance in a variety of circumstances. 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 1. You're watching module 1; The Introduction. To give you a little bit of background I've written a couple of courses for Pluralsight around physical database design and optimizing Ad Hoc statement performance. And one of the things that I tend to focus on is what it takes for a database to be well tuned and well designed. And one concept that I think a lot of people, you know, think is important is, what's the code, what did I put into the code or what does my data look like or what do my table structures look like? What are my workloads and my priorities. Is it OLTP or decision support. And don't get me wrong, all of these things are important, you want to have effective code, you want to have well designed databases, you need to understand your workload, but when it really comes down to performance tuning, I think of three things. I think of course knowing your data's important, knowing your data how it's going to be used, knowing the criteria, knowing what kind of domain of values you're going to store. All of these things are incredibly important. You need to know your workload, that's important too. If I'm prioritizing for OLTP, that's probably going to have a totally different spin on performance tuning than if I am tuning for decision support. So these things are incredibly important, don't get me wrong, but the piece that I think a lot of people are missing and the thing that I try to strive to do in my classes, is showing you some of the oddities and interesting things, I think, in how SQL Server works. Because that is really the key to getting great performance.