Consulta de tabela do SQL Server com ajuste de desempenho de paginação, entenda a solução atual

conforme indicado no título, inicio um ajuste de desempenho de uma consulta de tabela com paginação gerada por um programa legado que usa Linq To SQL como ORM.

Eu encontrei este recurso no qual é altamente recomendado classificar a tabela antes da paginação: https://rimdev.io/optimizing-linq-sql-skip-take/

Então eu segui a sugestão fornecida e experimentei uma enorme diferença. Está claro para mim que está um pouco relacionado a como row_number é calculado, mas não está claro para mim exatamente o que aconteceu e por que há tanta diferença entre as duas consultas.

Consulta lenta original (conjunto de dados de ~7K elementos, leva ~3s):

SELECT [t7].[ID], [t7].[ID_BRAND], [t7].[CODE], [t7].[CODFOR], [t7].[COD_ALT01], [t7].[COD_ALT02], [t7].[COD_ALT03], [t7].[ID_UOM], [t7].[IS_ACTIVE], [t7].[_ATTRIBUTES] AS [_ATTRIBUTES], [t7].[_DOCUMENTS] AS [_DOCUMENTS], [t7].[_SEO] AS [_SEO], [t7].[_TRANSLATIONS] AS [_TRANSLATIONS], [t7].[_TAGS] AS [_TAGS], [t7].[_NOTES] AS [_NOTES], [t7].[_METADATA] AS [_METADATA], [t7].[IS_B2B], [t7].[IS_B2C], [t7].[IS_PROMO], [t7].[IS_NEWS], [t7].[CAN_BE_RETURNED], [t7].[IS_SHIPPABLE], [t7].[HAS_SHIPPING_COSTS], [t7].[IS_PURCHEASABLE], [t7].[test], [t7].[ID2], [t7].[CODE2], [t7].[BUSINESS_NAME], [t7].[NAME], [t7].[PHONE_01], [t7].[PHONE_02], [t7].[PHONE_03], [t7].[FAX_01], [t7].[FAX_02], [t7].[COUNTRY_01], [t7].[CITY_01], [t7].[ADDRESS_01], [t7].[COUNTRY_02], [t7].[CITY_02], [t7].[ADDRESS_02], [t7].[EMAIL_01], [t7].[EMAIL_02], [t7].[PEC], [t7].[SITE_01], [t7].[SITE_02], [t7].[SITE_03], [t7].[SITE_04], [t7].[VAT_NUMBER], [t7].[SORT], [t7].[GROUPID_01], [t7].[IS_GROUPLEADER_01], [t7].[GROUPID_02], [t7].[IS_GROUPLEADER_02],[t7].[IS_ACTIVE2], [t7].[[_DOCUMENTS]]2] AS [_DOCUMENTS2], [t7].[[_SEO]]2] AS [_SEO2], [t7].[[_METADATA]]2] AS [_METADATA2], [t7].[test2], [t7].[ID3], [t7].[CODE3], [t7].[[_TRANSLATIONS]]2] AS [_TRANSLATIONS2], [t7].[[_METADATA]]3] AS [_METADATA3], [t7].[test3], [t7].[ID4], [t7].[ID_LINE], [t7].[ID_GROUP], [t7].[ID_CLASS], [t7].[ID_FAM], [t7].[ID_ARTICLE]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ID], [t0].[ID_BRAND], [t0].[CODE], [t0].[CODFOR], [t0].[COD_ALT01], [t0].[COD_ALT02], [t0].[COD_ALT03], [t0].[ID_UOM], [t0].[IS_ACTIVE], [t0].[_ATTRIBUTES], [t0].[_DOCUMENTS], [t0].[_SEO], [t0].[_TRANSLATIONS], [t0].[_TAGS], [t0].[_NOTES], [t0].[_METADATA], [t0].[IS_B2B], [t0].[IS_B2C], [t0].[IS_PROMO], [t0].[IS_NEWS], [t0].[CAN_BE_RETURNED], [t0].[IS_SHIPPABLE], [t0].[HAS_SHIPPING_COSTS], [t0].[IS_PURCHEASABLE], [t2].[test], [t2].[ID], [t2].[CODE], [t2].[BUSINESS_NAME], [t2].[NAME], [t2].[PHONE_01], [t2].[PHONE_02], [t2].[PHONE_03], [t2].[FAX_01], [t2].[FAX_02], [t2].[COUNTRY_01], [t2].[CITY_01], [t2].[ADDRESS_01], [t2].[COUNTRY_02], [t2].[CITY_02], [t2].[ADDRESS_02], [t2].[EMAIL_01], [t2].[EMAIL_02], [t2].[PEC], [t2].[SITE_01], [t2].[SITE_02], [t2].[SITE_03], [t2].[SITE_04], [t2].[VAT_NUMBER], [t2].[SORT], [t2].[GROUPID_01], [t2].[IS_GROUPLEADER_01], [t2].[GROUPID_02], [t2].[IS_GROUPLEADER_02], [t2].[IS_ACTIVE], [t2].[_DOCUMENTS], [t2].[_SEO], [t2].[_METADATA], [t4].[test], [t4].[ID], [t4].[CODE], [t4].[_TRANSLATIONS], [t4].[_METADATA], [t6].[test], [t6].[ID], [t6].[ID_LINE], [t6].[ID_GROUP], [t6].[ID_CLASS], [t6].[ID_FAM], [t6].[ID_ARTICLE]) AS [ROW_NUMBER], [t0].[ID], [t0].[ID_BRAND], [t0].[CODE], [t0].[CODFOR], [t0].[COD_ALT01], [t0].[COD_ALT02], [t0].[COD_ALT03], [t0].[ID_UOM], [t0].[IS_ACTIVE], [t0].[_ATTRIBUTES], [t0].[_DOCUMENTS], [t0].[_SEO], [t0].[_TRANSLATIONS], [t0].[_TAGS], [t0].[_NOTES], [t0].[_METADATA], [t0].[IS_B2B], [t0].[IS_B2C], [t0].[IS_PROMO], [t0].[IS_NEWS], [t0].[CAN_BE_RETURNED], [t0].[IS_SHIPPABLE], [t0].[HAS_SHIPPING_COSTS], [t0].[IS_PURCHEASABLE], [t2].[test], [t2].[ID] AS [ID2], [t2].[CODE] AS [CODE2], [t2].[BUSINESS_NAME], [t2].[NAME], [t2].[PHONE_01], [t2].[PHONE_02], [t2].[PHONE_03], [t2].[FAX_01], [t2].[FAX_02], [t2].[COUNTRY_01], [t2].[CITY_01], [t2].[ADDRESS_01], [t2].[COUNTRY_02], [t2].[CITY_02], [t2].[ADDRESS_02], [t2].[EMAIL_01], [t2].[EMAIL_02], [t2].[PEC], [t2].[SITE_01], [t2].[SITE_02], [t2].[SITE_03], [t2].[SITE_04], [t2].[VAT_NUMBER], [t2].[SORT], [t2].[GROUPID_01], [t2].[IS_GROUPLEADER_01], [t2].[GROUPID_02], [t2].[IS_GROUPLEADER_02], [t2].[IS_ACTIVE] AS [IS_ACTIVE2], [t2].[_DOCUMENTS] AS [[_DOCUMENTS]]2], [t2].[_SEO] AS [[_SEO]]2], [t2].[_METADATA] AS [[_METADATA]]2], [t4].[test] AS [test2], [t4].[ID] AS [ID3], [t4].[CODE] AS [CODE3], [t4].[_TRANSLATIONS] AS [[_TRANSLATIONS]]2], [t4].[_METADATA] AS [[_METADATA]]3], [t6].[test] AS [test3], [t6].[ID] AS [ID4], [t6].[ID_LINE], [t6].[ID_GROUP], [t6].[ID_CLASS], [t6].[ID_FAM], [t6].[ID_ARTICLE]
    FROM [dbo].[tbl_ana_Articles] AS [t0]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t1].[ID], [t1].[CODE], [t1].[BUSINESS_NAME], [t1].[NAME], [t1].[PHONE_01], [t1].[PHONE_02], [t1].[PHONE_03], [t1].[FAX_01], [t1].[FAX_02], [t1].[COUNTRY_01], [t1].[CITY_01], [t1].[ADDRESS_01], [t1].[COUNTRY_02], [t1].[CITY_02], [t1].[ADDRESS_02], [t1].[EMAIL_01], [t1].[EMAIL_02], [t1].[PEC], [t1].[SITE_01], [t1].[SITE_02], [t1].[SITE_03], [t1].[SITE_04], [t1].[VAT_NUMBER], [t1].[SORT], [t1].[GROUPID_01], [t1].[IS_GROUPLEADER_01], [t1].[GROUPID_02], [t1].[IS_GROUPLEADER_02], [t1].[IS_ACTIVE], [t1].[_DOCUMENTS], [t1].[_SEO], [t1].[_METADATA]
        FROM [dbo].[tbl_ana_Brands] AS [t1]
        ) AS [t2] ON [t2].[ID] = [t0].[ID_BRAND]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t3].[ID], [t3].[CODE], [t3].[_TRANSLATIONS], [t3].[_METADATA]
        FROM [dbo].[tbl_ana_UoMs] AS [t3]
        ) AS [t4] ON [t4].[ID] = [t0].[ID_UOM]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t5].[ID], [t5].[ID_LINE], [t5].[ID_GROUP], [t5].[ID_CLASS], [t5].[ID_FAM], [t5].[ID_ARTICLE]
        FROM [dbo].[tbl_src_ArticlesCategories] AS [t5]
        ) AS [t6] ON [t6].[ID_ARTICLE] = [t0].[ID]
    WHERE (
        (CASE 
            WHEN 1 = 1 THEN CONVERT(Int,[t0].[IS_ACTIVE])
            ELSE 0
         END)) = 1
    ) AS [t7]
