Hcfyk

The query:

    SELECT Object1.Column1, Object2.Column2 AS Column3, Object2.Column4 AS Column5, 

Object3.Column6, Object3.Column7,Object1.Column8, Object1.Column9, 

Object1.Column10, Object1.Column11, Object1.Column12, Object1.Column13, 

Object1.Column14, Object1.Column15 as Column15, Object1.Column16, 

Object4.Column4 AS Column17, Object4.Column2 AS Column18, Object1.Column19,

 Object1.Column20, Object1.Column21, Object1.Column22, Object1.Column23, 

Object1.Column24, Object1.Column25, Object1.Column26, Object5.Column4, 

Object1.Column27, Object1.Column28, Object1.Column29, Object3.Column30, 

Object3.Column1 as Column31, Object3.Column32 as Column33, Object1.Column34 

as Column34, ? AS Column35 , Object3.Column36 as Column37 

FROM Object6 AS Object1 

INNER JOIN Object7 AS Object3 ON Object1.Column38 = Object3.Column1 

INNER JOIN Object8 AS Object2 ON Object3.Column30 = Object2.Column1 

LEFT JOIN Object9 AS Object4 ON Object1.Column16 = Object4.Column2 

LEFT JOIN Object10 AS Object5 ON Object1.Column9 = Object5.Column2 

WHERE Object2.Column1 <> ? AND Object1.Column8 = ? 

AND ( coalesce(Column16,?)= ? ) 

AND EXISTS (

         SELECT ? 

         FROM Object11 

         WHERE Column39 = ? 

         AND Column30 = Object3.Column30) 

ORDER BY Column7 desc

OFFSET ? ROWS FETCH FIRST ? ROWS ONLY

here is the query plan

I know that I should maybe add an index on this:

Database1.Schema1.Object7.Column30, Database1.Schema1.Object7.Column36, Database1.Schema1.Object7.Column6, Database1.Schema1.Object7.Column32

but one of this columns is a varchar 4000 and it can't be created cause of the big dimension of the field.

I noticed that it takes 25 second only if the rows returned are fewer than the fetch first number

The execution plan accesses Object7 first using a non covering index in Column7 order. It then does some key lookups on that table and nested loops joins against the other tables with the final joined resulting arriving at the TOP operator still ordered by Column7.

Once this has received enough rows to satisfy the OFFSET ... FETCH it can stop requesting any more rows from downstream operators. SQL Server estimates that it will only need to read 2419 rows from the initial index on Object7.Column7 before this point is arrived at.

This estimate is not at all correct. In fact it ends up reading the entirety of Object7 and likely runs out of rows before the OFFSET ... FETCH is satisfied.

The semi join on Object11 reduces the rowcount by almost half but the killer is the join on Object6 and predicate on the same table. Together these reduce the 9,753,116 rows coming out of the semijoin to 2.

You could try spending some time looking at statistics on the tables involved to try and get the cardinality estimates from these joins to be more accurate or alternatively you could add OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL') ) so the plan is costed without the assumption that it can stop early due to the OFFSET ... FETCH - this will certainly give you a different plan.

If you can add an index on Object11, Column39 + Column30, and an index on Object7, Column30, with other fields from Object7 in the INCLUDE portion of the CREATE INDEX statement for Object 7, you should have a large increase in performance. This is the vast majority of the resource expenditure involved in this query.

Based on the plan's XML, these would appear to be close to optimal indexes for this query:

CREATE INDEX Idx_Object11_Column39_Column30

ON Object11(Column39_Column30)



CREATE INDEX Idx_Object7_Column30_Column1_Includes

ON Object7 (Column30, Column1)

INCLUDE (Column7, Column36, Column6, Column2)