CODEWITHSUNDEEP
sqlsql-serverperformancesql-execution-plan
Megrez7
Megrez7

Reputation: 1457

RANK() SQL Server execution plan issue

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

Execution plans

Upvotes: 0

Views: 212

Answers (1)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

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:

  1. 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
  2. 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://learn.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.

Upvotes: 2

Related Questions