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 ««1234»»»

Local Temporary Tables and Table Variables Expand / Collapse
Author
Message
Posted Tuesday, September 2, 2008 7:24 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:04 PM
Points: 531, Visits: 431
To humbleDBA,

You are right. INSERT ... EXEC works for table variable too in SQL Server 2005. In SQL 2000, it does not work. Thanks.

Jeff, Gail, and Chris,

Thanks for the workarounds for UDTs in master and msdb. I have not had a chance to test and confirm it.

David Quéméré,

Your are right. The script has a mistake. It's actually the workaround script. I guess I messed it up by copy/paste

Thank you guys for taking time to read/comment on it



Post #562381
Posted Monday, September 8, 2008 3:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, May 4, 2013 8:21 PM
Points: 13, Visits: 58
hmmm... I personally hate table variables because they cause the disk queue length (I/O) and CPU to shoot through the roof. They only work on simple small systems with low transactional volume. If you take a real life OLTP system or a large warehouse, they are pretty much useless in my book. They seem to bring the system to its knees. They kind of remind me of Cursors... wait... aren't cursors memory intensive also? Do you see the similarities?

I will take an indexed temp table over a table variable any day on a heavy usage system.


SQL Silvey
Post #565798
Posted Tuesday, September 9, 2008 1:44 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
Probably because of the affect they have on queries. Bad cardinality = bad query plans = higher than expected IO and CPU.


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 #565951
Posted Friday, September 12, 2008 6:59 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 772, Visits: 1,186
I have a question, hopefully you guys will know better than me

We use a lot of TABLE VARIABLES (not temp tables) in our select statements for our heavy traffic websites
Server has 4GB RAM. Windows 2003 Standard x64, SQL 2008 RC0 64-bit
SQL Server has been limited to max of 3GB memory, with 8GB page file

Now the issue is our page file usage in Task Manager kept growing
In 1~2 hours it has grown from 4.2GB to 9.5GB and not letting go so it seems
There is no LONG running queries (no long duration, no high CPU)

Probably a matter of time before it may reach the 12GB commited charge (4 physical + 8 page file)
But does table variable get dropped??

Noticed the article says "Current Batch" scope, while temp table is "Current Session"
what is defined a batch? what if the front end .Net keeps re-using the same connection, therefore not dropping the table variable?

Appreciate any help


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #569004
Posted Saturday, September 13, 2008 3:16 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
Table variables has the same scope as normal variables. Much less than a temp table. Their lifetime is limited to one set of commands (batch) sent to the server.

So, if you send a bunch of ad-hoc SQL to the server (using Execute or ExecuteNoQuery or the like) and that ad-hoc SQL declares and uses a table variable, the table variable only exists for the duration of that piece of ad-hoc SQL.

Are you running SQL Enterprise or Standard?
Have you checked to see if the page file usage is coming from SQL or from something else on the box?

Lastly (not a perf issue) RC0 isn't licensed for production usage (unless you're a member of the early-adopter program) and has a built-in expiry date. You should replace that with SQL 2008 RTM as soon as you can.



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 #569022
Posted Saturday, September 13, 2008 9:25 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 772, Visits: 1,186
GilaMonster (9/13/2008)
Table variables has the same scope as normal variables. Much less than a temp table. Their lifetime is limited to one set of commands (batch) sent to the server.

So, if you send a bunch of ad-hoc SQL to the server (using Execute or ExecuteNoQuery or the like) and that ad-hoc SQL declares and uses a table variable, the table variable only exists for the duration of that piece of ad-hoc SQL.

Are you running SQL Enterprise or Standard?
Have you checked to see if the page file usage is coming from SQL or from something else on the box?

Lastly (not a perf issue) RC0 isn't licensed for production usage (unless you're a member of the early-adopter program) and has a built-in expiry date. You should replace that with SQL 2008 RTM as soon as you can.


Thank you Gila

Good, that's smaller scope. After noticing previous posts, we do also observe high IO and CPU as well
so my next step is perhaps change all TABLE VARIABLE -> TEMP TABLE, and see if similiar pattern exists

