Find Top 10 consumers of memory from Buffer Pool

  • Comments posted to this topic are about the item Find Top 10 consumers of memory from Buffer Pool

  • I get an error

    Msg 207, Level 16, State 1, Line 4

    Invalid column name 'single_pages_kb'.

    Msg 207, Level 16, State 1, Line 10

    Invalid column name 'single_pages_kb'.

  • That means you are either running SQL2012 or 2014.

    To get the same information for those you would execute the following.

    -- Top 10 consumers of memory from Buffer Pool

    SELECT TOP 10

    [type]

    , SUM(pages_kb) AS memory_in_kb

    FROM

    sys.dm_os_memory_clerks

    GROUP BY

    type

    ORDER BY

    SUM(pages_kb) DESC ;

  • --A slight modification so it displays the memory used in MB as well.

    -- Top 10 consumers of memory from Buffer Pool

    SELECT TOP 10

    [type] AS OBJECT_TYPE

    , SUM(pages_kb) AS memory_in_kb

    , (SUM(pages_kb) / 1024) AS memory_in_MB

    FROM

    sys.dm_os_memory_clerks

    GROUP BY

    type

    ORDER BY

    SUM(pages_kb) DESC ;

  • thank you it works I am testing SQL 2012

Viewing 5 posts - 1 through 4 (of 4 total)

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