Tutorial de SQL Server TempDB

De: Eric Blinn | Actualizado: 2020-07-17 | Comentarios (2) / Relacionado: Más > Bases de datos del sistema

Problema

SQL Server viene instalado con cuatro bases de datos del sistema de forma predeterminada. Son maestros, modelos, msdb y TempDB. Necesito saber qué es la base de datos del sistema «TempDB» y para qué se utiliza. Estos tutoriales anteriores cubren master, model y msdb con gran detalle.

Solución

Este consejo cubrirá la base de datos TempDB. TempDB es una base de datos que tiene muchas funciones dentro de SQL Server, pero rara vez se llama explícitamente. Tiene muchas funciones que a menudo es una de las bases de datos más concurridas, si no la más concurrida, en la mayoría de las instancias de SQL Server. ¡Sigue leyendo para conocer muchas de estas funciones!

Descripción general de SQL Server TempDB

¿Cuál es el propósito de SQL Server TempDB?

Una de las funciones de TempDB es actuar como una página o un archivo de intercambio a nivel del sistema operativo. Si una operación de SQL Server es demasiado grande para completarse en memoria o si la concesión de memoria inicial para una consulta es demasiado pequeña, la operación se puede mover al disco en TempDB.

Otra función de TempDB es almacenar tablas temporales. Cualquier persona que haya creado una tabla temporal en T-SQL usando un prefijo de libra o hash ( # ) o el prefijo de doublepound/hash ( # # ) ha creado un objeto en TempDB, ya que aquí es donde se almacenan.

CREATE TABLE #MSSQLTips (RowID int);

Cuando atrigger está ejecutando, las tablas virtuales insertadas y eliminadas se almacenan en TempDB.

Cualquier base de datos que use AISLAMIENTO DE INSTANTÁNEAS CONFIRMADAS por el usuario (RCSI) tendrá la información de control de versiones adicional almacenada en TempDB.

Cada una de las funciones anteriores de TempDB se completan sin mencionar nunca el nombre de Tempdb y, por lo tanto, pueden ser usos sorprendentes para TempDB.

TempDB también se puede llamar explícitamente de varias maneras. Las tablas se pueden generar en TempDB haciendo referencia a la base de datos en una instrucción create. El código se parece exactamente a una operación DDL normal, pero cuando se ejecuta en TempDB, la tabla es,por definición, una tabla temporal.

CREATE TABLE TempDB.dbo.MSSQLTips(RowID int);

TempDB se regenera cada vez que se inicia la instancia de SQL Server. Cualquier objeto que se haya creado en TempDB durante una sesión anterior no persistirá al reiniciar el servicio. TempDB obtiene su lista de objetos inicial de la base de datos de modelos, que generalmente va a estar vacía o casi vacía.

Se puede pedir opcionalmente al mantenimiento de índices que haga ordenamientos en TempDB en lugar de intentar hacerlo utilizando espacio libre en la base de datos de usuarios.

ALTER INDEX ON . REBUILD WITH (SORT_IN_TEMPDB = ON);

¿Puedo ejecutar SQL Server sin una base de datos TempDB?

SQL Server no se puede ejecutar (excepto en escenarios de recuperación muy extremos y por períodos muy cortos) sin TempDB.

¿La versión de la base de datos TempDB es única para la versión, edición y nivel de parche de SQL Server?

TempDB es una base de datos en su mayoría en blanco y no hay nada particularmente único sobre cualquier edición o nivel de parche de SQL Server.

Solo ha habido 2 cambios significativos en TempDB en los últimos años a nivel de versión. La primera es que, a partir de SQL Server 2016, el comportamiento de TEMPDB cambió de tal manera que habilitar los indicadores de seguimiento T1117 y T1118 ya no se considera una práctica recomendada. La segunda es que a partir de SQL Server 2019, algunos objetos de sistema de uso intensivo en TempDB se actualizaron a tablas en memoria para reducir el contenido y mejorar el rendimiento general del servidor.

¿Puede soltar o cambiar el nombre de la base de datos TempDB y debe hacerlo por cualquier motivo?

TempDB no se puede soltar, separar, desconectar ni cambiar de nombre. Intentar cualquiera de estas operaciones devolverá un error. No hay razón para que esto se intente nunca, ya que se trata de una base de datos crítica del sistema.

Ubicación de la base de datos TempDB de SQL Server

¿Qué archivos y nombres físicos admiten la base de datos TempDB?

Los nombres de archivo lógicos predeterminados son tempdev para datos y templog para registro.Se pueden encontrar en el disco como tempdb.mdf y templog.ldf, respectivamente. TEMPDB tiene muchos archivos de datos.

¿Cuántos archivos de datos TempDB deben haber?

Debe haber un archivo de datos TempDB para cada subproceso / núcleo/vCPU en la instancia con un máximo de 8.

¿Dónde puedo encontrar la base de datos TempDB en disco y en SSM?

Los archivos se pueden encontrar consultando sys.sysfiles dmv o el panel de archivos en la ventana databaseproperties.

SELECT * FROM TempDB.sys.sysfiles;

¿Hace una diferencia dónde residen los archivos de base de datos TempDB (necesitan estar en C:\, SAN, etc.)?)?

Las prácticas recomendadas dictan que los archivos de datos TempDB deben estar en el almacenamiento más rápido disponible debido a sus niveles de actividad. Para algunos casos, esto puede significar almacenarlos con los archivos de datos de la base de datos del usuario, pero se prefiere un volumen separado en un disco separado, si está disponible.

Hay diferentes opiniones sobre el archivo de registro TempDB, pero este autor prefiere colocar este archivo con los otros archivos de registro de transacciones.

¿Se puede mover la base de datos TempDB?

La base de datos TempDB se puede mover. El método para mover TempDB es fácil de implementar. Simplemente modifique la propiedad NOMBRE DE ARCHIVO de los archivos deseados. No es necesario mover archivos como cuando se mueven otras bases de datos del sistema. SQLServer creará los archivos con el nuevo nombre y / o en la nueva ubicación al iniciar el próximo servicio. Simplemente no se olvide de volver y eliminar los archivos TempDB no utilizados que se quedarán atrás.

ALTER DATABASE MODIFY FILE ( NAME = N'tempdev', FILENAME = N'G:\DATA\tmp.mdf');GOALTER DATABASE MODIFY FILE ( NAME = N'templog', FILENAME = N'H:\LOG\tmplog.ldf');GO

Tamaño y crecimiento de la base de datos TempDB de SQL Server

¿Cómo deben configurarse los ajustes de crecimiento de la base de datos y del registro de transacciones para la base de datos TempDB?

La configuración de autocrecimiento debe establecerse en números específicos de MB, no en un porcentaje.Cada archivo de datos debe tener el mismo número de MBs establecido para el crecimiento.

El dimensionamiento correcto de los archivos para una base de datos es generalmente una práctica recomendada, pero nunca más que para TempDB. Supervise la instancia, especialmente si es nueva, y determine si los archivos están creciendo durante el funcionamiento normal.Si es así, cambie el tamaño inicial de modo que SQL Server no se vea obligado a hacer crecer los archivos de forma consistente después de cada inicio del servicio.

¿Cuál es el tamaño típico de la base de datos TempDB y cuán grande puede llegar a ser?

No hay una buena respuesta a esta pregunta. Lo que es típico para un caso puede no ser típico para otro. Este autor ha visto casos en los que el TempDB se utiliza basicamente, promediando solo unos pocos MB de espacio usado en un momento dado. Sin embargo, otros están siendo consultados constantemente y utilizan cientos de GB de espacio, incluso en el espacio medido en TB, especialmente durante una ventana de mantenimiento de índice.

Al decidir cuánto espacio dedicar a TempDB en una nueva instancia, hay pocas cosas que se pueden hacer. ¿La nueva instancia está reemplazando a una instancia existente más antigua? Si es así, utilice un seguimiento de perfmon o una captura programada del DMV para determinar el espacio utilizado en TempDB en el transcurso de varios días. Asegúrese de incluir una ventana de mantenimiento en esa captura. Intente asignar suficiente espacio en la nueva instancia para tener en cuenta el mayor espacio utilizado capturado durante el rastreo con margen de maniobra adicional. Luego, ajuste el tamaño correcto de los archivos para que quepan en esa asignación de espacio.

Las 2 consultas a continuación pueden ayudar. El primero tomará una instantánea del tamaño de los datos y archivos de registro junto con el espacio utilizado dentro de los archivos. Esto se puede ejecutar de forma regular para rastrear el uso a lo largo del tiempo. La segunda consulta devolverá el número de veces que el archivo de registro ha crecido desde la última vez que se reinició la instancia.

Si no hay una carga de trabajo existente que pueda usarse como guía y no hay otra guía, tal vez de un proveedor de software, entonces lo único que se puede hacer es asegurarse de que haya espacio en TempDB para dar cuenta de los objetos individuales anticipados más grandes que pueden ordenarse en TempDB durante las ventanas de mantenimiento de índices. A partir de ahí, supervise el uso y ajuste en consecuencia.

USE TempDB;GOSELECT GETUTCDATE() AS SnapshotDateTime, groupid --0 = data, 1 = log, SUM(size/128.) SizeOnDiskInMB, SUM(FILEPROPERTY(name, 'spaceused')/128.) MBUsedWithinFile FROM TempDB.sys.sysfilesGROUP BY groupid; SELECT * FROM sys.dm_os_performance_countersWHERE counter_name = 'Log Growths' AND instance_name = 'tempdb';

Mantenimiento de la base de datos TempDB del servidor

¿Puede reducir la base de datos y el registro de transacciones de la base de datos TempDB?

Técnicamente, sí,pero esta no es una operación que deba completarse.El objetivo siempre debe ser el TempDB del tamaño correcto. Si algún tipo de operación puntual hace que estos archivos crezcan inexplicablemente, volverán a la normalidad al reiniciar nextservice.

¿Debería reconstruir los índices?

TempDB no formará parte de ningún plan de mantenimiento relacionado con el índice. Esto significa que no hay que reconstruir índices, reorganizar o actualizar estadísticas.

¿Debería ejecutar comprobaciones de integridad qué es el código?

Hay diferentes opiniones sobre si se debe verificar la integridad de Tempdb.Este autor expondrá los argumentos a favor y en contra y permitirá que el lector decida.

Contra: Una operación de comprobación de integridad cannotcheck TempDB con la misma totalidad que todas las demás bases de datos.Los datos que existen en TempDB son altamente transitorios, por lo que la operación de comprobación de integridad no encontrará mucha comprobación en ningún momento dado.Por la misma razón, es probable que los datos que se corrompan sean datos activos que causarán una alerta cuando se lean desde el disco mucho antes de que se llame a una operación de comprobación de integridad.

Para: Mientras que una operación de comprobación de integridad no puede comprobar todo en TempDB que comprueba en otras bases de datos que no significa que no tenga valor.TempDB es una base de datos integral y si hay corrupción, es imperativo saberlo para que se puedan tomar medidas correctivas.

El código para ejecutar una comprobación de integridad se encuentra a continuación.

DBCC CHECKDB ('TempDB'); 

Objetos de base de datos TempDB de SQL Server

¿Cuáles son las tablas y procedimientos clave de la base de datos TEMPDB que son importantes para que los profesionales de SQL Server conozcan?

No hay ninguno.Esta base de datos se utiliza para el almacenamiento temporal de objects.It no viene instalado con ninguna mesa o procedimiento.

¿Puedo almacenar mis propios objetos en la base de datos TEMPDB?

Los objetos ciertamente se pueden crear en TempDB, pero no persistirán después del siguiente inicio del servicio SQL Server.

Permisos de base de datos TempDB de SQL Server

¿Quién tiene acceso a la base de datos TempDB y hay diferentes niveles de permisos?

Todos tienen acceso y el mismo acceso a TempDB.

Copia de seguridad de la base de datos TempDB de SQL Server

¿Necesito hacer una copia de seguridad de la base de datos TempDB?

No se puede hacer una copia de seguridad de TempDB. Solo almacena datos transitorios y se vuelve a procesar cada vez que se inicia el servicio para que no haya nada que recuperar en caso de desastre.

Modelo de recuperación de base de datos TempDB de SQL Server

¿Qué TempDB de recuperación debe tener la base de datos TempDB y se puede cambiar el modelo de recuperación?

TempDB está en recuperación SIMPLE y esto no se puede cambiar.

Restaurar la base de datos TempDB de SQL Server

¿Alguna vez es necesario restaurar la base de datos TempDB?

TempDB no se puede restaurar. No debe haber datos ni código en TEMPDB para recuperar. Si TempDB se daña, simplemente elimine los datos y archivos de registro para que se puedan generar nuevos al iniciar el siguiente servicio.

Pasos siguientes

• Este consejo muestra cómo hacer alertas de problemas encontrados en tamaños TempDB y espacio en disco
• Este consejo muestra cómo habilitar la inicialización instantánea de archivos, una característica importante para todos los DBs, pero especialmente para TempDB si no tiene el tamaño adecuado.

Última actualización: 2020-07-17

Sobre el autor

Eric Blinn es el Sr. Arquitecto de datos para el Escudero Patton Boggs. También es autor de SQL y Líder de Grupo Local de PASS.
Ver todos mis consejos

Recursos relacionados

• Más consejos de DBA de SQL Server…