Hi Guys,
After the success of the previous post where we compared temp table and table variable, today I wanted to go back to talking to you again about table variables.
This time i will tell you about their history and about their evolution over time.
We will also talk about the trace flag 2453 which will allow us to have higher performance in some cases.
But now let's go!
Table Variables: The history of...
Tables of the table variable type date back to the days of SQL Server 2000.
Created to handle temporary data faster than Temp Tables, it was chosen to limit recompilations and not provide them with statistics.
In fact, the idea turned out to be successful ... halfway!
In fact, if you store only a few lines in a Variable Table, you will have excellent performance.
But if you need to remember happier that performance will drop.
They will drop precisely because of the lack of statistics.
So over time the Table Variables got a bad name!
Without statistics for SQL Server a Table Variables always contains only one row!
This leads the SQL Server optimizer to choose an incorrect execution plan when the rows contained in the table are many more!
But let's see it in practice!
So let's create our table variable with these T-SQL commands:
DECLARE @T TABLE(ID INT PRIMARY KEY, NAME SYSNAME NOT NULL UNIQUE);
INSERT @T SELECT TOP (1000) [OBJECT_ID], NAME FROM SYS.ALL_OBJECTS;
SELECT T.ID, T.NAME
FROM @T AS T;
Executing them by viewing the actual execution plan we immediately see that the estimated number of rows is 1 even if the table of rows in contains many more and this is a big problem!
Now let's take a step forward in our analysis.
Let's first see that SQL Server is not actually adopting the correct execution plan.
Then we introduce the workaround which is commonly used to solve.
For this we take our temporary table and JOIN with itself:
DECLARE @T TABLE(ID INT PRIMARY KEY, NAME SYSNAME NOT NULL UNIQUE);
INSERT @T SELECT TOP (2000) [OBJECT_ID], NAME FROM SYS.ALL_OBJECTS;
SELECT T.ID, T.NAME
FROM @T AS T
JOIN @T AS T2 ON T.NAME = T2.NAME
Let's run it and observe the execution plan:
Hey what happens?
The optimizer is convinced that the @T table contains only one row and for this reason it uses the nested loop as the JOIN operator.
The nested JOIN is the slowest JOIN operator of the three that SQL Server can use.
In nested JOIN the first table in JOIN will act as the Master table while the second table in JOIN will act as Detail.
So, for each of the 2000 rows in the Master table, it will search for the searched data on the detail table.
In this way, 4018 pages of memory are read and a time of 342 ms is required.
The situation in this case is solved by forcing the recompilation of the Query using the RECOMPILE option. (This is the -bad- workaround typically you can adopt to solve)
But let's try it..
We write:
DECLARE @T TABLE(ID INT PRIMARY KEY, NAME SYSNAME NOT NULL UNIQUE);
INSERT @T SELECT TOP (2000) [OBJECT_ID], NAME FROM SYS.ALL_OBJECTS;
SELECT T.ID, T.NAME
FROM @T AS T
JOIN @T AS T2 ON T.NAME = T2.NAME
OPTION (RECOMPILE) When we run it we get:
What you notice?
The estimated number of rows contained in our table is now 2000 rows.
The JOIN operator used is now the Merge Join which is the fastest JOIN operator.
This is because the data of the two tables are in this case read only once and then "merged" through a hash function.
Thus 36 pages are read in place 4018!
The downsides of adding the RECOMPILE option to our Query are:
- If the Query is embedded into an application, we need to change the source of our application, which is not always possible.
- Repeatedly forcing the recompile of the query results in unnecessary consumption of resources.
The trace flag 2453
Let's go back to our history.
We'll have to wait until the SQL Server 2012 second service pack (SP2) and SQL Server 2014 cumulative update package 3 are released to see some news!
Both updates contained the patch 2952444 with the original title "Poor performance when you use table variables in SQL Server 2012 or SQL Server 2014"
With this patch the trace flag 2453 was introduced.
By enabling it, the SQL Server optimizer will be able to get a better estimate of cardinality.
Let's verify it with a test!
DBCC TRACEON(2453)
DECLARE @T TABLE(ID INT PRIMARY KEY, NAME SYSNAME NOT NULL UNIQUE);
INSERT @T SELECT TOP (2000) [OBJECT_ID], NAME FROM SYS.ALL_OBJECTS;
SELECT T.ID, T.NAME
FROM @T AS T
JOIN @T AS T2 ON T.NAME = T2.NAME
DBCC TRACEOFF(2453)
If we look at the execution plan we see that the number of estimated rows is not 1 but the actual number of rows contained in the table.
As a JOIN operator we have the Merge Join and the pages read are only 36 exactly as when we applied the RECOMPILE option.
There are two differences.
While with the recompile the Query is recompiled every time and then SQL Server returns to re-evaluate the execution plan by choosing the best one, with the trace flag this does not happen.
A smarter approach is taken..the Query is recompiled only when the number of rows changes beyond the predefined threshold and the parameters are not re-evaluated.
My personal advice is therefore to activate this flag never globally at the start of the instance but to use it only in some targeted queries and only after evaluating that it actually benefits. Alternatively, consider that the recompile option is beneficial instead!
SQL Server 2019
And so we come to the current SQL Server 2019 ...but we will talk about this and the news that SQL server 2019 brings us in the next post!
That's all for today, you just have to wait for the next post and stay tuned!
Some small income is always welcome so I honestly said yes to adsense.
Luca Biondi @ SQLServerPerformance blog!
Next post:Previous post: Temp Tables (#) Vs. Table Variables (@): which one to use, which solution is the fastest?