Běžící tasky nad databází

MSSQL

21. 12. 2018

Simple

 SELECT DISTINCT
     SPID           = er.session_id
    ,STATUS         = ses.STATUS
    ,Login          = ses.login_name
    ,Host           = ses.host_name
    ,BlkBy          = er.blocking_session_id
    ,DBName         = DB_Name(er.database_id)
    ,CommandType    = er.command
    ,ObjectName     = OBJECT_NAME(st.objectid)
    ,CPUTime        = er.cpu_time
    ,StartTime      = er.start_time
    ,TimeElapsed    = CAST(GETDATE() - er.start_time AS TIME)
    ,SQLStatement   = st.text
FROM sys.dm_exec_requests er
    OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
    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
WHERE st.text IS NOT NULL
    AND st.text not like '%--running sessions%'
ORDER BY 1

Complex

DECLARE   @Debug INT = 0,
  @RunsLimit INT = 200;
DECLARE
  @Session_ID INT = NULL,
  @OrderBy TINYINT = 0,
  @OpenTranMinDuration TINYINT = 2,
  @OpenTranIgnoreCustomHosts TINYINT = 1,
  @OnlyMyRequests TINYINT = NULL,
  @chain INT = 1,
  @chainTemp INT = 0,
  @OrderTemp INT = 0,
  @BlockedBy INT = NULL,
  @SPID INT = NULL,
  @Order INT,
  @Runs INT = 0;
  DECLARE
    @ActiveRequest TABLE(
      SPID SMALLINT,
      blocked_by SMALLINT,
      module NVARCHAR(500),
      sql_text NVARCHAR(MAX),
      host_name NVARCHAR(128),
      login_name NVARCHAR(128),
      running_time_s INT,
      wait_type NVARCHAR(60),
      logical_reads_gb NVARCHAR(30),
      memory_mb NVARCHAR(30),
      dop SMALLINT,
      open_transaction_count INT,
      program_name NVARCHAR(128),
      chain INT,
      chain_order INT
  );

  INSERT INTO @ActiveRequest
  (
    SPID,
    blocked_by,
    module,
    sql_text,
    host_name,
    login_name,
    running_time_s,
    wait_type,
    logical_reads_gb,
    memory_mb,
    dop,
    open_transaction_count,
    program_name,
    chain,
    chain_order
  )
  SELECT
      SPID = S.session_id,
      blocked_by = R.blocking_session_id,
      module =
          CASE
              WHEN R.session_id IS NULL THEN '!!! (INACTIVE & ACTIVE TRANSACTION) !!!'
              ELSE DB_NAME(Q.dbid) + ISNULL( '.' + OBJECT_SCHEMA_NAME(Q.objectid, Q.dbid) + '.','') + COALESCE(OBJECT_NAME(Q.objectid, Q.dbid), '?')
          END,
      sql_text =
          CASE
              WHEN R.session_id IS NULL THEN CONCAT('DBCC INPUTBUFFER(', S.session_id, ');')
              ELSE
                  SUBSTRING(Q.text,
                  CASE when R.statement_start_offset = -1 then 1 ELSE R.statement_start_offset/2 END,
                  1 + (case when R.statement_end_offset = -1 then DATALENGTH(Q.text) ELSE R.statement_end_offset END - case when R.statement_start_offset = -1 then 1 ELSE R.statement_start_offset END)/2)
          END,
      S.host_name, S.login_name,
      running_time_s = D.Duration, --CONCAT(D.Duration,'', CASE WHEN D.Duration > 3600 THEN CONCAT(' (>',D.Duration/3600,'h)') END),
      R.wait_type,
      --last_wait_type,
      logical_reads_gb = CONCAT(D.Logical_reads_gb,'', CASE WHEN D.Logical_reads_gb > 1024 THEN CONCAT(' (>',D.Logical_reads_gb/1024,'TB)') END),
      --reads_mb = R.reads  / 128,
      memory_mb =  CASE WHEN R.granted_query_memory > 0 THEN CONCAT('[',ROW_NUMBER() OVER (ORDER BY R.granted_query_memory DESC), '] ',R.granted_query_memory / 128) END,
      dop = (SELECT MAX(G.dop) FROM sys.dm_exec_query_memory_grants AS G WITH(NOLOCK) WHERE G.session_id = S.session_id),
      S.open_transaction_count,
      S.program_name,
    chain = 0,
    chain_order = 0
  FROM
      sys.dm_exec_sessions AS S WITH(NOLOCK)
    LEFT JOIN sys.dm_exec_requests AS R WITH(NOLOCK) ON S.session_id = R.session_id
    OUTER APPLY    sys.dm_exec_sql_text(R.sql_handle) AS Q
    OUTER APPLY (
        SELECT
            Duration = DATEDIFF(SECOND, ISNULL(R.start_time, S.last_request_end_time), GETDATE()),
            Logical_reads_gb = R.logical_reads/128/1024
    ) D
  WHERE
    S.session_id <> @@SPID
      AND
      (
          (
              ISNULL(R.wait_type,'') NOT IN ( 'SP_SERVER_DIAGNOSTICS_SLEEP', 'TRACEWRITE','BROKER_RECEIVE_WAITFOR','XE_LIVE_TARGET_TVF')
              AND R.command <> 'WAITFOR'
              AND S.is_user_process = 1
          )
          OR S.session_id IN (SELECT W.blocking_session_id FROM sys.dm_exec_requests AS W WITH(NOLOCK))
          OR
      (
        S.open_transaction_count > 0
        --AND S.is_user_process = 1
        AND
        (
          R.start_time < DATEADD(SECOND, -1*@OpenTranMinDuration, GETDATE())
          OR
          (
            @OpenTranIgnoreCustomHosts = 0
            --OR
            --DATEADD(MINUTE, 1, R.start_time) < GETDATE()
          )
        )
      )
      )
      AND S.session_id = ISNULL(@Session_ID, S.session_id)
    --AND S.host_name LIKE IIF(@OnlyMyRequests = 1, '%' + dbo.GetHostName(), S.host_name)
  ORDER BY
    IIF(@OrderBy = 1, R.blocking_session_id, NULL) ASC,
    IIF(@OrderBy = 2, S.session_id, NULL) ASC,
    ISNULL(R.start_time, S.last_request_end_time);
