Got Memory?

Here are some perf counter stats from a box I am trying to better performance on:

buffer cache hit/avg 99.5

page reads/sec 5000

stolen pages 114000

page life expectancy 5!!!!!!!!!!!!!!!

the server is x86 sql 05' std. with the 2gb cap w/o awe enabled config. 2 Xeon 3.06 GHZs physical CPUs each dual core thus 4 logical CPUs with total system mem of approx 3.75 gb.

that page life metric is very concerning to me I am new to tuning sql memory but I would like to know right off the bat if his 2gb cap is not "gonna cut it" for his workload needs, I would hate to have to recommend 64 bit to him ahhhh, $$$. He actually has 3 identical boxes load balacned with a cisco device. (gets a ton of Inet traffic to the front-end Cold Fusion sites). His frontend app uses several DBs ranging in size from 4GB-42GB.

Thanks,

Derek

I changed the min/max memory options to both be aprox 2gb and now my page life exp jumped to 45 avg.

Global Temp Tables Getting Dropped form Time to Time

Hi all,

I have created several global temp tables to cache some intermediate results ...
However, it seems that after a while those tables will be dropped by SQL Server 2005 automatically (I have not restarted the server and no drop table statement ever executed against those tables). Is this a feature by design? How to make those global temp tables persistence to next service restart?

Thanks,
Ning
....maybe, but either way, you may want to consider using something other than global temp tables. Why don't you just create user tables and clear them out when you need to?|||

This is by design. ##tmp is dropped if the service is restarted and when there is no one else connects to it.

"Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended."

|||

Global temp tables are dropped after the last connection to the server closes. It is by design. As long as a connection is open, the tables persist.

If you need permenance of a table, then don't use 'Temp' tables -they are temporary...