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)
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.
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.
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.
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.