Post reply

Good script to analyze Buffer pool

  • June 6, 2018 at 4:04 pm

    #362129

    Can someone please share a good script to analyze buffer pool, would like to free space ,used space by different objects etc.

  • July 3, 2018 at 12:53 pm

    #1996211

    Refer to below Ed Pollack's article for analyzing your Buffer pool.
    https://www.sqlshack.com/author/edward-pollack/

  • July 3, 2018 at 2:58 pm

    #1996228

    Sreekanth B - Tuesday, July 3, 2018 12:53 PM

    Refer to below Ed Pollack's article for analyzing your Buffer pool.
    https://www.sqlshack.com/author/edward-pollack/

    Looks like he has some good stuff but i couldn't find anything on buffer pool. I ended writing script for something i was looking for, doesn't give all the details but good enough to start 

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    -- Physical Memory

    SELECT ( total_physical_memory_kb / 1048576 ) Total_Physical_Memory_GB ,
       ( available_physical_memory_kb / 1048576 ) Available_Physical_Memory_GB
    FROM sys.dm_os_sys_memory;

    -- Current BufferPoolInGB

    SELECT type ,
       ( SUM(pages_kb)) / 1048576 CurrentBufferPoolInGB
    FROM  sys.dm_os_memory_clerks
    GROUP BY type
    HAVING type LIKE '%bufferpool%';

    -- SQL Instance Level memory settings

    SELECT description ,
       ( CAST(value AS BIGINT) / 1024 ) ConfiguredValueInGB ,
       ( CAST(value_in_use AS BIGINT) / 1024 ) RunningValueInGB
    FROM  sys.configurations
    WHERE  NAME LIKE '%server memory%'
    ORDER BY NAME
    OPTION ( RECOMPILE );

    -- PLE Distribution Per NUMA Node

    DECLARE @CounterPrefix NVARCHAR(30);
    SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN
              'SQLServer:'
             ELSE 'MSSQL$' + @@SERVICENAME + ':'
           END;

    IF OBJECT_ID('tempdb..#perf_counters_init') IS NOT NULL
      DROP TABLE #perf_counters_init;

    IF OBJECT_ID('tempdb..#perf_counters_second') IS NOT NULL
      DROP TABLE #perf_counters_second;

    -- Capture the first counter set
    SELECT CAST(1 AS INT) AS collection_instance ,
       [OBJECT_NAME] ,
       counter_name ,
       instance_name ,
       cntr_value ,
       cntr_type ,
       CURRENT_TIMESTAMP AS collection_time
    INTO #perf_counters_init
    FROM sys.dm_os_performance_counters
    WHERE ( OBJECT_NAME = @CounterPrefix + 'Buffer Node'
        AND counter_name = 'Page life expectancy' );

    -- Wait on Second between data collection
    WAITFOR DELAY '00:00:01';

    -- Capture the second counter set
    SELECT CAST(2 AS INT) AS collection_instance ,
       OBJECT_NAME ,
       counter_name ,
       instance_name ,
       cntr_value ,
       cntr_type ,
       CURRENT_TIMESTAMP AS collection_time
    INTO #perf_counters_second
    FROM sys.dm_os_performance_counters
    WHERE ( OBJECT_NAME = @CounterPrefix + 'Buffer Node'
        AND counter_name = 'Page life expectancy' );

    -- Calculate the cumulative counter values

    SELECT GETDATE() ,
       i.OBJECT_NAME ,
       i.counter_name ,
       i.instance_name ,
       CASE WHEN i.cntr_type = 272696576 THEN s.cntr_value - i.cntr_value
         WHEN i.cntr_type = 65792 THEN s.cntr_value
       END AS Delta_Value
    FROM  #perf_counters_init AS i
       JOIN #perf_counters_second AS s ON i.collection_instance + 1 = s.collection_instance
                   AND i.OBJECT_NAME = s.OBJECT_NAME
                   AND i.counter_name = s.counter_name
                   AND i.instance_name = s.instance_name
    ORDER BY OBJECT_NAME;

    -- Data distribution per memory node

    SELECT memory_node_id ,
       ( locked_page_allocations_kb / 1048576 ) locked_page_allocations_Gb ,
       ( virtual_address_space_committed_kb / 1048576 ) virtual_address_space_committed_GB ,
       ( pages_kb / 1048576 ) pages_GB ,
       ( shared_memory_reserved_kb / 1048576 ) shared_memory_reserved_GB
    FROM sys.dm_os_memory_nodes
    WHERE memory_node_id != 64;

    -- Buffer pool distribution per database. This includes data pages and index pages.

    SELECT CASE WHEN database_id = 32767 THEN 'ResourceDB'
         ELSE DB_NAME(database_id)
       END AS DatabaseName ,
       COUNT(*) AS cached_pages ,
       ( COUNT(*) * 8.0 ) / 1048576 AS BufferPoolInGB
    FROM  sys.dm_os_buffer_descriptors
    GROUP BY database_id
    ORDER BY BufferPoolInGB DESC;
    GO

  • August 9, 2018 at 7:47 am

    #2001013

    curious_sqldba - Tuesday, July 3, 2018 2:58 PM

    Sreekanth B - Tuesday, July 3, 2018 12:53 PM

    Refer to below Ed Pollack's article for analyzing your Buffer pool.
    https://www.sqlshack.com/author/edward-pollack/

    Looks like he has some good stuff but i couldn't find anything on buffer pool. I ended writing script for something i was looking for, doesn't give all the details but good enough to start 

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    -- Physical Memory

    SELECT ( total_physical_memory_kb / 1048576 ) Total_Physical_Memory_GB ,
       ( available_physical_memory_kb / 1048576 ) Available_Physical_Memory_GB
    FROM sys.dm_os_sys_memory;

    -- Current BufferPoolInGB

    SELECT type ,
       ( SUM(pages_kb)) / 1048576 CurrentBufferPoolInGB
    FROM  sys.dm_os_memory_clerks
    GROUP BY type
    HAVING type LIKE '%bufferpool%';

    -- SQL Instance Level memory settings

    SELECT description ,
       ( CAST(value AS BIGINT) / 1024 ) ConfiguredValueInGB ,
       ( CAST(value_in_use AS BIGINT) / 1024 ) RunningValueInGB
    FROM  sys.configurations
    WHERE  NAME LIKE '%server memory%'
    ORDER BY NAME
    OPTION ( RECOMPILE );

    -- PLE Distribution Per NUMA Node

    DECLARE @CounterPrefix NVARCHAR(30);
    SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN
              'SQLServer:'
             ELSE 'MSSQL$' + @@SERVICENAME + ':'
           END;

    IF OBJECT_ID('tempdb..#perf_counters_init') IS NOT NULL
      DROP TABLE #perf_counters_init;

    IF OBJECT_ID('tempdb..#perf_counters_second') IS NOT NULL
      DROP TABLE #perf_counters_second;

    -- Capture the first counter set
    SELECT CAST(1 AS INT) AS collection_instance ,
       [OBJECT_NAME] ,
       counter_name ,
       instance_name ,
       cntr_value ,
       cntr_type ,
       CURRENT_TIMESTAMP AS collection_time
    INTO #perf_counters_init
    FROM sys.dm_os_performance_counters
    WHERE ( OBJECT_NAME = @CounterPrefix + 'Buffer Node'
        AND counter_name = 'Page life expectancy' );

    -- Wait on Second between data collection
    WAITFOR DELAY '00:00:01';

    -- Capture the second counter set
    SELECT CAST(2 AS INT) AS collection_instance ,
       OBJECT_NAME ,
       counter_name ,
       instance_name ,
       cntr_value ,
       cntr_type ,
       CURRENT_TIMESTAMP AS collection_time
    INTO #perf_counters_second
    FROM sys.dm_os_performance_counters
    WHERE ( OBJECT_NAME = @CounterPrefix + 'Buffer Node'
        AND counter_name = 'Page life expectancy' );

    -- Calculate the cumulative counter values

    SELECT GETDATE() ,
       i.OBJECT_NAME ,
       i.counter_name ,
       i.instance_name ,
       CASE WHEN i.cntr_type = 272696576 THEN s.cntr_value - i.cntr_value
         WHEN i.cntr_type = 65792 THEN s.cntr_value
       END AS Delta_Value
    FROM  #perf_counters_init AS i
       JOIN #perf_counters_second AS s ON i.collection_instance + 1 = s.collection_instance
                   AND i.OBJECT_NAME = s.OBJECT_NAME
                   AND i.counter_name = s.counter_name
                   AND i.instance_name = s.instance_name
    ORDER BY OBJECT_NAME;

    -- Data distribution per memory node

    SELECT memory_node_id ,
       ( locked_page_allocations_kb / 1048576 ) locked_page_allocations_Gb ,
       ( virtual_address_space_committed_kb / 1048576 ) virtual_address_space_committed_GB ,
       ( pages_kb / 1048576 ) pages_GB ,
       ( shared_memory_reserved_kb / 1048576 ) shared_memory_reserved_GB
    FROM sys.dm_os_memory_nodes
    WHERE memory_node_id != 64;

    -- Buffer pool distribution per database. This includes data pages and index pages.

    SELECT CASE WHEN database_id = 32767 THEN 'ResourceDB'
         ELSE DB_NAME(database_id)
       END AS DatabaseName ,
       COUNT(*) AS cached_pages ,
       ( COUNT(*) * 8.0 ) / 1048576 AS BufferPoolInGB
    FROM  sys.dm_os_buffer_descriptors
    GROUP BY database_id
    ORDER BY BufferPoolInGB DESC;
    GO

    Oops! My Bad...I meant to post this link of his. https://www.sqlshack.com/insight-into-the-sql-server-buffer-cache/
    Also, I use Glenn Berry's DMV scripts to analyze buffer pool very often. Apologize for my earlier link, looks like I provided Ed Pollack's profile link instead of his specific article 🙂

  • August 9, 2018 at 7:50 am

    #2001014

    Sreekanth B - Thursday, August 9, 2018 7:47 AM

    curious_sqldba - Tuesday, July 3, 2018 2:58 PM

    Sreekanth B - Tuesday, July 3, 2018 12:53 PM

    Refer to below Ed Pollack's article for analyzing your Buffer pool.
    https://www.sqlshack.com/author/edward-pollack/

    Looks like he has some good stuff but i couldn't find anything on buffer pool. I ended writing script for something i was looking for, doesn't give all the details but good enough to start 

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    -- Physical Memory

    SELECT ( total_physical_memory_kb / 1048576 ) Total_Physical_Memory_GB ,
       ( available_physical_memory_kb / 1048576 ) Available_Physical_Memory_GB
    FROM sys.dm_os_sys_memory;

    -- Current BufferPoolInGB

    SELECT type ,
       ( SUM(pages_kb)) / 1048576 CurrentBufferPoolInGB
    FROM  sys.dm_os_memory_clerks
    GROUP BY type
    HAVING type LIKE '%bufferpool%';

    -- SQL Instance Level memory settings

    SELECT description ,
       ( CAST(value AS BIGINT) / 1024 ) ConfiguredValueInGB ,
       ( CAST(value_in_use AS BIGINT) / 1024 ) RunningValueInGB
    FROM  sys.configurations
    WHERE  NAME LIKE '%server memory%'
    ORDER BY NAME
    OPTION ( RECOMPILE );

    -- PLE Distribution Per NUMA Node

    DECLARE @CounterPrefix NVARCHAR(30);
    SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN
              'SQLServer:'
             ELSE 'MSSQL$' + @@SERVICENAME + ':'
           END;

    IF OBJECT_ID('tempdb..#perf_counters_init') IS NOT NULL
      DROP TABLE #perf_counters_init;

    IF OBJECT_ID('tempdb..#perf_counters_second') IS NOT NULL
      DROP TABLE #perf_counters_second;

    -- Capture the first counter set
    SELECT CAST(1 AS INT) AS collection_instance ,
       [OBJECT_NAME] ,
       counter_name ,
       instance_name ,
       cntr_value ,
       cntr_type ,
       CURRENT_TIMESTAMP AS collection_time
    INTO #perf_counters_init
    FROM sys.dm_os_performance_counters
    WHERE ( OBJECT_NAME = @CounterPrefix + 'Buffer Node'
        AND counter_name = 'Page life expectancy' );

    -- Wait on Second between data collection
    WAITFOR DELAY '00:00:01';

    -- Capture the second counter set
    SELECT CAST(2 AS INT) AS collection_instance ,
       OBJECT_NAME ,
       counter_name ,
       instance_name ,
       cntr_value ,
       cntr_type ,
       CURRENT_TIMESTAMP AS collection_time
    INTO #perf_counters_second
    FROM sys.dm_os_performance_counters
    WHERE ( OBJECT_NAME = @CounterPrefix + 'Buffer Node'
        AND counter_name = 'Page life expectancy' );

    -- Calculate the cumulative counter values

    SELECT GETDATE() ,
       i.OBJECT_NAME ,
       i.counter_name ,
       i.instance_name ,
       CASE WHEN i.cntr_type = 272696576 THEN s.cntr_value - i.cntr_value
         WHEN i.cntr_type = 65792 THEN s.cntr_value
       END AS Delta_Value
    FROM  #perf_counters_init AS i
       JOIN #perf_counters_second AS s ON i.collection_instance + 1 = s.collection_instance
                   AND i.OBJECT_NAME = s.OBJECT_NAME
                   AND i.counter_name = s.counter_name
                   AND i.instance_name = s.instance_name
    ORDER BY OBJECT_NAME;

    -- Data distribution per memory node

    SELECT memory_node_id ,
       ( locked_page_allocations_kb / 1048576 ) locked_page_allocations_Gb ,
       ( virtual_address_space_committed_kb / 1048576 ) virtual_address_space_committed_GB ,
       ( pages_kb / 1048576 ) pages_GB ,
       ( shared_memory_reserved_kb / 1048576 ) shared_memory_reserved_GB
    FROM sys.dm_os_memory_nodes
    WHERE memory_node_id != 64;

    -- Buffer pool distribution per database. This includes data pages and index pages.

    SELECT CASE WHEN database_id = 32767 THEN 'ResourceDB'
         ELSE DB_NAME(database_id)
       END AS DatabaseName ,
       COUNT(*) AS cached_pages ,
       ( COUNT(*) * 8.0 ) / 1048576 AS BufferPoolInGB
    FROM  sys.dm_os_buffer_descriptors
    GROUP BY database_id
    ORDER BY BufferPoolInGB DESC;
    GO

    Oops! My Bad...I meant to post this link of his. https://www.sqlshack.com/insight-into-the-sql-server-buffer-cache/
    Also, I use Glenn Berry's DMV scripts to analyze buffer pool very often. Apologize for my earlier link, looks like I provided Ed Pollack's profile link instead of his specific article 🙂

    These scripts are available everywhere. Not sure if you claim other's script as yours!!!

    Thanks.

  • August 9, 2018 at 8:34 am

    #2001029

    SQL-DBA-01 - Thursday, August 9, 2018 7:50 AM

    Sreekanth B - Thursday, August 9, 2018 7:47 AM

    curious_sqldba - Tuesday, July 3, 2018 2:58 PM

    Sreekanth B - Tuesday, July 3, 2018 12:53 PM

    Refer to below Ed Pollack's article for analyzing your Buffer pool.
    https://www.sqlshack.com/author/edward-pollack/

    Looks like he has some good stuff but i couldn't find anything on buffer pool. I ended writing script for something i was looking for, doesn't give all the details but good enough to start 

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    -- Physical Memory

    SELECT ( total_physical_memory_kb / 1048576 ) Total_Physical_Memory_GB ,
       ( available_physical_memory_kb / 1048576 ) Available_Physical_Memory_GB
    FROM sys.dm_os_sys_memory;

    -- Current BufferPoolInGB

    SELECT type ,
       ( SUM(pages_kb)) / 1048576 CurrentBufferPoolInGB
    FROM  sys.dm_os_memory_clerks
    GROUP BY type
    HAVING type LIKE '%bufferpool%';

    -- SQL Instance Level memory settings

    SELECT description ,
       ( CAST(value AS BIGINT) / 1024 ) ConfiguredValueInGB ,
       ( CAST(value_in_use AS BIGINT) / 1024 ) RunningValueInGB
    FROM  sys.configurations
    WHERE  NAME LIKE '%server memory%'
    ORDER BY NAME
    OPTION ( RECOMPILE );

    -- PLE Distribution Per NUMA Node

    DECLARE @CounterPrefix NVARCHAR(30);
    SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN
              'SQLServer:'
             ELSE 'MSSQL$' + @@SERVICENAME + ':'
           END;

    IF OBJECT_ID('tempdb..#perf_counters_init') IS NOT NULL
      DROP TABLE #perf_counters_init;

    IF OBJECT_ID('tempdb..#perf_counters_second') IS NOT NULL
      DROP TABLE #perf_counters_second;

    -- Capture the first counter set
    SELECT CAST(1 AS INT) AS collection_instance ,
       [OBJECT_NAME] ,
       counter_name ,
       instance_name ,
       cntr_value ,
       cntr_type ,
       CURRENT_TIMESTAMP AS collection_time
    INTO #perf_counters_init
    FROM sys.dm_os_performance_counters
    WHERE ( OBJECT_NAME = @CounterPrefix + 'Buffer Node'
        AND counter_name = 'Page life expectancy' );

    -- Wait on Second between data collection
    WAITFOR DELAY '00:00:01';

    -- Capture the second counter set
    SELECT CAST(2 AS INT) AS collection_instance ,
       OBJECT_NAME ,
       counter_name ,
       instance_name ,
       cntr_value ,
       cntr_type ,
       CURRENT_TIMESTAMP AS collection_time
    INTO #perf_counters_second
    FROM sys.dm_os_performance_counters
    WHERE ( OBJECT_NAME = @CounterPrefix + 'Buffer Node'
        AND counter_name = 'Page life expectancy' );

    -- Calculate the cumulative counter values

    SELECT GETDATE() ,
       i.OBJECT_NAME ,
       i.counter_name ,
       i.instance_name ,
       CASE WHEN i.cntr_type = 272696576 THEN s.cntr_value - i.cntr_value
         WHEN i.cntr_type = 65792 THEN s.cntr_value
       END AS Delta_Value
    FROM  #perf_counters_init AS i
       JOIN #perf_counters_second AS s ON i.collection_instance + 1 = s.collection_instance
                   AND i.OBJECT_NAME = s.OBJECT_NAME
                   AND i.counter_name = s.counter_name
                   AND i.instance_name = s.instance_name
    ORDER BY OBJECT_NAME;

    -- Data distribution per memory node

    SELECT memory_node_id ,
       ( locked_page_allocations_kb / 1048576 ) locked_page_allocations_Gb ,
       ( virtual_address_space_committed_kb / 1048576 ) virtual_address_space_committed_GB ,
       ( pages_kb / 1048576 ) pages_GB ,
       ( shared_memory_reserved_kb / 1048576 ) shared_memory_reserved_GB
    FROM sys.dm_os_memory_nodes
    WHERE memory_node_id != 64;

    -- Buffer pool distribution per database. This includes data pages and index pages.

    SELECT CASE WHEN database_id = 32767 THEN 'ResourceDB'
         ELSE DB_NAME(database_id)
       END AS DatabaseName ,
       COUNT(*) AS cached_pages ,
       ( COUNT(*) * 8.0 ) / 1048576 AS BufferPoolInGB
    FROM  sys.dm_os_buffer_descriptors
    GROUP BY database_id
    ORDER BY BufferPoolInGB DESC;
    GO

    Oops! My Bad...I meant to post this link of his. https://www.sqlshack.com/insight-into-the-sql-server-buffer-cache/
    Also, I use Glenn Berry's DMV scripts to analyze buffer pool very often. Apologize for my earlier link, looks like I provided Ed Pollack's profile link instead of his specific article 🙂

    These scripts are available everywhere. Not sure if you claim other's script as yours!!!

     Look Mr who ever you are, you obviously have a lot of time to stalk people and comment them. I realize you are mocking me based on my reply to your other post , you are taking it way tooo personal. Bottom line is ASK YOUR QUESTION CLEARLY.

Viewing 6 posts - 1 through 6 (of 6 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy