Advertisement
Guest User

Untitled

a guest
Jul 23rd, 2014
222
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.78 KB | None | 0 0
  1. -- Note: querying sys.dm_os_buffer_descriptors
  2. -- requires the VIEW_SERVER_STATE permission.
  3.  
  4. DECLARE @total_buffer INT;
  5.  
  6. SELECT @total_buffer = cntr_value
  7. FROM sys.dm_os_performance_counters
  8. WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
  9. AND counter_name = 'Total Pages';
  10.  
  11. ;WITH src AS
  12. (
  13. SELECT
  14. database_id, db_buffer_pages = COUNT_BIG(*)
  15. FROM sys.dm_os_buffer_descriptors
  16. --WHERE database_id BETWEEN 5 AND 32766
  17. GROUP BY database_id
  18. )
  19. SELECT
  20. [db_name] = CASE [database_id] WHEN 32767
  21. THEN 'Resource DB'
  22. ELSE DB_NAME([database_id]) END,
  23. db_buffer_pages,
  24. db_buffer_MB = db_buffer_pages / 128,
  25. db_buffer_percent = CONVERT(DECIMAL(6,3),
  26. db_buffer_pages * 100.0 / @total_buffer)
  27. FROM src
  28. ORDER BY db_buffer_MB DESC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement