sql - sp_who - ¿Cómo saber por qué se suspende el estado de un spid? ¿Qué recursos está esperando el spid?

sp_who2 status

Question

EXEC sp_who2 78 y obtengo los siguientes resultados:

¿Cómo puedo averiguar por qué se suspende su estado?

Este proceso es un INSERT pesado basado en una consulta costosa. Un gran SELECT que obtiene datos de varias tablas y escribe algunas filas de 3-4 millones en una tabla diferente.

No hay cerraduras / bloques.

El waittype que está vinculado es CXPACKET . que puedo entender porque hay 9 78 como se puede ver en la imagen de abajo.

Lo que me preocupa y lo que realmente me gustaría saber es por qué se suspende el número 1 del SPID 78.

Entiendo que cuando se suspende el estado de un SPID significa que el proceso está esperando en un recurso y se reanudará cuando reciba su recurso.

¿Cómo puedo encontrar más detalles sobre esto? que recurso ¿Por qué no está disponible?

Uso mucho el código de abajo y sus variaciones, pero ¿hay algo más que pueda hacer para averiguar por qué se suspende el SPID ?

select * 
from sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
where r.session_id = 78

Ya he usado sp_whoisactive . El resultado que obtengo para este spid78 en particular es el siguiente: (dividido en 3 imágenes para ajustar la pantalla)

Answer 1

SUSPENDIDO: significa que la solicitud actualmente no está activa porque está esperando en un recurso. El recurso puede ser una E / S para leer una página, A WAITit puede ser comunicación en la red, o está a la espera de un bloqueo o un pestillo. Se activará una vez que se complete la tarea que está esperando. Por ejemplo, si la consulta ha publicado una solicitud de E / S para leer datos de una tabla completa tblStudents, esta tarea se suspenderá hasta que se complete la E / S. Una vez que se completa la E / S (los datos para la tabla tblStudents están disponibles en la memoria), la consulta se moverá a la cola RUNNABLE.

Por lo tanto, si está en espera, verifique la columna wait_type para entender qué está esperando y solucione los problemas según el tiempo de espera.

He desarrollado el siguiente procedimiento que me ayuda con esto, incluye el WAIT_TYPE.

use master
go

CREATE PROCEDURE [dbo].[sp_radhe] 

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT 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_time
FROM 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 st
where es.is_user_process = 1 
  and es.session_id <> @@spid
ORDER BY es.session_id

end 

Esta consulta a continuación también puede mostrar información básica para ayudar cuando se suspende el spid, al mostrar qué recurso está esperando.

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 

Por favor, vea la imagen de abajo como un ejemplo:

Answer 2

Se puede resolver con formas:

1. Corrige el índice de cluster.
2. Use tablas temporales para obtener una parte de la tabla completa y trabajar con ella.

Tengo el mismo problema con una tabla con 400,000,000 de filas, y uso una tabla temporal para obtener una parte de ella y luego uso mis filtros e inners porque cambiar el índice no era una opción.

Algunos ejemplos:

--
--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.DATA
FROM 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 T
WHERE T.DATA1 < T.DATA2
--
--IMPORTANT DROP THE TABLE.
DROP TABLE #TEMPORAL

Answer 3

Utilizo sp_whoIsActive para ver este tipo de información, ya que es una herramienta gratuita ya hecha que le brinda buena información para resolver problemas en consultas lentas:

Cómo usar sp_WhoIsActive para encontrar consultas lentas de SQL Server

Con esto, puede obtener el texto de la consulta, el plan que está utilizando, el recurso que la consulta está esperando, qué lo está bloqueando, qué bloqueos está eliminando y mucho más.

Mucho más fácil que intentar rodar el tuyo.