SQL Server Memory Accounting with Perfmon

After learning to account for database memory accounting with DMVs & DMFs, the numbers from perfmon can be downright confusing.  Lack of alignment with the DMV/DMF values notwithstanding* I believe using perfmon to profile SQL Server resource utilization and response times can be extremely valuable.  For me, its an indespensible tool.  Using a perfmon counters file and a bat script with a logman command, I can easily distribute a tool to collect metrics from a physical server or vm running SQL Server in 30 second intervals.  I wouldn't want to try to collect the same information in 30 second or even 1 minute intervals with SQL Agent or service broker.  Anyway...

Let's look at the total of SQL Server shared memory for SQL Server 2012 and beyond.  As I've mentioned before, the shared memory can be fully accounted for with database cache, stolen, and free categories.

By zooming in on the 108 GB to 124 GB range we can see that the sum of the three categories is equal to "Total Server Memory".

Lets look at stolen memory a bit.  The relationship between memory grants and stolen memory is probably the least intuitive relationship.  Remember - if a query gets a memory grant the grant happens at the beginning of query execution.  Its just a promise of sort/hash memory to be made available when the query needs it.  The grant memory isn't stolen immediately - rather its stolen in small allocations over time as needed by the query.

In the graph immediately below, the outstanding grants are shown over time.  There are no pending grants during the observation period.  Granted memory and reserved memory are both shown as areas, with reserved memory in front of granted memory.  Granted memory is consistently greater than reserved memory (in this case, no resource pools have been added beyond the pre-existing default and internal pools).  This is how we can determine that the reserved memory is granted memory which hasn't been stolen yet.

Here is the difference between granted memory and reserved memory, graphed as single measure area graph below, with the number of outstanding grants in a line graph.

Now lets stack several memory components in an area graph in front of stolen memory.  Let's put in several perfmon metrics directly as well as the difference between granted and reserved memory.

Notice that plan cache pages increase slowly throughout the capture period.  The amount of query memory used (granted kb - reserved kb) is more variable, and together with the other memory metrics selected trends very well with total stolen memory.

There's definitely at least 1 significant stolen memory consumer since up to 5 GB of stolen memory is currently unaccounted for.

Main things I wanted to show here:
-used query memory is accounted for in perfmon stolen memory
-although plan cache bloat is a fairly common topic, some workloads exeperience much more pressure from workspace memory than from the plan cache.

*as well as some other issues with perfmon that I'll detail at a later date

Harmonic mean of SQLOS Buffer Node PLEs on NUMA servers

By default, on a (v)NUMA server/vm, SQL Server will partition its shared memory resources by using a count of SQLOS memory nodes equal to the visible (v)NUMA nodes.

In that strategy, each SQLOS memory node gets its own IO completion port, its own lazy writer and in SQL Server 2016 there will be one transaction log writer per node (up to 4, on (v/l)cpus 1-4 as needed).  Each SQLOS node gets a portion of the database cache, stolen memory, and free memory.

When pages read from disk are inserted into the bpool for a worker, they are inserted into the database cache for the SQLOS memory node associated with the compute node for the scheduler running the worker.

Put all that together: each SQLOS memory node may be experiencing different rate and footprint of pages read, first-writes to new database pages, steal against memory grant (or other steals) and freeing memory.  I don't know the formula used for PLE... but each of those factors are part of the calculation.  So... each SQLOS node has its own PLE, and these PLE values can vary greatly.

Yet the node level PLE is a metric seldom checked.  Rather, the overall calculated PLE for the instance is the metric usually consulted.  Here's that metric added into the same graph.

I've been puzzling for quite a while how the overall PLE was calculated.  Its fairly obvious its not an arithmetic mean of the PLEs - it varies gradually even as individual PLEs vary greatly.  I figured perhaps it was a weighted average, maybe with weight determined by the amount of database cache on the individual nodes.

But that could also be quite volatile, since especially in the case of large hashes, memory can be stolen against a grant very quickly - rapidly shrinking the amount of database cache on an individual SQLOS node.

Eventually this Paul Randal blog post was pointed out to me.  The post is originally from 2011, but after Matt Slocum pointed out that arithmetic mean didn't fit for deriving the overall PLE it was updated in 2015. 

http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/

