My background: I'm a dev/architect, not a DBA. Sorry!
So we have a ~400 table 75GB database. I ran Profiler for ~24 hours ("Tuning" template minus LoginName) and have ~7GB of usage recorded in a trace file. I then ran the Database Engine Tuning Advisor on this trace file (no partitioning, keep clustered indexes, PDS Recommend: Indexes) against our production database (after hours). I gave it ~4 hours to analyze. And here are the summary results I got:
The things that stood out here to me that seem "wrong" are:
- It only tuned ~9k out of ~530k queries
- It recommended I drop a ton of indexes (in fact, most of them)
- It recommended I create 0 indexes
Does this sound right to you guys? I expected it to drop most of my indexes but then to create a ton of new indexes. Also, if it takes 4 hours to analyze 9k queries, is it even feasible for me to get this to consider a normal day's worth of usage? Compared to most large databases, ours is fairly light on consumption (~50 users total).
I think I'm either misunderstanding something or am simply doing something wrong.
Best Answer
I wouldn't bother with DTA. It's a whole lot of work for results that are sketchy at best. Everyone that gets into index tuning eventually realizes it's both a science and an art, and there's a lot to it. You can use tools to get recommendations, but implementing those recommendations blindly almost always results in issues down the road.
Instead, I'd give BlitzIndex a try. It'll take advantage of the statistics SQL Server already maintains to determine where you can improve your indexing (and does so within seconds). It also provides very accessible documentation on how to use the results. Every result includes a link to the pertinent doc. This is how I maintain my indexes these days.