Yes. This script will find the process that is burning the most IO and CPU time. The first column will contain a KILL ??? command. If you copy that value and paste it into a new query window and run it, it will kill the command with the highest CPU and IO time. Carefully! This will kill the process so your database may not like this but at least you will regain control of your system.
To effectively run this against a system that is being hogged, open a connection from a different machine (ie, not on the machine itself).
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 |
SELECT TOP (1) command = 'KILL ' + CAST(prc.spid AS VARCHAR(30)), prc.status, DatabaseName = DB_NAME(prc.dbid), Domain = LEFT(prc.loginame, PATINDEX('%\%', prc.loginame) - IIF(PATINDEX('%\%', prc.loginame) > 0, 1, 0)), LoginName = SUBSTRING( prc.loginame, PATINDEX('%\%', prc.loginame) + IIF(PATINDEX('%\%', prc.loginame) > 0, 1, 0), 999 ), prc.open_tran, LastCommand = txt.text, prc.hostname, prc.blocked, prc.cmd, CPUTime = prc.cpu, DiskIO = prc.physical_io, LastBatch = prc.last_batch, prc.program_name, prc.request_id FROM sys.sysprocesses AS prc OUTER APPLY sys.dm_exec_sql_text(prc.sql_handle) AS txt WHERE prc.nt_username > '' AND prc.status = 'runnable' ORDER BY CPUTime DESC, DiskIO DESC; |