This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(80, 9%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELR%3C/text%3E%3C/svg%3E)
Hi, I'm a newbie to database tuning advisor and haven't been able to find an answer to this question. When I run DTA against a workload, it gives me a total estimated improvement number for that workload and recommends adding statistics and/or indexes for 5 tables. I this case, the estimated improvement is 75%, When I run run "Evaluate Recommendations" for all the recommendations for each individual table, the highest estimated improvement is 27% and the sum of all 5 is 57%. Based on other threads, I think I understand what the estimated improvement value means for an individual change like a new index, but I don't understand how DTA comes up with 75% unless there is some kind of synergy between the changes. I've read enough to not blindly trust DTA and would not do an "apply all", I just am curious how this number is calculated. Thanks!
Hello,
The DTA let the database engine creating an execution plan for the query, evaluates it and tries to generates may useful indexes as "Hypothetical indexes" in the database engine, create new execution plan and compares the result.
DTA overacts this a bit, it sometimes adds tons of columns to the INCLUDE clause to get a full-covering index.
Hi Olaf,
Thanks for the response. I like the term "hypothetical" and that is what I am trying to sort out- I'm trying to go through the recommendations and see if there are any worth implementing. What I am trying to understand is how they come up with a 75% overall improvement number when no individual index is higher than 27%. I would expect that there would be some indexes at 90% and some that were lower. Perhaps I misunderstand that total number or it just really isn't very useful. Thanks again!