9

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?

  • 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 02 '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? –  Mar 02 '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 02 '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. –  Mar 02 '11 at 16:32
  • 1
    Here are two reads http://msdn.microsoft.com/en-us/library/ms178067.aspx and http://sqlserverperformance.wordpress.com/2008/08/06/suggested-max-memory-settings-for-sql-server-20052008-2/ – Saif Khan Mar 02 '11 at 16:34
  • @Mörre: While this is a nice example, it is _very_ specific - I think the idea of the OP is to determine the basic nature of databases, not the extreme possibilities. – Grant Thomas Mar 02 '11 at 16:35
  • Um, comment #2 from me should read 'supporting hardware' at the end. Oops. – Grant Thomas Mar 02 '11 at 16:38
  • @Mr. Disappointment: I know, but I could not add "offtopic" because I had just 3 letters left. I added the comment because someone wrote an answer (now deleted) questioning the need for ever having a full DB in memory. As I said: "for perspective". –  Mar 02 '11 at 16:41
  • @Mörre: It is an excellent point, don't get me wrong. Was _just saying_, so to speak. :) – Grant Thomas Mar 02 '11 at 16:42
  • You need to remember that data pages are not the only memory consumers in SQL Server. The plan cache and even locks can also take hefty amounts of memory. Additionally dirty pages will get written out to disc every checkpoint so there will still be some disk activity. – Martin Smith Mar 02 '11 at 16:51
  • I am not sure it matters, but is that a 32bit or 64bit OS? – Zoredache Mar 02 '11 at 17:20

2 Answers2

5

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.
Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22
  • '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 02 '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 02 '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 02 '11 at 19:09
  • As I said: `you don't have to guess, you can measure this exactly`. – Remus Rusanu Mar 02 '11 at 19:36
-5

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.

  • 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 02 '11 at 16:58