Can OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY cause to create totally different execution plan and also cause query to run slow?

Question

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?

• GMassDBA: Would you mind sharing actual plans for both executions (with and without the offset...fetch) please?
• rois: Seems like problem with row goal. Look into this question(and answers): dba.stackexchange.com/questions/24832/…

Answer 1

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.

• sachin-SQLServernewbiee: I am going through the articles. Just wanted to know whether OFFSET will also impact the decision of optimizer on choosing index as well? Because without OFFSET it is doing SEEK on NCI. But with OFFSET it is not using that Index and rather going for Scan on NC Primary key.