How data will load in to memory in sql server

Question

I am a beginner in sql server.I have read about buffer cache in sql server.I think it is the location in system RAM where the data will be stored once the query is executed.If that is correct,i have a few questions about query execution.

1)If my RAM size is 2GB, and i have data in my sql server of 10GB size and if i execute the sqlstatment for retreiving all the data(10 GB) from database,what will happen(Work/Not work)?

2)In the same case, if multiple user execute queries for retrieving 5 GB each(total 10 GB),what will happen?

Answer 1

When you issue a SELECT * FROM [MyTable] and your table has 10Gb on a system that has only 2Gb of RAM a database does not have to read the entire 10 Gb at once in memory. The select will start a scan of the data, starting with the first data page. For this it only needs that page (which is 8Kb) in memory, so it reads the page and consumes 8Kb of RAM. As it scans this page, it produces output that you see as the result set. As soon as is done with this page, it needs the next page so it read is in memory. It scans the records in it and produces output for your result. Then next page, then next page. The key point is that once is done with a page, its no longer needed. As it keeps adding these 8kb pages into RAM, they will eventually add up and consume all free RAM. In that moment SQL will free the old, unused, pages in RAM and thus make room for new ones. It will keep doing so until the entire 10Gb of your table are read.

If there are two users reading a table of 5GB each, things work exactly the same. Each user's query is scanning only one page at a time, and as they make progress and keep reading pages, they will fill up the RAM. When all the available RAM is used, SQL will start discarding old pages from RAM to make room for new ones.

In the real world things are fancier because of considerations like read-ahead.

And as a side note, you should never scan 10Gb of data. Your application should always request only the data it needs, and the data should be retrievable fast by using an index, exactly to avoid such large scan that needs to examine the entire table.

Answer 2

As you noted, data retrieved goes into the buffer cache. Some of that is real memory, some is spooled off to disk.

You can observe whether or not you're reusing memory by watching the Page Life Expectancy performance counter (there other indicators too, but this one is a quick shorthand). If you run a query that returns huge amounts of data and pushes other data out of the cache, you can watch the page life expectancy drop. As you run lots & lots of small queries, you can see the page life expectancy grow, sometimes to a lenght of days & days.

It's not a great measure for memory pressure on the system, but it can give you an indication of how well you're seeing the data in cache get reused.

Answer 3

the full resultset of a query is not stored on the RAM by sql server for reusing. What can be stored is the execution plan used on a query.

SQL Server stores data to manipulate it, like on an update statement, it reads the data from the DB, stores on the RAM, edit it and then another process writes it back to the DB. BUt as @n8wrl said, you dont need to worry about it.

Answer 4

Thankfully you don't have to worry about this. Sure, it is important to tune your queries, minimize resultsets for network transfer, etc. etc. but SQL Server has been around a long time and it's very good at its own memory management. Unless you encounter a specific query that misbehaves, I'd say don't worry about it.