{"id":1029,"date":"2016-01-06T07:17:23","date_gmt":"2016-01-06T15:17:23","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1029"},"modified":"2017-04-13T12:28:44","modified_gmt":"2017-04-13T19:28:44","slug":"sql-server-diagnostic-information-queries-detailed-day-6","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-6\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 6"},"content":{"rendered":"

For Day 6 of this series, we start out with Query #11<\/strong>, which is SQL Server NUMA Info. This query retrieves information from the sys.dm_os_nodes<\/a> dynamic management view about the NUMA node structure that has been created by the SQLOS. By default, this will mimic the hardware processor locality (whether it is the actual hardware of the host, or a virtual machine), but this structure can be altered by using soft-NUMA. Query #11 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- SQL Server NUMA Node information  (Query 11) (SQL Server NUMA Info)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count, <\/pre>\n
<\/p>\n
   3:<\/span>        active_worker_count, avg_load_balance, resource_monitor_state<\/pre>\n
<\/p>\n
   4:<\/span> FROM<\/span> sys.dm_os_nodes WITH<\/span> (NOLOCK) <\/pre>\n
<\/p>\n
   5:<\/span> WHERE<\/span> node_state_desc <> N'ONLINE DAC'<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   6:<\/span>  <\/pre>\n
<\/p>\n
   7:<\/span> -- Gives you some useful information about the composition and relative load on your NUMA nodes<\/span><\/pre>\n
<\/p>\n
   8:<\/span> -- You want to see an equal number of schedulers on each NUMA node<\/span><\/pre>\n
<\/p>\n
   9:<\/span> -- Watch out if SQL Server 2016 Standard Edition has been installed on a machine with more than 16 physical cores<\/span><\/pre>\n
<\/p>\n
  10:<\/span>  <\/pre>\n
<\/p>\n
  11:<\/span> -- Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes<\/span><\/pre>\n
<\/p>\n
  12:<\/span> -- https:\/\/www.sqlskills.com\/blogs\/glenn\/balancing-your-available-sql-server-core-licenses-evenly-across-numa-nodes\/<\/span><\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #11 SQL Server NUMA Info<\/strong><\/p>\n
This query tells you how many NUMA nodes are being used by SQLOS. In a modern, NUMA-capable server, you should see one NUMA node for each physical processor socket (unless someone has disabled NUMA in the UEFI\/BIOS). So a two-socket server should have two NUMA nodes. If you are using virtualization, you might see a  VM with only one NUMA node and many virtual processor cores, or whoever created the VM might have used multiple NUMA nodes with fewer virtual processor cores per NUMA node. You also want to confirm that you are seeing the same online_scheduler_count on each NUMA node. <\/p>\n
Query #12<\/strong> is System Memory. This query retrieves information from the sys.dm_os_sys_memory<\/a> dynamic management view about the memory usage by the operating system. Query #12 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Good basic information about OS memory amounts and state  (Query 12) (System Memory)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> total_physical_memory_kb\/1024 AS<\/span> [Physical Memory (MB)], <\/pre>\n
<\/p>\n
   3:<\/span>        available_physical_memory_kb\/1024 AS<\/span> [Available Memory (MB)], <\/pre>\n
<\/p>\n
   4:<\/span>        total_page_file_kb\/1024 AS<\/span> [Total Page File<\/span> (MB)], <\/pre>\n
<\/p>\n
   5:<\/span>        available_page_file_kb\/1024 AS<\/span> [Available Page File<\/span> (MB)], <\/pre>\n
<\/p>\n
   6:<\/span>        system_cache_kb\/1024 AS<\/span> [System Cache (MB)],<\/pre>\n
<\/p>\n
   7:<\/span>        system_memory_state_desc AS<\/span> [System Memory State<\/span>]<\/pre>\n
<\/p>\n
   8:<\/span> FROM<\/span> sys.dm_os_sys_memory WITH<\/span> (NOLOCK) OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   9:<\/span>  <\/pre>\n
<\/p>\n
  10:<\/span> -- You want to see \"Available physical memory is high\" for System Memory State<\/span><\/pre>\n
<\/p>\n
  11:<\/span> -- This indicates that you are<\/span> not<\/span> under<\/span> external<\/span> memory pressure<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #12 System Memory<\/strong><\/p>\n
This query tells you how much memory is visible to the operating system (whether it is the host OS or the guest OS for a virtual machine). It also tells you how much memory is available to the operating system, which is normally a relatively low amount when SQL Server has seen a normal workload. It is very important to look at the system_memory_state_desc column result, which is the result of two separate flags about the system memory state.<\/p>\n
What you want to see is \u201cAvailable physical memory is high\u201d, which means that the OS has plenty of available memory, and you are not under external memory pressure. What you might see in some cases is  \u201cAvailable physical memory is steady\u201d, or \u201cAvailable physical memory is low\u201d, which means that you are under external memory pressure, which is something you don\u2019t want to see.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 6 of this series, we start out with Query #11, which is SQL Server NUMA Info. This query retrieves information from the sys.dm_os_nodes dynamic management view about the NUMA node structure that has been created by the SQLOS. By default, this will mimic the hardware processor locality (whether it is the actual hardware […]<\/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-1029","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 6 - 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-6\/\" \/>\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 6 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 6 of this series, we start out with Query #11, which is SQL Server NUMA Info. This query retrieves information from the sys.dm_os_nodes dynamic management view about the NUMA node structure that has been created by the SQLOS. By default, this will mimic the hardware processor locality (whether it is the actual hardware […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-6\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-06T15:17:23+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:44+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=\"3 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-6\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-6\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 6 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-06T15:17:23+00:00\",\"dateModified\":\"2017-04-13T19:28:44+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-6\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-6\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-6\/#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 6\"}]},{\"@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 6 - 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-6\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 6 - Glenn Berry","og_description":"For Day 6 of this series, we start out with Query #11, which is SQL Server NUMA Info. This query retrieves information from the sys.dm_os_nodes dynamic management view about the NUMA node structure that has been created by the SQLOS. By default, this will mimic the hardware processor locality (whether it is the actual hardware […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-6\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-06T15:17:23+00:00","article_modified_time":"2017-04-13T19:28:44+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-6\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-6\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 6 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-06T15:17:23+00:00","dateModified":"2017-04-13T19:28:44+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-6\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-6\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-6\/#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 6"}]},{"@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\/1029","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=1029"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1029\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1029"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1029"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1029"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}