SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Variables and performance


Table Variables and performance

Author
Message
Bryant McClellan
Bryant McClellan
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1797 Visits: 548
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
Michael Earl-395764
Michael Earl-395764
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21723 Visits: 23078
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
Bryant McClellan
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1797 Visits: 548
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
Michael Earl-395764
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21723 Visits: 23078
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
Grant Fritchey
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145995 Visits: 33199
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Marios Philippopoulos
Marios Philippopoulos
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21746 Visits: 3773
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
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340203 Visits: 42634
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340203 Visits: 42634
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. Smile

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)

Group: General Forum Members
Points: 370694 Visits: 46957
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)

Group: General Forum Members
Points: 370694 Visits: 46957
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 TABLE (
id INT IDENTITY PRIMARY KEY,
name VARCHAR(50)
)

INSERT INTO @Test (name)
SELECT name FROM sys.columns

SELECT * from @Test 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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search