Resolving SQL Server Hardware Bottlenecks

If your SQL Server is experiencing I/O bottlenecks, consider these possible solutions:

·         Add additional physical RAM so that your server can go to RAM instead of the I/O system to access data. If SQL Server buffer cache hit ratio is less than 99%, consider adding more RAM to your server.

·         If you are not already, use RAID level 10 for your arrays. RAID level 10 is the fastest RAID level you can choose that supports redundancy.

·         Add more physical drives to the current arrays. This helps to boost both read and write access times. But don't add more drives to the array than your I/O controller can support.

·         Replace your current hard drives with faster drives.

·         Add faster or additional I/O controllers. Consider adding more cache (if possible) to your current controllers.

·         Tune your application so that it doesn't have to access the disk as often. For example, add indexes so table scans aren't needed, normalize your database to eliminate redundant data, etc.) Be sure to only return the data required for a query, not any extra data not needed.

·         Check to see how many page splits there are. Excessive page splits can cause excessive disk I/O. Increasing the fillfactor or reindexing indexes more often may help to reduce excessive page splits.

·         Check to see how busy the tempdb database is. Perhaps excessive tempdb usage is causing excessive disk I/O. If so, either try to reduce the use of the tempdb, or move the tempdb database to its own dedicated disk array (RAID 1 or RAID 10 is best choice).

·         Move database or transaction log files from busy arrays to less busy arrays. Or if you have the resources, dedicate a separate disk array (RAID 1 is best) to each distinct database transaction log file.

·         Store your databases and transaction log files on a SAN (storage area network).

·         Used partitioned views and federated servers to distribute workload (2000 and 2005 only).

·         Ensure that SQL Server is the only application running on the server (other than server management utilities).

·         Remove or disable all unnecessary services on the SQL Server.

·         Consider running a disk defragmentation program, such as Diskeeper from Executive Software, on the server to defragment physical files.

·         Be sure you perform all database maintenance (backups, DBCC commands, etc.) during slow periods.

·         Turn off virus checking software.

[6.5, 7.0, 2000, 2005] Updated 6-20-2005

*****

If your SQL Server is experiencing CPU bottlenecks, consider these possible solutions:

·         Get faster CPUs, use dual or quad core CPUs, use hyperthreading CPUS, or add additional CPUs.

·         Get CPUs with a larger L2 cache.

·         Use Xeon-caliber CPUs.

·         Tune your application so that it doesn't have SQL Server does not have use as much CPU resources to complete a task. For example, add indexes so table scans aren't needed, normalize your database to eliminate redundant data, etc.). Be sure to only return the data required for a query, not any extra data not needed.

·         Tune your queries to reduce the CPU load. Hash Joins, for example, use a lot of CPU resources.

·         Avoid deadlocks, which use up a lot of CPU power.

·         Experiment with turning parallelism on and off to see which provides better overall performance.

·         Move some of the processing load to another SQL Server.

·         Consider turning on Windows NT fibers. [7.0, 2000, 2005 only]

·         Be sure that both OLTP and OLAP queries are not being run on the same server. These different database applications should be performed on separate servers.

·         Ensure that SQL Server is the only application running on the server (other than server management utilities).

·         Remove or disable all unnecessary services.

·         Be sure you perform all database maintenance (backups, DBCC commands, etc.) during slow periods.

·         Turn off any screen savers running on the server.

·         Turn off virus checking software.

[6.5, 7.0, 2000, 2005] Updated 6-20-2005