Recomendaciones para reducir la contención de asignaciones en la base de datos Tempdb de SQL Server

Se aplica a: SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 Express

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 de tempdb . Además, el tipo de espera es PAGELATCH_UP y 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 entre 8088, se trata de 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 mucho tempdb :
  • Repetative: operación 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 los CURSOres.
  • 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 los planes HASH.
Estas actividades pueden causar problemas de contención.

Causa


Cuando se usa mucho la base de datos tempdb , 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 solicitudes que implican a tempdb no respondan momentáneamente.
Durante la creación de objetos, se deben asignar dos (2) páginas a partir de una extensión mixta y se pueden asignar al objeto nuevo. Una página es para el mapa de asignación de índices (IAM) y la segunda es para la primera página del objeto. SQL Server realiza el seguimiento de extensiones mixtas mediante la página mapa de asignación global compartido (SGAM). Cada página de SGAM realiza un seguimiento de 4 gigabytes de datos.Para asignar una página desde la extensión mixta, SQL Server debe examinar la página de espacio libre de página (PFS) para determinar qué página mixta se va a asignar de forma gratuita. 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 páginas de 8000. Se mantiene la sincronización adecuada para realizar cambios en las páginas PFS y SGAM; y que pueden atascar otros modificadores para períodos cortos.Cuando SQL Server busca una página mixta para asignar, siempre inicia el examen en el mismo archivo y página de SGAM. Esto provoca una contención intensa en la página de SGAM cuando se están realizando varias asignaciones de página mixta. Esto puede provocar los problemas documentados en la sección "síntomas".Nota: Las actividades de eliminación de asignació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 diferentes mecanismos de asignación que usa SQL Server (SGAM, GAM, PFS, IAM), consulte la sección "referencias".

Resolución


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 y reducir la contención de las estructuras de asignación. Como 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 superior a 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 el contención se reduzca a niveles aceptables. Como alternativa, puede realizar cambios en la carga de trabajo o en el código.
  • Considere la posibilidad de implementar las recomendaciones de procedimientos recomendados en el siguiente tema de TechNet:  
  • Si los pasos anteriores no reducen significativamente la contención de asignación y la contención se encuentra en páginas de 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 de las páginas de SGAM. Notas  
  • Aplique el CU correspondiente para SQL Server 2016 y 2017 para aprovechar las ventajas de la actualización reciente. Se ha hecho 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 de operación por turnos en todos los archivos de datos de tempdb , la corrección mejora la asignación de páginas PFS al realizar asignaciones de operación por turnos en varias páginas PFS en el mismo archivo de datos. Para obtener más información, vea el siguiente contenido:  

Más información


Aumentar el número de archivos de datos de tempdb que tienen el mismo tamaño

Como 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, se recomienda aumentar el número de archivos de datos a ocho (8 GB para mantener el mismo tamaño) y dejar el archivo de registro tal cual. Disponer de los diferentes archivos de datos en discos separados sería ofrecer beneficios adicionales de rendimiento. Sin embargo, esto no es necesario. Los archivos pueden coexistir en el mismo volumen de disco. El número óptimo de archivos de datos de tempdb depende del grado de contención observado en tempdb. Como punto de partida, puede configurar tempdbser al menos igual al número de procesadores lógicos asignados para SQL Server. En el caso de sistemas superiores, el número inicial puede ser ocho (8). Si no se reduce la contención, es posible que tenga que aumentar el número de archivos de datos.Le recomendamos que use el mismo tamaño que 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ágina mixta. Debido a esta mejora, el archivo de inicio es diferente para cada asignación de página mixta consecutiva (si hay más de un archivo). El nuevo algoritmo de asignación de SGAM es una operación por turnos y no respeta el relleno proporcional para mantener la velocidad. Le recomendamos que cree todos los archivos de datos de tempdbcon el mismo tamaño.  

Cómo aumentar el número de archivos de datos de tempdb reduce la contención

En la siguiente lista se explica cómo aumentar el número de archivos de datos de tempdb que tienen el mismo tamaño reducen la contención:
  • Si tiene un archivo de datos para tempdb, solo tiene una página GAM y una página de SGAM por cada 4 GB de espacio.
  • Si se aumenta el número de archivos de datos que tienen el mismo tamaño para tempdb , se crean de forma eficaz una o varias páginas GAM y SGAM para cada archivo de datos.
  • El algoritmo de asignación de GAM asigna una de las extensiones a la vez (ocho páginas contiguas) desde el número de archivos en un modo de operación por turnos mientras se respeta el relleno proporcional. Por lo tanto, si tiene 10 archivos de igual tamaño, la primera asignación es de archivo1, la segunda de archivo2, la tercera de Archivo3, etc.
  • La contención de recursos de la página PFS se reduce porque se marcan ocho páginas a la vez como completas porque GAM está asignando las páginas.
 

Cómo implementar el indicador de seguimiento: T1118 reduce la contención

En la siguiente lista se explica cómo usar 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 siga teniendo efecto incluso después de que se haya reciclado SQL Server.
  • -T1118 elimina casi todas las asignaciones de página única en el servidor.
  • Al deshabilitar la mayoría de las asignaciones de página individuales, se reduce la contención en la página de SGAM.
  • Si está activado -T1118 , casi todas las asignaciones nuevas se realizan desde una página GAM (por ejemplo, 2:1:2) que asigna ocho (8) páginas (1 extensión) al mismo tiempo 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 una sola página de la página de SGAM, incluso si está activada la -T1118 . Sin embargo, cuando se combina con el Hotfix 8.00.0702 y se aumentan los archivos de datos tempdb , el efecto de la red es una reducción en la página de SGAM. En el caso de problemas de espacio, consulte la siguiente sección.
Desventajas

El inconveniente de usar -T1118 es que es posible que vea aumentos en el tamaño de la base de datos si se cumplen las siguientes condiciones:

  • 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 (8 páginas * 8 KB = 64 KB) para un objeto que solo requiere 8 KB de espacio, lo que hace que se pierdan 56 KB de almacenamiento. Sin embargo, si el nuevo objeto usa más de 64 KB (8 páginas) en su período de duración, no hay ninguna desventaja en 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 de objetos nuevos que nunca crecen más allá de una (1) página.

Referencias


Para obtener más información sobre los productos o herramientas que comprueban automáticamente esta condición en la instancia de SQL Server y en las versiones del producto SQL Server, consulte la tabla siguiente.

Software de regla Título de regla Descripción de regla Versiones de producto para las que se ha evaluado la regla
System Center Advisor Configuración de base de datos de SQL Server: el número de archivos de datos de tempdb puede causar bloqueo System Center Advisor comprueba el número de archivos de datos que están configurados para la base de datos tempdb. Si solo hay uno, y el servidor que ejecuta SQL Server usa más de un procesador, esta alerta se produce si se genera. Revise la información de este artículo y agregue más archivos de datos a la base de datos tempdb. SQL Server 2008 SQL Server 2008 R2 SQL Server 2012