patternModerate

Slow order by SQL Server

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

sqlorderslowserver

Problem

In my application, I have a query which performs a search in "files" table.

The table "files" is partitioned by "f"."created" (see the table definition and has ~26 million rows for the client 19 ("f"."cid = 19).

The point here is, if I do this query:

SELECT "f"."id" AS "FileId"  
, "f"."name" AS "FileName"  
, "f"."year" AS "Fileyear"  
, "f"."cid" AS "clientId"
, "f"."created" AS "FileDate"
, CASE WHEN ("vnVE0"."value" is not null AND "vnVE0"."value" != '')                           
                                THEN CAST("vnVE0"."value" AS decimal(28,2))
                                ELSE 0 END AS "keywordValueCol0_numeric"
FROM files "f"  
OUTER APPLY
(
    SELECT DISTINCT
        VT.[value]
    FROM dbo.value_number AS VT
    WHERE
        VT.id_file = F.id
        AND VT.id_field = 260
) AS "vnVE0"
WHERE "grapado" IS NULL AND "masterversion" IS NULL AND ("f"."year" = 2013 OR "f"."year" = 0) AND "f"."cid" = 19
GROUP BY "f"."id", "f"."name", "f"."year", "f"."cid", "f"."created", CASE WHEN ("vnVE0"."value" is not null AND "vnVE0"."value" != '')                           
                            THEN CAST("vnVE0"."value" AS decimal(28,2))
                            ELSE 0 END
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;

I get the results in 0 seconds, with the following execution plan: https://www.brentozar.com/pastetheplan/?id=SkV0-FDcG

But If I try to order by "name" the query becomes too slow:

```
SELECT "f"."id" AS "FileId"
, "f"."name" AS "FileName"
, "f"."year" AS "Fileyear"
, "f"."cid" AS "clientId"
, "f"."created" AS "FileDate"
, CASE WHEN ("vnVE0"."value" is not null AND "vnVE0"."value" != '')
THEN CAST("vnVE0"."value" AS decimal(28,2))
ELSE 0 END AS "keywordValueCol0_numeric"
FROM files "f"
OUTER APPLY
(
SELECT DISTINCT
VT.[value]
FROM dbo.value_number AS VT
WHERE
VT.id_file = F.id

Solution

Analysis

For the query with no order preference, SQL Server can stream grouped rows using a Hash Match Flow Distinct. If it encounters the required number of distinct entries quickly, the execution time is short.

When a specific order is required, SQL Server must test every row. For example, to place rows in name order, it must sort all rows by name. This will be slow if there are a lot of rows, and no index to provide that order without sorting.

There are a number of fundamental complications in your case, most notably the partitioning, and the disjunction on [year]. The partitioning means your indexes cannot deliver the order you might expect. For example an index on name is actually sorted first by partition number, then by name. It cannot deliver rows sorted on name alone.

You also have FORCED PARAMETERIZATION set. This may be beneficial overall, but it comes with impacts you should fully understand. That combined with the partitioning and multi-column indexes means your statistics are largely useless.

The disjunction on year also messes with ordering, and means SQL Server can only seek `year >= 0 and year fiddle

Code Snippets

CREATE INDEX [IX dbo.files cid, year, name : grapado IS NULL AND masterversion IS NULL] 
ON dbo.files (cid, [year], [name])
INCLUDE (grapado, masterversion)
WHERE grapado IS NULL AND masterversion IS NULL;

CREATE INDEX [IX dbo.value_number id_file, id_field, value] 
ON dbo.value_number (id_file, id_field, [value]);

WITH PartitionNumbers AS
(
    -- Each partition of the table
    SELECT P.partition_number
    FROM sys.partitions AS P
    WHERE P.[object_id] = OBJECT_ID(N'dbo.files', N'U')
    AND P.index_id = 1
)
SELECT
    FF.id,
    FF.[name],
    FF.[year],
    FF.cid,
    FF.created,
    vnVE0.keywordValueCol0_numeric
FROM PartitionNumbers AS PN
CROSS APPLY
(
    SELECT
        F100.*
    FROM 
    (
        -- 50 rows in order for year 2013
        SELECT
            F.id,
            F.[name],
            F.[year],
            F.cid,
            F.created
        FROM dbo.files AS F
        WHERE
            F.grapado IS NULL
            AND F.masterversion IS NULL
            AND F.[year] = 2013
            AND F.cid = 19
            AND $PARTITION.PF_files_partitioning(F.created) = PN.partition_number
        ORDER BY
            F.[name]
            OFFSET 0 ROWS
            FETCH FIRST 50 ROWS ONLY

        UNION ALL

        -- 50 rows in order for year 0
        SELECT
            F.id,
            F.[name],
            F.[year],
            F.cid,
            F.created
        FROM dbo.files AS F
        WHERE
            F.grapado IS NULL
            AND F.masterversion IS NULL
            AND F.[year] = 0
            AND F.cid = 19
            AND $PARTITION.PF_files_partitioning(F.created) = PN.partition_number
        ORDER BY
            F.[name]
            OFFSET 0 ROWS
            FETCH FIRST 50 ROWS ONLY
    ) AS F100
) AS FF
OUTER APPLY
(
    -- Lookup distinct values
    SELECT
        keywordValueCol0_numeric = 
            CASE
                WHEN VN.[value] IS NOT NULL AND VN.[value] <> ''
                THEN CONVERT(decimal(28, 2), VN.[value])
                ELSE CONVERT(decimal(28, 2), 0)
            END
    FROM dbo.value_number AS VN
    WHERE
        VN.id_file = FF.id
        AND VN.id_field = 260
    GROUP BY
        VN.[value]
) AS vnVE0
ORDER BY
    FF.[name]
    OFFSET 0 ROWS
    FETCH FIRST 50 ROWS ONLY;

SELECT COUNT_BIG(*) 
FROM dbo.files AS F
OUTER APPLY
(
    SELECT DISTINCT VN.[value]
    FROM dbo.value_number AS VN
    WHERE
        VN.id_file = F.id
        AND VN.id_field = 260
) AS vnVE0
WHERE
    F.grapado IS NULL
    AND F.masterversion IS NULL
    AND F.[year] IN (0, 2013)
    AND F.cid = 19;

Context

StackExchange Database Administrators Q#202380, answer score: 10

Revisions (0)

No revisions yet.