SQL Server

Minimice el bloqueo en SQL Server

Cherié Warren

Resumen:

• Por qué se produce la extensión de bloqueo
• Cómo evitar el bloqueo innecesario
• Optimización de las consultas
• Supervisión del impacto del bloqueo sobre el rendimiento

Aunque el bloqueo es necesario para admitir actividades simultáneas de lectura y escritura en una base de datos, también puede afectar de forma negativa al rendimiento del sistema, a veces de forma sutil. En este artículo, voy a describir cómo optimizar las bases de datos de SQL Server 2005 y SQL Server 2008 para minimizar

el bloqueo y cómo supervisar el sistema para entender mejor cómo repercute el bloqueo sobre el rendimiento.

Bloqueo y extensión

SQL Server® elige el grano de bloqueo más apropiado en función del número de registros afectados y de la actividad simultánea que tiene lugar en el sistema. De forma predeterminada, SQL Server selecciona el grano de bloqueo más pequeño posible, eligiendo sólo granos de bloqueo más gruesos si se puede usar la memoria del sistema de manera más eficaz. SQL Server extenderá un bloqueo si la extensión beneficia al rendimiento general del sistema. Como muestra la Figura 1, las extensiones se producen cuando el número de bloqueos en una detección concreta excede de 5.000 o cuando la memoria destinada por el sistema a los bloqueos supera la capacidad disponible:

Figura 1** Condiciones que causan la extensión de bloqueo **(Hacer clic en la imagen para ampliarla)

• 24 por ciento de la memoria no AWE usada por el motor de base de datos si la configuración de bloqueos es 0
• 40 por ciento de la memoria no AWE usada por el motor de base de datos si la configuración de bloqueos es distinta de 0

Si se produce una extensión, siempre es a un bloqueo de tabla.

Cómo evitar el bloqueo innecesario

El bloqueo puede ocurrir en cualquier grano de bloqueo, pero la exposición a bloqueos aumenta si se producen extensiones. La extensión de bloqueo puede indicar la ineficacia del diseño, el código o la configuración de su aplicación.

Ceñirse a los aspectos fundamentales del diseño de base de datos (como usar un esquema normalizado con claves estrechas y evitar las operaciones de datos masivas en sistemas de transacciones) es importante para evitar bloqueos. Si no se siguen estos principios (como apartar el sistema de informes del sistema de transacciones o procesar fuentes de datos fuera del horario de oficina), será difícil ajustar el sistema.

La indización puede ser un factor clave en relación con el número de bloqueos que son necesarios para tener acceso a los datos. Un índice puede reducir el número de registros a los que accede una consulta mediante la reducción del número de búsquedas internas que debe realizar el motor de base de datos. Por ejemplo, al seleccionar una sola fila de una tabla en una columna que no está indizada, cada fila de la tabla necesita ser bloqueada temporalmente hasta que se identifique el registro deseado. Por contra, si la columna estuviera indizada, sólo se necesitaría un bloqueo.

Tanto SQL Server 2005 como SQL Server 2008 contienen vistas de administración dinámica (sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details) que revelan tablas y columnas que se beneficiarían de los índices en función de las estadísticas de uso acumuladas.

La fragmentación también puede verse implicada en los problemas de rendimiento, en tanto que el motor de base de datos puede necesitar acceso a más páginas que en condiciones normales. Además, una estadística incorrecta puede hacer que el optimizador de consultas elija un plan menos eficaz.

Tenga presente que aunque los índices aceleran el acceso a datos, también pueden ralentizar la modificación de datos porque además de que los datos subyacentes necesitan cambiar, los índices también necesitan actualizarse. La vista de administración dinámica sys.dm_db_index_usage_stats destaca con qué frecuencia se usan los índices. Un ejemplo común de indización ineficaz es el de los índices compuestos, donde la misma columna se indiza de forma aislada y combinada. Puesto que SQL Server obtiene acceso a los índices de izquierda a derecha, se usarán los índices siempre que las columnas situadas más a la izquierda sean útiles.

Crear particiones de las tablas puede optimizar el sistema (reduciendo la exposición a bloqueos) y dividir los datos en distintos objetos físicos que se pueden tratar por separado. Aunque habilitar las particiones de fila es una manera más obvia de separar los datos, crear particiones horizontales de los datos es otra opción a tener en cuenta. Puede elegir deshacer la normalización intencionadamente mediante la división de una tabla en varias tablas individuales con el mismo número de filas y claves, pero con un número distinto de columnas, para reducir las posibilidades de que diferentes procesos quieran tener acceso exclusivo a los datos al mismo tiempo.

