Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I got the following recommendation on a table:

CREATE NONCLUSTERED INDEX 
    [_dta_index_MyTable_6_44943632__K16_K52_K8_K9_K15_1_2_4_5_6_7_10_11_12_13_14_17_18_19_20_21_22_23_24_25_26_27_28_29_30_31_] 
ON [dbo].[MyTable] 
(
    [ColumnA] ASC,
    [ColumnB] ASC,
    [ColumnC] ASC
)

However, I am a bit surprised about the order of the fields in the recommendation. I would expect the result to be ColumnB, ColumnA, ColumnC.

Can I rely on the recommendation or should I decide the order of the fields?

share|improve this question
4  
We should guess what your query is? –  ypercube Apr 17 '13 at 8:50
    
and which version of sql server are you using? –  KookieMonster Apr 17 '13 at 8:56
    
I am using SQL Server 2008 R2 - i will se if I can find the query linked to the index –  MrMox Apr 17 '13 at 9:13
add comment

1 Answer

up vote 1 down vote accepted

You need to provide more info, specifically why do you expect the order you expect. An index on (A, B, C) is completely different from an index on (B, A, C) and one cannot replace the other. The DTA may be wrong sometimes, but is way more often right rather than wrong.

share|improve this answer
    
The table is a transaction table and the recommendation is something like (TransactionStatus, Company, ReferenceId) The Company column holds only to different values (Company1 and Company2), the TransactionStatus has 5 different values, and the ReferenceId points to another table. I would expect the order to be TransactionStatus, ReferenceId, Company –  MrMox Apr 17 '13 at 9:17
1  
You still don't explain why do you expect that. From the little info you provide, I woudl expect the order to be the one given by DTA, as low selectivity keys have little value after high selectivity keys. –  Remus Rusanu Apr 17 '13 at 9:25
    
Well, I don't know if I'd go so far as to say the DTA is right more often than wrong, unless you're talking specifically about the order of the key columns. It can be overly aggressive about INCLUDE columns, will often suggest redundant almost-duplicate indexes, and can be totally wrong if your input is wrong (e.g. small portion of workload). –  Aaron Bertrand Apr 17 '13 at 13:18
1  
@AaronBertrand: I'm talking about DTA in general. Remember that you are in a position where you're called to help when things fail so you will always see a worse picture than average user sees. The countless times DTA works and gives good advice will tend to go unnoticed... –  Remus Rusanu Apr 17 '13 at 13:26
    
I've seen it do the things I've mentioned plenty of times, and not just in cases where I'm called to help when things fail. My point was simply this: use DTA with caution. –  Aaron Bertrand Apr 17 '13 at 13:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.