T Sql Performance Practices
Download as PPS, PDF
0 likes209 views
This document discusses techniques for enhancing the performance of T-SQL queries and stored procedures. It identifies tools like sp_lock, sp_who, and the Profiler that help analyze queries and identify bottlenecks. It provides tips for writing efficient queries such as using WHERE clauses to filter results, selecting only necessary columns, avoiding cursors, batching operations, and using temporary tables appropriately. It also warns that triggers and missing indexes can negatively impact performance.
T Sql Performance Practices
- 1. Performance Enhancing T-SQL Queries and Stored Procedures Iwan de Beer
- 2. Analysing Queries Filtering Results Batching Operations Performance Killers
- 3. 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
- 4. Ensure your queries have WHERE clauses Select as little data as possible into Temporary Tables Avoid SELECT * - return subsets of columns
- 5. Avoid Cursors Perform operations in Batches Use Temporary Tables
- 6. Slow Triggers impact on insert, update, and/or delete statements Missing / Inappropriate Indexes slows down SELECT statements
- 7. 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