Cuanto más diversas sean las formas en que una aplicación puede tener acceso a una fila de datos concreta y cuantas más columnas haya en esa fila, más atractivo será el enfoque de creación de particiones de columna. Las tablas de estado y la cola de aplicaciones pueden beneficiarse a veces de este enfoque. SQL Server 2008 agrega la capacidad de deshabilitar las extensiones de bloqueo por partición (o por tabla si las particiones no están habilitadas para la tabla).

Optimización de consultas

La optimización de consultas juega un papel importante en la mejora del rendimiento. Aquí tiene tres enfoques que puede adoptar:

Acorte la transacción Una de las formas más importantes de reducir el bloqueo, así como de mejorar el rendimiento general, es asegurarse de que las transacciones son tan pequeñas como sea posible. Debería omitirse todo procesamiento que no sea esencial para la integridad de la transacción (como la búsqueda de datos relacionados, la indización y la limpieza de datos) para reducir su tamaño.

SQL trata cada instrucción como una transacción implícita. Si la instrucción afecta a muchas filas, una sola instrucción puede constituir una transacción grande, especialmente si hay muchas columnas implicadas o si las columnas contienen un tipo de datos grande. Una sola instrucción también puede causar divisiones de página si el factor de relleno es alto o si una instrucción UPDATE rellena una columna con un valor más ancho del que había sido asignado. En tales circunstancias, puede ser útil dividir la transacción en grupos de filas y procesarlas de una en una hasta completarlas. Procesarlas por lotes sólo debe considerarse cuando la instrucción individual o los grupos de instrucciones pueden dividirse en lotes más pequeños que aún pueden ser considerados completos como unidad de trabajo si se realizan correctamente o si generan errores.

Secuencie la transacción Dentro de la transacción, secuenciar intencionadamente las instrucciones puede reducir la posibilidad de bloqueo. Hay dos principios a tener en cuenta. Primero, acceda a los objetos en el mismo orden dentro del código SQL de su sistema. Sin un orden coherente, pueden producirse interbloqueos cuando dos procesos que compiten obtienen acceso a los datos en distinto orden, causando un error del sistema para uno de los procesos. En segundo lugar, coloque al final de la transacción los objetos a los que se obtiene acceso con frecuencia o cuyo acceso resulta caro. SQL espera a que los objetos sean necesarios en la transacción para bloquearlos. Demorar el acceso a las "zonas activas" permite que estos objetos mantengan los bloqueos durante un porcentaje reducido de tiempo.

Use las sugerencias de bloqueo Las sugerencias de bloqueo se pueden usar en el nivel de sesión o en el nivel de instrucción para una vista o una tabla específica. Un escenario típico para usar sugerencias en el nivel de sesión sería el de un procesamiento por lotes en un almacén de datos donde el desarrollador sabe que el proceso será el único en ejecutarse en un momento dado en ese conjunto de datos. Usando un comando como SET ISOLATION LEVEL READ UNCOMMITTED al principio del procedimiento almacenado, SQL Server no reservará ningún bloqueo de lectura, reduciendo así la sobrecarga general de bloqueo y aumentando el rendimiento.

Un escenario típico para usar sugerencias en el nivel de instrucción sería cuando el desarrollador sabe que se puede producir una lectura de datos sucios sin riesgos (como al leer una sola fila de una tabla donde otros procesos simultáneos nunca necesitarán esa misma fila) o cuando todos los esfuerzos por ajustar el rendimiento hayan sido erróneos (diseño de esquema, diseño y mantenimiento de índice y ajuste de consulta) y el desarrollador quiera forzar al compilador a que use una clase específica de sugerencia.

Las sugerencias de bloqueo de fila pueden tener sentido si la supervisión demuestra que se producen bloqueos de grano más grande cuando muy pocos registros se ven afectados por la consulta, ya que esto podría reducir el bloqueo. Las sugerencias de bloqueo de tabla pueden tener sentido si la supervisión muestra que los bloqueos de grano más pequeño se mantienen (no se extienden) cuando casi todos los registros de la tabla se ven afectados por la consulta, ya que esto podría reducir los recursos del sistema necesarios para mantener los bloqueos. Tenga en cuenta que especificar una sugerencia de bloqueo no garantiza que no se extienda el bloqueo cuando el número de bloqueos alcance el umbral de la memoria del sistema. Sin embargo, esta acción evitará otras extensiones.

Ajuste la configuración

Como muestra la Figura 2, hay varios factores a tener en cuenta al configurar el sistema de SQL Server.

Figura 2** Cómo determina SQL Server la cantidad de memoria que se puede usar para el bloqueo **(Hacer clic en la imagen para ampliarla)

Memoria Los bloqueos se mantienen siempre en la memoria no AWE, así que aumentar el tamaño de la memoria no AWE aumentará también la capacidad del sistema para mantener los bloqueos.

Una arquitectura de 64 bits debe ser una prioridad al intentar aumentar la capacidad de bloqueo, ya que la arquitectura de 32 bits está limitada a 4 GB de memoria no AWE mientras que la de 64 bits no está limitada en absoluto.

En sistemas de 32 bits, puede tomar un gigabyte adicional de memoria del sistema operativo para SQL Server agregando el conmutador /3GB al archivo Boot.ini.

Valores de configuración de SQL Server Varios de los valores de configuración que se pueden ajustar a través de sp_configure afectan al bloqueo. Los valores de bloqueo configuran cuántos bloqueos puede mantener el sistema antes de generar un error. De forma predeterminada, la configuración es 0, lo que significa que el servidor ajustará dinámicamente los bloqueos reservados con otros procesos que compiten por la memoria. SQL reservará inicialmente 2.500 bloqueos y cada bloqueo consume 96 bytes de memoria. La memoria paginada no se usa.

Los valores mínimo y máximo de memoria reservan la cantidad de memoria usada por SQL Server, configurando así el servidor para mantener la memoria de manera estática. Puesto que la extensión de bloqueo está relacionada con la memoria disponible, reservar la cantidad de memoria de los procesos que compiten puede marcar la diferencia con respecto a si las extensiones se producen o no.

Configuración de la conexión De forma predeterminada, los bloqueos activos no expiran, pero puede usar la configuración @@LOCK_TIMEOUT para generar un error si se excede el umbral de espera especificado para liberar un bloqueo.

Marcas de traza Dos marcas de traza en particular están relacionadas con las extensiones de bloqueo. Una es la marca de traza 1211, que deshabilita las extensiones de bloqueo. Si el número de bloqueos consumidos excede la memoria disponible, se genera un error. La otra marca de traza es la 1224, que deshabilita las extensiones de bloqueo para instrucciones individuales.

Supervisión del sistema

Otras lecturas

• Microsoft SQL Server 2005 por dentro: El Motor de Almacenamiento de Kalen Delaney
• Cómo supervisar el bloqueo en SQL Server 2005 y SQL Server 2000
• Script de estadísticas de rendimiento de SQL Server 2005
• Cómo resolver los problemas de bloqueo causados por la extensión de bloqueo en SQL Server
• Extensión de bloqueo (motor de base de datos)

Se puede supervisar el impacto que tiene el bloqueo sobre el rendimiento general del sistema de bloques y bloqueos mediante el sondeo de datos de estado a intervalos específicos (quizás por hora) y la captura de las estadísticas que se ejecutan en los bloqueos mantenidos. La información clave que se debe capturar es:

• Objeto afectado, grano y tipo de bloqueo
• Duración de bloque y bloqueo
• Comando SQL que se emite (nombre del procedimiento almacenado, instrucción SQL)
• Información acerca de la cadena de bloqueo, si es relevante
• Cómo consume el sistema su capacidad de bloqueo disponible

Puede ejecutar un script como el de la Figura 3 para capturar esta información y escribirla en una tabla con la marca de hora pertinente. Y para desglosar aún más el ResourceId de los datos que se están bloqueando, puede ejecutar un script como el de la Figura 4.

Figure 4 Más información acerca de los datos bloqueados

DECLARE @SQL                           nvarchar(max)
      , @CallingResourceType           varchar(30)
      , @Objectname                    sysname
      , @DBName                        sysname
      , @resource_associated_entity_id int

-- TODO: Set the variables for the object you wish to look up

SET @SQL = N'
USE     ' + @DbName + N'
DECLARE @ObjectId int

SELECT  @ObjectId = CASE
                    WHEN @CallingResourceType = ''OBJECT''
                    THEN @resource_associated_entity_id
                    WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
                    THEN (SELECT  object_id
                          FROM    sys.partitions 
                          WHERE   hobt_id = @resource_associated_entity_id)
                    WHEN @CallingResourceType = ''ALLOCATION_UNIT''
                    THEN (SELECT  CASE
                                     WHEN type IN (1, 3)
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions 
                                           WHERE   hobt_id = allocation_unit_id)
                                     WHEN type = 2
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions 
                                           WHERE   partition_id = allocation_unit_id)
                                     ELSE NULL
                                     END
                          FROM    sys.allocation_units 
                          WHERE   allocation_unit_id = @resource_associated_entity_id)
                    ELSE NULL
                    END

SELECT  @ObjectName = OBJECT_NAME(@ObjectId)'

EXEC    dbo.sp_executeSQL
        @SQL
      , N'@CallingResourceType varchar(30)
      , @resource_associated_entity_id int
      , @ObjectName sysname OUTPUT'
      , @resource_associated_entity_id = @resource_associated_entity_id
      , @CallingResourceType = @CallingResourceType
      , @ObjectName = @ObjectName OUTPUT

Figure 3 Captura de estadísticas de bloqueo

SELECT  er.wait_time                      AS WaitMSQty
      , er.session_id                     AS CallingSpId
      , LEFT(nt_user_name, 30)            AS CallingUserName
      , LEFT(ces.program_name, 40)        AS CallingProgramName
      , er.blocking_session_id            AS BlockingSpId
      , DB_NAME(er.database_id)           AS DbName
      , CAST(csql.text AS varchar(255))   AS CallingSQL
      , clck.CallingResourceId
      , clck.CallingResourceType
      , clck.CallingRequestMode
      , CAST(bsql.text AS varchar(255))   AS BlockingSQL
      , blck.BlockingResourceType
      , blck.BlockingRequestMode
FROM    master.sys.dm_exec_requests er WITH (NOLOCK)
        JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
          ON er.session_id = ces.session_id
        CROSS APPLY fn_get_sql (er.sql_handle) csql
        JOIN (
-- Retrieve lock information for calling process, return only one record to 
-- report information at the session level
              SELECT  cl.request_session_id                 AS CallingSpId
                    , MIN(cl.resource_associated_entity_id) AS CallingResourceId
                    , MIN(LEFT(cl.resource_type, 30))       AS CallingResourceType
                    , MIN(LEFT(cl.request_mode, 30))        AS CallingRequestMode 
-- (i.e. schema, update, etc.)
              FROM    master.sys.dm_tran_locks cl WITH (nolock)
              WHERE   cl.request_status = 'WAIT' -- Status of the lock request = waiting
              GROUP BY cl.request_session_id
              ) AS clck
           ON er.session_id = clck.CallingSpid
         JOIN (
              -- Retrieve lock information for blocking process
              -- Only one record will be returned (one possibility, for instance, 
              -- is for multiple row locks to occur)
              SELECT  bl.request_session_id            AS BlockingSpId
                    , bl.resource_associated_entity_id AS BlockingResourceId
                    , MIN(LEFT(bl.resource_type, 30))  AS BlockingResourceType
                    , MIN(LEFT(bl.request_mode, 30))   AS BlockingRequestMode
              FROM    master.sys.dm_tran_locks bl WITH (nolock)
              GROUP BY bl.request_session_id
                    , bl.resource_associated_entity_id 
              ) AS blck
           ON er.blocking_session_id = blck.BlockingSpId
          AND clck.CallingResourceId = blck.BlockingResourceId
        JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
          ON er.blocking_session_id = ber.session_id
        CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE   ces.is_user_process = 1
        AND er.wait_time > 0

Puede supervisar las extensiones del sistema a través de Analizador de SQL Server (Bloqueo: evento de extensión), la vista de administración dinámica dm_db_index_operational_stats (index_lock_promotion_count) o mediante sondeos regulares de la información de bloqueo del sistema. La información pertinente que hay que deducir de la supervisión de las extensiones es si el procesamiento garantiza una extensión. De lo contrario, los procedimientos almacenados relevantes pueden localizar con toda precisión la causa raíz de los problemas de rendimiento. El enfoque principal de la evaluación deberían ser las tablas con grandes cantidades de datos o un alto volumen de uso simultáneo.

Después que reunir los datos de bloqueo, bloques y extensiones, se pueden analizar los datos para determinar el tiempo acumulativo de bloque y bloqueo (el número de incidencias multiplicado por la duración de las incidencias) por objeto. Normalmente, esto puede iniciar un ciclo iterativo de ajuste del rendimiento en el que se implementan, supervisan, analizan y corrigen los cambios. A veces, para alterar el área del sistema donde se produce el cuello de botella de rendimiento más doloroso, basta con realizar un cambio sencillo como, por ejemplo, agregar un índice que mejore considerablemente el rendimiento.

Puede encontrar más información acerca de la reducción del bloqueo en SQL Server en la barra lateral "Otras lecturas". Si se presta atención para mantener las transacciones pequeñas a través de las fases de diseño, codificación y estabilización, pueden minimizarse muchos problemas de bloqueo. Un hardware adecuado también puede reducir notablemente la posibilidad de extensiones no deseadas. No obstante, la evaluación continua del bloqueo en el sistema puede identificar rápidamente la raíz de los problemas de rendimiento.

Cherié Warren es responsable senior de desarrollo en Microsoft TI. Actualmente es responsable de una de las bases de datos de transacciones más grandes de Microsoft. Cherié también oficia con frecuencia como consultora acerca de las causas principales y los problemas de rendimiento de dirección relacionados con el bloqueo. Se ha especializado en bases de datos de SQL Server a nivel empresarial durante 10 años.

© 2008 Microsoft Corporation and CMP Media, LLC. Reservados todos los derechos; queda prohibida la reproducción parcial o total sin previa autorización.