June 12, 2014 at 9:42 am
I know that sql server is overly eager to recommend indexes when you query the dmv's and likewise from what it recommends from the output of the DTA.
I have just run a workload trace into the DTA and added a few of the recommended indexes which seem to be being used in a favourable manner.
However I have lots of recommendations for statistics - is it wise to just follow what the dta says and add them ?
One other thing to note is that the database has 'Auto create' and 'Auto update statistics' set to true - so how come the dta finds so many of these statistics to add ?
June 13, 2014 at 4:11 am
I don't trust the DTA recommendations on anything. It makes way too many suggestions and misses really obvious ones. You'll note that it almost never suggests a clustered index or a change to a clustered index. It's also notorious for suggesting lots and lots of statistics and filtered statistics. Again, I don't trust it or follow it's recommendations.
The software was written by the marketing team, not the query engine team. If it was the latter, I might put more trust into it, but as it is, no way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 22, 2014 at 7:56 am
I agree to Grant Fritchey as far as I'm familiar with the DTA (he sure know's better than me).
I've seen some "mistakes" in the DTA-suggestions as well.
Also, the DTA shows you suggestions for one specific query, I think. A real DBA has think in a different way to not create many similar or even overlapping indexes.
For a real emergency, where performance for a query or for serveral queries on a specific table, you might take a DTA-suggestion and create the indexes, if there's nobody else to help you. In many cases it might help.
Still you should inform your DBA so he can think of the creation and decide which indexes might be better.
June 22, 2014 at 10:14 am
WolfgangE (6/22/2014)
Also, the DTA shows you suggestions for one specific query, I think.
That's the missing index DMVs. DTA can tune a workload. It's still very prone to over-suggesting indexes. Badly so.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply