Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 2 '11 at 16:50

This question came from our site for professional and enthusiast programmers.

1  
Well, for comparison, what's the usage when the application is not active? Generally, one of the benefits of a database system is that it doesn't need the full file in memory, but it will fit what you pull out of it into memory until you flush it elsewhere. I'm going to have to declare 'no'. –  Grant Thomas Mar 2 '11 at 16:25
    
@Mr. Disappointment : I agree that is a benefit, but in the case where it can put the full file in memory (like this case), would it? –  Joda Maki Mar 2 '11 at 16:29
    
Should it need to, then yes. But on demand of the callers and the data they require, not natively as a matter of course. So, generally, if you have a database memory problem, it will be with the calling code or the supporting software, or a mixture of the two. –  Grant Thomas Mar 2 '11 at 16:30
1  
An example for perspective: I know a very big internet company owning several very big portal sites. They deliver ca. 300 million ads from their own adservers for those portals. The structure of the ad servers is such that there is one big Oracle DB with all the information for accounting and targeting, but there are 32 big (32GB RAM) servers holding smaller databases (with only the currently active ads) in RAM. They don't touch a disk, they must not: they only have 1/1000 second to find an ad to place when the query for it comes in, any disk access would make that goal impossible to reach. –  Mörre Mar 2 '11 at 16:32
1  

2 Answers 2

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.
share|improve this answer
    
'Yes, the entire database is likely stored in memory.' So, in the case of our production server, with a minimum of ~60 websites each utilising a dedicated database within the SQL Server Master, where even only a handful of these equate to a size larger than the machines available memory, how does that work? I'm tempted to say there is no definitive default, or likelihood of either scenario - as per a previous comment I made, it is really more dependent on the environment as opposed to the engine. –  Grant Thomas Mar 2 '11 at 18:38
    
@Mr. Disappointment: you are applying my answer to a different question. The OP said: My database is about 1gb in size (according to the mdf file of my db). My database server has 4gb of ram.. If you want an answer for the case when you have 60 websites and how does buffer pool caching work in that case, then ask that question :) –  Remus Rusanu Mar 2 '11 at 19:00
    
You're the expert here, but please excuse my inquisitive nature: so does this theory assume (because we don't have specific data) that the machine is doing naught else? Or accounts for a certain threshold of memory being used by unknown consumers? –  Grant Thomas Mar 2 '11 at 19:09
    
As I said: you don't have to guess, you can measure this exactly. –  Remus Rusanu Mar 2 '11 at 19:36

The whole DB (ie data) would not be stored in memory (sql server). After examining indexes and/or keys, it will retrieve a pointer to where in the data file the data you're looking for is, and would likely then go to disc to retrieve it. If no keys or indexes, would have to scan the whole heap.

Depending on which version of SQL Server you're running, there may be strategies for caching well-used data to minimise disc reads.

You CAN get in-memory DBs eg MySql Cluster database.... This is designed to fit the ENTIRE db into memory across a cluster of servers for high availability/disaster recovery and fast data retrieval.

share|improve this answer
1  
I haven't enough rep to vote this down but not sure why you are saying that it categorically would not be stored in memory. Once a page is read into the buffer cache it will stay there until internal or external memory pressure causes the cache to be trimmed. –  Martin Smith Mar 2 '11 at 16:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.