SqlServer: Analizando Bloqueos, DeadLocks y Select Costosas

Uno de los problemas más complicados que podemos sufrir en nuestras aplicaciones es sin duda el tema de los bloqueos y deadLocks para buscar y encontrar como se han producido en nuestro sistema.

Para entenderlo de forma simple, Microsoft lo explica muy bien aqui: https://technet.microsoft.com/es-es/library/ms178104 y nos da informacion de como detectarlos, controlarlos y minimizarlos.

Por mi experiencia en varias aplicaciones, yo siempre utilizo las siguientes SELECTs que me van muy bien para ver lo que esta pasando en un entorno real de producción.

Información general de databases, sessions y procesos

SELECT * FROM sys.dm_exec_sessions where login_name <> 'sa';

SELECT * FROM sys.sysprocesses where blocked > 0;

SELECT name, is_read_committed_snapshot_on FROM sys.databases;

Información general de los posibles bloqueos en conexiones y peticiones

SELECT Blocker.text , Blocker.*, *
FROM sys.dm_exec_connections AS Conns
INNER JOIN sys.dm_exec_requests AS BlockedReqs
    ON Conns.session_id = BlockedReqs.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks AS w
    ON BlockedReqs.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(Conns.most_recent_sql_handle) AS Blocker

Información sobre transacciones activas SQL-server

SELECT  L.request_session_id AS SPID,
    DB_NAME(L.resource_database_id) AS DatabaseName,
    O.Name AS LockedObjectName,
    P.object_id AS LockedObjectId,
    L.resource_type AS LockedResource,
    L.request_mode AS LockType,
    ST.text AS SqlStatementText,       
    ES.login_name AS LoginName,
    ES.host_name AS HostName,
    TST.is_user_transaction as IsUserTransaction,
    AT.name as TransactionName,
    CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
    JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
    JOIN sys.objects O ON O.object_id = P.object_id
    JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
    JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
    JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
    JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
    CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

Información para análisis de Deaklock Graph XEvent

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM ( SELECT XEvent.query('.') AS XEvent
       FROM ( SELECT CAST(target_data AS XML) AS TargetData
              FROM sys.dm_xe_session_targets st
              JOIN sys.dm_xe_sessions s
              ON s.address = st.event_session_address
              WHERE s.name = 'system_health'
               AND st.target_name = 'ring_buffer'
              ) AS Data
              CROSS APPLY TargetData.nodes
                   ('RingBufferTarget/event[@name="xml_deadlock_report"]')
              AS XEventData ( XEvent )
      ) AS src;

Ultimas select ejecutadas sin necesidad de Profiler

SELECT TOP (50) * 
FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
 execution_count,s2.objectid,
 (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
 ( (CASE WHEN statement_end_offset = -1
 THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
 last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
ORDER BY last_execution_time DESC

Ultimas Select más costosas ejecutadas

SELECT TOP (50)
    q.text, s.total_elapsed_time, s.max_elapsed_time, s.min_elapsed_time,
    s.last_elapsed_time, s.execution_count, last_execution_time, *
FROM sys.dm_exec_query_stats as s
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
WHERE s.last_execution_time > DateAdd(mi , -1500 , GetDate()) -- solo las recientementes
AND text not like '%sys.%' -- eliminar consulta sys
ORDER BY s.total_elapsed_time DESC

Con esto veremos la memoria RAM que consume cada BBDD

Si lanzamos la query podemos ver el consumo concreto de una base de datos. Y si esta consulta la ejecutamos de forma regular podemos observar si el consumo fluctúa o no.

SELECT database_id, COUNT (*) * 8 / 1024 AS MB_EN_USO 
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id ORDER BY COUNT (*) * 8 / 1024 DESC

Como siempre, espero que os sea de utilidad!!

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s