If you’ve ever found yourself in the situation were a command executing against a small table is nowhere near instant there can be numerous reasons for this but the most common causes are locks and waits.

The first step in identifying the problem is to execute the script below in a new query window while the troublesome command is running.

/* Queries Not Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status <> 'running';

/* Queries Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status = 'running';

This script will return two lists of the currently active sessions along with the stats associated with their execution. The first list will contain all the active sessions that are not running. The second list will contain all the active sessions that are running and will likely not contain the troublesome query you’re dealing with.

Identify your session based on the SqlText field. Be sure you’ve identified the session correctly as you may decide you want to kill the process later and killing the wrong one could cause you a lot of trouble.

• status : If the status is not running look to the other fields in the returned result set to help identify the problem. If the session is in the running result set but you are unhappy with the performance it is likely the T-SQL needs to be optimized to make it run faster. This is a very broad topic and there are tons of articles and guides on the internet dealing with it.
• blocking_session_id : If another session is blocking yours from executing, e.g. it has locked a table your command needs to write to, then this field will include the Id of the session causing the table to be locked. You can use EXEC sp_who2 to assess if the underlying command/query is experiencing a problem. If you are familiar with the blocking session you may know that you are able to kill the session without incurring any negative consequences. You can use the following code snippet to kill the blocking session.

  KILL blocking_session_id /*replace by the actual Id*/

  NOTE: Before you kill anything if it’s a command that has been running for a very long time it will likely take at least the same amount of time to roll back and unlock the table. You might be better off waiting for the session to finish on its own.

• wait_type : If no blocking session is available, then the query is waiting for something, e.g. server resources etc. More details about wait types can be found HERE
• wait_time : This stat value is measured in milliseconds. Short wait times are fine, specially in PAGEIOLATCH wait types (access to physical files) but longer wait times indicate a more serious problem.
• last_wait_type : Indicates if the last wait type was different. This is quite helpful in analyzing if the query was blocked for the same reason before.