September 2, 2008 at 10:13 am
The database has about 200 tables and hasn’t been index tuned for a couple years. I am planning to do an index tuning by first turning profiler on to capture the workload for half day. Each workload file will be limited to 20MB (about 50 minutes trace, so will be 6-8 files); then running Index Tuning Wizard with the load of each .trc file. My question here is shall I check the check box “Keep all the existing index” when I load the each .trc file? The database has been modified in the latest release, a lot of stored procedures are not used anymore. Is there a better way to do index tuning in my case?
September 2, 2008 at 10:20 am
If you are confident that you will have a COMPLETE set of activity types that are going to occur against the database then by all means you could allow the Advisor to consider removing indexes. However, remember that the tool is only an advisor and will not tune your database perfectly. So, dropping indexes to me is often a bit more shaky especially as there is typically activity that you can't or don't catch in your trace times such as nightly run jobs, reports, ad-hoc stuff, etc.
Work through the changes carefully and you should be ok. I certainly wouldn't recommend a blanket application of recommended indexes or removal.
A good bit of work unfortunately but they only way to truly tune.
Enjoy!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 2, 2008 at 11:44 am
I generally don't recommend that index tuning be done on an entire database at once. Too much opportunity for something to go wrong. i perfer to either tune indexes one table at a time or one procedure at a time.
Half a day is probably not enough to capture all possible queries that happen to a database unless you have a very regular workload.
Lastly, be careful with the Index Tuning Wizard. It's not perfect. Don't take all of its recomendations and apply them without testing that they do infact improve performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2008 at 12:00 pm
I'd also take a close look at each recommendation. You may actually find overlapping indexes that could be merged into a single index.
I also agree heavily with testing all the recommendations one at a time in a test environemnt.
😎
September 2, 2008 at 12:09 pm
Thanks for the reply.
Then shall I keep all the existing index when I am doing index tuning table by table? Or let the tuning wizard drop the index it thinks unuseful?
Thx again
September 2, 2008 at 12:45 pm
Are you completely certain that the trace you have given the tuning wizard contains every single query that gets run against your SQl server, including any regular batch processes, month end processes, year end processes or the like?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2008 at 1:16 pm
95% sure. Month, quarter and year Reports request the same thing with different range of data. I will capture a overnight batch process with a four hours limit. I am planning run the tuning on test server before I do it on production.
September 2, 2008 at 1:42 pm
Then you're about 90% safe to drop the indexes that the tuning wizard doesn't want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2008 at 1:45 pm
Yeah, just be prepared for the userX calls saying that his / her ad-hoc queries are running really slow today and they can't figure out why. 😛 Of course, that might be a bit fun too. Bad David....
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply