SQL Server: Optimizing Stored Procedure Performance

By Kimberly L. Tripp
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.
Start FREE course
Course info
Rating
(283)
Level
Intermediate
Updated
Aug 28, 2014
Duration
7h 10m
Table of contents
Introduction
Introduction 2m This Course 3m What Does Optimizing Stored Procedure Performance Mean? 3m Does This Sound Familiar? 2m What Does Optimizing Stored Procedure Performance NOT Mean? 4m Why This Course Is Relevant 3m Course Focus and Structure (1) 2m Course Focus and Structure (2) 3m
Why Use Stored Procedures?
Overview 2m Different Ways to Execute SQL Statements 2m Some Statements Can Be Cached for Reuse (1) 2m Some Statements Can Be Cached for Reuse (2) 4m Version-Specific Plan Caching Whitepapers 1m Reducing Plan Cache Pollution 5m Understanding sp_executesql 3m Stored Procedures / sp_executesql and the Cache 3m Parameter Sniffing 2m Summary: Why Use Stored Procedures? 4m
Creation, Compilation, and Invalidation Section 1
Overview 2m What Happens When You Create a Procedure? 3m Where Are Stored Procedures Stored? (1) 2m Where Are Stored Procedures Stored? (2) 2m Creating Stored Procedures 3m Stored Procedure Plans and Caching 5m Side Effect: Plan Cache Flush (1) 3m Side Effect: Plan Cache Flush (2) 1m Side Effect: Plan Cache Flush (3) 3m Side Effect: Plan Cache Flush (4) 3m DEMO: Generic Demo Intro 2m DEMO: Setup Credit Sample Database 5m DEMO: Setup Analysis Procedures for Credit 8m DEMO: Object-Level Invalidation or Eviction 16m DEMO: Database-Level Invalidation or Eviction 4m DEMO: Server-Level Invalidation or Eviction 7m
Creation, Compilation, and Invalidation Section 2
Plan Invalidation 5m Plan Invalidation Due to Statistics Updates 4m DEMO: Plan Invalidation and Statistics - Part 1 14m Updates to Statistics may not Invalidate Bad Plans 3m DEMO: Plan Invalidation and Statistics - Part 2 14m Plan Invalidation / Recompilation Causes 3m Stored Procedure Caching + Compilation Concerns 4m When Should You Recompile? 4m Summary: Creation and Compilation 3m
Optimization and Recompilation Section 1
Overview 4m Options for Recompilation 4m sp_recompile object_name 7m DEMO: Long Blocking Chains 11m CREATE ... WITH RECOMPILE 3m DEMO: CREATE ... WITH RECOMPILE 5m Conditional Logic 3m DEMO: Conditional Logic - Part 1 9m Modularization 3m DEMO: Conditional Logic - Part 2 - Modularization 5m
Optimization and Recompilation Section 2
EXECUTE ... WITH RECOMPILE (1) 4m DEMO: EXECUTE ... WITH RECOMPILE For Testing 10m EXECUTE ... WITH RECOMPILE (2) 4m DEMO: Determining Plan Stability 8m Statement-Level Recompilation 6m DEMO: Recompilation With OPTION (RECOMPILE) 4m DEMO: Recompilation With OPTION (Optimize For Literal) 4m DEMO: Recompilation With OPTION (Optimize For UNKNOWN) 9m DEMO: Variables Simulate Optimize For UNKNOWN 3m
Optimization and Recompilation Section 3
OPTION (RECOMPILE) 4m OPTIMIZE FOR ... 5m DEMO: Scenario - Setup 4m DEMO: Scenario - Scenario Explained 5m DEMO: Scenario - Conditional Logic Does NOT Work 6m DEMO: Scenario - EXEC With RECOMPILE 7m DEMO: Scenario - Conditional Logic Plus Modularization WORKS! 7m DEMO: Stabilizing Plans With Indexes - Setup and Scenario 5m DEMO: Stabilizing Plans With Indexes - Plan Stability 10m DEMO: Stabilizing Plans With Indexes - Voila! 10m Summary: Walkthrough Demo (1) 2m Summary: Walkthrough Demo (2) 3m
Optimization and Recompilation Section 4
When Does a Procedure Get Optimized? 4m Server-Wide: OPTIMIZE FOR UNKNOWN 3m The Checkered Past of OPTION (RECOMPILE) 4m Dynamic String Execution 3m DEMO: Dynamic String Execution 8m Multi-Purpose Procedures 3m Building Strings Dynamically and Caching 4m DEMO: Multi-Purpose Procedures - Setup and Scenario 5m DEMO: Multi-Purpose Procedures - The Problem 5m DEMO: Multi-Purpose Procedures - Option Recompile too Much CPU 3m DEMO: Multi-Purpose Procedures - The Ultimate Multi-Purpose Proc 17m Patterns and Practices in Statement Recompilation 3m Summary: Stored Procedure Pitfalls/Performance 1m
Other Concerns and Considerations
Overview 4m Demystifying Plan Caching for Bad Plans 3m Secondary Concerns and Considerations 8m Optimizing Statement and Procedure Performance 3m Just the Tip of the Iceberg 4m Course Summary 2m
Description
Course info
Rating
(283)
Level
Intermediate
Updated
Aug 28, 2014
Duration
7h 10m
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.

Course FAQ
Course FAQ
What is a stored procedure in SQL Server?

Stored procedure is a piece of SQL code that is saved and can be reused as needed instead of writing the same SQL code over and over again. It can be used to insert, delete, or update a SQL statement, and much more.

What are some of the benefits of stored procedures?

SQL Server stored procedures help maintain data integrity through consistency. They also improve productivity because certain statements will only need to be written once instead of several times over.

What are the types of stored procedures?

Some of the different types of SQL Server Stored Procedures include:

  • System Defined Stored Procedures
  • Extended procedures - gives access to external programs
  • User Defined Stored Procedures - made by users for their own specific actions
  • CLR Stored Procedures - allows coding procedures in a .NET language
Who is this course for?

This course will be most applicable to SQL Server developers, but will be beneficial to anyone who wants to ensure productivity and performance working within SQL Server.

What will I learn in this course?

This 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.

You will learn:

  • Why it is effective to use stored procedures
  • How to create a stored procedure
  • Where stored procedures are stored
  • Stored procedure plans and caching
  • When you should recompile
  • Conditional logic
  • Much more
What version do I need for this course?

Course demos are shown on SQL Server 2014, but they are applicable as far back as SQL Server 2005 through today.

Are there prerequisites for this course?

Before taking this course you should be familiar with SQL Server fundamentals.

About the author
Kimberly L. Tripp
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
SQL Server: Optimizing Stored Procedure Performance - Part 2
Intermediate
3h 13m
May 21, 2015
More courses by Kimberly L. Tripp

You're the smartest person in the room

PROVE IT

Your team won't just close the skills gap

THEY'LL HURDLE IT