26 performance tuning questions and solutions, Are Table Variables as Good as Temporary Tables in SQL, Having performance issues with table variables? SQL, How to tune SQL queries, Improvements in Table Variables and Temporary Tables in, Improving SQL Server performance when using table, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, MythBusting–“Table variables have no statistics, MythBusting–“Table variables have no statistics” - SQL, New Trace Flag Aimed at Fixing Table Variable Performance, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL SERVER – Difference TempTable and Table Variable, SQL SERVER Interview questions, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL Server Statistics: Problems and Solutions, SQL SERVER Tips, SQL Temporary Tables vs. Table Variables, SQL Tuning Overview, table (Transact-SQL) - MSDN - Microsoft, Table variable for large tables, Table Variables in SQL Server 2014, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, What's the difference between a temp table and table variable
Table Variable – Estimated no of rows = 1 | No Statistics?
SQL Server treats table variable just like it treats any other variable and it estimates them that they have just one row. Even if SQL Server just inserted 1000 rows in them.
According to http://msdn.microsoft.com/en-us/library/ms175010.aspx, Table variables do not have distribution statistics, they will not trigger recompiles. Therefore, in many cases, the optimizer will build a sub optimal query plan on the assumption that the table variable has 1 rows. For this reason, you should be cautious about using a table variable if you expect a larger number of rows (more than 100). Temp tables are better in these cases.
They came in SQL Server 2000. The original intention was to reduce recompiles. As time passes more and more people supported it and many users use to populate huge number of rows and then join with other tables. Just like what we are doing in our example below.
Let’s prove with an example
-- DECLARE @TabVar TABLE ( BillId INT , GlNm VARCHAR(1) , Amt INT ) INSERT INTO @TabVar SELECT * FROM [dbo].[BillGLNumber] SELECT * FROM @TabVar RIGHT JOIN NumbersTable t ON BillId = t.Number --
Now let’s execute the above batch and check the execution plan. In the below execution plan it is clearly visible that the estimated number of rows and actual number of rows are not matching. Here the number are very small hence doesn’t matter much but consider if you have 100K rows.
Now let’s add a small variation to the above by adding option recompile
-- DECLARE @TabVar TABLE ( BillId INT , GlNm VARCHAR(1) , Amt INT ) INSERT INTO @TabVar SELECT * FROM [dbo].[BillGLNumber] SELECT * FROM @TabVar RIGHT JOIN NumbersTable t ON BillId = t.Number OPTION(RECOMPILE) --
Execution plan analysis of the above query is –
Okay so this time our estimates are bang on target and we have same estimated number of rows and actual number of rows.
Please note that SQL Server does not support statistics or track number of rows in a table variable while compiling a query plan.
In SQL Server 2012 a new trace flag 2453 which can help when you are inserted large number of rows in a table variable and joins with other tables. So if this flag is enabled then SQL Server recompiles the statements where table variable is used. And now once the SQL Server knows the row count it can produce a better rather than a sub optimal plan in previous cases.
Well the good news is SQL Server has documented this. The URL is – http://support.microsoft.com/kb/2952444
The article says below things-
This trace flag differs from OPTION (RECOMPILE) in two main aspects.
(1) It uses the same row count threshold as other tables. The query does not need to be compiled for every execution unlike OPTION (RECOMPILE). It would trigger recompile only when the row count change exceeds the predefined threshold.
(2) OPTION (RECOMPILE) forces the query to peek parameters and optimize the query for them. This trace flag does not force parameter peeking.
Note this trace flag must be ON at runtime. You cannot use this trace flag with QUERYTRACEON. This trace flag must be used with caution because it can increase number of query recompiles which could cost more than savings from better query optimization.
Since I don’t have SQL 2012 SP2 can’t give you practical example. Please follow this link for detailed example –
Thanks for reading!
Pawan Kumar Khowal