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

For Day 24 of this series, we start out with Query #54<\/strong>, which is Bad NC Indexes. This query retrieves information from the sys.dm_db_index_usage_stats<\/a> dynamic management view and the sys.indexes object catalog view about non-clustered indexes that have more writes than reads in the current database. Query #54 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Possible Bad NC Indexes (writes > reads)  (Query 54) (Bad NC Indexes)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> OBJECT_NAME(s.[object_id]) AS<\/span> [Table<\/span> Name], i.name AS<\/span> [Index<\/span> Name], i.index_id, <\/pre>\n
<\/p>\n
   3:<\/span> i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,<\/pre>\n
<\/p>\n
   4:<\/span> user_updates AS<\/span> [Total Writes], user_seeks + user_scans + user_lookups AS<\/span> [Total Reads<\/span>],<\/pre>\n
<\/p>\n
   5:<\/span> user_updates - (user_seeks + user_scans + user_lookups) AS<\/span> [Difference]<\/pre>\n
<\/p>\n
   6:<\/span> FROM<\/span> sys.dm_db_index_usage_stats AS<\/span> s 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> s.[object_id] = i.[object_id]<\/pre>\n
<\/p>\n
   9:<\/span> AND<\/span> i.index_id = s.index_id<\/pre>\n
<\/p>\n
  10:<\/span> WHERE<\/span> OBJECTPROPERTY(s.[object_id],'IsUserTable'<\/span>) = 1<\/pre>\n
<\/p>\n
  11:<\/span> AND<\/span> s.database_id = DB_ID()<\/pre>\n
<\/p>\n
  12:<\/span> AND<\/span> user_updates > (user_seeks + user_scans + user_lookups)<\/pre>\n
<\/p>\n
  13:<\/span> AND<\/span> i.index_id > 1<\/pre>\n
<\/p>\n
  14:<\/span> ORDER<\/span> BY<\/span> [Difference] DESC<\/span>, [Total Writes] DESC<\/span>, [Total Reads<\/span>] ASC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  15:<\/span>  <\/pre>\n
<\/p>\n
  16:<\/span> -- Look for indexes with high numbers of writes and zero or very low numbers of reads<\/span><\/pre>\n
<\/p>\n
  17:<\/span> -- Consider your complete workload, and how long your instance has been running<\/span><\/pre>\n
<\/p>\n
  18:<\/span> -- Investigate further before<\/span> dropping an index<\/span>!<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #54 Bad NC Indexes<\/strong><\/p>\n
What you are looking for with this query are indexes that have high numbers of writes and very few or even zero reads. If you are paying the cost to maintain an index as the data changes in your table, but the index is never used for reads, then you are placing unneeded stress on your storage subsystem that is not providing any benefits to the system. Having unused indexes also makes your database larger, and makes index maintenance more time consuming and resource intensive.<\/p>\n
One key point to keep in mind before you start dropping indexes that appear to be unused is how long your SQL Server instance has been running. Before you drop an index, consider whether you have seen your complete normal business cycle. Perhaps there are monthly reports that actually do use an index that normally does not see any read activity with your regular workload.<\/p>\n
<\/strong> <\/p>\n
<\/strong> <\/p>\n
Query #55<\/strong> is Missing Indexes. This query retrieves information from the sys.dm_db_missing_index_group_stats<\/a> dynamic management view, the sys.dm_db_missing_index_groups<\/a> dynamic management view, the sys.dm_db_missing_index_details<\/a> dynamic management view and the sys.partitions<\/a> catalog view about \u201cmissing\u201d indexes that the SQL Server Query Optimizer thinks that it would like to have in the current database. Query #55 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Missing Indexes for current database by Index Advantage  (Query 55) (Missing Indexes)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> DISTINCT<\/span> CONVERT<\/span>(decimal<\/span>(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS<\/span> [index_advantage], <\/pre>\n
<\/p>\n
   3:<\/span> migs.last_user_seek, mid.[statement<\/span>] AS<\/span> [Database<\/span>.Schema<\/span>.Table<\/span>],<\/pre>\n
<\/p>\n
   4:<\/span> mid.equality_columns, mid.inequality_columns, mid.included_columns,<\/pre>\n
<\/p>\n
   5:<\/span> migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact,<\/pre>\n
<\/p>\n
   6:<\/span> OBJECT_NAME(mid.[object_id]) AS<\/span> [Table<\/span> Name], p.rows<\/span> AS<\/span> [Table<\/span> Rows<\/span>]<\/pre>\n
<\/p>\n
   7:<\/span> FROM<\/span> sys.dm_db_missing_index_group_stats AS<\/span> migs WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   8:<\/span> INNER<\/span> JOIN<\/span> sys.dm_db_missing_index_groups AS<\/span> mig WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   9:<\/span> ON<\/span> migs.group_handle = mig.index_group_handle<\/pre>\n
<\/p>\n
  10:<\/span> INNER<\/span> JOIN<\/span> sys.dm_db_missing_index_details AS<\/span> mid WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  11:<\/span> ON<\/span> mig.index_handle = mid.index_handle<\/pre>\n
<\/p>\n
  12:<\/span> INNER<\/span> JOIN<\/span> sys.partitions AS<\/span> p WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  13:<\/span> ON<\/span> p.[object_id] = mid.[object_id]<\/pre>\n
<\/p>\n
  14:<\/span> WHERE<\/span> mid.database_id = DB_ID() <\/pre>\n
