Is Database Engine Tuning Advisor's Recommedations are safe to apply on DB

Question

Is Database Engine Tuning Advisor's Recommedations are safe to apply on DB

neellotus07

Ten Centuries

Points: 1245
More actions
September 3, 2012 at 9:36 am

#393077

Hi All,
I am new to use Database Engine Tuning Advisor for tunning of my company's DataBase Named (ABCData) and I don't have any experience to Tuneup DB on my own. So i made trace template in SQL Profiler to find out slow Queries(those taking time more than 40 seconds to complete) and all data of trace storing in same Database(ABCData) Table named (Slow_Qrys). Then i run the trace for two days and it is sufficient time to cover workload.This trace showing 4 to 6 DB name in Database column of trace but i want to apply tunning only on DataBase Named (ABCData).
Now i want to ask you, have anybody tunned your Whole DB and what is the result of this tunning, i mean after this tunning your DB performing fast transactions or not.Is your DB working fine?. And is it safe to run and apply all recommendations suggested by DTA of SQL 2005.
Pls also tell me should i run whole DB Tunning or Run tunning on those tables & SP that are slow in resulting.Because i was read in google search that tunning whole DB is good than the tune only slow querys to overcome overlapping of indexes.
I am very very tensed & under pressure to do this.Pleeeeeeeeeease help me and suggest some solutions what i do? I will be very thankull to you.
Thanx
Rgrds
Kamal

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

First, you shouldn't even think about "tuning" things that don't actually need it. You can cause much more harm than good. For example, adding an index to a large table that has a first column that contains just 2 or 3 unique values in the table can cause massive timeouts on inserts due to massive extent splits in the indexes.

Second, adding indexes is not a performance panacea and cannot be done in a willy nilly fashion for the same reasons as those given above. If you have queries that are taking more than 40 seconds, chances are good that an index might not even help in such a situation because the query might not even be SARGable.

The index tuning advisor should only be used to give you a hint as to what might be needed. Sometimes it gives good recommendations and sometimes they're absolutely horrible. If you don't know the difference, then don't even think about using the tool. It also has a bug or two in it that make it do things like recommending an index pattern that already exists.

And none of that will help with a poor database design. Even if the database design is perfect, nothing short of rewritting the offending queries will help if the queries were written poorly or in a non-SARGable (can't use index seeks) fashion or it there are many-to-many joins in the query.

Tuning a whole database is a daunting task. I know of some automatic tools that will take 2 days to do it and still come up with some awful recommendations.

The bottom line here is that if you don't know how to tune at the query level, I suggest you get some professional help before you cause some damage by adding indexes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If you mean running tuning adviser and just accepting all suggestions without consideration, then no, it's not safe to do.

You need to make sure your profile trace included all queries, not just the long running ones, otherwise the analysis is based on incomplete data. You also need to test out all suggestions and see which help and which don't and only implement the ones that help.

I've spent significant time with clients removing the indexes created by DTA and I often get nice performance gains from doing so.

If the performance problems are critical and you have no idea where to start, consider getting a performance tuning specialist in to fix the immediate problems and teach you how to do it in the future.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

GilaMonster (9/3/2012)
If you mean running tuning adviser and just accepting all suggestions without consideration, then no, it's not safe to do.
You need to make sure your profile trace included all queries, not just the long running ones, otherwise the analysis is based on incomplete data. You also need to test out all suggestions and see which help and which don't and only implement the ones that help.
I've spent significant time with clients removing the indexes created by DTA and I often get nice performance gains from doing so.
If the performance problems are critical and you have no idea where to start, consider getting a performance tuning specialist in to fix the immediate problems and teach you how to do it in the future.

BIG PLUS ONE ALL ALL COUNTS!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service