Last week I was on SQL Server Seminar presented by Kevin Kline.
Performance, Baselining, Benchmarking and Monitioring Presentation
Stored Procedure Best Practices Presentation
Surviving the Data Avalanche Presentation
Top 10 Mistakes on SQL Server Presentations
There were a few interesting points.
•Causes of performance problems-5%-hardware, 15%-bad database design, 80%-bad application code.
•Do not Interleaved DDL and DML -No create/Drop table in the same SP as insert/Select.
•Keep transactions as short as possible.Use @@TRANCOUNTor sys.dm_tran_active_transactionsto look for orphaned transactions when entering a new routine.
•Set Theory is Better. then rows.Cursors are depricated.
•Use table variables,minimize use of tempdb
•Again: Set Nocount On
Again: Owner qualify all names (2-part)
Active(e.g. RAID10, high RPM speeds).
Near-term older data (e.g. RAID5, middle RPM speeds)
Old, long-term data (e.g. SATA drives – high volume, low RPM speeds)
Related white papers from Quest Site(login probably will be required):
The Top 10 Mistakes on SQL Server
Tuning SQL Statements on Microsoft SQL Server
Finding and Fixing Bad SQL in Microsoft SQL Server 2000
Performance Baselining, Benchmarking, and Monitoring for SQL Server 2000