Find Top 10 consumers of memory from Buffer Pool

Question

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

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

muhammadm SSC Enthusiast Points: 188 More actions · Answer 1

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'.

ccp-brabra Grasshopper Points: 10 More actions · Answer 2

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 ;

akhamoshioke Ten Centuries Points: 1110 More actions · Answer 3

--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 ;

muhammadm SSC Enthusiast Points: 188 More actions · Answer 4

SSC Enthusiast

Points: 188

June 15, 2014 at 3:43 pm

thank you it works I am testing SQL 2012