SQL Server Tuning Top 10

Thursday May 6th 1999 by Guy van den Berg

Heres the real basics, how to get the most from your server.

Heres the real basics, how to get the most from your server, at the very least you should:

  1. Memory - you can never have enough of it...use sp_configure memory, value to set it to the right level for your system.
  2. Design for performance - Effective initial database and application design is very important.
  3. Check basic configuration - Things to look for include:
    • Procedure Cache
    • Max Async IO
    • Number of Locks
    • Number of User Connections
    Again use sp_configure
  4. Disk subsystem - check for disk queuing using (Physical or Logical)Disk Queue - try load balancing across mulitple drives.
  5. RAID - if you can afford it try and make sure your system is using hardware RAID.
  6. 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.
  7. Tempdb in Ram? - popular question, answer is no :)
  8. Index Design - efficent index design can make all the difference. Use SHOWPLAN to make sure your queries are using the correct indexes.
  9. Learn to use performance monitor - to monitor your installation. Take a look here to put you on the right track.
  10. 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

Mobile Site | Full Site