Solved

Database Tuning Advisor vs. using your own good judgement

Posted on 2013-11-01
4
242 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:Kdo
Kdo earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now