<\/p>\n
  15:<\/span> ORDER<\/span> BY<\/span> index_advantage DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  16:<\/span>  <\/pre>\n
<\/p>\n
  17:<\/span> -- Look at index advantage, last user seek time, number of user seeks to help determine source and importance<\/span><\/pre>\n
<\/p>\n
  18:<\/span> -- SQL Server is overly eager to add included columns, so beware<\/span><\/pre>\n
<\/p>\n
  19:<\/span> -- Do not<\/span> just blindly add<\/span> indexes that show up from<\/span> this query!!!<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #55 Missing Indexes<\/strong><\/p>\n
This query is very useful, but also very easy to misinterpret and misuse. I have seen many novice DBAs and developers use the results of this query to pretty badly over-index their databases, which affects their database size and hurts insert, update, and delete performance. I like to focus on the last_user_seek column, and see how long ago that was. Was it a few seconds or minutes ago, or was it days or weeks ago? <\/p>\n
I then start looking at the user_seeks, avg_total_user_cost, and avg_user_impact columns to get a sense for how often SQL Server thinks it wants this proposed index, how expensive it is not to have the index, and how much the query optimizer thinks the cost of the query would be reduced if it did have this index that it is requesting. <\/p>\n
Next, I\u2019ll look at any other proposed indexes on the same table to see if I can come up with a wider, consolidated index that covers multiple requested indexes. Finally, I\u2019ll look at the existing indexes on that table, and look at the index usage metrics for that table to have a better idea of whether a new index would be a good idea, based on the volatility of that table. <\/p>\n
This query is very similar to Query #28, but this one is only for the current database. It also pulls back the number of rows in a table, which is useful information when you are considering creating a new index, especially when you are using SQL Server Standard Edition, which does not have online index operations. <\/p>\n
  <\/p>\n
Query #56<\/strong> is Missing Index Warnings. This query retrieves information from the sys.dm_exec_cached_plans<\/a> dynamic management view, the sys.dm_exec_query_plan<\/a> dynamic management function about missing index warnings in the plan cache for the current database. Query #56 is shown in Figure 3.<\/p>\n
\n
\n
   1:<\/span> -- Find missing index warnings for cached plans in the current database  (Query 56) (Missing Index Warnings)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> -- Note: This query could take some time on a busy instance<\/span><\/pre>\n
<\/p>\n
   3:<\/span> SELECT<\/span> TOP<\/span>(25) OBJECT_NAME(objectid) AS<\/span> [ObjectName], <\/pre>\n
<\/p>\n
   4:<\/span>                query_plan, cp.objtype, cp.usecounts, cp.size_in_bytes<\/pre>\n
<\/p>\n
   5:<\/span> FROM<\/span> sys.dm_exec_cached_plans AS<\/span> cp WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   6:<\/span> CROSS<\/span> APPLY sys.dm_exec_query_plan(cp.plan_handle) AS<\/span> qp<\/pre>\n
<\/p>\n
   7:<\/span> WHERE<\/span> CAST<\/span>(query_plan AS<\/span> NVARCHAR(MAX<\/span>)) LIKE<\/span> N'%MissingIndex%'<\/span><\/pre>\n
<\/p>\n
   8:<\/span> AND<\/span> dbid = DB_ID()<\/pre>\n
<\/p>\n
   9:<\/span> ORDER<\/span> BY<\/span> cp.usecounts DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  10:<\/span>  <\/pre>\n
<\/p>\n
  11:<\/span> -- Helps you connect missing indexes to specific stored procedures or queries<\/span><\/pre>\n
<\/p>\n
  12:<\/span> -- This can help you decide whether to<\/span> add<\/span> them or<\/span> not<\/pre>\n
<\/div>\n<\/div>\n
Figure 3: Query #56 Missing Index Warnings<\/strong><\/p>\n
This query (which can be time consuming on a busy instance with a large plan cache) shows you where you have cached query plans with missing index warnings. This is very helpful, since it can often help you tie requested \u201cmissing\u201d indexes to a particular stored procedure or prepared query plan.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 24 of this series, we start out with Query #54, which is Bad NC Indexes. This query retrieves information from the sys.dm_db_index_usage_stats dynamic management view and the sys.indexes object catalog view about non-clustered indexes that have more writes than reads in the current database. Query #54 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-1052","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 24 - 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-24\/\" \/>\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 24 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 24 of this series, we start out with Query #54, which is Bad NC Indexes. This query retrieves information from the sys.dm_db_index_usage_stats dynamic management view and the sys.indexes object catalog view about non-clustered indexes that have more writes than reads in the current database. Query #54 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-24\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-24T19:29:50+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:52+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=\"6 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-24\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-24\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 24 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-24T19:29:50+00:00\",\"dateModified\":\"2017-04-13T19:28:52+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-24\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-24\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-24\/#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 24\"}]},{\"@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 24 - 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-24\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 24 - Glenn Berry","og_description":"For Day 24 of this series, we start out with Query #54, which is Bad NC Indexes. This query retrieves information from the sys.dm_db_index_usage_stats dynamic management view and the sys.indexes object catalog view about non-clustered indexes that have more writes than reads in the current database. Query #54 is shown in Figure 1. 1: -- […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-24\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-24T19:29:50+00:00","article_modified_time":"2017-04-13T19:28:52+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-24\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-24\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 24 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-24T19:29:50+00:00","dateModified":"2017-04-13T19:28:52+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-24\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-24\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-24\/#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 24"}]},{"@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\/1052","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=1052"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1052\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1052"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1052"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1052"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}