SQL

查询慢查询的信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT DISTINCT
SUBSTRING(qt.TEXT, (er.statement_start_offset/2)+1, ((CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE er.statement_end_offset END - er.statement_start_offset)/2)+1) AS query_sql,
er.session_id AS pid,
er.status AS status,
er.command AS command,
sp.hostname AS hostname,
DB_NAME(sp.dbid) AS db_name,
sp.program_name AS program_name,
er.cpu_time AS cpu_time,
er.total_elapsed_time AS cost_time
FROM sys.sysprocesses AS sp LEFT JOIN sys.dm_exec_requests AS er ON sp.spid = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE 1 = CASE WHEN er.status IN ('RUNNABLE', 'SUSPENDED', 'RUNNING') THEN 1 WHEN er.status = 'SLEEPING' AND sp.open_tran > 0 THEN 1 ELSE 0 END
AND er.command = 'SELECT'
ORDER BY er.total_elapsed_time DESC

查询的结果如下:

结果中展示了session的pid以及正在执行的命令,消耗的cpu时间以及总的耗费时间,如果需要杀掉这个session,可以用下面的命令:

1
kill <pid>

参考

  1. KILL (Transact-SQL) | Microsoft Docs
  2. Helpful functions when you need to find out what is going on on SQL Server