WHERE [t7].[ROW_NUMBER]  BETWEEN 7272 + 1 AND 7284
ORDER BY [t7].[ROW_NUMBER]

Aqui plano de execução de consulta lenta: https://www.brentozar.com/pastetheplan/?id=Sk-rLnY3F

Consulta rápida revisada (conjunto de dados de ~7K elementos, leva ~0s):

SELECT [t7].[ID], [t7].[ID_BRAND], [t7].[CODE], [t7].[CODFOR], [t7].[COD_ALT01], [t7].[COD_ALT02], [t7].[COD_ALT03], [t7].[ID_UOM], [t7].[IS_ACTIVE], [t7].[_ATTRIBUTES] AS [_ATTRIBUTES], [t7].[_DOCUMENTS] AS [_DOCUMENTS], [t7].[_SEO] AS [_SEO], [t7].[_TRANSLATIONS] AS [_TRANSLATIONS], [t7].[_TAGS] AS [_TAGS], [t7].[_NOTES] AS [_NOTES], [t7].[_METADATA] AS [_METADATA], [t7].[IS_B2B], [t7].[IS_B2C], [t7].[IS_PROMO], [t7].[IS_NEWS], [t7].[CAN_BE_RETURNED], [t7].[IS_SHIPPABLE], [t7].[HAS_SHIPPING_COSTS], [t7].[IS_PURCHEASABLE], [t7].[test], [t7].[ID2], [t7].[CODE2], [t7].[BUSINESS_NAME], [t7].[NAME], [t7].[PHONE_01], [t7].[PHONE_02], [t7].[PHONE_03], [t7].[FAX_01], [t7].[FAX_02], [t7].[COUNTRY_01], [t7].[CITY_01], [t7].[ADDRESS_01], [t7].[COUNTRY_02], [t7].[CITY_02], [t7].[ADDRESS_02], [t7].[EMAIL_01], [t7].[EMAIL_02], [t7].[PEC], [t7].[SITE_01], [t7].[SITE_02], [t7].[SITE_03], [t7].[SITE_04], [t7].[VAT_NUMBER], [t7].[SORT], [t7].[GROUPID_01], [t7].[IS_GROUPLEADER_01], [t7].[GROUPID_02], [t7].[IS_GROUPLEADER_02],[t7].[IS_ACTIVE2], [t7].[[_DOCUMENTS]]2] AS [_DOCUMENTS2], [t7].[[_SEO]]2] AS [_SEO2], [t7].[[_METADATA]]2] AS [_METADATA2], [t7].[test2], [t7].[ID3], [t7].[CODE3], [t7].[[_TRANSLATIONS]]2] AS [_TRANSLATIONS2], [t7].[[_METADATA]]3] AS [_METADATA3], [t7].[test3], [t7].[ID4], [t7].[ID_LINE], [t7].[ID_GROUP], [t7].[ID_CLASS], [t7].[ID_FAM], [t7].[ID_ARTICLE]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ID]) AS [ROW_NUMBER], [t0].[ID], [t0].[ID_BRAND], [t0].[CODE], [t0].[CODFOR], [t0].[COD_ALT01], [t0].[COD_ALT02], [t0].[COD_ALT03], [t0].[ID_UOM], [t0].[IS_ACTIVE], [t0].[_ATTRIBUTES], [t0].[_DOCUMENTS], [t0].[_SEO], [t0].[_TRANSLATIONS], [t0].[_TAGS], [t0].[_NOTES], [t0].[_METADATA], [t0].[IS_B2B], [t0].[IS_B2C], [t0].[IS_PROMO], [t0].[IS_NEWS], [t0].[CAN_BE_RETURNED], [t0].[IS_SHIPPABLE], [t0].[HAS_SHIPPING_COSTS], [t0].[IS_PURCHEASABLE], [t2].[test], [t2].[ID] AS [ID2], [t2].[CODE] AS [CODE2], [t2].[BUSINESS_NAME], [t2].[NAME], [t2].[PHONE_01], [t2].[PHONE_02], [t2].[PHONE_03], [t2].[FAX_01], [t2].[FAX_02], [t2].[COUNTRY_01], [t2].[CITY_01], [t2].[ADDRESS_01], [t2].[COUNTRY_02], [t2].[CITY_02], [t2].[ADDRESS_02], [t2].[EMAIL_01], [t2].[EMAIL_02], [t2].[PEC], [t2].[SITE_01], [t2].[SITE_02], [t2].[SITE_03], [t2].[SITE_04], [t2].[VAT_NUMBER], [t2].[SORT], [t2].[GROUPID_01], [t2].[IS_GROUPLEADER_01], [t2].[GROUPID_02], [t2].[IS_GROUPLEADER_02], [t2].[IS_ACTIVE] AS [IS_ACTIVE2], [t2].[_DOCUMENTS] AS [[_DOCUMENTS]]2], [t2].[_SEO] AS [[_SEO]]2], [t2].[_METADATA] AS [[_METADATA]]2], [t4].[test] AS [test2], [t4].[ID] AS [ID3], [t4].[CODE] AS [CODE3], [t4].[_TRANSLATIONS] AS [[_TRANSLATIONS]]2], [t4].[_METADATA] AS [[_METADATA]]3], [t6].[test] AS [test3], [t6].[ID] AS [ID4], [t6].[ID_LINE], [t6].[ID_GROUP], [t6].[ID_CLASS], [t6].[ID_FAM], [t6].[ID_ARTICLE]
    FROM [dbo].[tbl_ana_Articles] AS [t0]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t1].[ID], [t1].[CODE], [t1].[BUSINESS_NAME], [t1].[NAME], [t1].[PHONE_01], [t1].[PHONE_02], [t1].[PHONE_03], [t1].[FAX_01], [t1].[FAX_02], [t1].[COUNTRY_01], [t1].[CITY_01], [t1].[ADDRESS_01], [t1].[COUNTRY_02], [t1].[CITY_02], [t1].[ADDRESS_02], [t1].[EMAIL_01], [t1].[EMAIL_02], [t1].[PEC], [t1].[SITE_01], [t1].[SITE_02], [t1].[SITE_03], [t1].[SITE_04], [t1].[VAT_NUMBER], [t1].[SORT], [t1].[GROUPID_01], [t1].[IS_GROUPLEADER_01], [t1].[GROUPID_02], [t1].[IS_GROUPLEADER_02], [t1].[IS_ACTIVE], [t1].[_DOCUMENTS], [t1].[_SEO], [t1].[_METADATA]
        FROM [dbo].[tbl_ana_Brands] AS [t1]
        ) AS [t2] ON [t2].[ID] = [t0].[ID_BRAND]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t3].[ID], [t3].[CODE], [t3].[_TRANSLATIONS], [t3].[_METADATA]
        FROM [dbo].[tbl_ana_UoMs] AS [t3]
        ) AS [t4] ON [t4].[ID] = [t0].[ID_UOM]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t5].[ID], [t5].[ID_LINE], [t5].[ID_GROUP], [t5].[ID_CLASS], [t5].[ID_FAM], [t5].[ID_ARTICLE]
        FROM [dbo].[tbl_src_ArticlesCategories] AS [t5]
        ) AS [t6] ON [t6].[ID_ARTICLE] = [t0].[ID]
    WHERE (
        (CASE 
            WHEN 1 = 1 THEN CONVERT(Int,[t0].[IS_ACTIVE])
            ELSE 0
         END)) = 1
    ) AS [t7]
WHERE [t7].[ROW_NUMBER] BETWEEN 7272 + 1 AND 7284
ORDER BY [t7].[ROW_NUMBER]

Aqui o plano de execução de consulta rápida: https://www.brentozar.com/pastetheplan/?id=B10l82K2Y

Nota: todo o código de consulta é gerado automaticamente pelo ORM

As duas consultas parecem muito semelhantes e não está claro para mim o que melhora tão drasticamente o desempenho. Eu realmente aprecio uma dica sobre o que ajuda tanto o SQL Server, para que eu possa entender melhor como ajustar o ORM no futuro.