469,572 Members | 1,163 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,572 developers. It's quick & easy.

tempdb in RAM?

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
Jul 20 '05 #1
2 6336
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

Jul 20 '05 #2
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
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Deaconess | last post: by
2 posts views Thread by Tom | last post: by
8 posts views Thread by arijitchatterjee123 | last post: by
2 posts views Thread by Thomas R. Hummel | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.