La ejecución de consultas tarda más tiempo en réplicas secundarias alwaysOn de SQL Server

Este artículo le ayuda a resolver el problema en el que experimenta problemas de rendimiento de consultas en réplicas secundarias de solo lectura.

Versión del producto original: SQL Server
Número de KB original: 4040549

Síntomas

Supongamos que tiene una base de datos miembro del grupo de disponibilidad AlwaysOn de Microsoft SQL Server que contiene una o varias tablas grandes que tienen un clustered row-store índice. Una consulta de una o varias de las tablas grandes se completa más rápido en la réplica principal que en una réplica secundaria.

Notas

• La consulta provoca un examen de índice agrupado de una gran parte de la tabla.
• La consulta usa la sugerencia NOLOCK .
• Los operadores del plan de ejecución y el orden del operador son idénticos para las ejecuciones rápidas y lentas.
• La consulta sys.dm_db_index_physical_stats revela una fragmentación significativa del índice agrupado.
• La unión de la base de datos del grupo de disponibilidad AlwaysOn mejora el rendimiento en la misma instancia de réplica secundaria (anterior) para que sea similar al rendimiento de la réplica principal.

Causa

Cuando se aplica el aislamiento de instantáneas, se omiten las sugerencias NOLOCK . La discrepancia en la duración de ejecución entre las réplicas principal y secundaria se produce porque la sugerencia NOLOCK se omite en la réplica secundaria de solo lectura en la que se aplica el aislamiento de instantáneas, pero no en la réplica principal donde no se aplica el aislamiento de instantáneas de forma predeterminada. Esto hace que el examen del índice agrupado tenga el orden de clave aplicado en la réplica secundaria. En la réplica principal, la sugerencia NOLOCK tiene prioridad y afecta al comportamiento. Cuando el índice clúster está muy fragmentado, la aplicación del orden de clave para el examen en la réplica secundaria de solo lectura hace que SQL Server emita lecturas de página única. Pero en la réplica principal, SQL Server realiza un examen de unidad de asignación para leer varias páginas por solicitud de E/S.

Solución

Para corregir este problema, vuelva a generar el índice en la réplica principal. Esta operación se propaga a las réplicas secundarias. Para obtener más información, consulte Recomendaciones para el mantenimiento de índices con grupos de disponibilidad AlwaysOn.

Más información

Es posible que la SET STATISTICS IO información de estadísticas de E/S reales del plan de ejecución y no le ayude en el diagnóstico cuando se produce este problema. Estos proporcionan información sobre el número de páginas que se leen, pero no el número de operaciones de E/S para leer las páginas.

En su lugar, busque primero la fragmentación del índice agrupado. Además, puede recopilar las operaciones de lectura de E/S de Monitor de rendimiento y bytes de lectura de E/S por segundo dos veces al ejecutar la consulta con la base de datos en el grupo de disponibilidad y de nuevo de la misma instancia cuando se quita la base de datos del grupo de disponibilidad y se pone en línea. Si la fragmentación de índices está causando lecturas de página única en la réplica secundaria, pero no en la réplica principal, esperaría ver un mayor número de E/S de lectura y un número menor de bytes de lectura/s cuando la base de datos se encuentra en el grupo de disponibilidad en comparación con cuando no es así.

Además, este comportamiento puede producirse pero no manifestarse notablemente en todos los entornos. Por ejemplo, un subsistema de E/S que puede controlar el mayor nivel de E/S con una latencia mínima y un rendimiento similar podría permitir que este problema pase desapercibido.