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

For Day 26 of this series, we start out with Query #59<\/strong>, which is Table Properties. This query retrieves information from the sys.tables<\/a> object catalog view, and the sys.partitions<\/a> object catalog view about various table properties in the current database. Query #59 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Get some key table properties (Query 59) (Table Properties)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> OBJECT_NAME(t.[object_id]) AS<\/span> [ObjectName], p.[rows<\/span>] AS<\/span> [Table<\/span> Rows<\/span>], p.index_id, <\/pre>\n
<\/p>\n
   3:<\/span>        p.data_compression_desc AS<\/span> [Index<\/span> Data<\/span> Compression],<\/pre>\n
<\/p>\n
   4:<\/span>        t.create_date, t.lock_on_bulk_load, t.is_replicated, t.has_replication_filter, <\/pre>\n
<\/p>\n
   5:<\/span>        t.is_tracked_by_cdc, t.lock_escalation_desc, t.is_memory_optimized, t.durability_desc, t.is_filetable,<\/pre>\n
<\/p>\n
   6:<\/span>        t.temporal_type_desc, t.is_remote_data_archive_enabled, t.remote_data_archive_migration_state_desc, t.is_external -- new for SQL Server 2016<\/span><\/pre>\n
<\/p>\n
   7:<\/span> FROM<\/span> sys.tables AS<\/span> t WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   8:<\/span> INNER<\/span> JOIN<\/span> sys.partitions AS<\/span> p WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   9:<\/span> ON<\/span> t.[object_id] = p.[object_id]<\/pre>\n
<\/p>\n
  10:<\/span> WHERE<\/span> OBJECT_NAME(t.[object_id]) NOT<\/span> LIKE<\/span> N'sys%'<\/span><\/pre>\n
<\/p>\n
  11:<\/span> ORDER<\/span> BY<\/span> OBJECT_NAME(t.[object_id]), p.index_id OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  12:<\/span>  <\/pre>\n
<\/p>\n
  13:<\/span> -- Gives you some good information about your tables<\/span><\/pre>\n
<\/p>\n
  14:<\/span> -- Is Memory optimized and durability description are Hekaton-related properties that were new in SQL Server 2014<\/span><\/pre>\n
<\/p>\n
  15:<\/span> -- temporal_type_desc, is_remote_data_archive_enabled, remote_data_archive_migration_state_desc, is_external are new in SQL Server 2016<\/span><\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #59 Table Properties<\/strong><\/p>\n
This query helps you understand what is going on with properties for the tables in the current database, showing you things such as whether they are being replicated, being tracked by change data capture, whether they are Hekaton tables, whether they are StretchDB<\/a> tables, etc. It also shows you the data compression status for every index in each table. This can help you find possible data compression candidates.<\/p>\n
<\/strong> <\/p>\n
Query #60<\/strong> is Statistics Update. This query retrieves information from the sys.objects<\/a> object catalog view, the sys.indexes<\/a> object catalog view, the sys.stats<\/a> object catalog view, and the sys.dm_db_partition_stats<\/a> dynamic management view about the properties and status of the statistics in the current database. Query #60 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- When were Statistics last updated on all indexes?  (Query 60) (Statistics Update)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> SCHEMA_NAME(o.Schema_ID) + N'.'<\/span> + o.NAME AS<\/span> [Object<\/span> Name], o.type_desc AS<\/span> [Object<\/span> Type],<\/pre>\n
<\/p>\n
   3:<\/span>       i.name AS<\/span> [Index<\/span> Name], STATS_DATE(i.[object_id], i.index_id) AS<\/span> [Statistics<\/span> Date<\/span>], <\/pre>\n
<\/p>\n
   4:<\/span>       s.auto_created, s.no_recompute, s.user_created, s.is_incremental, s.is_temporary,<\/pre>\n
<\/p>\n
   5:<\/span>       st.row_count, st.used_page_count<\/pre>\n
<\/p>\n
   6:<\/span> FROM<\/span> sys.objects AS<\/span> o WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   7:<\/span> INNER<\/span> JOIN<\/span> sys.indexes AS<\/span> i WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   8:<\/span> ON<\/span> o.[object_id] = i.[object_id]<\/pre>\n
<\/p>\n
   9:<\/span> INNER<\/span> JOIN<\/span> sys.stats AS<\/span> s WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  10:<\/span> ON<\/span> i.[object_id] = s.[object_id] <\/pre>\n
<\/p>\n
  11:<\/span> AND<\/span> i.index_id = s.stats_id<\/pre>\n
<\/p>\n
  12:<\/span> INNER<\/span> JOIN<\/span> sys.dm_db_partition_stats AS<\/span> st WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  13:<\/span> ON<\/span> o.[object_id] = st.[object_id]<\/pre>\n
<\/p>\n
  14:<\/span> AND<\/span> i.[index_id] = st.[index_id]<\/pre>\n
<\/p>\n
  15:<\/span> WHERE<\/span> o.[type] IN<\/span> ('U'<\/span>, 'V'<\/span>)<\/pre>\n
<\/p>\n
  16:<\/span> AND<\/span> st.row_count > 0<\/pre>\n
<\/p>\n
  17:<\/span> ORDER<\/span> BY<\/span> STATS_DATE(i.[object_id], i.index_id) DESC<\/span> OPTION<\/span> (RECOMPILE);  <\/pre>\n
<\/p>\n
  18:<\/span>  <\/pre>\n
<\/p>\n
  19:<\/span> -- Helps discover possible problems with out-of-date statistics<\/span><\/pre>\n
<\/p>\n
  20:<\/span> -- Also gives you an idea which indexes are<\/span> the most active<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #60 Statistics Update<\/strong><\/p>\n
This query shows you a number of relevant properties about the index-associated statistics in your current database, ordered by the last time that statistics were updated. This can help you determine whether you might have a problem with out of date statistics. My general guidance about statistics is that you should use the default database properties of auto create and auto update for statistics, plus I think you should also use the auto update statistics asynchronously database property, along with global trace flag 2371<\/a>. In some situations, you may also want\/need to do additional statistics maintenance on highly volatile tables with SQL Server Agent jobs.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 26 of this series, we start out with Query #59, which is Table Properties. This query retrieves information from the sys.tables object catalog view, and the sys.partitions object catalog view about various table properties in the current database. Query #59 is shown in Figure 1. 1: — Get some key table properties (Query […]<\/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":[272],"class_list":["post-1054","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-dmv-diagnostic-queries-detailed"],"yoast_head":"\nSQL Server Diagnostic Information Queries Detailed, Day 26 - 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-26\/\" \/>\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 26 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 26 of this series, we start out with Query #59, which is Table Properties. This query retrieves information from the sys.tables object catalog view, and the sys.partitions object catalog view about various table properties in the current database. Query #59 is shown in Figure 1. 1: -- Get some key table properties (Query […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-26\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-26T17:14:09+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:53+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-26\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-26\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 26 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-26T17:14:09+00:00\",\"dateModified\":\"2017-04-13T19:28:53+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-26\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-26\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-26\/#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 26\"}]},{\"@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 26 - 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-26\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 26 - Glenn Berry","og_description":"For Day 26 of this series, we start out with Query #59, which is Table Properties. This query retrieves information from the sys.tables object catalog view, and the sys.partitions object catalog view about various table properties in the current database. Query #59 is shown in Figure 1. 1: -- Get some key table properties (Query […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-26\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-26T17:14:09+00:00","article_modified_time":"2017-04-13T19:28:53+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-26\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-26\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 26 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-26T17:14:09+00:00","dateModified":"2017-04-13T19:28:53+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-26\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-26\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-26\/#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 26"}]},{"@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\/1054","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=1054"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1054\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1054"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1054"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1054"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}