1 stycznia 190015 kwietnia 2020 Michał PawlikowskiPrzydatne skrypty

Session statistics – statystyki sesji cpu memory itp

Transact-SQL

SELECT sys.dm_exec_sessions.session_id AS [SESSION ID] , DB_NAME(su.database_id) AS [DATABASE Name] , host_name AS [System Name] , program_name AS [Program Name] , login_name AS [USER Name] , status , cpu_time AS [CPU TIME (in milisec)] , total_scheduled_time AS [Total Scheduled TIME (in milisec)] , total_elapsed_time AS [Elapsed TIME (in milisec)] , ( memory_usage * 8 ) AS [Memory USAGE (in KB)] , ( user_objects_alloc_page_count * 8 ) / 1024 AS [SPACE Allocated FOR USER Objects (in MB)] , ( user_objects_dealloc_page_count * 8 ) / 1024 AS [SPACE Deallocated FOR USER Objects (in MB)] , ( internal_objects_alloc_page_count * 8 ) AS [SPACE Allocated FOR Internal Objects (in KB)] , ( internal_objects_dealloc_page_count * 8 ) AS [SPACE Deallocated FOR Internal Objects (in KB)] , CASE is_user_process WHEN 1 THEN 'user session' WHEN 0 THEN 'system session' END AS [SESSION Type] , row_count AS [ROW COUNT] FROM sys.dm_db_session_space_usage su INNER JOIN sys.dm_exec_sessions ON su.session_id = sys.dm_exec_sessions.session_id ORDER BY [SPACE Allocated FOR USER Objects (in MB)] DESC;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SELECT  sys.dm_exec_sessions.session_id AS [SESSION ID] ,         DB_NAME(su.database_id) AS [DATABASE Name] ,         host_name AS [System Name] ,         program_name AS [Program Name] ,         login_name AS [USER Name] ,         status ,         cpu_time AS [CPU TIME (in milisec)] ,         total_scheduled_time AS [Total Scheduled TIME (in milisec)] ,         total_elapsed_time AS [Elapsed TIME (in milisec)] ,         ( memory_usage * 8 ) AS [Memory USAGE (in KB)] ,         ( user_objects_alloc_page_count * 8 ) / 1024 AS [SPACE Allocated FOR USER Objects (in MB)] ,         ( user_objects_dealloc_page_count * 8 ) / 1024 AS [SPACE Deallocated FOR USER Objects (in MB)] ,         ( internal_objects_alloc_page_count * 8 ) AS [SPACE Allocated FOR Internal Objects (in KB)] ,         ( internal_objects_dealloc_page_count * 8 ) AS [SPACE Deallocated FOR Internal Objects (in KB)] ,         CASE is_user_process           WHEN 1 THEN 'user session'           WHEN 0 THEN 'system session'         END AS [SESSION Type] ,         row_count AS [ROW COUNT] FROM    sys.dm_db_session_space_usage su         INNER JOIN sys.dm_exec_sessions ON su.session_id = sys.dm_exec_sessions.session_id ORDER BY [SPACE Allocated FOR USER Objects (in MB)] DESC;

 

1 stycznia 190015 kwietnia 2020 Michał PawlikowskiPrzydatne skrypty

Wykorzystanie pamięci przez bazy i obiekty – memory consumption

Transact-SQL

--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;

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;