SQL SERVER – Unused Index Script – Download

Performance Tuning is quite interesting and Index plays a vital role in it. A proper index can improve the performance and a bad index can hamper the performance. Here is the script from my script bank, which I use to identify unused indexes on any database. Let us see script for unused index.

SQL SERVER - Unused Index Script - Download unusedIndex-800x302

Please note, if you should not drop all the unused indexes this script suggest. This is just for guidance. You should not create more than 5-10 indexes per table. Additionally, this script sometime does not give accurate information so use your common sense.

Anyway, the scripts is a good starting point. You should pay attention to User Scan, User Lookup and User Update when you are going to drop the index. The generic understanding is if this value is all high and User Seek is low, the index needs tuning. The index drop script is also provided in the last column.

-- Unused Index Script
-- Original Author: Pinal Dave 
SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.' 
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id 
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO

If you need further help with this script, do leave a comment or send me email at my email address.

Note: It goes without saying, please test all the script on your development server and after they pass your test, deploy them on production server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Download, SQL Index, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Missing Index Script – Download
Next Post
SQL SERVER – Beginning SQL Server: One Step at a Time – SQL Server Magazine

Related Posts

60 Comments. Leave new

  • Added in Schema name ‘.’ and distinct to produce same results but with schema name

    ; with UnUsedTables (TableName , TotalRowCount, CreatedDate , LastModifiedDate )
    AS (
    SELECT distinct (sysschema.name +’.’ + DBTable.name) AS TableName
    ,PS.row_count AS TotalRowCount
    ,DBTable.create_date AS CreatedDate
    ,DBTable.modify_date AS LastModifiedDate
    FROM sys.schemas sysschema
    Join sys.all_objects DBTable on sysschema.schema_id = DBTable.schema_id
    JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name
    WHERE DBTable.type =’U’
    AND NOT EXISTS (SELECT OBJECT_ID
    FROM sys.dm_db_index_usage_stats
    WHERE OBJECT_ID = DBTable.object_id )
    )
    — Select data from the CTE
    SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate
    FROM UnUsedTables
    ORDER BY TotalRowCount ASC

    Reply
  • Faiz Qureshi
    April 4, 2017 8:40 pm

    I do not have access to DBA account, is there any other way for me to use these scripts.

    Reply
  • Hameed Mohammed
    July 27, 2017 10:07 pm

    Hi Dave

    Just wanted to know how to find unused indexes in SQL server 2014

    Reply
  • Antonio Paterno
    February 9, 2018 5:01 pm

    Hi Pinal, it would be useful to add to the script the date of Index creation:
    STATS_DATE(o.object_id,i.index_id) as CreateDate

    Bye

    Reply
    • Russ Thomas ( @SQLJudo )
      May 22, 2018 3:40 am

      That shows the last time the statistics were updated, not the date index was created.

      Reply
  • Hi,

    I’ve just ran this and all the top 25 have 0 userseeks, scans and user lookups – I assume these indexes are just not being hit? It’s a EF created database which does create a lot of indexes.

    Should I look at the “UserUpdates” column as the indication of the number of times the index has been updated and therefore a feel for the cost of updates and inserts?

    Reply
    • If its an third party application’s database then I would be worried about making changes to indexes unless you are sure that it won’t cause any harm.

      Reply
  • Hi Pinal Ji,

    Actually one of my database size is around 25 GB in January ending and it was created 5 years ago, But from Feb starting the size was increasing rapidly and now gone to 36 GB. Can you please suggest me with any article related this issue(Database size rapid growth)

    Reply
  • I havI have used your t-scripts to detect un-used index over 20 production sql server.
    The results returned plus minus 40 indexes on a specific user database in prod environment.
    I’m not clear that about dropping those un-used indexes.
    Questions:
    • Should I drop those un-used indexes?
    • Should I check each table that has more than 5 indexes before dropping the un-used index?
    used your t-script to detech

    Reply
  • Pinal, this is an awesome script and I’m trying to better understand it.

    I am wondering why the Userscans and UserUpdates number is so high if the tablerows value is 0?

    For instance,

    userseek=0, userscans=3074 , userlookups=0, userupdates=278896 , tablerows =0

    My takeaway is that this is a wasted index taking up space and should be dropped. Am I correct?

    Reply
    • 0 table rows indicates that the table is currently empty, but the number of userupdates indicates that it has not always been empty. The non-zero value of userscans indicates that this index HAS been used.

      Reply
  • if user seeks, user scans, user lookups are all 0 but user updates have a high value, can the index be dropped?

    Reply
  • How can i identify that which index i have to drop.?

    Reply
  • Very interesting article, thanks for sharing.

    in my case, I have a table with almost 750 million rows, and this is the result:

    IndexID UserSeek UserScans UserLookups UserUpdates TableRows
    15 1 0 0 1486844 759254315
    14 6 3 0 1486844 759254315
    16 37 3 0 1486844 759254315
    9 349196 3 0 1486844 759254315

    Based on this, is it safe to say that I can delete index 14, 15 and 16?

    Many thanks

    Reply
  • How do you know that you can delete the unused indexes. What determines that an unused index is good to delete?

    Reply
    • If it’s *unused* – why would you want to keep it? It just creates overhead on your table for inserts / updates / deletes and takes up space.

      Reply
  • Hitendra Deriya
    January 31, 2020 4:02 pm

    Hi Pinal Sir ,

    I Execute Above Script , In My Database User Seek is high . Should I Drop Index ?

    Reply
  • Hi Pinal. In looking a little closer at some of my table indexes, on one table I see four non-clustered indexes which are indexed on the exact same columns, the only thing different between then is the included columns. Each index differs slightly by including different columns. Each of these four indexes were added by looking at the activity monitor/active expensive queries while a Power BI query was running and then creating the missing index that it indicated when viewing the execution plan. (Power BI can create some crazy looking SQL!!). My question is, if four non-clustered indexes are all indexed on the same columns and ONE of those indexes has an “includes” that covers all of the columns that the other three indexes combined also include, then would it be a safe bet that removing the first three indexes and only keeping the one index that “includes” all of the required columns should not affect performance? (and in fact improve it slightly?) When a query plan runs that used to use one of the other, now deleted indexes, will it know enough to use the index I kept or will it show a missing index again?

    Thanks, Brad

    Reply
  • Hi Dave, I run your missing index script in MySql Workbench and it returned an error: Error 1064 invalid Sql Syntax.
    I have Server version: 5.7.31-0ubuntu0.18.04.1 (Ubuntu): To which version must I upgrade in order to be able to run your script ?

    Reply
  • Hi Dave, nice script. I have received a similar script from a DBA and I would recommend the following additions for better interpretation:

    , CASE
    WHEN dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups + dm_ius.user_updates = 0 THEN ‘zombie index’
    WHEN dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups = 0 THEN ‘never accessed’
    ELSE ‘in use’
    END as category
    , CASE
    WHEN dm_ius.user_updates < 1 THEN 100
    WHEN dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups = 0 THEN -1 * dm_ius.user_updates
    ELSE CONVERT(DECIMAL(10,4), 1.0 * (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) / dm_ius.user_updates)
    END AS reads_per_write

    With the script also came the following how-to-use, which I find quite helpful:
    The "reads_per_write" field helps to find indexes that aren't helping to improve performance. For every 1 write to the index, you want to see as many reads as possible. Indexes with a reads_per_write score of 1 mean that
    for every 1 write, the index is also used 1 time to help with performance. Ideally, you want to see scores much higher than that. Consider dropping indexes with a reads_per_write score under 1, and strongly consider dropping ones with scores under 0.1.

    Cheers & keep up the good work!

    Reply
  • I’d suggest that your query needs to go from the sys.tables to the index usage DMV as an outer join. The row in the DMV is only created when a read or a write is done to an index. There may well be indexes that haven’t been read or written, e.g. Filtered indexes, unused tables etc.

    Reply
  • i like it too
    in that case, I would also change the ordering: this way you can concentrate on the first rows:

    ORDER BY reads_per_write, category ASC

    Reply
  • Somnath Ray
    May 2, 2022 9:23 am

    Hello,

    This scripts lists all unused indexes but does it consider indexes which are built on primary key and shouldn’t be be dropped for unexpected outcome and also shouldn’t those indexes on which sql server has not worked on for insert, update operation by checking this condition dm_db_index_usage_stats.user_updates 0 be considered

    Reply
  • nurarun271Arun
    May 4, 2022 5:17 pm

    Hi Pinal,

    I am getting below count on four indexes on a table (all 0s). I kept on checking for 2 weeks. I think I should drop these indexes. What you think

    UserSeek UserScans UserLookups
    0 0 0
    0 0 0
    0 0 0
    0 0 0

    Reply

Leave a Reply