June 22, 2017 at 4:05 am
Hello
Using the database engine tuning advisor, is the any way to show how much each individual recommendation will improve your queries?
It shows overall improvement, but i want to see individual object.
June 22, 2017 at 4:20 am
If it's not shown in the DETA report? If you're talking about indexes, you might consult the missing indexes DMVs, one of which I think does show the anticipated percentage improvement.
John
June 22, 2017 at 4:27 am
SQLAssAS - Thursday, June 22, 2017 4:05 AMHelloUsing the database engine tuning advisor, is the any way to show how much each individual recommendation will improve your queries?
It shows overall improvement, but i want to see individual object.
I would take the DTA recommendations with a pinch of salt, it does not recognize existing indices, overlaps etc.
😎
June 22, 2017 at 4:55 am
The DTA works off of estimations in the optimizer. It can't measure actual performance improvements, let alone provide you with a projected report of them. Instead, it's basing it's recommendations of improvements based on lowering the estimated costs of execution plans based on the addition of indexes or statistics to change how the plans are generated.
I'm 100% with Eirikur on this. You need to be extremely judicious in accepting any recommendation from this tool. In fact, this tool is so weak that it's really not worth using. If you want to improve performance, you'd be better off doing the work yourself.
"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, 2017 at 7:31 am
Thanks for the replies.
This is the first time I have used it, I normally use all the DMV's as mentioned above but was curious how DTA works and how effective it is.
I will continue with caution..
June 22, 2017 at 7:36 am
SQLAssAS - Thursday, June 22, 2017 7:31 AMThanks for the replies.
This is the first time I have used it, I normally use all the DMV's as mentioned above but was curious how DTA works and how effective it is.
I will continue with caution..
In my opinion, it doesn't work 😉
😎
June 22, 2017 at 7:57 am
Eirikur Eiriksson - Thursday, June 22, 2017 7:36 AMSQLAssAS - Thursday, June 22, 2017 7:31 AMThanks for the replies.
This is the first time I have used it, I normally use all the DMV's as mentioned above but was curious how DTA works and how effective it is.
I will continue with caution..In my opinion, it doesn't work 😉
😎
Agreed.
"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, 2017 at 12:01 pm
Grant Fritchey - Thursday, June 22, 2017 7:57 AMEirikur Eiriksson - Thursday, June 22, 2017 7:36 AMSQLAssAS - Thursday, June 22, 2017 7:31 AMThanks for the replies.
This is the first time I have used it, I normally use all the DMV's as mentioned above but was curious how DTA works and how effective it is.
I will continue with caution..In my opinion, it doesn't work 😉
😎Agreed.
I will state, and NOT as an opinion, that the DTA DOES NOT WORK. It does HORRIBLY BAD THINGS TO YOUR DATABASE IF YOU USE IT. All stop.
In my career as a SQL Server consultant I have made somewhere around 200000USD DIRECTLY attributable to the use of DTA by various clients.
You yourself can actually quite easily do what the DTA does. Here's the logic:
1) Take a query and find the one or few columns that will/may restrict rows reasonably effectively for said query
2) Put those column(s), and perhaps a few randomly chosen other columns used anywhere in the query, into the indexed columns
3) Put EVERY other column used in the query, up to and including every column on the table at hand, into the INCLUDE section of the index
4) Default EVERY available CREATE INDEX option
5) DO NOT evaluate any existing index while doing the above
6) Create the index
VOILA - you are now the DTA!!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 22, 2017 at 1:16 pm
I'd be very skeptical of any advice from the Missing Index views as well. They have all the same problems people have mentioned about Database Engine Tuning Advisor plus more including no consideration for order of columns in the index:
https://technet.microsoft.com/en-us/library/ms345485(v=sql.105).aspx
June 23, 2017 at 2:40 am
appreciate all the feedback - I won't waste my time with it!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply