December 9, 2011 at 12:57 pm
Hello
when i use from Data Base Engine Tuning, it gives many suggestion about that we can better our query or suggestion that we use from many indexes, how can i understand, which suggestion is the best? must i do use
all of the suggestions it?
Thank You
December 9, 2011 at 1:12 pm
While the DTA is a wonderful tool, you really do need to understand SQL Server, your database and your business use cases to properly implement the changes it will suggest. For example, indexes that it wants to create will speed up the code in question, but it will obviously impact the delete, update and inserts related to the table indexed.
Indexes the DTA suggests you delete may be fine, but what was the work sample you used to get the advice? Did it cover all the uses of the database or was it a very small sample?
While DTA is a useful tool, it is a dangerous tool to use if you are just applying changes without thought, or if you don't have the expertise to understand ramifications of those changes. I know this doesn't necessarily help, but I would suggest you focus on specific performance issues instead of using the DTA. Running some traces to find expensive queries along with the many DMVs available.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
December 12, 2011 at 6:12 am
And there is the problem with the DTA in a nutshell. How important are the suggestions it has, not even worrying about whether or not those suggestions are helpful.
Unfortunately, you really only have two options.
Option 1) Take everything it says and implement it. You won't know which is good and useful and which isn't.
Option 2) Learn what are your most costly queries either in terms of execution time or cost per execution. Figure out what's wrong with them. Determine if the DTA suggestions are helpful.
The problem with Option 2 is that if you can do that, you don't need the DTA.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 12, 2011 at 2:42 pm
I agree with Grants Option 2, if you understand it, you dont need it.
I would add option 3 if you dont understand it dont touch it! you will more then likely cause more harm then good.
Better yet I think you need to buy GRANTS book. Start there, when understand his book you wont need the DTA.
Good luck
December 12, 2011 at 6:42 pm
Some of the worst things I have ever had to clean up at clients came from the unfettered use of DTA. Use it (without sufficient knowledge and thought) at your peril. And if you have said knowledge and thought you won't need it at all.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 12, 2011 at 9:24 pm
Option 4... provided that it ISN'T a production server, learn from your mistakes... let DTA rip and discover the problems it causes. "A Developer must not guess... a Developer must KNOW" and sometimes the only way to truly know is to make the mistake and learn from it.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2011 at 12:24 am
Jeff Moden (12/12/2011)
Option 4... provided that it ISN'T a production server, learn from your mistakes... let DTA rip and discover the problems it causes. "A Developer must not guess... a Developer must KNOW" and sometimes the only way to truly know is to make the mistake and learn from it.
Agree but I find a catch here. Developer should possess good analytical skills as well. Else he might be happy noticing the performance gain on SELECTs by adding more and more indexes on table and might ignore the performance degradation on DMLs.
I believe systematic learning would be good idea as compared to learn in hard way (he/she will get enough opportunities in routine work anyways ;-)).
December 13, 2011 at 5:27 am
Dev (12/13/2011)
Jeff Moden (12/12/2011)
Option 4... provided that it ISN'T a production server, learn from your mistakes... let DTA rip and discover the problems it causes. "A Developer must not guess... a Developer must KNOW" and sometimes the only way to truly know is to make the mistake and learn from it.Agree but I find a catch here. Developer should possess good analytical skills as well. Else he might be happy noticing the performance gain on SELECTs by adding more and more indexes on table and might ignore the performance degradation on DMLs.
I believe systematic learning would be good idea as compared to learn in hard way (he/she will get enough opportunities in routine work anyways ;-)).
I've found that the combination of the two methods makes a better teacher. If you don't press against the sides of the box, you don't know that you're in one. 🙂 If systematic learning were all that folks knew, then things like the Tally Table, the Quirky Update, and using XML for high speed concatenation might not exist. I've also found that failure can greatly increase analytical skills. If nothing ever goes wrong, there's nothing to practice such skills on. Heh... ironically, in order to succeed, you must also be given the opportunity to fail.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2011 at 5:31 am
Hence my favorite Moto => Fail Fast Forward.
December 13, 2011 at 6:04 am
If systematic learning were all that folks knew, then things like the Tally Table, the Quirky Update, and using XML for high speed concatenation might not exist.
So they are accidental inventions just like penicillin... 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply