T-SQL to find Memory Used by Database and its Objects | SansSQL

Saturday, June 24, 2017

T-SQL to find Memory Used by Database and its Objects

When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server and we use this for calculating the memory used by the Databases and its objects.

-- Memory Used by All Databases
SELECT 
 CAST (COUNT(1)*8/1024 AS decimal(8,2)) AS [MemoryUsedByAllDB's(MB)]
,CAST (COUNT(1)*8/1024/1024 AS decimal(8,2)) AS [MemoryUsedByAllDB's(GB)]
FROM sys.dm_os_buffer_descriptors
GO

-- Memory Used by Individual Databases
SELECT
 CASE WHEN database_id = 32767 
      THEN 'ResourceDB' 
      ELSE DB_NAME(database_id) 
 END AS [DatabaseName]
,COUNT(1) AS [CachedPages]
,CAST (COUNT(1)*8/1024 AS decimal(8,2)) AS [MemoryUsed(MB)]
,CAST(COUNT(1)*8/1024/1024 AS decimal(8,2)) AS [MemoryUsed(GB)]
,CASE WHEN is_modified = 1 
      THEN 'Dirty Page' 
      ELSE 'Clean Page' 
 END AS [PageState]
FROM
sys.dm_os_buffer_descriptors
GROUP BY database_id
	,is_modified
ORDER BY CachedPages DESC
	,DB_NAME(database_id)
GO

-- Memory Used by Objects within a Database. Change the <<database name>> to your desiged Database Name
USE <<database name>>
GO
SELECT
 CASE WHEN database_id = 32767 
      THEN 'ResourceDB' 
      ELSE DB_NAME(database_id) 
 END AS [DatabaseName],
 InnerData.ObjectName,
 InnerData.ObjectType,
 InnerData.IndexName,
 InnerData.IndexType
,COUNT(1) AS [CachedPages]
,CAST (COUNT(1)*8/1024 AS decimal(8,2)) AS [MemoryUsed(MB)]
,CAST(COUNT(1)*8/1024/1024 AS decimal(8,2)) AS [MemoryUsed(GB)]
,CASE WHEN is_modified = 1 
      THEN 'Dirty Page' 
      ELSE 'Clean Page' 
 END AS [PageState]
FROM
sys.dm_os_buffer_descriptors SBD INNER JOIN 
(
SELECT
SAU.allocation_unit_id, 
SO.name AS [ObjectName],
SO.type_desc AS [ObjectType],
SI.name AS [IndexName],
SI.type_desc AS [IndexType]
FROM sys.allocation_units SAU INNER JOIN sys.partitions SP
ON SAU.container_id = SP.partition_id INNER JOIN sys.objects SO
ON SP.object_id = SO.object_id INNER JOIN sys.indexes SI
ON SO.object_id = SI.object_id
WHERE SO.is_ms_shipped=0
) AS InnerData
ON SBD.allocation_unit_id = InnerData.allocation_unit_id
WHERE DB_NAME(SBD.database_id) = DB_NAME()
GROUP BY database_id
	,is_modified
	,InnerData.ObjectName
	,InnerData.ObjectType
	,InnerData.IndexName
	,InnerData.IndexType
ORDER BY CachedPages DESC
	,InnerData.ObjectName
GO

11 comments:

ananya shri said...

any possibility to connect excel data to website. i think its no possibility to do this. if we can connect excel data into our website is there any thread for it
Ananya,
Workday training in chennai

హాట్‌గర్ల్స్ said...

It's Really A Great Post.
Best IT Servicesin Bangalore

Unknown said...

It's A Really Nice Post. Looking for Some More stuff.
Best Digital Marketing Courses in Bangalore

Unknown said...

We keep your page. Watch it offline again soon. Very interesting article. 1tb external hard disk price 

Unknown said...

Your new valuable key points imply much a person like me and extremely more to my office workers. With thanks; from every one of us.

devops training in bangalore

devops training in chennai

Chandu Chinnu said...

Thanks for giving a great information about SQL Good Explination nice Article
anyone want to learn advance devops tools or devops online training
DevOps Online Training contact Us: 9704455959

Unknown said...

very informative post, thanks for it, keep adding more information to this. Thank you.
DevOps Online Training

Raji said...

Great and nice blog thanks sharing..I just want to say that all the information you have given here is awesome...Thank you very much for this one.
Devops Training in Chennai | Devops Training Institute in Chennai

chandhran said...

Such a mind-blowing blog. I am very fascinated with the information.
architecture of selenium
advantages of angular
features of angularjs
validity of aws certification
android 8.0
aws interview questions and answers for freshers pdf

Rupesh Kumar said...

This blog is really nice and informative blog, The explanation given is really comprehensive and informative. Ziyyara Edutech brings you top-notch online tuition for Class 11. Our experienced tutors provide personalized guidance and comprehensive support to help you achieve your academic goals.
For more info Contact us: +91-9654271931, +971-505593798 or visit online tuition for class 11

Rupesh Kumar said...

I liked your blog too much and it's well informative. Unlock the door to impeccable English fluency with Ziyyara Edutech’s top-notch online home tuition services in Saudi Arabia.
For Book a demo now Best english speaking course in Riyadh

Post a Comment

Ads