Find why your SQL Server query is stuck

PROBLEM:
What should I do when SQL Server query is stuck?

SOLUTION:
First, open SQL Server Activity Monitor, you should wait some time before data is loaded

The Activity Monitor is available in SQL Server 2008 Management Studio is a great tool which can be used to get a quick overview of SQL Server 2008 system performance. The Activity Monitor tool in the previous version of SQL Server used to display information related to processes, lock by objects and locks by process. There are many enhancements in Activity Monitor in SQL Server 2008 like a graphical display of processor time, waiting tasks, database I/O's, batch requests, processes, resource waits, data file I/O's and also information about the most expensive queries.
To view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission

One of the first lines of defense in determining why a query is stuck is to use sp_who2. It will shows all the sessions that are currently established in the database. These are denoted as SPID’s, or server process Id’s.

sp_who2

The first 50 results are system SPIDs. Generally these do not effect slowdowns of the system. These system events include the Checkpoint writer, Log writers and task schedulers. User processes are SPID numbers 50 and over. In diagnosing slowdowns it is these SPIDs that are the potential resource hogs.

There are four main things to look for when when diagnosing slowdowns:

• Blocking
• High CPU usage
• High IO usage
• Multiple entries for the same SPID (representing parallelism)

When a number is shown in the column named BlkBy, this represents the SPID that is currently stopping the SPID in the row shown. Sometimes many rows will show SPID numbers in the BlkBy column. This is because there is a chain of blockers. The way this occurs usually starts with one “lead” blocker blocking another process. In turn, the process that is being blocked, blocks others. This occurs down a chain. It can be a messy situation. In order to rectify, you may have to kill the lead blocker. If it happens often, you will want to research why this particular process is blocking. So, before you kill any process, find out what statement it is running first. To do this, execute DBCC INPUTBUFFER

DBCC INPUTBUFFER(500) -- 500 represent the SPID you see in the result of sp_who2

Once you find the blocker, you may need to kill it. To do so use the kill command (replace the number below with the blocking SPID)
IMPORTANT: before the kill, keep a copy of the SQL statement being run for later investigation

KILL 500

sp_who2 does provide limited information regarding slowdowns.

For more information use the following script that shows the SQL statement being run, only sessions that have a current executing request, reads and writes for the current command, along with the number of reads and writes for the entire SPID and the protocol being used (TCP, NamedPipes, or Shared Memory)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT SPID = er.session_id ,
       BlkBy = er.blocking_session_id ,
       ElapsedMS = er.total_elapsed_time ,
       CPU = er.cpu_time ,
       IOReads = er.logical_reads + er.reads ,
       IOWrites = er.writes ,
       Executions = ec.execution_count ,
       CommandType = er.command ,
       ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,
       SQLStatement = SUBSTRING ( qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2 ) ,
       Status = ses.status ,
       [Login] = ses.login_name ,
       HOST = ses.host_name ,
       DBName = DB_Name(er.database_id) ,
       LastWaitType = er.last_wait_type ,
       StartTime = er.start_time ,
       Protocol = con.net_transport ,
       transaction_isolation = CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END ,
        ConnectionWrites = con.num_writes ,
        ConnectionReads = con.num_reads ,
        ClientAddress = con.client_net_address ,
        Authentication = con.auth_scheme
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt OUTER APPLY
( SELECT execution_count = MAX(cp.usecounts)
 FROM sys.dm_exec_cached_plans cp
 WHERE cp.plan_handle = er.plan_handle) ec
ORDER BY er.blocking_session_id DESC,
         er.logical_reads + er.reads DESC,
         er.session_id

The following script will show the blocking processes (lead blocker)

SELECT spid ,
       sp.status ,
       loginame = SUBSTRING(loginame, 1, 12) ,
       hostname = SUBSTRING(hostname, 1, 12) ,
       blk = CONVERT(char(3), blocked) ,
       open_tran ,
       dbname = SUBSTRING(DB_NAME(sp.dbid),1,10) ,
       cmd ,
       waittype ,
       waittime ,
       last_batch ,
       SQLStatement = SUBSTRING ( qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2 )
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er ON er.session_id = sp.spid OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN
    (SELECT blocked
     FROM master.dbo.sysprocesses)
  AND blocked = 0

The following script will find most expensive queries (remark the needed ORDER BY)

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1),
              qs.execution_count,
              qs.total_logical_reads,
              qs.last_logical_reads,
              qs.total_logical_writes,
              qs.last_logical_writes,
              qs.total_worker_time,
              qs.last_worker_time,
              qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
              qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
              qs.last_execution_time,
              qp.query_plan
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

The following script will find longest running query

SELECT DISTINCT TOP 10 t.TEXT QueryName,
   s.execution_count AS ExecutionCount,
   s.max_elapsed_time AS MaxElapsedTime,
   ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
   s.creation_time AS LogCreatedOn,
   ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
ORDER BY s.max_elapsed_time DESC

LETS CHECK, do the following steps and run the above scripts when needed

-- open new query window (SESSION A) and run the following 3 lines
USE [AdventureWorks]
BEGIN TRANSACTION
UPDATE Sales.SalesOrderDetail
SET [UnitPrice] = 1 -- this update takes ~30sec

-- while the above update in progress, open a new query window (SESSION B) and run sp_who2
-- open another query window (SESSION C) and run the following line, it will be blocked by SESSION A because the transaction there is not committed yet
UPDATE Sales.SalesOrderDetail
SET [UnitPrice] = 2

-- while the above update in progress, go to SESSION B query window and run again sp_who2, you will see the blocking
-- return to SESSION A query window and rollback the transaction. This will free the blocking in SESSION C (after the update finishes, takes ~30sec)
ROLLBACK

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!