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.