Good script to analyze Buffer pool

• curious_sqldba

  SSC-Dedicated

  Points: 36494

  More actions

  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.

• Sreekanth B

  SSCertifiable

  Points: 6145

  More actions

  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/

• curious_sqldba

  SSC-Dedicated

  Points: 36494

  More actions

  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

• Sreekanth B

  SSCertifiable

  Points: 6145

  More actions

  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 🙂

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  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.

• curious_sqldba

  SSC-Dedicated

  Points: 36494

  More actions

  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.