Home Forums SQL Server 2008 SQL Server 2008 - General How do I find what is consuming SQL Server's memory? RE: How do I find what is consuming SQL Server's memory?

  • February 17, 2014 at 4:50 am

    #1689446

    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;