|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
--sprawdzanie baz 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; --sprawdzanie obiektow w bazie USE BAZA_DO_SPRAWDZENIA; GO WITH src AS ( SELECT [Object] = o.name , [Type] = o.type_desc , [Index] = COALESCE(i.name, '') , [Index_Type] = i.type_desc , p.[object_id] , p.index_id , au.allocation_unit_id FROM sys.partitions AS p INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id] INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id WHERE au.[type] IN ( 1, 2, 3 ) AND o.is_ms_shipped = 0 ) SELECT src.[Object] , src.[Type] , src.[Index] , src.Index_Type , buffer_pages = COUNT_BIG(b.page_id) , buffer_mb = COUNT_BIG(b.page_id) / 128 FROM src INNER JOIN sys.dm_os_buffer_descriptors AS b ON src.allocation_unit_id = b.allocation_unit_id WHERE b.database_id = DB_ID() GROUP BY src.[Object] , src.[Type] , src.[Index] , src.Index_Type ORDER BY buffer_pages DESC; |