Solved

Database Tuning Advisor vs. using your own good judgement

Posted on 2013-11-01
4
264 Views
Last Modified: 2013-11-04
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?
0
Comment
Question by:al4629740
  • 2
4 Comments
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 250 total points
ID: 39618260
There's a reason that it's call "The Advisor".  It can make general recommendations on ways to make general queries better, but it can't know how you use the data and if your needs are outside of its scope.

Some columns are easy to identify as likely candidates for an index.  Others are not.  You wouldn't normally need an index on the amount column of a transaction log, but if you have a requirement to report on amount by date, a composite index over date and amount may be appropriate.

Let the Advisor suggest things that it finds.  But only the DBA can really know where to place indexes.


Kent
0
 
LVL 21

Expert Comment

by:oleggold
ID: 39618305
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 .
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39618359
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:
Look and see how much improvement Tuning Adviser things it will get - is 5% worth it? Maybe. is 50% + worth it, quite likely.

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
You can also choose to implement advice incrementally [option: re-profile ] and re-using the Advisor.

Specifics on how to use it are available here

btw: an inherent assumption: don't run it directly in production
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39618361
While not directly related to Tuning Advisor, this statement by Scott Pletcher may influence the indexing you are about to perform:
[Btw, worst performance myth of all time: no matter what some "experts" say, an identity column should NEVER be considered a "default" clustering key.  The clustering key should istead always be carefully chosen for that specific table's data, NOT based on some nursey-rhyme-like saying about "identity always makes a good default clustering key".]
see this question

Scott is someone I would utterly trust in index design, and I've seen him make this point several times.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SqlServer Table Triggers 3 28
find SQL job run average duration 24 56
Can I skip a node in XML? 9 34
Converting Stored Procedure to SQL Statement 5 42
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question