运维文档

# 实时查询数据库活动会话状态

# MySQL

show full processlist;

select * from information_schema.processlist where info is not null order by time desc;

# SQl Server

WITH t_proc AS (SELECT spid, count(*) proc_count, max(loginame) loginame FROM sys.sysprocesses GROUP BY spid),
exec_sql_tab AS (SELECT * FROM sys.dm_exec_requests),
h_lock AS (
  SELECT
    a.blocking_session_id AS blocked
  FROM
    exec_sql_tab a
  WHERE
    NOT EXISTS (SELECT 1 FROM exec_sql_tab WHERE blocking_session_id > 0 AND session_id = a.blocking_session_id)
    AND blocking_session_id > 0
  GROUP BY
    a.blocking_session_id
) SELECT
  (CASE WHEN h_lock.blocked IS NOT NULL THEN 1 ELSE 0 END) AS 'headlockflag',
  '"' + (
    CASE
      WHEN substring(dest.TEXT, 1, 16) = 'FETCH API_CURSOR' THEN
        (SELECT t.TEXT FROM sys.dm_exec_cursors (der.session_id) c CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t)
      ELSE
        dest.TEXT
    END
  ) + '"' AS 'SQL语句',
  (CASE WHEN substring(dest.TEXT, 1, 16) = 'FETCH API_CURSOR' THEN 1 ELSE 0 END) AS 'fetch_cursor状态',
  (
    CASE
      WHEN charindex ('<ParameterList>', dest_plan.query_plan) > 0 THEN
        '"' + substring(
          dest_plan.query_plan,
          charindex ('<ParameterList>', dest_plan.query_plan),
          charindex ('</ParameterList>', dest_plan.query_plan) - charindex ('<ParameterList>', dest_plan.query_plan) + 16
        ) + '"'
      ELSE
        ''
    END
  ) AS 'SQL变量绑定参数',
  der.cpu_time AS '运行时间',
  der.session_id AS '会话ID',
  t_proc.proc_count AS '运行线程数',
  der.command AS '命令',
  der.blocking_session_id AS '阻塞其会话的会话ID',
  DB_NAME (der.database_id) AS '数据库名称',
  der.request_id AS '请求ID',
  der.start_time AS '开始时间',
  der.STATUS AS '状态',
  der.READS AS '物理读次数',
  der.writes AS '写次数',
  der.logical_reads AS '逻辑读次数',
  der.row_count AS '返回结果行数',
  der.wait_type AS '等待资源类型',
  der.wait_time AS '等待时间',
  der.total_elapsed_time,
  der.wait_resource AS '等待的资源',
  conn.client_net_address AS '客户端地址',
  tmpdb.user_objects_alloc_page_count AS 'tempdb使用页',
  t_proc.loginame AS '登录用户名',
  der.query_hash AS 'SQL的HASH',
  der.transaction_id,
  der.open_transaction_count,
  der.open_resultset_count,
  CONVERT(CHAR(24), getdate (), 121) AS '采集时刻'
FROM
  exec_sql_tab der
  LEFT JOIN sys.dm_exec_connections conn ON (conn.session_id = der.session_id)
  LEFT JOIN h_lock ON (h_lock.blocked = der.session_id)
  LEFT JOIN t_proc ON (t_proc.spid = der.session_id)
  LEFT JOIN sys.dm_db_session_space_usage tmpdb ON (tmpdb.session_id = der.session_id) CROSS APPLY sys.dm_exec_sql_text (der.sql_handle) AS dest CROSS APPLY sys.dm_exec_text_query_plan (der.plan_handle, DEFAULT, DEFAULT) AS dest_plan
WHERE
  der.session_id <> @@spid
ORDER BY
  'headlockflag' DESC,
  blocking_session_id,
  cpu_time DESC

# Oracle

# Postgres

# DM

# KingBase
编撰人：wangyxyf

快速跳转

← 如何检查升级是否正常 →