Can I fit an entire DB in memory?

My database is about 1gb in size (according to the mdf file of my db). My database server has 4gb of ram. Looking at the memory consumption on the computer while active, it sits at around 85% used (including OS, etc)

Does this mean that all DB read activity works in memory alone (i.e. the whole db is sitting in memory)? or is there still a case where it would have to go to disk?

Solution 1:

Yes, the entire database is likely stored in memory. It will flush dirty pages to disk at checkpoint intervals. Note though that all updates will have to write to the log and wait for the log records to be hardened on durable storage before commit. This old paper SQL Server 2000 I/O Basics has all the details you'll ever need and more.

But you don't have to guess, you can measure this exactly and see if it happens or not. The relevant performance counters are:

• Page life expectancy Number of seconds a page will stay in the buffer pool without references.
• Page reads/sec Number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design.
• Page writes/sec Number of physical database page writes issued per second.