Mejoras de concurrencia para la base de datos tempdb

Nº DE ERROR: 362099 (SHILOH_BUGS)

Síntomas

Cuando la base de datos tempdb es un uso intensivo, SQL Server puede experimentar contención cuando intenta asignar páginas.

Desde la salida de tabla del sistema sysprocesses , el waitresource puede aparecer como "2:1:1" (página PFS) o "2:1:3" (página SGAM). Dependiendo del grado de contención, esto también puede conducir a SQL Server que aparece no responde durante cortos períodos.


Estas operaciones utilizan intensivamente tempdb:
  • Repite crear y colocar temporales de las tablas (local o global).
  • Variables de tabla que utilizan tempdb para propósitos de almacenamiento.
  • Tablas asociadas con los CURSORES de trabajo.
  • Tablas de trabajo asociadas con una cláusula ORDER BY.
  • Tablas de trabajo asociadas con una cláusula GROUP BY.
  • Funcionan los archivos asociados con los planes de HASH.
Uso pesado y significativo de estas actividades puede llevar a problemas de contención.

Causa

Durante la creación del objeto y dos (2) páginas deben ser asignadas desde una extensión mixta y asignadas al nuevo objeto. Una página es para el mapa de asignación de índices (IAM), y la segunda es para la primera página para el objeto. SQL Server realiza el seguimiento de extensiones mixtas mediante la página del mapa de asignación Global compartido (SGAM). Cada página SGAM pistas unos 4 gigabytes de datos.

Como parte de la asignación de una página de la extensión mixta, SQL Server debe buscar la página página libre espacio (PFS) para averiguar qué página mixta es libre de asignar. La página PFS realiza un seguimiento del espacio libre disponible en cada página y realiza el seguimiento de cada página PFS aproximadamente 8000 páginas. Se mantiene la sincronización apropiada para realizar cambios en las páginas PFS y SGAM; y otros modificadores que puede entorpecer durante cortos períodos.

Cuando SQL Server busca una página mixta asignar, el análisis se inicia siempre en el mismo archivo y página SGAM. Esto produce intensa contención en la página SGAM cuando varios mixto las asignaciones de página en marcha, que pueden causar los problemas documentados en la sección "Síntomas" de este artículo.

Nota: Actividades de eliminación de asignación también deben modificar las páginas, que pueden contribuir a la mayor contención.

Para obtener más información acerca de los mecanismos de asignación diferentes utilizados por SQL Server (SGAM, GAM, PFS, IAM), vea la sección "Referencias" de este artículo.

Solución

Microsoft SQL Server 2000


Para reducir la contención de recursos de la asignación de una tempdb que está experimentando el uso intenso, siga todos estos pasos:
  1. Aplicar Service Pack 4 de Microsoft SQL Server 2000. SQL Server 2000 Service Pack 4 (SP4) está disponible en el sitio Web de Microsoft siguiente:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5

    Para obtener información adicional, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
    290211 cómo obtener el service pack más reciente para SQL Server 2000
  2. Implementar el indicador de traza -T1118.
  3. Aumentar el número de archivos de datos de tempdb para maximizar el ancho de banda de disco y reducir la contención en las estructuras de asignación. Como regla general, si el número de procesadores lógicos es menor que 8 o igual a 8, utilice el mismo número de archivos de datos como procesadores lógicos. Si el número de procesadores lógicos es mayor que 8, utilizar archivos de datos de 8 y, a continuación, si continúa la contención, aumentar el número de archivos de datos en múltiplos de 4 (hasta el número de procesadores lógicos) hasta que la contención se reduce hasta alcanzar niveles aceptables o realizar cambios en el código de carga de trabajo.
Nota: Estos pasos también se aplican a Microsoft SQL Server 7.0. La única excepción es que no hay ninguna revisión para SQL Server 7.0; por lo tanto, no se aplica el paso 1.

En relación con el paso 2, marcar el uso de traza - T1118 para Microsoft SQL Server 7.0, antes de utilizar el indicador de traza, consulte el siguiente artículo en Microsoft Knowledge Base:
CORREGIR 813492 : crear índice se produce un error en SQL Server 7.0 cuando se habilita el indicador de traza 1118

Microsoft SQL Server 2005 y versiones posteriores

Para reducir la contención de recursos de la asignación de una tempdb que está experimentando el uso intenso, siga estos pasos:
  1. Implementar el seguimiento de la bandera - T1118.
  2. Aumentar el número de archivos de datos de tempdb para maximizar el ancho de banda de disco y reducir la contención en las estructuras de asignación. Como regla general, si el número de procesadores lógicos es menor o igual a 8, utilice el mismo número de archivos de datos como procesadores lógicos. Si el número de procesadores lógicos es mayor que 8, utilizar archivos de datos de 8 y, a continuación, si continúa la contención, aumentar el número de archivos de datos en múltiplos de 4 (hasta el número de procesadores lógicos) hasta que la contención se reduce hasta alcanzar niveles aceptables o realizar cambios en el código de carga de trabajo.

Más información

¿Cómo reduce la contención de la corrección en SQL 2000 SP4 y versiones posteriores

SQL Server 2000 Sp4 y versiones posteriores tienen una corrección que introduce un algoritmo por turnos para asignaciones de página mixta. Con la solución, el archivo de inicio ahora será diferente para cada asignación de página mixta consecutivos (si existe más de un archivo). Esto evita el problema de contención dividiendo el tren que pasó por el SGAMs en el mismo orden cada vez con el mismo punto de inicio. El nuevo algoritmo de asignación de página SGAM es turnos puro y no respeta el relleno proporcional para mantener la velocidad. Microsoft recomienda crear tempdb de archivos de datos con el mismo tamaño.

Cómo implementar el indicador de traza-T1118 reduce la contención

Aquí está la lista de cómo el uso de -T1118 reduce la contención:
  • -T1118 es una configuración general del servidor.
  • Incluir la -T1118 indicador en los parámetros de inicio de traza de SQL Server para que el indicador de traza permanece en efecto incluso después de que SQL Server se recicla.
  • -T1118 elimina casi todas las asignaciones de página único en el servidor.
  • Al deshabilitar la mayoría de las asignaciones de página único, se reduce la contención en la página SGAM.
  • Con -T1118 encendidos, casi todas las nuevas asignaciones se realizan desde una página GAM (por ejemplo, 2:1:2) que asigna páginas de ocho (8) (1 extensión) a la vez a un objeto en lugar de una sola página de una extensión para las páginas primero ocho (8) de un objeto, sin el indicador de traza.
  • Las páginas IAM seguir utilizan las asignaciones de página desde la página SGAM, incluso con -T1118 activada. Sin embargo, cuando se combina con revisión 8.00.0702 y archivos de datos de tempdb de mayor, el efecto neto es una reducción de la contención en la página SGAM. Para cuestiones de espacio, consulte la sección "Desventajas" de este artículo.

Aumentar el número de archivos de datos tempdb con igual tamaño

Si el tamaño de archivo de datos de tempdb es de 5 GB y el tamaño de archivo de registro es 5 GB, se recomienda aumentar el archivo de datos único a 10 (cada uno de 500 MB para mantener igual tamaño) y dejar el archivo de registro como está. Sería bueno tener los archivos de datos diferentes en discos independientes. Sin embargo, esto no es necesario y puede coexistir en el mismo disco.

El número óptimo de los 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 asignados para SQL Server. Para sistemas de extremo superior (por ejemplo, proc de 16 o 32), el número de inicio podría ser 10. Si no se reduce la contención, tendrá que aumentar el número de archivos de datos más.

Nota: Un procesador de doble núcleo se considera dos procesadores.

El tamaño de los archivos de datos igual es esencial porque el algoritmo de relleno proporcional se basa en el tamaño de los archivos. Si se crean archivos de datos con tamaño desigual, el algoritmo de relleno proporcional intenta utilizar el archivo más grande, más para asignaciones de GAM en lugar de propagarse las asignaciones entre los archivos, con lo que tiene sentido crear varios archivos de datos.

