1

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

1 Answer 1

0

Why are you forcing the index it uses with an index hint instead of letting the optimizer decide that for itself? When you force it to use the clustered index, it appears to be hitting the tipping point where it thinks a scan will be more efficient.

My guess is this is because your clustered index key definition is not ordering the columns the same way as your ORDER BY clause in your first query. If you try re-writing your clustered index to match the ORDER BY clause or re-write your query so the ORDER BY clause matches the clustered index definition like the below, does it result in a Clustered Index Seek operation now?

SELECT Shi, Ohs, MatId
FROM [schemaX].[TransactionJournal]  WITH(INDEX = 1)
ORDER BY Shi ASC, Ohs ASC, MatId ASC
OFFSET 2000000 ROWS FETCH NEXT 2000 ROWS ONLY
5
  • Forcing indexes was one of the attempts, the behaviour without index hints (queries differ only by ORDER BY columns) is the same - the one hitting clustered index does the whole clustered index scan (whole table), the one hittting NC index does nonclustered index scan (it stops once it finds the 2000 records) + clustered index seek (acting as a key lookup to fetch columns not coverd by NC index). If I align the column order in ORDER by clause with key order in indexes, behaviour is stil the same.
    – Sranda
    Apr 13, 2022 at 19:00
  • 1
    @Sranda Can you please upload the actual execution plan to Paste The Plan for the clustered index scan query with the ORDER BY clause columns ordered the same as the clustered index key, and add it to your original post please?
    – J.D.
    Apr 13, 2022 at 19:06
  • As trivial and foolproof as pasting the plan is, I am getting "The supplied XML did not parse correctly. Are you sure you have a valid query plan XML text?" - I am not tampering with the copy-pasted XML in any way. Would it help you at least to include a screenshot of execution plan to my original post?
    – Sranda
    Apr 14, 2022 at 11:33
  • 1
    @Sranda It may help, though most of the information is encoded in the plan and not displayed in a screenshot. It's only available when you highlight over it. I find a lot of times lately people have been botching their plan by somehow including special characters in the names of objects in it. If you can upload the plan XML to an online text editor somewhere like PasteBin.com, I can try to clean up the broken part of the XML and can upload it to Paste The Plan for you.
    – J.D.
    Apr 14, 2022 at 11:40
  • 1
    My original post has been edited and the PasteBin.com URLs added. In the original, out-of-SSMS XML, there was some garbage in the T-SQL query part of the XML (similar to how you encode special characters in URL, e.g. %20 represents space). That I deleted in the XML and it was not accepted by pastetheplan.com anyway. Let me know if you've been successful feeding that XML to pastetheplan.com. Thank you for the help.
    – Sranda
    Apr 15, 2022 at 7:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.