October 12, 2011 at 4:05 am
Hi Guys,
Will need help on this.
Spec:
SQL SERVER 2008 Standard Edition 64 bits
Windows Server 2007 64 Bits
8GB Ram
Based on the sqlserver memory: total server memory, my sql server is using 4.1GB of memory.
Is there anyway that I find out and break down how the 4.1GB memory is utilized?
Findings:
sqlserver:buffermanager:databasepages =236MB
sqlserver:plan cache:cachepages = 2340mb
sqlserver:memorymanager:connectionmemory = 18MB
sqlserver:memorymanager:sqlcachememory= 90MB
However it doesnt sum up to 4.1GB.
Can advise how to i find the breakdown? be it through perfmon of table query.
thanks
October 12, 2011 at 6:44 am
This query is quite handy for breaking down buffer pool object consumption
selectisnull(quotename(db_name(bd.database_id)), '[MSSQLResource]') as DB
, bd.database_id
, bd.file_id, bd.page_id
, bd.page_level
, bd.page_type
, bd.row_count
, bd.free_space_in_bytes
, bd.is_modified
, case au.type when 0 then 'Dropped' when 1 then 'In-Row' when 2 then 'LOB' when 3 then 'Row Overflow' end as [Type]
, au.container_id
, au.data_space_id
, (au.total_pages * 8) as total_kb
, (au.used_pages * 8) as used_kb
, (au.data_pages * 8) as data_mb
from sys.dm_os_buffer_descriptors bd inner join sys.allocation_units au
on bd.allocation_unit_id = au.allocation_unit_id
order by DB, page_id
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 12, 2011 at 6:48 am
You could use DBCC MEMORYSTATUS.
Thanks
Chris
October 13, 2011 at 12:18 am
Hi Chris,
You could use DBCC MEMORYSTATUS.
>> which area must i look into it? wish to find the components that adds up to the commit memory.
thanks
October 13, 2011 at 12:19 am
Hi Perry,
actually wish to find out which "kind" of pool is using the total memory breakdown..
for eg:
plan cache
buffer <data? cache> etc.
thanks
October 13, 2011 at 12:51 am
Try this....
select type,(sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB
from sys.dm_os_memory_cache_counters
Group by type
Order by plan_cache_in_GB desc
October 14, 2011 at 4:52 am
hi zealot,
appreciate ur help.
this return a value of 19gb, doesn't really tally.
thanks!
October 14, 2011 at 5:14 am
You can use the below link to help with DBCC MEMORYSTATUS
http://support.microsoft.com/kb/907877
You could also look at any of the below quieries and modify them to suit.
-- Listing System memory usage.
SELECT total_physical_memory_kb / 1024 AS total_physical_memory_mb ,
available_physical_memory_kb / 1024 AS available_physical_memory_mb ,
total_page_file_kb / 1024 AS total_page_file_mb ,
available_page_file_kb / 1024 AS available_page_file_mb ,
system_memory_state_desc
FROM sys.dm_os_sys_memory
-- Memory usage by the SQL Server process.
SELECT physical_memory_in_use_kb ,
virtual_address_space_committed_kb ,
virtual_address_space_available_kb ,
page_fault_count ,
process_physical_memory_low ,
process_virtual_memory_low
FROM sys.dm_os_process_memory
-- Get total buffer usage by database
SELECT DB_NAME(database_id) AS [Database Name] ,
COUNT(*) * 8 / 1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- exclude system databases
AND database_id <> 32767 -- exclude ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC ;
-- Breaks down buffers by object (table, index) in the buffer pool
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName] ,
p.index_id ,
COUNT(*) / 128 AS [Buffer size(MB)] ,
COUNT(*) AS [Buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors
AS b ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100 -- exclude system objects
GROUP BY p.[object_id] ,
p.index_id
ORDER BY buffer_count DESC ;
-- Buffer Pool Usage for instance
SELECT TOP(20) [type], SUM(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC;
THanks
Chris
October 14, 2011 at 5:21 am
To obtain information per database level, run the below query against each database to obtain the usage.
SELECT TOP 25
obj.[name],
i.[name],
i.[type_desc],
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
-- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC
October 17, 2011 at 3:12 am
Hi Chris,
thanks for your help.
My output as below:
1)
-- Memory usage by the SQL Server process.
4.1 GB - Physical memory in used
2)
-- Get total buffer usage by database
135MB - i have only one user db
3)
-- Buffer Pool Usage for instance
2.6GB (after summing up total)
however, after adding up 2 and 3, i still can't add up to a total of 4.1 GB (physical ram utilized by sql server).
Any idea on this?
thanks
October 24, 2011 at 8:12 pm
hi guys,
anyone can advise?
thanks
October 25, 2011 at 7:21 am
chewelson (10/24/2011)
hi guys,anyone can advise?
thanks
As has been said already, DBCC memorystatus will give you detailed output on memory usage. It is officially semi-documented online.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply