I have a problem to understand why I am getting 2 different execution plans based on whether clustered index is involved or not. It's happening in Azure managed instance, I have no possibility to test it on-prem.
The table with its clustered index goes as follows (it has approx 30+ additonal columns but I omitted those for clarity):
CREATE TABLE [schemaX].[TransactionJournal](
[Shi] [bigint] NOT NULL,
[Ohs] [bigint] NOT NULL,
[MatId] [bigint] NOT NULL,
[Dop] [smallint] NULL,
[OldShi] [bigint] NULL,
[OldOhs] [bigint] NULL,
[Qty] [decimal](18, 8) NULL,
[Price] [decimal](18, 8) NULL,
CONSTRAINT [PK_TransactionJournal] PRIMARY KEY CLUSTERED
(
[MatId] ASC,
[Shi] ASC,
[Ohs] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
)
GO
The nonclustered index has following definition:
CREATE NONCLUSTERED INDEX [IND_T_TransactionJournal01] ON [schemaX].[TransactionJournal]
(
[Shi] ASC,
[Ohs] ASC,
[MatId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
GO
Now, when I issue following query:
SET STATISTICS TIME, IO ON;
SELECT Shi, Ohs, MatId
FROM [schemaX].[TransactionJournal] WITH(INDEX = 1)
ORDER BY MatId ASC, Shi ASC, Ohs ASC
OFFSET 2000000 ROWS FETCH NEXT 2000 ROWS ONLY
, I get Clustered index scan (why not seek?) on TransactionJournal which has around 190GB and over 250M records. For 2000 rows, I am waiting 30 minutes.
With following query:
SELECT Shi, Ohs, MatId
FROM [schemaX].[TransactionJournal] WITH(INDEX = IND_T_TransactionJournal01)
ORDER BY Shi ASC, Ohs ASC,MatID ASC
OFFSET 2000000 ROWS FETCH NEXT 2000 ROWS ONLY
I get index seek on TransactionJournal and the 2000 rows come under 2 seconds.
I know I have different column order in these C and NC indexes and in the ORDER BY clauses of both queries (however ORDER BY column order is aligned with index column order in both cases), but I fail to understand why the difference is so huge (i.e. why I get scan in one case and seek in the other, although scan is not always bad and seek not always good).
EDIT: As per J.D.'s request, here are the XMLs representing the execution plans (for some reason, the pastetheplan.com website does not accept these XMLs as valid exec plans):
The one with nonclustered index seek: https://pastebin.com/ggQ7bVkv
The one with Clustered Index Scan: https://pastebin.com/8v4V7Ch2