sql server misc: I am working with a system that uses quite a lot of stored procedures
that make use of table variables extensively. The ram used by the
database server quickly rises to close to 2 Gb. The amount of memory
used by sql server is limted but as soon as the memory reaches the
limit, the performance of the system is reduced greatly. It seems to
me that the table variables when used are of no use outside the
procedure in which they were created, however, the memory used doesn't
seem to be de-allocated. Is there any way of de-allocating the memory

Table variables work just like temp tables except for they have slightly
less overhead for logging and locking. When the sp completes the variable
will go out of scope and the pages will be free for other uses. The overall
memory used by SQL will remain at the maximum level. If dynamic memory is
set in SQL and another application on the server needs the ram SQL will
slowly give up memory. This is the natural behavior of SQL server. If
this is a dedicated SQL server then allow SQL to allocate all the ram it can
short of a min of 128mb for the OS (I like a little more like 512MB).
Sounds like you are using SQL Standard edition. It is limited to 2GB.
(Actually is uses about 1.7GB) When there is not enough memory for all the
pages to fit the I/O subsystem quickly becomes the bottleneck and things
slow down. Optimize you procedures, invest in better or more spread out
I/O, or upgrade to SQL enterprise edition.

Danny

[quoted text, click to view]