I checked (sp_configure, Process Explorere). SQL has max size of 3GB yes, but the VirtualSize is 10GB so it is from sqlserv.exe
It's a dedicated SQL Server, nothing else runs on it

It's SQL 2008 Standard (yes RC0 evaluation), I wish we can upgrade already

Added:
actually now I recall, we are in the early-adopter program, so we may be safe there
and Pagefile usage was 8GB last night -> 5GB near midnight -> 10GB this morning -> 8.7GB noon -> 12GB now
btw, we use Spatial index and Full-text Daemon on the server


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #569043
Posted Saturday, September 13, 2008 10:23 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 35,224, Visits: 31,683
Ummm... high IO and CPU isn't necessarily caused by either Table Variables or Temp Tables. It's HOW they are used. I think what you're going to find is that the shift to Temp Tables may help a bit, but the real key is, how much RBAR and aborant joins you're working with in the code.

--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."

(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 #569046
Posted Sunday, September 14, 2008 2:20 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
jerryhung (9/13/2008)

Good, that's smaller scope. After noticing previous posts, we do also observe high IO and CPU as well
so my next step is perhaps change all TABLE VARIABLE -> TEMP TABLE, and see if similiar pattern exists


You can try. It may help, it may not. Depends on what's causing the problem in the first place.
Can you post of of the queries that seems to be giving problems (perhaps start a separate thread for that?)


I checked (sp_configure, Process Explorere). SQL has max size of 3GB yes, but the VirtualSize is 10GB so it is from sqlserv.exe
It's a dedicated SQL Server, nothing else runs on it


SQL's max memory is 3GB, but it's using 10? You may want to raise that with MS using your early adopter contacts. SQL does and will use a bit more memory than what the max memory says, but not 7 GB more.

What do the target and total server memory counters return (perfmon, SQL Server:memory manager)
What does the following return?

select SUM(single_pages_kb + multi_pages_kb)/1024.0
FROM sys.dm_os_memory_clerks


It's SQL 2008 Standard (yes RC0 evaluation), I wish we can upgrade already


You should look into upgrading to RTM. I don't know when RC0 is going to expire and I don't think you want to find out when your prod server refuses to start up.



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 #569151
Posted Sunday, September 14, 2008 8:05 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 772, Visits: 1,186
GilaMonster (9/14/2008)
jerryhung (9/13/2008)

Good, that's smaller scope. After noticing previous posts, we do also observe high IO and CPU as well
so my next step is perhaps change all TABLE VARIABLE -> TEMP TABLE, and see if similiar pattern exists


You can try. It may help, it may not. Depends on what's causing the problem in the first place.
Can you post of of the queries that seems to be giving problems (perhaps start a separate thread for that?)


I checked (sp_configure, Process Explorere). SQL has max size of 3GB yes, but the VirtualSize is 10GB so it is from sqlserv.exe
It's a dedicated SQL Server, nothing else runs on it


SQL's max memory is 3GB, but it's using 10? You may want to raise that with MS using your early adopter contacts. SQL does and will use a bit more memory than what the max memory says, but not 7 GB more.

What do the target and total server memory counters return (perfmon, SQL Server:memory manager)
What does the following return?

select SUM(single_pages_kb + multi_pages_kb)/1024.0
FROM sys.dm_os_memory_clerks


It's SQL 2008 Standard (yes RC0 evaluation), I wish we can upgrade already


You should look into upgrading to RTM. I don't know when RC0 is going to expire and I don't think you want to find out when your prod server refuses to start up.



I agree, I had the fear of one day, SQL Server would just stop working (in house, and production)

But at the moment, I *think* we may have found the issue - Page File going nuts with a iFTS Catalog. RC0 x64 on Windows 2003 x64 as well

So I disabled the Full-Text Automatic Change Tracking, and guess what, Pagefile has stayed at 3.5-3.7GB for 12 hours
Now we just need to prove the theory


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #569172
Posted Monday, June 15, 2009 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 9, 2012 2:41 PM
Points: 1, Visits: 115
Very good article.
Pingback from
http://madebysql.blogspot.com/

Thanks
Post #734943
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse