0

We have a SELECT query which is taking time (around 90 secs) to execute. It has OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY in it.

When we remove OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY, it completes in 4-5 secs.

On observing execution plan for both the queries, it is totally different than one another. Without OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY it returns 154 rows.

I am puzzled on how using OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY can degrade performance and cause different execution plan? Since it limits the records that should be returned, I thought it will improve the performance.

Does anyone has any inputs on how to proceed further?

sachin-SQLServernewbiee
  • 473
  • 6
  • 18

1 Answers1

2

Using OFFSET...FETCH will likely set a "row goal" in the execution plan. Row goals are often a good thing, as SQL Server can choose a different plan based on the knowledge that it only needs a few rows. See Paul White's series on row goals for lots of additional detail on that subject, which starts here: Setting and Identifying Row Goals in Execution Plans

Unfortunately, when there isn't good indexing in place, or the data being requested just doesn't match many of the rows in the tables involved, the introduction of a row goal can wreak havoc on performance. This is because all, or most, of the table might need to be read just to get those 25 rows.

There's a great example of this on Erik Darling's blog: When Data Isn’t There

Your situation sounds like it matches this one closely. If there are only 154 matches in the entire table (or set of tables), then without the row goal the optimizer might choose a parallel scan of the tables, using hash joins to bring the result set together. With the row goal, the optimizer is likelier to choose a serial plan, with nested loops joins, which generally won't perform as well if a lot of data needs to be read.

Josh Darnell
  • 30,133
  • 5
  • 70
  • 124