WHILE 1=1
BEGIN
  SELECT TOP 1
    @BlockedBy = blocked_by,
    @SPID = SPID
  FROM
    @ActiveRequest
  WHERE
    COALESCE(blocked_by,0) > 0
    AND chain = 0;
  SET @Order = 1;
  IF @Debug > 0
    SELECT @BlockedBy AS '@BlockedBy', @SPID AS '@SPID';
  IF @SPID IS NOT NULL
  BEGIN
    WHILE 1=1
    BEGIN
      UPDATE @ActiveRequest
      SET
        chain = @chain,
        chain_order = @Order
      WHERE
        SPID = @SPID;
      IF COALESCE(@BlockedBy,0) = 0
        BREAK;

      SET @Order = @Order +1;
      SELECT TOP 1
        @BlockedBy = blocked_by,
        @SPID = SPID,
        @chainTemp = chain,
        @OrderTemp = chain_order
      FROM
        @ActiveRequest
      WHERE
        SPID = @BlockedBy
        AND (chain = 0 OR chain <> @chain);

      IF @@ROWCOUNT = 0
        BREAK;
      --pokud jsem jiz narazil na nejakou vetev, tak zmenim chain na puvodni
      IF @chainTemp > 0
      BEGIN
        --SELECT @chainTemp '@chainTemp', @chain '@chain';
        UPDATE @ActiveRequest
        SET
          chain = @chainTemp,
          chain_order = chain_order - (@Order-@OrderTemp)
        WHERE
          chain = @chain;
        BREAK;
      END;
    END;
  END
  ELSE
  BEGIN
    PRINT 'OK';
    BREAK;
  END;
  SELECT @chain = MAX(@chain) +1 FROM @ActiveRequest;
  SET @SPID = NULL;
  SET @Runs = @Runs +1


  IF @Runs > @RunsLimit
  BEGIN
    PRINT 'CYKLES'
    BREAK;
  END;
END;
SELECT
  SPID,
  blocked_by,
  chain,
  chain_order,
  module,
  sql_text,
  wait_type,
  host_name,
  login_name,
  running_time_s,
  IIF(running_time_s > 3600, CONCAT(' (>',running_time_s/3600,'h)'), '') AS running_time_h,
  logical_reads_gb,
  memory_mb,
  dop,
  open_transaction_count,
  program_name,
  MAX(IIF(chain = 0, 0, running_time_s)) OVER (PARTITION BY chain) AS ChainMaxRunningTime
FROM
  @ActiveRequest
ORDER BY
  ChainMaxRunningTime DESC,
  chain DESC,
  chain_order DESC,
  running_time_s DESC;