performance Questions - Temp Databases

We have some internal applications that rely heavily on using temporary
databases with a lot of data. I have noticed that when these applications
run, they seem to starve the system of RAM. This is indicated by an
increase in the RAM used by the SQL process and a heavy amount of disk I/O.
My assumption is that when the temp db's are created, SQL tries to get as
much RAM from the OS as possible which creates swapping non-SQL memory to
the pagefile and SQL internally needs to free RAM and thus needs to write
cached data out to disk.
Questions:
1) Are my assumptions correct in the usage of the temp databases?
2) Are there any workarounds to this?
3) Since SQL optimizes RAM over time, I would guess that no matter what we
do, SQL will always fill up its internal RAM with cached data, thus adding
more RAM to the system won't resolve these symptoms, it will merely delay
them by taking more time to cache all the RAM. (Our applications are
already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will not
have a significant improvement due to the memory address extensions. 64 bit
SQL would probably be our only solution to flood the system with enough RAM
that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
enough RAM in a system for this would be significanlty expensive for our
needs.)
Thank You,Kevin,
What do you mean by Temp databases? Are you creating databases and then
dropping them? Or do you mean temporary tables being created in the tempdb
database? If you only have 2GB of ram and your using it all I would
suggest you set your max memory setting in sql server to around 1.5GB to
allow the OS and memtoleave areas some slack. If you are memory constrained
and it sounds like you are, then going to 4GB can make a huge difference.
You can set the /3gb switch to allow sql server use of up to 3GB and leave
1GB for the OS. This should alleviate most of the OS paging and sql server
can use it's caching mechanisims as designed without dealing with the OS
swap file.
Andrew J. Kelly SQL MVP
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c5jhcm$42i$1@.grandcanyon.binc.net...
> We have some internal applications that rely heavily on using temporary
> databases with a lot of data. I have noticed that when these applications
> run, they seem to starve the system of RAM. This is indicated by an
> increase in the RAM used by the SQL process and a heavy amount of disk
I/O.
> My assumption is that when the temp db's are created, SQL tries to get as
> much RAM from the OS as possible which creates swapping non-SQL memory to
> the pagefile and SQL internally needs to free RAM and thus needs to write
> cached data out to disk.
> Questions:
> 1) Are my assumptions correct in the usage of the temp databases?
> 2) Are there any workarounds to this?
> 3) Since SQL optimizes RAM over time, I would guess that no matter what
we
> do, SQL will always fill up its internal RAM with cached data, thus adding
> more RAM to the system won't resolve these symptoms, it will merely delay
> them by taking more time to cache all the RAM. (Our applications are
> already filling up SQL with 2 GB of RAM. I suspect moving to 4GB will not
> have a significant improvement due to the memory address extensions. 64
bit
> SQL would probably be our only solution to flood the system with enough
RAM
> that our apps can't fill up SQL's internal RAM usage, but 64 bit SQL and
> enough RAM in a system for this would be significanlty expensive for our
> needs.)
>
> Thank You,
>|||Yes I was referring to creating databases then dropping them. In one
particular application, a lot of the stored procedures are running
algorithms that do this.
Thank You for the feedback. Now that we have a monitoring system in place,
I will experiment with restricting SQL to 1.5 GB or so and monitor memory
swapping and related disk I/O.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OzqSg7oIEHA.3276@.TK2MSFTNGP09.phx.gbl...
> Kevin,
> What do you mean by Temp databases? Are you creating databases and then
> dropping them? Or do you mean temporary tables being created in the
tempdb
> database? If you only have 2GB of ram and your using it all I would
> suggest you set your max memory setting in sql server to around 1.5GB to
> allow the OS and memtoleave areas some slack. If you are memory
constrained
> and it sounds like you are, then going to 4GB can make a huge difference.
> You can set the /3gb switch to allow sql server use of up to 3GB and leave
> 1GB for the OS. This should alleviate most of the OS paging and sql
server
> can use it's caching mechanisims as designed without dealing with the OS
> swap file.
> --
> Andrew J. Kelly SQL MVP
>
> "Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
> news:c5jhcm$42i$1@.grandcanyon.binc.net...
applications[vbcol=seagreen]
> I/O.
as[vbcol=seagreen]
to[vbcol=seagreen]
write[vbcol=seagreen]
> we
adding[vbcol=seagreen]
delay[vbcol=seagreen]
not[vbcol=seagreen]
> bit
> RAM
>