July 14, 2011 at 7:55 am
So, recently I have been looking into executions plans and thanks to g.fritchey's book on execution plans i feel as i have learnt alot about "manually " tuning queries etc.
However, recently I was told that alot of DBAs use DTA as a starting point to start performance trouble shooting work, I always thought that its best to do these things manually....
I read from a SQL book:
"The DTA provides features designed for
both the novice and experienced DBA. In fact, I would bet that the DTA can do a better job of tuning
indexes than most DBAs who perform the same task manually"
basically, i am just wondering, what do people do here... start of with the DTA or go the manual route? im kind of confused as to which approach to take? is DTA reliable?
your thoughts please?
July 14, 2011 at 8:05 am
I've never yet found the DTA to be either reliable or useful to me.
Last time I checked, it recommended creating a whole list of indexes which could all be satisfied by creating one single index of slightly better design. It actually recommeded adding something like 20 or 30 indexes to one table, all of which were only marginally different. With that many, the query optimizer would end up ignoring most of them all of the time. I actually tested it in a dev environment and found that only one of them ended up being used, and where the rest might have been useful, the SQL Server engine ignored them and did bookmark lookups instead. Manually built the index I wanted, and no more bookmark lookups, and a lot less wasted disk space.
Others may have different results with it.
I start with the index DMVs these days, but I use judgement from there, not the DTA.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2011 at 9:12 am
I usually use DTA when i have no documentation about a database that's having performance issues.
it does recommend creating indexes on every column in the where clause and depending on the case not work well with include columns.
You should always fine tune the results manually.
Did you read Grant's 2008 book on execution plans? I don't see that statement in there.
July 14, 2011 at 9:26 am
AlexSQLForums (7/14/2011)
I usually use DTA when i have no documentation about a database that's having performance issues.it does recommend creating indexes on every column in the where clause and depending on the case not work well with include columns.
You should always fine tune the results manually.
Did you read Grant's 2008 book on execution plans? I don't see that statement in there.
yes sorry - that quote was from MASTERING sql PROFILER - brad mcghee.
i believe that Grant prefers to tune the manual way 🙂
( was a great read)
i guess its fair to say that sql expert opinions differ on this subject......
July 18, 2011 at 2:51 am
You scared me very badly. I can't imagine saying that DTA would do a better job than an experienced DBA.
Personally, I'm extremely underwhelmed by what the DTA delivers. Is it better than nothing? Possibly. But, I do prefer tuning "manually" until I see something better come along.
"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
July 18, 2011 at 2:53 am
AlexSQLForums (7/14/2011)
I usually use DTA when i have no documentation about a database that's having performance issues.it does recommend creating indexes on every column in the where clause and depending on the case not work well with include columns.
You should always fine tune the results manually.
Did you read Grant's 2008 book on execution plans? I don't see that statement in there.
He can't read the 2008 book unless he's hacked my machine. It's not done yet. It will be shortly. Just in time for me to start rewriting it all for Denali.
"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
July 18, 2011 at 8:02 am
Grant Fritchey (7/18/2011)
AlexSQLForums (7/14/2011)
I usually use DTA when i have no documentation about a database that's having performance issues.it does recommend creating indexes on every column in the where clause and depending on the case not work well with include columns.
You should always fine tune the results manually.
Did you read Grant's 2008 book on execution plans? I don't see that statement in there.
He can't read the 2008 book unless he's hacked my machine. It's not done yet. It will be shortly. Just in time for me to start rewriting it all for Denali.
Grant
It's the one about execution plans from 2008. Great book 🙂
July 18, 2011 at 8:20 am
AlexSQLForums (7/18/2011)
Grant Fritchey (7/18/2011)
AlexSQLForums (7/14/2011)
I usually use DTA when i have no documentation about a database that's having performance issues.it does recommend creating indexes on every column in the where clause and depending on the case not work well with include columns.
You should always fine tune the results manually.
Did you read Grant's 2008 book on execution plans? I don't see that statement in there.
He can't read the 2008 book unless he's hacked my machine. It's not done yet. It will be shortly. Just in time for me to start rewriting it all for Denali.
Grant
It's the one about execution plans from 2008. Great book 🙂
Oh, sorry. Misunderstood. I thought you meant for 2008, not in 2008. That makes sense.
"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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply