Simple SQL Server query takes too long-CodePudding

This query takes too long to produce a result.

The table contains about 150 million rows, and the query has no missing index.

select 
    Number, BankContacts_ID
from
    dbo.BankContactNumbers b with (nolock)
where 
    b.BankContacts_ID = 1234
order by 
    b.ID
    offset 0 rows fetch next 10 rows only

Here is the table structure:

create table BankContactNumbers
(
    ID int identity
       constraint PK_BankContactNumbers primary key nonclustered
                with (fillfactor = 70),
    BankContacts_ID  int not null,
    Number           char(11)
)

create index IX_BankContactNumbers_BankContacts_ID
    on BankContactNumbers (BankContacts_ID) include (ID, Number)

The execution plan is: https://www.brentozar.com/pastetheplan/?id=SJ8S1TiLo

CodePudding user response：

The existing index on BankContacts_ID is useful for the equality predicate but not the ORDER BY since the ID column is included rather than a key column.

Modify the existing index to add ID as the second key column. This way, BankContacts_ID can be used for the WHERE condition and rows returned in ID sequence for the ORDER BY without a sort in the query plan. Furthermore, the included Number column will allow the non-clustered index to cover the query.

CREATE INDEX IX_BankContactNumbers_BankContacts_ID ON BankContactNumbers (BankContacts_ID, ID) include (Number) WITH(DROP_EXISTING=ON);