In this post, we’ll look at some interesting behaviour I’ve stumbled across with window functions and derived datasets in SQL Server.
I came across a query in a workload that was looking to get the most recent thing per group, a representative example in the Stack Overflow 2010 Database is below. The database is provided under cc-by-sa 4.0 licence from Stack Exchange Data Dump, I am running in compatibility level 150 on a SQL Server 2022 instance installed on a VM with 8 cores and 35GB RAM, though the issue being illustrated also occurs in earlier compatibility levels.
This query gets the most recently created user with the display name passed in via a variable (in real life, this was a parameterised query, though the behaviour below is the same):
DECLARE @DisplayName NVARCHAR(40) = 'John';
SELECT *
FROM (
SELECT Id,
DisplayName,
[Location],
ROW_NUMBER() OVER (PARTITION BY DisplayName ORDER BY CreationDate DESC) AS RowN
FROM dbo.Users
) a
WHERE DisplayName = @DisplayName AND
RowN = 1;
We can create an index to support this query:
CREATE INDEX IX_DisplayName ON dbo.Users
(
DisplayName,
CreationDate DESC
)
INCLUDE
(
[Location]
);
If we run the query, we can see our index gets used:
…but sadly not in the way we would like.
SQL Server decides to scan the entire index, it then performs the window aggregate and then finally filters down to the two predicates in our WHERE clause:
The STATISTICS IO output for the query is:
Table 'Users'. Scan count 1, logical reads 1885, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 63 ms.
Seems inefficient…
What we really want is SQL Server to seek into the index for the DisplayName we provided and then do the windowing just on the records it finds.
I found we can force SQL Server to do this in a number of ways:
Method 1 – OPTION (RECOMPILE)
DECLARE @DisplayName NVARCHAR(40) = 'John';
SELECT *
FROM (
SELECT Id,
DisplayName,
[Location],
ROW_NUMBER() OVER (PARTITION BY DisplayName ORDER BY CreationDate DESC) AS RowN
FROM dbo.Users
) a
WHERE DisplayName = @DisplayName AND
RowN = 1
OPTION (RECOMPILE);
Now SQL Server knows the value of the parameter at compile time and does what we want it to:
We can see SQL Server has pulled back just 1 row from the seek, then applied the window function before finally filtering for just RowN = 1:
The STATISTICS IO output also shows an improvement in the amount of data being read:
Table 'Users'. Scan count 1, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Method 2 – Literal Value
Likewise, we can get the same result when we use a literal value:
SELECT *
FROM (
SELECT Id,
DisplayName,
[Location],
ROW_NUMBER() OVER (PARTITION BY DisplayName ORDER BY CreationDate DESC) AS RowN
FROM dbo.Users
) a
WHERE DisplayName = N'John' AND
RowN = 1;
And we get the same execution plan as the OPTION (RECOMPILE) plan:
However, in the situation I was dealing with, this query was firing thousands of times over and over with different values for the parameter so adding a literal value or an OPTION (RECOMPILE) would give a CPU hit. Furthermore, the literal value option would cause plan cache bloat as excess plans would be compiled due to the difference in query text.
Method 3 – a Subtle Re-Write
We can get the index seek by moving our display name filter into the sub-query:
DECLARE @DisplayName NVARCHAR(40) = 'John';
SELECT *
FROM (
SELECT Id,
DisplayName,
[Location],
ROW_NUMBER() OVER (PARTITION BY DisplayName ORDER BY CreationDate DESC) AS RowN
FROM dbo.Users
WHERE DisplayName = @DisplayName
) a
WHERE RowN = 1;
We now get the seek we wanted:
Another approach would be a slightly different re-write using TOP:
DECLARE @DisplayName NVARCHAR(40) = 'John';
SELECT TOP 1
Id,
DisplayName,
[Location]
FROM dbo.Users
WHERE DisplayName = @DisplayName
ORDER BY CreationDate DESC;
With this option, we get the index seek and a simpler execution plan:
Other Cases
I found that the window function behaves this way on all “derived datasets”.
The same happens on a CTE:
DECLARE @DisplayName NVARCHAR(40) = 'John';
WITH UserCTE
AS
(
SELECT Id,
DisplayName,
[Location],
ROW_NUMBER() OVER (PARTITION BY DisplayName ORDER BY CreationDate DESC) AS RowN
FROM dbo.Users
)
SELECT *
FROM UserCTE
WHERE RowN = 1 AND
DisplayName = @DisplayName;
And we can fix it the same way, pushing the WHERE clause into the CTE:
DECLARE @DisplayName NVARCHAR(40) = 'John';
WITH UserCTE
AS
(
SELECT Id,
DisplayName,
[Location],
ROW_NUMBER() OVER (PARTITION BY DisplayName ORDER BY CreationDate DESC) AS RowN
FROM dbo.Users
WHERE DisplayName = @DisplayName
)
SELECT *
FROM UserCTE
WHERE RowN = 1;
It also happens with a view:
CREATE OR ALTER VIEW dbo.UsersView
AS
SELECT Id,
DisplayName,
[Location],
ROW_NUMBER() OVER (PARTITION BY DisplayName ORDER BY CreationDate DESC) AS RowN
FROM dbo.Users;
GO
DECLARE @DisplayName NVARCHAR(40) = 'John';
SELECT *
FROM dbo.UsersView
WHERE DisplayName = @DisplayName AND
RowN = 1;
However, we can’t apply the same fix to a view as it isn’t possible to create a parameterised view. We can however create another object which suffers from the same issue initially, an inline TVF.
Here is an example with the filter outside of the TVF:
CREATE OR ALTER FUNCTION dbo.tvf_Users()
RETURNS TABLE
AS
RETURN
SELECT Id,
DisplayName,
[Location],
ROW_NUMBER() OVER (PARTITION BY DisplayName ORDER BY CreationDate DESC) AS RowN
FROM dbo.Users;
GO
DECLARE @DisplayName NVARCHAR(40) = 'John';
SELECT *
FROM dbo.tvf_Users()
WHERE RowN = 1 AND
DisplayName = @DisplayName;
The plan shows the same issue:
You’ve guessed it – it can be fixed in the same way:
CREATE OR ALTER FUNCTION dbo.tvf_Users
(
@DisplayName NVARCHAR(40)
)
RETURNS TABLE
AS
RETURN
SELECT Id,
DisplayName,
[Location],
ROW_NUMBER() OVER (PARTITION BY DisplayName ORDER BY CreationDate DESC) AS RowN
FROM dbo.Users
WHERE DisplayName = @DisplayName;
GO
DECLARE @DisplayName NVARCHAR(40) = 'John';
SELECT *
FROM dbo.tvf_Users(@DisplayName)
WHERE RowN = 1;
Here’s our seek:
The fact that in the sub-query version we looked at first, SQL Server is able to push the predicate down to the index seek with OPTION (RECOMPILE) but not without makes me think that there are some possible parameters where the seek plan is not safe. I have tried various examples (using the OPTION (RECOMPILE)) and all have come back with the seek plan, with the exception of NULL, which produces a constant scan since nothing can equal NULL. With that, I am not entirely sure why SQL Server doesn’t push the predicate down into the index seek by default. Perhaps it’s just one of those “it just doesn’t do that” limitations.
I tried to force SQL Server to seek the index, the syntax is below:
DECLARE @DisplayName NVARCHAR(40) = 'John';
SELECT *
FROM (
SELECT Id,
DisplayName,
[Location],
ROW_NUMBER() OVER (PARTITION BY DisplayName ORDER BY CreationDate DESC) AS RowN
FROM dbo.Users WITH (INDEX(IX_DisplayName), FORCESEEK)
) a
WHERE DisplayName = @DisplayName AND
RowN = 1;
With this query, I get the error:
Msg 8622, Level 16, State 1, Line 18
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
When I look at the way the query is structured with the hint, it starts to make sense to me as to why the optimizer is not pushing the predicate down – we can see that the FORCESEEK hint is inside the subquery. In the context of the subquery, which must return all records to number them, what would SQL Server seek to?
A Twist
The real-life example was on a SQL Server 2019 instance and as such, the highest compatibility level possible was 150. While testing for writing this post, I found that this behaviour is different on compatibility level 160:
ALTER DATABASE [StackOverflow2010] SET COMPATIBILITY_LEVEL = 160;
Let’s execute our original query again:
DECLARE @DisplayName NVARCHAR(40) = 'John';
SELECT *
FROM (
SELECT Id,
DisplayName,
[Location],
ROW_NUMBER() OVER (PARTITION BY DisplayName ORDER BY CreationDate DESC) AS RowN
FROM dbo.Users
) a
WHERE DisplayName = @DisplayName AND
RowN = 1;
We now get the execution plan with the seek we wanted:
So it appears that the optimizer can indeed push the predicate down from compatibility level 160.
Conclusion
We’ve seen that in compatibility level 150, SQL Server does not push a predicate down into the data access node if a filter exists outside of a derived dataset when there is a window function in the inner dataset, even though the filter is on the column being partitioned. We looked at a few options to get this behaviour and assessed the pros and cons of each and observed how the behaviour changed in compatibility level 160.