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

For Day 8 of this series, we start out with Query #16<\/strong>, which is Hardware Info.  This query retrieves some hardware-related information from the sys.dm_os_sys_info<\/a> dynamic management view. Query #16 is shown in Figure 1. <\/p>\n

\n
\n
   1:<\/span> -- Hardware information from SQL Server 2016  (Query 16) (Hardware Info)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> cpu_count AS<\/span> [Logical CPU Count<\/span>], scheduler_count, hyperthread_ratio AS<\/span> [Hyperthread Ratio],<\/pre>\n
<\/p>\n
   3:<\/span> cpu_count\/hyperthread_ratio AS<\/span> [Physical CPU Count<\/span>], <\/pre>\n
<\/p>\n
   4:<\/span> physical_memory_kb\/1024 AS<\/span> [Physical Memory (MB)], committed_kb\/1024 AS<\/span> [Committed Memory (MB)],<\/pre>\n
<\/p>\n
   5:<\/span> committed_target_kb\/1024 AS<\/span> [Committed Target Memory (MB)],<\/pre>\n
<\/p>\n
   6:<\/span> max_workers_count AS<\/span> [Max<\/span> Workers Count<\/span>], affinity_type_desc AS<\/span> [Affinity Type], <\/pre>\n
<\/p>\n
   7:<\/span> sqlserver_start_time AS<\/span> [SQL<\/span> Server Start<\/span> Time<\/span>], virtual_machine_type_desc AS<\/span> [Virtual Machine Type], <\/pre>\n
<\/p>\n
   8:<\/span> softnuma_configuration_desc AS<\/span> [Soft NUMA Configuration]<\/pre>\n
<\/p>\n
   9:<\/span> FROM<\/span> sys.dm_os_sys_info WITH<\/span> (NOLOCK) OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  10:<\/span>  <\/pre>\n
<\/p>\n
  11:<\/span> -- Gives you some good basic hardware information about your database server<\/span><\/pre>\n
<\/p>\n
  12:<\/span> -- Cannot distinguish between HT and multi-core<\/span><\/pre>\n
<\/p>\n
  13:<\/span> -- Note: virtual_machine_type_desc of HYPERVISOR does not automatically mean you are running SQL Server inside of a VM<\/span><\/pre>\n
<\/p>\n
  14:<\/span> -- It merely indicates that you have a hypervisor running on your host<\/span><\/pre>\n
<\/p>\n
  15:<\/span> -- Soft NUMA configuration is<\/span> a new<\/span> column<\/span> for<\/span> SQL<\/span> Server 2016<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #16 Hardware Info<\/strong><\/p>\n
This query tells you some very useful information about your hardware, even though it is frustratingly incomplete. You can determine your physical socket count, and the total number of logical processors, but you cannot tell what type of processor you have or whether Intel Hyper-Threading is enabled or not. You can also determine how much RAM is visible to the machine you are running on (physical or virtual).<\/p>\n
You can also tell whether a hypervisor is present on the host you are running on, and whether someone has manually modified CPU process affinity. New in SQL Server 2016, you can see whether soft NUMA has been enabled. Finally, you can see the last time the SQL Server Service was started, which is very important to know when you are interpreting the results of many of the other queries in this set. It would be very nice if the results from the next two queries were also included in this DMV.<\/p>\n
 <\/p>\n
Query #17<\/strong> is System Manufacturer.  This query tries to read the SQL Server Error log to determine the manufacturer and model number of the server that you are running on. If you are running inside of a hypervisor virtual machine, you will see that instead of the actual host hardware. Query #17 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Get System Manufacturer and model number from SQL Server Error log (Query 17) (System Manufacturer)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> EXEC<\/span> sys.xp_readerrorlog 0, 1, N'Manufacturer'<\/span>; <\/pre>\n
<\/p>\n
   3:<\/span>  <\/pre>\n
<\/p>\n
   4:<\/span> -- This can help you determine the capabilities and capacities of your database server<\/span><\/pre>\n
<\/p>\n
   5:<\/span> -- Can also be used to confirm if you are running in a VM<\/span><\/pre>\n
<\/p>\n
   6:<\/span> -- This query might take a few seconds if you have not recycled your error log recently<\/span><\/pre>\n
<\/p>\n
   7:<\/span> -- This query will return<\/span> no<\/span> results if<\/span> your error log has been recycled since the instance was started<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #17 System Manufacturer<\/strong><\/p>\n
