Planeamiento de capacidad para tempdb
En este tema se proporcionan directrices para determinar la cantidad adecuada de espacio en disco que necesita tempdb. En este tema también se incluyen recomendaciones sobre el modo de configurar tempdb para obtener un rendimiento óptimo en un entorno de producción, así como información acerca de cómo supervisar el uso del espacio de tempdb.
Cómo se usa tempdb
La base de datos del sistema tempdb es un recurso global a disposición de todos los usuarios conectados a una instancia de SQL Server. La base de datos tempdb se usa para almacenar los objetos siguientes: objetos de usuarios, objetos internos y almacenes de versiones.
Objetos de usuario
Los objetos de usuario los crea el usuario de forma explícita. Estos objetos pueden estar en el ámbito de una sesión de usuario o en el ámbito de la rutina en la que se crea el objeto. Una rutina es un procedimiento almacenado, un desencadenador o una función definida por el usuario. Los objetos de usuario pueden ser alguno de los siguientes:
Índices y tablas definidos por el usuario
Índices y tablas del sistema
Índices y tablas temporales globales
Índices y tablas temporales locales
Variables de tabla
Tablas devueltas en funciones con valores de tabla
Objetos internos
Los objetos internos los crea SQL Server Database Engine (Motor de base de datos de SQL Server) cuando son necesarios para procesar instrucciones SQL Server. Los objetos internos se crean y se quitan dentro del ámbito de una instrucción. Los objetos internos pueden ser alguno de los siguientes:
Tablas de trabajo para operaciones de cola (Spool) o cursor y almacenamiento de objetos grandes (LOB) temporales.
Archivos de trabajo para operaciones de combinación hash o de agregado hash.
Resultados de orden intermedio de operaciones como crear o volver a generar índices (si se ha especificado SORT_IN_TEMPDB), o algunas consultas GROUP BY, ORDER BY o UNION.
Cada objeto interno usa un mínimo de nueve páginas, una página IAM y una extensión de ocho páginas. Para obtener más información acerca de las páginas y las extensiones, vea Descripción de páginas y extensiones.
Almacenes de versiones
Un almacén de versiones es una colección de páginas de datos que contiene las filas de datos que son necesarias para admitir las características que utilizan el control de versiones de fila. Hay dos almacenes de versiones: un almacén de versiones común y otro de generación de índices en línea. Los almacenes de versiones pueden contener lo siguiente:
Versiones de fila generadas por transacciones de modificación de datos en una base de datos que usa instantáneas o elementos lectura confirmada utilizando niveles de aislamiento de versiones de filas.
Las versiones de fila que se generan mediante transacciones de modificación de datos para características como operaciones de índice en línea, conjuntos de resultados activos múltiples (MARS) y desencadenadores AFTER.
En la siguiente tabla se enumeran las características de SQL Server que crean objetos de usuario, objetos internos o versiones de fila en tempdb. Siempre que sea posible, se proporcionarán métodos para calcular el espacio en disco.
Característica |
Uso de tempdb |
Información adicional |
|---|---|---|
Operaciones de carga masiva con desencadenadores habilitados |
Las optimizaciones de la importación en bloque están disponibles si los desencadenadores están habilitados. SQL Server usa versiones de fila para los desencadenadores que actualizan o eliminan transacciones. Una copia de cada fila eliminada o actualizada se agrega al almacén de versiones. Vea la sección "Desencadenadores" más abajo en esta tabla. |
|
Consultas de expresiones de tabla comunes |
Una expresión de tabla común se puede considerar un conjunto de resultados temporal que se define en el ámbito de la ejecución de una instrucción única SELECT, INSERT, UPDATE, DELETE o CREATE VIEW. Cuando el plan de consulta de una expresión de tabla común usa un operador de cola para guardar los resultados intermedios de la consulta, Motor de base de datos crea una tabla de trabajo en tempdb para admitir esta operación. |
|
Cursores |
Los cursores controlados por conjuntos de claves y estáticos utilizan tablas de trabajo que están integradas en tempdb. Los cursores dinámicos utilizan las tablas de trabajo para almacenar el conjunto de claves que identifican las filas en el cursor. Los cursores estáticos utilizan la tabla de trabajo para almacenar el conjunto completo de resultados del cursor. El uso del espacio en disco de los cursores puede variar en función del plan de consulta que se elija. Si el plan de consulta es el mismo que en versiones anteriores de SQL Server, el uso del espacio en disco es aproximadamente el mismo. |
|
Correo electrónico de base de datos |
Vea la sección "Service Broker" más abajo en esta tabla. |
|
DBCC CHECKDB |
DBCC CHECKDB usa las tablas de trabajo de tempdb para almacenar los resultados de orden intermedio y para operaciones de ordenación. Para determinar los requisitos de espacio en disco de tempdb para la operación, ejecute DBCC CHECKDB WITH ESTIMATEONLY. |
|
Notificaciones de eventos |
Vea la sección "Service Broker" más abajo en esta tabla. |
|
Índices |
Cuando crea o vuelve a generar un índice (en línea o sin conexión) y establece la opción SORT_IN_TEMPDB en ON, le está indicando a Motor de base de datos que use tempdb para almacenar los resultados de orden intermedio que se utilizan para generar el índice. Si se especifica SORT_IN_TEMPDB y la ordenación es necesaria, tempdb debe disponer de espacio en disco suficiente para incluir el índice más grande, así como espacio en disco, que sea igual al valor de la opción index create memory. Para obtener más información, vea Ejemplo de espacio en disco del índice. Las tablas y los índices pueden tener particiones. Para los índices con particiones, si la opción de índice SORT_IN_TEMPDB está especificada y el índice está alineado con la tabla base, debe haber suficiente espacio disponible en tempdb para almacenar las ordenaciones intermedias de la partición más grande. Si el índice no está alineado, debe haber suficiente espacio en tempdb para almacenar las ordenaciones intermedias de todas las particiones. Para obtener más información, vea Directrices especiales para índices con particiones. Las operaciones de índice en línea utilizan el control de versiones de fila para aislar la operación de índice de los efectos de las modificaciones efectuadas por otras transacciones. El control de versiones de fila evita la necesidad de solicitar que se compartan bloqueos en filas que se han leído. Las operaciones simultáneas de eliminación y actualización de usuarios durante las operaciones de índice en línea requieren espacio para registros de versión en tempdb. Cuando las operaciones de índice en línea utilizan SORT_IN_TEMPDB y se requiere la ordenación, tempdb también debe disponer del espacio en disco adicional que se ha descrito anteriormente para los resultados de orden intermedio. Las operaciones de índice en línea que crean, quitan o recompilan un índice clúster también necesitan espacio en disco adicional para compilar y mantener un índice de asignación temporal. Las operaciones CREATE y UPDATE STATISTICS pueden usar tempdb para ordenar el ejemplo de filas con fines de compilación de estadísticas. Para obtener más información, vea Requisitos de espacio en disco para operaciones DDL de índice. |
tempdb y la creación de índices Directrices especiales para índices con particiones Requisitos de espacio en disco para operaciones DDL de índice |
Parámetros y variables para tipos de datos de objetos grandes (LOB) |
Los tipos de datos de objetos grandes son varchar(max), nvarchar(max), varbinary(max)text, ntext, image y xml. Estos tipos pueden tener un tamaño máximo de 2 GB y se pueden usar como variables o parámetros en procedimientos almacenados, funciones definidas por el usuario, lotes o consultas. Los parámetros y variables que están definidos como tipos de datos LOB utilizan la memoria principal como almacén si los valores son pequeños. Sin embargo, los valores grandes se almacenan en tempdb. Cuando los parámetros y variables LOB se almacenan en tempdb, se tratan como objetos internos. Puede realizar una consulta a la vista de administración dinámica sys.dm_db_session_space_usage para que informe de las páginas asignadas a los objetos internos para una sesión determinada. Algunas funciones de cadena intrínsecas, como SUBSTRING o REPLICATE, pueden requerir el almacenamiento temporal intermedio en tempdb cuando están trabajando en valores LOB. De manera similar, cuando está habilitado un nivel de aislamiento de transacción basado en el control de versiones de fila en la base de datos y se realizan modificaciones en objetos grandes, el fragmento cambiado del LOB se copia en el almacén de versiones de tempdb. |
|
Conjuntos de resultados activos múltiples (MARS) |
Se pueden producir conjuntos de resultados activos múltiples en una única conexión; esto es lo que normalmente se denomina MARS. Si una sesión MARS emite una instrucción de modificación de datos (como INSERT, UPDATE o DELETE) cuando hay un conjunto de resultados activos, las filas afectadas por la instrucción de modificación se almacenan en el almacén de versiones de tempdb. Vea la sección "Control de versiones de fila" más abajo en esta tabla. |
|
Notificaciones de consultas |
Vea la sección "Service Broker" más abajo en esta tabla. |
|
Consultas |
Las consultas que contienen instrucciones SELECT, INSERT, UPDATE y DELETE pueden usar objetos internos para almacenar los resultados intermedios de las combinaciones hash, agregados hash u operaciones de orden. Cuando un plan de ejecución de consultas se almacena en caché, las tablas de trabajo que requiere el plan se almacenan en caché. Cuando una tabla de trabajo se almacena en caché, la tabla se trunca y nueve páginas permanecen en la caché para su reutilización. De esta forma, se mejora el rendimiento de la siguiente ejecución de la consulta. Si el sistema tiene poca memoria, Motor de base de datos puede quitar el plan de ejecución y las tablas de trabajo asociadas. |
|
Control de versiones de fila |
El control de versiones de fila es un marco general que se usa con las características siguientes:
El control de versiones de fila se conserva en el almacén de versiones de tempdb mientras una transacción activa necesite tener acceso al mismo. El contenido del almacén de versiones actual se devuelve en sys.dm_tran_version_store. El seguimiento de las páginas del almacén de versiones se realiza en el archivo porque son recursos globales. Puede usar la columna version_store_reserved_page_count en sys.dm_db_file_space_usage para ver el tamaño real del almacén de versiones. Debe tenerse en cuenta una limpieza del almacén de versiones cuando se tengan que ejecutar transacciones prolongadas que requieran el acceso a una versión determinada. Las transacciones de ejecución prolongada relacionadas con la limpieza del almacén de versiones se pueden descubrir comprobando la columna elapsed_time_seconds en sys.dm_tran_active_snapshot_database_transactions. Los contadores Espacio disponible en Tempdb (KB) y Tamaño de almacén de versiones (KB) del objeto Transactions se pueden usar para supervisar el tamaño y la tasa de crecimiento del almacén de versiones de filas en tempdb. Para obtener más información, vea Transactions (objeto de SQL Server). Para estimar la cantidad de espacio que se requiere en tempdb para el control de versiones de fila, tiene que tener en cuenta primero que una transacción activa debe mantener todos sus cambios en el almacén de versiones. Esto significa que una transacción de instantáneas que se inicie más tarde podrá tener acceso a las versiones anteriores. Además, si hay una transacción de instantánea activa, todos los datos del almacén de versiones generados mediante las transacciones que estuviesen activas cuando la instantánea se inicia también se deben mantener. Esta es una fórmula básica: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction] |
Descripción de los niveles de aislamiento basado en el control de versiones de filas |
Service Broker |
Service Broker ayuda a los desarrolladores de software a crear aplicaciones asincrónicas de acoplamiento flexible en las que los componentes independientes funcionan conjuntamente para llevar a cabo una tarea. Estos componentes de aplicación intercambian mensajes que contienen la información necesaria para finalizar la tarea. Service Broker usa de forma explícita tempdb para conservar el contexto de cuadro de diálogo existente que no puede permanecer en la memoria. El tamaño es aproximadamente 1 KB por cuadro de diálogo. Además, Service Broker usa de forma implícita tempdb a través del almacenamiento de objetos en caché en el contexto de la ejecución de consultas, como las tablas de trabajo utilizadas para eventos de temporizador y conversaciones entregadas en segundo plano. El Correo electrónico de base de datos, las notificaciones de eventos y las notificaciones de consulta utilizan Service Broker de forma implícita. |
|
Procedimientos almacenados |
Los procedimientos almacenados pueden crear objetos de usuario como tablas temporales locales o globales, y sus índices, variables o parámetros. Los objetos temporales de los procedimientos almacenados se pueden almacenar en caché para optimizar las operaciones que eliminan y crean esos objetos. Este comportamiento puede incrementar los requisitos de espacio en disco de tempdb. Se almacenan hasta nueva páginas por objeto temporal para su reutilización. Vea "Tablas temporales y variables table" más abajo en esta tabla. |
|
Tablas temporales y variables table
|
Las tablas temporales y las variables table se almacenan en tempdb. Los requisitos de espacio en disco para objetos de tabla temporales son los mismos que en versiones anteriores de SQL Server. El método para estimar el tamaño de una tabla temporal es el mismo que el que se emplea para estimar el tamaño de una tabla estándar. Para obtener más información, vea Calcular el tamaño de una tabla. Una variable table se comporta como una variable local. Una variable table es de tipo table y se usa principalmente para el almacenamiento temporal de un conjunto de filas devuelto como el conjunto de resultados de una función con valores de tabla. La cantidad de espacio en disco que se requiere para incluir una variable table depende del tamaño de la variable declarada y del valor almacenado en la variable. Las tablas temporales locales y las variables se almacenan en caché cuando se cumplen las siguientes condiciones:
Cuando una tabla temporal o variable table se almacena en caché, el objeto temporal no se elimina cuando alcanza su objetivo. En lugar de eso, el objeto temporal se trunca. Hasta un máximo de nueve páginas se almacenan y se reutilizan la siguiente vez que se ejecuta el objeto de llamada. El almacenamiento en caché permite que las operaciones que quitan y crean los objetos se ejecuten rápidamente y reduce la contención de asignación de páginas. Con el fin de obtener un rendimiento óptimo, debe calcular el espacio en disco necesario para almacenar en caché las tablas temporales locales o las variables table en tempdb mediante la siguiente fórmula: 9 page per temp table * number of average temp tables per procedure * number of maximum simultaneous executions of the procedure |
|
Desencadenadores |
Las tablas inserted y deleted que se utilizan en desencadenadores AFTER se crean en tempdb. Es decir, se crean versiones de las filas actualizadas o eliminadas por el desencadenador. Esto incluye todas las filas modificadas por la instrucción que ha activado el desencadenador. No se crean versiones de las filas que inserta el desencadenador. Los desencadenadores INSTEAD OF utilizan tempdb de una manera similar a las consultas. El uso del espacio en disco de los desencadenadores INSTEAD OF es el mismo que en versiones anteriores de SQL Server. Vea "Consultas" anteriormente en esta tabla. Al cargar datos de manera masiva con los desencadenadores habilitados, se agrega una copia de cada fila eliminada o actualizada al almacén de versiones. |
|
Funciones definidas por el usuario |
Las funciones definidas por el usuario pueden crear objetos de usuario temporales, como tablas locales o globales, y sus índices, variables o parámetros. Por ejemplo, la tabla devuelta de una función con valores de tabla se almacena en tempdb. Los tipos de datos permitidos para parámetros y los valores devueltos en funciones escalares y funciones con valores de tabla contienen la mayoría de los tipos de datos LOB. Por ejemplo, un valor devuelto puede ser de tipo xml o varchar(max). Vea "Parámetros y variables para tipos de datos de objetos grandes (LOB)" anteriormente en esta tabla. Los objetos temporales de funciones con valores de tabla definidas por el usuario se pueden almacenar en caché para optimizar las operaciones que eliminan y crean estos objetos. Vea "Tablas temporales y variables table" anteriormente en esta tabla. |
|
XML |
Las variables y los parámetros de tipo xml pueden tener hasta 2 GB. Utilizan la memoria principal como almacén si los valores son pequeños. Sin embargo, los valores grandes se almacenan en tempdb. Vea "Parámetros y variables para tipos de datos de objetos grandes (LOB)" anteriormente en esta tabla. El procedimiento almacenado del sistema sp_xml_preparedocument crea una tabla de trabajo en tempdb. El analizador MSXML usa la tabla de trabajo para almacenar el documento XML analizado. Los requisitos de espacio en disco de tempdb son casi proporcionales al tamaño del documento XML especificado cuando se ejecuta el procedimiento almacenado. |
Planear la capacidad para actualizaciones a SQL Server
Determinar el tamaño adecuado para tempdb en un entorno de producción depende de muchos factores. Como se ha descrito en el tema anterior, estos factores incluyen la carga de trabajo existente y las características de SQL Server que se utilizan. Se recomienda analizar la carga de trabajo existente llevando a cabo las siguientes tareas en un entorno de prueba de SQL Server:
Establezca el crecimiento automático en ON para tempdb.
Ejecute consultas individuales o archivos de seguimiento de carga de trabajo y supervise el uso del espacio de tempdb.
Ejecute operaciones de mantenimiento de índice, como volver a generar índices, y supervise el espacio de tempdb.
Use los valores de uso de espacio de los pasos anteriores para predecir el uso de carga de trabajo total; ajuste este valor para la actividad simultánea proyectada y, a continuación, establezca el tamaño de tempdb según corresponda.
Para obtener más información acerca de cómo supervisar el espacio de tempdb, vea Solucionar problemas de espacio en disco insuficiente en tempdb. Para obtener más información acerca de cómo calcular el uso de tempdb durante las operaciones de índice, vea Ejemplo de espacio en disco del índice.
Configurar tempdb para entornos de producción
Para lograr un rendimiento óptimo de tempdb, siga las directrices y recomendaciones que se proporcionan en Optimizar el rendimiento de tempdb.
Cómo supervisar el uso de tempdb
Si se agota el espacio de disco de tempdb, se pueden producir interrupciones importantes en el entorno de producción de SQL Server y es posible que las aplicaciones en ejecución no puedan finalizar sus operaciones. Puede usar la vista de administración dinámica sys.dm_db_file_space_usage para supervisar el espacio en disco que utilizan estas características en los archivos de tempdb. Asimismo, para supervisar la actividad de asignación o desasignación de páginas en tempdb en la sesión o tarea, se pueden usar las vistas de administración dinámica sys.dm_db_session_space_usage y sys.dm_db_task_space_usage. Estas vistas pueden utilizarse para identificar consultas grandes, tablas temporales o variables de tabla que utilizan mucho espacio de disco de tempdb. También hay muchos contadores que se pueden usar para supervisar el espacio disponible que hay disponible en tempdb, así como los recursos que están utilizando tempdb. Para obtener más información, vea Solucionar problemas de espacio en disco insuficiente en tempdb.
Vea también