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
ReplyDeleteWhat'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
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
ReplyDeleteExcellent 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
ReplyDeleteI 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
ReplyDeleteI think this is a piece of very interesting information about SQL and other utilities as well.
ReplyDeleteSQL Server Load Soap API