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.

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

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

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

GilaMonster (9/14/2008)

---

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



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 

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.