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
- 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.