"The calculation is: add the reciprocals of (1000 x PLE) for each node, divide that into the number of nodes and then divide by 1000."

OK... let's plug that formula into Excel along with the data for the 4 SQLOS nodes and the overall PLE to see if it fits.

That's a pretty good fit.  The very first harmonic mean I calculate rounds to 142 rather than the value of 144 that is the overall PLE.  That's not surprising - after seeing how good the fit is otherwise, I suspect that was a timing issue - volatility in the 000 and 001 PLEs probably lead to a slightly different value between the time that the individual SQLOS node values were reported and the value a short time later when the overall PLE was reported.

A graph of my calculated harmonic mean and the overall PLE shows just how good the fit is...

That's good enough for me :-)

I'm glad that Paul Randal and Matt Slocum dug into this... one fewer question in my heap.

Migration to pvscsi from LSI for SQL Server on VMware; It *really* matters

I expect it to make a big difference.  Even so, I'm still pleasantly surprised how much of a difference it makes.

About the VM:
8 vcpu system.
19 logicaldisk/physicaldisks other than the C Windows install drive.
About the guests vdisks:
Each guest physicaldisk is its own datastore, each datastore is on a single ESXi host LUN.

On July 27th, the 19 SQL Server vdisks were distributed among 3 LSI vHBA (with 1 additional LSI vHBA reserved for the C install drive).

I finally caught back up with this system.  An LSI vHBA for the C install drive has been retained.  But the remaining 3 LSI vHBA have been switched out by pvscsi vHBA.

The nature of the workload is the same on both days, even though the amount of work done is different.  Its a concurrent ETL of many tables, with threads managed in a pool and the pool size is constant between the two days.

Quite a dramatic change at the system level :-)

Lets first look at read behavior before and after the change.  I start to cringe when read latency for this workload is over 150 ms.  100 ms I *might* be able to tolerate.  After changing to the pvscsi vHBA it looks very healthy at under 16 ms.

OK, what about write behavior?

Ouch!! The workload can tolerate up to 10ms average write latency for a bit.  5 ms is the performance target.  With several measures above 100 ms write latency on July 28th, the system is at risk of transaction log buffer waits, SQL Server free list stalls, and more painful than usual waits on tempdb.  But after the change to pvscsi, all averages are below 10 ms with the majority of time below 5 ms.  Whew!

Looking at queuing behavior is the most intriguing :-) Maximum device and adapter queue depth is one of the most significant differences between the pvscsi and LSI vHBA adapters. The pvscsi adapter allows increasing the maximum adapter queue depth from default 256 all the way to 1024 (by setting a Windows registry parameter for "ringpages"). Also allows increasing device queue depth from default 64 to 256 (although storport will pass no more than 254 at a time to the lower layer).  By contrast, LSI adapter and device queue depths are both lower and no increase is possible.

It may be counter-intuitive unless considering the nature of the measure (instantaneous) and the nature of what's being measured (outstanding disk IO operations at that instant).  But by using the vHBA with higher adapter and device queue depth (thus allowing higher queue length from the application side), the measured queue length was consistently lower.  A *lot* lower. :-)

Perfmon SSIS Counters missing? Try running as admin...

A colleague and I were hoping to review SSIS perfmon counters on a VM.  We use a logman command with a counters file to log perfmon to csv.

Opened up the csv that was captured on the VM... there were all of my typical SQL Server counters... but the following SSIS counters were missing.

\SQLServer:SSIS Service\SSIS Package Instances
\SQLServer:SSIS Pipeline\Buffer memory
\SQLServer:SSIS Pipeline\Buffers in use
\SQLServer:SSIS Pipeline\Buffers spooled
\SQLServer:SSIS Pipeline\Flat buffer memory
\SQLServer:SSIS Pipeline\Flat buffers in use
\SQLServer:SSIS Pipeline\Private buffer memory
\SQLServer:SSIS Pipeline\Private buffers in use
\SQLServer:SSIS Pipeline\Rows read
\SQLServer:SSIS Pipeline\Rows written

Huh.

Got on the vm, and used:
typeperf -q | find "SSIS"

That command returned one line:
\SQLServer:SSIS Service 11.0\SSIS Package Instances

Huh.

I recalled recently learning that the VMware perfmon counters require privilege to display.  As I prattled on about that, my colleague simply launched a cmd.exe as an administrator.

In that elevated cmd.exe, the previous typeperf command returned all of the SSIS counters we expected.

Except they were all versioned, so we updated the counters file like so:
\SQLServer:SSIS Service 11.0\SSIS Package Instances
\SQLServer:SSIS Pipeline 11.0\Buffer memory
\SQLServer:SSIS Pipeline 11.0\Buffers in use
\SQLServer:SSIS Pipeline 11.0\Buffers spooled
\SQLServer:SSIS Pipeline 11.0\Flat buffer memory
\SQLServer:SSIS Pipeline 11.0\Flat buffers in use
\SQLServer:SSIS Pipeline 11.0\Private buffer memory
\SQLServer:SSIS Pipeline 11.0\Private buffers in use
\SQLServer:SSIS Pipeline 11.0\Rows read
\SQLServer:SSIS Pipeline 11.0\Rows written

With the perf counters file updated, and executing the logman command as an administrator, all of the expected counters appeared in our csv.

Now I should point out: there are other reasons the counters may be missing.  Sometimes they need to be reloaded with a lodctr command, like the following post.

Perfmon Counters for SSIS Pipeline

But that'll have to be a story for another day.

Finé

Windows Guest: VMware Perfmon metrics intermittently missing?

I'm using logman to collect Windows, SQL Server, and VMware metrics in a csv file(30 second intervals).

Not sure what's causing the sawtooth pattern below.  I thought it was a timekeeping problem on the ESXi host, now not so sure.

See this Ryan Ries blog post for what may be a similar issue, caused by clock sync of guest with ESXi host.
https://www.myotherpcisacloud.com/post/Mystery-of-the-Performance-Counters-with-Negative-Denominators!
But that is also a reversal of the problem somewhat.  In that case, guest metrics were returning -1.  In my case, its the VMware metrics (passed through from the host) that are missing.

The same pattern for "Host processor speed in MHz" adds to the mystery.

If only "Effective VM Speed in MHz" was missing, I'd chalk it up to a possible arithmetic issue, with a negative denominator resulting from time skew between guest and host.

But... "Host processor speed in MHz" should be a constant 2600 in this case.  Maybe somehow its still calculated with a time interval, and time skew can screw it up?

For now, I've got a loop running on this VM logging local time, and using NET TIME to retrieve time from another VM as well.  That's turned up variations, but it seems to be variations of up to 5 seconds in contacting the other VM rather than large time skew between the VMs.

Guess I'll see what turns up...

*****
So far this is the closest I've found.  Similar problem reported - no resolution.

"Windows VM perfmon counters - VM Processor"
https://communities.vmware.com/thread/522281

Modeling SQL Server Behavior for Performance Intervention: Feed the CPUs

Before recommending any changes on a SQL Server system, I like to collect a number of days worth of perfmon data.  -- I also like to ask if there are particular windows of time or particular queries that are performance concerns.  That can help prevent me from spending lots of time on time windows or queries that are considered low value :-) But that's a story for another day.

When reviewing perfmon data with the intent of finding system optimizations, I like to say:
"It helps to have expected models of behavior, and a theory of performance."

I don't say that just to sound impressive - honest (although if people tell me it sounds impressive I'll probably say it more frequently).  There are a lot of thoughts behind that, I'll try to unpack them over time.

When I work with SQL Server batch-controlled workflows, I use the theory "feed the CPUs".  That's the simplest positive adaptation I could come up with of Kevin Closson's paradigm "Everything is a CPU problem" :-)

What I mean by "Feed the CPUs" is that memory and disk response times are primary factors determining the maximum rate for the CPUs to process the data.  Nuts & bolts of such a model for SQL Server are slightly different than a similar model for Oracle.  SQL Server access to persistent data is always through database cache, while Oracle uses shared access to database cache in SGA and private access to persistent data through direct access in PGA.

Here's where I'll throw in a favorite quote.

*****
"Essentially, all models are wrong, but some are useful."
Box, George E. P.; Norman R. Draper (1987)
Empirical Model-Building and Response Surfaces, p. 424
Wiley ISBN 0471810339
*****

The workflows I work with most often are quite heavy on disk access.  The direction of change of CPU utilization over time will usually match the direction of change in read bytes per second.  So if you want to improve performance - ie shorten the elapsed time of a batch workload on a given system - Feed the CPUs.  Increase average disk read bytes per second over the time window.

That's a very basic model.  It ignores concepts like compression.  In my case, all tables and indexes should be page compressed.  If not I'll recommend it :)  It kinda sidesteps portions of the workload that may be 100% database cache hit.  (Although I consider that included in the "usually" equivocation.)  It definitely doesn't account for portions of the workload where sorting or other intensive work (like string manipulation) dominates CPU utilization.  Still, a simple model can be very useful.

Here's a 2 hour time window from a server doing some batch ETL processing.  You can kinda see how CPU utilization trends upward as read bytes per second trends upward, right?  I've highlighted two areas with boxes to take a closer look.

Let's look at the 8:15 to 8:30 am window first.  By zooming in on just that window & changing the scale of the CPU utilization axis, the relationship between CPU utilization and read bytes/second is much easier to see.  That relationship - direction of change - is the same as other parts of the 2 hour window where the scale is different.  Depending on the complexity of the work (number of filters, calculations to perform on the data, etc) with the data, the amount of CPU used per unit of data will vary.  But the relationship of directionality* still holds.  I'm probably misusing the term 'directionality' but it feels right in these discussions :-)

All right.  Now what about the earlier time window?  I've zoomed in a bit closer and its 7:02 to 7:07 that's really interesting.

Assuming that SQL Server is the only significant CPU consumer, and assuming that that whole 5 minute time isn't dominated by spinlocks (which at ~40% CPU utilization doesn't seem too likely), the most likely explanation is simple: 5 minutes of 100% cache hit workload, no physical reads needed.  That can be supported by looking at perfmon counters for page lookups/second (roughly logical IO against the database cache) and the difference between granted memory and reserved memory (which is equal to the amount of used sort/hash memory at that time).

OK... well, that's all the time I want to spend justifying the model for now :-)

Here's the original graph again, minus the yellow boxes.  There's plenty of CPU available most of the time.  (No end users to worry about on the server at that time so we can push it to ~90% CPU utilization comfortably.) Plenty of bandwidth available.  If we increase average read bytes/sec we can increase average CPU utilization.  Assuming that the management overhead is insignificant (spinlocks, increased context switching among busy threads, etc), increasing average CPU utilization will result in a corresponding decrease in elapsed time for the work.

All right.  So next we can investigate how we'll increase read bytes per second on this system.

Ciao for now!

SSAS Multidimensional Cube Processing - Shrinking the unfamiliar window

Last week I posted some of my perfmon graphs from an SSAS server.  I want to model the work happening on an SSAS server during multidimensional cubes processing - both dimension and fact processing.

That's here:
SSAS Multidimensional Cube Processing - What Kind of Work is Happening?
http://sql-sasquatch.blogspot.com/2016/07/ssas-multidimensional-cube-processing.html

There was a window at the end of the observation period where data was still flowing across the network, CPU utilization was still high enough to indicate activity, but the counters for rows read/converted/written/created per second were all zero.  The index rows/sec counter was also zero.

A colleague read my post, looked at my data... and pointed out:
"One of your graphs shows what was happening there!"

Sure enough, he was right.  Even though I time-shifted my graphs to somewhat obscure the data, he noticed what activity filled the gaps.  I'm gonna hafta work hard to make sure these guys don't lap me soon. :-)

  

In-memory file work, mainly for dimensions. Well, that shrinks the mystery window for me :-)

Let's look at the very tail end of the observation.

 

 

At the very tail end of the observation period, data stops coming over the network.  But CPU utilization increases. And CPU utilization drops off as "In-Memory Other File KB" drops off.

So this "other file" was somewhat expensive to handle, even though only for a limited time.

Wonder what that file was, since it wasn't a Dimension Index, Property, or String file?

By now you're probably wondering - why don't you just use profiler already? :-)

I'll get there - soon, probably.  But I'm trying to lay the groundwork for understanding system activity independent of knowing the individual cubes being processed.  My colleagues and I will likely be reviewing many perfmon captures across many cubes servers.  Some of the captures will almost certainly be started after cubes processing has begun.

So I'm hoping to get as far as possible from just the perfmon files, before returning for another source of information.

But certainly - being able to tie out system activity to process step beginning and end from profiler is preferable if in a position to influence cube design and development from the front end :-)