MSSQL - How to measure your SQL Server instance memory utilization – (calculating the memory utilization)

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

Read More