Why is the cost for the Sort operator in this execution plan so high?-CodePudding

The Sort operator says its only got 100 rows to sort. How could that possibly be more expensive than reading 1.9 million rows? I must be reading this wrong or misunderstanding something.

Also, how is the Estimated Number of Rows Per Execution in the Sort operator only 100? If the Index Seek operator estimates the Number of Rows Per Execution to be 1.9 million, how does only 100 rows get piped over to the Sort operator?

Here is the query:

    DECLARE @PageIndex INT = 1000;
    DECLARE @PageCount INT  = 1000;

    SELECT  ID
    FROM    dbo.Table1
    WHERE DateCreated >= '2021-10-27'
            AND
        DateCreated < '2021-10-28'
    ORDER BY ID
        OFFSET @PageIndex * @PageCount ROWS FETCH NEXT @PageCount ROWS ONLY

CodePudding user response：

The Sort operator (as opposed to "Top N Sort") will sort the entirety of its input in its open method before returning any rows.

SQL Server estimates that the seek will output 1.9 million rows that go into the sort.

The costing is therefore for sorting 1.9 million rows.

You are doing

OFFSET 1000000 ROWS FETCH NEXT 1000 ROWS ONLY

The actual output rows from the sort will be at least 1,001,000 (maybe more in a parallel plan) and the TOP operator discards the first million for the offset and then stops requesting rows after it has received the 1000 to be returned.

The estimate of 100 is just a guess as SQL Server has no idea what the value of the variables will be at runtime when the plan is compiled.