How to Speed Up SQL Server Paging with ROW_NUMBER, FORCESEEK and Hash Joins

In a production project the event‑log query page became extremely slow under high CPU load, taking up to four minutes to fetch just a few rows and the same time to load the second page. The original SQL used a double‑TOP pattern with NOT IN, which prevented the optimizer from using SARGable predicates and resulted in full scans.

Replacing TOP with ROW_NUMBER()

The author rewrote the query using a Common Table Expression (CTE) and ROW_NUMBER() OVER (ORDER BY AlarmTime DESC) AS RowNo. Adding WITH(FORCESEEK) forced index usage. Execution time dropped from 14 seconds to 5 seconds, showing the efficiency of ROW_NUMBER pagination.

"Tricking" the Optimizer

When a filter on AddrId caused the plan to use a non‑time index, the author changed the condition from AND b.AddrId IN ('02109000', …) to AND b.AddrId+'' IN (...). This simple expression prevented the optimizer from folding the predicate into the join, forcing the engine to apply the time index first and reducing execution time to under one second.

Handling Large Page Numbers

For deep pagination (e.g., page 19981‑20000) the query slowed to 30 seconds. The cause was excessive key lookups caused by the CTE processing all preceding rows. Solutions included:

Moving tables that are not part of the WHERE clause outside the CTE.

Using FORCESEEK hints on the main table.

Creating a temporary table ( tmpMgrObj) that pre‑filters AddrId values, then joining it inside the CTE.

These changes cut I/O dramatically and reduced the query to about 10 seconds.

Forcing Hash Joins

Another optimization forced a hash join for large pages, avoiding the need to sort all previous pages. The revised query wrapped the base eventlog selection in a sub‑query and applied ROW_NUMBER() only after the filter, then performed a hash join with the remaining tables. Execution time improved from 50 seconds to 12 seconds, with I/O remaining low.

Key Takeaways

1. ROW_NUMBER() pagination is the most efficient method for SQL Server 2005+.

2. Small tricks like appending an empty string to a column can force the optimizer to use the desired index.

3. Large page numbers can be mitigated by moving non‑filter tables out of the CTE, using temporary tables, or forcing hash joins.

4. Index hints such as FORCESEEK can override sub‑optimal plans.

5. Monitoring logical reads and physical I/O per table helps identify bottlenecks.