Drop Unused indexes?

Question

Drop Unused indexes?

Pyman

SSChampion

Points: 13830
More actions
March 23, 2015 at 2:25 pm

#296183

Hello,
I am using sql 2012 SE. I noticed there were lots of blocking on a subscriber database where an insert is blocking a bunch of selects and causing timeouts. I started looking at unused indexes based of pinal dave's unused index located at http://blog.sqlauthority.com/2011/01/04/sql-server-2008-unused-index-script-download/
Attached is how the user seeks, scans, lookups and updates look for a few indexes.
Although the user seeks, user scans and userlookups are all 0 the user updates is definitely a lot. I am thinking to speed up updates on subscriber these indexes are being used.So I am not comfortable dropping these as they are being used for updates. However I would like to know the inputs of experts if it is a good idea of dropping these indexes?
Please share your thoughts on this. Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Although the user seeks, user scans and userlookups are all 0 the user updates is definitely a lot. I am thinking to speed up updates on subscriber these indexes are being used.So I am not comfortable dropping these as they are being used for updates. However I would like to know the inputs of experts if it is a good idea of dropping these indexes?

Based on what you have posted, these indexes are not helping you in any way. Indexes speed up reads and slow down modifications. In other words an index, when used, will speed up a SELECT query but slow down an INSERT, UPDATE or DELETE query. Since, according to the query you are running (and I have not looked at the article from where you got the query) these indexes are not being seeked or scanned - they are not speeding anything up and only slowing things down.

Based on what you posted, these indexes should likely be dropped. I say likely because I know very little about your data.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Dropping unused indexes is a GREAT idea - but with a bunch of caveats, provisos, limitations, gotchas, etc. A short list includes:

1) do not drop indexes that ensure something such as uniqueness.

2) make sure your server has been up long enough to cover all 'important' period for you (day, week, month, quarter, etc). index usage is restarted each time the server is started.

3) you MUST scan ALL source code for each index name you drop to ensure you don't break existing code that has index hints!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Pyman SSChampion Points: 13830 More actions · Answer 3

Alan.B (3/23/2015)
Although the user seeks, user scans and userlookups are all 0 the user updates is definitely a lot. I am thinking to speed up updates on subscriber these indexes are being used.So I am not comfortable dropping these as they are being used for updates. However I would like to know the inputs of experts if it is a good idea of dropping these indexes?
Based on what you have posted, these indexes are not helping you in any way. Indexes speed up reads and slow down modifications. In other words an index, when used, will speed up a SELECT query but slow down an INSERT, UPDATE or DELETE query. Since, according to the query you are running (and I have not looked at the article from where you got the query) these indexes are not being seeked or scanned - they are not speeding anything up and only slowing things down.
Based on what you posted, these indexes should likely be dropped. I say likely because I know very little about your data.

Please take a look @ http://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/

It clearly states:

'If you see indexes where there are no seeks, scans or lookups, but there are updates this means that SQL Server has not used the index to satisfy a query but still needs to maintain the index. '

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Sapen (3/24/2015)
Alan.B (3/23/2015)
Although the user seeks, user scans and userlookups are all 0 the user updates is definitely a lot. I am thinking to speed up updates on subscriber these indexes are being used.So I am not comfortable dropping these as they are being used for updates. However I would like to know the inputs of experts if it is a good idea of dropping these indexes?
Based on what you have posted, these indexes are not helping you in any way. Indexes speed up reads and slow down modifications. In other words an index, when used, will speed up a SELECT query but slow down an INSERT, UPDATE or DELETE query. Since, according to the query you are running (and I have not looked at the article from where you got the query) these indexes are not being seeked or scanned - they are not speeding anything up and only slowing things down.
Based on what you posted, these indexes should likely be dropped. I say likely because I know very little about your data.
Please take a look @ http://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/
It clearly states:
'If you see indexes where there are no seeks, scans or lookups, but there are updates this means that SQL Server has not used the index to satisfy a query but still needs to maintain the index. '

Exactly! That's what I explained and that sentence reinforces my point. Let's review (emph. mine)...

'If you see indexes where there are no seeks, scans or lookups, but there are updates this means that SQL Server has not used the index to satisfy a [SELECT] query but still needs to maintain the index. '

. What that means is that the index is not helping speed up any SELECT queries but is slowing down your INSERT/UPDATE/DELETE (and perhaps MERGE) queries. Why? Because SQL Server still needs to maintain the index; they are saying that the index must still be modified when when you change the data. This requires overhead but yields no benefit.

Again, for this reason the unused Indexes should likely be dropped. As TheSQLGuru mentioned, there are several Caveats (which is why I emphasized the word likely). Another caveat that I can think of is if you have transactional or merge replication - each requires a primary key but the associated clustered index may never get used. Removing the clustered index in that case will be a bad thing. My suggestion is to run that query regularly and understand your index usage over time. Understand where your indexes came from and why they are there. If they are not speeding up your SELECT statments and don't need to be there then they should be removed.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

MadAdmin SSChampion Points: 11261 More actions · Answer 5

19 indexes? Epic!

In addition to TheSqlGuru's great advice,

before you drop indexes, take note that there are some heroes who do silly things like drop and create indexes in SSIS packages for the bulk loads to speed up bulk inserts, and these might break if you drop the indexes.

I would recommend disabling indexes first as they would still pass an existence check, and then see if they are still disabled after the month end processing has occurred.

Find sql text which contain the index name.

Drop from staging first ( where they are not enforcing constraints).

Next point, find the hero who created 19 heroic indexes and have a chat with them, else you will be cleaning up his nappies whenever he makes doodoo in future.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

... and since this is such an important topic I thought I'd do quick test to demonstrate.

For the first test we'll INSERT 500,000 rows into a table without an index, then into same table after adding a clustered index. Results are included in the comments below the DML

USE tempdb

GO

-- let's create the sample table with two columns

IF OBJECT_ID ('tempdb..#sampledata') IS NOT NULL DROP TABLE #sampledata;

CREATE TABLE #sampledata (sd_id int identity not null, sd_txt varchar(900) not null);

GO

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'INSERT 1M rows WITHOUT clustered index:';

INSERT #sampledata (sd_txt)

SELECT TOP 500000 cast(newid() AS varchar(36))+cast(newid() AS varchar(36))+cast(newid() AS varchar(36))

FROM sys.all_columns a CROSS JOIN sys.all_columns b CROSS JOIN sys.all_columns c;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

-- THIS WILL CREATE A CLUSTERED INDEX

ALTER TABLE #sampledata ADD CONSTRAINT pk_sd PRIMARY KEY (sd_id);

GO

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'INSERT 1M rows WITH a clustered index:';

INSERT #sampledata (sd_txt)

SELECT TOP 500000 cast(newid() AS varchar(36))+cast(newid() AS varchar(36))+cast(newid() AS varchar(36))

FROM sys.all_columns a CROSS JOIN sys.all_columns b CROSS JOIN sys.all_columns c;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

/*

1M rows without a clustered index:

Table 'syscolpars'. Scan count 1, logical reads 409, physical reads 0, read-ahead reads 224, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 3, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 750 ms, elapsed time = 773 ms.

1M rows with a clustered index:

Table '#sampledata_________________________________________________________________________________________________________000000000006'.

Scan count 0, logical reads 1632921, physical reads 0, read-ahead reads 6857, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 1, logical reads 409, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 3, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 2125 ms, elapsed time = 2757 ms.

*/

See that? Adding 500K records to the table with the clustered index added 1.63 million reads and the insert took three times as long. But that's okay because you want to have a primary key& clustered index; that's generally considered a best practice.

Now lets do a select statement with an ORDER BY

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'SELECT TOP 100 against Clustered Index:'

SELECT TOP 100 sd_txt

FROM #sampledata

ORDER BY sd_txt;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

/*

SELECT TOP 100 against Clustered Index:

Table '#sampledata_________________________________________________________________________________________________________000000000006'.

Scan count 5, logical reads 15672, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 561 ms, elapsed time = 181 ms.

*/;

Query Plan:

Here the query optimizer had to do a huge TOP N sort and required a parallel query plan (e.g. all my CPUs are kicking) and required 15K+ reads and finished in 181ms. We could speed this query up by adding a non-clustered index on the sd_txt column.

Before looking at how a non-clustered index could speed this query up, let's look at the downside of adding a new index... For our next test we're going to do an update before and after adding the new index.

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'UPDATE all rows Clustered Index only:'

UPDATE #sampledata

SET sd_txt = replace(sd_txt,'-','&&')

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

CREATE NONCLUSTERED INDEX nc_xxx ON #sampledata(sd_txt);

GO

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'UPDATE all rows Clustered Index and Nonclustered index:'

UPDATE #sampledata

SET sd_txt = replace(sd_txt,'&&','-');

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

/*

UPDATE all rows Clustered Index only:

Table '#sampledata_________________________________________________________________________________________________________000000000002'.

Scan count 1, logical reads 172003, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 5031 ms, elapsed time = 6593 ms.

UPDATE all rows Clustered Index and Nonclustered index:

Table '#sampledata_________________________________________________________________________________________________________000000000002'.

Scan count 1, logical reads 8531314, physical reads 0, read-ahead reads 26, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 22375 ms, elapsed time = 32180 ms.

*/

Before we added the second index the update required 172K reads and ran for 5 seconds. After we added the index, a similar update required 8.5M reads and ran for 22 seconds. See how adding an index dramatically slowed down that update?

And now let's re-run that SELECT query with the Nonclustered index in place:

SET STATISTICS TIME ON;

SET STATISTICS IO ON;

PRINT 'SELECT TOP 100 against nonclustered Index:'

SELECT TOP 100 sd_txt

FROM #sampledata

ORDER BY sd_txt;

SET STATISTICS TIME OFF;

SET STATISTICS IO OFF;

/*

SELECT TOP 100 against nonclustered Index:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Table '#sampledata_________________________________________________________________________________________________________000000000002'.

Scan count 1, logical reads 24, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 91 ms.

*/

Query Plan:

See that, the nonclustered index reduced the reads from 15K to 24 reads and returned the results in 1/2 the time without a parallel query plan (e.g. one CPU was twice as fast as all 8 CPUs used in the original query).

So, what have we learned? Indexes slow down data modification (e.g. INSERTS and UPDATES) but speed up SELECT queries when (and only when) they are used. This is a key consideration when deciding if you want to keep that index or not.

Edit: fixed bad grammar.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

TheSQLGuru (3/23/2015)
...
3) you MUST scan ALL source code for each index name you drop to ensure you don't break existing code that has index hints!

I have to remember this one the next time I'm arguing that data access should be done using stored procedures. It's a lot easier for a DBA to search stored procedures than it is to search the application source code which is stored ..... Where did you say the latest version is?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 8

Based on what you have posted, these indexes are not helping you in any way. Indexes speed up reads and slow down modifications. In other words an index, when used, will speed up a SELECT query but slow down an INSERT, UPDATE or DELETE query. Since, according to the query you are running (and I have not looked at the article from where you got the query) these indexes are not being seeked or scanned - they are not speeding anything up and only slowing things down.

Just kind of nit picking on this and it's not really important to whether an index is used but indexes aren't only used to speed up SELECT statements, how else does SQL Server find the rows it needs to UPDATE/DELETE?

Alan Burstein SSC Guru Points: 61141 More actions · Answer 9

MadAdmin (3/24/2015)
19 indexes? Epic!
In addition to TheSqlGuru's great advice,
before you drop indexes, take note that there are some heroes who do silly things like drop and create indexes in SSIS packages for the bulk loads to speed up bulk inserts, and these might break if you drop the indexes.
I would recommend disabling indexes first as they would still pass an existence check, and then see if they are still disabled after the month end processing has occurred.
Find sql text which contain the index name.
Drop from staging first ( where they are not enforcing constraints).
Next point, find the hero who created 19 heroic indexes and have a chat with them, else you will be cleaning up his nappies whenever he makes doodoo in future.

The query is returning the indexes from all the tables in the DB not just a single table. Note the queries in the link he provided.

If you work in a Data Warehouse Environment with millions/billions of rows then 20+ indexes is not uncommon. You just don't want them there until AFTER your ETL job(s) is done :-P. 19 indexes on a single table in an OLTP table would be a bad thing though.

That's the benefit of a Datawarehouse, you can have more indexes.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 10

ZZartin (3/24/2015)
Based on what you have posted, these indexes are not helping you in any way. Indexes speed up reads and slow down modifications. In other words an index, when used, will speed up a SELECT query but slow down an INSERT, UPDATE or DELETE query. Since, according to the query you are running (and I have not looked at the article from where you got the query) these indexes are not being seeked or scanned - they are not speeding anything up and only slowing things down.
Just kind of nit picking on this and it's not really important to whether an index is used but indexes aren't only used to speed up SELECT statements, how else does SQL Server find the rows it needs to UPDATE/DELETE?

bwahahahaha! Unfortunately our comments crossed paths and you missed my follow-up comment/examples.

how else does SQL Server find the rows it needs to UPDATE/DELETE?

It depends on the query. Unless, perhaps, your Update is based on a JOIN more indexes will slow down you UPDATE statements, not speed them up.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001