Why does SQL Server consume so much memory?

August 8, 2017 Internals 1443 Views

/_include/blog-images/article-images/ram-memory2.jpg

There is a reason why SQL Server consumes so much memory; It does not leak memory, and there is a reason behind that.



Why does SQL Server consume so much memory?

You might have come across times that SQL Server has consumed a lot of server memory, maybe all of that. Have you ever wondered why has SQL Server used the memory for?

SQL Server is designed to perform as fast as possible. Therefore, if needed, it consumes as much server memory as possible just to be able to respond to requests as fast as possible. Therefore, SQL Server is memory intensive by design.

Database servers are assumed to have one single responsibility only; and that is the Database. Therefore, SQL Server eats up as much memory as possible, unless it is configured not to.


What does SQL Server consume memory for?

SQL Server uses memory for three main purposes. Each purpose is fulfilled by one internal component.

  1. Buffer Pool:
    Buffer Pool consumes memory to cache user data. In other words, the actual data in the user tables are taken into memory by the Buffer Pool component. Obviously, as the Buffer Pool memory grows, the more data is cached in server memory, therefore, the database server is expected to perform faster.

  2. Execution Plan Cache:
    Execution Plan generation is quite CPU intensive, therfore SQL Server caches the generated plans. The server memory where the Execution Plans are cached is called Plan Cache.

  3. Query Execution:
    SQL Server consumes some part of memory to execute the queries. Sorting, joining, and other operations that usually happen in query execution requires memory. SQL Server consumes some part of available memory to execute queries.


Which table/index has consumed so much of Buffer Pool?

Using the DMV sys.dm_os_buffer_descriptors, you can pretty quickly figure out how the Buffer Pool is populated by different objects of your database.
This DMV can untangle a lot of mysteries around the big amount of memory SQL Server has consumed. Here is the query to extract Buffer Pool usage:


SELECT
	o.name,
	o.type_desc,
	i.name as index_name,
	i.type_desc,
	count_big(b.page_id) * 8 as size_kb
from  sys.partitions AS p 
join sys.allocation_units AS au ON p.hobt_id = au.container_id
join sys.objects AS o ON p.[object_id] = o.[object_id]
join sys.indexes AS i ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id  
join sys.dm_os_buffer_descriptors AS b ON au.allocation_unit_id = b.allocation_unit_id
WHERE au.[type] IN (1,2,3) AND o.is_ms_shipped = 0
group by db_name(b.database_id),
		 o.name,
		 o.type_desc,
		 i.name,
		 i.type_desc
order by count_big(b.page_id) desc

How do you rate this topic?


Further reading:
Unit of data in Buffer Pool
Some SQL functions are constants
SQL Server Pages and Extents