Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Database Tuning Advisor vs. using your own good judgement

Posted on 2013-11-01
4
Medium Priority
?
304 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 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1000 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 49

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 49

Accepted Solution

by:
PortletPaul earned 1000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

564 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