Table Variables and performance

Question

Table Variables and performance

Bryant McClellan

SSCarpal Tunnel

Points: 4397
More actions
October 8, 2008 at 5:07 am

#213845

When SQL determines that there is not enough memory to store a table variable in memory, it is written to tempdb instead. When is this decision made? Is it after the table grows to large for the available memory, which causes SQL to shift gears and move the table, or is the determination made at execution plan time? And if this happens on a regular basis, how can you find out? Where do you look to see that this event occurs? And does it make sense, if this change to tempdb happens most of the time, to instead issue DDL statements to create the temp table in tempdb in the first place?
Similarly, is there a penalty to using a CTE if the resultset is too large for memory? And would a DDL-defined temp table be the better choice?
------------
Buy the ticket, take the ride. -- Hunter S. Thompson

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

The decision is not made during the generation of the execution plan. It can only be made while data is being added to your table variable. If you do not often put too much data into your table variable, the overhead of the database engine deciding to spill over to TempDB is not too bad. However, remember that you can index temp tables, so if you have significant amounts of records, it may be better to just use a temp table.

If you are really in need of asking these questions, you should test the code both ways. It will be the only way to verify the best solution in your particular situation.

As for CTE's - they don't actually cache data. Excluding recursive CTE's, a CTE is handled by the optimizer exactly the same way a sub-query would be. It really gets translated into it's base tables. If you reference the same CTE multiple times in your query, the engine does not cache the data and re-use it. It does exactly what it would have done if you simply typed the same sub-query in multiple times.

Bryant McClellan SSCarpal Tunnel Points: 4397 More actions · Answer 2

Michael,

Thanks for the reply. I can see that testing is the only sure-fire method to compare the performance, but there must be some indication that the switch from memory to tempdb has been made. I've inherited support for this application, notorious for its poor performance, and I'm trying to get my arms around all the targets of opportunity to determine where to strike next. That is why I want to determine if the switch to tempdb is happening before dedicating time that I can use in other tuning methods.

------------
Buy the ticket, take the ride. -- Hunter S. Thompson

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

While it is running, look for tables in tempdb:

SELECT * FROM TempDB..Sysobjects WHERE Type = 'u'

A table variable usually has to get pretty big before it spills over into TempDB.

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 4

If you're using table variables that are more than a few rows in size, switch them to temp tables. Except for a primary key, table variables have not statistics where as temp tables do. This means that they are treated as one row tables for all execution plans, which can lead to some seriously bad performance.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 5

Grant Fritchey (10/8/2008)
If you're using table variables that are more than a few rows in size, switch them to temp tables. Except for a primary key, table variables have not statistics where as temp tables do. This means that they are treated as one row tables for all execution plans, which can lead to some seriously bad performance.

Also, if joining the table variable to other tables - especially large tables - switch to temp table, EVEN IF the number of rows in the table variable is small. The lack of stats in the table variable can lead to a suboptimal plan for the join query.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 6

G Bryant McClellan (10/8/2008)
Michael,
Thanks for the reply. I can see that testing is the only sure-fire method to compare the performance, but there must be some indication that the switch from memory to tempdb has been made. I've inherited support for this application, notorious for its poor performance, and I'm trying to get my arms around all the targets of opportunity to determine where to strike next. That is why I want to determine if the switch to tempdb is happening before dedicating time that I can use in other tuning methods.

Ummm.... both table variables and temp tables start out in memory and "jump" to TempDB when they overcome some point in memory. If your table variables are large enough where you are concerned that they are spooling out to TempDB, then you should probably be using temp tables instead.

For many more reasons than that (especially ease of troubleshooting), the only place where I'll use a table variable is in a UDF... and then only because I can't use a temp table there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 7

Michael Earl (10/8/2008)
While it is running, look for tables in tempdb:
SELECT * FROM TempDB..Sysobjects WHERE Type = 'u'
A table variable usually has to get pretty big before it spills over into TempDB.

Actually, so does a Temp Table. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Michael Earl (10/8/2008)
While it is running, look for tables in tempdb:
SELECT * FROM TempDB..Sysobjects WHERE Type = 'u'

Temp tables and table variables are added to the TempDB system tables when they're created, not when they spill to disk.

Easy to demonstrate (run on a server without other usage, so other people's temp tables don't mess up the test)

DECLARE @BatchStart DATETIME

SET @BatchStart = GETDATE()

DECLARE @test1 TABLE (id int)

CREATE TABLE #Test2 (id int)

SELECT * FROM TempDB.sys.tables where create_date >= @BatchStart

DROP TABLE #Test2

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Grant Fritchey (10/8/2008)
Except for a primary key, table variables have not statistics where as temp tables do. This means that they are treated as one row tables for all execution plans, which can lead to some seriously bad performance.

Even the PK of a table var doesn't keep stats. Run the below code with the exec plan on and look at the estimated and actual rows on the index seek. When run in master on my server, I get Estimated rows 1, actual rows 191

DECLARE @test-2 TABLE (

id INT IDENTITY PRIMARY KEY,

name VARCHAR(50)

)

INSERT INTO @test-2 (name)

SELECT name FROM sys.columns

SELECT * from @test-2 WHERE id between 10 and 200

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 10

That is something I was curious about but never pursued. Thanks for the info.

So then, would you say, there's pretty much no reason to ever use a table variable (except, as Jeff says, in UDF's where you have to)?

I've been recommending against them for years, but that seems to take away the last vestige of redemption.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

They have uses. The main one for me is error/progress logging in procs. If all that you're doing to a table var is inserting and then selecting the entire table without joins, the bad stats isn't a problem because the plan's simple, and avoiding having pieces of the proc recompiling just because the logging table's had a couple rows added to it is good.

Also, because they don't take part in transactions they will keep any logged rows even if the tran is rolled back.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 12

GilaMonster (10/10/2008)
...Also, because they don't take part in transactions they will keep any logged rows even if the tran is rolled back.

Another use of table variables - from Gail's last point: in audit triggers where it is required that insertions be audited even if txn is rolled back.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 13

Except for things like what Marios pointed out, I avoid table variables like the plague not only because of their "single record" execution plan, but because they are also more difficult when troubleshooting.

Remember the "old" days when if you wanted to see what was in an array, you had to write a bit of code into what you were testing on so you could see it's contents? Same goes with table variables because the don't persist in SMS at the end of a run. You also can't start the run part way if the table variable was populated above where you're trying to troubleshoot. With Temp Tables, you can because they persist.

Some will be shocked and mortified at what I'm about to say, but even if I find that a temp table causes a large batch job to be several seconds slower (which hardly ever happens... it's usually the other way around), I'll still use Temp Tables over table variables just for that reason... ease of troubleshooting when "under the gun". 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 14

In my experience, the table variable flushes to disk when approx two pages has been filled.

N 56°04'39.16"
E 12°55'05.25"