Change of index key columns order affects performance 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?
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.