Writing Faster T-SQL Stored Procedures and Functions: Lessons Learned

PDF From Seacoast SQL 2015-04-27 Improving SQL Server Performance by Writing Faster T-SQL Code SeaCoast SQL UG 2015-04-27 Download file with Examples: Writing-Faster-T-SQL-Andy_Novick-Examples-2015-04-27

Recorded Webinar September 2014: Video

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:

  •  Using Profier, extended events and DMV’s as diagnostic tools
  •  Managing indexes on temp tables
    •  Why heaps are often the best choice for temp tables
    •  How to discover if an index is helping
    •  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. (Updated 2012-10-12) Or just the Writing Faster T-SQL Stored Procedures and Functions slides as a PDF (Upated 2012-10-12)