Post reply

find which database is using the most sql server memory

  • August 26, 2011 at 9:57 am

    #243896

    Hi

    Is there a way to find out which database in the server is using or used the most sql server memory?

    thanks

  • August 28, 2011 at 6:29 pm

    #1374356

    Hello,

    You could query sys.dm_os_buffer_descriptors which returns information about all data pages currently in the buffer pool. this will give you an idea of how the pool is split up between your databases.

    You will find information in Books Online on this DMV and from there I plucked this example query...

    [font="Courier New"]SELECT count(*)AS cached_pages_count

    ,CASE database_id

    WHEN 32767 THEN 'ResourceDb'

    ELSE db_name(database_id)

    END AS Database_name

    FROM sys.dm_os_buffer_descriptors

    GROUP BY db_name(database_id) ,database_id

    ORDER BY cached_pages_count DESC;[/font]

  • August 31, 2011 at 4:21 am

    #1375374

    this is what I use

    --

    -- Server buffer Cache

    --

    select isnull(db_name(database_id),'Total') as 'Database Name',

    convert(numeric(8,2),count(page_id)/128.0) as Mb

    from sys.dm_os_buffer_descriptors with (nolock)

    where database_id !=32767

    group by database_id

    with rollup

    order by count(page_id) desc;

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • September 1, 2011 at 7:39 am

    #1375949

    thanks!!

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

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