July 5, 2012 at 2:09 pm
Hey Guys,
I've been digging around in one of our higher-transaction databases on the largest table and noticed some interesting index stats. Indexes 1-6 have 0 total reads and I am contemplating deleting them which would free up about 29 GB. Indexes 7-11 all have some reads - just not nearly as many as writes. I guess what I want to ask is if it is safe to remove at least indexes 1-6 since they are basically wasting space? Will there be any performance improvements from doing this and are they worth removing? And what about indexes 7-11? Appreciate any input.
Index Stats:
IndexNameindex_idTotalWritesTotalReadsDifference
index1201605983306059833
index23571970805719708
index396562253005622530
index4110562252905622529
index520562252905622529
index6214289802602898026
index711758771148055876309
index889562252916385620891
index9168562252917575620772
index10216287010617822868324
index1194562864918515626798
I'm using the below query to give me the index usage stats:
-- Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY [Difference] DESC , [Total Writes] DESC, [Total Reads] ASC ;
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
July 5, 2012 at 2:28 pm
Is it a third-party app database or an in-house app that you have reach-back to the devs? I ask because just because I would think that just because you do not see read activity from when the DMV was able to collect stats (since SQL startup) that does not necessarily mean that the indexes are not used for jobs that are run less frequently. I would say do a little bit more investigation before killing them off.
If you are on SQL 2008 (which is the assumption based on the forum you posted in) you might want to try disabling the indexes to determine performance impact first.
Joie Andrew
"Since 1982"
July 6, 2012 at 6:16 am
Before removing any indexes from a production system, it is a wise idea to try removing them from a QA environment and running full-load tests to see if it in anyway alters performance. It's better to find out in a non-production environment if something is going to get screwed up.
July 6, 2012 at 7:22 am
Joie Andrew (7/5/2012)
Is it a third-party app database or an in-house app that you have reach-back to the devs?
I'm going to get with the "owner" of the database and see what he has to say.
I ask because just because I would think that just because you do not see read activity from when the DMV was able to collect stats (since SQL startup) that does not necessarily mean that the indexes are not used for jobs that are run less frequently. I would say do a little bit more investigation before killing them off.
Our initial thoughts were they might be used by some of our end-of-the-month jobs. This database was transferred over to a 2008 server from a 2005 server at the beginning of June so we only had the DMV stats from then. However, even after our monthly jobs ran the indexes still had zero or very few reads.
If you are on SQL 2008 (which is the assumption based on the forum you posted in) you might want to try disabling the indexes to determine performance impact first.
Might suggest this to the DB owner and view performance throughout the day compared to some of our benchmarks.
Appreciate all of the feedback.
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
July 9, 2012 at 8:10 am
Determining the performance impact of removing these unused indexes is important; understanding the impact of leaving them there is also important. In the past 14 years I cannot recall a time where removing an unused nonclustered index would ever cause a performance problem. Indexes make read queries (SELECT FROM) faster but INSERT/UPDATE/DELETE statements run slower because every time you update a table with index(es), you also have to update that/those index(es). Ideally you want as few indexes possible on tables which are mostly written to.
Looking at index1 of your index stats: the totalwrites (6,059,833) represents a negetive performance impact on the associated INSERT/UPDATE/DELETE queries. The TotalReads (0) represents the number of times this index was used to make a SELECT query faster.
First - it is important to determine how often these indexes are used. You are seeing 0 reads but is that for what time period? Dynamic Management Views (DMV's) collect index usuage beginning when SQL starts. To get a longer time period it is worth collecting index usage for a long period of time. If you are using a 3rd party monitoring tool you may already have this information available. Otherwise you could create a SQL job that simply runs the query you posted and writes the resultset to a table with a timestamp.
A few other questions to ask include: what kind of indexes are these - clustered or nonclustered? If they are clustered do they exist because of a primary key? Is the table replicated? These are some of the questions I need answered before removing unused indexes.
If these are clustered indexes then understand the impact of removing it on any nonclustered indexes on that table. E.g. If you drop a clustered index on a table with nonclustered indexes, all the nonclustered indexes are rebuilt to replace the clustered index keys with row pointers. This can be time consuming depending on the size, quantity and content of the nonclustered indexes. Removing a clustered index from an indexed view automatically removes all nonclustered indexes.
Sometimes you will have unused indexes that cannot be removed. For example: I recently setup transactional replication. In transactional (and Merge) replication you must have a primary key on a published table. The published source tables in my transactional replication are written to by an application then replicated to datawarehouses. Though the clustered indexes associated with my PK's on the source table are never used they associated with a PK and cannot be removed.
I have seen cases in the past where developers add a number of indexes on a table while doing performance tuning & testing. The indexes are garbage and would never be used in our query execution plans. Nonetheless, they are not removed because of sloppy developer work and go into Production.
Lastly: documentation is always key! In the BI world - we say, "If you can't measure it, you can't manage it." Documentation helps us understand why it exists in the first place.
-- Itzik Ben-Gan 2001
July 9, 2012 at 8:52 am
XMLSQLNinja (7/9/2012)
Determining the performance impact of removing these unused indexes is important; understanding the impact of leaving them there is also important. In the past 14 years I cannot recall a time where removing an unused nonclustered index would ever cause a performance problem. Indexes make read queries (SELECT FROM) faster but INSERT/UPDATE/DELETE statements run slower because every time you update a table with index(es), you also have to update that/those index(es). Ideally you want as few indexes possible on tables which are mostly written to.Looking at index1 of your index stats: the totalwrites (6,059,833) represents a negetive performance impact on the associated INSERT/UPDATE/DELETE queries. The TotalReads (0) represents the number of times this index was used to make a SELECT query faster.
First - it is important to determine how often these indexes are used. You are seeing 0 reads but is that for what time period? Dynamic Management Views (DMV's) collect index usuage beginning when SQL starts. To get a longer time period it is worth collecting index usage for a long period of time. If you are using a 3rd party monitoring tool you may already have this information available. Otherwise you could create a SQL job that simply runs the query you posted and writes the resultset to a table with a timestamp.
A few other questions to ask include: what kind of indexes are these - clustered or nonclustered? If they are clustered do they exist because of a primary key? Is the table replicated? These are some of the questions I need answered before removing unused indexes.
The stats I posted above are from about a month and a half time period - over this time they received no usage. Indexes 1-6 are all Non-unique, non-clustered indexes and I am probably going to drop or at least disable them later today.
Will removing the indexes cause any performance hits on the server/database during the actual removal? Since they are non-clustered shouldn't they drop pretty quick? It's about a 700GB database and these indexes are all located on a 360GB table with almost 900 million rows. I can wait until after peak-hours to remove them if it will cause a performance hit.
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
July 9, 2012 at 9:23 am
Are any of the indexes on foreign key columns?
If they are, you can run into issues when you try to delete a row from a referenced table, because it will have to scan the referencing table to make sure it would not create any orphan rows. Doesn't sound like you are doing that much, but it is something to be aware of.
July 9, 2012 at 10:37 am
Based on what you've said I cannot see any use for them but I would I would disable them first for a little while to be on the safe side (provided you can still spare the space for the time being.)
There should not be any performance hits and dropping nonclustered indexes doesn't take any time.
-- Itzik Ben-Gan 2001
July 9, 2012 at 10:45 am
I'd concur with Alan on the disabling them. Easier to put them back if you have issues.
July 9, 2012 at 12:38 pm
Steve Jones - SSC Editor (7/9/2012)
I'd concur with Alan on the disabling them. Easier to put them back if you have issues.
Would it be better to simply right click -> disable the indexes or script out the creation script for each, totally drop each index, and then have the DDL to recreate the indexes in case we need to revert?
I know we can drop the indexes and not have any performance issues but will disabling the indexes potentially cause issues?
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
July 10, 2012 at 7:52 am
I've never heard of disabled indexes causing any problems.
July 10, 2012 at 8:04 am
No issues. Disables is much like dropping, just leaves the metadata behind
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2012 at 8:40 am
Disabling an index is fine, provided it is not clustered index.
Disabling a clustered index prevents user access to the underlying table data.
July 10, 2012 at 8:43 am
With a couple very rare exceptions (not relevant for this conversation) I have never had any issues with disabling indexes.
-- Itzik Ben-Gan 2001
July 10, 2012 at 8:46 am
Thanks for all of the input everybody. Disabled the indexes and haven't seen any issues.
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply