Databases Reference
In-Depth Information
Likewise, if you have a very CPU-intensive SQL Server agent job, then looking for changes in that
job's runtime might also help you detect signs of CPU contention at the host server level.
Detecting Hypervisor Memory Reclamation
Knowing how much memory your virtual server has access to at any point in time is something you
should be able i nd the moment you suspect a previously healthy SQL Server instance has issues.
While different hypervisors have different ways to assign memory to virtual servers — based on
either demand, static allocations, or host server load — they usually all use a balloon driver to
reclaim memory if they ever have to start taking memory back from a virtual server.
Although Windows and SQL Server have hot-add memory features that enable you to add memory
to a running instance of SQL Server, they don't have an opposite feature whereby memory can be
taken away from Windows and SQL Server, yet this is sometimes a requirement in the virtual world
when memory is to be reclaimed by a balloon driver.
Monitoring this reclamation happening can be tricky because the amount of “physical” memory the
virtual server's operating system thinks it has never decreases. However, when the balloon driver
“inl ates,” the amount of available memory within Windows begins to drop, and when it falls below
an internal threshold SQL Server begins releasing memory to prevent the operating system from
running out. Figure 17-9 shows a before and after representation of the balloon drive inl ating.
Available Memory
Balloon Driver
SQL Server
Available Memory
SQL Server
Windows Operating System
Windows Operating System
Total Memory
Total Memory
FIGURE 17-9
One way to detect changes in the allocation of the memory SQL Server is using is to look for falls in
the Page Life Expectacy counter value or increases in the Pages/sec value.
If you're using Hyper-V, then another way is to query how SQL Server's memory utilization has
potentially changed in response to activity by the Dynamic Memory feature. The following query
returns not how much memory SQL Server is currently using but what percentage of your virtual
server's memory it's using. The reason for monitoring a percentage rather than an absolute value
Search WWH ::
Custom Search