Database Reference
In-Depth Information
Chapter 2
Memory Performance Analysis
A system can directly impact SQL Server and the queries running on it in three primary places: memory, disk, and
CPU. You're going to explore each of these in turn starting, in this chapter, with memory. Queries retrieving data
in SQL Server must first load that data into memory. Any changes to data are first loaded into memory where the
modifications are made, prior to writing them to disk. Many other operations take advantage of the speed of memory
in the system, from sorting data due to an ORDER BY clause in a query to performing calculations to create hash
tables for joining two tables. Because of all this work being done within the memory of the system, it's important that
you understand how memory is being managed.
In this chapter I cover the following topics:
•
The basics of the Performance Monitor tool
•
Some of the dynamic management objects used to observe system behavior
•
How and why hardware resources can be bottlenecks
•
Methods of observing and measuring memory use within SQL Server and Windows
•
Possible resolutions to memory bottlenecks
Performance Monitor Tool
Windows Server 2012 R2 provides a tool called Performance Monitor, which collects detailed information about the
utilization of operating system resources. It allows you to track nearly every aspect of system performance, including
memory, disk, processor, and the network. In addition, SQL Server 2014 provides extensions to the Performance
Monitor tool that track a variety of functional areas within SQL Server.
Performance Monitor tracks resource behavior by capturing performance data generated by hardware and
software components of the system, such as a processor, a process, a thread, and so on. The performance data
generated by a system component is represented by a performance object. The performance object provides counters
that represent specific aspects of a component, such as % Processor Time for a Processor object. Just remember,
when running these counters within a virtual machine (VM), the performance measured for the counters in many
instances, depending on the type of counter, is for the VM, not the physical server. That means some values collected
on a VM are not going to accurately reflect physical reality.
There can be multiple instances of a system component. For instance, the Processor object in a computer with
two processors will have two instances, represented as instances 0 and 1. Performance objects with multiple instances
may also have an instance called Total to represent the total value for all the instances. For example, the processor
