Crear sp_who3 para mejor búsqueda de bloqueos, un mejor sp_who3

 El siguiente sp salio del blog 

https://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3

El siguiente código genera la misma información que se encuentra en sp_who2, junto con información adicional de solución de problemas. También contiene la instrucción SQL que se está ejecutando, por lo que en lugar de tener que ejecutar un DBCC INPUTBUFFER por separado, la instrucción que se está ejecutando se muestra en los resultados.

A diferencia de sp_who2, este sp_who3 personalizado solo muestra las sesiones que tienen una solicitud en ejecución actual.

Lo que también se muestra son las lecturas y escrituras del comando actual, junto con el número de lecturas y escrituras de todo el SPID. También muestra el protocolo que se está utilizando (TCP, NamedPipes o Shared Memory).

El bloqueador de clientes potenciales a continuación se mostrará en la columna BlkBy como -1.

create PROCEDURE [dbo].[Sp_who3]

AS

BEGIN

SET TRANSACTION isolation level READ uncommitted

SELECT SPID = er.session_id,

--,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END,

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,

LastWaitType = er.last_wait_type,

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),

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,

DatetimeSnapshot = Getdate(),

plan_handle = er.plan_handle

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

OUTER 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

/*OUTER APPLY

(

SELECT

lead_blocker = 1

FROM master.dbo.sysprocesses sp

WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses)

AND sp.blocked = 0

AND sp.spid = er.session_id

) lb*/

WHERE er.sql_handle IS NOT NULL

AND er.session_id != @@SPID

ORDER BY

--CASE WHEN lead_blocker = 1 THEN -1 * 1000 ELSE -er.blocking_session_id END,

er.blocking_session_id DESC,

er.logical_reads + er.reads DESC,

er.session_id

END

Se ejecuta:

SP_WHO3