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.