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.
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)
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
I do not have access to DBA account, is there any other way for me to use these scripts.
DBA account? Did you mean SysAdmin?
Hi Dave
Just wanted to know how to find unused indexes in SQL server 2014
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
That shows the last time the statistics were updated, not the date index was created.
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?
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.
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)
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
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?
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.
if user seeks, user scans, user lookups are all 0 but user updates have a high value, can the index be dropped?
That would be a good idea.
How can i identify that which index i have to drop.?
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
How do you know that you can delete the unused indexes. What determines that an unused index is good to delete?
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.
Hi Pinal Sir ,
I Execute Above Script , In My Database User Seek is high . Should I Drop Index ?
No, those are useful ones.
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
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 ?
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!
Great addition.
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.
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
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
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
So little information to say anything.