471,888 Members | 2,223 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,888 software developers and data experts.

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 6429
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 YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.