SQL Server 2012 row_number ASC DESC performance

Question

In a SQL Server 2012 version 11.0.5058 I've a query like this

SELECT TOP 30 
    row_number() OVER (ORDER BY SequentialNumber ASC) AS [row_number], 
    o.Oid, StopAzioni 
FROM 
    tmpTestPerf O 
INNER JOIN
    Stati s on O.Stato = s.Oid
WHERE 
    StopAzioni = 0

• When I use ORDER BY SequentialNumber ASC it takes 400 ms
• When I use ORDER BY DESC in the row_number function it takes only 2 ms

(This is in a test environment, in production it is 7000, 7 seconds vs 15 ms!)

Analyzing the execution plan, I found that it's the same for both queries. The interesting difference is that in the slower it works with all the rows filtered by the stopazioni = 0 condition, 117k rows

In the faster it only uses 53 rows

There are a primary key on the tmpTestPerf query and an indexed ASC key on the sequential number column.

How it could be explained?

Regards. Daniele

This is the script of the tmpTestPerfQuery and Stati query with their indexes

CREATE TABLE [dbo].[tmpTestPerf]
(
    [Oid] [uniqueidentifier] NOT NULL,
    [SequentialNumber] [bigint] NOT NULL,
    [Anagrafica] [uniqueidentifier] NULL,
    [Stato] [uniqueidentifier] NULL,

    CONSTRAINT [PK_tmpTestPerf] 
      PRIMARY KEY CLUSTERED ([Oid] ASC)
)

CREATE NONCLUSTERED INDEX [IX_2] 
   ON [dbo].[tmpTestPerf]([SequentialNumber] ASC)

CREATE TABLE [dbo].[Stati]
(
    [Oid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [Descrizione] [nvarchar](100) NULL,
    [StopAzioni] [bit] NOT NULL

    CONSTRAINT [PK_Stati] 
      PRIMARY KEY CLUSTERED ([Oid] ASC)
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [iStopAzioni_Stati] 
   ON [dbo].[Stati]([StopAzioni] ASC)
GO

Answer 1

The query plans are not exactly the same.

Select the Index Scan operator.

Press F4 to view the properties and have a look at Scan Direction.

When you order ascending the Scan Direction is FORWARD and when you order descending it is BACKWARD.

The difference in number of rows is there because it takes only 53 rows to find 30 rows when scanning backwards and it takes 117k rows to find 30 matching rows scanning forwards in the index.

_{Note, without an order by clause on the main query there is no guarantee on what 30 rows you will get from your query. In this case it just happens to be the first thirty or the last thirty depending on the order by used in row_number().}