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