SQL Server Tuning Top 10

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

Previous articlesp_resetstatusflag.sql
Next articlesp_export65.sql

Latest Articles