How to find out why the status of a spid is suspended? What resources the spid is waiting for?

SUSPENDED:It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAITit can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed. For example, if the query the has posted a I/O request to read data of a complete table tblStudents then this task will be suspended till the I/O is complete. Once I/O is completed (Data for table tblStudents is available in the memory), query will move into RUNNABLE queue.

So if it is waiting, check the wait_type column to understand what it is waiting for and troubleshoot based on the wait_time.

I have developed the following procedure that helps me with this, it includes the WAIT_TYPE.

use mastergoCREATE PROCEDURE [dbo].[sp_radhe] ASBEGINSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT es.session_id AS session_id,COALESCE(es.original_login_name, '') AS login_name,COALESCE(es.host_name,'') AS hostname,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch,es.status,COALESCE(er.blocking_session_id,0) AS blocked_by,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype,COALESCE(er.wait_time,0) AS waittime,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype,COALESCE(er.wait_resource,'') AS waitresource,coalesce(db_name(er.database_id),'No Info') as dbid,COALESCE(er.command,'AWAITING COMMAND') AS cmd,sql_text=st.text,transaction_isolation =    CASE es.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,COALESCE(es.cpu_time,0)     + COALESCE(er.cpu_time,0) AS cpu,COALESCE(es.reads,0)     + COALESCE(es.writes,0)     + COALESCE(er.reads,0)     + COALESCE(er.writes,0) AS physical_io,COALESCE(er.open_transaction_count,-1) AS open_tran,COALESCE(es.program_name,'') AS program_name,es.login_timeFROM sys.dm_exec_sessions es    LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id    LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id    LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid    LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id    LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS stwhere es.is_user_process = 1   and es.session_id <> @@spidORDER BY es.session_idend 

This query below also can show basic information to assist when the spid is suspended, by showing which resource the spid is waiting for.

SELECT  wt.session_id,     ot.task_state,     wt.wait_type,     wt.wait_duration_ms,     wt.blocking_session_id,     wt.resource_description,     es.[host_name],     es.[program_name] FROM  sys.dm_os_waiting_tasks  wt  INNER  JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id WHERE es.is_user_process =  1 

Please see the picture below as an example:

I use sp_whoIsActive to look at this kind of information as it is a ready made free tool that gives you good information for troubleshooting slow queries:

How to Use sp_WhoIsActive to Find Slow SQL Server Queries

With this, you can get the query text, the plan it is using, the resource the query is waiting on, what is blocking it, what locks it is taking out and a whole lot more.

Much easier than trying to roll your own.

You can solve it with to ways:

1. Fix the cluster index.
2. Use temporal tables to get a part of the all table and work with it.

I have the same problem with a table with a 400,000,000 rows, and use a temporal tables to get a part of it and then i use my filters and inners because change the index was not a option.

Some example:

----this is need be cause DECLARE @TEMPORAL are not well for a lot of data.CREATE TABLE #TEMPORAL(    ID BIGINT,    ID2 BIGINT,    DATA1 DECIMAL,    DATA2 DECIMAL);WITH TABLE1 AS(    SELECT        L.ID,        L.ID2,        L.DATA    FROM LARGEDATA L    WHERE L.ID = 1), WITH TABLE2 AS(    SELECT        L.ID,        L.ID2,        L.DATA    FROM LARGEDATA L    WHERE L.ID = 2) INSERT INTO #TEMPORAL SELECT    T1.ID,    T2.ID,    T1.DATA,    T2.DATAFROM TABLE1 T1    INNER JOIN TABLE2 T2        ON T2.ID2 = T2.ID2;----this take a lot of resources proces and time and be come a status suspend, this why i need a temporal table.SELECT    *FROM #TEMPORAL TWHERE T.DATA1 < T.DATA2----IMPORTANT DROP THE TABLE.DROP TABLE #TEMPORAL