How data will be loaded into memory on sql server

Question

I am new to sql server. I read about buffer cache in sql server. I think this is the place in system RAM where the data will be stored after the request is completed. If this is correct, I have a few questions about query execution.

1) If my RAM size is 2 GB and I have data on my sql server with a size of 10 GB, and if I run sqlstatment in order to pull all the data (10 GB) from the database, what will happen (Work / Not work)

2) In the same case, if several user requests fulfill a request for 5 GB each (10 GB in total), what will happen?

Answer 1

When you issue SELECT * FROM [MyTable] , and your table has 10Gb on a system with 2Gb of RAM, the database should not read all 10 Gb at once in memory. The selection will begin scanning data, starting with the first data . To do this, he only needs this page (which is 8 KB) in memory, so it reads the page and consumes 8 KB of RAM. When scanning this page, it displays the result that you see as a result set. As soon as this is done with this page, she needs the next page in order for it to be read in memory. It scans the entries in it and produces output for your result. Then the next page, then the next page. The key point is that once done with the page, it is no longer needed. As he continues to add these 8kb pages to RAM, they will eventually add up and consume all of the free RAM. At this point, SQL will free up old, unused pages in RAM and thus free up space for new ones. He will continue to do this until all 10Gb of your table has been read.

If there are two users who read a table of 5 GB each, everything works the same way. Each user request scans only one page at a time, and as they advance and continue to read the pages, they fill up RAM. When all available RAM is used, SQL will begin to discard old pages from RAM to make room for new ones.

In the real world, things are becoming more attractive due to considerations such as reading ahead .

And as a note, you should never scan 10Gb of data. Your application should always request only the data that it needs, and the data should be quickly restored using the index to avoid such a large scan, which should check the entire table.

Answer 2

Fortunately, you have nothing to worry about. Of course, it is important to configure your queries, minimize the result set for network transfer, etc., but SQL Server has been around for a long time, and it is very good at managing its own memory. If you do not come across a specific request that does not work correctly, I would say do not worry about it.

Answer 3

the full set of query results is not saved in sql server RAM for reuse. What can be saved is the execution plan used in the request.

SQL Server stores data for managing it, for example, in the update statement, it reads data from the database, saves it in RAM, edits it, and then another process writes it back to the database. BUt like @ n8wrl said you don’t need to worry about that.

Answer 4

As you noted, the received data goes to the buffer cache. Some of them are real memory, some of them are lowered to disk.

You can find out if you are using memory reuse by looking at the performance counter of the life expectancy page (there are other indicators, but this is a short reduction). If you run a query that returns huge amounts of data and pops other data out of the cache, you can see a drop in page life. When you run many, many small queries, you can see that the page's lifespan increases, sometimes up to several days and days.

This is not very important for the pressure of memory on the system, but it can give you an idea of ​​how well you see that the data in the cache is being reused.