SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I really drop that index?


Can I really drop that index?

Author
Message
Tony Davis
Tony Davis
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: Administrators
Points: 1763 Visits: 1171
Comments posted to this topic are about the item Can I really drop that index?
Paul Randal
Paul Randal
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12720 Visits: 1721
Invisible indexes - you can already do that in SQL Server since 2005 - ALTER INDEX .... DISABLE and then REBUILD to enable it again if you want.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
luis.a.martin
luis.a.martin
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 47
I'm free lance DBA, one part of my work in any client is to improve performance.
So I use to check execution plan and make some indexes.
After a while (said at least 3 month with non stop SQL) I check if my indexes are using or not. If no, I drop it.
Paul Randal
Paul Randal
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12720 Visits: 1721
And I meant to add, I use the index_usage_stats DMV all the time with clients - helps to see how indexes are being used, which can be more important than whether they're being used.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
techamitdev
techamitdev
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 6
have got a scheduler that does two job

- Delete indexes not been used for a year.
- ReIndex all , after hours
luis.a.martin
luis.a.martin
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 47
Since all my client have 3rd party software, I can only drop my indexes not those who came with original software.
Talking about maintenance, I use to reindex also in week ends, and full update statistics during no working ours (from Monday to Friday).
Steven Devaney
Steven Devaney
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 50
It is important to consider if an index is every used. As the DMV information on unused indices is lost at each restart of the instance you may need to record this information yourself from the DMVs regularly before even thinking of disabling the index or indices in question.
Some occasional processing can have a major impact on a database system and you could get a bit of a surprise when performing processing that occurs only monthly or quaterly for example.
I have used the ALTER INDEX DISABLE and ALTER INDEX REBUILD method very successfully.
"Getting a quart into a pint pot"
Disabling indices can also be useful when you desperately need to get a version of your database with a really small footprint. To do this you need to disable lots of indices before performing the backup (not from production!), shrink it all as much as possible (performance is not the issue here - disk space is) then backup your DB. Restore it on your disk-space challenged system and enable (rebuild) only the indices that you need. You will need to rebuild lots of indices if you have shrunk any filegroups and do please SORT_IN_TEMPDB - this will reduce the amount of additional growth of your filegroups while rebuilding large indices.
Bob Barrows
Bob Barrows
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 137
I'd found a script that persists index usage stats into a table so that restarts don't cause them all to be reset. It runs hourly and updates the stats for each index in the table.

This was good, but it in creating a single record for each index on the server, it did not provide the information I needed to decide whether or not to retain an index. I've come to the conclusion that usage trends are more important than simply the raw numbers of how many times the index gets used. So I modified the script to create a new snapshot of the index usage every two hours, storing the delta values so I can compute stats like seeks per day. I've created a report to graph the daily usage of each index so now I can see the trends that help me decide whether to drop little used indexes. For example, two indexes might have 4000 user seeks total, but one might have accumulated those seeks three weeks ago, and has not registered any in the past three weeks. Armed with this information, I can investigate: perhaps refreshing the statistics to see if that gets the index used again, and if not, dropping it totally.

What is still missing from my analysis is a list of queries that are currently using each index so that, when a query does stop getting used, I can display the execution plan for the query that was using the index to see if the optimizer still calls for the index to be used. If so, this would be a good indication that the report that was using that query has either been changed so the query is not being used, or the report is not being used. This can tell me whether I should drop the index, or, if the report is performing poorly as a result of the change, modify the index so the changed report goes back to using it.
DavidBridgeTechnology.com
DavidBridgeTechnology.com
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 238
I think the question you you mainly want to hear answered is "Are invisible indexes a good idea? (for future sql server versions)".

Personally, I like the idea as a rebuild on a large table can be really time consuming. I am currenly dealing with tables with over 200 million rows and this does sound like a good idea in this case.

For smaller tables I doubt it makes any difference and have concerns that idiot IT development managers may edict the feature's usage over index drops e.g. "All indexes must be made invisible for 6 months prior to being dropped". If this happens then nobody benefits as it just becomes another long term maintenance task.

My conclusion...

As long as the DBA can make their own decisions about when to use the feature I would say it should be implemented.

David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
GSquared
GSquared
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94855 Visits: 9730
I think it would be quite useful on 24x7 systems. Anything else, you probably have downtime that could be used to rebuild an index if you find it really was useful after all. But for large tables that are being constantly accessed, the ability to make one unqueryable while still maintaining data in it, and monitor the performance impact of that over a period of time, does sound useful. If you've suggested this on MS Connect, please post the link and we can all vote for/against there.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search