How to fine tune SQL Server query execution plan generation?

Question

I have a large "Deals" table (1.3 million rows) that needs to be displayed on a paginated grid in my application, the application also includes filters to help the user search through those rows, the generated SQL follows the structure below:

SELECT TOP 10 * 
FROM (
  SELECT 
    ROW_NUMBER() OVER(ORDER BY [DealID] DESC) AS RowNumber, * 
  FROM (
    select d.DealID, a.[Description] as Asset, 
    from Deals d
    inner join Assets a on d.AssetID = a.AssetID
  ) as Sorted
  where Asset like '%*my asset%' 
) as Sorted

My problem is with the execution plan generated for this query, because it's ordered by DealID, SQL Server is choosing the clustered index on DealID to execute this query and performs a clustered Index Scan on this table that has 1.3 million rows, but the query is also being filtered by Asset and there are only 171 rows that satisfy the filter, so it's much faster to use the non-clustered index on the asset first and then sort the resulting rows, I'm already able to fix this issue by adding the WITH INDEX(IX_Asset_ID)) hint into the query, but the problem is that since this is a generated query, this will add a lot of complexity to the code the generates this query.

So my question is, is there a way to get SQL Server to detect this situation without the hint? Maybe update statistics or something like that? Or even moving the hint to the end of the query would actually help since the middle of the query is actually a report written by the client.

--Edit--

As pointed out in the comments there are a few issues with the query, but those were actually created by the fact that I attempted to create a minimal reproducible example of the problem so I omitted the paging part of the query, structure below is a more complete version that should make more sense:

SELECT TOP @pageLength * FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY [DealID] DESC) AS RowNumber, * 
    FROM (
        SELECT d.DealID, a.[Description] AS Asset, FROM Deals d
        INNER JOIN Assets a on d.AssetID = a.AssetID
    ) AS Sorted
    WHERE Asset LIKE '%*my asset%'
) AS Paged 
WHERE RowNumber > @startRow
ORDER BY RowNumber
OPTION(RECOMPILE)

Answer 1

In the end the solution for me was to use the DISABLE_OPTIMIZER_ROWGOAL hint.

I think what's happening here is that SQL Server is being too optimistic about the query only requiring 10 rows and is scanning too much of the table because if thinks it won't take long to find the first 10 but in reality it's better to use the available indexes, adding the hint causes it to change the plan and the query runs quickly.

SELECT TOP @pageLength * FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY [DealID] DESC) AS RowNumber, * 
    FROM (
        SELECT d.DealID, a.[Description] AS Asset, FROM Deals d
        INNER JOIN Assets a on d.AssetID = a.AssetID
    ) AS Sorted
    WHERE Asset LIKE '%*my asset%'
) AS Paged 
WHERE RowNumber > @startRow
ORDER BY RowNumber
OPTION(RECOMPILE, USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'))

Answer 2

It's much better to page based off your clustered index key values, something like this

SELECT TOP (@pageLength)  
d.DealID, 
a.[Description] AS Asset, 
@startRowNumber + ROW_NUMBER() OVER(ORDER BY [DealID] DESC) AS RowNumber 
FROM Deals d
INNER JOIN Assets a on d.AssetID = a.AssetID
WHERE DealId > @startDealId
 and a.[Description] LIKE '%*my asset%'
ORDER BY DealId

This technique is sometimes called "keyset pagination", and it leverages the ordered index to allow SQL to seek directly to the next clustered index key after the last page. You track the rows based on the key value rather than the generated row number.