Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 6, 1999

SQL Server Tuning Top 10

By Guy van den Berg

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

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM