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
10 comments:
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
It's Really A Great Post.
Best IT Servicesin Bangalore
It's A Really Nice Post. Looking for Some More stuff.
Best Digital Marketing Courses in Bangalore
We keep your page. Watch it offline again soon. Very interesting article. 1tb external hard disk price
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
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
very informative post, thanks for it, keep adding more information to this. Thank you.
DevOps Online Training
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
wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries
AWS training in chennai | AWS training in annanagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery
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
Post a Comment