Databases Reference
In-Depth Information
Information and Misinformation from Performance Monitor
Clearly, the same tools that used to reliably provide us with monitoring information can now be
sources of misinformation as you'll now see.
Performance Monitor is still the most efi cient way to monitor a virtual server's performance. The
only caveat is to ensure that you monitor the right counters from the right source. You'll look at
which specii c counters to use later in this chapter.
Some Performance Monitor counters collected from within the virtual server are as valid and
useful as they are on a physical server. Logical storage performance data, for example, will help you
monitor the virtual server's I/O workload and enables you to measure what percentage of the host
server's HBA capacity SQL Server is using, as well as ensure that SQL Server's read and write
latencies are acceptable.
While the role of the hypervisor is to make the virtual server believe it is running on dedicated
hardware and totally abstract it from the underlying physical hardware, some calls to specii c
hardware-related APIs are passed by the hypervisor straight through to the physical hardware. An
example of this is retrieving technical information about the CPU that an instance of Windows is
using. Figure 17-8 shows an example of information retrieved by Windows running on a Hyper-V
virtual server.
FIGURE 17-8
These hardware query requests are passed straight through to the hardware because it would be
difi cult for virtualization vendors to know what “artii cial” value to pass back today and in the future
in order to guarantee compatibility with any applications that check the version of the CPU on which
they're running. This behavior enables you to put the information that Windows or a tool like CPU-Z
returns into perspective, particularly as it's able to i nd the clock speed of the physical CPU even
though the hypervisor might be limiting your access to only a portion of the available clock speed.
SQL Server wait stats are another area to consider when you are determining your sources of
information or misinformation. However, even in the physical world, wait stats identify only the
symptom of a system issue, not its cause. Therefore, in the virtual world they are still excellent
indicators of potential issues hindering SQL Server's performance, and wait stats are a good source
of information, rather than potentially misleading misinformation.
Agent job runtimes are another source of excellent information within SQL Server you can use for
performance monitoring. By creating jobs that perform the same tasks with the same volumes of data
repeatedly, you can compare the time they took to run today with the time they took to run yesterday.
If, for example, you have a job to back up a database approximately 20GB in size, and for six weeks
it took 20 minutes to run but in the last few days it started taking longer, you may have identii ed a
reduction in the host server's I/O capabilities. This information on its own may not be of signii cant
operational value, but if your SQL Server instance has also started reporting a much greater occurrence
of
pageiolatch_xx
wait stats, you may well want to start looking outside of your virtual server i rst.
Search WWH ::
Custom Search