A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


What the? How much memory is SQL Server using?

05 January 2011 Don-Rickman SQL Server - Admin (0)
 
 
The question is fairly straight forward. There are many many many different articles online which will allow any person to dive into the following queries in detail. I'm only giving a high level view of memory usage based on DMV's (Dynamic Management Views).
 
 
How much memory is SQL Server Using?

--HOW MUCH MEMORY IS MY SQL SERVER USING? 

declare @obj_cache_size float, @obj_data_size float 

            --OBJECT and PLAN DATA IN MEMORY

select @obj_cache_size = sum(pages_kb) / 1024.0 from sys.dm_os_memory_cache_counters; 

-- DATA IN CACHE (PAGES, INCLUDING INDEXES) 

with buffer (Buffer_Pool_MB) 

as ( 

 SELECT count(*) * 8192.0 / (1024.0 * 1024.0) 

 FROM sys.dm_os_buffer_descriptors  

 GROUP BY db_name(database_id) ,database_id 

)

select @obj_data_size = sum(buffer_pool_mb) from buffer 

 

-- CACHE TOTALS 

select @obj_cache_size [Object_Memory_Usage_MB]  

 , @obj_data_size [Data_Memory_Usage_MB] 

 , [Misc_Memory_Usage_MB] 

 , [Misc_Memory_Usage_MB]  

 + @obj_data_size  

 + @obj_cache_size [Total_Memory_Usage_MB] 

from  

( 

 select sum(cntr_value)/1024.0 [Misc_Memory_Usage_MB]  

 from sys.dm_os_performance_counters  

 where object_name like '%memory%' 

 and ( 

   counter_name like '%Connection Memory (KB)%' 

   or 

   counter_name like '%Lock Memory (KB)%' 

   or  

   counter_name like '%Optimizer Memory (KB)%'

   or  

   counter_name like '%SQL Cache Memory (KB)%'

 ) 

) x

 
 
 
Results:
 

 

Let’s take a peek into the Server Memory.

 Syscacheobjects actually reads from sys.dm_exec_cached_plans.
But we’ll use the view to get the sql.

 

SyscacheObjects

select top 25

cacheobjType

, ObjType

, (pagesUsed * 8192)/1024.0/1024.0 [space_used_MB]

, db_name(dbid) [database_name]

, object_name(objid, dbid) [object_name]

, [sql]

from syscacheobjects (nolock)

order by pagesUsed desc

 

Distinct Cached Plans and Objects returned
Compiled Plan         Proc
Compiled Plan         Trigger
Compiled Plan         Adhoc
Compiled Plan         Prepared
Extended Proc         Proc
Parse Tree               UsrTab
Parse Tree               Check
Parse Tree               View

 

Results:


 

-----------------------

 

Buffer Pool Memory Per DB

 
dm_os_buffer_descriptors

SELECT LEFT(CASE database_id

                  WHEN 32767 THEN 'ResourceDb'

                  ELSE db_name(database_id)

        END, 20) AS Database_Name,

      count(*)AS Buffered_Page_Count,

      count(*) * 8192.0 / (1024.0 * 1024.0) as Buffer_Pool_MB

FROM sys.dm_os_buffer_descriptors

GROUP BY db_name(database_id) ,database_id

ORDER BY Buffered_Page_Count DESC

 

Results:


 

-----------------------

 

 How much space are my indexes taking up in memory?

Buffer Index Memory By Object

SELECT

      au_p.[obj_name],

      i.[name] [index_name],

      i.[type_desc],

      count(page_id) AS Buffered_Page_Count,

      cast(count(page_id) as bigint) * 8192 / (1024 * 1024) as Buffer_MB

 
 

FROM sys.dm_os_buffer_descriptors AS bd

    INNER JOIN

    ( SELECT object_name(par.object_id) AS [obj_name] , par.index_id , par.object_id, au.allocation_unit_id

        FROM sys.allocation_units AS au

        INNER JOIN sys.partitions AS par ON au.container_id = par.hobt_id

    ) AS au_p ON bd.allocation_unit_id = au_p.allocation_unit_id

LEFT JOIN sys.indexes i on i.object_id = au_p.object_id AND i.index_id = au_p.index_id

WHERE database_id = db_id('<DATABASE NAME>')

GROUP BY obj_name, au_p.index_id , i.[name],i.[type_desc]

ORDER BY count(page_id) DESC

 
 

 

Results: 

 
 
 
 If you have any comments or suggestions regarding these queries, feel free to post suggestions.