tempdb in RAM?

[New DB Admin]

Is it a good thing to do? what are the cons? Are there any risks? (this is
an ISP database running 24 hrs) I have sql 6.5 on Win NT with 256 MB Ram (64
MB reserved for SQL Server). tempdb size is 10 MB. Currently i'm
experiencing slow response for large queries and sometimes users have
problems logging on the internet (authentication stops).

thx

[David R Rawheiser]

As always the clear answer is "Yes and No"

If the queries use temp tables that are not large, putting tempDB in ram
will speed them up.
However if the results get large, you could run out of space in tempDB.
And reserving RAM for TempDB makes less available for the other caches, so
it may slow down other things more than the speed of temp table access.

I would persue adding indexes on the tables.

Use the Profiler to save a peak period's activity, and then
use the index tuning wizard to see what can be done.

"New DB Admin" <th******************@hotmail.com> wrote in message
news:be***********@ID-31123.news.dfncis.de...

Is it a good thing to do? what are the cons? Are there any risks? (this is
an ISP database running 24 hrs) I have sql 6.5 on Win NT with 256 MB Ram (64 MB reserved for SQL Server). tempdb size is 10 MB. Currently i'm
experiencing slow response for large queries and sometimes users have
problems logging on the internet (authentication stops).

thx

[Erland Sommarskog]

New DB Admin (th******************@hotmail.com) writes:

Is it a good thing to do? what are the cons? Are there any risks? (this
is an ISP database running 24 hrs) I have sql 6.5 on Win NT with 256 MB
Ram (64 MB reserved for SQL Server). tempdb size is 10 MB. Currently i'm
experiencing slow response for large queries and sometimes users have
problems logging on the internet (authentication stops).

Keep the finger away from that dial. Tempdb in RAM is very rarely a good
idea to play with. Give SQL Server as much freedom as possible to
determine itself what is to be in memory and not. Reserving memory
for tempdb or a table with DBCC PINTABLE only means that you make the
cache smaller. If there are plenty of reference, the tempdb stuff will
be in memory anyway. I could add that this option does not exist in
SQL 2000. Microsoft decided that it was a mistake to have it, so they
removed it.

What causes your performance problems, I don't know. But in 6.5
it can be a huge problem if you create tables in transactions, because
you get page locks on the system tables in tempdb.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp