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