Home > Blogs > Virtualize Business Critical Applications


Monitor SQL Server Performance on VMware

Virtualization adds new software layers and new types of interactions between SQL Server and the hardware components. While the general methodology for monitoring and troubleshooting SQL Server performance does not change, VMware provides additional tools for monitoring and troubleshooting at the physical host level. This is the first of a series of Monitoring and Troubleshooting SQL Server performance on VMware blogs. In this blog, I'd like to discuss how to effectively monitor SQL Server performance on VMware.

SQL Server and Windows provide a number of tools for monitoring performance. Those are still the primary tools to use for identifying problem areas, and resource bottlenecks with SQL Server running on VMware. The methodologies stay the same as performance monitoring SQL Server in a physical environment.

  • Performance Monitor: monitors overall resource usages. SQL Server specific counters: \SQLServer:*\*
  • SQL Server Profiler: monitors performance at T-SQL statement level. Common events of interests are: SQL:BatchStarting/SQL:BatchCompleted, RPC:Starting/RPC:Completed, Errors and Warnings (All), P:StmtStarting/Completed, SQL:StmtStarting/Completed
  • DMVs: monitors the internal health of SQL Server, sys.dm_*

For more information on using these tools for monitoring and troubleshooting SQL Server, see Troubleshooting Performance Problem in SQL Server 2008.

VMware provides two main tools for observing and collecting performance data at the host-level: the vSphere Client and the esxtop/resxtop utilities.

vSphere Client:

  • GUI interface, primary tool for observing performance and configuration data for one or more ESX/ESXi hosts
  • Does not require high levels of privilege to access the data

esxtop/resxtop

  • Command line utility that runs in interactive, batch, or replay mode
  • Gives access to detailed performance data of a single ESX/ESXi host
  • Provides fast access to a large number of performance metrics
  • Requires root-level access

     

Figure 1. Performance Chart Views with vSphere Client

Figure 2. resxtop CPU Metrics

 

Among the performance data exposed by host-level monitoring tools, the following is a list of key metrics that can help you quickly isolate issues to a specific resource area such as CPU, memory, storage, or network. See VMware Communities: Interpreting esxtop Statistics and vCenter Performance Counters for a full list of counters.

Note** The measurement units reported in esxtop/resxtop and vSphere Client may be different. See the above VMware community links for details.

Resource

Metric (esxtop/resxtop)

Metric (vSphere Client)

Host/Virtual Machine

Description

CPU

%USED

Used

Both

CPU used over the collection interval (%)

%RDY

Ready

Virtual Machine

CPU time spent in ready state

%SYS

System

Both

Percentage of time spent in the ESX Server VMKernel

Memory

Swapin, Swapout

Swapinrate, Swapoutrate

Both

Memory ESX host swaps in/out from/to disk (per virtual machine, or cumulative over host)

MCTLSZ (MB)

vmmemctl

Both

Amount of memory reclaimed from resource pool by way of ballooning

Disk

READs/s, WRITEs/s

NumberRead, NumberWrite

Both

Reads and Writes issued in the collection interval

DAVG/cmd

deviceLatency

Both

Average latency (ms) of the device (LUN)

KAVG/cmd

KernelLatency

Both

Average latency (ms) in the VMkernel, also known as "queuing time"

GAVG/cmd

TotalLatency

Both

Average latency (ms) in the guest. GAVG = DAVG + KAVG

Network

MbRX/s, MbTX/s

Received, Transimitted

Both

Amount of data transmitted per second

PKTRX/s, PKTTX/s

PacketsRx, PacketsTx

Both

Packets transmitted per second

%DRPRX, %DRPTX

DroppedRx, DroppedTx

Both

Dropped packets per second

Of the CPU counters, the total used time indicates system load. Ready time indicates overloaded CPU resources. A significant swap rate in the memory counters is a clear indication of a shortage of memory, and high device latencies in the storage section point to an overloaded or misconfigured array. Network traffic is not frequently the cause of most database performance problems, but it is an area to monitor to make sure the network bandwidth is there.

When SQL Server is running in a virtual environment, any time-based measurements reported with SQL Server monitoring tools or Windows Perfmon may be inaccurate if the host machine resources are over-committed. The accuracy of the in-guest tools would depend on the guest OS and kernel version being used, and the total load of the VMware ESX host. The vSphere tools provide more accurate reporting on those measurements. See vSphere Performance Troubleshooting for more information. For effective monitoring and troubleshooting of SQL Server performance problem on VMware use the SQL Server tools and Perfmon for monitoring SQL Server resource usage, server state, and internal health. Focus on identifying performance bottlenecks instead of time-base measurements and then further correlate performance data collected from the host using vSphere tools. By focusing on key performance metrics you can quickly isolate issues to a particular resource area.

For example, for identifying a CPU bottleneck, instead of looking at the % Processor Time Perfmon counter, you can focus on the Process Queue Length counter. If there are a high number of processor queues and SQL Server Wait Statistics is showing a high number of waiters for worker, it's obvious a processor bottleneck exists. You can then use vSphere Client to verify:

  1. If the number of virtual processors allocated to the SQL Server virtual machine is sufficient.
  2. If the Shares, reservation, and limits for the virtual machine CPU Resource Allocation are configure properly.
  3. If there is a host overload with sustained high %used, and high %rdy.

Similarly, you can effectively identify a disk bottleneck if the Disk Queue Length is high and a high number of SQL Server users are waiting on PAGEIOLATCH_EX, PAGEIOLATCH_SH. After a resource bottleneck is identified on the SQL Server virtual machine you can correlate statistics collected from the vSphere Client and esxtop/resxtop to identify any host level configuration issue that's affecting the SQL Server virtual machine performance.

Thanks for reading. On my next blog, I will discuss identifying and solving some common SQL Server on VMware performance problems. Stay tuned…

 

-Wanda

Wanda He, Technical Solutions Architect

 

5 thoughts on “Monitor SQL Server Performance on VMware

  1. Richard Fohrenbach

    Wanda, Thanks for this. btw, parc is right next door to VMWARE HQ! I’m working from CT until I relocate in June. (But I have been to PA in Dec and FEB) We have our Deltek ERP on 4 Windows 2008 servers on VMWARE: Management server VMcenter 4.0 Update 1, build 08111 – - VMWARE hosts VMWare ESX 4.0 Update 1 vSphere Enterprise – - Struggling with performance issues after recent upgrade to SQl Server 2008 – Don’t know if it’s all sql server or whether I should allocate an additional processor to the database server.

    Reply
  2. Wanda He

    Hi Richard,
    If the performance issues were observed after SQL Server 2008 upgrade. I would suggest you to check for any query regression with the upgrade. If you are suspecting processor being the bottleneck, you may check the following perfmon counters to see if SQL Server is waiting for available processor: System\Processor Queue Length, SQLServer:Wait Statistics\Wait for the worker. Also the sys.dm_os_schedulers DMV within SQL Server can provide more information to confirm the bottleneck. If SQL Server is waiting on available processor, the querying of work_queue_count from sys.dm_os_schedulers should return value > 0.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>