Blog gratis
Reportar
Editar
¡Crea tu blog!
Compartir
¡Sorpréndeme!
img
img

 

 

SQL Server
Blog. (Apuntes y Recopilaciones) por Norman M. Pardell

img
img
08 de Enero, 2012 · Detectar-bloqueos-SQL-Server

Detectar bloqueos. SQL Server V.2005 y superiores

 La existencia de bloqueos es algo normal y necesaria en las bases de datos relacionales, esto garantiza la integridad de los datos, no permitiendo actualizaciones concurrentes sobre los mismos datos o la lectura de datos que están siendo actualizados en ese momento por otras transacciones...

 SQL Server administra los bloqueo de forma dinámica, sin embargo, sigue siendo importante entender como bloquean las consulas transact-SQL en SQL Server...

 Conceptos de tipos de bloqueos sobre los recuros de SQLServer:

 Shared lock. (S).- (bloqueo compartido) se eproducen durante operaciones de sólo lectura, consultas que no modifican los datos. este tipo de bloqueos permite que los datos puedan ser leídos por otras transacciones, ya que no se actualizan o modifican por otros procesos mientras accede a ellos.

 Inater lock.- ESte tipo de bloqueo se crea cuano existe la intención crear una cola de bloqueo, cuando hay mas de una transaccion que quiere acceder al mismo dato, designa el orden de las conexiones y su derecho asociado a la actualización o leer los recursos. Dicho de otra forma "Inater lock" nos muestra la intención de adquirir un bloqueo sobre un recurso específico en un futuro. Los tipos de bloqueo de intención son: intención compartido (IS), intención exclusivo (IX) y compartido con intención exclusivo (SIX).

 Update lock. (U).- (Bloqueos de actualización) Este bloqueo se adquiere antes de que sea modificado el datos, y justo antes de que se produzca la modificación del dato el bloqueo se aumenta a un bloqueo exclusivo. Si la modificación no se produce, el bloqueo queda degradado a un bloqueo compartido. Este tipo de bloqueo evita bloqueos, por ejemplo si existen conexiones con un bloqueo compartido en un recurso y otra conexión trata de crear un bloqueo exclusivo, no puede porque están esperando a que la otra transacción libere el bloqueo compartido.
 
 Exclusive lock. (X).- (Bloqueo exclusivo) Este tipo de bloqueo prohíbe cualquier tipo de de acceso (lectura o escritura). Que se emite durante las instrucciones INSERT, UPDATE, o DELETE.

 Schema modification. (Sch-M).- Este tipo de bloqueo que se produce cuando una instrucción DDL se ejecuta.

 Schema stability. (Sch-S).- (Estabilidad del esquema) Este tipo de bloqueo se produce cuando una consulta se está compilando. Mantiene operaciones DDL.

 Bulk update. (BU).- Actualización masiva. Este tipo de bloqueo se  da durante una operación de copia masiva. Hace que la concurrencia a latabla se reduzca por culpa de la operación de copia masiva. Se utiliza cuando se copian datos de forma masiva en una tabla y se especifica la sugerencia TABLOCK.

 key-rage locks.- es un bloqueo que se da en un intervalo para proteger un rango de filas (en base a la clave del índice). ejemplo, la protección de filas en una instrucción UPDATE, con un rango de fechas de 1/1/2005 al 12/31/2005.


Los bloqueos sobre los recursos varían de granularidad, por ejemplo, podemos encontrar bloqueos de una unica fila o pagina (granularidad pequeña), en una tabla, o incluso en una base de datos (granularidad grade). Los bloqueos de granularidad pequeña permiten muchos mas accesos que los de mayor granularidad, en la base de datos. Pero esto no quita que miles de bloqueos de filas individuales distintas puedan llega a afectar al rendimiento de SQL Server


Os muestro un ejemplo de como ver la actividad de bloqueos en la base de datos con la DMV: sys.dm_tran_locks.
 se ejecuta:


SELECT request_session_id sessionid,
 resource_type type,
 resource_database_id dbid,
 OBJECT_NAME(resource_associated_entity_id, resource_database_id) objectname,
 request_mode rmode,
 request_status rstatus
FROM sys.dm_tran_locks
WHERE resource_type IN ('DATABASE', 'OBJECT')


En la aterior consulta, como pudes ver solo muestro los bloqueos de tipo DATABASE (un bloqueo a la bd entera) y de tipo OBJET ( a objetos de la base de datos, por ejemplo a una vista, un procedimiento almacenado, una funcion...). Si no pusieramos esta condición por el campo resource_type podemos encontrar si existen otros bloqueos de tipo: DATABASE, OBJECT, FILE, PAGE, KEY, RID, EXTENT, METADATA, APPLICATION, ALLOCATION_UNIT, o de tipo HOBT.


DATABASE, bloqueo a la bd entera
OBJECT, cloqueos a objetos de la base de datos, por ejemplo a una vista, un procedimiento almacenado, una funcion,etc. incluidos todos los datos e índices. El objeto puede ser cualquier elemento que tenga una entrada en sys.all_objects.
FILE, bloqueo en una fila de una base de datos.
PAGE, Bloqueo en una pagina de datos 8KB
KEY, bloqueo de una filade o un indice, ayudando a prevenir las lecturas fantasmas...
RID, Identifica un registro de una tabla donde se produce el bloqueo
EXTENT, Unidad de asignación de paginas de datos de 8 KB, bloqueo en un grupo contiguo de 8 páginas. Bloqueo de mas de una en las páginas de índice.
METADATA, bloqueo en la metadata tambien llamado información del catálogo.
APPLICATION, Un conjunto de páginas relacionadas o agrupadas por tipo de datos, por ejemplo, las filas de datos, las filas de índice,
 o filas de datos de objetos grande...
ALLOCATION_UNIT, Recursos necesarios que bloquea una aplicacion.
HOBT, Bloqueo en un montón de páginas de datos o en la estructura árbol b de un índice. bloqueo en una tabla sin un índice agrupado.
TABLE. Un recurso que bloquea toda la tabla, los datos e índices.


De una forma rapida y sencilla, si lo que quieres es ver qué sesiones son las que bloquean y a qué sesiones estan bloqueando, puedes ejecutar:

SELECT blocking_session_id, wait_duration_ms, session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL


donde blocking_session_id es la sesion que bloquea a session_id

como ejemplo, piensa que blocking_session_id = 38

Para saber que esta haciendo (que T-SQL esta ejecutando) blocking_session_id, ejecuta:


SELECT t.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t
WHERE c.session_id = 38


Herramientas de información de interbloqueos:

Para ver la información de interbloqueos, Motor de base de datos proporciona herramientas de supervisión en la forma de dos marcas de traza, y el evento deadlock graph del SQL Server Profiler.

Marcas de traza 1204 y 1222
Cuando se produce el interbloqueo, las marcas de traza 1204 y 1222 devuelven la información que se ha capturado en el registro de errores de SQL Server 2005. La marca de traza 1204 informa sobre el interbloqueo con un formato que especifica cada nodo implicado en el mismo. La marca de traza 1222 aplica formato a la información de interbloqueo, primero por procesos y luego por recursos. Es posible habilitar ambas marcas de traza para obtener dos representaciones del mismo evento de interbloqueo.

Además de definir las propiedades de las marcas de traza 1204 y 1222, en la siguiente tabla se muestran las similitudes y las diferencias.

por ejemplo,
para activar la traza:

DBCC TRACEON (1222, -1)


Para ver las trazas que tienes activadas ejecuta:

DBCC TRACESTATUS


Y para desactivar la traza:

DBCC TRACEOFF (1222, -1)

Para mas información ir a: http://msdn.microsoft.com/es-es/library/ms178104.aspx


Analizar interbloqueos con el Analizador de SQL:

Use el SQL Server Profiler para identificar la causa de un interbloqueo. Un interbloqueo se produce cuando hay una dependencia cíclica entre dos o más subprocesos o procesos para algún conjunto de recursos en SQL Server. El SQL Server Profiler le permite crear una traza que registra, reproduce y muestra eventos de interbloqueo para su análisis.

Para realizar un seguimiento de los eventos de interbloqueo, agregue la clase de evento Deadlock graph a una traza. Esta clase de evento rellena la columna de datos TextData de la traza con datos XML acerca de los procesos y objetos implicados en el interbloqueo. El SQL Server Profiler puede extraer el documento XML a un archivo XML de interbloqueo (.xdl) que puede ver después en SQL Server Management Studio. Puede configurar el SQL Server Profiler para extraer eventos Deadlock graph en un solo archivo que contenga todos los eventos Deadlock graph o en archivos independientes. Esta extracción se puede hacer de las siguientes formas:

Al configurar la traza, mediante la ficha Configuración de extracción de eventos. Tenga en cuenta que esta ficha no aparece hasta que haya seleccionado el evento Deadlock graph en la ficha Selección de eventos.

Mediante la opción Extraer eventos de SQL Server del menú Archivo.

También puede extraer y guardar eventos individuales haciendo clic con el botón secundario en un evento concreto y eligiendo Extraer datos de evento.

Para mas información ir a: http://msdn.microsoft.com/es-es/library/ms188246.aspx

 

Finalmente; Se recomienda la utilización del procedimiento "sp_WhoIsActive" o alguno similar. En este link podrás encontrar un link al código, además del código del procedimiento, una guía de cómo detectar el proceso que bloquea todos los demás.

http://sqlblog.com/blogs/adam_machanic/archive/2011/04/23/leader-of-the-block-a-month-of-activity-monitoring-part-23-of-30.aspx

 

A puntes y recopilaciones de Norman M. Pardell

 

Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.

publicado por normanmpardell a las 19:41 · Sin comentarios  ·  Recomendar
Comentarios (0) ·  Enviar comentario
Esta entrada no admite comentarios.
img
.Sobre mí
FOTO

Norman M. Pardell

MCITP: Database Administrator & Database Developer, SQL Server 2008. MCC Award Certificate. Consultor Senior de bases de datos en Capgemini España, S.L. Asesoramiento en implementación, desarrollo y gestión de bases de datos en grandes compañías. Actualmente, asignado a proyecto en compañía líder en el sector energético global. Más de 10 años trabajando con SQL Server (y otros gestores de BBDD)

» Ver perfil

img
.Secciones
» Inicio
img
.Enlaces
» Microsoft MSDN Foros
» Windows Server 2012
img
.Más leídos
» Asignar la cantidad correcta de Memoria para SQL Server
» Base de Datos Sospechosa (Suspect)
» Como modificar la Intercalación (Collation) en SQL Server
» Como renombrar una instancia de SQL Server. sp_dropserver. sp_addserver
» Detectar bloqueos. SQL Server V.2005 y superiores
» Funciones SQL Server. Funciones escalares y funciones con valores de tabla.
» Integridad y corrupción en las bases de datos: DBCC CHECKDB
» Log de transacciones ( .ldf ). SQL Server.
» Migrando SQL Server 2005, 2008, 2008 R2 a SQL Server 2012
» Transacciones activas. SQL server 2008
img
.Nube de tags [?]
                                                           
img img
FULLServices Network | Crear blog | Privacidad