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

Statystyki filestats – io na serwerze

Transact-SQL

USE master go SET NOCOUNT ON DECLARE @crDate DateTime; DECLARE @hours DECIMAL(18,3), @Days int; DECLARE @FinalHours int, @FinalMinutes int, @FinalSeconds int, @total_seconds int; -- Determine uptime by checking Tempdb creation datetime: SELECT top 1 @crdate=create_date FROM sys.databases WHERE NAME='tempdb' SELECT @hours = DATEDIFF(ss,@crDate,GETDATE())/CAST(60 AS Decimal)/CAST(60 AS Decimal); PRINT 'SQL Server instance '+ @@SERVERNAME + '\' + @@SERVICENAME + ' is Up since: ' + CAST (@crdate as varchar) ; -- From hours to days: SELECT @Days = @hours/CAST(24 AS Decimal); -- Determine the remaining part of the hours: SELECT @FinalHours = @hours - (@Days*CAST(24 AS Decimal)) -- Remaining minutes: SELECT @FinalMinutes = (@hours - (@Days*CAST(24 AS Decimal)) - @FinalHours ) * 60; -- Remaining seconds: SELECT @FinalSeconds = (((@hours - (@Days*CAST(24 AS Decimal)) - @FinalHours ) * 60) - @Finalminutes) * 60; PRINT 'Or: '+ CAST(@Days as varchar) + ' Days, ' + CAST(@FinalHours as varchar) + ' Hours,' + CAST(@FinalMinutes as varchar) + ' Minutes and ' + CAST(@FinalSeconds as varchar) + ' Seconds.' SELECT @total_seconds = (CAST(@Days AS decimal(12,2))*24*3600 + CAST(@Finalhours AS decimal(12,2))*3600 + CAST(@Finalminutes AS decimal(12,2))*60 ) + CAST(@Finalseconds AS decimal(12,2)) PRINT 'Total uptime in seconds: '+ CONVERT(VARCHAR(20) ,@total_seconds ) SELECT @@SERVERNAME as Hostname, @@SERVICENAME as Instancename, @crdate AS SQL_Start_Date_Time , @total_seconds as TotalSeconds_Up SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , (num_of_bytes_read / 1024 / 1024 /1024) as GB_Read_Total, num_of_bytes_read / @total_seconds * 3600 * 24 /1024/1024/1024 as AVG_GB_read_per_day_ESTIMATE, CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes , num_of_bytes_written / 1024 / 1024/1024 as GB_Written_Total, num_of_bytes_written /@total_seconds * 3600 * 24 /1024/1024/1024 as AVG_GB_Written_per_day_ESTIMATE, CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [IO_Stalls] , num_of_reads + num_of_writes AS [Total_IO] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [AVG_IO_stall_ms] FROM sys.dm_io_virtual_file_stats(NULL, NULL) --ORDER BY avg_io_stall_ms DESC ; order by GB_read_total 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

USE master go   SET NOCOUNT ON DECLARE @crDate DateTime; DECLARE @hours DECIMAL(18,3), @Days int; DECLARE @FinalHours int, @FinalMinutes int, @FinalSeconds int, @total_seconds  int;     -- Determine uptime by checking Tempdb creation datetime: SELECT top 1 @crdate=create_date FROM sys.databases WHERE NAME='tempdb' SELECT @hours = DATEDIFF(ss,@crDate,GETDATE())/CAST(60 AS Decimal)/CAST(60 AS Decimal); PRINT 'SQL Server instance '+ @@SERVERNAME + '\' + @@SERVICENAME + ' is Up since: ' + CAST (@crdate as varchar)  ;   -- From hours to days: SELECT @Days = @hours/CAST(24 AS Decimal);   -- Determine the remaining part of the hours: SELECT @FinalHours = @hours - (@Days*CAST(24 AS Decimal))   -- Remaining minutes: SELECT @FinalMinutes = (@hours - (@Days*CAST(24 AS Decimal)) - @FinalHours ) * 60;   -- Remaining seconds: SELECT @FinalSeconds = (((@hours - (@Days*CAST(24 AS Decimal)) - @FinalHours ) * 60) - @Finalminutes) * 60;   PRINT 'Or: '+ CAST(@Days as varchar) + ' Days, ' + CAST(@FinalHours as varchar) + ' Hours,' + CAST(@FinalMinutes as varchar) + ' Minutes and ' +  CAST(@FinalSeconds as varchar) + ' Seconds.'   SELECT  @total_seconds = (CAST(@Days AS decimal(12,2))*24*3600 + CAST(@Finalhours AS decimal(12,2))*3600 + CAST(@Finalminutes AS decimal(12,2))*60 ) + CAST(@Finalseconds AS decimal(12,2)) PRINT 'Total uptime in seconds: '+ CONVERT(VARCHAR(20) ,@total_seconds )   SELECT @@SERVERNAME as Hostname, @@SERVICENAME as Instancename, @crdate AS SQL_Start_Date_Time ,  @total_seconds as TotalSeconds_Up   SELECT  DB_NAME(database_id) AS [Database Name] ,         file_id ,         io_stall_read_ms ,         num_of_reads ,             (num_of_bytes_read / 1024 / 1024 /1024) as GB_Read_Total,             num_of_bytes_read / @total_seconds * 3600 * 24 /1024/1024/1024  as AVG_GB_read_per_day_ESTIMATE,         CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1))             AS [avg_read_stall_ms] ,         io_stall_write_ms ,         num_of_writes ,             num_of_bytes_written / 1024 / 1024/1024 as GB_Written_Total,             num_of_bytes_written /@total_seconds * 3600 * 24 /1024/1024/1024  as AVG_GB_Written_per_day_ESTIMATE,         CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1))             AS [avg_write_stall_ms] ,         io_stall_read_ms + io_stall_write_ms AS [IO_Stalls] ,         num_of_reads + num_of_writes AS [Total_IO] ,         CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads                                                           + num_of_writes)            AS NUMERIC(10,1)) AS [AVG_IO_stall_ms] FROM    sys.dm_io_virtual_file_stats(NULL, NULL) --ORDER BY avg_io_stall_ms DESC ; order by GB_read_total DESC