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:

• 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.
• Rewriting scalar and multi-statement functions to as inline functions and why it’s so important
• 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.
• Query optimization using query execution plans.

Each technique is illustrated with examples drawn from real cases Andy has worked on.

Here are the zip with the slides and the examples to download.
Or just the Writing Faster T-SQL Stored Procedures and Functions sides as a PDF