sql server 2005 memory - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

sql server 2005 memory

Author

Topic

dbachew
Starting Member

1 Post

Posted - 2011-09-29 : 10:13:57

Hi Guys,
Perfmon--->Memory--->Manager --> Target Server Memory = Around 1.6GB
Right click task manager sqlservr.exe = around 1.6GB

I have a few databases in that particular instance, i wish to find out how the 1.6GB was utilized among the databases. In short to find out the most heavily utilized db on memory.

I executed this statement:

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 / (1024 * 1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC

However the total was only about 500MB+. Where did the rest of the memory goes in sql server? Anyway to find out?

thanks

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-11 : 07:45:08

The query above will give you memory utilized by buffer pool only and not by other components.
run below code.

select name,type,
sum(single_pages_kb+multi_pages_kb) as MemoryKB
from sys.dm_os_memory_clerks
group by name,type
order by memorykb desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources