Why Does SQL Server Pagination Get Slower on Later Pages?

A practical comparison of OFFSET/FETCH and keyset pagination using execution plans and logical reads.

Noman Kamran

10 min read

·

Sep 6, 2026

--

Listen

Share

Press enter or click to view image in full size

Pagination looks simple.

An application needs 50 rows at a time, so we write a query using OFFSET and FETCH.

For the first few pages, everything may feel fast.

But then a user moves deeper into the result set — page 500, page 1,000, or page 2,000 — and suddenly the same query begins doing much more work.

The strange part is that the application still asks for only 50 rows.

So why should a later page cost more than the first one?

The answer is that SQL Server may still need to move through all the earlier rows before it can return the requested page.

In this article, we will reproduce that behavior with 200,000 rows, measure it using SET STATISTICS IO ON and Actual Execution Plans, and then compare OFFSET/FETCH with keyset pagination.

The goal is not to prove that OFFSET/FETCH is bad.

The goal is to understand when deep pagination becomes expensive — and when a different approach can avoid that work.

Set Up the Test Data

We will use one simple table with 200,000 rows.

Run this in a test database, not production.

DROP TABLE IF EXISTS dbo.PagingOrders;
GO

CREATE TABLE dbo.PagingOrders
(
    OrderID      INT IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_PagingOrders PRIMARY KEY,
    CustomerID   INT NOT NULL,
    OrderDate    DATETIME2(0) NOT NULL,
    Status       VARCHAR(20) NOT NULL,
    TotalAmount  DECIMAL(12,2) NOT NULL
);
GO

;WITH E1(N) AS
(
    SELECT 1
    FROM (VALUES
        (0),(0),(0),(0),(0),
        (0),(0),(0),(0),(0)
    ) AS V(N)
),
E2(N) AS
(
    SELECT 1
    FROM E1 A
    CROSS JOIN E1 B
),
E4(N) AS
(
    SELECT 1
    FROM E2 A
    CROSS JOIN E2 B
),
Numbers AS
(
    SELECT TOP (200000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
    FROM E4 A
    CROSS JOIN E2 B
)
INSERT INTO dbo.PagingOrders
(
    CustomerID,
    OrderDate,
    Status,
    TotalAmount
)
SELECT
    ((N - 1) % 1000) + 1,
    DATEADD(MINUTE, N, CAST('20200101' AS DATETIME2(0))),
    CASE N % 4
        WHEN 0 THEN 'Completed'
        WHEN 1 THEN 'Pending'
        WHEN 2 THEN 'Shipped'
        ELSE 'Cancelled'
    END,
    CAST((((N * 37) % 50000) + 500) / 100.0 AS DECIMAL(12,2))
FROM Numbers;
GO

The table has a clustered primary key on OrderID.

That detail matters because our queries will sort by OrderID, allowing us to focus specifically on the cost of pagination rather than introducing a separate sorting problem.

Test 1: The First Page

Start with the first 50 orders.

SET STATISTICS IO ON;

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount
FROM dbo.PagingOrders
ORDER BY OrderID
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY;

In my test, the query returned 50 rows with only:

3 logical reads

The Actual Execution Plan showed a Clustered Index Scan followed by a Top operator.

At first glance, seeing the word Scan may look suspicious.

But this is a good reminder that an Index Scan or Clustered Index Scan is not automatically a performance problem.

SQL Server already has the rows stored in OrderID order through the clustered index.

Because the query says:

OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

SQL Server can begin at the start of the index, read the required rows, and stop almost immediately.

Very little work is required.

Press enter or click to view image in full size

The important point is not the operator name.

It is how much work that operator actually performs.

Test 2: Move 100,000 Rows Deeper

Now change only the offset.

Suppose the application needs the 50 rows after the first 100,000:

SET STATISTICS IO ON;

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount
FROM dbo.PagingOrders
ORDER BY OrderID
OFFSET 100000 ROWS
FETCH NEXT 50 ROWS ONLY;

The query still returns exactly 50 rows.

But the work is very different.

In my test, logical reads increased from 3 to:

727 logical reads

The execution plan provided an even clearer picture.

The Clustered Index Scan processed: 100,050 rows

before the Top operator returned the final 50 rows.

Why 100,050?

Because SQL Server needs to move past the first 100,000 rows and then retrieve the next 50.

Conceptually, the request is:

Find the rows in order, ignore 100,000 of them, then give me 50.

The fact that the application does not receive those first 100,000 rows does not mean SQL Server can simply pretend they do not exist.

It still has to reach the correct position in the ordered result.

Press enter or click to view image in full size

This is the central problem with deep OFFSET/FETCH pagination.

As the offset grows, the amount of work required to reach the requested page can grow as well.

Why an Index Does Not Completely Solve This

You might look at this example and think:

Do I simply need an index on OrderID?

But we already have one.

OrderID is the clustered primary key.

SQL Server can read the rows in exactly the order requested by:

ORDER BY OrderID

There is no separate Sort operator in this example.

The problem is not that SQL Server cannot find the correct ordering.

The problem is the meaning of the offset itself.

An offset of 100,000 explicitly tells the database engine to move beyond 100,000 ordered rows before returning anything.

A perfectly useful index can help SQL Server access those rows efficiently, but it does not make the requirement to skip them disappear.

That distinction is important.

Sometimes query performance problems are caused by a missing or badly designed index.

Other times, the access pattern itself requires unnecessary work.

Test 3: Keyset Pagination

Instead of telling SQL Server how many rows to skip, another approach is to tell it where the previous page ended.

Suppose the last order from the previous page had:

OrderID = 100000

We can request the next 50 rows like this:

SET STATISTICS IO ON;

SELECT TOP (50)
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount
FROM dbo.PagingOrders
WHERE OrderID > 100000
ORDER BY OrderID;

This technique is commonly called keyset pagination, and it is also sometimes described as seek-based pagination.

The difference is subtle but important.

The OFFSET version says:

Start from the beginning and skip 100,000 rows.

The keyset version says:

Start after OrderID 100000 and give me the next 50 rows.

In my test, the query required only:
3 logical reads

The execution plan also changed from a Clustered Index Scan to a:

Clustered Index Seek

And instead of processing 100,050 rows, SQL Server located the starting position and returned the required 50 rows.

Press enter or click to view image in full size

This is the advantage of having a known starting key.

SQL Server can navigate directly to the relevant part of the index rather than repeatedly working through everything that appeared on earlier pages.

The Difference Is Bigger Than Scan Versus Seek

It would be easy to summarize the result as:

Scan bad, seek good.

But that would miss the more useful lesson.

Our first-page query also used a Clustered Index Scan, and it required only 3 logical reads.

That query was already efficient.

The real difference was the amount of data SQL Server had to process.

For the deep OFFSET query, SQL Server processed 100,050 rows to return 50.

For the keyset query, it could begin near the required key and retrieve only the rows needed for the next page.

So when examining execution plans, do not stop after reading the operator name.

Look at:

• logical reads
• rows processed
• rows returned
• predicates
• seek conditions
• how much work happens before the final result reaches the application

Two queries can return exactly the same number of rows while requiring dramatically different amounts of work.

Why This Matters in Real Applications

The effect becomes more noticeable as tables grow.

Imagine an API returning 25 or 50 records at a time from a table containing millions of rows.

Early pages may perform well because very few rows need to be skipped.

But requests such as:

page 5000
page 10000
page 20000

can force increasingly large amounts of work before the requested rows are returned.

That can mean:

• more logical reads
• more CPU work
• longer response times
• additional pressure on the buffer pool
• slower API endpoints under concurrent load

The application may still display only 50 records.

The database work required to reach those records is the part users do not see.

When Keyset Pagination Works Well

Keyset pagination is particularly useful when an interface moves sequentially through data.

Common examples include:

• infinite scrolling
• activity feeds
• APIs
• transaction history
• audit logs
• Next and Previous navigation
• large administrative lists

In those situations, the application normally knows the final key from the current page.

That value can become the starting point for the next request.

Instead of sending: page = 2001

the application can conceptually send: lastOrderId = 100000

The database then continues from that position.

When OFFSET/FETCH Still Makes Sense

Keyset pagination is not a universal replacement for OFFSET/FETCH.

OFFSET/FETCH remains simple and useful when:

• result sets are relatively small
• users normally browse only the first few pages
• deep-page performance is not important
• users need to jump directly to arbitrary page numbers

That last point is one of the biggest trade-offs.

If a user clicks: Go directly to page 347

OFFSET/FETCH naturally expresses that request.

Keyset pagination normally depends on knowing the key where the previous page ended.

It is therefore best suited to sequential navigation.

The correct choice depends on the application.

The lesson is not:

Never use OFFSET/FETCH.

It is:

Understand the cost of large offsets before relying on them for deep pagination.

Use a Stable Ordering Key

There is another important detail when implementing pagination in a real application.

The ordering needs to be deterministic.

Our example uses:

ORDER BY OrderID

and OrderID is unique.

That makes the order unambiguous.

Real applications often sort by something such as: CreatedDate

But many rows can have the same creation date.

In that situation, a unique tie-breaker such as OrderID should normally be included in the ordering.

For example, the conceptual order might be: CreatedDate, OrderID

The next page would then continue using both values.

Without deterministic ordering, rows with identical sort values can move unpredictably between pages.

This becomes even more important while rows are being inserted or updated at the same time users are browsing.

Another Advantage: Changing Data

Position-based pagination can also behave strangely when the underlying table changes between requests.

Suppose a user retrieves the first page.

Before the next request, new rows are inserted near the beginning of the ordered result.

An offset based purely on row position may now point to a slightly different location.

That can sometimes lead to rows appearing again or being skipped from the user’s perspective.

Keyset pagination is anchored to a known value from the previous result instead of only to a numerical position.

That often makes it a better fit for continuously changing feeds and API results.

It does not eliminate every pagination consistency problem, but it avoids repeatedly depending on the number of rows that happened to exist before the current page.

A Practical Troubleshooting Checklist

When later pages of a SQL Server query become slower, check a few things before immediately adding another index:

• Compare an early page with a deep page using SET STATISTICS IO ON.
• Capture the Actual Execution Plan.
• Check how many rows the plan processes compared with how many rows the query returns.
• Confirm whether the ORDER BY columns have an appropriate index.
• Look for an expensive Sort if the required ordering is not supported.
• Test whether keyset pagination fits the application’s navigation pattern.
• Make sure the ordering is deterministic.
• Use a unique tie-breaker when the main sort column is not unique.

Most importantly, test with realistic data volumes.

A pagination query that performs perfectly with 5,000 rows may behave very differently when the table contains several million.

Conclusion

OFFSET/FETCH is convenient, readable, and perfectly reasonable for many SQL Server applications.

But later pages can become increasingly expensive because SQL Server may still need to process all the ordered rows that come before the requested page.

In our test, the difference was easy to see.

The first page required very little work.

A page 100,000 rows deeper still returned only 50 rows, but SQL Server had to process 100,050 rows to reach them.

Keyset pagination changed the question.

Instead of asking SQL Server to count past all the earlier rows, we gave it the last known key and allowed the clustered index to seek directly to the next position.

That reduced the work dramatically.

So when pagination becomes slow, do not ask only:

“Do I have the right index?”

Also ask:

“Am I making SQL Server repeatedly walk past thousands of rows that the application does not even need?”

Sometimes the fastest rows to process are the ones you never ask SQL Server to skip in the first place.

References

[1] Microsoft, ORDER BY Clause (Transact-SQL), Microsoft Learn.
Documents SQL Server ordering and the OFFSET and FETCH arguments used for pagination.
ORDER BY Clause (Transact-SQL)

[2] Microsoft, TOP (Transact-SQL), Microsoft Learn.
Reference for limiting the number of rows returned by a query, as used in the keyset pagination example.
TOP (Transact-SQL)

[3] Microsoft, SET STATISTICS IO (Transact-SQL), Microsoft Learn.
Reference for measuring logical and physical I/O generated during query execution.
SET STATISTICS IO (Transact-SQL)

[4] Microsoft, Display an Actual Execution Plan, Microsoft Learn.
Explains how to capture the execution plan SQL Server actually used, including runtime information.
Display an Actual Execution Plan