SQL Server: Finding Query using SPID

A SPID in SQL Server is a Server Process ID. Every time an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session.

Status of a Session:

RUNNING:
This status means session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches.

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 WAIT 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. Once I/O is completed (Data for table tblStudents is available in the memory), query will move into RUNNABLE queue.

RUNNABLE:
The SPID is in the runnable queue of a scheduler and waiting for a quantum to run on the scheduler. This means that requests got a worker thread assigned but they are not getting CPU time.

PENDING:
The request is waiting for a worker to pick it up. This means the request is ready to run but there are no worker threads available to execute the requests in CPU.  This doesn't mean that you have to increase 'Max. Worker threads", you have to check what the currently executing threads are doing and why they are not yielding back.

BACKGROUND:
The request is a background thread such as Resource Monitor or Deadlock Monitor.

SLEEPING:
There is no work to be done.

Finding all users’ SPIDs:

SELECT

    SPID                = er.session_id

    ,Status             = ses.status

    ,[Login]            = ses.login_name

    ,Host               = ses.host_name

    ,BlkBy              = er.blocking_session_id

    ,DBName             = DB_Name(er.database_id)

    ,CommandType        = er.command

    ,SQLStatement       = st.text

    ,ObjectName         = OBJECT_NAME(st.objectid)

    ,ElapsedMS          = er.total_elapsed_time

    ,CPUTime            = er.cpu_time

    ,IOReads            = er.logical_reads + er.reads

    ,IOWrites           = er.writes

    ,LastWaitType       = er.last_wait_type

    ,StartTime          = er.start_time

    ,Protocol           = con.net_transport

    ,ConnectionWrites   = con.num_writes

    ,ConnectionReads    = con.num_reads

    ,ClientAddress      = con.client_net_address

    ,Authentication     = con.auth_scheme

FROM sys.dm_exec_requests er

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

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

WHERE DBID NOT IN (1,2,3,4) -- Master, Tempdb, Model, MSDB

AND con.session_id > 50

AND con.session_id <> @@spid

ORDER BY con.session_id DESC;

Finding Long Running Queries:

select

    p.spid

,   right(convert(varchar,

            dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'),

            121), 12) as 'batch_duration'

,   P.program_name

,   P.hostname

,   P.loginame

from master.dbo.sysprocesses P

where P.spid > 50

and      P.status not in ('background', 'sleeping')

and      P.cmd not in ('AWAITING COMMAND'

                    ,'MIRROR HANDLER'

                    ,'LAZY WRITER'

                    ,'CHECKPOINT SLEEP'

                    ,'RA MANAGER')

order by batch_duration desc;

 

Finding SQL Text against SPID:

DBCC INPUTBUFFER (55);

OR

DECLARE @sqltext VARBINARY(128);

select @sqltext = sql_handle from sys.sysprocesses where spid=55;

select TEXT from sys.dm_exec_sql_text(@sqltext);



Credits:
http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/08/different-status-of-a-spid-in-sql-server-and-what-do-they-mean.aspx
http://sqlserverplanet.com/dba/spid-what-is-it