OFFSET X ROWS FETCH NEXT 50 ROWS ONLY not using indexes

I have a query that is using OFFSET X ROWS FETCH NEXT 50 ROWS ONLY. If X is 160745 it uses the index, if X is 160746 it does not. There are more than 400000 records. With the index, it takes about 2 seconds to run, without the index it takes ~30 seconds.

We are using Azure S0

I know I can use query hints, however is there another option? Is there something in Azure that I can set to improve this? Any ideas would be appreciated.

**Note: assume all columns are no bigger than nvarchar(255) and only 6 columns.

Below is the query:

SELECT *
FROM [TableName]
ORDER BY [ColumnName] ASC
OFFSET 456450 ROWS FETCH NEXT 50 ROWS ONLY

It's hard to say without seeing query plans, the table structure, or the index definitions on the table. You should make sure that the index is covering the query. Other than that, you can encourage SQL Server to use the index even without an explicit hint to use the index. For example, if you are confident that the query should always use the index you could try setting a small rowgoal with a hint:

DECLARE @row_goal INT = 456450 

SELECT *
FROM [TableName]
ORDER BY [ColumnName] ASC
OFFSET @row_goal ROWS FETCH NEXT 50 ROWS ONLY
OPTION (OPTIMIZE FOR (@row_goal = 1));

If you want to avoid hints entirely I recommend rewriting the query. Using OFFSET with large values requires special care. It's easy to end up with a plan that scans lots of rows and does a lot of unnecessary work just to get the next 50.

If your application pages through the data one page at a time you can consider the technique described here. The basic idea is that the application keeps track of the last value that the end user has seen. When you need a new page you can pass down the last value as a filter:

SELECT *
FROM [TableName]
WHERE [ColumnName] > ?
ORDER BY [ColumnName] ASC
OFFSET 456450 ROWS FETCH NEXT 50 ROWS ONLY;

If you need to be able to show an arbitrary 50 pages you can use the approach detailed here by Aaron Bertrand. With CTEs you can encourage the query optimizer to scan through a narrow index until it reaches the first row that you want. The query will get slower as you increase the OFFSET but it could perform much better than what you're doing now. The basic idea would be like this:

;WITH pg AS 
(
  SELECT [key_column] 
  FROM [TableName]
  ORDER BY [ColumnName] ASC
  OFFSET @PageSize * (@PageNumber - 1) ROWS
  FETCH NEXT @PageSize ROWS ONLY
)
SELECT t.*
  FROM [TableName] AS t
  INNER JOIN pg ON t.[key_column] = pg.[key_column] -- or EXISTS
  ORDER BY [ColumnName] ASC;

Finally, Paul White also writes about another way to efficient page through data here that does not use OFFSET at all.

With the right indexes I expect that at least one of the techniques described in this answer will work well for you.

StackExchange Database Administrators Q#167870, answer score: 4