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