0

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 columns 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 columns?

Improve this question
3
  • 4
    We should guess what your query is?
    – ypercubeᵀᴹ
    Commented Apr 17, 2013 at 8:50
  • and which version of sql server are you using?
    – KookieMonster
    Commented Apr 17, 2013 at 8:56
  • I am using SQL Server 2008 R2 - i will se if I can find the query linked to the index
    – MrMox
    Commented Apr 17, 2013 at 9:13

1 Answer 1

Reset to default
1

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.

Improve this answer
5
  • 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
    Commented Apr 17, 2013 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
    Commented Apr 17, 2013 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
    Commented Apr 17, 2013 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
    Commented Apr 17, 2013 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
    Commented Apr 17, 2013 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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