A Real life performance issue due to Fetch Next N rows in MS SQL Server

A couple of days ago, I was asked to improve the performance of a particular SQL query which took, in average, 10 second to complete. Here’s below the corresponding SQL and It’s  plan taken from a MS SQL Server 2017 instance running under Windows Server 2016 Standart.

SELECT [x].*
FROM [NotA] AS [x]
WHERE [x].[IsDeleted] = 0
ORDER BY [x].[Id]
OFFSET 5144530 ROWS FETCH NEXT 10 ROWS ONLY

NotA table has primary key Clustered Index on ID column. From the SQL Plan statistics we can observe that more then 5 million Index rows were scanned. But why almost all Index rows were scanned, despite that we read just 10 rows from the Table. Each Index leaf node has address for the next leaf node, the next one for the next leaf node and etc. The more value of OFFSET parameter  the more Index rows were scanned. Let’s see the SQL Plan if we set smaller value for OFFSET parameter.

SELECT [x].*
FROM [NotA] AS [x]
WHERE [x].[IsDeleted] = 0
ORDER BY [x].[Id]
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

Just 20 rows were scanned and SQL takes less then 1 second to complete. But what should we do If we need to set big value for OFFSET parameter.

Solution for the situation will be using the exact value by BETWEEN statement in the query.  The SQL query and It’s Plan shown below.

SELECT [x].*
FROM [NotA] AS [x]
WHERE [x].[IsDeleted] = 0 and
[x].[Id] between 5144530 and 5144530 + 10
ORDER BY [x].[Id]
--OFFSET 5144530 ROWS FETCH NEXT 10 ROWS ONLY 

SQL query takes less then 1 second to complete.

In case of data is not sequential using approach like 5144530 and 5144530 + 10 is not correct of course. In that case you should use approach like below:

 
SELECT [x].* FROM [NotA] AS [x] WHERE [x].[IsDeleted] = 0 and [x].[Id] in 
(SELECT [x].id FROM [NotA] AS [x] WHERE [x].[IsDeleted] = 0 ORDER BY [x].[Id] 
OFFSET 5144530 ROWS FETCH NEXT 10 ROWS ONLY)

That’s all thanks for attention 🙂