I have two tables (in an Azure SQL database):

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
)

and

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
)

The Action table contains about 15M actions, regarding about 1.5M unique resources. The ResourceScope table contains about 10K rows with unique resources and one of a couple of different scopes.

An API requires paging through the Action table with the following query, where both and are variable, but the pages are always the same size.

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

When paging through the data for a certain scope about 95% of all queries take about 15ms, but the remaining 5% take anywhere between 500ms and 4000ms. The slow queries use always exactly the same id/scope combination, where I can narrow down to some exact IDs that make the query slow.

The ids for which the query is slow have the exact same query plan as the fast ones, but the slow ones perform a huge amount of clustered index seeks on the ResourceScope primary key, where the fast ones only perform a couple of executions (mostly around 100 executions) of that clustered index seek on that index.

Example of a plan for a slow id:
https://www.brentozar.com/pastetheplan/?id=ryScGHekR

Example of a plan for a fast id:
https://www.brentozar.com/pastetheplan/?id=B1c6MrxyA

When I add an indexed view for a specific scope, like below, and page trough that view, the 5% slow queries disappear.

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);

Paging query on view:

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

Since the scopes can be dynamically added (but are not expected to grow beyond ~25 unique scopes), I would prefer not to make indexed views for every scope.

Two questions:

1. What causes the slow queries
2. Are there any indexes or other things I could add/change to allow for the same or similar query performance without the use of the indexed views?