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.