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
| DECLARE @PROCESSES TABLE
(
SPID SMALLINT,
LOGINNAME SYSNAME,
BLOCKED SMALLINT,
CPU INT,
PHYSICAL_IO INT,
RQ_TIME DATETIME,
LAUNCHED VARCHAR(16),
HOSTNAME VARCHAR(64),
STATUS SYSNAME,
COMMAND VARCHAR(64),
DB VARCHAR(32),
PROGRAM_NAME VARCHAR(128),
SQL VARCHAR(8000)
);
INSERT @PROCESSES
SELECT
PROCESS.SPID,
CONVERT(SYSNAME, RTRIM(PROCESS.LOGINAME)) LOGINNAME,
PROCESS.BLOCKED,
PROCESS.CPU,
PROCESS.PHYSICAL_IO,
SESSION.last_request_start_time,
CASE(CONVERT(VARCHAR, DATEDIFF(second, SESSION.last_request_start_time, GETDATE())/3600) + 'h ')
WHEN '0h' THEN ''
ELSE CONVERT(VARCHAR, DATEDIFF(second , SESSION.last_request_start_time, GETDATE())/3600) + 'h '
END +
CASE(CONVERT(VARCHAR, DATEDIFF(second, SESSION.last_request_start_time, GETDATE())/60%60) + 'min ')
WHEN '0min' THEN ''
ELSE CONVERT(VARCHAR, DATEDIFF(second, SESSION.last_request_start_time, GETDATE())/60%60) + 'min '
END +
CONVERT(VARCHAR, DATEDIFF(second, SESSION.last_request_start_time, GETDATE())%60) + 's' AS LAUNCHED,
PROCESS.HOSTNAME,
UPPER(PROCESS.STATUS) Status,
UPPER(PROCESS.CMD) Command,
DB_NAME(PROCESS.DBID) DB,
CASE SUBSTRING(PROCESS.PROGRAM_NAME, 1, 38)
WHEN 'Microsoft SQL Server Management Studio' THEN 'SSMS Query'
ELSE PROCESS.PROGRAM_NAME
END PROGRAM_NAME,
CONVERT(VARCHAR(8000), ST.text) SQL
FROM sys.sysprocesses (nolock) PROCESS
JOIN sys.dm_exec_sessions SESSION (nolock) ON PROCESS.spid = SESSION.session_id
CROSS APPLY sys.dm_exec_sql_text(PROCESS.sql_handle) ST
WHERE SPID > 50
AND SPID != @@SPID
AND CMD != 'AWAITING COMMAND'
AND PROCESS.PROGRAM_NAME NOT LIKE 'DatabaseMail90%'
ORDER BY PROCESS.SPID
SELECT SPID, COUNT(*) NB_THREADS, LOGINNAME, BLOCKED, SUM(CPU) CPU, SUM(PHYSICAL_IO) PHYSICAL_IO, RQ_TIME, LAUNCHED, HOSTNAME, STATUS, COMMAND, DB, PROGRAM_NAME, SQL
FROM @PROCESSES
GROUP BY SPID, LOGINNAME, BLOCKED, RQ_TIME, LAUNCHED, HOSTNAME, STATUS, COMMAND, DB, PROGRAM_NAME, SQL
ORDER BY SPID, LOGINNAME DESC |
Partager