SQL Server Tuning Top 10
May 6, 1999
Heres the real basics, how to get the most from your server, at the very least you should:
- Memory - you can never have enough of it...use sp_configure memory, value to set it to the right level for your system.
- Design for performance - Effective initial database and application design is very important.
- Check basic configuration - Things to look for include:
Again use sp_configure
- Procedure Cache
- Max Async IO
- Number of Locks
- Number of User Connections
- Disk subsystem - check for disk queuing using (Physical or Logical)Disk Queue - try load balancing across mulitple drives.
- RAID - if you can afford it try and make sure your system is using hardware RAID.
- Learn importance of FILLFACTOR and PAD_INDEX - try using SQL Server:I/O Single Page Writes/Sec and DBCC SHOWCONTIG to determine if page splitting is occuring. Use FILLFACTOR with the PAD_INDEX option to set up your system to optimal I/O throughput.
- Tempdb in Ram? - popular question, answer is no :)
- Index Design - efficent index design can make all the difference. Use SHOWPLAN to make sure your queries are using the correct indexes.
- Learn to use performance monitor - to monitor your installation. Take a look here to put you on the right track.
- Avoid slow SQL - such as:
- WHERE column != value
- WHERE column <> value
- Try not to use NOT!
Copyright ) 1998-99 G.h.van den Berg. All rights reserved.
These pages may not be resold or redistributed without prior written permission from Guy van den Berg