SQL Table Variable Deferred Compilation in SQL Server 2019
SQL Table Variable Deferred Compilation in SQL Server 2019
Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolSQL Table Variable Deferred Compilation in SQL Server 2019
November 25, 2019 by Rajendra Gupta In an article, An overview of the SQL table variable, we explored the usage of SQL table variables in SQL Server in comparison with a temporary table. Let’s have a quick recap of the table variable: We can define a table variable and use it similar to a temporary table with few differences. The table variable scope is within the batch The storage location of the table variable is in the TempDB system database SQL Server does not maintain statistics for it. Therefore, it is suitable for small result sets It does not participate in explicit transactions We cannot define indexes on table variables except primary and unique key constraintsIssues with SQL table variables
Let me ask a few questions to set agenda for this article: Have you seen any performance issues with queries using table variables? Do you see any issues in the execution plan of a query using these table variables? Go through the article for getting the answer to these questions in a particular way. Once we define a SQL table variable in a query, SQL Server generates the execution plan while running the query. SQL Server assumes that the table variable is empty. We insert data in a table variable during runtime. We might have an optimized execution plan of the query because SQL Server could not consider the data in the table variable. It might cause performance issues with high resource utilization. We might have a similar execution plan even if we have a different number of rows in each execution. Let’s view the table variable issue in SQL Server 2017 with the following steps: Set Statistics IO ON and Set Statistics Time On to capture query IO and time statistics. It will help in performing a comparison of multiple query executions Define a table variable @Person with columns [BusinessEntityID] ,[FirstName] and [LastName] Insert data into table variable @person from the [Person] table in the AdventureWorks sample database Join the table variable with another table and view the result of the join operation View the actual execution plan of the query 123456789101112131415 SET STATISTICS IO ON;SET STATISTICS TIME ON;DECLARE @Person TABLE([BusinessEntityID] INT, [FirstName] VARCHAR(30), [LastName] VARCHAR(30));INSERT INTO @Person SELECT [BusinessEntityID], [FirstName], [LastName] FROM [AdventureWorks].[Person].[Person];SELECT *FROM @Person P1 JOIN [AdventureWorks].[Person].[Person] P2 ON P1.[BusinessEntityID] = P2.[BusinessEntityID]; Note: In this article, I use ApexSQL Plan for viewing execution plans. In the above screenshot, we can note the following. Estimated number of rows: 1 The actual number of rows: 19,972 Actual/estimated number of rows: 1997200% Really! You can imagine the difference in the calculations. SQL Server missed the estimation of actual rows counts by 1997200% for the execution plan. You can see that SQL Server could not estimate the actual number of rows. The estimated number of rows is nowhere close to actual rows. It is a big drawback that does not provide an optimized execution plan. Let’s look at statistics in the message tab of SSMS. It took 59,992 logical reads (59916+76) for this query: In SQL Server 2012 SP2 or later versions, we can use trace flag 2453. It allows SQL table variable recompilation when the number of rows changes. Execute the previous query with trace flag and observe query behavior. We can enable this trace flag at the global level using DBCC TRACEON(2453,-1) command as well: 12345678910111213141516 DBCC TRACEON(2453);SET STATISTICS IO ON;SET STATISTICS TIME ON;DECLARE @Person TABLE([BusinessEntityID] INT, [FirstName] VARCHAR(30), [LastName] VARCHAR(30));INSERT INTO @Person SELECT [BusinessEntityID], [FirstName], [LastName] FROM [AdventureWorks].[Person].[Person];SELECT *FROM @Person P1 JOIN [AdventureWorks].[Person].[Person] P2 ON P1.[BusinessEntityID] = P2.[BusinessEntityID]; In the following screenshot of the execution plan after enabling the trace flag 2453, we can note the following: Estimated number of rows: 19,972 The actual number of rows: 19,972 Actual/estimated number of rows: 100% It improves the IO and Time statistics as well as compared to previous runs without the trace flag: Trace flag 2453 works similar to adding a query hint OPTION (RECOMPILE). The difference between the trace flag and OPTION(RECOMPILE) is the recompilation frequency. Let’s execute the previous query with the query hint OPTION (RECOMPILE) and view the actual execution plan: 12345678910111213141516 SET STATISTICS IO ON;SET STATISTICS TIME ON;DECLARE @Person TABLE([BusinessEntityID] INT, [FirstName] VARCHAR(30), [LastName] VARCHAR(30));INSERT INTO @Person SELECT [BusinessEntityID], [FirstName], [LastName] FROM [AdventureWorks].[Person].[Person];SELECT *FROM @Person P1 JOIN [AdventureWorks].[Person].[Person] P2 ON P1.[BusinessEntityID] = P2.[BusinessEntityID] OPTION (RECOMPILE); We can see that using query hint also improves the estimated number of rows for the SQL table variable statement: Trace flag recompiles the query once a predefined (internal) threshold changes for several rows while OPTION(RECOMPILE) compiles on each execution.SQL Table Variable Deferred Compilation in SQL Server 2019
SQL Server 2017 introduced optimization techniques for improving query performance. These features are part of the Intelligent Query Processing (IQP) family. In the following, image from SQL Server 2019 technical whitepaper, we can see new features introduced in SQL 2019: SQL Server 2019 introduces the following new features and enhancements: Table variable deferred compilation Batch mode on a Row store T-SQL scalar UDF Inlining Approximate Count Distinct Row mode memory grant feedback Let’s explore the Table variable deferred compilation feature in SQL Server 2019. You can refer to SQL Server 2019 articles for learning these new features.SQL Table variable deferred compilation
Once SQL Server compiles a query with a table variable, it does not know the actual row count. It uses a fixed guess of estimated one row in a table variable. We have observed this behavior in the above example of SQL Server 2017. SQL Server 2019 table variable deferred compilation, the compilation of the statement with a table variable is deferred until the first execution. It helps SQL Server to avoid fix guess of one row and use the actual cardinality. It improves the query execution plan and improves performance. To use this feature, we should have a database with compatibility level 150 in SQL Server 2019. If you have a database in another compatibility level, we can use the following query for changing it: 1 ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 150; We can use sp_helpdb command for verifying database compatibility level: Note: In this article, I use SQL Server 2019 general availability release announced on 4th November 2019 at Microsoft Ignite. You should download the SQL 2019 General availability release and restore the AdventureWorks database before proceeding further with this article. We do not have a SQL 2019 version of this AdventureWorks database. You should change the database compatibility level after restoration. Execute the earlier query (without trace flag) in SQL Server 2019 database and view the actual execution plan. We do not require enabling any trace flag for SQL table variable deferred compilation. In the below screenshot, we can note the following: Estimated number of rows: 19,972 The actual number of rows: 19,972 Actual/estimated number of rows: 100% Bang on! The estimated and actual numbers of rows are the same. If we look at the statistics in SQL Server 2019, we can see it took 43,783 logical reads in comparison with 59,992 logical reads in SQL 2017. You might see more performance benefits while working with complex data and queries. My point is to show that SQL Server optimizer can match the estimation rows accurately: In the default behavior, it eliminates the requirement of: Trace flag 2453 We can skip adding OPTION (RECOMPILE) at the statement level. It avoids any code changes, and SQL Server uses deferred compilation by default We do not require explicit plan hintsConclusion
In this article, we explored the issues in query optimization with SQL table variables in SQL Server 2017 or before. It also shows the improvements in SQL Server 2019 using table variable deferred compilation. You might also face these issues. I would suggest downloading the general availability release and preparing yourself with enhancements and new features of SQL 2019.Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022