Novick Software
SQL Server Consulting Design Programming Tuning

  andy novick is a sql server mvp



Writing Faster T-SQL
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. Well 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
    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 its 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 sides as a PDF (Upated 2012-10-12)


Code Camp 17
March 31

SQL Saturday
May 19

NYC SQL  Saturday August 4, 2012 Boston
Code Camp 18
Oct 20
NH SQL Saturday
Oct 20


Personal Blog

New Tips:

Use dsinit to set the SQL Server instance for Windows Azure dev storage


Nov 7, '12
Loser: DB

Full Schedule