I've asked myself, what would be the best strategy to keep my indexes always optimal and up-to-date. What are your practices.
I tried several approaches :
- Missing index views and unused index views : I marked that ones, that need to persist, allthough they are not used often through extended property comments and deleted the unused periodically and create the missing new ones. Problems : Danger of creating and deleting the same indexes over an over again, because you got other better indexes, or the index get's shown as missing allthoug you created it (MS Bug in SQL Server 2008 prior to R2). this is a lot of work and not taking care of partitioning etc. Why is impact not a counter I can rely on...what can I rely on?
- DTA : The database tuning advisor seems to be good, but the naming conventions are terrible and the dta doesn't know about indexes that need to be persisted because it speeds up a very important query executed very seldom up to 100%. So it's a lot of extra work an I never get rid of the best and optimal settings for index maintenance in my very special case, because the documentation is very bad. I don't know in deep how the dta works and I want to have control over what I do. Problems : Capturing a workload over such a long period of time, that all interesting queries get captured.
- Manual Analysis : I traced out the queries that took the longest time and tried to speed them up through analysis of the execution plans. Problems : What is the best way to have the optimal amount of indexes serving all statements as best as possible. Is it better to have wide indexes or is it better to have small indexes whit the downside of slowing down inser, updates and deletes.
I hope you know what I mean and can help me with your experience.
Thanks in advance!!! God bless you!!!