October 30, 2010 at 11:46 am
Comments posted to this topic are about the item Can I really drop that index?
October 30, 2010 at 5:00 pm
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
October 30, 2010 at 5:33 pm
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.
October 30, 2010 at 5:37 pm
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
October 30, 2010 at 7:09 pm
have got a scheduler that does two job
- Delete indexes not been used for a year.
- ReIndex all , after hours
October 31, 2010 at 7:43 am
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).
October 31, 2010 at 8:06 am
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.
October 31, 2010 at 10:47 am
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.
November 1, 2010 at 6:45 am
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
November 2, 2010 at 8:25 am
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
November 2, 2010 at 4:04 pm
I could see uses for this kind of index. Without being required to run rebuild, it would be nice to test an index here or there - on the high availability systems.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 8, 2010 at 9:21 am
Sounds like a fantastic idea to me.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply