En este artículo, discutiremos las mejores prácticas que se deben seguir para mantener la base de datos TempDB en un estado saludable y evitar cualquier crecimiento inesperado de la base de datos, además del procedimiento que se puede seguir para detectar este crecimiento inesperado una vez que se produjo.
Descripción general de TempDB
La base de datos TempDB es una de las bases de datos del sistema SQL Server más importantes, que se utiliza para almacenar objetos temporales de usuario, como las tablas temporales definidas por el usuario o devueltas de la ejecución de funciones con valores de tabla, procedimientos almacenados temporales, variables de tabla o índices.
Además de los objetos de usuario, TempDB se utilizará para almacenar objetos internos creados por el motor de base de datos de SQL Server durante las diferentes operaciones internas, como la clasificación intermedia, la cola, el agregado o las operaciones de cursor.
La base de datos del sistema TempDB también se utiliza para almacenar las versiones de filas con el fin de admitir las características que requieren el seguimiento de los cambios que se realizan en las filas de la tabla, como el nivel de aislamiento de instantáneas de las transacciones, las reconstrucciones de índices en línea o la función de Conjuntos de resultados Múltiples Activos.
La base de datos TempDB se eliminará y se volverá a crear cada vez que se reinicie el servicio SQL Server, comenzando con una nueva copia limpia de la base de datos. En base a este hecho, todos los usuarios y bases de datos internas objetadas que se almacenan en esta base de datos se eliminarán automáticamente cuando se reinicie el servicio SQL Server o cuando se desconecte la sesión en la que se crearon estos objetos. Por lo tanto, las operaciones de copia de seguridad y restauración no están disponibles para TempDB.
Por qué TempDB
La base de datos del sistema TempDB juega un papel importante en el proceso de ajuste de rendimiento de SQL Server. Porque se utiliza como almacenamiento en caché para almacenar diferentes tipos de objetos de base de datos de usuarios y para almacenar los objetos internos del sistema con el fin de acelerar los procesos relacionados con el motor de base de datos de SQL Server.
Debido a este papel vital que desempeña la base de datos del sistema TempDB en la mejora del rendimiento general de las instancias de SQL Server, es muy importante aplicar las prácticas recomendadas de tamaño inicial, crecimiento automático y ubicación en los datos y archivos de registro de la base de datos TempDB. La aplicación de estas prácticas recomendadas de una manera que se ajuste a la carga de trabajo de su instancia de SQL Server evitará expandir los datos de la base de datos TempDB y los archivos de registro con mucha frecuencia, teniendo en cuenta que el proceso de expansión de archivos es un proceso costoso en el que SQL Server Engine solicitará espacio adicional al sistema operativo, que realizará una puesta a cero en ese espacio antes de asignarlo a SQL Server Engine. Puede imaginar el tiempo y los recursos necesarios para este proceso de asignación.
Si ya aplica todas las prácticas recomendadas y se ha producido una operación de crecimiento inesperada, puede acelerar el proceso de asignación utilizando la opción de inicialización instantánea de archivos de base de datos, en la que el sistema operativo confiará en el servicio SQL Server y asignará el espacio necesario sin realizar la operación de desperdicio de tiempo y recursos en el espacio asignado.
Prácticas recomendadas
Existen varias prácticas recomendadas de configuración de la base de datos del sistema TempDB que se recomiendan para obtener el mejor rendimiento de ese recurso compartido y proteger su instancia de SQL Server de cualquier crecimiento inesperado en los datos de la base de datos TempDB o los archivos de registro que pueden llenar el espacio de disco subyacente.
Estas configuraciones incluyen:
- El número de archivos de datos TempDB debe coincidir con el número de procesadores lógicos, hasta ocho archivos, en la máquina donde está instalada la instancia de SQL Server. Si el número de procesadores lógicos en esa máquina es mayor que ocho, establezca el número de archivos de datos en ocho, con la capacidad de extenderlo por múltiplo de 4 en caso de que tenga un problema de contención de TempDB
- Asegúrese de que los archivos TempDB tengan el mismo tamaño dentro del mismo grupo de archivos para proporcionar el mayor grado posible de eficiencia de operaciones paralelas, debido al mecanismo de escritura de relleno Proporcional que se utiliza para escribir los datos dentro de los archivos de datos. Para obtener más información, consulte Comprensión del algoritmo de relleno proporcional de SQL Server
- Configure los datos de la base de datos TempDB y los archivos de registro con un tamaño inicial razonable y cantidades de crecimiento automático que se ajusten a la cantidad esperada de datos que se escribirán en estos archivos, y los diferentes tipos de cargas de trabajo realizadas en su instancia de SQL Server, incluidas las actividades de usuario y las operaciones de mantenimiento de índices, para evitar los pequeños incrementos frecuentes que afectan al rendimiento general de la instancia
- Cuellos de botella de E/S
- Localice TempDB en una unidad de disco separada, lejos de las bases de datos de usuarios y otras bases de datos del sistema. De esta manera, las pesadas operaciones de lectura y escritura realizadas en TempDB no afectarán ni se verán afectadas por la carga de trabajo de las bases de datos de usuarios, mejorando el rendimiento general de la instancia de SQL Server
Para obtener más información sobre las nuevas mejoras en el rendimiento de TempDB, consulte el documento de la base de datos TempDB.
Dónde aplicar
Antes de la versión de SQL Server 2016, la asignación de tamaño TempDB se puede realizar después de instalar la instancia de SQL Server, desde la página Propiedades de la base de datos. Donde puede controlar el número de archivos de datos de base de datos que se ajustan a la instancia de SQL Server actual, el tamaño inicial de cada archivo de registro y datos de base de datos, el valor adecuado para la opción Autocrowth y la ubicación de los archivos de registro y datos de base de datos.
Para realizar estos cambios, expanda el nodo de bases de datos en SQL Server Management Studio, expanda el nodo de bases de datos del sistema, haga clic con el botón derecho en la base de datos tempdb y elija la opción Propiedades de la siguiente manera:
Desde la ventana de Propiedades de la base de datos abierta, navegue por la página Archivos y verifique o cambie las diferentes configuraciones de datos de la base de datos y archivos de registro que se muestran a continuación:
A partir de la versión de SQL Server 2016, todos estos cambios, con las prácticas recomendadas que se ajustan a los recursos actuales del servidor ya sugeridos por el asistente de instalación, se pueden verificar y realizar durante el proceso de instalación de la instancia de SQL Server, utilizando el Asistente de instalación de SQL Server, en Configuraciones del motor de base de datos | TempDB, como se muestra a continuación:
Rastrear el crecimiento de TempDB
El seguimiento del crecimiento de los archivos de base de datos TemDB y las consultas que consumen los recursos TempDB se puede realizar mediante los contadores de monitor de rendimiento asociados a los archivos TempDB y la herramienta Generador de perfiles SQL que escucha un tipo de carga de trabajo de base de datos específico.
Una forma más amigable para el administrador de bases de datos de rastrear el crecimiento de los archivos de base de datos TempDB es consultar las siguientes Vistas de administración dinámica:
- sys.dm_db_file_space_usage que devuelve la información de uso de espacio para cada archivo de la base de datos, sin mostrar qué sesión o tarea consumió ese espacio
- sys.dm_db_session_space_usage que devuelve el número de páginas asignadas y desasignadas por cada sesión
- sys.dm_db_task_space_usage que devuelve el número de páginas asignadas y desasignadas por cada tarea
Tenga en cuenta que el resultado devuelto por el sistema.dm_db_session_space_usage DMV resumió el resultado devuelto por el sistema.dm_db_task_space_usage DMV cuando todas las tareas se completan con éxito, debido al hecho de que se realizarán múltiples tareas relacionadas con la misma consulta en la misma sesión. Otra ventaja de estos DMV es que mostrarán el tipo de objeto que consume los recursos TempDB y si este objeto es un objeto de usuario, como una tabla temporal o variable o un objeto interno, como mantenimiento de índices u operación de ordenación. Por lo tanto, en función del número de páginas consumidas por cada objeto, puede especificar si TempDB es consumido por un objeto interno o de usuario y optimizar ese consumidor.
sys. dm_db_file_space_usage
El DMV sys.dm_db_file_space_usage se puede usar para monitorear el espacio en disco utilizado en los archivos de la base de datos TempDB. Las consultas a continuación muestran el espacio no asignado, el espacio utilizado para almacenar versiones de filas, el espacio consumido por los objetos internos y, finalmente, el espacio consumido por los objetos de usuario, de la siguiente manera:
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECCIONE (SUM(unallocated_extent_page_count)*1.0/128) COMO TempDB_FreeSpaceAmount_InMB
FROM sys.dm_db_file_space_usage;
SELECCIONAR (SUM(version_store_reserved_page_count)*1.0/128) COMO TempDB_VersionStoreSpaceAmount_Inmb
FROM sys.dm_db_file_space_usage;
SELECCIONAR (SUM(internal_object_reserved_page_count)*1.0/128) COMO TempDB_InternalObjSpaceAmount_InMB
DE sys.dm_db_file_space_usage;
SELECT (SUM(user_object_reserved_page_count)*1.0/128) COMO TempDB_UserObjSpaceAmount_InMB
DE sys.dm_db_file_space_usage;
|
A partir del resultado, que se devuelve desde mi instancia local de SQL Server, puede ver que hay 60 MB sin usar en los archivos TempDB, con solo 0,5 MB utilizados para los objetos internos y 1,5 MB utilizados para los objetos de usuario, como se muestra a continuación:
sys.dm_db_task_space_usage y sys.dm_db_session_space_usage
sys.dm_db_task_space_usage y sys.dm_db_session_space Los DMV se utilizan para comprobar el número de páginas asignadas y desasignadas por cada tarea o sesión en la base de datos TempDB. De esta manera, podrá ver qué usuario o un objeto interno está consumiendo el espacio TempDB.
Por ejemplo, el siguiente resultado se devuelve al consultar el sistema.dm_db_task_space_usage DMV en la base de datos TempDB, donde devolverá el número de páginas asignadas y desasignadas para cada usuario o tarea interna, de la siguiente manera:
Pero el DBA no requerirá tantos detalles para comenzar a rastrear la consulta que está consumiendo los recursos TempDB. Por lo tanto, necesitamos resumir el resultado anterior para cada sesión, utilizando la consulta a continuación:
|
1
2
3
4
5
6
7
8
|
SELECCIONE session_id,
SUM(internal_objects_alloc_page_count) COMO NumOfPagesAllocatedInTempDBforInternaltask,
SUM(internal_objects_dealloc_page_count) COMO NumOfPagesDellocatedInTempDBforInternaltask,
SUM(user_objects_alloc_page_count) COMO NumOfPagesAllocatedInTempDBforUsertask,
SUM(user_objects_dealloc_page_count) COMO NumOfPagesDellocatedInTempDBforUsertask
FROM sys.dm_db_task_space_usage
GRUPO POR session_id
PEDIDO POR NumOfPagesAllocatedInTempDBforInternaltask DESC, NumOfPagesAllocatedInTempDBforUsertask DESC
|
Y el resumen de las páginas de resultado de consumo por cada sesión será como a continuación:
El mismo resultado puede ser devuelto por la consulta de la sys.dm_db_session_space_usage DMW, que resume la asignación y desasignación de páginas para todas las tareas que se ejecutan en la misma sesión, como se muestra a continuación:
Vamos a realizar algunas acciones en la base de datos TempDB utilizando objetos internos y de usuario como se muestra a continuación:
Luego ejecute la consulta consolidada a continuación, que devuelve el número de páginas asignadas y desasignadas por objetos internos y de usuario, y muestra las operaciones completadas (usando sys.dm_db_session_space_usage DMV) y la pendiente de las operaciones (el uso de sys.dm_db_task_space_usage DMV):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECCIONE SpacePerTask.session_id,
SpacePerSession.internal_objects_alloc_page_count
+ SUM (SpacePerTask.internal_objects_alloc_page_count) COMO NumOfPagesAllocatedInTempDBforInternaltask,
SpacePerSession.internal_objects_dealloc_page_count
+ SUMA (Máscara espaciadora.internal_objects_dealloc_page_count) COMO NumOfPagesDellocatedInTempDBforInternaltask,
SpacePerSession.User_objects_alloc_page_count
+ SUM (SpacePerTask.User_objects_alloc_page_count) COMO NumOfPagesAllocatedInTempDBforUsertask,
SpacePerSession.User_objects_dealloc_page_count
+ SUM (SpacePerTask.User_objects_dealloc_page_count) COMO NumOfPagesDellocatedInTempDBforUsertask
FROM sys.dm_db_session_space_usage COMO SpacePerSession
INNER JOIN sys.dm_db_task_space_usage COMO SpacePerTask EN SpacePerSession.session_id = SpacePerTask.session_id
GRUPO POR tarea espacial.session_id, SpacePerSession.internal_objects_alloc_page_count, SpacePerSession.internal_objects_dealloc_page_count, SpacePerSession.User_objects_alloc_page_count, SpacePerSession.User_objects_dealloc_page_count
ORDENAR POR Número de páginas asignadas INTEMPDB para tareas internas DESC, Número de páginas asignadas INTEMPDB para tareas internas DESC
|
Y el resultado de las operaciones anteriores reflejará la salida de la consulta, mostrando el número de páginas asignadas y desasignadas mediante la creación de la tabla temporal del usuario y la ordenación de los datos durante el proceso de reconstrucción del índice, como se muestra a continuación:
Para obtener el texto de la consulta que consume los recursos TempDB, puede usar sus habilidades de desarrollo SQL uniendo el resultado del DMV ys.dm_db_task_space_usage con otros DMV, como sys.dm_exec_requests y sys.dm_exec_sessions con la Función de Administración dinámica sys.dm_exec_sql_text, donde mostrará las consultas que están consumiendo el espacio TempDB ahora, como en esta consulta.
El resultado será similar al siguiente:
- Autor
- Publicaciones recientes
Es Experto en Soluciones Certificadas por Microsoft en Gestión y Análisis de Datos, Asociado en Soluciones Certificadas por Microsoft en Administración y Desarrollo de Bases de Datos SQL, Asociado en Desarrolladores de Azure y Entrenador Certificado por Microsoft.
Además, está contribuyendo con sus consejos SQL en muchos blogs.
Ver todos los mensajes de Ahmad Yaseen
- Preguntas y respuestas de la entrevista de Azure Data Factory-11 de febrero de 2021
- Cómo supervisar Azure Data Factory-15 de enero de 2021
- Usando el control de origen en Azure Data Factory-enero 12, 2021