Thanks for your response, that was a nice little piece of info. I found that my test machine had the whole DB in RAM, but the Main SQL Server only had half of its Database in RAM.
What can i do to speed up the Database if it is already in Mem, its holding realtime info and i see that it can get 100Msg/sec, any suggestions?
I have inherited this database and need to try and get it to a good place :-D
if you have the OS setup to use all 8 gig of ram (either 64bit os or something else) Sql Server will read pages into memory from disk as it needs them. SQL Server will only flush the cache if the server comes under memory pressure and needs to reclaim some from sql server. otherwise the information just sits in the buffer cache. along with how much of your database is in memory also look at the page life expectancy. In our production environment we have around 100 gigs of data and 32 gig of ram on our server, however we only use a small portion as there is allot of historical logging tables that are not accessed very often. because of that our "Active" portion of our database fist very comfortably in memory and our page life expectancy sits at several hours. (an overnight maintenance on indexes uses all of that ram and then our page life expectancy just rises through out the day.)
all that is to say that just because the entire DB is not in memory does not mean something is wrong. If SQL Server never needs half the data (or has not needed half the data since the last time the buffer cache was flushed) its perfectly normal behavior to only have half the DB in memory. As long as you have the memory there so that if SQL Server needs more memory its there and ready so its only a read from disk and not a flush of memory then a read from disk your good to go.
as far as what to do once it all is in memory that would be performance tuning which is an entirely deep and broad subject.
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden
for the best way to ask your question.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw
Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2