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.
More
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.
More
Introduction
13m 2s
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.
Table of Contents
Introduction
13m 2s
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.
Course info
Level
Intermediate
Rating
(73)
Duration
3h 13m
Updated
21 May 2015
Course authors

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.

Share course