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




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

· Add more physical RAM. If you are already at 2GB of physical RAM and are still having a memory problem, consider upgrading to SQL 2000 or 2005 Enterprise Edition, which can support up to 64GB of physical RAM using the AWE memory extension.

· Consider upgrading to the 64-bit version of SQL Server 2000 or 2005.

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

· Remove or disable all unnecessary services.

· Ensure SQL Server is running as a member server (not a domain controller).

· Configure SQL Server to allocate memory dynamically (the default), don't hard code how much RAM SQL Server is allocated (unless using AWE memory)

· Be sure that data pages don't have too much empty space, either because the fillfactor is too high, or because of page fragmentation. Extra space on database pages wastes space in the buffer cache.

· Turn off virus checking software.

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

*****

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

· Add faster network cards.

· Add additional network cards.

· Use the latest network card drivers.

· Server network card(s) should be attached to switches.

· Network cards should be running in full duplex mode.

· Manually hard code network cards to match the speed of the switch they are connected to. Don't use auto-detect sensing methods.

· Tune your application so that it does not require unnecessary network trips. Do this by returning only the required data and by using stored procedures.

· Remove all unnecessary network protocols from the server.

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

· Remove or disable all unnecessary services.

· Use TCP/IP as the network library on the client and server.

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