Recomendaciones para reducir la contención de asignación en SQL Server base de datos tempdb
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, se observa un bloqueo grave cuando el servidor experimenta una carga pesada. Vistas de administración dinámica [sys.dm_exec_request o sys.dm_os_waiting_tasks] indica que estas solicitudes o tareas están esperando los recursos de tempdb . Además, el tipo de espera es PAGELATCH_UPy el recurso de espera apunta a las 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 por 8088, es una página PFS. Por ejemplo, la página 2:3:905856 es un PFS en file_id=3 en tempdb.
Las operaciones siguientes usan tempdb ampliamente:
- Operación repetitiva de creación y eliminación de tablas temporales (local o global).
- 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 a PLANES HASH.
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. En función del grado de contención, esto puede hacer que las consultas y solicitudes que implican tempdb no respondan brevemente.
Durante la creación de objetos, dos (2) páginas deben asignarse desde una extensión mixta y asignarse al nuevo objeto. Una página es para el mapa de asignación de índices (IAM) y la segunda para la primera página del objeto. SQL Server realiza un seguimiento de las extensiones mixtas mediante la página Mapa de asignación global compartida (SGAM). Cada página de SGAM realiza un seguimiento de unos 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ágina (PFS) para determinar qué página mixta es libre de asignar. La página PFS realiza un seguimiento del espacio disponible en cada página y cada página pfs realiza un seguimiento de unas 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 asignar, siempre inicia el examen en el mismo archivo y página de SGAM. Esto provoca una contención intensa en la página SGAM cuando hay varias asignaciones de páginas mixtas en curso. Esto puede causar los problemas que se documentan 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 usan 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 – Archivos y marcas de seguimiento y Novedades, Oh My!
Aplique la CU correspondiente para SQL Server 2016 y 2017 para aprovechar 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, consulte 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 introducidos 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 del disco y reducir la contención en las estructuras de asignación. Por regla general, 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 en 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 las páginas de 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 las páginas de SGAM, vea la contención de supervisión causada por las operaciones de DML.
Para SQL Server entornos de 2014, asegúrese de aplicar Service Pack 3 para aprovechar la corrección documentada en el siguiente artículo de KB. La mejora reduce aún más la contención en SQL Server entornos de 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 de MSSQL Tiger Team: Archivos y marcas de seguimiento y actualizaciones en SQL Server tempdb
Aumentar el número de archivos de datos tempdb que tienen el mismo tamaño
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 sería una ventaja de rendimiento adicional. Sin embargo, esto no es necesario. 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 observado 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 mismo tamaño de los archivos de datos. SQL Server 2000 Service Pack 4 (SP4) introdujo una corrección que usa un algoritmo round robin para asignaciones de páginas mixtas. Debido a esta mejora, el archivo inicial 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 el aumento del número de archivos de datos tempdb reduce la contención
En la lista siguiente se explica cómo el aumento del número de archivos de datos tempdb que tienen el mismo tamaño reduce la contención:
Si tiene un archivo de datos para tempdb, solo tiene una página GAM y una página SGAM por cada 4 GB de espacio.
El aumento del número de archivos de datos que tienen los mismos tamaños para tempdb crea de forma eficaz una o varias páginas GAM y SGAM para cada archivo de datos.
El algoritmo de asignación para GAM asigna una extensión a la vez (ocho páginas contiguas) del número de archivos de forma round robin, al tiempo que respeta el relleno proporcional. Por lo tanto, si tiene 10 archivos de igual tamaño, 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 la implementación de 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 que se recicle SQL Server.
- -T1118 quita casi todas las asignaciones de página únicas 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 asignaciones nuevas 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 iam siguen usando las asignaciones de página única de la página SGAM, incluso si -T1118está activado. 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 es posible que vea 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 se cumplen estas condiciones, puede asignar 64 KB (ocho páginas * 8 KB = 64 KB) para un objeto que solo requiere 8 KB de espacio, con lo que se desperdician 56 KB de almacenamiento. Sin embargo, si el nuevo objeto usa más de 64 KB (ocho páginas) en su duración, 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 los objetos nuevos que nunca crecen más allá de una (1) página.