{"id":1040,"date":"2016-01-17T10:31:50","date_gmt":"2016-01-17T18:31:50","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1040"},"modified":"2020-01-12T11:03:39","modified_gmt":"2020-01-12T19:03:39","slug":"sql-server-diagnostic-information-queries-detailed-day-17","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 17"},"content":{"rendered":"

For Day 17 of this series, we start out with Query #39<\/strong>, which is PLE by NUMA Node. This query retrieves information from the sys.dm_os_performance_counters<\/a> dynamic management view about your page life expectancy (PLE) by NUMA node. Query #39 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Page Life Expectancy (PLE) value for each NUMA node in current instance  (Query 39) (PLE by NUMA Node)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> @@SERVERNAME<\/span> AS<\/span> [Server Name], [object_name], instance_name, cntr_value AS<\/span> [Page Life Expectancy]<\/pre>\n
<\/p>\n
   3:<\/span> FROM<\/span> sys.dm_os_performance_counters WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   4:<\/span> WHERE<\/span> [object_name] LIKE<\/span> N'%Buffer Node%'<\/span> -- Handles named instances<\/span><\/pre>\n
<\/p>\n
   5:<\/span> AND<\/span> counter_name = N'Page life expectancy'<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   6:<\/span>  <\/pre>\n
<\/p>\n
   7:<\/span> -- PLE is a good measurement of memory pressure<\/span><\/pre>\n
<\/p>\n
   8:<\/span> -- Higher PLE is better. Watch the trend over time, not the absolute value<\/span><\/pre>\n
<\/p>\n
   9:<\/span> -- This will only return one row for non-NUMA systems<\/span><\/pre>\n
<\/p>\n
  10:<\/span>  <\/pre>\n
<\/p>\n
  11:<\/span> -- Page Life Expectancy isn\u2019t what you think\u2026<\/span><\/pre>\n
<\/p>\n
  12:<\/span> -- https:\/\/www.sqlskills.com\/blogs\/paul\/page-life-expectancy-isnt-what-you-think\/<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #39 PLE by NUMA Node<\/strong><\/p>\n
I think that page life expectancy (PLE) is probably one of the best ways to gauge whether you are under internal memory pressure, as long as you think about it correctly. What you should do is monitor your PLE value ranges over time so that you know what your typical minimum, average, and maximum PLE values are at different times and on different days of the week. They will usually vary quite a bit according to your workload.<\/p>\n
The ancient guidance that a PLE measurement of 300 or higher is good, is really not relevant with modern database servers with much higher amounts of physical RAM compared to 10-12 years ago. Basically, higher PLE values are always better<\/a>. You want to watch the ranges and trends over time, rather than focus on a single measurement.<\/p>\n
 <\/p>\n
 <\/p>\n
Query #40<\/strong> is Memory Grants Pending. This query retrieves information from the sys.dm_os_performance_counters<\/a> dynamic management view about the current value of the Memory Grants Pending performance counter. Query #40 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Memory Grants Pending value for current instance  (Query 40) (Memory Grants Pending)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> @@SERVERNAME<\/span> AS<\/span> [Server Name], [object_name], cntr_value AS<\/span> [Memory Grants Pending]                                                                                                       <\/pre>\n
<\/p>\n
   3:<\/span> FROM<\/span> sys.dm_os_performance_counters WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   4:<\/span> WHERE<\/span> [object_name] LIKE<\/span> N'%Memory Manager%'<\/span> -- Handles named instances<\/span><\/pre>\n
<\/p>\n
   5:<\/span> AND<\/span> counter_name = N'Memory Grants Pending'<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   6:<\/span>  <\/pre>\n
<\/p>\n
   7:<\/span> -- Run multiple times, and run periodically is you suspect you are under memory pressure<\/span><\/pre>\n
<\/p>\n
   8:<\/span> -- Memory Grants Pending above zero for<\/span> a sustained period is<\/span> a very strong indicator<\/span> of<\/span> internal memory pressure<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #40 Memory Grants Pending<\/strong><\/p>\n
This query is another way to determine whether you are under severe internal memory pressure. The value of this query will change from second to second, so you will want to run it multiple times when you suspect you are under memory pressure. Any sustained value above zero is not a good sign. In fact, it is a very bad sign, showing that you are under pretty extreme memory pressure. <\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 17 of this series, we start out with Query #39, which is PLE by NUMA Node. This query retrieves information from the sys.dm_os_performance_counters dynamic management view about your page life expectancy (PLE) by NUMA node. Query #39 is shown in Figure 1. 1: — Page Life Expectancy (PLE) value for each NUMA node […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[270],"class_list":["post-1040","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-sql-server-diagnostic-information-queries-detailed"],"yoast_head":"\nSQL Server Diagnostic Information Queries Detailed, Day 17 - Glenn Berry<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Diagnostic Information Queries Detailed, Day 17 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 17 of this series, we start out with Query #39, which is PLE by NUMA Node. This query retrieves information from the sys.dm_os_performance_counters dynamic management view about your page life expectancy (PLE) by NUMA node. Query #39 is shown in Figure 1. 1: -- Page Life Expectancy (PLE) value for each NUMA node […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-17T18:31:50+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-01-12T19:03:39+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 17 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-17T18:31:50+00:00\",\"dateModified\":\"2020-01-12T19:03:39+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 17\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Diagnostic Information Queries Detailed, Day 17 - Glenn Berry","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 17 - Glenn Berry","og_description":"For Day 17 of this series, we start out with Query #39, which is PLE by NUMA Node. This query retrieves information from the sys.dm_os_performance_counters dynamic management view about your page life expectancy (PLE) by NUMA node. Query #39 is shown in Figure 1. 1: -- Page Life Expectancy (PLE) value for each NUMA node […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-17T18:31:50+00:00","article_modified_time":"2020-01-12T19:03:39+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 17 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-17T18:31:50+00:00","dateModified":"2020-01-12T19:03:39+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-17\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"SQL Server Diagnostic Information Queries Detailed, Day 17"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1040","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1040"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1040\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1040"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1040"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1040"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}