![\"\"](\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/01\/image.png\")
<\/a><\/td>\n![\"\"](\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/01\/image1.png\")
<\/a><\/td>\n<\/tr>\nThe root cause of the problem was something I consider to be a coding anti-pattern, using a CTE with a window function inside of a view.\u00a0 As we\u2019ll see in this post, mixing the three of these together can result in significant performance problems from the need to materialize the intermediate results of the view before filtering can be applied.<\/p>\n
Setting up The Problem<\/h2>\n
To demonstrate the problem I\u2019ll first create a new table in AdventureWorks2012 to show the impact on performance as the number of rows increases in our test table. This table will be based on the HumanResources.EmployeePayHistory table and can be generated with the script attached to this post<\/a>.<\/p>\n With my table created, I\u2019ll create a view to return the current pay value for the employees as follows:<\/p>\n Then I\u2019ll create a stored procedure for my application to be able to get the current pay value for a specific employee by the BusinessEntityID for the employee.<\/p>\n If I run the stored procedure for any employee\u2019s BusinessEntityID the results will always be a single row.<\/p>\n For a table with 316 rows of pay history, the execution plan will have the following shape:<\/p>\n The execution plan shows that the tables were all scanned and the CTE was computed before the filter predicate on BusinessEntityID was applied. I can see this in the tool-tip for the Filter operator and by the number of input rows versus output rows for the Filter.<\/p>\n With a small number of rows, this\u00a0doesn’t\u00a0show up as being a significant problem as shown by the STATISTICS IO and STATISTICS TIME output for the execution.<\/p>\n The total execution time was just 4ms with 316 rows. If I scale up our test table to 11,916 rows, the plan changes to using a Merge Join with the EmployeePayHistoryEnlarged table, but I can still see that the CTE was fully computed before the filtering on BusinessEntityID was applied.<\/p>\n Performance wise, the stored procedure executes in 85ms and only uses 31ms of CPU time as shown by the execution statistics.<\/p>\n The real problems start to occur when the number of rows is increased to 116,316. The increased cost of computing the CTE entirely results in an execution plan that uses parallelism.<\/p>\n Testing this stored procedure on its own\u00a0wouldn’t\u00a0make you think that there was a performance problem lying in wait for a production system. Looking at the execution statistics, the procedure executed in 240ms, but the important item to look at is the shift in proportion of CPU time. Since the query used parallelism, I now have significantly more CPU time, by a factor of 3 in the 4vCPU VM I tested this on, which is the result of multiple threads processing the execution.<\/p>\n The problem with this specific procedure is that it\u00a0doesn’t\u00a0just get executed once, it gets executed thousands of times every couple of seconds, so the cumulative effect of the CPU time required to compute the CTE completely becomes significant enough to run a 32-core server at nearly 100% CPU utilization as shown in the images at the top of the post.<\/p>\n The problem only gets worse as the number of rows in the table increases, as shown by the execution plan and execution statistics for 1,160,316 rows.<\/p>\n Note:<\/strong> Depending on the hardware that the tests are run on, the plan might be different for these tests. For example the first plan below was generated with 4 vCPUs while the second plan using the Hash Join and Bitmap optimization was generated on the same server but with the -Pn startup parameter to start SQLOS with 24 logical schedulers, as showed in my post SQL Server and Soft NUMA<\/a>. In either case parallelism is used and the CPU time is roughly three times the execution time.<\/p>\n Fixing this problem is actually very simple; don\u2019t use the VIEW and move the WHERE clause for the BusinessEntityID column up into the CTE so that it can be used to filter the tables before computation occurs.<\/p>\n By pushing the WHERE clause up into the CTE, with 316 rows we get an execution plan that shows only a couple of rows are being returned from the tables during execution.<\/p>\n If I look at the tool-tip for the Clustered Index Seek on the EmployeePayHistoryEnlarged table, I\u2019ll see the Seek Predicate being applied to the BusinessEnityID column to filter the results.<\/p>\n The execution statistics for the rewritten procedure also shows the reduction in logical reads and the execution time.<\/p>\n I also get plan stability with this rewrite, regardless of the number of rows I have in our EmployeePayHistoryEnlarged table. At 1,160,316 rows, the execution plan is still the same, except that I can see more rows being returned from EmployeePayHistoryEnlarged to be able to identify the most recent pay rate.<\/p>\n The execution statistics also shows the reduction in logical reads, and the lower CPU time and execution time for the procedure.<\/p>\n Since I have three different coding aspects at play here, you are probably wondering what exactly was the root problem? If you\u00a0aren’t\u00a0wondering that, you\u00a0aren’t\u00a0alone, I\u00a0didn’t\u00a0either, but after doing a review of the article, my colleague Joe Sack pointed out that it was unclear where the problem actually was. I have to agree with Joe; so far I haven\u2019t really shown definitively where the problem is. Is it the fact that I have a CTE within a view? Let\u2019s find out. First I\u2019ll rewrite a new view that only contains a CTE, but still provides the same result as the original test query, using a sub-query to determine the MAX(RateChangeDate) from the EmployeePayHistoryEnlarged table for the BusinessEntityID and PayFrequency.<\/p>\n Now a simple test by running the new procedure and reviewing the execution plan for 1,160,316 rows:<\/p>\n\n
\r\nCREATE VIEW dbo.EmployeeCurrentPayData\r\nAS\r\n\r\nWITH PayChanges AS(\r\nSELECT\r\n per.BusinessEntityID,\r\n per.FirstName,\r\n per.LastName,\r\n per.MiddleName,\r\n emp.JobTitle,\r\n pay.Rate,\r\n pay.PayFrequency,\r\n ROW_NUMBER() OVER (PARTITION BY per.BusinessEntityID, JobTitle, PayFrequency ORDER BY pay.RateChangeDate DESC) AS RevisionID\r\nFROM Person.Person AS per\r\nINNER JOIN HumanResources.Employee AS emp\r\n ON per.BusinessEntityID = emp.BusinessEntityID\r\nLEFT JOIN HumanResources.EmployeePayHistoryEnlarged AS pay\r\n ON emp.BusinessEntityID = pay.BusinessEntityID)\r\nSELECT\r\n BusinessEntityID,\r\n FirstName,\r\n LastName,\r\n MiddleName,\r\n JobTitle,\r\n Rate,\r\n PayFrequency\r\nFROM PayChanges\r\nWHERE RevisionID = 1;\r\n\r\nGO\r\n<\/pre>\n
\r\nCREATE PROCEDURE dbo.GetEmployeeCurrentPay_View (@BusinessEntityID INT)\r\nAS\r\nBEGIN\r\n\r\nSELECT\r\n BusinessEntityID,\r\n FirstName,\r\n LastName,\r\n MiddleName,\r\n JobTitle,\r\n Rate,\r\n PayFrequency\r\nFROM dbo.EmployeeCurrentPayData\r\nWHERE BusinessEntityID = @BusinessEntityID;\r\n\r\nEND\r\nGO\r\n<\/pre>\n
\r\nDECLARE @BusinessEntityID INT = 250;\r\nEXECUTE dbo.GetEmployeeCurrentPay_View @BusinessEntityID;\r\n<\/pre>\n
<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n\r\nSQL Server parse and compile time:\r\n CPU time = 14 ms, elapsed time = 14 ms.\r\n\r\nTable '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 'Person'. Scan count 0, logical reads 897, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Employee'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'EmployeePayHistoryEnlarged'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\n\r\n SQL Server Execution Times:\r\n CPU time = 0 ms, elapsed time = 4 ms.\r\n<\/pre>\n
<\/a><\/p>\n\r\nSQL Server parse and compile time:\r\n CPU time = 0 ms, elapsed time = 9 ms.\r\n\r\nTable 'EmployeePayHistoryEnlarged'. Scan count 1, logical reads 86, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Person'. Scan count 0, logical reads 897, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Employee'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\n\r\n SQL Server Execution Times:\r\n CPU time = 31 ms, elapsed time = 85 ms.\r\n <\/pre>\n
<\/a><\/p>\n\r\nSQL Server parse and compile time:\r\n CPU time = 16 ms, elapsed time = 16 ms.\r\n\r\nTable 'Employee'. Scan count 25, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Person'. Scan count 25, logical reads 319, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'EmployeePayHistoryEnlarged'. Scan count 25, logical reads 949, 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 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\n\r\n SQL Server Execution Times:\r\n CPU time = 643 ms, elapsed time = 240 ms.\r\n<\/pre>\n
<\/a><\/p>\n\r\nSQL Server parse and compile time:\r\n CPU time = 0 ms, elapsed time = 0 ms.\r\n\r\nTable 'EmployeePayHistoryEnlarged'. Scan count 290, logical reads 7416, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Person'. Scan count 0, logical reads 923, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Employee'. Scan count 5, logical reads 22, 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 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\n\r\n SQL Server Execution Times:\r\n CPU time = 4336 ms, elapsed time = 1788 ms.\r\n<\/pre>\n
<\/a><\/p>\n\r\nSQL Server parse and compile time:\r\n CPU time = 23 ms, elapsed time = 23 ms.\r\n\r\nTable 'Employee'. Scan count 25, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Person'. Scan count 25, logical reads 319, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'EmployeePayHistoryEnlarged'. Scan count 290, logical reads 7656, 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 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 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\n\r\n SQL Server Execution Times:\r\n CPU time = 6209 ms, elapsed time = 2235 ms.\r\n<\/pre>\n
How do you fix this?<\/h2>\n
\r\nCREATE PROCEDURE dbo.GetEmployeeCurrentPay_NoView (@BusinessEntityID INT)\r\nAS\r\nBEGIN\r\n\r\nWITH PayChanges AS(\r\nSELECT\r\n per.BusinessEntityID,\r\n per.FirstName,\r\n per.LastName,\r\n per.MiddleName,\r\n emp.JobTitle,\r\n pay.Rate,\r\n pay.PayFrequency,\r\n ROW_NUMBER() OVER (PARTITION BY per.BusinessEntityID, JobTitle, PayFrequency ORDER BY pay.RateChangeDate DESC) AS RevisionID\r\nFROM Person.Person AS per\r\nINNER JOIN HumanResources.Employee AS emp\r\n ON per.BusinessEntityID = emp.BusinessEntityID\r\nLEFT JOIN HumanResources.EmployeePayHistoryEnlarged AS pay\r\n ON emp.BusinessEntityID = pay.BusinessEntityID\r\nWHERE per.BusinessEntityID = @BusinessEntityID)\r\nSELECT\r\n BusinessEntityID,\r\n FirstName,\r\n LastName,\r\n MiddleName,\r\n JobTitle,\r\n Rate,\r\n PayFrequency\r\nFROM PayChanges\r\nWHERE RevisionID = 1;\r\n\r\nEND\r\nGO\r\n<\/pre>\n
<\/a><\/p>\n<\/a><\/p>\n\r\nSQL Server parse and compile time:\r\n CPU time = 4 ms, elapsed time = 4 ms.\r\n\r\nTable 'EmployeePayHistoryEnlarged'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Person'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Employee'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\n\r\n SQL Server Execution Times:\r\n CPU time = 0 ms, elapsed time = 4 ms.\r\n<\/pre>\n
<\/a><\/p>\n\r\nSQL Server parse and compile time:\r\n CPU time = 0 ms, elapsed time = 0 ms.\r\n\r\nTable 'EmployeePayHistoryEnlarged'. Scan count 1, logical reads 25, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Person'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\nTable 'Employee'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\r\n\r\n SQL Server Execution Times:\r\n CPU time = 16 ms, elapsed time = 152 ms.\r\n<\/pre>\n
What was Actually the Problem?<\/h2>\n
\r\n\/* Drop the view before creating it *\/\r\nIF OBJECT_ID(N'EmployeeCurrentPayData_NoWF') IS NOT NULL\r\nBEGIN\r\n DROP VIEW dbo.EmployeeCurrentPayData_NoWF;\r\nEND\r\nGO\r\n\r\n\/* Create the problem view for the tests *\/\r\nCREATE VIEW dbo.EmployeeCurrentPayData_NoWF\r\nAS\r\nWITH PayChanges AS(\r\nSELECT\r\n per.BusinessEntityID,\r\n per.FirstName,\r\n per.LastName,\r\n per.MiddleName,\r\n emp.JobTitle,\r\n pay.Rate,\r\n pay.PayFrequency\r\nFROM Person.Person AS per\r\nINNER JOIN HumanResources.Employee AS emp\r\n ON per.BusinessEntityID = emp.BusinessEntityID\r\nLEFT JOIN HumanResources.EmployeePayHistoryEnlarged AS pay\r\n ON emp.BusinessEntityID = pay.BusinessEntityID\r\nWHERE RateChangeDate = (SELECT MAX(RateChangeDate)\r\n FROM HumanResources.EmployeePayHistoryEnlarged AS pay2\r\n WHERE pay.BusinessEntityID = pay2.BusinessEntityID\r\n AND pay.PayFrequency = pay.PayFrequency)\r\n)\r\nSELECT\r\n BusinessEntityID,\r\n FirstName,\r\n LastName,\r\n MiddleName,\r\n JobTitle,\r\n Rate,\r\n PayFrequency\r\nFROM PayChanges;\r\nGO\r\n\r\n\/* Drop the stored procedure using the view before creating it *\/\r\nIF OBJECT_ID(N'GetEmployeeCurrentPay_ViewNoWF') IS NOT NULL\r\nBEGIN\r\n DROP PROCEDURE dbo.GetEmployeeCurrentPay_ViewNoWF;\r\nEND\r\nGO\r\n\r\n\/* Create the stored procedure that uses the view to tes the problem *\/\r\nCREATE PROCEDURE dbo.GetEmployeeCurrentPay_ViewNoWF (@BusinessEntityID INT)\r\nAS\r\nBEGIN\r\n SELECT\r\nBusinessEntityID,\r\n FirstName,\r\n LastName,\r\n MiddleName,\r\n JobTitle,\r\n Rate,\r\n PayFrequency\r\n FROM dbo.EmployeeCurrentPayData_NoWF\r\n WHERE BusinessEntityID = @BusinessEntityID;\r\nEND\r\nGO\r\n<\/pre>\n