January 25, 2009 at 11:09 pm
I ran a profiler to capture activities on the production server for performance tuning, I then used the tuning advisor on the captured trace file. As part of recommendations it came up with several indexes and a number of statistics objects to be created.
We do have indexes in our tables, I would like to find out if one can rely on the tuning advisor and implement all of its recommendations?
Thank you in advance.
January 25, 2009 at 11:20 pm
DTA is very good tool but you have to present substantial query load (representative of complete load) for it to give a good set of options. I wouldn't simply create all the indexes & stats advised by DTA based on only few queries. And also don't forget that you need to find the right balance between writes/reads. Having too many indexes with less (no) reads hurts the performance.
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
January 26, 2009 at 12:38 am
thulani.moyana (1/25/2009)
I would like to find out if one can rely on the tuning advisor and implement all of its recommendations?
No.
The DTA does an OK job. Not great, but OK. Regardless of how much of your average workload was in the sample that it used, it has a tendency to massively over-recommend indexes, recommending, say, 3 indexes and 10 statistics where just one index is needed.
My personal preference is not to use it at all and to do index tuning by hand. If you want to use it, fine, but don't just apply its recommendations. Test them out, one by one on a test server, see which of its suggestions help a lot and which ones don't and then just implement the ones that you've found give a good improvement.
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
January 26, 2009 at 6:55 am
Just to pile on a bit, I find the DTA to be very problematic. I've given it substantial loads to see what it will do and it misses very obvious and simple turning opportunities. Sometimes it misses index recommendations that are sitting in the missing indexes DMV.
I agree with Gail. You can use it, but you need to take it's recommendations with a very hefty grain of salt, especially when it recommends dropping objects.
"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
January 27, 2009 at 1:31 pm
I will take the opposite end of the spectrum and say that DTA should be avoided at all costs by anyone without significant knowledge of SQL Server and indexing strategies and the database/application at hand. And if you have that you should do your own indexing anyway. I have seen it repeatedly do amazingly bad things to databases.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 20, 2009 at 7:13 am
TheSQLGuru -How about some examples of these "amazingly bad things" you say the tuning advisor has done to databases? I've used it a couple of times with great results. Some stored procs ran exponentially quicker.
October 20, 2009 at 8:22 am
ed_elfrink (10/20/2009)
TheSQLGuru -How about some examples of these "amazingly bad things" you say the tuning advisor has done to databases? I've used it a couple of times with great results. Some stored procs ran exponentially quicker.
1) it routinely creates overlapping indexes by including more and more columns or simply switching order. maint overhead for no gain
2) it LOVES included columns, often including 80% or more of the columns in a table to cover a particular query
3) I have seen it increase database size by over TEN FOLD
4) I had a client where I was able to remove 72% of all indexes created (all by DTA, including an astounding 62 indexes on one table) and reworked about 50% of the remaining indexes. Read performance on average was slowed by less than 5% but DML activity improved by over 400% with significant reductions in blocking. LOTS of timeouts were eliminated.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 20, 2009 at 8:26 am
I've seen a case (posted here actually) where someone implemented the recommended indexes and performance degraded 40%.
I have a client that ran DTA, implemented all of it's recommendations and now has 28 indexes on a heavily inserted transactional table. Insert performance has declined quite dramatically. Many of the indexes are duplicates.
It's overly fond of recommending far too many indexes. In a test I gave it one query, it gave back a recommendation for 5 indexes and 7 column statistics. Implementing one of those indexes gave a 98% performance improvement. Implementing the other 4 and the stats improved performance by maybe 2% and tripled the size of the table.
It's not a bad place to start, but it's recommendations have to be vetted and tested, not implemented blindly.
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
October 20, 2009 at 8:50 am
And I've seen the opposite. Until a recent bug fix, it was missing indexes that were blatant and obvious that resulted in major performance improvements. There's an MS Connect issue on that one by the way.
When you have no alternatives, meaning, no one in-house who can tune indexes or queries and no budget to bring people in, it can be a somewhat useful tool. But it's no substitute for good old fashioned labor.
"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
October 20, 2009 at 9:05 am
I agree, it is no substitute for hard work, ie. review, research and familiarity with the environment. While it may recommend "too many" or duplicate indexes it also may recommended some indexes or statistics I never would have thought of. I'll continue to use it but review the results before applying some or all of them.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply