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