Database Tuning Adviser (DTA)

Question

Database Tuning Adviser (DTA)

pacita_tibay

SSC Enthusiast

Points: 135
More actions
January 8, 2010 at 11:55 am

#214339

HI,
I have a complex query and tried to use the DTA to analyze the query. Based on its recommendations, it will improved performance by 81%. When I looked at the recommendations, it listed several creation of NonCLustered Indixes, some of which are similar but only the order of fields are different.
Example:
CREATE NONCLUSTERED INDEX [_dta_index_csincidenthistory_5_1552060615__K7_K8_K2_K3_6] ON [dbo].[csincidenthistory]
(
[statusid] ASC,
[statusidlastweek] ASC,
[incident_id] ASC,
[secondary_id] ASC
)
INCLUDE ( [date_ih]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE NONCLUSTERED INDEX [_dta_index_csincidenthistory_5_1552060615__K2_K7_K8_K3_6] ON [dbo].[csincidenthistory]
(
[incident_id] ASC,
[statusid] ASC,
[statusidlastweek] ASC,
[secondary_id] ASC
)
INCLUDE ( [date_ih]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE STATISTICS [_dta_stat_1552060615_3_7] ON [dbo].[csincidenthistory]([secondary_id], [statusid])
go
CREATE STATISTICS [_dta_stat_1552060615_2_8] ON [dbo].[csincidenthistory]([incident_id], [statusidlastweek])
go
CREATE STATISTICS [_dta_stat_1552060615_8_7_3] ON [dbo].[csincidenthistory]([statusidlastweek], [statusid], [secondary_id])
go
CREATE STATISTICS [_dta_stat_1552060615_2_3_7] ON [dbo].[csincidenthistory]([incident_id], [secondary_id], [statusid])
go
I just started using DTA. Are the nonclustered indexes different because of the probably the order of the select statements in the query are different? I'm not quite sure why it creates 2 nonclustered indexes with the same fields but only different order.
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

DTA will throw out multiple indexes that it deems appropriate. It is recommended that you test the indexes to ensure they will work appropriately. You will not always need all indexes suggested by DTA. From the two you have listed, create one test it and then drop it. Create the other, test it and compare the results with the first one. If needed, create both and then test again.

Also, it is a good idea to rename your indexes to something more meaningful than the default DTA name.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

Take DTA's recommendations with a very large pinch of salt. Test them all carefully before you decide to implement (yes, the statistics too).

It's also worth noting that SQL seldom will use two indexes on the same table for one query. So one of those is probably far more useful than the other. Can't say which one without seeing the query and existing indexes.

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

konkakiran81 Newbie Points: 9 More actions · Answer 3

it is better if you compare your result along with trace, there you select duration and also reads as well as writes for that complicated query and based on that you can proceed:-)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

be wary!!! If you let it DTA will severely bloat your database with massive numbers of indexes with huge amounts of INCLUDED columns. And trust me, LOTS of bad consequences come from that.

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

gmby SSCarpal Tunnel Points: 4511 More actions · Answer 5

lol, I've got a database that has a few of those runaway DTA indexes (all columns included) - almost like having multiple clustered indexes <grin>

btw, you can get the cpu, duration, reads and writes using SET STATISTICS...to me easier than setting up traces...

set statistics io on

set statistics time on

go

--query goes here

go

set statistics io off

set statistics time off

go

jg

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

gmby (1/15/2010)
lol, I've got a database that has a few of those runaway DTA indexes (all columns included) -

I've seen that as well. Nice to run the utility just to see what it thinks. But implementing the suggestions is a totally different story.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

CirquedeSQLeil (1/15/2010)
gmby (1/15/2010)
lol, I've got a database that has a few of those runaway DTA indexes (all columns included) -
I've seen that as well. Nice to run the utility just to see what it thinks. But implementing the suggestions is a totally different story.

I had a client pay me a tidy sum last year to clean up a HUGE mess they got into with running rampant with DTA. 🙂

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

TheSQLGuru (1/15/2010)
CirquedeSQLeil (1/15/2010)
gmby (1/15/2010)
lol, I've got a database that has a few of those runaway DTA indexes (all columns included) -
I've seen that as well. Nice to run the utility just to see what it thinks. But implementing the suggestions is a totally different story.
I had a client pay me a tidy sum last year to clean up a HUGE mess they got into with running rampant with DTA. 🙂

I like that Idea. I should find me some of those.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

It was actually a very intense and interesting exercise. Obviously significant tuning and benchmarking, but quite a bit of analytical work too. When you have a table with 67 indexes on it that average over 15 columns (numerous with >80% of all columns) each it is quite difficult to run down all the permutations that will eliminate the most indexes while providing the best bang-for-the-buck for the ones that you finally leave on the table!!! I was very proud of the outcome, and the client was most pleased. 😎

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

I could imagine. I have come into jobs where we had to replace a bunch of DTA indexes. Similar exercise. Pain in the butt - and very rewarding.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events