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

For Day 28 of this series, we start out with Query #63<\/strong>, which is Overall Index Usage \u2013 Reads. This query retrieves information from the sys.indexes<\/a> object catalog view, and the sys.dm_db_index_usage_stats<\/a> dynamic management view about the overall index usage in the current database, ordered by reads. Query #63 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> --- Index Read\/Write stats (all tables in current DB) ordered by Reads  (Query 63) (Overall Index Usage - Reads)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> OBJECT_NAME(i.[object_id]) AS<\/span> [ObjectName], i.name AS<\/span> [IndexName], i.index_id, <\/pre>\n
<\/p>\n
   3:<\/span>        s.user_seeks, s.user_scans, s.user_lookups,<\/pre>\n
<\/p>\n
   4:<\/span>        s.user_seeks + s.user_scans + s.user_lookups AS<\/span> [Total Reads<\/span>], <\/pre>\n
<\/p>\n
   5:<\/span>        s.user_updates AS<\/span> [Writes],  <\/pre>\n
<\/p>\n
   6:<\/span>        i.type_desc AS<\/span> [Index<\/span> Type], i.fill_factor AS<\/span> [Fill Factor], i.has_filter, i.filter_definition, <\/pre>\n
<\/p>\n
   7:<\/span>        s.last_user_scan, s.last_user_lookup, s.last_user_seek<\/pre>\n
<\/p>\n
   8:<\/span> FROM<\/span> sys.indexes AS<\/span> i WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   9:<\/span> LEFT<\/span> OUTER<\/span> JOIN<\/span> sys.dm_db_index_usage_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.index_id<\/pre>\n
<\/p>\n
  12:<\/span> AND<\/span> s.database_id = DB_ID()<\/pre>\n
<\/p>\n
  13:<\/span> WHERE<\/span> OBJECTPROPERTY(i.[object_id],'IsUserTable'<\/span>) = 1<\/pre>\n
<\/p>\n
  14:<\/span> ORDER<\/span> BY<\/span> s.user_seeks + s.user_scans + s.user_lookups DESC<\/span> OPTION<\/span> (RECOMPILE); -- Order by reads<\/span><\/pre>\n
<\/p>\n
  15:<\/span>  <\/pre>\n
<\/p>\n
  16:<\/span>  <\/pre>\n
<\/p>\n
  17:<\/span> -- Show which indexes in<\/span> the current<\/span> database<\/span> are<\/span> most active for<\/span> Reads<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #63 Overall Index Usage \u2013 Reads<\/strong><\/p>\n
This query shows you which indexes in the current database have the most cumulative reads (including seeks, scans and lookups) since the instance was last restarted or the index was created. This helps you understand your workload, and shows you which indexes are the most valuable for your workload.  Another use for this query is to help identify possible data compression candidates. If you find an index on a large table with a high number of reads and a low number of writes, then it might be a good candidate for data compression if the data is highly compressible.<\/p>\n
 <\/p>\n
Query #64<\/strong> is Overall Index Usage \u2013 Writes. This query retrieves information from the sys.indexes<\/a> object catalog view, and the sys.dm_db_index_usage_stats<\/a> dynamic management view about the overall index usage in the current database, ordered by writes. Query #64 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> --- Index Read\/Write stats (all tables in current DB) ordered by Writes  (Query 64) (Overall Index Usage - Writes)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> OBJECT_NAME(i.[object_id]) AS<\/span> [ObjectName], i.name AS<\/span> [IndexName], i.index_id,<\/pre>\n
<\/p>\n
   3:<\/span>        s.user_updates AS<\/span> [Writes], s.user_seeks + s.user_scans + s.user_lookups AS<\/span> [Total Reads<\/span>], <\/pre>\n
<\/p>\n
   4:<\/span>        i.type_desc AS<\/span> [Index<\/span> Type], i.fill_factor AS<\/span> [Fill Factor], i.has_filter, i.filter_definition,<\/pre>\n
<\/p>\n
   5:<\/span>        s.last_system_update, s.last_user_update<\/pre>\n
<\/p>\n
   6:<\/span> FROM<\/span> sys.indexes AS<\/span> i WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   7:<\/span> LEFT<\/span> OUTER<\/span> JOIN<\/span> sys.dm_db_index_usage_stats AS<\/span> s WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   8:<\/span> ON<\/span> i.[object_id] = s.[object_id]<\/pre>\n
<\/p>\n
   9:<\/span> AND<\/span> i.index_id = s.index_id<\/pre>\n
<\/p>\n
  10:<\/span> AND<\/span> s.database_id = DB_ID()<\/pre>\n
<\/p>\n
  11:<\/span> WHERE<\/span> OBJECTPROPERTY(i.[object_id],'IsUserTable'<\/span>) = 1<\/pre>\n
<\/p>\n
  12:<\/span> ORDER<\/span> BY<\/span> s.user_updates DESC<\/span> OPTION<\/span> (RECOMPILE);                         -- Order by writes<\/span><\/pre>\n
<\/p>\n
  13:<\/span>  <\/pre>\n
<\/p>\n
  14:<\/span> -- Show which indexes in<\/span> the current<\/span> database<\/span> are<\/span> most active for<\/span> Writes<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #64 Overall Index Usage \u2013 Writes<\/strong><\/p>\n
This query shows you which indexes in the current database have the most cumulative writes since the instance was last restarted or the index was created. This helps you understand your workload, and shows you which indexes are the most volatile in your workload.  Another use for this query is to help identify possible indexes that you might consider dropping. If you find a non-clustered, non-key index on a table with a high number of writes and a very low number of reads, then you might want to drop that index, after doing some further investigation. You want to make sure that your instance has been running long enough so that you have seen your complete workload so that you don\u2019t drop an index that is actually needed for queries that have not run yet.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 28 of this series, we start out with Query #63, which is Overall Index Usage \u2013 Reads. This query retrieves information from the sys.indexes object catalog view, and the sys.dm_db_index_usage_stats dynamic management view about the overall index usage in the current database, ordered by reads. Query #63 is shown in Figure 1. 1: […]<\/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-1056","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 28 - 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-28\/\" \/>\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 28 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 28 of this series, we start out with Query #63, which is Overall Index Usage \u2013 Reads. This query retrieves information from the sys.indexes object catalog view, and the sys.dm_db_index_usage_stats dynamic management view about the overall index usage in the current database, ordered by reads. Query #63 is shown in Figure 1. 1: […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-28\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-28T16:12:16+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-28\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-28\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 28 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-28T16:12:16+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-28\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-28\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-28\/#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 28\"}]},{\"@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 28 - 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-28\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 28 - Glenn Berry","og_description":"For Day 28 of this series, we start out with Query #63, which is Overall Index Usage \u2013 Reads. This query retrieves information from the sys.indexes object catalog view, and the sys.dm_db_index_usage_stats dynamic management view about the overall index usage in the current database, ordered by reads. Query #63 is shown in Figure 1. 1: […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-28\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-28T16:12:16+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-28\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-28\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 28 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-28T16:12:16+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-28\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-28\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-28\/#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 28"}]},{"@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\/1056","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=1056"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1056\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1056"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1056"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1056"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}