{"id":1025,"date":"2016-01-02T11:11:29","date_gmt":"2016-01-02T19:11:29","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1025"},"modified":"2017-04-13T12:28:43","modified_gmt":"2017-04-13T19:28:43","slug":"sql-server-diagnostic-information-queries-detailed-day-2","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-2\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 2"},"content":{"rendered":"

Query #3<\/strong> is Server Properties. This query simply makes multiple calls to the T-SQL SERVERPROPERTY<\/a> metadata function to retrieve quite a bit of useful information about the current instance of SQL Server that you are connected to. Query #3 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Get selected server properties (Query 3) (Server Properties)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> SERVERPROPERTY('MachineName'<\/span>) AS<\/span> [MachineName], <\/pre>\n
<\/p>\n
   3:<\/span> SERVERPROPERTY('ServerName'<\/span>) AS<\/span> [ServerName],  <\/pre>\n
<\/p>\n
   4:<\/span> SERVERPROPERTY('InstanceName'<\/span>) AS<\/span> [Instance], <\/pre>\n
<\/p>\n
   5:<\/span> SERVERPROPERTY('IsClustered'<\/span>) AS<\/span> [IsClustered], <\/pre>\n
<\/p>\n
   6:<\/span> SERVERPROPERTY('ComputerNamePhysicalNetBIOS'<\/span>) AS<\/span> [ComputerNamePhysicalNetBIOS], <\/pre>\n
<\/p>\n
   7:<\/span> SERVERPROPERTY('Edition'<\/span>) AS<\/span> [Edition], <\/pre>\n
<\/p>\n
   8:<\/span> SERVERPROPERTY('ProductLevel'<\/span>) AS<\/span> [ProductLevel],                -- What servicing branch (RTM\/SP\/CU)<\/span><\/pre>\n
<\/p>\n
   9:<\/span> SERVERPROPERTY('ProductUpdateLevel'<\/span>) AS<\/span> [ProductUpdateLevel],    -- Within a servicing branch, what CU# is applied<\/span><\/pre>\n
<\/p>\n
  10:<\/span> SERVERPROPERTY('ProductVersion'<\/span>) AS<\/span> [ProductVersion],<\/pre>\n
<\/p>\n
  11:<\/span> SERVERPROPERTY('ProductMajorVersion'<\/span>) AS<\/span> [ProductMajorVersion], <\/pre>\n
<\/p>\n
  12:<\/span> SERVERPROPERTY('ProductMinorVersion'<\/span>) AS<\/span> [ProductMinorVersion], <\/pre>\n
<\/p>\n
  13:<\/span> SERVERPROPERTY('ProductBuild'<\/span>) AS<\/span> [ProductBuild], <\/pre>\n
<\/p>\n
  14:<\/span> SERVERPROPERTY('ProductBuildType'<\/span>) AS<\/span> [ProductBuildType],              -- Is this a GDR or OD hotfix (NULL if on a CU build)<\/span><\/pre>\n
<\/p>\n
  15:<\/span> SERVERPROPERTY('ProductUpdateReference'<\/span>) AS<\/span> [ProductUpdateReference], -- KB article number that is applicable for this build<\/span><\/pre>\n
<\/p>\n
  16:<\/span> SERVERPROPERTY('ProcessID'<\/span>) AS<\/span> [ProcessID],<\/pre>\n
<\/p>\n
  17:<\/span> SERVERPROPERTY('Collation'<\/span>) AS<\/span> [Collation<\/span>], <\/pre>\n
<\/p>\n
  18:<\/span> SERVERPROPERTY('IsFullTextInstalled'<\/span>) AS<\/span> [IsFullTextInstalled], <\/pre>\n
<\/p>\n
  19:<\/span> SERVERPROPERTY('IsIntegratedSecurityOnly'<\/span>) AS<\/span> [IsIntegratedSecurityOnly],<\/pre>\n
<\/p>\n
  20:<\/span> SERVERPROPERTY('FilestreamConfiguredLevel'<\/span>) AS<\/span> [FilestreamConfiguredLevel],<\/pre>\n
<\/p>\n
  21:<\/span> SERVERPROPERTY('IsHadrEnabled'<\/span>) AS<\/span> [IsHadrEnabled], <\/pre>\n
<\/p>\n
  22:<\/span> SERVERPROPERTY('HadrManagerStatus'<\/span>) AS<\/span> [HadrManagerStatus],<\/pre>\n
<\/p>\n
  23:<\/span> SERVERPROPERTY('IsXTPSupported'<\/span>) AS<\/span> [IsXTPSupported],<\/pre>\n
<\/p>\n
  24:<\/span> SERVERPROPERTY('IsPolybaseInstalled'<\/span>) AS<\/span> [IsPolybaseInstalled],    -- New for SQL Server 2016<\/span><\/pre>\n
<\/p>\n
  25:<\/span> SERVERPROPERTY('InstanceDefaultDataPath'<\/span>) AS<\/span> [InstanceDefaultDataPath],<\/pre>\n
<\/p>\n
  26:<\/span> SERVERPROPERTY('InstanceDefaultLogPath'<\/span>) AS<\/span> [InstanceDefaultLogPath],<\/pre>\n
<\/p>\n
  27:<\/span> SERVERPROPERTY('BuildClrVersion'<\/span>) AS<\/span> [Build CLR Version];<\/pre>\n
<\/p>\n
  28:<\/span>  <\/pre>\n
<\/p>\n
  29:<\/span> -- This gives you a lot of useful information about your instance of SQL Server,<\/span><\/pre>\n
<\/p>\n
  30:<\/span> -- such as the ProcessID for SQL Server and your collation<\/span><\/pre>\n
<\/p>\n
  31:<\/span> -- Note: Some<\/span> columns will be NULL<\/span> on<\/span> older SQL<\/span> Server builds<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #3 Server Properties<\/strong><\/p>\n
This metadata function was recently enhanced for SQL Server 2012 and SQL Server 2014 by adding quite a bit of information about the product level, product update level, build numbers, etc. Aaron Bertrand went into much more detail about this change here<\/a>. One challenge with this T-SQL metadata function is that many, but not all of the possible valid values for input values are documented in Books Online (BOL). One example is SERVERPROPERTY(‘InstanceDefaultDataPath’) and SERVERPROPERTY(‘InstanceDefaultLogPath’), which I just recently discovered.<\/p>\n
Query #4<\/strong> is Configuration Values. This query retrieves information from the sys.configurations catalog view. It will return a different number of rows depending on which version of SQL Server you are using. For example, SQL Server 2014 SP1 CU4 returns 70 rows, while SQL Server 2016 CTP 3.2 returns 76 rows. Query #4 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Get instance-level configuration values for instance  (Query 4) (Configuration Values)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> name, value<\/span>, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced<\/pre>\n
<\/p>\n
   3:<\/span> FROM<\/span> sys.configurations WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   4:<\/span> ORDER<\/span> BY<\/span> name OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   5:<\/span>  <\/pre>\n
<\/p>\n
   6:<\/span> -- Focus on these settings:<\/span><\/pre>\n
<\/p>\n
   7:<\/span> -- automatic soft-NUMA disabled (should be 0 in most cases)<\/span><\/pre>\n
<\/p>\n
   8:<\/span> -- backup checksum default (should be 1)<\/span><\/pre>\n
<\/p>\n
   9:<\/span> -- backup compression default (should be 1 in most cases)<\/span><\/pre>\n
<\/p>\n
  10:<\/span> -- clr enabled (only enable if it is needed)<\/span><\/pre>\n
<\/p>\n
  11:<\/span> -- cost threshold for parallelism (depends on your workload)<\/span><\/pre>\n
<\/p>\n
  12:<\/span> -- lightweight pooling (should be zero)<\/span><\/pre>\n
<\/p>\n
  13:<\/span> -- max degree of parallelism (depends on your workload and hardware)<\/span><\/pre>\n
<\/p>\n
  14:<\/span> -- max server memory (MB) (set to an appropriate value, not the default)<\/span><\/pre>\n
<\/p>\n
  15:<\/span> -- optimize for ad hoc workloads (should be 1)<\/span><\/pre>\n
<\/p>\n
  16:<\/span> -- priority boost (should be zero)<\/span><\/pre>\n
<\/p>\n
  17:<\/span> -- remote admin connections (should be 1)<\/span><\/pre>\n
<\/p>\n
  18:<\/span>  <\/pre>\n
<\/p>\n
  19:<\/span> -- New options for SQL Server 2016<\/span><\/pre>\n
<\/p>\n
  20:<\/span> -- hadoop connectivity<\/span><\/pre>\n
<\/p>\n
  21:<\/span> -- polybase network encryption<\/span><\/pre>\n
<\/p>\n
  22:<\/span> -- remote data<\/span> archive (to<\/span> enable Stretch Databases)<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #4  Configuration Values<\/strong><\/p>\n
Even though there are many rows that are returned from this view, I usually focus on just a small number of them, as detailed in the comments below the query. If you find any of these instance-level configuration values<\/a> that you would like to change<\/a>, you have two ways of doing it (at least for many of the values). One way is to use the SQL Server Management Studio (SSMS) GUI to change the value, and then either click Ok or script out the change (which is always a much better idea). The other way is to use the sp_configure system stored procedure<\/a>.<\/p>\n
What you want to set these values to depends on your workload and a number of other factors. One cautionary piece of verbiage I recently spotted in BOL<\/a> was this:<\/p>\n
\n
\u201cAdvanced options should be changed only by an experienced database administrator or certified SQL Server technician.\u201d<\/p>\n<\/blockquote>\n
I would agree that you should probably not change these configuration values unless you know what you are doing, but I am not sure what they really mean about a certified SQL Server technician\u2026<\/p>\n","protected":false},"excerpt":{"rendered":"
Query #3 is Server Properties. This query simply makes multiple calls to the T-SQL SERVERPROPERTY metadata function to retrieve quite a bit of useful information about the current instance of SQL Server that you are connected to. Query #3 is shown in Figure 1. 1: — Get selected server properties (Query 3) (Server Properties) 2: […]<\/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-1025","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 2 - 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-2\/\" \/>\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 2 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"Query #3 is Server Properties. This query simply makes multiple calls to the T-SQL SERVERPROPERTY metadata function to retrieve quite a bit of useful information about the current instance of SQL Server that you are connected to. Query #3 is shown in Figure 1. 1: -- Get selected server properties (Query 3) (Server Properties) 2: […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-02T19:11:29+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:43+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-2\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-2\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 2 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-02T19:11:29+00:00\",\"dateModified\":\"2017-04-13T19:28:43+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-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-2\/#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 2\"}]},{\"@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 2 - 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-2\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 2 - Glenn Berry","og_description":"Query #3 is Server Properties. This query simply makes multiple calls to the T-SQL SERVERPROPERTY metadata function to retrieve quite a bit of useful information about the current instance of SQL Server that you are connected to. Query #3 is shown in Figure 1. 1: -- Get selected server properties (Query 3) (Server Properties) 2: […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-2\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-02T19:11:29+00:00","article_modified_time":"2017-04-13T19:28:43+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-2\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-2\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 2 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-02T19:11:29+00:00","dateModified":"2017-04-13T19:28:43+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-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-2\/#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 2"}]},{"@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\/1025","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=1025"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1025\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1025"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1025"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1025"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}