Why Is There a Sort on this Column

Question

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 <= '1/1/2022') THEN DATEDIFF(DAY, '1/1/2022', CURRENT_TIMESTAMP) ELSE -9999 END DESC

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)

Answer 1

nope

Even with perfect indexes, SQL Server won't be able to take advantage of them 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.

aside

Since I see you falling for the cost meme, I'm going to repeat part of my answer here.

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.

• Why You Should Stop Looking At Query Cost
• Is It Time To Remove Costs From Query Plans?
• 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 operator times to figure out which parts are most inefficient.

Answer 2

You have ORDER BY clause that contain LFTYLM_STATUS and LFTYLM_DATE_INCLUDED_LAST so Sort is on both of those columns.

ORDER BY
CASE WHEN (LFTYLM_STATUS = 'A') THEN 1 ELSE 2 END ASC,
CASE WHEN (LFTYLM_DATE_INCLUDED_LAST <= '1/1/2022') THEN DATEDIFF(DAY, '1/1/2022', CURRENT_TIMESTAMP) ELSE -9999 END DESC