Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can I really drop that index? Expand / Collapse
Author
Message
Posted Saturday, October 30, 2010 11:46 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Yesterday @ 1:22 PM
Points: 569, Visits: 1,033
Comments posted to this topic are about the item Can I really drop that index?
Post #1013497
Posted Saturday, October 30, 2010 5:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:48 AM
Points: 2,040, Visits: 1,667
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
Post #1013527
Posted Saturday, October 30, 2010 5:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 6, 2014 6:51 AM
Points: 2, Visits: 46
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.
Post #1013528
Posted Saturday, October 30, 2010 5:37 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:48 AM
Points: 2,040, Visits: 1,667
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
Post #1013529
Posted Saturday, October 30, 2010 7:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 30, 2010 7:08 PM
Points: 3, Visits: 5
have got a scheduler that does two job

- Delete indexes not been used for a year.
- ReIndex all , after hours

Post #1013532
Posted Sunday, October 31, 2010 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 6, 2014 6:51 AM
Points: 2, Visits: 46
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).
Post #1013574
Posted Sunday, October 31, 2010 8:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 11:00 AM
Points: 46, 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.
Post #1013577
Posted Sunday, October 31, 2010 10:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 3:26 PM
Points: 6, 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.
Post #1013600
Posted Monday, November 1, 2010 6:45 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:58 AM
Points: 58, Visits: 215
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
Post #1013784
Posted Tuesday, November 2, 2010 8:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1014542
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse