Database Reference
In-Depth Information
Chapter 5
Creating a Baseline
In the previous three chapters, you learned a lot about various possible system bottlenecks caused by memory, the
disk, and the CPU. I also introduced a number of Performance Monitor metrics for gathering data on these parts of
the system. Within the descriptions of most of the counters, I referred to comparing your metric to a baseline. This
chapter will cover how to gather your metrics so that you have that baseline for later comparison. I'll go over how to
configure an automated method gathering this information. A baseline is a fundamental part of understanding system
behavior, so you should always have one available. This chapter covers the following topics:
•
Considerations for monitoring virtual and hosted machines
•
How to set up an automated collection of Performance Monitor metrics
•
Considerations to avoid issues when using Performance Monitor
•
Creating a baseline
Considerations for Monitoring Virtual and Hosted Machines
Before you start creating the baseline, I will talk about virtual machines (VMs). More and more SQL Server
instances are running on VMs. When you are working with VMs or you are hosting VMs in remote environments
such as Amazon or Microsoft Azure, many of the standard performance counters will no longer display meaningful
information. If you monitor these counters within the VM, your numbers may not be helpful from a troubleshooting
perspective. If you monitor these counters on the physical box, assuming you have access to it, which doubtless is
shared by multiple different VMs, you will be unable to identify specific SQL Server instance resource bottlenecks.
Because of this, additional information must be monitored when working with a VM. Most of the information that you
can gather on disk and network performance are still applicable within a VM setting. And all query metric information
will be accurate to those queries. It's the memory and CPU metrics that are completely different and quite unreliable.
This is because CPU and memory are shared between machines within a virtualized server environment. You
may start a process on one CPU and finish it on another one entirely. Some virtual environments can actually change
the memory allocated to a machine as that machine's demands for memory go up and down. With these kinds of
changes, traditional monitoring just isn't applicable. The good news is that the major VM vendors provide you with
guidance on how to monitor their systems and how to use SQL Server within their systems. You can largely rely on
these third-party documents for the specifics of monitoring a VM. Taking the two most common hypervisors, VMware
and HyperV, here is a document from each:
•
VMware Monitoring Virtual Machine Performance (
•
Measuring Performance on HyperV (
