{"id":1250,"date":"2012-01-04T00:00:00","date_gmt":"2011-12-06T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/window-functions-in-sql-server-part-3-questions-of-performance\/"},"modified":"2021-08-16T15:02:02","modified_gmt":"2021-08-16T15:02:02","slug":"window-functions-in-sql-server-part-3-questions-of-performance","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/window-functions-in-sql-server-part-3-questions-of-performance\/","title":{"rendered":"SQL Server Window Function Performance: Execution Plans for Running Aggregates, LEAD, LAG, FIRST_VALUE, LAST_VALUE"},"content":{"rendered":"
\n

Are Window Functions as fast as the equivalent alternative SQL methods? In this article I’ll be exploring the performance of the window functions. After you read this article I hope you’ll be in a better position to decide whether it is really worth changing your application code to start using window functions.<\/p>\n

Before I start the tests, I would like to explain the most important factor that affects the performance of window functions. A window spool operator has two alternative ways of storing the frame data, with the in<\/em><\/strong>–<\/em><\/strong>memory<\/em><\/strong> worktable or with a disk<\/em><\/strong>–<\/em><\/strong>based<\/em><\/strong> worktable. You’ll have a huge difference on performance according to the way that the query processor is executing the operator.<\/p>\n

The in-memory worktable is used when you define the frame as ROWS and it is lower than 10000 rows. If the frame is greater than 10000 rows, then the window spool operator will work with the on-disk worktable.<\/p>\n

The on-disk based worktable is used with the default frame, that is, “range<\/em>…<\/em>“<\/em> and a frame with more than 10000 rows.<\/p>\n

It’s possible to check whether the window spool operator is using the on-disk worktable by looking at the results of the SET STATISTICS IO<\/strong>. Let’s see a small example of the window spool using the on-disk based worktable:<\/p>\n

USE tempdb\r\nGO\r\nIF OBJECT_ID('tempdb.dbo.#TMP')\u00a0\u00a0IS NOT NULL\r\n\u00a0\u00a0DROP TABLE #TMP\r\nGO\r\nCREATE TABLE #TMP (ID INT, Col1 CHAR(1), Col2 INT)\r\nGO\r\nINSERT INTO #TMP VALUES(1,'A', 5), (2, 'A', 5), (3, 'B', 5), (4, 'C', 5), (5, 'D', 5)\r\nGO\r\nSET STATISTICS IO ON\r\nSELECT *,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(Col2) OVER(ORDER BY Col1 RANGE UNBOUNDED PRECEDING) \"Range\" \u00a0\u00a0FROM #TMP\r\nSET STATISTICS IO OFF <\/pre>\n
\u00a0<\/p>\n
Table 'Worktable'. Scan count 5, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Worktable'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/pre>\n
\u00a0<\/p>\n
SET STATISTICS IO ON\r\nSELECT *,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(Col2) OVER(ORDER BY Col1 ROWS UNBOUNDED PRECEDING) \"Rows\" \u00a0\u00a0FROM #TMP\r\nSET STATISTICS IO OFF <\/pre>\n
\u00a0<\/p>\n
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Worktable'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. <\/pre>\n
As you can see, the worktable has some reads when you use a “range”, but it doesn’t happen with the in-memory worktable which is used when you use “Rows”.<\/p>\n
Optionally you also can look at the xEvent called window_spool_ondisk_warning<\/em>. <\/em>Fortunately on SQL Server 2012 we’ve a very nice interface that we can use to deal with xEvents. In SSMS, you can easily capture an event, and then look at the live data captured. Here you can see a screen-capture of this interface:<\/p>\n
\n\n\n\n
\"1403-image001.png\"<\/td>\n\"1403-image003.png\"<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
There are many aspects of the window spool operator that could be improved, I just opened some connect items about this and I’d appreciate it if you could take a minute or so to vote on this items.<\/p>\n