Recomendaciones para reducir la contención de asignación en la base de datos tempdb de SQL Server.
Este artículo le ayuda a resolver el problema en el que observa un bloqueo grave cuando el servidor está experimentando una carga pesada.
Versión del producto original: SQL Server
Número de KB original: 2154845
Síntomas
En un servidor que ejecuta Microsoft SQL Server, observará un bloqueo grave cuando el servidor está experimentando una carga pesada. Las vistas de administración dinámica [sys.dm_exec_request
o sys.dm_os_waiting_tasks
] indican que estas solicitudes o tareas están esperando recursos tempdb . Además, el tipo de espera es PAGELATCH_UP
y el recurso de espera apunta a páginas de tempdb. Estas páginas pueden tener el formato 2:1:1, 2:1:3, etc. (páginas PFS y SGAM en tempdb).
Nota:
Si una página es divisible uniformemente en 8088, es una página PFS. Por ejemplo, la página 2:3:905856 es un PFS en file_id=3 en tempdb.
Las siguientes operaciones usan tempdb ampliamente:
- Operación repetitiva de creación y eliminación de tablas temporales (locales o globales).
- Variables de tabla que usan tempdb para el almacenamiento.
- Tablas de trabajo asociadas a CURSORS.
- Tablas de trabajo asociadas a una cláusula ORDER BY.
- Tablas de trabajo asociadas a una cláusula GROUP BY.
- Archivos de trabajo asociados con HASH PLANS.
Estas actividades pueden causar problemas de contención.
Causa
Cuando la base de datos tempdb se usa en gran medida, SQL Server puede experimentar contención cuando intenta asignar páginas. Según el grado de contención, esto puede hacer que las consultas y las solicitudes que implican tempdb no respondan brevemente.
Durante la creación de objetos, se deben asignar dos (2) páginas desde una extensión mixta y asignarlas al nuevo objeto. Una página es para el mapa de asignación de índices (IAM) y el segundo es para la primera página del objeto. SQL Server realiza un seguimiento de extensiones mixtas mediante la página Mapa de asignación global compartido (SGAM). Cada página de SGAM realiza un seguimiento de aproximadamente 4 gigabytes de datos.
Para asignar una página desde la extensión mixta, SQL Server debe examinar la página Espacio libre de páginas (PFS) para determinar qué página mixta está disponible para asignarse. La página PFS realiza un seguimiento del espacio libre disponible en cada página y cada página PFS realiza un seguimiento de aproximadamente 8000 páginas. Se mantiene la sincronización adecuada para realizar cambios en las páginas PFS y SGAM; y que pueden detener otros modificadores durante períodos cortos.
Cuando SQL Server busca una página mixta que se va a asignar, siempre inicia el examen en el mismo archivo y página SGAM. Esto provoca una contención intensa en la página SGAM cuando se están llevando a cabo varias asignaciones de páginas mixtas. Esto puede causar los problemas documentados en la sección Síntomas .
Nota:
Las actividades de desasignación también deben modificar las páginas. Esto puede contribuir al aumento de la contención.
Para obtener más información sobre los distintos mecanismos de asignación que usa SQL Server (SGAM, GAM, PFS, IAM), consulte la sección Referencias .
Solución
SQL Server 2016 y versiones posteriores:
Revisar
Optimización del rendimiento de la base de datos tempdb en SQL Server.
TEMPDB – Files and Trace Flags and Updates, Oh My! (TEMPDB: archivos, marcas de seguimiento y actualizaciones)
Aplique la CU pertinente para SQL Server 2016 y 2017 para aprovechar las ventajas de la siguiente actualización. Se ha realizado una mejora que reduce aún más la contención en SQL Server 2016 y SQL Server 2017. Además de la asignación round robin en todos los archivos de datos tempdb, la corrección mejora la asignación de páginas PFS mediante la realización de asignaciones round robin en varias páginas PFS en el mismo archivo de datos. Para obtener más información, vea KB4099472: mejora del algoritmo round robin de página PFS en SQL Server 2014, 2016 y 2017.
Para obtener más información sobre estas recomendaciones y otros cambios que se introdujeron en la revisión de SQL 2016
SQL Server 2014 y versiones anteriores:
Para mejorar la simultaneidad de tempdb, pruebe los métodos siguientes:
Aumente el número de archivos de datos en tempdb para maximizar el ancho de banda de disco y reducir la contención en las estructuras de asignación. Como regla, si el número de procesadores lógicos es menor o igual que ocho (8), use el mismo número de archivos de datos que los procesadores lógicos. Si el número de procesadores lógicos es mayor que ocho (8), use ocho archivos de datos. Si continúa la contención, aumente el número de archivos de datos por múltiplos de cuatro (4) hasta el número de procesadores lógicos hasta que la contención se reduzca a niveles aceptables. Como alternativa, realice cambios en la carga de trabajo o el código.
Considere la posibilidad de implementar las recomendaciones de procedimientos recomendados en Trabajar con tempdb en SQL Server 2005.
Si los pasos anteriores no reducen significativamente la contención de asignación y la contención está en páginas SGAM, implemente la marca de seguimiento -T1118. En esta marca de seguimiento, SQL Server asigna extensiones completas a cada objeto de base de datos, lo que elimina la contención en páginas SGAM.
Nota:
Esta marca de seguimiento afecta a todas las bases de datos de la instancia de SQL Server. Para obtener información sobre cómo determinar si la contención de asignación está en páginas SGAM, consulte la contención de supervisión causada por operaciones DML.
Para entornos de SQL Server 2014, asegúrese de aplicar Service Pack 3 para aprovechar las ventajas de la corrección documentada en el siguiente artículo de KB. La mejora reduce aún más la contención en entornos de SQL Server 2014. Además de la asignación round robin en todos los archivos de datos tempdb, la corrección mejora la asignación de páginas PFS mediante la realización de asignaciones round robin en varias páginas PFS en el mismo archivo de datos.
KB4099472: mejora del algoritmo round robin de página PFS en SQL Server 2014, 2016 y 2017
Blog del equipo tiger de MSSQL: archivos y marcas de seguimiento y actualizaciones en tempdb de SQL Server
Aumento del número de archivos de datos tempdb que tienen un tamaño igual
Por ejemplo, si el tamaño de archivo de datos único de tempdb es de 8 GB y el tamaño del archivo de registro es de 2 GB, la recomendación es aumentar el número de archivos de datos a ocho (8) (cada uno de 1 GB para mantener el tamaño igual) y dejar el archivo de registro tal cual. Tener los diferentes archivos de datos en discos independientes proporcionaría una ventaja de rendimiento adicional. pero esto no es obligatorio. Los archivos pueden coexistir en el mismo volumen de disco.
El número óptimo de archivos de datos tempdb depende del grado de contención visto en tempdb. Como punto de partida, puede configurar tempdb para que sea al menos igual al número de procesadores lógicos asignados para SQL Server. En el caso de los sistemas de gama superior, el número inicial podría ser ocho (8). Si no se reduce la contención, es posible que tenga que aumentar el número de archivos de datos.
Se recomienda usar el tamaño igual de los archivos de datos. SQL Server 2000 Service Pack 4 (SP4) introdujo una corrección que usa un algoritmo round robin para las asignaciones de páginas mixtas. Debido a esta mejora, el archivo de inicio es diferente para cada asignación de página mixta consecutiva (si existe más de un archivo). El nuevo algoritmo de asignación para SGAM es round robin puro y no respeta el relleno proporcional para mantener la velocidad. Se recomienda crear todos los archivos de datos tempdb con el mismo tamaño.
Cómo aumentar el número de archivos de datos tempdb reduce la contención
En la lista siguiente se explica cómo aumentar el número de archivos de datos tempdb que tienen un tamaño igual reduce la contención:
Si tiene un archivo de datos para tempdb, solo tiene una página GAM y una página SGAM para cada 4 GB de espacio.
Aumentar el número de archivos de datos que tienen los mismos tamaños para tempdb crea eficazmente una o varias páginas GAM y SGAM para cada archivo de datos.
El algoritmo de asignación de GAM asigna una extensión a la vez (ocho páginas contiguas) del número de archivos de forma round robin mientras respeta el relleno proporcional. Por lo tanto, si tiene 10 archivos de tamaño igual, la primera asignación es de File1, la segunda de File2, la tercera de File3, etc.
La contención de recursos de la página PFS se reduce porque ocho páginas a la vez se marcan como FULL porque GAM asigna las páginas.
Cómo implementar la marca de seguimiento -T1118 reduce la contención
Nota:
Esta sección solo se aplica a SQL Server 2014 y versiones anteriores.
En la lista siguiente se explica cómo el uso de la marca de seguimiento -T1118 reduce la contención:
- -T1118 es una configuración de todo el servidor.
- Incluya la marca de seguimiento -T1118 en los parámetros de inicio de SQL Server para que la marca de seguimiento permanezca en vigor incluso después de reciclar SQL Server.
- -T1118 quita casi todas las asignaciones de página única en el servidor.
- Al deshabilitar la mayoría de las asignaciones de página única, se reduce la contención en la página SGAM.
- Si -T1118 está activado, casi todas las nuevas asignaciones se realizan desde una página GAM (por ejemplo, 2:1:2) que asigna ocho (8) páginas (una extensión) a un objeto en lugar de una sola página desde una extensión para las ocho primeras (8) páginas de un objeto, sin la marca de seguimiento.
- Las páginas de IAM siguen usando las asignaciones de página única de la página SGAM, incluso si -T1118 está activada. Sin embargo, cuando se combina con la revisión 8.00.0702 y el aumento de los archivos de datos tempdb , el efecto neto es una reducción de la contención en la página SGAM. Para conocer los problemas de espacio, consulte la sección siguiente.
Desventajas
La desventaja de usar -T1118 es que puede ver aumentos en el tamaño de la base de datos si se cumplen las condiciones siguientes:
- Los nuevos objetos se crean en una base de datos de usuario.
- Cada uno de los nuevos objetos ocupa menos de 64 KB de almacenamiento.
Si estas condiciones son verdaderas, puede asignar 64 KB (ocho páginas * 8 KB = 64 KB) para un objeto que solo requiere 8 KB de espacio, lo que desperdicia 56 KB de almacenamiento. Sin embargo, si el nuevo objeto usa más de 64 KB (ocho páginas) durante su vigencia, no hay ninguna desventaja para la marca de seguimiento. Por lo tanto, en el peor de los casos, SQL Server puede asignar siete (7) páginas adicionales durante la primera asignación solo para nuevos objetos que nunca crecen más allá de una página (1).