RANK() SQL Server execution plan issue

Question

What is driving SQL Server to use less optimal execution plan for queries where 6000+ rows are returned? I need to improve query performance for scenario where all rows are returned.

I select all fields and add rank over same three columns included in index. Depending on number of returned rows, query has two different execution plans, hence execution takes 0.2s or 3s respectively.

From 1 row returned up to ca. 5000 query runs fast. From 6000 rows returned up to all, query runs slow.

Table1 has ca. 38000 rows. Database runs on Azure SQL v12.

Table:

CREATE TABLE [dbo].[Table1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [KOD_ID] [int] NULL,
    [SYM] [nvarchar](20) NULL,
    [AN] [nvarchar](35) NULL,
    [A] [nvarchar](10) NULL,
    [B] [nvarchar](2) NULL,
    [C] [datetime] NULL,
    [D] [datetime] NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

CREATE NONCLUSTERED INDEX [IX_Table1] ON [dbo].[Table1]
(
    [KOD_ID] ASC,
    [SYM] ASC,
    [AN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Queries:

SELECT TOP 6000 *, RANK() OVER(ORDER BY KOD_ID ASC, SYM ASC, AN ASC) AS Rank#
FROM [dbo].[Table1]

SELECT TOP 7000 *, RANK() OVER(ORDER BY KOD_ID ASC, SYM ASC, AN ASC) AS Rank#
FROM [dbo].[Table1]

Execution plans for both queries

Is the * necessary? This prohibits the use of a covering index, pushing the optimizer into a plan that simply scans the clustered index and sorts the whole table first because it thinks the nonclustered index won't sufficiently reduce I/O (and apparently, as it turns out, sorting takes even more time). Try adding ORDER BY KOD_ID ASC, SYM ASC, AN ASC to the query as a whole, this may tip the balance in favor of the index again. — Jeroen Mostert
@JeroenMostert Absolutely not necessary. I have replaced * with explicit column names, but this has not helped. — Megrez7
That's not what I meant, I meant whether or not you really needed all columns. * versus spelling them out makes no difference (performance-wise). — Jeroen Mostert
In theory, bookmark lookups can be forced by joining on a subquery: SELECT TOP(7000) T.*, T1.Rank# FROM (SELECT TOP(7000) ID, RANK() OVER(ORDER BY KOD_ID ASC, SYM ASC, AN ASC) AS Rank# FROM Table1 ORDER BY ID) T JOIN Table1 ON T.ID = Table1.ID. This is fairly hacky even if it works, though, since it prevents the optimizer from doing a single table scan even where that really is the best approach. Reclustering your table (as presented in the answer) is another promising approach. — Jeroen Mostert
@JeroenMostert TOP clause was used only to find out and demonstrate change in performance. Real-life requirement is to return all rows, so joining works perfectly. And this is how I have finally done it. I join remaining columns with the rank subquery SELECT ID, RANK() OVER(ORDER BY KOD_ID ASC, SYM ASC, AN ASC) AS Rank# FROM [dbo].[Table1] on ID column. — Megrez7

Evaldas Buinauskas Evaldas Buinauskas · Accepted Answer · 2017-06-19T18:04:22

CREATE NONCLUSTERED INDEX [IX_Table1] ON [dbo].[Table1]
(
    [KOD_ID] ASC,
    [SYM] ASC,
    [AN] ASC
) INCLUDE ([A], [B], [C], [D]);

Create such kind of a covering index and it should scan this index and most likely sort won't even be needed because it's data is already sorted in index.

The key points in your queries are:

First plan has a key lookup, avoid them as much as possible (key lookup is additional scan for each row because index does not have them) create covering indexes with INCLUDED columns
Avoid sort operations too, they're costly to SQL Server

If you're alright with index rebuilds and favor reads over inserts, these could be alternate DDLs for your table considering that and KOD_ID, SYM, AN are not null-able:

If ID is needed to ensure uniqueness:

CREATE TABLE [dbo].[Table1] (
    [KOD_ID] [int] NOT NULL
    , [SYM] [nvarchar](20) NOT NULL
    , [AN] [nvarchar](35) NOT NULL
    , [ID] [int] IDENTITY(1, 1) NOT NULL
    , [A] [nvarchar](10) NULL
    , [B] [nvarchar](2) NULL
    , [C] [datetime2] NULL
    , [D] [datetime2] NULL
    , CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ([KOD_ID], [SYM], [AN], [ID])
    );
GO

If ID is not needed to ensure uniqueness:

CREATE TABLE [dbo].[Table1] (
    [KOD_ID] [int] NOT NULL
    , [SYM] [nvarchar](20) NOT NULL
    , [AN] [nvarchar](35) NOT NULL
    , [A] [nvarchar](10) NULL
    , [B] [nvarchar](2) NULL
    , [C] [datetime2] NULL
    , [D] [datetime2] NULL
    , CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ([KOD_ID], [SYM], [AN])
    );
GO

Also, note that I use datetime2 instead of datetime, that's what Microsoft recommends: https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

RANK() SQL Server execution plan issue

1 Answers