I got the following recommendation on a table:

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?

  • 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

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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