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