locked
Determining the size of Buffer Pool RRS feed

  • Question

  • Hello!

    I'm trying to figure out the size of SQL Server memory buffer pool on my server - this can be done by 1) using the sys.dm_os_buffer_descriptors view or 2)  the sys.dm_os_performance_counters view.

    According to MS the buffer pool size "determines the maximum number of pages that can be cached in the buffer pool at any time in the running instance" so I think the buffer size can be found by counting all (distinct) pages returned by the sys.dm_os_buffer_descriptors view:

    select distinct page_id INTO #PAGES FROM sys.dm_os_buffer_descriptors
    select COUNT(*) AS cached_pages_count, COUNT(*)*8/1024 as BufferSIZEinMB from #PAGES
    DROP TABLE  #PAGES;

    But the sys.dm_os_performance_counters view displayes the values that differ from the one above:

    Q1: Is it the Total pages counter that satisfies MS definition?

    Q2: Why the number of distinct pages from the sys.dm_os_buffer_descriptors view is three times smaller than that from the sys.dm_os_performance_counters\Total pages ?

    Thank you for advance,
    Michael


    Tuesday, October 8, 2019 10:37 AM

Answers

  • I would say "Database pages" come pretty close to the count from sys.dm_os_buffer_descriptors.

    The target pages might what equates to "Max server memory", but you may not be there yet.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by MF47 Wednesday, October 9, 2019 6:50 AM
    Tuesday, October 8, 2019 9:08 PM

All replies

  • I would say "Database pages" come pretty close to the count from sys.dm_os_buffer_descriptors.

    The target pages might what equates to "Max server memory", but you may not be there yet.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by MF47 Wednesday, October 9, 2019 6:50 AM
    Tuesday, October 8, 2019 9:08 PM
  • Thank you very much for the explanation, Erland!

    Regards,

    Michael Firsov

    Wednesday, October 9, 2019 6:50 AM