Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Table Variables and performance Expand / Collapse
Author
Message
Posted Wednesday, October 08, 2008 5:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:25 AM
Points: 846, Visits: 466
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
Post #582450
Posted Wednesday, October 08, 2008 5:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #582461
Posted Wednesday, October 08, 2008 5:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:25 AM
Points: 846, Visits: 466
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
Post #582464
Posted Wednesday, October 08, 2008 5:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #582473
Posted Wednesday, October 08, 2008 7:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 14,835, Visits: 27,311
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #582555
Posted Thursday, October 09, 2008 3:40 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:42 PM
Points: 1,851, Visits: 3,575
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.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #583626
Posted Thursday, October 09, 2008 4:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 36,002, Visits: 30,294
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #583643
Posted Thursday, October 09, 2008 4:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 36,002, Visits: 30,294
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #583644
Posted Friday, October 10, 2008 2:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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

Post #583818
Posted Friday, October 10, 2008 2:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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

Post #583819
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse