Azure SQL数据库分页查询偶发慢查询的原因排查与优化咨询

我在Azure SQL数据库中有两张表：

Action表结构 #

CREATE TABLE [dbo].[Action]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Action] [varchar](7) NOT NULL,
    [Resource] [varchar](16) NOT NULL,
    [Timestamp] [datetime] NOT NULL
)

ALTER TABLE [dbo].[Action] 
    ADD CONSTRAINT [PK_Action] 
        PRIMARY KEY CLUSTERED ([ID] ASC)

ResourceScope表结构 #

CREATE TABLE [dbo].[ResourceScope]
(
    [Resource] [varchar](16) NOT NULL,
    [Scope] [varchar](100) NOT NULL
)

ALTER TABLE [dbo].[ResourceScope] 
    ADD CONSTRAINT [PK_ResourceScope] 
        PRIMARY KEY CLUSTERED ([Scope] ASC, [Resource] ASC)

Action表包含约1500万条操作记录，涉及约150万个唯一Resource；ResourceScope表包含约1万行数据，记录唯一Resource与其所属Scope的对应关系。

分页查询场景 #

某API通过以下分页查询遍历Action表，其中<id>和<scope>为变量，每页大小固定：

SELECT TOP 101
    [Action].[ID], 
    [Action].[Action], 
    [Action].[Resource], 
    [Action].[Timestamp]
FROM 
    [Action] 
JOIN 
    [ResourceScope] ON [ResourceScope].[Resource] = [Action].[Resource] 
WHERE 
    [Action].[ID] <= <id>
    AND [ResourceScope].[Scope] = <scope>
ORDER BY 
    [Action].[ID] DESC

慢查询现象 #

针对某一Scope进行分页时，约95%的查询耗时约15ms，但剩余5%的特定id/scope组合查询耗时在500ms至4000ms之间。慢查询与快查询的执行计划完全一致，但慢查询会对ResourceScope的主键执行大量聚集索引seek操作，而快查询仅执行约100次该操作。

索引视图临时解决方案 #

当为特定Scope创建如下索引视图并基于该视图分页时，慢查询现象消失：

CREATE VIEW [dbo].[DemoSetAction]
WITH SCHEMABINDING
AS  
    SELECT
        [dbo].[Action].[ID], 
        [dbo].[Action].[Action], 
        [dbo].[Action].[Resource], 
        [dbo].[Action].[Timestamp]
    FROM 
        [dbo].[Action] 
    JOIN 
        [dbo].[ResourceScope] ON [dbo].[ResourceScope].[Resource] = dbo.[Action].[Resource] 
    WHERE 
        [dbo].[ResourceScope].[Scope] = 'demo-set'

CREATE UNIQUE CLUSTERED INDEX IDX_DemoSet ON [dbo].[DemoSetAction] (ID);

视图分页查询语句：

SELECT TOP 101
    [DemoSetAction].[ID], 
    [DemoSetAction].[Action], 
    [DemoSetAction].[Resource], 
    [DemoSetAction].[Timestamp]
FROM 
    [DemoSetAction] 
WHERE 
    [Action].[ID] <= <id>   
ORDER BY 
    [Action].[ID] DESC

由于Scope可动态添加（预计不超过25个），我希望不为每个Scope创建索引视图，现咨询两个问题：

1. 慢查询的触发原因是什么？
2. 有无无需创建索引视图即可实现同等性能的索引或其他优化方案？

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