Why Is There a Sort on this Column



  • This query has a plan which includes a SORT and the sort is taking up the majority (77%) of the cost:

    SELECT
    LFTYLM_STATUS
    FROM
    LFTYLM
    WHERE
    LFTYLM_LIS_ID = 138998 AND 
    LFTYLM_LFTY_ID = 78 AND 
    ((LFTYLM_STATUS = 'A' AND (LFTYLM_DATE_INCLUDED_LAST IS NULL OR '1/1/2022' >= LFTYLM_DATE_INCLUDED_LAST)) OR (LFTYLM_STATUS = 'I' AND ('1/1/2022' >= LFTYLM_DATE_INCLUDED_LAST OR LFTYLM_DATE_INCLUDED_LAST IS NULL OR LFTYLM_DATE_INCLUDED_LAST < LFTYLM_DATE_UPDATED)))
    ORDER BY
    CASE WHEN (LFTYLM_STATUS = 'A') THEN 1 ELSE 2 END ASC,
    CASE WHEN (LFTYLM_DATE_INCLUDED_LAST 

    The plan is at: https://www.brentozar.com/pastetheplan/?id=BktHa062K

    Why is there a SORT on LFTYLM_STATUS?

    The index being used includes LFTYLM_STATUS as a key column:

    CREATE NONCLUSTERED INDEX [IX_LISTING_FEED_TYPE_LISTING_MAP_5] ON [dbo].[LISTING_FEED_TYPE_LISTING_MAP]
    (
        LFTYLM_LIS_ID ASC,
        LFTYLM_LFTY_ID ASC,
        LFTYLM_DATE_INCLUDED_LAST ASC,
        LFTYLM_DATE_UPDATED ASC,
        LFTYLM_STATUS ASC
    )
    WITH (FILLFACTOR = 95)
    


  • nope

    Even with perfect indexes, SQL Server https://www.erikdarlingdata.com/sql-server/sargable-isnt-just-for-your-where-clause/ to avoid physically sorting data, the way your where clause is written.

    The issue is apparent in your query plan, because you're not sorting by columns, you're sorting by expressions.

    NUTS

    The only way to make expressions like this index-able is to:

    • Make computed columns, and index them
    • Create an indexed view, and index the resulting columns

    Otherwise, SQL Server will have to calculate those case expressions at runtime and sort the results.

    aside

    Since I see you falling for the cost meme, I'm https://dba.stackexchange.com/a/305722/32281 .

    I see you talking a lot about costs, and reducing them. Please don't pay attention to cost. It's a unit-less, estimated metric that has nothing to do with which parts of your query are slow and inefficient.

    Note that when you get an actual execution plan, there are no "actual" costs. They remain estimates, even after SQL Server measures and reports on many other runtime (actual) metrics.

    • https://www.erikdarlingdata.com/sql-server/why-you-should-stop-looking-at-query-cost/
    • https://www.erikdarlingdata.com/sql-server/is-it-time-to-remove-costs-from-query-plans/
    • https://www.erikdarlingdata.com/plan-cache-liars/signs-your-execution-plan-is-lying-to-you-operator-costs/

    The optimizer uses costs to choose a good enough plan quickly. Reducing them won't necessarily get you a faster query. You can have expensive queries that run quickly, and cheap queries that run slowly.

    I would suggest looking at https://www.erikdarlingdata.com/sql-server/how-long-did-that-operator-run-for/ to figure out which parts are most inefficient.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2