Faster Stored Procedures and Functions: Lessons Learned

Stored procedures and functions are the mainstay of SQL Server coding. This presentation will show techniques to improve their performance using the lessons learned by Andy Novick over several years of development. We’ll start with the most important techniques to learn where the time is really going using standard server traces and the new SQL Server Extended Events. Once we know where the time is going we look at strategies to decrease it: Rewriting scalar and TVF functions to as inline functions. Managing indexes on temp tables o Why heaps are often the best choice for temp tables o How to discover if an index is helping o How building non-clustered indexes on heaps is faster than building clustered indexes. Mastering minimal logging and the "Go Faster" switch. Rewriting scalar or multi-statement functions as SQLCLR functions and when it makes sense When temp tables are better performing than table variables and how to choose between them. Each technique is illustrated with examples drawn from real cases Andy has worked on.