June 04, 2008

Basic steps to improve SQL Server Performance

1. Rebuild your indexes
2. Update statistics(full) - EXEC SP_UPDATESTATS
3. Recompile stored procedure sp_recompile SP_Name
A lot of times developers change table data types and forget to change the stored procedure, thus the optimizer cannot generate a good query plan.
4. Look at the graphical execution plan in Query Analyzer (select SQL statement or statements and press Ctrl-L) to get some idea of what part of a query is the most time-consuming. You might be able to rewrite the query to improve performance, or modify index structures.
5. Keep transactions as short as possible. This can be used to prevent deadlocks.
6. Try to avoid using SQL Server cursors, whenever possible.SQL Server cursors can results in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.
If cursor cannnot avoided, then try to reduce the number of records to process in the cursor,try to reduce the number of columns to process in the cursor.

1 comment:

daspeac said...

I have heard about another way of extract data from mdf. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues.