Guidelines for tuning MS SQL Server databases

Be sure to review these guidelines before tuning an MS SQL server:

  • Make the computer use background services rather than programs. To change your current setting, right-click My Computer > Advanced > Settings for Performance.
  • For Server properties, select the Boost SQL server priority.
  • To avoid frequent checkpoints, adjust the Recovery interval.
Note: All transactions are very short ones (less than a second).
  • Use File Groups for database files.
  • To watch for I/O bottlenecks, use the System Performance Monitor to check the Physical Disk :% Disk Time and Physical Disk: Avg. Disk Queue Length parameters. Consistently high values indicate an I/O bottleneck. To improve performance, use a faster disk drive, move files to a second disk, or add disks to a RAID array.
  • Adjust the memory allocated to the SQL server.
  • Keep an eye on the SQLServer:Buffer Manager: Page reads/sec and SQL Server:Buffer Manager: Page writes/sec parameters.
  • Store temporary databases on a fast disk.
  • Use hardware-based RAID.