The case of the filter predicate with window functions such as ROW_NUMBER and RANK
Published Apr 25 2019 02:28 PM 8,012 Views
Microsoft

I recently helped troubleshoot a query performance issue that involved the window function "ROW_NUMBER". The running theory was that the query’s filter predicate should target the index resulting in a seek, but was instead applied via a Filter operator positioned at the top of the query plan. This led to a costly index scan and higher query duration.

  

While the actual use case was a bit more complicated, it can be repro'd using a trivial data set:

 

--Create table and index

CREATE TABLE [DBO].[T1](

[COL1] [INT] NULL

) ON [PRIMARY]

GO

 

CREATE CLUSTERED INDEX [ClusteredIndex_T1] ON [DBO].[T1]

(

[COL1] ASC

)ON [PRIMARY]

GO

 

--Load ~10,000 rows

DECLARE @START INT;

SET @START = 1;

WHILE @START < 10001

BEGIN;

INSERT INTO T1 VALUES (@START)

SET @START = @START + 1;

END

 

--Create a View

CREATE VIEW MyView

AS

SELECT * FROM T1

 

--Create a View w/non-deterministic function

CREATE VIEW RN_View

AS

SELECT

  ROW_NUMBER() OVER(ORDER BY COL1) AS Row#, *

FROM T1

 

Let's enable the Actual Execution Plan and execute test #1

 

--Test #1

SELECT * FROM RN_View AS RN

WHERE RN.Row# = 10

 

1.jpg

 

Note the costly Index Scan, which eventually feeds to a Filter(Estimates versus Actuals available inline via SSMS v18.0:(

 

2.gif

 

Instead of applying the predicate earlier in the execution, which would be optimal as it would reduce the output down to "1" row quickly, our predicate is instead being applied via the Filter operator "late" in the execution", after a costly scan operator:

 

3.jpg

 

Let's try another test, in which we move the ROW_NUMBER function to the "base" query itself:

 

--Test #2

SELECT

  ROW_NUMBER() OVER(ORDER BY COL1) AS Row#, *

FROM MyView MV

WHERE MV.COL1 = 10

 

4.jpg

 

The plan has changed to a "fast" variation, in which the predicate was sent to the appropriate index, resulting in a seek. Also, it is important to note the result set has changed when compared to test #1.

 

5.gif

 

Management Studio’s Plan Comparison feature gives us a birds eye view of plan #1 versus plan #2:

 

7.gif

 

So what happened in Test #1? Why can't the predicate drive an index seek like in Test #2?

 

The key factor here relates to when the Query Optimizer deems it safe, or not safe, to apply a predicate. Specifically, if the QO determines the result set requested by the T-SQL would change if the predicate was in fact applied at that point in the plan...

 

The View definition, AKA the T-SQL executed by the View, is basically considered a subquery by the Algebrizer. Each subquery has its own result set. If we execute the SELECT directly from the View definition, we receive a result set 10,000 rows with a sequential Row#, which were added by the ROW_NUMBER function(result set truncated to save blog space).

 

CREATE VIEW RN_View

as

SELECT

  ROW_NUMBER() OVER(ORDER BY COL1) AS Row#, *

FROM T1

 

8.jpg

 

If we review the ROW_NUMBER definition, we can see why applying the predicate up front would not be a good idea:

 

https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017

 

Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

 

The "result set" requested by the View definition is not only 10,000 rows in length, but also adds the sequential numbering to each row. If the following query was allowed to generate a query plan in which the filter "10" was pushed down and applied early in the plan, the result set would be limited to only the first 10 rows. Hence, the result set would be different:

 

SELECT * FROM RN_View AS RN

WHERE RN.Row# = 10

 

9.jpg

 

Conversely,  if the ROW_NUMBER function is pulled out of the View definition and moved into the "base" query, the predicate can be applied/executed “safely” at the start of the execution plans processing. The filter is now in the "same query" as the ROW_NUMBER function, and the QO knows the user is asking for only COL1 values equal to 10, and to apply the ROW_NUMBER function to only those rows. Note the different result set:

 

SELECT

  ROW_NUMBER() OVER(ORDER BY COL1) AS Row#, *

FROM MyView MV

WHERE MV.COL1 = 10

 

10.jpg

 

This same scenario can be reproduced using a CTE:

 

WITH MyCTE (COL1, COL2)

AS

(

SELECT  ROW_NUMBER() OVER(ORDER BY COL1) AS Row#, *

FROM MyView

)

SELECT * FROM MyCTE WHERE COL2 = 10

 

11.jpg

12.gif

 

The moral of the story is to always pay close attention to what your subquery's are asking for, especially when window functions such as ROW_NUMBER or RANK are used. The definition of said subqueries can change filter predicate location in query plans and significantly affect performance.

 

Troy Moen

Sr. Escalation Engineer

 

1 Comment
Version history
Last update:
‎Apr 25 2019 02:34 PM
Updated by: