Lesson 56. Troubleshooting Long Running Queries

If you have a process that you determine to be taking too long, the first troubleshooting step is to run DBCC OPENTRAN. This will give you information on currently running transactions. Take note of the process ID for anything sitting in a weird status or any process that you determine is running too long. You can also use sp_who2 to supplement DBCC OPENTRAN information. This will give you a list of processes and their status. Look for anything in rollback status.

Once you identify the problem process, you have to kill it using the KILL [process ID] command.

If you have a long-running process that fails, the rollback can take just as long as it took to process to that point. If something is rolling back, you’ll know it by not being able to KILL the process. To get an estimate of how long the rollback will take use KILL [process ID] WITH STATUSONLY.

Examples

Troubleshooting Step 1

Either of these commands will help you find the problem process. Take note of any process IDs.

USE AdventureWorks2016

DBCC OPENTRAN

sp_who2

Troubleshooting Step 2

Use this command to kill any unruly processes.

--8 is a random example of a process ID
KILL 8

Troubleshooting Step 3

Use this to check how long the rollback is going to take.

--8 is a random example of a process ID
KILL 8 WITH STATUSONLY