:::: 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
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]
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



  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

  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