al4629740
asked on
Database Tuning Advisor vs. using your own good judgement
I am trying to debate whether to use the Database Tuning Advisor in SQL Studio vs. just picking columns that need to be indexed in my table. Is there really an advantage to using the Database Tuning Advisor.
Also, how does it work? Do I just run an aggressive script and then apply the recommendations afterwards?
Also, how does it work? Do I just run an aggressive script and then apply the recommendations afterwards?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I agree with Kent , adviser is there to "advise" , You could try few variations where to put Your indexes ,still You will determine the best use of indexes for Your table based on the factors You know the best .
For arguments against Tuning Advisor, you might want to visit:
this post by bhess1
and this one by chapmandew
- these questions are slightly different but I think the opinions expressed are relevant although a couple of years ago.
Given that your question indicates you already have ideas on what the indexing should be I'd suggest adding those, then profiling, then using the advisor.
With respect to how does it work, yes, that is the sequence - but you don't have to implement the advice:
Specifics on how to use it are available here
btw: an inherent assumption: don't run it directly in production
this post by bhess1
and this one by chapmandew
- these questions are slightly different but I think the opinions expressed are relevant although a couple of years ago.
Given that your question indicates you already have ideas on what the indexing should be I'd suggest adding those, then profiling, then using the advisor.
With respect to how does it work, yes, that is the sequence - but you don't have to implement the advice:
Look and see how much improvement Tuning Adviser things it will get - is 5% worth it? Maybe. is 50% + worth it, quite likely.You can also choose to implement advice incrementally [option: re-profile ] and re-using the Advisor.
That is, if one or several recommendations gets 80% say, and the rest are small, then the two that give you the 80% improvement are the ones you need to concentrate on, and maybe completely ignore the others.
dtodd
Specifics on how to use it are available here
btw: an inherent assumption: don't run it directly in production
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.