{"id":83273,"date":"2019-02-11T19:14:01","date_gmt":"2019-02-11T19:14:01","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=83273"},"modified":"2026-04-16T08:59:32","modified_gmt":"2026-04-16T08:59:32","slug":"the-performance-of-window-aggregates-revisited-with-sql-server-2019","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/the-performance-of-window-aggregates-revisited-with-sql-server-2019\/","title":{"rendered":"SQL Server 2019 Window Aggregate Performance: Batch Mode on Rowstore"},"content":{"rendered":"

Window aggregate functions in SQL Server – using SUM(), AVG(), MIN(), and MAX() with an OVER clause – are powerful for analytical queries but have historically suffered significant performance overhead, particularly for accumulating aggregates like running totals. SQL Server 2019 addresses this with Batch Mode on Rowstore: a query processing improvement that allows batch mode execution on standard heap and B-tree tables without requiring a columnstore index. This article benchmarks the improvement specifically for window aggregate queries, showing up to 7\u00d7 elapsed time improvement on accumulating SUM queries, and covers what types of window operations benefit most from the feature.<\/strong><\/p>\n

In 2005 and 2012, Microsoft introduced a number of windowing functions in SQL Server, like my favourite function LAG<\/code>. These functions perform well, but, in my opinion, the main benefit is making complicated queries easier to write. I\u2019ve been fascinated by these functions for years, but there is one thing about them that has bothered me, and that is the performance of window aggregate functions. Luckily, that is changing with SQL Server 2019.<\/p>\n

What are Window Aggregate Functions?<\/h2>\n

Window aggregate functions let you add your favourite aggregate functions, like SUM<\/code>, AVG<\/code> and MIN<\/code>, to non-aggregate queries. This lets you return the details in the results while adding in grand totals or sub-totals or any other aggregate you need. Here\u2019s an example:<\/p>\n

USE AdventureWorks2017;\nGO\nSET STATISTICS IO ON;\nGO\nSELECT CustomerID, SalesOrderID, OrderDate, TotalDue \nFROM Sales.SalesOrderHeader\nORDER BY CustomerID;\n\nSELECT CustomerID, SalesOrderID, OrderDate, TotalDue, \n\tSUM(TotalDue) OVER(PARTITION BY CustomerID) AS SubTotal \nFROM Sales.SalesOrderHeader\nORDER BY CustomerID;<\/pre>\n
I\u2019m running SQL Server 2019 CTP 2.2, and the database is in 2016 compatibility mode. The first query lists the sales orders, and the second query also has a sub total for each customer. The results should look like Figure 1.<\/p>\n
<\/p>\n
Figure 1: The partial results of the sales orders queries<\/p>\n
I think you would have to agree that calculating the subtotal was easy to do. You just have to add the OVER<\/code> clause. In this case, I wanted the subtotals for each customer, so I included PARTITION BY CustomerID<\/code>. The problem with this technique is that the performance has been disappointing. In fact, I\u2019ve been recommending that window aggregates be avoided when they must operate on a large number of rows. To show what I mean, take a look at the STATISTICS IO<\/code> results in Figure 2.<\/p>\n
<\/p>\n
Figure 2: The logical reads for the sales orders queries<\/p>\n
The first query has 0 logical reads for a worktable and 689 logical reads for scanning the entire table since there is no WHERE<\/code> clause. The second query also takes 689 logical reads for scanning the table, and 139,407 logical reads for a worktable. That\u2019s a big difference, but do the logical reads for a worktable affect query run times? These AdventureWorks tables are too small to see the performance impact, but it does make a difference as you\u2019ll see in the next section.<\/p>\n
Is the Performance Really That Bad?<\/h2>\n
To show how much the window aggregate impacts performance, the following queries use bigger tables created from Adam Mechanic\u2019s script Thinking Big Adventure<\/a>. To make sure that populating SSMS (SQL Server Management Studio) does not affect the run times, the results are saved in temp tables. Here are two queries calculating the subtotal for each product in this table of 31 million rows.<\/p>\n
SET STATISTICS IO ON;\nSET STATISTICS TIME ON;\nCREATE TABLE #Products(ProductID INT, TransactionDate DATETIME, \n\tTotal MONEY, SubTotal MONEY);\n\nWITH SubTotalByProduct AS (\n\tSELECT ProductID, SUM(Quantity * ActualCost) AS SubTotal\n\tFROM bigTransactionHistory \n\tGROUP BY ProductID)\nINSERT INTO #Products (ProductID, TransactionDate, Total, SubTotal)\nSELECT STP.ProductID, TransactionDate, Quantity * ActualCost AS Total, \n     SubTotal \nFROM bigTransactionHistory AS BT \nJOIN SubTotalByProduct AS STP ON BT.ProductID = STP.ProductID; \n\nTRUNCATE TABLE #Products;\n\nINSERT INTO #Products (ProductID, TransactionDate, Total, SubTotal)\nSELECT ProductID, TransactionDate, Quantity * ActualCost AS Total, \n\tSUM(Quantity * ActualCost) \n        OVER(PARTITION BY ProductID) AS SubTotal \nFROM dbo.bigTransactionHistory;\n\nDROP TABLE #Products;<\/pre>\n
Since the results are stored in temp tables, the only output is the STATISTICS IO<\/code> and STATISTICS TIME<\/code>. The first query in this example demonstrates one of the traditional methods to get subtotals, using a CTE (common table expression) that joins to the original table. The second query creates the same results, but with a window aggregate function.<\/p>\n
To make sure that caching didn\u2019t affect the run time, I ran the queries twice. The statistics results of the second run can be seen in Figure 3.<\/p>\n
<\/p>\n
Figure 3: The results of running the test on bigTransactionHistory<\/p>\n
The first thing to notice is that the traditional method took 35 seconds. The window aggregate function method took 156 seconds or about 2.2 minutes. You can also see a big difference in the logical reads. The traditional method took twice as many logical reads when scanning the table. That\u2019s because the table was accessed once inside the CTE and again in the outer query. The table was scanned just once when using the window aggregate, but again, the logical reads required is much higher than just reading the table due to the worktable. As you can see, there is a big performance hit with window aggregates over traditional methods.<\/p>\n
Batch Mode to the Rescue<\/h2>\n
In 2016, Microsoft announced an advancement in the columnstore index feature called batch mode processing. This feature also improves the performance of window aggregate queries as long as a table with a columnstore index is part of the query. If that happens naturally, wonderful! Otherwise, artificially adding a columnstore index seems like more trouble than just using the traditional query method.<\/p>\n
To show the difference in performance, this example creates an empty temp table with a columnstore index and adds it to the query.<\/p>\n
USE master;\nGO\nALTER DATABASE AdventureWorks2017\n     --Make sure it\u2019s 2016 compat\n     SET COMPATIBILITY_LEVEL = 130 WITH NO_WAIT\nGO\nUSE AdventureWorks2017;\nGO\nCREATE TABLE #Products(ProductID INT, TransactionDate DATETIME, \n\tTotal MONEY, SubTotal MONEY);\nCREATE TABLE #CS(KeyCol INT NOT NULL PRIMARY KEY, \n\tCol1 NVARCHAR(25));\nCREATE COLUMNSTORE INDEX CSI_CS ON #CS(KeyCol, Col1);\n\nINSERT INTO #Products (ProductID, TransactionDate, Total, SubTotal)\nSELECT ProductID, TransactionDate, Quantity * ActualCost AS Total, \n\tSUM(Quantity * ActualCost) \n        OVER(PARTITION BY ProductID) AS SubTotal \nFROM dbo.bigTransactionHistory\nOUTER APPLY #CS;\n\nDROP TABLE #Products;\nDROP TABLE #CS;<\/pre>\n
To see the benefits of the 2016 change, the database compatibility level was changed to 2016. The empty table #CS was created along with a columnstore index. It\u2019s added to the query with an OUTER APPLY<\/code> and doesn\u2019t affect the results.<\/p>\n
The performance results can be found in Figure 4:<\/p>\n
<\/p>\n
Figure 4: Including a columnstore index to improve performance<\/p>\n
In this test, the performance was similar to the traditional CTE query. That\u2019s great, but in my opinion the main reason to use the window aggregate is to make the query simple to write. I don\u2019t want to add the complexity of adding an artificial columnstore index. Luckily, in 2019, batch row processing applies to some queries that could benefit from it without a columnstore index, including window aggregates! This feature is called Batch Mode on Rowstore<\/em>. Here is another test, this time with 2019 compatibility level.<\/p>\n
USE master;\nGO\nALTER DATABASE AdventureWorks2017\n     --Set to 2019 compat\n     SET COMPATIBILITY_LEVEL = 150 WITH NO_WAIT\nGO\nUSE AdventureWorks2017;\nGO\nCREATE TABLE #Products(ProductID INT, TransactionDate DATETIME, \n\tTotal MONEY, SubTotal MONEY);\n\nINSERT INTO #Products (ProductID, TransactionDate, Total, SubTotal)\nSELECT ProductID, TransactionDate, Quantity * ActualCost AS Total, \n\tSUM(Quantity * ActualCost) \n        OVER(PARTITION BY ProductID) AS SubTotal \nFROM dbo.bigTransactionHistory;\n\nDROP TABLE #Products;<\/pre>\n
This time, the query took just 15 seconds to run! The performance results are shown in Figure 5.<\/p>\n
<\/p>\n
Figure 5: Using the 2019 batch mode feature<\/p>\n
Accumulating Aggregates<\/h2>\n
To get running totals, all you have to do is add ORDER BY<\/code> to the OVER<\/code> clause to the window aggregate. This is easy to do and will give you the results you expect as long as the columns in the ORDER BY<\/code> option of the OVER<\/code> clause are unique. To drastically improve performance, you need to add the frame (ROWS BETWEEN UNBOUND PROCEEDING AND CURRENT ROW<\/code>) to the OVER<\/code> clause. This is intuitive and is probably left out more often than it\u2019s included. To see the difference, this test compares using the frame to not using the frame in 2016 compatibility mode.<\/p>\n
USE master;\nGO\nALTER DATABASE AdventureWorks2017\n\tSET COMPATIBILITY_LEVEL = 130 WITH NO_WAIT\nGO\nUSE AdventureWorks2017;\nGO\nCREATE TABLE #Products(TransactionID INT, ProductID INT, \n        TransactionDate DATETIME, \n\tTotal MONEY, RunningTotal MONEY);\nINSERT INTO #Products (TransactionID, ProductID, TransactionDate, \n        Total, RunningTotal) \nSELECT TransactionID, ProductID, TransactionDate, \n        Quantity * ActualCost, \n\tSUM(Quantity * ActualCost) \n        OVER(PARTITION BY ProductID ORDER BY TransactionID)\nFROM bigTransactionHistory;\n\nTRUNCATE TABLE #Products;\n\nINSERT INTO #Products (TransactionID, ProductID, TransactionDate, \n       Total, RunningTotal) \nSELECT TransactionID, ProductID, TransactionDate, \n        Quantity * ActualCost, \n\tSUM(Quantity * ActualCost) \n        OVER(PARTITION BY ProductID ORDER BY TransactionID\n\tROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)\nFROM bigTransactionHistory;\n\nDROP TABLE #PRODUCTS<\/pre>\n
The results when using 2016 compatibility mode can be seen in Figure 6.<\/p>\n
<\/p>\n
Figure 6: The results comparing using a frame with 2016 compatibility<\/p>\n
By using the frame, the query went from almost 4 minutes to 1.4 minutes. Here\u2019s the same test except for 2019 compatibility to take advantage of batch mode processing.<\/p>\n
USE master;\nGO\nALTER DATABASE AdventureWorks2017\n\tSET COMPATIBILITY_LEVEL = 150 WITH NO_WAIT\nGO\nUSE AdventureWorks2017;\nGO\nCREATE TABLE #Products(TransactionID INT, ProductID INT, \n        TransactionDate DATETIME, \n\tTotal MONEY, RunningTotal MONEY);\nINSERT INTO #Products (TransactionID, ProductID, TransactionDate, \n        Total, RunningTotal) \nSELECT TransactionID, ProductID, TransactionDate, \n        Quantity * ActualCost, \n\tSUM(Quantity * ActualCost) \n        OVER(PARTITION BY ProductID ORDER BY TransactionID)\nFROM bigTransactionHistory;\n\nTRUNCATE TABLE #Products;\n\nINSERT INTO #Products (TransactionID, ProductID, TransactionDate, \n        Total, RunningTotal) \nSELECT TransactionID, ProductID, TransactionDate, \n        Quantity * ActualCost, \n\tSUM(Quantity * ActualCost) \n        OVER(PARTITION BY ProductID ORDER BY TransactionID\n\tROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)\nFROM bigTransactionHistory;\n\nDROP TABLE #Products;<\/pre>\n
With batch mode, there is no performance penalty due to leaving out the frame. In each case, it took less than 30 seconds to run, even faster than using the frame in 2016. Of course, you will need to include the frame if you need one that is different than the default. (See my article Introduction to T-SQL Window Functions<\/a> to learn more.) Figure 7 has the performance results.<\/p>\n
<\/p>\n
Figure 7: The results of using 2019 compatibility mode with running totals<\/p>\n
Batch Mode on Rowstore Processing<\/h2>\n
As you can see, this new batch mode on rowstore feature dramatically improved the performance of these two window function queries. My queries ran about 7 times faster, but your results will vary. How does batch mode work? It breaks the processing down into chunks of 900 values. SQL Server reserves this for queries with aggregation and sorts when scanning a large number of rows. It\u2019s one of several query processing improvements available with 2019 which together are called Intelligent Query Processing<\/a>.<\/p>\n
You may be wondering how to know if batch processing is used for a query. If you take a look at the execution plan, you can see the difference. Figure 8 is the execution plan for the subtotal query while in 2016 compatibility mode. To make this easier to see, I\u2019ve split the image into two. The top shows the left side of the plan, and the bottom is the right side of the plan.<\/p>\n
<\/p>\n
Figure 8: The execution plan for the subtotals with 2016 compatibility mode<\/p>\n
The first interesting thing to note is that parallelism is not used even though it seems like it would be useful in a query of 31 million rows. It might be possible to rewrite the query to persuade the optimizer to parallelize the query, but that\u2019s an article for another day.<\/p>\n
Also notice the Table Spool operators. These are the worktables that you saw in the STATISTICS IO<\/code> output. These are joined to the results using Nested Loops.<\/p>\n
If you open the properties of the Index Scan operator (Figure 9), you\u2019ll see that the storage is Rowstore, and the Actual Execution Mode is Row.<\/p>\n
<\/p>\n
Figure 9: The properties showing Row mode<\/p>\n
Now take a look at the execution plan in Figure 10 for the same query when in 2019 compatibility mode. Again, I have split it up to make it easier to read.<\/p>\n
<\/p>\n
Figure 10: The plan used in 2019 compatibility mode<\/p>\n
First, you will probably notice that this plan looks much simpler. There are no more worktables (Table Spools) and no need for the Nested Loops. There is also the new Window Aggregate operator. Hovering over the operator brings up the information shown in Figure 11.<\/p>\n
<\/p>\n
Figure 11: The Window Aggregate operator popup<\/p>\n
You can see that batch mode was used. By looking at the properties of the Index Scan in Figure 12, you will see that batch mode was used even though the index is stored in rowstore and not in columnstore.<\/p>\n
<\/p>\n
Figure 12: The Index Scan properties<\/p>\n
As mentioned earlier, batch mode on rowstore is reserved for queries of a large size that include certain aspects such as sorting or aggregates. I wondered where the tipping point was for this query, so I decide to create a copy of the bigTransactionHistory<\/em> with the same indexes, just a smaller number of random rows. I found that the tipping point was 131,072 rows for this query on this instance running in an Azure VM. It could be something entirely different for another situation.<\/p>\n
If you are using a monitoring tool, such as Redgate\u2019s SQL Monitor<\/a>, you can see the performance history of your queries and whenever the execution plans change. Wouldn\u2019t it be a nice surprise if a query suddenly started running 5 or 10 times more quickly as the data grew?<\/p>\n
Conclusion<\/h2>\n
Many organizations are not going to upgrade every time there is a new version of SQL Server available. When they do upgrade, they often skip versions. The Big Data Clusters feature is getting a lot of publicity, but not every shop will need it. I think everyone is going to appreciate the new query optimizations. Batch mode on rowstore is just one of many great features that will improve performance by just upgrading to 2019 when it\u2019s generally available.<\/p>\n\n\n
\n    
FAQs: Window aggregates in SQL Server 2019<\/h2>\n\n                        
1. Why are window aggregate functions slow in SQL Server?<\/h3>\n            
\n                
Window aggregate functions with ORDER BY in the OVER clause require SQL Server to maintain a running computation across sorted rows. Before SQL Server 2019, these operations used row mode processing – evaluating each row individually – which is CPU-intensive for large datasets. Accumulating aggregates (running totals) are particularly expensive because each row requires recalculating the aggregate from all preceding rows in the window frame.<\/p>\n            <\/div>\n                    
2. How much does SQL Server 2019 improve window aggregate performance?<\/h3>\n            
\n                
For accumulating aggregate queries (running totals using SUM with an ORDER BY in the OVER clause), SQL Server 2019 Batch Mode on Rowstore can improve elapsed time by 5\u20137\u00d7 compared to SQL Server 2017 row mode processing on the same query and data. The improvement is most pronounced for large datasets and ORDER BY-based window frames. Non-accumulating window aggregates (without ORDER BY in OVER) see smaller improvements.<\/p>\n            <\/div>\n                    
3. Does Batch Mode on Rowstore work for all window functions in SQL Server 2019?<\/h3>\n            
\n                
Batch Mode on Rowstore improves aggregate window functions (SUM, AVG, MIN, MAX, COUNT with OVER). It does not apply to ranking functions (ROW_NUMBER, RANK, DENSE_RANK) or analytical functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE) in the same way. The improvement is most significant for accumulating SUM queries (running totals) where the ORDER BY in the OVER clause forces sequential computation. Check your execution plan for Batch Hash Match operators to confirm batch mode is being used.<\/p>\n            <\/div>\n                    
4. What is the difference between window functions and window aggregates in SQL Server?<\/h3>\n            
\n                
Window functions is the umbrella term for all SQL functions that use an OVER clause. Window aggregates are the subset that applies aggregate operations – SUM, AVG, MIN, MAX, COUNT – within a window. Window ranking functions (ROW_NUMBER, RANK, DENSE_RANK) and window analytic functions (LAG, LEAD, NTILE) are also window functions but are not aggregates. SQL Server 2019’s Batch Mode on Rowstore performance improvement specifically targets the aggregate subset.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
SQL Server 2019 Batch Mode on Rowstore dramatically improves window aggregate performance – running totals, moving averages, and accumulating SUM queries run up to 7\u00d7 faster without a columnstore index. Benchmarked comparison with SQL Server 2017.…<\/p>\n","protected":false},"author":110218,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[143529,143531],"tags":[5842],"coauthors":[11292],"class_list":["post-83273","post","type-post","status-publish","format-standard","hentry","category-performance-sql-server","category-t-sql-programming-sql-server","tag-sql-monitor"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83273","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/110218"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=83273"}],"version-history":[{"count":13,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83273\/revisions"}],"predecessor-version":[{"id":109909,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83273\/revisions\/109909"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=83273"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=83273"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=83273"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=83273"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}