SQL Server 2008 R2: Desbloquear los bloqueos
A veces, resolver problemas de bloqueo con SQL Server 2008 R2 es problemática, otras veces puede ser sorprendentemente sencilla.
William Stanek
Solución de problemas de la base de datos no es para el valientes de corazón, sobre todo cuando se trata de problemas de bloqueos de la solución de problemas. A veces el elefante en la sala es realmente un vulvovaginitis, un problema que parece ser algo más que puede realizar la solución de problemas de todo cuanto más difícil y frustrante. En otras ocasiones, la respuesta que está buscando es fijamente derecho en la imagen. Esto es el caso de SQL Server 2008 R2, que tiene un problema conocido de bloqueo se puede resolver si basta con aplicar el service pack más reciente o actualiza.
A veces, las operaciones que realiza que las bases de datos que tienen problemas de bloqueo o bloqueo. Bloqueos son inevitables. Se producen en cada sistema de administración de bases de datos relacionales (RDBMS) y SQL Server 2008 R2 no es una excepción.
El bloqueo se produce cuando una sesión de base de datos de inicio de sesión único, o el proceso de servidor ID (SPID), se mantiene un bloqueo en un recurso específico y un segundo SPID intenta adquirir un bloqueo en conflicto en ese mismo recurso. Normalmente, los bloqueos de recursos no duran tanto. Cuando el primer usuario libera el bloqueo, el segundo servidor SPID puede bloquear el recurso para continuar procesando una consulta.
El Nitty mensajes de bloqueo
SQL Server 2008 R2 se utiliza un enfoque detallado para el bloqueo. Selecciona el nivel de bloqueo apropiado en función del número de registros afectados y otras actividades continuas.
De forma predeterminada, se concentraron bloqueos de menores bloqueos de fila y de página, para mayores bloqueos de tabla según sea necesario para mejorar el rendimiento. Aunque el escalado por lo general, es bueno, también puede causar problemas, como cuando un identificador de conjunto de servicios (SSID) bloquea toda la tabla y se impide que otro SSID trabajar con esa tabla.
Puede configurar las opciones de bloqueo de nivel de fila y de página. Éstas están habilitadas de forma predeterminada en los índices. SQL Server 2008 R2 también es compatible con la partición de tablas. Debido a que las particiones de dividen los datos en objetos independientes, las tablas de partición puede mejorar el rendimiento global.
Los tipos de consultas también pueden determinar la duración de los bloqueos. Cuando no se ejecuta una consulta en una transacción y no se utilizan las sugerencias de bloqueo, para las instrucciones SELECT se utilizan bloqueos sólo mientras se está leyendo el recurso, no para la duración de la consulta. Para las instrucciones INSERT, UPDATE y DELETE se utilizan bloqueos para la duración de la consulta. Esto ayuda a garantizar la coherencia de datos y permite deshacer la consulta, si es necesario que SQL Server.
Cuando se ejecuta una consulta en una transacción, tres factores determinan la duración de un bloqueo:
- El tipo de consulta
- El nivel de aislamiento de la transacción
- La presencia o ausencia de sugerencias de bloqueo
Bloqueos son las características normales de un RDBMS.Sin embargo, puede degradar el rendimiento cuando un SPID mantiene los bloqueos en los recursos durante períodos prolongados de tiempo. Rendimiento también se ve afectado cuando un SPID mantiene los bloqueos de recursos y se produce un error liberarlos.
El problema de bloqueo anterior puede resolver por sí mismo con el tiempo, cuando el SPID finalmente libera los bloqueos. La degradación de rendimiento resultante sigue siendo muy real. El segundo tipo de problema de bloqueo puede causar una degradación del rendimiento, pero es, por suerte, relativamente fácil de encontrar si supervisar SQL Server para los problemas de bloqueos.
Bloque de desactivación de almacenamiento
El Monitor de actividad de SQL Server puede ayudarle a identificar problemas de bloqueos. Estrechamente observar cómo el tiempo de espera, Wait esperar recursos y del tipo bloqueados por valores de los procesos enumerados.
La mayor parte de la información de proceso del Monitor de actividad proviene de las siguientes vistas de administración dinámica:
- sys.dm_os_tasks
- sys.dm_os_waiting_tasks
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.dm_resource_governor_workload_group
Para obtener una imagen más clara de los bloqueos con la vista sys.dm_tran_locks. Devuelve información acerca de las solicitudes de bloqueo activo que tienen o están esperando para ser concedidas. El sys.dm_exec_connections, sys.dm_exec_sessions y sys.dm_exec_requests proporcionan información acerca de las conexiones activas, sesiones y las solicitudes, respectivamente.
Centrarse en sys.dm_exec_requests (que se describe en detalle en la biblioteca de MSDN ). Una solicitud con un estado “ en inactividad ” ha finalizado su ejecución y es probable que está esperando un comando de la aplicación. Una solicitud con un estado “ ejecución ” o “ ejecutable ” está procesando una consulta. Una solicitud con un estado “ suspendido ” espera un bloqueo, cierre o cualquier otro suceso.
La columna wait_type, como el nombre implica, devuelve el tipo de espera. Si el valor es mayor que cero, se espera el SPID. Para obtener más información, busque las columnas tiempoEspera y wait_resource. Si la solicitud se bloquea, tiempoEspera muestra la duración en milisegundos. El wait_resource muestra los recursos para el que se espera que el SPID. Tenga en cuenta que también que blocking_session_id muestra el identificador de sesión en el que está bloqueando la solicitud, o un valor negativo con información sobre el propietario del recurso de bloqueo.
Las aplicaciones de usuario mal escritas pueden causar una gran variedad de problemas de bloqueo. Si se produce un error en una aplicación administrar correctamente los niveles de la transacción anidada, se puede tener una situación de bloqueo donde wait_type de la solicitud es cero, su estado está en suspensión, y su open_transaction_count es mayor que cero.
Es muy probable que esta aplicación tiene un tiempo de espera de consulta o emite una cancelación sin emitir también el número requerido de instrucciones ROLLBACK y COMMIT. Esto mantiene los bloqueos activos y bloquea los otros SPID. SQL Server no corregir el problema. La aplicación debe escribirse para controlar correctamente las transacciones anidadas.
Si se produce un error en una aplicación recuperar por completo de todas las filas de resultados, se puede tener una situación de bloqueo que una solicitud wait_type es cero, el estado es se puede ejecutar, y el open_transaction_count es mayor que cero. Probablemente, esto significa que la aplicación no recupera todas las filas de resultados y bloqueos de la izquierda en las tablas, bloqueando otros SPID. Si es posible, debe configurar la aplicación para asegurarse de que recupera todos los resultados.
Cuando el problema está en el servidor, se pueden resolver los problemas de bloqueo al garantizar que SQL Server crea los índices adecuados en las bases de datos. También puede realizar el ajuste de las consultas según sea necesario. Los índices pueden reducir el número de registros que se tiene acceso una consulta. Para ello, lo que reduce el número de búsquedas en que el motor de base de datos debe realizar. Para ayudarle a encontrar las tablas y columnas que se necesitan los índices, utilice las siguientes vistas de administración dinámica:
- sys.dm_db_missing_index_groups
- sys.dm_db_missing_index_details
- sys.dm_db_missing_index_group_stats
Es importante tener en cuenta que los índices pueden ralentizar la modificación de datos (tal y como se describe en “ del trabajo de operaciones de índice en pantalla de How ”). Por lo tanto, al crear índices, asegúrese de que se utilizan con la vista de index_usage_stats sys.dm_db_.
La indización no hace necesariamente que a largo plazo de bloqueos de tabla. Indización en línea, sólo un bloqueo de intención compartido (IS) se mantiene en la tabla de origen durante la fase principal de la operación de índice. De este modo, las consultas o actualizaciones de con. Por lo general, se mantiene un bloqueo compartido en el objeto de origen durante un rato al inicio (y en algunos casos, al final) de la operación de índice.
Cuando el bloqueo se debe a una consulta normalmente con un tiempo de ejecución larga, puede buscar formas de optimizar la consulta. Una de las mejores maneras de hacerlo es minimizar la cantidad de recursos en una transacción de bloqueo. Para ello, lo que las transacciones lo más pequeño posible, quite el procesamiento no crítico y reducir a lo que realmente necesita. Quizás desee considerar la posibilidad de dividir una transacción en varias consultas que afectan a la menos filas, pero sólo si este no afecta a los resultados deseados. Diseñar transacciones para utilizar el nivel de aislamiento de la transacción correcta y considere si se pueden utilizar las sugerencias de bloqueo para reducir el bloqueo al obtener los resultados correctos de todavía.
Tratamiento de interbloqueo
Un tipo especial de problema de bloqueo llamada a un interbloqueo se produce cuando dos o más de los SPID tienen bloqueos en objetos independientes y cada uno de ellos desea un bloqueo en el objeto del otro. En este caso, cada SPID espera a que el otro libere el bloqueo, pero no suceda. Puede minimizar los interbloqueos, pero no evitar por completo. Afortunadamente, el Administrador de bloqueos de SQL Server comprueba automáticamente los interbloqueos. Cuando el Administrador de bloqueos detecta un interbloqueo, lo hace tres cosas:
- Selecciona uno de los SPID para terminar
- Genera un mensaje de error 1205 que se envía al cliente original
- Elimina el SPID para liberar el recurso bloqueado y permitir que los otros SPID continuar
La vista Monitor de actividad y sys.dm_tran_locks también puede ayudarle a identificar los interbloqueos. En el Monitor de actividad, observe el tiempo de espera (tiempo de espera transcurrido mostrado en milisegundos), tipo de espera (se muestra si se espera de procesamiento o no) y la espera de recursos (si se espera, muestra el recurso para que el procesamiento se encuentra en espera).
Las aplicaciones de usuario se deben diseñar para controlar los interbloqueos. Una aplicación bien diseñada de aplicaciones para usuario se captura el error 1205, vuelva a conectarse a SQL Server y, a continuación, vuelva a enviarlo la transacción. También comenzará a inspeccionar una aplicación cliente bien diseñada para los interbloqueos distribuidos que pueden producirse cuando un lado de la solicitud de recursos en el nivel de cliente y el otro extremo es el nivel de servidor.
La aplicación, a continuación, puede resolver los interbloqueos distribuidos con tiempos de espera de consulta o enlazar conexiones. En este caso, interrumpirá un interbloqueo distribuido cuando se produce un tiempo de espera de consulta. Un cliente también puede tener varias conexiones que se enlazan a un espacio de transacción para que las conexiones no bloquean entre sí.
Eso es todo en pocas palabras. Que es el modo de bloqueo funciona y cómo encontrar y resolver problemas de bloqueo. La mayoría de los casos, SQL Server puede resolver muchos problemas de bloqueo, incluidos los interbloqueos. También puede eliminar o reducir los problemas de bloqueo con un buen diseño. Empezar con buen diseñadas de consultas y las aplicaciones de usuario más experimentadas. A continuación, extender esto para los servicios de fondo al ajuste.
.jpg)
**William r Stanek**es un experto en tecnología líder, un instructor con instrucciones y el autor de la premiada de más de 100 libros. Siga Stanek Twitter en https://twitter.com/williamstanek.