July 15, 2010 at 8:10 am
How do you measure the memusage in Sysprocesses table? How many KB in memusage because that is described as Number of pages in the
procedure cache that are
currently allocated to this
process. A negative number
indicates that the process
is freeing memory allocated
by another process.
Is this the right measure of memory usage?
Thanks
July 15, 2010 at 12:25 pm
angiepimentel 1403 (7/15/2010)
How do you measure the memusage in Sysprocesses table? How many KB in memusage because that is described as Number of pages in theprocedure cache that are
currently allocated to this
process. A negative number
indicates that the process
is freeing memory allocated
by another process.
Is this the right measure of memory usage?
Thanks
You have posted in SQL Server 2005 forum an will assume you are using 2005 version. here are some memory related queries.
select
top 10 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
SELECT sum(a.page_id)*8 AS MemorySpaceKB
FROM (SELECT database_id, allocation_unit_id, COUNT(page_id) page_id FROM sys.dm_os_buffer_descriptors GROUP BY database_id, allocation_unit_id) a
JOIN sys.allocation_units ON a.allocation_unit_id = sys.allocation_units.allocation_unit_id
JOIN sys.partitions ON (sys.allocation_units.type IN (1,3)
AND sys.allocation_units.container_id = sys.partitions.hobt_id)
OR (sys.allocation_units.type = 2 AND sys.allocation_units.container_id = sys.partitions.partition_id)
JOIN sys.tables ON sys.partitions.object_id = sys.tables.object_id
AND sys.tables.is_ms_shipped = 0
SELECT type, name, SUM(single_pages_kb + multi_pages_kb) AS 'SIZE (KB)'
FROM sys.dm_os_memory_clerks
group by type,name order by 3 desc
--Buffer usage by database
select db_name(database_id) as dbName, count(*)*8/1024 as BufferPoolMB
from sys.dm_os_buffer_descriptors
group by db_name(database_id)
order by 2 desc
--Identify the min & max memory setting
select * from sys.dm_os_performance_counters where counter_name like '%server memory%'
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply