Monday, May 11, 2015

SQL Server PLE with 8 NUMA nodes

I posted earlier about PLE as a tricky performance metric - showing how a drop in PLE in some cases is a sign of performance improvement rather than performance drop :-)

Here's another quick set of thoughts about PLE - this time about the PLE on "home NUMA node" being more relevant to a given query than the overall PLE, or the PLEs of other NUMA nodes.

Because each NUMA node by default manages its own portion of the buffer pool cache, the number reported as the overall page life expectancy of SQL Server on an 8 NUMA node server is a composite of the 8 NUMA node-local PLEs.  I haven't been able to figure out how the overall PLE is calculated from node PLEs; I'm typically more interested in the amount of database cache and free memory per node anyway.  But here are some of the more interesting numbers. 

Note: these perfmon captures are from a SQL Server version where SQLOS still swapped to call NUMA node 0 "Buffer Node 1", and NUMA node 1 "buffer node 0".  SQL Server doesn't still do that in SQL Server 2014 :-)

Lets zoom in a little.  Here are the 8 NUMA node PLEs, and the dotted red line is the overall PLE.

The difference in PLE across the nodes is easy to understand once you notice the variation across NUMA nodes in CPU busy.  All of the CPU consumption on this server was from SQL Server.

Database page reads will insert the page into the buffer pool associated with the NUMA node that is home to the worker thread performing the read.  First writes, resulting in a newly allocated page, will also go into the NUMA node that is home for that worker.  Busier NUMA nodes(in terms of CPU utilization) can be expected to have higher insert rates.  Insert rate and database cache size are important contributors to PLE calculation.  But, there are other important considerations, too: rate of stealing, and rate of freeing - since these rates will determine the change in size of the database cache.  (Also, the rate of growth of the database cache - especially during rampup but also during other periods when other cache allotments such as procedure cache shrink resulting in lots of free pages.)

***** Update *****

Yeah, just wanted to throw in one more close-up :-)