Post reply

SQL Server 2005 Memory

  • September 29, 2011 at 8:12 am

    #258028

    Hi Guys,

    Perfmon--->Memory--->Manager --> Target Server Memory = Around 1.6GB

    Right click task manager sqlservr.exe = around 1.6GB

    I have a few databases in that particular instance, i wish to find out how the 1.6GB was utilized among the databases. In short to find out the most heavily utilized db on memory.

    I executed this statement:

    SELECT LEFT(CASE database_id

    WHEN 32767 THEN 'ResourceDb'

    ELSE db_name(database_id)

    END, 20) AS Database_Name,

    count(*)AS Buffered_Page_Count,

    count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB

    FROM sys.dm_os_buffer_descriptors

    GROUP BY db_name(database_id) ,database_id

    ORDER BY Buffered_Page_Count DESC

    However the total was only about 500MB+. Where did the rest of the memory goes in sql server? Anyway to find out?

    thanks

  • September 29, 2011 at 10:31 am

    #1388498

    sys.dm_os_buffer_descriptors DMV returns info regarding pages in buffer pool (or cache).

    Your numbers show that right now for current load sql server need only ~0.5GB from available 1.5GB.

    Check BOL on Memory Management- a lot of useful info.

  • September 29, 2011 at 10:45 am

    #1388514

    Hi Yuri,

    thanks for the reply.. in this case why is it holding to 1.5GB.. the exe process.

  • September 29, 2011 at 10:56 am

    #1388522

    Target Server Memory presents upper memory limit sql server plans to use-

    probably it (1,5GB) was assigned as Max server memory (configuration option).

    But right now it (sql server) uses only ~0.5GB (probably does not need more)

  • September 29, 2011 at 10:58 am

    #1388523

    Apology, it's the total server memory that is showing 1.5GB.

    I'm curious as since from the dmv it shows that only 0.5GB was used for cahing data, where is the remaining 1gb used in?

    the exe process from task manager also show as 1.5gb.

    thanks for the help!

  • September 29, 2011 at 11:32 am

    #1388542

    It (1GB) is not used right now- no needs

  • September 29, 2011 at 11:36 am

    #1388549

    Hi Yuri,

    Does it means that:

    1. the additional 1gb is allocated to sql server but not in used?

    2. since only 500mb of data is cached in the buffer pool now, how did it derive to take 1.5gb for the physical memory? is it because previously it has hold 1.5 gb of data and since no pressure from os, the 1.5 gb remains allocated to sql server.. however if this is the case, why doesn't it hold the full 1.5 gb of data?

    thanks

  • September 30, 2011 at 10:25 pm

    #1389074

    hi guys, anyone can advise me? thanks!!

Viewing 8 posts - 1 through 7 (of 7 total)

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