Like most things performance related in SQL Server, it depends.

Let me start with a common misconception on table variables.  I regularly see developers using @Temp table variables because “they are in memory”.  I cringe every time I hear this.  Why do I cringe? Because that’s not true.  Table variables are persisted just the same as #Temp tables.

The main performance affecting difference I see is the lack of statistics on table variables.  Since @table variables do not have statistics, there is very little for the optimizer to go on.  Most of the time I see the optimizer assume 1 row when accessing a table variable.  So why would Microsoft introduce these as opposed to using #temp tables for everything?  Since #temp tables have statistics, they will cause more statement recompilations.  Table variables will not cause recompilations since there is no variation in statistics on them.

I know there are a lot of other differences in the two; however, most of those are really feature differences (i.e. indexing etc).  I’m focussing this post on the lack of statistics because more often than not this is what causes unintended bad performance.

Example query

declare @AccountNumbers table (AccountNumber varchar(10))
insert into @AccountNumbers
select top 2000000 AccountNumber from CustomerTest with(nolock)
select count(1) from CustomerTest ct
inner join @AccountNumbers a on a.AccountNumber = ct.AccountNumber

Here is the output from set statistics io on and set statistics time on:

Note the high number of logical reads is quite high.  Also, note the runtime of this query: 726 seconds.

From looking at the plan, the query did an index seek on CustomerTest for every record returned from @AccountNumbers.   This plan would make sense if @AccountNumbers had very few rows.

If you look at the Estimated Number of Rows, the optimizer assumed 1 row.  If you look at the Actual Number of Rows, it was 2,000,000.  As a result the optimizer decided to perform 2,000,000 index seeks on the CustomerTest.test_index_0 index.  Is this the most efficient plan?  Let’s try this again, but this time we will use #temp tables.

create table #AccountNumbers (AccountNumber varchar(10))
insert into #AccountNumbers
select top 2000000 AccountNumber from CustomerTest with(nolock)
select count(1) from CustomerTest ct
inner join #AccountNumbers a on a.AccountNumber = ct.AccountNumber
And the results of this:

This query only runs in 11 seconds.  Let’s look at the execution plan:

As you can see, we are now scanning the CustomerTest table.  But scans are bad right?  Not always.  Accessing data via an index incurs overhead.  In a b-tree index you will perform one IO per level of the  b-tree plus an additional IO to access the leaf page. How do we know how many levels our index has? Use sys.dm_db_index_physical_stats to look up the “index_depth”.

select s.* from
sys.indexes i
cross apply
sys.dm_db_index_physical_stats(db_id(), i.object_id, i.index_id, NULL, NULL) s
where
i.name='test_index_0' -- this is the index name from the plan
and i.object_id = object_id('CustomerTest') -- this is the table name from the plan

We have an index depth of 3 for the index that was picked up. As a result, a lookup against the index will incur 3 IO’s per execution plus 1 additional for the leaf page lookup. If you look at the statistics IO output from the first query we executed, this pretty much holds true:

7,401,090 logical IO’s/2,000,000 index seeks = 3.7 logical IO’s per index seek

Why isn’t this 4 exactly?  Index misses and collocated values on leaf pages can account for this.  I won’t go too much deeper into b-tree indexes and how they work.  It starts to get academic at this point and I didn’t really care much for school.  There’s plenty of content you can search for on these fundamentals if you’re curious.

Sorry, I got a little side tracked, but I think that was necessary to explain the behavior we’re seeing.  Let’s take a second look at the plan. Look at the estimated number of rows in our #temp table:

Notice the estimated number of rows is now correct?  This is because #temp tables have stats.  As a result, the optimizer was able to determine that it would be more work to use the index 2,000,000 times rather than just scan the table.

If you look at the statistics IO output from our example with the #temp table, you’ll see we did much fewer logical IO’s because we didn’t use the index.  We only did 13,809 IO’s as opposed to 7,401,090 IO’s.  High logical IO’s will generally drive up CPU time and as a result will generally drive up your query runtime.  High logical IO’s can drive up runtimes even more if the data you’re scanning is large enough that it is not cached and it causes you to do physical IO’s.

I will admit, this example is a bit contrived; however, in the real world I commonly see the optimizer make bad decisions based on complex queries involving @table variables.

There are scenarios where the optimizer is able to estimate rows in @table variables, most of the time that doesn’t happen though.  I will save the details of when that does occur for a future blog post.

Should I always use #Temp tables?

No.  I don’t know how many times I fixed an application team’s performance issue by switching from table variables to #temp tables, and as a result management asks their team to make the change everywhere.  That’s not something I would ever recommend.  If you know you want the @table variable to be the driving source (i.e. first step) of the query, and the number of rows in the table variable is relatively small, and you are joining on an indexed value, you will probably be okay.  In fact you might be better off as a result of less recompilations.  I use @table variables when I’m dealing with a fairly simple query and joining to primary key indexes, and I know the number of rows is consistently small relative to the tables I’m joining to.

That being said, if you have a query that uses table variables and you are not happy with the performance, try using a #temp table instead.  Inspect the execution plan and run with set statistics io on and set statistics time on to compare performance between the two.

 

Watch us diagnose a query timeout with SQLGrease:

See how you can get query level wait events without Query Store:

Free you say?  How can that be?