SQL Server简单查询执行过慢，请求排查优化方案

问题背景 #

涉及表dbo.BankContactNumbers（约1.5亿行数据），不存在缺失索引，但以下分页查询执行耗时过长，无法快速返回结果：

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

表结构与现有索引 #

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)

问题根源 #

现有索引IX_BankContactNumbers_BankContacts_ID仅按BankContacts_ID排序，虽然包含了ID和Number，但同一BankContacts_ID分组内的ID是无序的。查询要求按ID排序后取前10行，数据库必须先取出所有BankContacts_ID=1234的行，再对这些行做全量排序——如果该分组下的行数较多，排序操作会占用大量CPU和IO资源，直接导致查询延迟。

优化方案 #

方案1：创建覆盖排序需求的复合索引（最优解） #

创建以BankContacts_ID为第一键、ID为第二键的复合索引，同时包含Number：

create index IX_BankContactNumbers_BankContacts_ID_ID
on dbo.BankContactNumbers (BankContacts_ID, ID) include (Number)
-- 若无需保留原索引，可添加 with (drop_existing = on) 替换原索引

这个索引的核心优势：

• 同一BankContacts_ID分组内的ID天然有序，查询时直接扫描匹配的索引段，取前10行即可，完全避免排序操作。
• 包含Number列，满足覆盖索引要求，无需回表查询。

方案2：调整主键为聚集索引（业务允许时可选） #

当前主键为非聚集索引，若业务允许，可将主键改为聚集索引（利用ID的自增性保证有序）：

-- 删除原非聚集主键
alter table dbo.BankContactNumbers
drop constraint PK_BankContactNumbers

-- 创建聚集主键
alter table dbo.BankContactNumbers
add constraint PK_BankContactNumbers primary key clustered (ID) with (fillfactor = 70)

调整后，原索引仍可保留。查询时数据库会先通过原索引找到所有匹配的ID，再利用聚集索引的有序性直接取前10行。但这种方式需要两次索引查找，效率略逊于方案1，适合无法新增索引的场景。

临时优化方案（无需修改索引） #

如果暂时无法调整索引，可通过子查询先获取前10个符合条件的ID，再关联取数据：

select bc.Number, bc.BankContacts_ID
from (
    select top 10 ID
    from dbo.BankContactNumbers with (nolock)
    where BankContacts_ID = 1234
    order by ID
) as sub
join dbo.BankContactNumbers bc with (nolock) on sub.ID = bc.ID

这种方式仍会触发排序，但仅对ID列排序，开销比原查询小，适合临时应急。

内容的提问来源于stack exchange，提问作者SnowStorm