Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong?

9 votes

1 answer

1659 views

sql-server sql-server-2008-r2 index index-tuning index-design

                          I am trying to improve this (sub-) query being part of a larger query:

    select SUM(isnull(IP.Q, 0)) as Q, 
            IP.OPID 
        from IP
            inner join I
            on I.ID = IP.IID
        where 
            IP.Deleted=0 and
            (I.Status > 0 AND I.Status  https://www.mssqltips.com/sqlservertutorial/3208/use-where-join-orderby-select-column-order-when-creating-indexes/ 

 to address the key lookups and create an index seek: 

    CREATE NONCLUSTERED INDEX [IX_I_Status_1]
        ON [dbo].[Invoice]([Status], [ID])

The extracted query immediately used this index. But the original larger query it is part of, didn't. It did not even use it when I forced it to using WITH(INDEX(IX_I_Status_1)). 

After a while I decided to try another new index and changed to order of the indexed columns:

    CREATE NONCLUSTERED INDEX [IX_I_Status_2]
        ON [dbo].[Invoice]([ID], [Status])

WOHA! This index was used by the extracted query and also by the larger query!

Then I compared the extracted queries IO statistics by forcing it to use [IX_I_Status_1] and [IX_I_Status_2]:
 	
Results [IX_I_Status_1]:

    Table 'I'. Scan count 5, logical reads 636, physical reads 16, read-ahead reads 574
    Table 'IP'. Scan count 5, logical reads 1134, physical reads 11, read-ahead reads 1040
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0

Results [IX_I_Status_2]:

    Table 'I'. Scan count 1, logical reads 615, physical reads 6, read-ahead reads 631
    Table 'IP'. Scan count 1, logical reads 1024, physical reads 5, read-ahead reads 1040
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,  read-ahead reads 0

OK, I could understand that the mega-large-monster query maybe just too complex to make SQL server catch the ideal execution plan and may miss my new index. 
But I don't understand why the index [IX_I_Status_2] seems to be more suitable and more efficient for the query. 

Since the query first of all filters table I by column STATUS and then joins with table IP, I don't understand why [IX_I_Status_2] is better and used by Sql Server instead of [IX_I_Status_1]?
                        

Asked by Magier (4827 rep)

May 25, 2016, 09:46 AM
Last activity: May 25, 2016, 06:32 PM

Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong?

Related Questions