Table Variables and performance

Question

Table Variables and performance

Viewing 12 posts - 16 through 26 (of 26 total)

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

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

Peso (10/12/2008)
In my experience, the table variable flushes to disk when approx two pages has been filled.

Interesting. How are you monitoring that?

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

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 2

Mostly with SQL Profiler and using the RowCount for 2005 and later, or integerdata for sql server 2000.

Last year I was involved in a large project for performance tuning and what I found out was that for about two pages, the table variable flushed to disk. The writes in SQL profiler went higher when filling a table variable for more than 2 pages.

A simple 5-column table ( 4 guids and 1 datetime ) can hold about 114 records with fillfactor 100.

What was seen in profiler was that writes did not match the amount of records written to table variable when table was populated with 200+ records.

What earlier took about 1.5 seconds now took almost 1 minute. Changing the table variable to a temp table solved the problem permanently, and after the change the writes matched the number of records written fairly linear, no matter the numer of records written to temp table.

But then again, this was measured under SQL Server 2000. I haven't made any real tests for SQL Server 2005 to find the threshold there.

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

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 3

Jeff Moden (10/9/2008)

G Bryant McClellan (10/8/2008)
Michael,
Ummm.... both table variables and temp tables start out in memory and "jump" to TempDB when they overcome some point in memory.

Really?

I was under the impression that temp tables always are written to disk because they are affected by transaction operations, which table variables are not.

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

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

Peso (10/12/2008)
I was under the impression that temp tables always are written to disk because they are affected by transaction operations, which table variables are not.

The changes will be logged to the tran log, and that's just to allow for rollback as TempDB never needs to be recovered. Doesn't mean that the pages will be written to disk.

I don't have a reference right now, but I recall reading that SQL's memory manager tries to keep pages from tempDB in memory as far as possible and not write them to disk because the odds are they they'll be reused very soon. Temp tables and table vars aren't usually kept around for long periods or time so it's very likely that data inserted into one will be used again very soon. It's inefficient to write those pages to disk, use the memory for something else and then have to read the pages back from disk seconds later.

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Peso (10/12/2008)

Jeff Moden (10/9/2008)
G Bryant McClellan (10/8/2008)
Michael,
Ummm.... both table variables and temp tables start out in memory and "jump" to TempDB when they overcome some point in memory.
Really?
I was under the impression that temp tables always are written to disk because they are affected by transaction operations, which table variables are not.

No... look at the following Peter...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

... especially Q3/A3 and Q4/A4

--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: 39752 More actions · Answer 6

There you go! 🙂

I learn something new every day...

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Michael Earl (10/8/2008)
A table variable usually has to get pretty big before it spills over into TempDB.

Don't forget that the 'definition' of 'pretty big' varies depending on the current memory utilization of the server too.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 8

Another aspect of using table variables is that there are no statistics available for them.

Thus Query Optimizer always assumes there is one and only one record in the table variable.

This can be problematic of you have a lot of records in the table variable and Query Optimizer decides for a query plan assuming there is only one record in the table variable.

However, the Storage Engine reads one page minimum anyway.

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Peso (10/13/2008)
There you go! 🙂
I learn something new every day...

Heh... me too! That's why I hang around here so much. Today, I learned that there was such a thing as a "persisted" computed column. I can see lots of applications for that provided it really does what it says it will do... increase performance of queries that use deterministic computed columns.

--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: 1003863 More actions · Answer 10

Michael Earl (10/8/2008)
A table variable usually has to get pretty big before it spills over into TempDB.

Just because the table name shows up in TempDB, doesn't mean that's where it lives. Temp tables and Table Variables both start out in memory despite the fact that it's name show in TempDB. And, what do you think all those oddly named tables are? 😉

Temp tables also have to get pretty big before they spill over into TempDB.

p.s. Yep, I know... old post... but, someone brought it up again on this thread.

--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: 39752 More actions · Answer 11

Jeff Moden (1/28/2009)
Increase performance of queries that use deterministic computed columns.

Persisted computed columns can also be indexed 🙂

http://msdn.microsoft.com/en-us/library/ms189292(SQL.90).aspx

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

Peso (1/28/2009)
Jeff Moden (1/28/2009)
Increase performance of queries that use deterministic computed columns.
Persisted computed columns can also be indexed 🙂
http://msdn.microsoft.com/en-us/library/ms189292(SQL.90).aspx

Yep... found that out at the same time. That's because they're necessarily deterministic. But, I sure do appreciate the cover on that, Peter. Thanks.

--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)