{"id":11684,"date":"2019-04-21T20:45:55","date_gmt":"2019-04-21T20:45:55","guid":{"rendered":"https:\/\/dbtut.com\/?p=11684"},"modified":"2019-04-21T20:47:06","modified_gmt":"2019-04-21T20:47:06","slug":"how-to-find-missing-indexes-in-sql-server","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/","title":{"rendered":"How To Find Missing Indexes in SQL Server"},"content":{"rendered":"

In the database, especially during the new development phase, indexes are the most important performance providers for us. You should be familiar with the index concept before detecting missing indexes. The article “Index Concept and Performance Effect on SQL Server<\/a>” will help you.<\/p>\n

The following query will be a start for you to detect missing indexes in your database. But running all the index create scripts returned in the query will not be the right solution. The result set is sorted by Impact. So you should start reviewing from the first record.<\/p>\n

SELECT\nmid.statement AS [Query Text]\n  ,migs.avg_total_user_cost * (migs.avg_user_impact \/ 100.0) * (migs.user_seeks + migs.user_scans) AS [Impact],\n  'CREATE INDEX [IX_'+LEFT (PARSENAME(mid.statement, 1), 32)+'_'\n  +ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(mid.equality_columns, '[',''),',','_'),']',''),' ',''),'')+\n  +ISNULL('_'+REPLACE(REPLACE(REPLACE(REPLACE(mid.inequality_columns, '[',''),',','_'),']',''),' ',''),'')+']'\n  + ' ON ' + mid.statement\n + ' (' + ISNULL (mid.equality_columns,'')\n    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END\n    + ISNULL (mid.inequality_columns, '')\n  + ')'\n  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS [Index Create Script]\nFROM sys.dm_db_missing_index_groups mig\nINNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle\nINNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle\nWHERE migs.avg_total_user_cost * (migs.avg_user_impact \/ 100.0) * (migs.user_seeks + migs.user_scans) > 10 and mid.database_id=DB_ID()\nORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC\n<\/pre>\n
\"\"<\/p>\n
When I looked at the first index create script in my environment, it suggested an index that has 17 included columns for a table that has 20 column. Of course I didn’t create this index. Because creating this index means almost the same thing with recreating table. In the article I mentioned above, I gave detailed information about which index we should create and which index we should not create.<\/p>\n
If you have performance problem in your SQL Server, you may want to read the following articles.<\/p>\n
“How To Find Most Expensive Queries in SQL Server<\/a>”<\/p>\n
“How To Identify Long Running Queries Using Extended Events<\/a>“,<\/p>\n
“How To Identify Long Running Queries Using SQL Server Profiler<\/a>“,<\/p>\n
“Running SQL Server With High Performance(High Performance)<\/a>“,<\/p>\n
“Index Concept and Performance Effect on SQL Server<\/a>“,<\/p>\n
“Statistics Concept and Performance Effect on SQL Server<\/a>”<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
In the database, especially during the new development phase, indexes are the most important performance providers for us. You should be familiar with the index concept before detecting missing indexes. The article “Index Concept and Performance Effect on SQL Server” will help you. The following query will be a start for you to detect missing …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":11688,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[3515,3517,3511,3514,3510,3516,3513,3512],"class_list":["post-11684","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-mssql","tag-find-missing-indexes-sql-server","tag-how-to-create-missing-indexes-in-sql-server","tag-identifying-missing-indexes-in-sql-server","tag-missing-index-on-columns-in-sql-server","tag-missing-index-with-create-scripts","tag-missing-indexes-query-in-sql-server","tag-sql-missing-index-impact","tag-tracking-missing-indexes-in-sql-server"],"aioseo_notices":[],"yoast_head":"\nHow To Find Missing Indexes in SQL Server - Database Tutorials<\/title>\n<meta name=\"description\" content=\"How To Find Missing Index in SQL Server\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Find Missing Indexes in SQL Server - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"How To Find Missing Index in SQL Server\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-21T20:45:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-04-21T20:47:06+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-39.png\" \/>\n\t<meta property=\"og:image:width\" content=\"578\" \/>\n\t<meta property=\"og:image:height\" content=\"338\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"How To Find Missing Indexes in SQL Server\",\"datePublished\":\"2019-04-21T20:45:55+00:00\",\"dateModified\":\"2019-04-21T20:47:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/\"},\"wordCount\":240,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-39.png\",\"keywords\":[\"find missing indexes sql server\",\"how to create missing indexes in sql server\",\"Identifying Missing Indexes in SQL Server\",\"missing index on columns in sql server\",\"missing index with create scripts\",\"missing indexes query in sql server\",\"sql missing index impact\",\"tracking missing indexes in sql server\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/\",\"name\":\"How To Find Missing Indexes in SQL Server - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-39.png\",\"datePublished\":\"2019-04-21T20:45:55+00:00\",\"dateModified\":\"2019-04-21T20:47:06+00:00\",\"description\":\"How To Find Missing Index in SQL Server\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-39.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-39.png\",\"width\":578,\"height\":338},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Find Missing Indexes in SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Find Missing Indexes in SQL Server - Database Tutorials","description":"How To Find Missing Index in SQL Server","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:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"How To Find Missing Indexes in SQL Server - Database Tutorials","og_description":"How To Find Missing Index in SQL Server","og_url":"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/","og_site_name":"Database Tutorials","article_published_time":"2019-04-21T20:45:55+00:00","article_modified_time":"2019-04-21T20:47:06+00:00","og_image":[{"width":578,"height":338,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-39.png","type":"image\/png"}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"How To Find Missing Indexes in SQL Server","datePublished":"2019-04-21T20:45:55+00:00","dateModified":"2019-04-21T20:47:06+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/"},"wordCount":240,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-39.png","keywords":["find missing indexes sql server","how to create missing indexes in sql server","Identifying Missing Indexes in SQL Server","missing index on columns in sql server","missing index with create scripts","missing indexes query in sql server","sql missing index impact","tracking missing indexes in sql server"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/","url":"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/","name":"How To Find Missing Indexes in SQL Server - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-39.png","datePublished":"2019-04-21T20:45:55+00:00","dateModified":"2019-04-21T20:47:06+00:00","description":"How To Find Missing Index in SQL Server","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-39.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-39.png","width":578,"height":338},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/21\/how-to-find-missing-indexes-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"How To Find Missing Indexes in SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/11684","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=11684"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/11684\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/11688"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=11684"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=11684"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=11684"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}