If you’re driving down a six-lane highway at five o’clock in the afternoon, what do you think will happen when four of the lanes are closed a mile ahead? Will things slow down? That’s what I call a bottleneck! It is very easy to identify a bottleneck on a highway. However, bottlenecks can happen on the Microsoft SQL Server similar to the traffic situation described above. When you should take steps to identify the cause of them. There are many Performance Counters that will help you identify bottlenecks. Here are a few of them.
First, determine if there is at least 100 MB of free memory. Use the Memory > Available Mbytes counter to determine that. See if there is excessive page file usage from the following counters: Memory > Pages Input/Sec, Paging File > % Usage and Paging File > % Usage Peak. The following counter will tell you the amount of RAM the server is ready to consume: SQL Server: Memory Manager > Target Server Memory. If you notice a memory problem, add more memory. Don’t just add memory from the start. Take a look at what the server requires and add memory as needed.
If you determine that memory is not your bottleneck, check the hard drive performance. Use the following counters to see how much the disks are being used: PhysicalDisk > % Disk Time and PhysicalDisk > Avg. Disk Queue Length. High values will indicate a bottleneck. One thing that could severely impact the server’s performance is the time it spends waiting for data to be written or read from the hard drive. Check the time using the following counters: PhysicalDisk > Avg. Disk sec/Read and PhysicalDisk > Avg. Disk sec/Write. If this is slow, more than 50 milliseconds, you may have a problem. Optimal values are anywhere between 5 to 20 milliseconds depending on how responsive you require you server to be.
If you do have disk performance problems, look over the following guidelines and setup a new configuration.
• Make sure the database files are on a dedicated drive. Don’t look at drive letters, look at the physical setup using Disk Management (diskmgmt.msc). If the server is virtualized, open Disk Management on the host.
• If the SQL databases are on a dedicated drive, split the data files from the transaction log files. Put the data file on a fast RAID 5 or RAID 10 volume and the transaction log files on a dedicated RAID 1 or RAID 10 volume. It is important that the transaction log files are on a drive that does not delay SQL from writing data to the drives. They need to be on a volume that will give it fast instant access to write data.
• More spindles are better.
• TEMPDB may be moved to a dedicated RAID 1 or RAID 10 volume to increase performance in some cases.
• Make sure your transaction log files are sized properly and are not fragmented on the physical disk. Don’t rely on autogrowth.
Next, check you network performance. Make sure you have good results when you ping the server from a workstation. Install a tool that records the amount of time it takes to transfer a file from the workstation to the server. Make sure you are using gigabit network cards and that all of the network hardware between the workstation and server is gigabit capable. The results of a network transfer tool will indicate if you have a problem.
The last thing you need to check is the CPU performance. Make sure the Processor > %Processor Time is not higher than 80 percent. Take a look at the System > ProcessorQueue Length counter over time to determine if you need faster or more processors. The queue length should not be higher than 2.
There is no way around it. You just have to sit down and spend the time in order to identify bottlenecks. Look at the server’s Performance Counters, and create a baseline. Compare the baseline with a second result set to see if there was improvement. In addition, ask the users what they are experiencing. Try timing certain tasks and see how long those same tasks take after you have made your changes.
If you need someone to review your system for performance problems contact a partner like The Resource Group (). The Resource Group offers a , which is performed by a Systems Engineer which will give you a list of recommendations to increase system performance.
by The Resource Group
