{"id":468,"date":"2017-07-11T09:52:39","date_gmt":"2017-07-11T14:52:39","guid":{"rendered":"http:\/\/www.sqlnuggets.com\/?p=468"},"modified":"2020-09-02T14:55:31","modified_gmt":"2020-09-02T19:55:31","slug":"sql-scripts-find-index-usage","status":"publish","type":"post","link":"https:\/\/sqlnuggets.com\/sql-scripts-find-index-usage\/","title":{"rendered":"SQL Scripts: How To Find Index Usage"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-456 size-full\" src=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg\" alt=\"\" width=\"1280\" height=\"640\" srcset=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg 1280w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831-300x150.jpeg 300w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831-768x384.jpeg 768w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831-1024x512.jpeg 1024w\" sizes=\"auto, (max-width: 1280px) 100vw, 1280px\" \/><\/p>\n<p><em>This post is part of an ongoing\u00a0<a href=\"https:\/\/sqlnuggets.com\/blog\/category\/sql-scripts\/\">SQL Scripts<\/a>\u00a0series, in which I list useful queries that I use in my day to day database administration.<\/em><\/p>\n<p><em>If you are interested in code that will <a href=\"https:\/\/sqlnuggets.com\/blog\/persist-and-aggregate-index-stats-across-server-restarts\/\">Persist And Aggregate Index Statistics<\/a>\u00a0for your server, take a look at my\u00a0<a href=\"https:\/\/sqlnuggets.com\/blog\/tag\/sql-metrics-pack\/\" rel=\"noopener\">SQL Server Metrics Pack<\/a>\u00a0project that is available on\u00a0<a href=\"https:\/\/github.com\/ericcobb\/SQL-Server-Metrics-Pack\" target=\"_blank\" rel=\"noopener noreferrer\">GitHub<\/a>.<\/em><\/p>\n<h4>Why Should I Check Index Usage?<\/h4>\n<p>Checking index usage statistics is a great way to find out how often indexes are being used, and identify if you have indexes that are not being used at all. \u00a0Unused indexes are unnecessary overhead, and need to be cleaned up whenever possible. \u00a0By regularly checking your index usage statistics, you can determine which ones are used heavily, and which ones you need to get rid of.<\/p>\n<h4>How Do I Find Usage Information For Indexes?<\/h4>\n<p>SQL Server actually keeps up with how often an index is used. \u00a0It does this behind the scenes, and you can find this information by querying the built in\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-catalog-views\/sys-indexes-transact-sql\" target=\"_blank\" rel=\"noopener noreferrer\">sys.indexes<\/a> and\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-dynamic-management-views\/sys-dm-db-index-usage-stats-transact-sql\" target=\"_blank\" rel=\"noopener noreferrer\">sys.dm_db_index_usage_stats<\/a> DMVs. \u00a0Below is a query that I use regularly to check the usage of indexes in my databases.<\/p>\n<pre class=\"lang:tsql decode:true\">SELECT  DB_NAME() AS DatabaseName\n\t   ,SCHEMA_NAME(s.schema_id) +'.'+OBJECT_NAME(i.OBJECT_ID) AS TableName\n\t   ,i.name AS IndexName\n\t   ,ius.user_seeks AS Seeks\n\t   ,ius.user_scans AS Scans\n\t   ,ius.user_lookups AS Lookups\n\t   ,ius.user_updates AS Updates\n\t   ,CASE WHEN ps.usedpages &gt; ps.pages THEN (ps.usedpages - ps.pages) ELSE 0 \n\t\t  END * 8 \/ 1024 AS IndexSizeMB\n\t   ,ius.last_user_seek AS LastSeek\n\t   ,ius.last_user_scan AS LastScan\n\t   ,ius.last_user_lookup AS LastLookup\n\t   ,ius.last_user_update AS LastUpdate\nFROM sys.indexes i\nINNER JOIN sys.dm_db_index_usage_stats ius ON ius.index_id = i.index_id AND ius.OBJECT_ID = i.OBJECT_ID\nINNER JOIN (SELECT sch.name, sch.schema_id, o.OBJECT_ID, o.create_date FROM sys.schemas sch \n\t\t\t INNER JOIN sys.objects o ON o.schema_id = sch.schema_id) s ON s.OBJECT_ID = i.OBJECT_ID\nLEFT JOIN (SELECT OBJECT_ID, index_id, SUM(used_page_count) AS usedpages,\n\t\t\t\t    SUM(CASE WHEN (index_id &lt; 2) \n\t\t\t\t\t\t  THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) \n\t\t\t\t\t\t  ELSE lob_used_page_count + row_overflow_used_page_count \n\t\t\t\t\t   END) AS pages\n\t\t\t\tFROM sys.dm_db_partition_stats\n\t\t\t\tGROUP BY object_id, index_id) AS ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id\nWHERE OBJECTPROPERTY(i.OBJECT_ID,'IsUserTable') = 1\n--optional parameters\nAND ius.database_id = DB_ID() --only check indexes in current database\nAND i.type_desc = 'nonclustered' --only check nonclustered indexes\nAND i.is_primary_key = 0 --do not check primary keys\nAND i.is_unique_constraint = 0 --do not check unique constraints\n--AND (ius.user_seeks+ius.user_scans+ius.user_lookups) &lt; 1  --only return unused indexes\n--AND OBJECT_NAME(i.OBJECT_ID) = 'tableName'--only check indexes on specified table\n--AND i.name = 'IX_Your_Index_Name' --only check a specified index\n order by i.name<\/pre>\n<p>You will note that in the WHERE clause I have specified several optional parameters, some of which are commented out. \u00a0These parameters will allow you to tailor this script to your needs, checking for as few or as many indexes as you want.<\/p>\n<h4>What Should I Look For In These Index Usage Statistics?<\/h4>\n<p>First, it&#8217;s important to note that the usage statistics you see are only aggregated since the last SQL Server restart. \u00a0<em>Every time SQL Server is restarted, these statistics are cleared out and reset to zero!<\/em> \u00a0If you want a way to persist index statistics across server restarts see <a href=\"https:\/\/sqlnuggets.com\/blog\/persist-and-aggregate-index-stats-across-server-restarts\/\">this post<\/a>.<\/p>\n<p>By looking at the <em>seeks, scans,<\/em> and <em>lookups<\/em> returned by the above query, we can easily see how often an index is used. \u00a0If all of these are zero, it means than the specified index has not had any <em>seeks, scans<\/em>, or <em>lookups<\/em> since the last SQL Server restart (or, since the index was created, if it is a recent addition). \u00a0The <em>lastseek, lastscan<\/em>, and <em>lastlookup<\/em> columns give you the date and time of when that specific operation occurred, or will be NULL if that operation has not occurred yet.<\/p>\n<p>The <em>updates<\/em> column will show you how many data modifications have occurred in the specified index. \u00a0Remember, every time you INSERT, UPDATE, or DELETE data in a table, all of the associated indexes on that table must have their data modified as well. \u00a0(ex. inserting a record into a table also inserts a record into all matching indexes on that table). \u00a0This is why it is so important to get rid of any unused indexes, because SQL Server has to maintain the data in them regardless of whether they are used or not. \u00a0Indexes with a large number of <em>updates<\/em>, but no <em>seeks, scans,<\/em> and <em>lookups<\/em>\u00a0are good candidates for removal.<\/p>\n<p>Another good use for these index usage statistics is to see how well SQL Server likes any new indexes that you deploy. \u00a0Every time I add an index, I keep check on these stats for the first 30 days or so, to determine if it is getting used like I want it to. \u00a0Sometimes you know almost immediately (Wow! \u00a01 million seeks in an hour!), or in other cases (It has been a month, why isn&#8217;t SQL Server using this index???)<\/p>\n<p>And this is just the start! \u00a0There are several more ways you can use this information. \u00a0For a much more detailed tutorial, I encourage you to check out Red Gate&#8217;s\u00a0<a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/performance\/tune-your-indexing-strategy-with-sql-server-dmvs\/\" target=\"_blank\" rel=\"noopener noreferrer\">Tune Your Indexing Strategy with SQL Server DMVs<\/a>\u00a0article.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This post is part of an ongoing\u00a0SQL Scripts\u00a0series, in which I list useful queries that I use in my day to day database administration. If you are interested in code &#8230;<\/p>\n","protected":false},"author":2,"featured_media":456,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[24,52,18],"tags":[10,39,27],"class_list":["post-468","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-indexes","category-sql-scripts","category-system-views","tag-database-maintenance","tag-internals","tag-nonclustered-indexes"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL Scripts: How To Find Index Usage - SQL Nuggets<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Scripts: How To Find Index Usage - SQL Nuggets\" \/>\n<meta property=\"og:description\" content=\"This post is part of an ongoing\u00a0SQL Scripts\u00a0series, in which I list useful queries that I use in my day to day database administration. If you are interested in code ...\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Nuggets\" \/>\n<meta property=\"article:published_time\" content=\"2017-07-11T14:52:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-09-02T19:55:31+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg\" \/>\n\t<meta property=\"og:image:width\" content=\"1280\" \/>\n\t<meta property=\"og:image:height\" content=\"640\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Eric Cobb\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@cfgears\" \/>\n<meta name=\"twitter:site\" content=\"@sqlnugg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Eric Cobb\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-find-index-usage\\\/#article\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-find-index-usage\\\/\"},\"author\":{\"name\":\"Eric Cobb\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\"},\"headline\":\"SQL Scripts: How To Find Index Usage\",\"datePublished\":\"2017-07-11T14:52:39+00:00\",\"dateModified\":\"2020-09-02T19:55:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-find-index-usage\\\/\"},\"wordCount\":623,\"commentCount\":2,\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-find-index-usage\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg\",\"keywords\":[\"Database Maintenance\",\"Internals\",\"Nonclustered Indexes\"],\"articleSection\":[\"Indexes\",\"SQL Scripts\",\"System Views\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-find-index-usage\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-find-index-usage\\\/\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-find-index-usage\\\/\",\"name\":\"SQL Scripts: How To Find Index Usage - SQL Nuggets\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-find-index-usage\\\/#primaryimage\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-find-index-usage\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg\",\"datePublished\":\"2017-07-11T14:52:39+00:00\",\"dateModified\":\"2020-09-02T19:55:31+00:00\",\"breadcrumb\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-find-index-usage\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-find-index-usage\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-find-index-usage\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg\",\"width\":1280,\"height\":640},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-find-index-usage\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Scripts: How To Find Index Usage\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"name\":\"SQL Nuggets\",\"description\":\"Nuggets Of SQL Server Knowledge\",\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\\\/\\\/www.sqlnuggets.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\",\"name\":\"SQL Nuggets\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"width\":320,\"height\":54,\"caption\":\"SQL Nuggets\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/sqlnugg\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/ericcobb\\\/\"]},{\"@type\":\"Person\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\",\"name\":\"Eric Cobb\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"caption\":\"Eric Cobb\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/cfgears\"],\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/author\\\/eric-cobb\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Scripts: How To Find Index Usage - SQL Nuggets","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":"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/","og_locale":"en_US","og_type":"article","og_title":"SQL Scripts: How To Find Index Usage - SQL Nuggets","og_description":"This post is part of an ongoing\u00a0SQL Scripts\u00a0series, in which I list useful queries that I use in my day to day database administration. If you are interested in code ...","og_url":"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/","og_site_name":"SQL Nuggets","article_published_time":"2017-07-11T14:52:39+00:00","article_modified_time":"2020-09-02T19:55:31+00:00","og_image":[{"width":1280,"height":640,"url":"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg","type":"image\/jpeg"}],"author":"Eric Cobb","twitter_card":"summary_large_image","twitter_creator":"@cfgears","twitter_site":"@sqlnugg","twitter_misc":{"Written by":"Eric Cobb","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/#article","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/"},"author":{"name":"Eric Cobb","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a"},"headline":"SQL Scripts: How To Find Index Usage","datePublished":"2017-07-11T14:52:39+00:00","dateModified":"2020-09-02T19:55:31+00:00","mainEntityOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/"},"wordCount":623,"commentCount":2,"publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg","keywords":["Database Maintenance","Internals","Nonclustered Indexes"],"articleSection":["Indexes","SQL Scripts","System Views"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/","url":"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/","name":"SQL Scripts: How To Find Index Usage - SQL Nuggets","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/#website"},"primaryImageOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/#primaryimage"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg","datePublished":"2017-07-11T14:52:39+00:00","dateModified":"2020-09-02T19:55:31+00:00","breadcrumb":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/#primaryimage","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg","width":1280,"height":640},{"@type":"BreadcrumbList","@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-find-index-usage\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.sqlnuggets.com\/"},{"@type":"ListItem","position":2,"name":"SQL Scripts: How To Find Index Usage"}]},{"@type":"WebSite","@id":"http:\/\/www.sqlnuggets.com\/#website","url":"http:\/\/www.sqlnuggets.com\/","name":"SQL Nuggets","description":"Nuggets Of SQL Server Knowledge","publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.sqlnuggets.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"http:\/\/www.sqlnuggets.com\/#organization","name":"SQL Nuggets","url":"http:\/\/www.sqlnuggets.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","width":320,"height":54,"caption":"SQL Nuggets"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/sqlnugg","https:\/\/www.linkedin.com\/in\/ericcobb\/"]},{"@type":"Person","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a","name":"Eric Cobb","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","url":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","caption":"Eric Cobb"},"sameAs":["https:\/\/x.com\/cfgears"],"url":"https:\/\/sqlnuggets.com\/author\/eric-cobb\/"}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg","jetpack_shortlink":"https:\/\/wp.me\/pdyDvE-7y","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":790,"url":"https:\/\/sqlnuggets.com\/sql-scripts-how-to-find-filtered-indexes\/","url_meta":{"origin":468,"position":0},"title":"SQL Scripts: How To Find Filtered Indexes","author":"Eric Cobb","date":"May 9, 2018","format":false,"excerpt":"Filtered indexes are one of those neat little SQL Server features that a lot of people don't seem to know about, or if they do they don't use them very much.\u00a0 While I agree that putting a filter on every index probably isn't a good idea, there are cases where\u2026","rel":"","context":"In &quot;Indexes&quot;","block_context":{"text":"Indexes","link":"https:\/\/sqlnuggets.com\/category\/indexes\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":1113,"url":"https:\/\/sqlnuggets.com\/recover-deleted-indexes-with-sql-server-metrics-pack\/","url_meta":{"origin":468,"position":1},"title":"Recover Deleted Indexes With SQL Server Metrics Pack","author":"Eric Cobb","date":"October 22, 2019","format":false,"excerpt":"In my latest enhancement to SQL Server Metrics Pack , I added a new feature to the index metrics scripts that automatically generates a CREATE INDEX script and stores it in the database along with the metrics for each index. (read more about that here) I'm really excited about this\u2026","rel":"","context":"In &quot;Indexes&quot;","block_context":{"text":"Indexes","link":"https:\/\/sqlnuggets.com\/category\/indexes\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg?resize=1400%2C800&ssl=1 4x"},"classes":[]},{"id":813,"url":"https:\/\/sqlnuggets.com\/sql-scripts-how-to-find-missing-indexes\/","url_meta":{"origin":468,"position":2},"title":"SQL Scripts: How To Find Missing Indexes","author":"Eric Cobb","date":"June 5, 2018","format":false,"excerpt":"One of the really cool things that SQL Server does is keep up with index statistics behind the scenes.\u00a0 It knows how an index has been used, how often it is used, or if it is even being used at all.\u00a0 But SQL Server doesn't just monitor the indexes that\u2026","rel":"","context":"In &quot;Indexes&quot;","block_context":{"text":"Indexes","link":"https:\/\/sqlnuggets.com\/category\/indexes\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/06\/lost-cat-tree-sign-fun-159868.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/06\/lost-cat-tree-sign-fun-159868.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/06\/lost-cat-tree-sign-fun-159868.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/06\/lost-cat-tree-sign-fun-159868.jpeg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/06\/lost-cat-tree-sign-fun-159868.jpeg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":449,"url":"https:\/\/sqlnuggets.com\/sql-scripts-find-index-fragmentation\/","url_meta":{"origin":468,"position":3},"title":"SQL Scripts: How To Find Index Fragmentation","author":"Eric Cobb","date":"June 22, 2017","format":false,"excerpt":"This is the first post in\u00a0an ongoing SQL Scripts series, in which I list useful queries that I use in my day to day database administration. What Is Index Fragmentation? Over time, as records are inserted, updated, and deleted, your tables and indexes become fragmented. \u00a0This fragmentation can lead to\u2026","rel":"","context":"In &quot;Indexes&quot;","block_context":{"text":"Indexes","link":"https:\/\/sqlnuggets.com\/category\/indexes\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/06\/board-electronics-computer-data-processing-50711-e1498160567831.jpeg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":1107,"url":"https:\/\/sqlnuggets.com\/sql-server-metrics-pack-update-september-2019\/","url_meta":{"origin":468,"position":4},"title":"SQL Server Metrics Pack Update &#8211; September 2019","author":"Eric Cobb","date":"September 20, 2019","format":false,"excerpt":"This morning I updated the Index Metrics piece of SQL Server Metrics Pack. This update mainly introduced some new functionality, with a few minor bug fixes. The two big enhancements are the capability to generate and store CREATE INDEX scripts and support for Columnstore indexes. Creating index scripts seems pretty\u2026","rel":"","context":"In &quot;Indexes&quot;","block_context":{"text":"Indexes","link":"https:\/\/sqlnuggets.com\/category\/indexes\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/binary-797263_1280-e1502824772680.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":1078,"url":"https:\/\/sqlnuggets.com\/sql-scripts-find-queries-that-have-missing-index-requests\/","url_meta":{"origin":468,"position":5},"title":"SQL Scripts: Find Queries That Have Missing Index Requests","author":"Eric Cobb","date":"August 27, 2019","format":false,"excerpt":"In a previous article, I went over how SQL Server will recommend an index that it believes will help a query run faster, and I showed how to find missing index recommendations. Now, as I said in that article, you do not want to just blindly add whatever index SQL\u2026","rel":"","context":"In &quot;SQL Scripts&quot;","block_context":{"text":"SQL Scripts","link":"https:\/\/sqlnuggets.com\/category\/sql-scripts\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2019\/08\/question-mark-1872634_1280.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2019\/08\/question-mark-1872634_1280.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2019\/08\/question-mark-1872634_1280.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2019\/08\/question-mark-1872634_1280.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2019\/08\/question-mark-1872634_1280.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/468","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/comments?post=468"}],"version-history":[{"count":1,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/468\/revisions"}],"predecessor-version":[{"id":1471,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/468\/revisions\/1471"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media\/456"}],"wp:attachment":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media?parent=468"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/categories?post=468"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/tags?post=468"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}