December 2, 2011 at 7:17 am
Hi Guys
I'm trying to find which processes are the most memory consuming on my SQL Server, when I monitor the page usage via perfmon and see the available pages in memory (say for example 1 Million) and the free pages the server has (for example 5k pages) I wonder which processes/transactions are eating up all these pages.
If I check the sysprocesses view the "memusage" column gives me a number that MSDN says is the number of pages the process has (not sure if this is the actual value it is using at the moment I see it or if it is the total of pages it has been using since it started) but the SUM of all of these pages is very very smal (for example 2k out of the million) so I wonder who or what else has these pages?
I tried to see the data DBCC MEMORYSTATUS but I don't seem to find nothing useful, maybe I'm looking wrong, but the memory usage is always on the commited or database(clean) section, the procedure cache has just a small chunk of it, but I don't seem to find actually which processes are eating the memory here, maybe I'm just looking wrong.
Can you guys help me?
Thanks in advance
December 2, 2011 at 7:31 am
The majority of the memory usage is usually the data cache and the plan cache together. The memory used by individual queries is usually tiny in comparison.
Use the sys.dm_os_memory_clerks DMV, not all that understandable, but shows the various caches and their memory usage.
SELECT * FROM sys.dm_os_memory_clerks ORDER BY (single_pages_kb + multi_pages_kb + awe_allocated_kb) desc
On my test machine, that shows the largest memory consumer to be MEMORYCLERK_SQLBUFFERPOOL, the data cache, with CACHESTORE_SQLCP (ad-hoc plans) second.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2011 at 8:52 am
Thanks Gila, I'll check it out to see if i can find something.
December 2, 2011 at 9:52 am
these queries tell you which databases are consuming most memory in the buffer cache and then you can drill down to which objects in that database are using the most memory, that might help point you in the direction of the queries that use those objects
--find out how big buffer pool is and determine percentage used by each database
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager' AND counter_name = 'Total Pages';
;WITH src AS( SELECT database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors --WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id)SELECT [db_name] = CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END, db_buffer_pages, db_buffer_MB = db_buffer_pages / 128, db_buffer_percent = CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;
--then drill down into memory used by objects in database of your choice
USE db_with_most_memory;
WITH src AS( SELECT [Object] = o.name, [Type] = o.type_desc, [Index] = COALESCE(i.name, ''), [Index_Type] = i.type_desc, p.[object_id], p.index_id, au.allocation_unit_id
FROM sys.partitions AS p INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id] INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id WHERE au.[type] IN (1,2,3) AND o.is_ms_shipped = 0)
SELECT src.[Object], src.[Type], src.[Index], src.Index_Type, buffer_pages = COUNT_BIG(b.page_id), buffer_mb = COUNT_BIG(b.page_id) / 128
FROM src
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON src.allocation_unit_id = b.allocation_unit_id
WHERE b.database_id = DB_ID()
GROUP BY src.[Object], src.[Type], src.[Index], src.Index_Type
ORDER BY buffer_pages DESC;
---------------------------------------------------------------------
October 18, 2012 at 3:29 am
Excellent scripts... It was really helpful 🙂
- SAMJI
If you marry one they will fight with you, If you marry 2 they will fight for you 🙂
August 24, 2013 at 8:31 am
I do face the same issue, with the output of the "memory used by objects", I could see tempdb come in the first place with huge buffer pages.
Could you please give me some vision to move future how I could address this memory consumption issue?
Thanks,
October 18, 2013 at 4:03 am
Excellent scripts it helped me to fix the Memory Bottleneck.
Thanx..
February 16, 2014 at 6:16 am
What if i need to get the query not the object as many queries could be using the same object, anyway to do that?.
Thanks
February 17, 2014 at 4:30 am
nadersam (2/16/2014)
What if i need to get the query not the object as many queries could be using the same object, anyway to do that?.Thanks
From Glenn Berrys diagnostic script
-- Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads],
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC;
-- This helps you find the most expensive cached stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure
---------------------------------------------------------------------
February 17, 2014 at 4:50 am
Thanks George Sibbald,
I have a question please, i see here you are getting the most expensive stored procedures based on I/O but what i wanted to know is which queries are consuming memory , so are you trying to use I/O as indirect way to have an insight what could be causing memory consumption?.
Thanks
george sibbald (2/17/2014)
nadersam (2/16/2014)
What if i need to get the query not the object as many queries could be using the same object, anyway to do that?.Thanks
From Glenn Berrys diagnostic script
-- This helps you find the most expensive cached stored procedures from a memory perspective-- You should look at this if you see signs of memory pressure
-- Top Cached SPs By Total Physical Reads (SQL 2008). Physical reads relate to disk I/O pressure
SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads],
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count,
qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_physical_reads, qs.total_logical_reads DESC;
February 17, 2014 at 4:57 am
sorry posted wrong script! I have edited script above.
---------------------------------------------------------------------
November 12, 2014 at 4:29 am
Dear Sir,
when i use your query i found same culprit which was you found.
Please tell us how to reduce that culprit.
Thanks.
May 2, 2016 at 9:32 pm
thanks george sibbald for your information
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply