Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Note: querying sys.dm_os_buffer_descriptors
- -- requires the VIEW_SERVER_STATE permission.
- DECLARE @total_buffer INT;
- SELECT @total_buffer = cntr_value
- FROM sys.dm_os_performance_counters
- WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
- AND counter_name = 'Total Pages';
- ;WITH src AS
- (
- SELECT
- database_id, db_buffer_pages = COUNT_BIG(*)
- FROM sys.dm_os_buffer_descriptors
- --WHERE database_id BETWEEN 5 AND 32766
- GROUP BY database_id
- )
- SELECT
- [db_name] = CASE [database_id] WHEN 32767
- THEN 'Resource DB'
- ELSE DB_NAME([database_id]) END,
- db_buffer_pages,
- db_buffer_MB = db_buffer_pages / 128,
- db_buffer_percent = CONVERT(DECIMAL(6,3),
- db_buffer_pages * 100.0 / @total_buffer)
- FROM src
- ORDER BY db_buffer_MB DESC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement