Performance Enhancing T-SQL Queries and Stored Procedures

Iwan de Beer

Analysing Queries

Filtering Results

Batching Operations

Performance Killers

sp_lock shows which tables are being locked

sp_who tells you which statements are currently running

The Profiler identifies long running queries

Query Execution Plans pinpoint bottle-necks

Ensure your queries have WHERE clauses

Select as little data as possible into Temporary Tables

Avoid SELECT * - return subsets of columns

Avoid Cursors

Perform operations in Batches

Use Temporary Tables

Slow Triggers impact on insert, update, and/or delete statements

Missing / Inappropriate Indexes slows down SELECT statements

http://www.mssqlcity.com/Tips/tipTSQL.htm

http://www.sql-server-performance.com/tips/all_main.aspx

http://www.codeproject.com/KB/database/sqldodont.aspx