El crecimiento de los archivos de datos tempdb también puede interferir con el algoritmo de relleno proporcional. Por lo tanto, puede ser aconsejable desactivar la función de crecimiento de los archivos de datos tempdb . Si está desactivada la opción de crecimiento, debe asegurarse de crear los archivos de datos para que sean lo suficientemente grandes como para impedir que el servidor experimenta una falta de espacio en disco con tempdb.

Cómo aumentar el número de datos tempdb archivos con igual tamaño reduce la contención

A continuación le presentamos una lista de cómo aumentar el número de archivos de datos tempdb con igual tamaño reduce la contención:
  • Con un archivo de datos de tempdb, sólo tiene una página GAM y una página SGAM cada 4 GB de espacio.
  • Aumentar el número de archivos de datos con los mismos tamaños para
    tempdb crea efectivamente una o más páginas GAM y SGAM para cada archivo de datos.
  • El algoritmo de asignación de GAM proporciona una extensión a la vez (ocho páginas contiguas) del número de archivos en turnos respetando el relleno proporcional. Por lo tanto, si tiene 10 archivos de tamaño iguales, la primera asignación es de archivo1, el segundo de archivo2, la tercera de archivo3 y así sucesivamente.
  • Se reduce la contención de recursos de la página PFS porque ocho páginas están marcadas como completo en un momento dado GAM está asignando las páginas.

Desventajas

La única desventaja de las recomendaciones que se mencionó anteriormente es que puede ver el tamaño de las bases de datos de aumentar cuando se cumplen las condiciones siguientes:
  • Se crean nuevos objetos en una base de datos de usuario.
  • Cada uno de los nuevos objetos ocupan menos de 64 KB de almacenamiento de información.
Si se cumplen estas condiciones, puede asignar 64 KB (8 páginas * 8 KB = 64 KB) para un objeto que requiere solamente 8 KB de espacio, por lo tanto perder 56 KB de almacenamiento de información. Sin embargo, si el nuevo objeto utilizará más de 64 KB (8 páginas) es el período de duración, no hay ningún inconveniente con el indicador de traza. Por lo tanto, en el peor de los caso, SQL Server puede acabar asignar páginas adicionales de siete (7) durante la primera asignación sólo para los nuevos objetos que nunca crecer más allá de la página uno (1).


Referencias

Para obtener más información acerca de GAM, SGAM, PFS y IAM, consulte los siguientes temas en libros en pantalla de SQL Server 2000:
  • "Administrar el espacio utilizado por objetos"
  • "Administrar las asignaciones de extensiones y el espacio libre"
  • "Arquitectura de tablas e índice"
  • "Estructuras de montón"
Referencias adicionales

Para obtener más información acerca de la base de datos tempdb en SQL Server 2005, visite el siguiente sitio Web MSDN:

http://technet.microsoft.com/en-us/library/cc966545.aspx

Para obtener más información acerca de los archivos de base de datos tempdb y traza 1118 de Tlag, visite el siguiente sitio Web MSDN:

http://blogs.msdn.com/b/psssql/archive/2009/06/04/sql-server-tempdb-number-of-files-the-raw-truth.aspx

Para obtener más información acerca de cómo utilizar 1118 del indicador de traza en SQL Server 2005 y SQL Server 2008, visite el siguiente sitio Web MSDN:

http://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx

Para obtener más información acerca de cómo supervisar y solucionar problemas de cuellos de botella de asignación en la base de datos tempdb, visite el siguiente sitio Web MSDN:

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/11/tempdb-monitoring-and-troubleshooting-allocation-bottleneck.aspx


Propiedades

Id. de artículo: 328551 - Última revisión: 17 ene. 2017 - Revisión: 1

Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2000 Personal Edition, Microsoft SQL Server 2000 Workgroup Edition

Comentarios