0

I have the following table in my database:

create table [dbo].[tb_StatusLog](
[RequestID] [int] not null,
[statusID] [int] not null,
[startTime] [datetime] not null,
[endTime] [datetime] null,
)

with the following non-clustered index

create nonclusteredindex [index1] on [dbo].[tb_StatusLog]
(
requestID asc, 
statusID asc
)
include ([startTime])

and the following table-valued function fn_getTable

select requestID, max(startTime)
from tb_StatusLog
where statusID=2
group by requestID, statusID

after running profiler trace and running the results by db engine tuning advisor, it proposed that I create the following index (which is the same index I have but with the key columns reversed):

create nonclusteredindex [index2] on [dbo].[tb_StatusLog]
(
statusID asc, 
requestID asc
)
include ([startTime])

Now, when running fn_getTable, the execution plan uses index2 instead of index1 and the performance improved.

Why is that?

3

Because you have an equality predicate on where statusID=2.

With the reversed order it is able to seek into exactly the rows matching the status and these can then feed into a stream aggregate to do the grouping.

Your original index supports the group by requestID, statusID but not the WHERE - meaning the whole index will need to be scanned and only rows matching the statusID=2 retained.

| improve this answer | |
  • which index does the group by use then? – Yevgeni Grinberg Jun 1 '18 at 15:39
2

Somewhat simplified, but you can think of a composite index as if rows are ordered first by the first column, second by the second column and so forth. Your first example would be:

requestID     statusID
----------------------
-> A             1
|  A             2
v  A             3
   B             1
   B             2
x  B             4

To find rows with statusId=2 there is no way to tell where to start, the whole index has to be scanned. Compare with:

statusID      requestID
----------------------
   1             A
   1             B
-> 2             A
v  2             B
x  3             A
   4             B

The DBMS can start with (2,A), read another entry (2,B) and be done with it.

| improve this answer | |

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.