Utilizando índices y hints para mejorar la concurrencia (y gestión de bloqueos) en soluciones de SQL Server

Recientemente hemos tratado un incidente con un cliente que tenía problemas de bloqueos en SQL Server. Me gustaría explicar el caso de este cliente — simplificando el problema, y aprovechar la oportunidad que los seguidores de nuestros blogs, entendáis algunos aspectos importantes de SQL Server, a saber, gestión de bloqueos, planes de ejecución, e indexación.

El escenario

El cliente tiene las siguientes dos consultas:

Consulta1:

UPDATE [Sales].[SalesOrderDetail]
SET UnitPrice+=1
WHERE SalesOrderID = 43659

Consulta2:

SELECT COUNT(*) FROM [Sales].[SalesOrderDetail]
WHERE SalesOrderID = 43659

Las dos consultas intentan tratar datos del mismo pedido: uno intenta modificar el precio unitario, y la otra intenta contar cuantas líneas de pedido existen.

A nivel de planes de ejecución tenemos lo siguiente:

Consulta1:

Consulta2:

La tabla en cuestión tiene los siguientes índices:

Con ello, si lanza la primera instrucción en contexto de transacción, la segunda consulta, no podrá resolverse porque no podrá “pasar” por el índice CLUSTERED (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID). Para comprobarlo puede utilizar el comando SP_LOCK, que le muestra los bloqueos adquiridos:

Donde puede apreciar (en color amarillo), como la conexión 54 intenta adquirir un bloqueo de lectura (tipo Shared – S), que no puede adquirir (estado WAIT) porque otra conexión (la 56) tiene un bloqueo (tipo Exclusivo – X) sobre la misma clave (0ca7b7436f59).

Para solucionar esa situación, debe proporcionar un camino alternativo a la consulta que quiere leer, para que no tenga que pasar sobre ese registro bloqueado.

Si vuelve a la consulta SELECT notará que sólo necesita contar cuantos registros cumplen la condición. Por otra parte, la consulta de actualización, necesita bloquear las claves correspondientes del índice CLUSTERED para proceder a hacer la modificación (el UPDATE).

Con ello, cualquiera de los otros índices de la tabla podría servir para satisfacer el SELECT COUNT(*). Para ello podemos contar con los hints de índices, e indicar cuál es el índice que deseas utilizar.

Por ejemplo, si cambiamos la instrucción como sigue:

SELECT COUNT(*) FROM [Sales].[SalesOrderDetail]
WITH (INDEX = [IX_SalesOrderDetail_ProductID])
WHERE SalesOrderID = 43659

A pesar de seguir el UPDATE en contexto de transacción la consulta se resolverá. La razón es porque estará utilizando un índice que no está bloqueando el UPDATE.

Es decir, hemos forzado un índice, para que evite pasar por el índice CLUSTERED. Para este caso, nos salva la situación pero deberá tener cuidado porque este índice incurrirá en un número mayor de lecturas.

Si ejecutamos las dos instrucciones juntas, podrá ver como SSMS nos indica que el índice forzado es ineficiente:

Y por otro lado, si muestras las estadísticas de E/S, podrás ver que usando el índice CLUSTERED se leen 3 páginas frente a las más de 300 de índice forzado.

Alternativamente podría crear un índice específico para esa query, que realizaría el mismo número de lecturas:

CREATE NONCLUSTERED INDEX nci_test
ON [Sales].[SalesOrderDetail]
(SalesOrderID )

Pero considere que necesitaría mantener también ese índice, con el coste que ello conllevaría. De hecho, el índice creado, lo considera el optimizador automáticamente para resolver la consulta sin necesidad de introducir el hint.

Conclusión:

• Para gestionar bien los bloqueos, debe entender qué recursos se bloquean, y cuál es la causa por la que otras conexiones no se pueden resolver sus consultas.
• Forzar índices puede ayudar a mejorar la gestión de bloqueos.
• Crear índices de apoyo (cobertura) suele ayudar a solventar estas situaciones.