Performance troubleshooting on Microsoft SQL Server

Gridlock

Evaluating Performance Details

You should gain a quick overview of the server's resource consumption and compare it with the baseline, assuming you have one. To do so, launch PerfMon and add the following performance counters to the real-time display (Figure 1):

  • Memory Available MBytes and Page Faults/sec
  • Network Adapter Bytes Sent/sec and Bytes Received/sec
  • Physical Disk: Avg. Disk Queue Length for each volume
  • Processor Information: % User time and % Processor Time
  • System: Processor Queue Length
  • TCPv4: Segments/sec and Segments Retransmitted/sec
  • TCPv6: The same as TCPv4 if TCPv6 is used in production. Now is the time to find out which version is used – some systems automatically agree to use TCPv6 if it is technically possible.
Figure 1: A small selection of counters in PerfMon allows a quick overview.

Even better, define a data collector with these values to log data over a slightly longer period of time. Watch out for the following phenomena:

  • Network: Bytes Sent/sec and Bytes Received/sec approaching the available bandwidth. Keep in mind that the bandwidth is specified in bits per second. Thus, for a Gigabit Ethernet interface, the critical value is 131,072 bytes per second. If the measured values are permanently close to the upper limit, then the diagnosis is available in the network bandwidth, but it is also critical to look at the size of the returned results.
  • Network/TCP: Retransmitted segments make up too large a proportion of the total transferred segments on your network. Typical values fluctuate between 0.5 and 2 percent. Ask your network specialists to be sure.
  • CPU: The utilization of CPU on a single physical server that approaches the 100 percent mark is not fundamentally worrying, but if you see the CPU queue length permanently exceeding the limit of 1, you should be worried. In virtualized environments, this value can also be distorted by CPU scheduling on the hypervisor.
  • RAM: A system with no free RAM is not good. If the Available MBytes value is clearly and permanently less than 256, action is required. You can also check the rate of Page Faults/sec , which then shoots up. Because this metric has no generally accepted thresholds, you should compare it with your own baseline, if possible.
  • Physical disk queue length: If this classic metric is permanently greater than 1, you definitely have a problem, even though you cannot be sure at this juncture where the currently observed performance bottleneck originates.

Observe performance for a while and remember the troubleshooting sequence "network, CPU, RAM, disk" to help you to exclude normal parameters at an early stage and concentrate on what is essential.

Conclusions

Armed with the right tools, you can identify and resolve performance issues accurately with SQL Server. In the second article of this series, I will take a look at the network, RAM usage, and the hard disk drives. Finally, I will look into visualizing and evaluating the results.

Buy this article as PDF

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy ADMIN Magazine

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

  • Hyper-V with the SMB 3 protocol
    Microsoft has introduced several improvements to Windows Server 2012 and Windows Server 2012 R2 with its Server Message Block 3. Hyper-V mainly benefits from faster and more stable access to network storage. In this article, we look at the innovations.
  • Optimizing Windows Server 2016 performance
    With Windows Server 2016, tweaking the settings and taking advantage of performance monitoring tools can help boost your system's performance.
  • Exploring SQL Server on Linux
    SQL Server runs on Linux now. We'll show you how Microsoft developers made their massive database system Linux ready, and we'll help you get started with setting up SQL Server on your own Linux system.
  • What's new in SQL Server 2016
    The focus in SQL Server 2016 is on mobility, cloud usage, and speed, with improvements to in-memory processing and security.
  • Software-defined networking with Windows Server 2016
    Windows Server 2016 takes a big step toward software-defined networking, with the Network Controller server role handling the centralized management, monitoring, and configuration of network devices and virtual networks. This service can also be controlled with PowerShell and is particularly interesting for Hyper-V infrastructures.
comments powered by Disqus