How do I find what is consuming SQL Server's memory?

  • dsbolanos

    SSC Eights!

    Points: 829

    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

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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

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

    SSC Eights!

    Points: 829

    Thanks Gila, I'll check it out to see if i can find something.

  • george sibbald

    SSC Guru

    Points: 104200

    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;

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

  • COOL_ICE

    SSCommitted

    Points: 1646

    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 🙂

  • Kumar-386306

    Default port

    Points: 1464

    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,

  • dm_tapas

    Hall of Fame

    Points: 3515

    Excellent scripts it helped me to fix the Memory Bottleneck.

    Thanx..

  • nadersam

    SSCertifiable

    Points: 6541

    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

  • george sibbald

    SSC Guru

    Points: 104200

    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

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

  • nadersam

    SSCertifiable

    Points: 6541

    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;

  • george sibbald

    SSC Guru

    Points: 104200

    sorry posted wrong script! I have edited script above.

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

  • haridatpurohit

    Right there with Babe

    Points: 793

    Dear Sir,

    when i use your query i found same culprit which was you found.

    Please tell us how to reduce that culprit.

    Thanks.

  • sayedkhalid99

    Ten Centuries

    Points: 1394

    thanks george sibbald for your information

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply