SQL Server: Optimizing Stored Procedure Performance - Part 2

Mastering SQL Server stored procedures is key to efficient database management. Stored procedures offer the advantage of pre-compiled code, leading to significant performance improvements for repeated operations by leveraging cached plans. However, realizing this potential requires careful attention.

This course, Part 2 of a comprehensive series, dives deep into the factors influencing stored procedure performance. You'll discover how SET options directly impact query plan creation and caching, and learn techniques to optimize their use.

Specifically, you'll learn how to:

• Understand the relationship between SET options and query plan behavior.
• Ensure filtered indexes are utilized effectively for performance gains.
• Troubleshoot potential performance bottlenecks caused by features like Resource Governor.
• Implement strategies for consistently high-performing stored procedures.

This course is essential for SQL Server developers and anyone responsible for writing stored procedures that demand reliable, repeatable performance.

The provided demo database is compatible with SQL Server 2008 through SQL Server 2014. While all demos are demonstrated on SQL Server 2014, we provide references to behavior differences across all supported versions. The optimization strategies taught are applicable to SQL Server 2005 onward, with some techniques even extending back to SQL Server 2000.

Unlock the full potential of your SQL Server database by writing optimized and efficient stored procedures.