Performance Tuning SQL Server Hardware

If your SQL Server is not connected to a switch (as recommended for best performance), try the following suggestions for boosting network performance. If you are running Ethernet, try to reduce the number of computers on the same segment as your SQL Server, and if possible, try to reduce the length of the physical segment your SQL Server is connected to. Also, don’t cascade hubs. If you are running Token-Ring, try to reduce the physical length of the ring and try to increase the number of active stations on the ring (reduces token travel time). [6.5, 7.0, 2000, 2005]

*****

Be sure that your the network card(s) in your server are set to the same duplex level (half or full) and speed as the switched port they are connected to (assuming they are connected to a switch, and not a hub). If there is a mismatch, the server may still be able to connect to the network, but network performance can be significantly impaired.

Don’t rely on network cards or switch that are supposed to auto-sense duplex or speed settings, as they often don’t work correctly. You will want to manually set the duplex and speed for the card from the operating system, and if necessary manually make the same changes to the switch. [6.5, 7.0, 2000, 2005] Updated 6-20-2001

*****

Assuming SQL Server is running on a dedicated server (for best performance, it should be), limit the number of network protocols installed on the server. NT Server allows multiple network protocols to be installed on a server. Unnecessary network protocols increase overhead on the server and send out unnecessary network traffic. For the best overall performance, only install TCP/IP on the server, and use the SQL Server TCP/IP Network Library to communicate to clients. [6.5, 7.0, 2000, 2005]

*****

While not always possible (especially for WANS and Internet connections), try to avoid a router between SQL Server clients and SQL Server. Especially try to avoid routers between two or more SQL Servers that need to communicate among each other. Routers are often a bottleneck for network traffic and can affect SQL Server client/server performance. If you have no choice but to go over a router, ensure that the router has been properly tuned for maximum performance. [6.5, 7.0, 2000, 2005]

*****

SQL Server 2000/2005 Enterprise Edition offers support for virtual device interface (VDI) network card adapters, which can greatly speed the network performance of SQL Server. This style of network card adapter provides a much more efficient protocol stack, which not only boosts network throughput, it also reduces the workload on the server, further increasing your server’s performance. [2000, 2005] Added 12-19-2000

*****

Windows 2000/2003 allows network cards to save energy by going to sleep when they are not used. If your network card has a power management feature, you will want to ensure that you have turned the power savings feature off on any cards in your production servers. Otherwise, you may experience some unexpected results, such as a network card that fails to wake up, or intermittent performance problems.

You can check to see if your network card has a power management feature by viewing the Properties sheet for your network card’s driver. If you see a Power Management tab on the Properties sheet, then check it to see how it has been set. [7.0, 2000, 2005] Added 7-03-2001