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.
SQL Server uses memory for three main purposes. Each purpose is fulfilled by one internal component.
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.
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.
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.
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