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.

Preview this course

What you'll learn

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.

Table of contents

Introduction
22mins
Why Use Stored Procedures?
28mins
Creation, Compilation, and Invalidation Section 1
69mins
Creation, Compilation, and Invalidation Section 2
54mins
Optimization and Recompilation Section 1
54mins
Optimization and Recompilation Section 2
52mins
Optimization and Recompilation Section 3
68mins
Optimization and Recompilation Section 4
63mins
Other Concerns and Considerations
24mins

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

Kimberly L. Tripp is a SQL Server MVP and a Microsoft Regional Director and has worked with computers since 1985. Since 1995, Kimberly has worked as a Speaker, Writer, Trainer and Consultant for her own company SQLskills.com which she now runs with her husband – Paul Randal. Working together Kimberly and Paul enjoy the ability to work on different types of projects – from Immersion Events to consulting to speaking at conferences to writing articles and books. Kimberly has presented lectures and ... more

See more courses by Kimberly L. Tripp
Ready to upskill? Get started