Síntomas
Cuando la base de datos tempdb es muy usada, SQL Server puede experimentar contención al intentar asignar páginas.
Desde el resultado de la tabla del sistema de sysprocesses, el waitresource puede aparecer como "2:1:1" (página PFS) o "2:1:3" (página de SGAM). Dependiendo del grado de contención, esto también puede provocar que SQL Server parezcan no responder durante períodos cortos.
Estas operaciones usan mucho tempdb:
- Creación y colocación repetidas de tablas temporales (locales o globales).
- Variables de tabla que usan tempdb para fines de almacenamiento.
- Tablas de trabajo asociadas con 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 PLANES HASH.
El uso pesado y significativo de estas actividades puede conducir a los problemas de contención.
Causa
Durante la creación de objetos, se deben asignar dos (2) páginas 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 es para la primera página del objeto. SQL Server realiza un seguimiento de las extensiones mixtas utilizando la página mapa global de asignación compartida (SGAM). Cada página de SGAM hace un seguimiento de unos 4 gigabytes de datos.
Como parte de la asignación de una página desde la extensión mixta, SQL Server debe escanear la página de espacio libre (PFS) para averiguar qué página mixta es gratuita para ser asignada. La página PFS realiza un seguimiento del espacio disponible en cada página, y cada página PFS hace un seguimiento de unas 8000 páginas. Se mantiene la sincronización adecuada para realizar cambios en las páginas PFS y SGAM; y esto puede detener otros modificadores durante períodos cortos.
Cuando SQL Server busca una página mixta para asignar, siempre inicia el análisis en el mismo archivo y página de SGAM. Esto resulta en intensa contención en la página de SGAM cuando se están realizando varias asignaciones de páginas mixtas, lo que puede causar los problemas documentados en la sección "Síntomas" de este artículo.
Nota Las actividades de desasignación también deben modificar las páginas, lo que puede contribuir a aumentar la contención.
Para obtener más información sobre los diferentes mecanismos de asignación utilizados por SQL Server (SGAM, GAM, PFS, IAM), consulte la sección "Referencias" de este artículo.
Resolución
Microsoft SQL Server 2000
Para reducir la contención de recursos de asignación para una tempdb que está experimentando un uso intensivo, siga estos pasos:
Aplique el Service Pack 4 para Microsoft SQL Server 2000. SQL Server 2000 Service Pack 4 (SP4) está disponible en el siguiente sitio web de Microsoft:
http://www.microsoft.com/download/details.aspx?FamilyId=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5
Para obtener más información, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
290211 Cómo obtener el Service Pack de SQL Server 2000 más reciente
Implementar marca de seguimiento -T1118.
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. 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 que los procesadores lógicos. Si el número de procesadores lógicos es mayor que 8, use 8 archivos de datos y, a continuación, si la contención continúa, aumente 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 reduzca a niveles aceptables o realice cambios en la carga de trabajo/código.
Nota Estos pasos también se aplican a Microsoft SQL Server 7.0. La única excepción es que no hay revisión para SQL Server 7.0; por lo tanto, el paso 1 no se aplica.
Con respecto al paso 2, el uso de marca de seguimiento -T1118 para Microsoft SQL Server 7.0, antes de usar el indicador de seguimiento, consulte el artículo siguiente en Microsoft Knowledge Base:
813492 CORRECCIÓN: Se produce un error al crear índice en SQL Server 7.0 cuando se habilita la marca de seguimiento 1118
Microsoft SQL Server 2005 y versiones posteriores
Para reducir la contención de recursos de asignación para una tempdb que está experimentando un uso intensivo, siga estos pasos:
- Implementar marca de seguimiento -T1118.
- 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. Como regla general, si el número de procesadores lógicos es menor o igual que 8, utilice el mismo número de archivos de datos que los procesadores lógicos. Si el número de procesadores lógicos es mayor que 8, use 8 archivos de datos y, a continuación, si la contención continúa, aumente 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 reduzca a niveles aceptables o realice cambios en la carga de trabajo/código.
Más información
Cómo la corrección en SQL 2000 SP4 y versiones posteriores reduce la contención
SQL Server 2000 Sp4 y versiones posteriores tienen una corrección que introduce un algoritmo de round robin para asignaciones de páginas mixtas. Con la corrección, el archivo inicial será ahora diferente para cada asignación de página mixta consecutiva (si existe más de un archivo). Esto evita el problema de contención al dividir el tren que atraviesa los SGAMs en el mismo orden cada vez con el mismo punto de partida. El nuevo algoritmo de asignación para SGAM es puro round-robin, y no respeta el relleno proporcional para mantener la velocidad. Microsoft recomienda crear los archivos de datos tempdb con el mismo tamaño.
Cómo implementar el indicador de seguimiento -T1118 reduce la contención
Esta es la lista de cómo el uso de -T1118 reduce la contención:
-T1118 es una configuración para todo el servidor.
Incluya la marca de seguimiento -T1118 en los parámetros de inicio para SQL Server para que la marca de seguimiento permanezca en vigor incluso después de reciclar SQL Server.
-T1118 elimina casi todas las asignaciones de página únicas en el servidor.
Al deshabilitar la mayoría de las asignaciones de una sola página, reduce la contención en la página de SGAM.
Con -T1118 activado, 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) a la vez a un objeto en lugar de una sola página desde una extensión para las primeras ocho (8) páginas de un objeto, sin la marca de seguimiento.
Las páginas de IAM siguen usando las asignaciones de página únicas desde la página de SGAM, incluso con -T1118 activado. Sin embargo, cuando se combina con revisión 8.00.0702 y archivos de datos tempdb aumentados, el efecto neto es una reducción en la contención en la página de SGAM. Para cuestiones de espacio, consulte la sección "Desventajas" de este artículo.
Aumentar el número de archivos de datos tempdb con el mismo tamaño
Si el tamaño del archivo de datos de tempdb es de 5 GB y el tamaño del archivo de registro es de 5 GB, la recomendación es aumentar el único archivo de datos a 10 (cada uno de 500 MB para mantener el mismo tamaño) y dejar el archivo de registro como está. Disponer de los diferentes archivos de datos en discos independientes sería una buena opción. Sin embargo, esto no es necesario y pueden coexistir en el mismo disco.
El número óptimo de archivos de datos tempdb depende del grado de contención que se muestra en tempdb. Como punto de partida, puede configurar tempdb para que sea al menos igual al número de procesadores asignados a SQL Server. Para sistemas de extremo superior (por ejemplo, 16 o 32 proc), el número inicial podría ser 10. Si la contención no se reduce, puede que tenga que aumentar el número de archivos de datos.
Nota Se considera que un procesador de doble núcleo es dos procesadores.
El tamaño igual de los archivos de datos es fundamental porque el algoritmo de relleno proporcional se basa en el tamaño de los archivos. Si los archivos de datos se crean con tamaños desiguales, el algoritmo de relleno proporcional intenta utilizar el archivo más grande para las asignaciones GAM en lugar de distribuir las asignaciones entre todos los archivos, con lo que se elimina el propósito de crear varios archivos de datos.
El crecimiento automático de archivos de datos tempdb también puede interferir con el algoritmo de relleno proporcional. Por lo tanto, puede ser una buena idea desactivar la característica de crecimiento automático para los archivos de datos tempdb. Si la opción de crecimiento automático está desactivada, debe asegurarse de crear los archivos de datos para que sean lo suficientemente grandes para evitar que el servidor experimente una falta de espacio en disco con tempdb.
Cómo aumentar el número de archivos de datos tempdb con el mismo tamaño reduce la contención
Esta es una lista de cómo aumentar el número de archivos de datos tempdb con el mismo tamaño reduce la contención:
- Con un archivo de datos para tempdb, solo tiene una página GAM y una página de SGAM por cada 4 GB de espacio.
- Aumentar el número de archivos de datos con el mismo tamaño para
tempdb crea una o más páginas GAM y SGAM para cada archivo de datos. - El algoritmo de asignación para GAM otorga una extensión a la vez (ocho páginas contiguas) del número de archivos de forma round robin, a la vez que respeta el relleno proporcional. Por lo tanto, si tiene 10 archivos con el mismo 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 ocho páginas se marcan como COMPLETAS a la vez porque GAM asigna las páginas.
Inconvenientes
La única desventaja de las recomendaciones mencionadas anteriormente es que es posible que vea que el tamaño de las bases de datos aumenta cuando se cumplen las condiciones siguientes:
- Los objetos nuevos se crean en una base de datos de usuarios.
- Cada uno de los nuevos objetos ocupan menos de 64 KB de almacenamiento.
Si se cumplen estas condiciones, puedes asignar 64 KB (8 páginas * 8 KB = 64 KB) para un objeto que solo requiera 8 KB de espacio, lo que supone una desperdiciación de 56 KB de almacenamiento. Sin embargo, si el nuevo objeto usa más de 64 KB (8 páginas) en su vida útil, no hay ninguna desventaja con la marca de seguimiento. Por lo tanto, en el peor de los casos, SQL Server puede terminar asignando siete (7) páginas adicionales solo durante la primera asignación para los objetos nuevos que nunca crecen más allá de una (1) página.
Referencias
Para obtener más información sobre GAM, SGAM, PFS y IAM, consulte los siguientes temas de libros en línea de SQL Server 2000:
- "Administración de espacio utilizado por objetos"
- "Administración de asignaciones de extensión y espacio libre"
- "Arquitectura de tablas e índices"
- "Estructuras de montón"
Referencias adicionales
Para obtener más información sobre la base de datos tempdb en SQL Server 2005, visite el siguiente sitio web de MSDN:
http://technet.microsoft.com/en-us/library/cc966545.aspx
Para obtener más información sobre los archivos de base de datos tempdb y Trace Tlag 1118, visita el siguiente sitio web de MSDN:
Para obtener más información sobre cómo usar la marca de seguimiento 1118 en SQL Server 2005 y SQL Server 2008, visita el siguiente sitio web de MSDN:
Para obtener más información sobre cómo supervisar y solucionar cuellos de botella de asignación en la base de datos tempdb, visite el siguiente sitio web de MSDN: