:::: MENU ::::

Tuesday, January 26, 2016

We addressed in the last post the difference between the memory on the server level and the buffer pool on the database engine, in this post we will address how to calculate the buffer pool consumption for the whole SQL Server instance.
In order to calculate this figure we need to get some information for different factors like:

1-   Total number of data pages that buffer pool section can hold inside the server memory.
This value can be calculated using the following query by querying sys.configurations catalog view  
select convert(int,value_in_use)*1024/8 as [TotalNumofPages] from sys.configurations where name='max server memory (MB)'

         2- Number of pages for every database in the SQL instance, we can get that
             from DMV sys.dm_os_buffer_descriptors
              SELECT
db_name(database_id) as [DBName],COUNT_BIG(*) as [NumofPages]
  FROM sys.dm_os_buffer_descriptors
GROUP BY database_id

After getting this data then we have to make some calculations to get the following values:
Current Buffer Pool usage (GB)
Current Buffer Pool usage (number of pages)
Current Buffer Pool usage (percentage)
And to add date/time column for every record we collect.

The complete script:
DECLARE @TotalNumofPages INT;

SELECT @TotalNumofPages=convert(INT,value_in_use)*1024/8 FROM sys.configurations WHERE name='max server memory (MB)'

SELECT db_name(database_id) as [DBName],COUNT_BIG(*) as [NumofPages]
INTO #DBBPUSG
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id

SELECT sum(convert(DECIMAL (10,3),[NumofPages] / 128)/1024) AS [Current used memory by BP (GB)],
sum([NumofPages]) AS [Current used pages in BP],SUM(CONVERT(DECIMAL(6,3),
[NumofPages] * 100.0 / @TotalNumofPages)) AS [Current used percentage of BP],convert(SMALLDATETIME,GETDATE()) AS [Date\Time] FROM #DBBPUSG
GO

DROP TABLE #DBBPUSG
GO

5 comments:


  1. What's up i am kavin, its my first time to commenting anywhere, when i read this piece of writing i thought i could also create comment due to this sensible paragraph. itunes account login

    ReplyDelete
  2. An added one time payment has got the greatest impact if you pay it right after taking your mortgage. canada mortgage calculator As a fellow bluenoser Reni personally was no brainer choice personally when searching for a knowledgeable and qualified broker. mortgage calculator

    ReplyDelete
  3. Excellent information, I am heartily thankful to you that you have shared this information with us. I got some different kind of knowledge from your article, and it is helpful for everyone. Thanks for share it.Miami IT services

    ReplyDelete
  4. I admire this article for the well-researched content and excellent wording. I got so involved in this material that I couldn’t stop reading. I am impressed with your work and skill. Thank you so much. computer support houston tx

    ReplyDelete
  5. I think this is a piece of very interesting information about SQL and other utilities as well.

    SQL Server Load Soap API

    ReplyDelete