Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How do I find what is consuming SQL Server's memory? Expand / Collapse
Author
Message
Posted Friday, December 02, 2011 7:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 02, 2013 2:11 PM
Points: 27, Visits: 241
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
Post #1215340
Posted Friday, December 02, 2011 7:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1215351
Posted Friday, December 02, 2011 8:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 02, 2013 2:11 PM
Points: 27, Visits: 241
Thanks Gila, I'll check it out to see if i can find something.
Post #1215440
Posted Friday, December 02, 2011 9:52 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 5,849, Visits: 12,585
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;



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

Post #1215519
Posted Thursday, October 18, 2012 3:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 6:10 AM
Points: 350, Visits: 429
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
Post #1374214
Posted Saturday, August 24, 2013 8:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 26, 2013 3:12 AM
Points: 233, Visits: 246
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,
Post #1488123
Posted Friday, October 18, 2013 4:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 11:34 AM
Points: 1,024, Visits: 373

Excellent scripts it helped me to fix the Memory Bottleneck.
Thanx..
Post #1506060
Posted Sunday, February 16, 2014 6:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 07, 2014 5:18 AM
Points: 85, Visits: 205
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
Post #1541900
Posted Monday, February 17, 2014 4:30 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 5,849, Visits: 12,585
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



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

Post #1542039
Posted Monday, February 17, 2014 4:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 07, 2014 5:18 AM
Points: 85, Visits: 205
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;

Post #1542045
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse