Consultas lentas por un cambio en el plan de ejecución Query plan regression (stats, parameter sniffing)
ID de la causa db-plan-flip · Responsable principal Infraestructura de BD (Equipo de infraestructura) · También Desarrollo de servidor (Equipo de desarrollo)
Aunque el código no cambie, si la BD cambia la forma de procesar una consulta (el plan de ejecución), una consulta que ayer tardaba 2 ms hoy tarda cientos de ms.
Por qué La actualización automática de estadísticas, un reinicio de la BD o un cambio en la distribución de los datos hacen que la BD calcule un plan de ejecución nuevo → Efecto Se elige un plan que no usa índice, la misma consulta pasa a ser de decenas a cientos de veces más lenta y las conexiones quedan ocupadas → En pantalla Sin ningún despliegue, la carga de una función concreta se vuelve lenta de repente y otras solicitudes también esperan
Responsable principal Infraestructura de BD (Equipo de infraestructura) · También Desarrollo de servidor (Equipo de desarrollo)
Tareas (Equipo de desarrollo)
Separar las consultas cuyo número de resultados varía mucho según el valor o valorar hints del plan, diseñar las consultas para que usen índice siempre.
Tareas (Equipo de infraestructura)
Vigilar los registros de consultas lentas y de planes de ejecución, fijar los planes buenos (Almacén de consultas de SQL Server, entre otros), controlar la hora de actualización de las estadísticas.
En el gráfico
Salto en escalón · Tiempo medio de ejecución por consulta
Dónde mirar
Recoger periódicamente el tiempo medio de cada forma de consulta y ver la tendencia: en MySQL, AVG_TIMER_WAIT de events_statements_summary_by_digest; en PostgreSQL, mean_exec_time de pg_stat_statements (mean_time en la versión 12 y anteriores). Comparar los planes de ejecución de antes y después de la ralentización con EXPLAIN o auto_explain de PostgreSQL, y en SQL Server con la vista Consultas con regresión (Regressed Queries) del Almacén de consultas
Se confirma si
Sin despliegue de por medio, el tiempo medio de una consulta se multiplica de golpe por varias decenas (salto en escalón), en un momento que coincide con una actualización de estadísticas o un reinicio de la BD, y el plan de ejecución ha cambiado
Se descarta si
Si el plan de ejecución no ha cambiado y aun así va más lento, apunta al crecimiento de los datos, a esperas por bloqueo (db-hot-row) o al disco
Se verifica con
Con herramientas de infraestructura (no hace falta código del juego)
Para saber más
SQL Server reutiliza el plan calculado para el primer valor que recibe (parameter sniffing). Si un plan calculado con un personaje nuevo que tiene unos pocos objetos se usa para un personaje veterano con decenas de miles, la consulta va mucho más lenta, y el caso contrario también es habitual. Al reiniciar se borran los planes, y a veces todo va bien un tiempo hasta que vuelve a empeorar.
Fuentes
Query Processing Architecture GuideMicrosoft SQL Server Parameter sniffing: el plan de ejecución se calcula según los valores de los parámetros que llegan al compilar o recompilar
Parameter Sensitive Plan OptimizationMicrosoft SQL Server Si los datos no están distribuidos de forma uniforme, un único plan en caché no sirve para todos los valores de los parámetros
Monitor performance by using the Query StoreMicrosoft SQL Server Los cambios de estadísticas, esquema o índices cambian el plan, y la caché de planes solo guarda el más reciente; forzar un plan en el Almacén de consultas fija el bueno, y la vista Consultas con regresión (Regressed Queries) compara las consultas ralentizadas y sus planes
Statement Summary TablesMySQL events_statements_summary_by_digest: COUNT_STAR y AVG_TIMER_WAIT (tiempo medio) por cada forma de consulta