For those of you who just started to manage SQL server might wonder why SQL server use so much memory. After you do some research online, you normally will see response saying SQL server is memory hungry and by default will take all your memory! You need to configure to the right amount for SQL and leave some to OS etc. Most of us will just follow the recommendation, and it normally works out for you. But do you ever wonder why does SQL server require so much memory?

Let me try to explains it in simple terms which will helps you to understand a bit more on how it works, so that next time you heard SQL server is taking all memory, you will know the reason behinds it, and most important of all, you can start to look at areas that might can identify the issue.

First of all, why does SQL server need so much memory? The reason is that nearly all operations perform by SQL server is within memory, (I won't go into details of pages IAM page, GAM etc, might cover that later in a more advance post). In short, SQL server have assign an area in memory call buffer pool, this is where most of the memory is used. You might ask, OK, so what is buffer pool? This is where all your "active" data will be host. Let me get into a bit technical here, for all data that needs to be process in SQL (any DML - read, write etc), it needs to be in buffer pool! Let me give a simple example here, let say you do a simple select statement from a table, it will firstly check if the data (stored in 8KB page) in buffer pool, if it is, then it will just return the data to the client (this operation is call logical read), this operation is fast as it just a read in memory and return the result. On the other hand, if the data is not in buffer pool, it will needs to read the data from disk to memory (this operation is call physical read) - which requires to perform physical I/O, then will return the result to the client.

You might already see that since all data that you select will be required in buffer pool, hence the more data you select, the more memory it will use. I know you might already been thinking, what if I have a 100Gb database but only 32Gb of memory on the server, how can it store all the data in memory? The answer is it won't! It recycle the usage of memory by latest use algorithm, meaning if there are data pages that has not been used for awhile, and new data page needs to be brings in, it will first remove the oldest page in buffer pool, free up the space for the new one. One indicator to monitor this process is call PLE (page life expectancy), this shows in sec of how long a page will live in buffer pool, the higher of the value the better, as it means a data page will live in your buffer pool longer, which in turn means you will only perform logical read rather then physical read (requires I/O). The less I/O it perform, the faster the result you can get, as memory is always faster then physical drives.

This is a very basic overview of why SQL server takes so much memory, and why does it important to give enough memory to SQL in order for it to run your queries faster. Keep in mind that buffer pool might be the biggest consumer of memory, there are other SQL process that requires memory as well, such as locks, connections, query plans etc. that will takes up memory as well. Hence that is why SQL server is so memory hungry and will try to takes up as much memory as possible.