Knowing the brand and model of the server you are running on helps you figure out other useful information, such as how many PCIe expansion slots it has, how much memory it supports, and what type of processors it supports. It also helps you figure out how old it might be. This query will return no result if the SQL Server Error log has been recycled enough times to rollover since it was started. I really hate having to pull information like this from the SQL Server Error log, it would be much better to get it from a DMV.<\/p>\n
 <\/p>\n
Query #18<\/strong> is Processor Description.  This query retrieves the processor description information from the Windows Registry. Query #18 is shown in Figure 3.<\/p>\n
\n
\n
   1:<\/span> -- Get processor description from Windows Registry  (Query 18) (Processor Description)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> EXEC<\/span> sys.xp_instance_regread N'HKEY_LOCAL_MACHINE'<\/span>, N'HARDWARE\\DESCRIPTION\\System\\CentralProcessor\\0'<\/span>, N'ProcessorNameString'<\/span>;<\/pre>\n
<\/p>\n
   3:<\/span>  <\/pre>\n
<\/p>\n
   4:<\/span> -- Gives you the model number and rated clock speed of your processor(s)<\/span><\/pre>\n
<\/p>\n
   5:<\/span> -- Your processors may be running at less than the rated clock speed due<\/span><\/pre>\n
<\/p>\n
   6:<\/span> -- to the Windows Power Plan or hardware power management<\/span><\/pre>\n
<\/p>\n
   7:<\/span>  <\/pre>\n
<\/p>\n
   8:<\/span> -- You can use CPU-Z to get your actual CPU core speed and a lot of other useful information<\/span><\/pre>\n
<\/p>\n
   9:<\/span> -- http:\/\/www.cpuid.com\/softwares\/cpu-z.html<\/span><\/pre>\n
<\/p>\n
  10:<\/span>  <\/pre>\n
<\/p>\n
  11:<\/span> -- You can learn more about processor selection for SQL Server by following this link<\/span><\/pre>\n
<\/p>\n
  12:<\/span> -- https:\/\/www.sqlskills.com\/blogs\/glenn\/processor-selection-for<\/span>-sql<\/span>-server\/<\/pre>\n
<\/div>\n<\/div>\n
Figure 3: Query #18 Processor Description<\/strong><\/p>\n
I think it is critical to know exactly what processor(s) you are running on your SQL Server instances. This helps you understand the capabilities of the system and how old it might be. It is also very important to know this if you are thinking about doing a server upgrade or consolidation effort. Even though the gory details of processors are probably not that interesting to many database professionals, it is actually pretty important for licensing purposes and for performance and scalability.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 8 of this series, we start out with Query #16, which is Hardware Info.  This query retrieves some hardware-related information from the sys.dm_os_sys_info dynamic management view. Query #16 is shown in Figure 1. 1: — Hardware information from SQL Server 2016 (Query 16) (Hardware Info) 2: SELECT cpu_count AS [Logical CPU Count], scheduler_count, […]<\/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-1031","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 8 - 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-8\/\" \/>\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 8 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 8 of this series, we start out with Query #16, which is Hardware Info.  This query retrieves some hardware-related information from the sys.dm_os_sys_info dynamic management view. Query #16 is shown in Figure 1. 1: -- Hardware information from SQL Server 2016 (Query 16) (Hardware Info) 2: SELECT cpu_count AS [Logical CPU Count], scheduler_count, […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-8\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-08T17:56:22+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=\"4 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-8\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-8\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 8 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-08T17:56:22+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-8\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-8\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-8\/#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 8\"}]},{\"@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 8 - 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-8\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 8 - Glenn Berry","og_description":"For Day 8 of this series, we start out with Query #16, which is Hardware Info.  This query retrieves some hardware-related information from the sys.dm_os_sys_info dynamic management view. Query #16 is shown in Figure 1. 1: -- Hardware information from SQL Server 2016 (Query 16) (Hardware Info) 2: SELECT cpu_count AS [Logical CPU Count], scheduler_count, […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-8\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-08T17:56:22+00:00","article_modified_time":"2017-04-13T19:28:44+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-8\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-8\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 8 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-08T17:56:22+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-8\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-8\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-8\/#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 8"}]},{"@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\/1031","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=1031"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1031\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1031"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1031"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1031"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}