Thursday, May 14, 2020

SQL KILLBILL What Is Running SnapShot ?

KUNG FU SQL - SNAPSHOT OF SITUATION ON ANY SQL SERVER

--kill 111
              SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

              SELECT
                       @@servername as 'SrvrName'
                     ,GETDATE() as 'SnapShotTime'
                     ,StartTime          = er.start_time
                     ,SPID                = er.session_id
                     ,BlkBy              = er.blocking_session_id     
                      ,ElapsedMS          = er.total_elapsed_time
                     ,CPU                = er.cpu_time
                     ,IOReads            = er.logical_reads + er.reads
                     ,IOWrites           = er.writes    
                      ,Executions         = ec.execution_count 
                      ,CommandType        = er.command        
                      ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,qt.dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) 
                      ,SQLStatement       =
                           SUBSTRING
                           (
                                  qt.text,
                                  er.statement_start_offset/2,
                                  (CASE WHEN er.statement_end_offset = -1
                                         THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                                         ELSE er.statement_end_offset
                                         END - er.statement_start_offset)/2
                           )       
                      ,STATUS             = ses.STATUS
                     ,[Login]            = ses.login_name
                     ,Host               = ses.host_name
                     ,DBName             = DB_Name(er.database_id)
                     ,LastWaitType       = er.last_wait_type
                     ,Protocol           = con.net_transport
                     ,transaction_isolation =
                           CASE ses.transaction_isolation_level
                                  WHEN 0 THEN 'Unspecified'
                                  WHEN 1 THEN 'Read Uncommitted'
                                  WHEN 2 THEN 'Read Committed'
                                  WHEN 3 THEN 'Repeatable'
                                  WHEN 4 THEN 'Serializable'
                                  WHEN 5 THEN 'Snapshot'
                           END
                     ,ConnectionWrites   = con.num_writes
                     ,ConnectionReads    = con.num_reads
                     ,ClientAddress      = con.client_net_address
                     ,Authentication     = con.auth_scheme
                     ,QueryPlan          = CONVERT(nvarchar(MAX),qp.query_plan)
                     ,PlanHandle         = er.plan_handle
              --INTO myDatabase.dbo.dmv_reqs_20200514
              FROM sys.dm_exec_requests er
              LEFT JOIN sys.dm_exec_sessions ses
              ON ses.session_id = er.session_id
              LEFT JOIN sys.dm_exec_connections con
              ON con.session_id = ses.session_id
              CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
              OUTER APPLY
              (
                     SELECT execution_count = MAX(cp.usecounts)
                     FROM sys.dm_exec_cached_plans cp
                     WHERE cp.plan_handle = er.plan_handle
              ) ec
              CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
              WHERE er.session_id <> @@SPID
              ORDER BY
                     er.blocking_session_id DESC,
                     er.logical_reads + er.reads DESC,
                     er.session_id OPTION (RECOMPILE);

GO

No comments:

Post a Comment