10

I'm looking at PLE (Page Life Expectancy) across NUMA nodes on our SQL Servers, and came across a rather weird distribution. NUMA node 000 has a very low PLE compared to 001. I'm unsure why this is. I've checked on multiple other SQL Servers in our environment, and the other production servers do not have this behavior.

System is running SQL Server 2012 Enterprise Edition on Dell m620 with 256GB Ram. It's a 2 socket, 6 core (HT enabled) machine. MAXDOP is set at 6. AFAIK memory modules are installed evenly across the memory banks of the CPUs

Something tells me NUMA node 000 has other SQL tasks to perform, that other nodes, but i've forgotten where I heard/saw it.

enter image description here

enter image description here

PLE image

@@Version shows: Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64)

migrated from stackoverflow.com May 3 '15 at 7:39

This question came from our site for professional and enthusiast programmers.

  • 2
    PLE on its own tells little. There are more counters like Buffer Node and Memory Node which can shed a bit more light. And ultimately: is there a performance problem, or this is just a curiosity? How to analyse SQL Server performance – Remus Rusanu May 3 '15 at 7:56
  • @RemusRusanu: If we are have a performance issue on our hands, no one knows :) i'm asking purely out of interest. – Kasper Brandenburg May 3 '15 at 17:34
  • If you see counter stolen nodes memory KB its value is 97G which is very high IMO. Stolen memory is memory not utilized for database purpose but by SQL Server for operations like sort, hash and other miscellaneous purposes. On other hand target and total memory are same. This seems strange. You must apply SP2 but I have feeling that PLE might be getting incorrectly calculated – Shanky May 4 '15 at 7:26
  • Alright. We might go SQL2014 visualized instead of installing SP2 – Kasper Brandenburg May 5 '15 at 7:13
1

If you have a read-intensive query running on one NUMA node (in this case, 0), then it can experience a lower page life expectancy relative to other NUMA nodes.

That's totally normal.

To see what queries are running right now, you can use Adam Machanic's excellent sp_WhoIsActive. It's totally free. Some folks even run it every X minutes and log the data into a table so they can go back to see what was running at the time PLE nosedived.

-1

My understanding of NUMA architecture is that each node pretty much isolates itself. In that case they could end up doing very different work. For example, 0 could be executing queries requiring lots of physical I/O while 1 gets lucky and finds all its data in the buffer pool.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.