Operaciones de carga masiva con desencadenadores habilitados

En SQL Server 2005, las optimizaciones de la importación masiva están disponibles si los desencadenadores están habilitados. SQL Server 2005 utiliza el control de 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.

Optimizar el rendimiento de la importación masiva

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 consultas de una expresión de tabla común utiliza un operador de cola para guardar los resultados intermedios de la consulta, Database Engine (Motor de base de datos) crea una tabla de trabajo en tempdb para admitir esta operación.

Usar expresiones de tabla comunes

WITH common_table_expression (Transact-SQL)

Cursores

Los cursores controlados por conjunto de claves y los cursores estáticos utilizan tablas de trabajo que están integradas en tempdb. Los cursores controlados por conjunto de claves 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 consultas que se elija. Si el plan de consultas es el mismo que en versiones anteriores de SQL Server, el uso del espacio en disco es aproximadamente el mismo.

Elegir un tipo de cursor

Correo electrónico de base de datos

Vea la sección "Service Broker" más abajo en esta tabla.

Correo electrónico de base de datos

DBCC CHECKDB

DBCC CHECKDB utiliza las tablas de trabajo de tempdb para almacenar los resultados de orden intermedio y para operaciones de ordenación.

El uso del espacio en disco de DBCC CHECKDB se incrementa debido a los siguientes cambios en la operación DBCC CHECK:

• Las comprobaciones lógicas que se incluyen son más exhaustivas.
  
• Algunas de las estructuras subyacentes que hay que comprobar son más complejas.
  
• Se han agregado muchas comprobaciones nuevas para incluir las nuevas características de SQL Server 2005.
  

Para determinar los requisitos de espacio en disco de tempdb para la operación, ejecute DBCC CHECKDB WITH ESTIMATE_ONLY.

DBCC CHECKDB (Transact-SQL)

Optimizar el rendimiento de DBCC CHECKDB

Notificaciones de eventos

Vea la sección "Service Broker" más abajo en esta tabla.

Conceptos básicos de las notificaciones de eventos

Índices

Cuando crea o vuelve a generar un índice (con conexión o sin conexión) y establece la opción SORT_IN_TEMPDB en ON, le está indicando a Database Engine (Motor de base de datos) que utilice tempdb para almacenar los resultados de orden intermedio que se utilizan para generar el índice. Cuando se especifica SORT_IN_TEMPDB y se requiere la ordenación, tempdb debe disponer de espacio en disco suficiente para incluir el índice más grande, más 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.

El espacio en disco necesario para operaciones de índice sin conexión que no utilizan características de SQL Server 2005 es el mismo que en versiones anteriores de SQL Server.

Cambios en el uso de tempdb en SQL Server 2005

En SQL Server 2005, se pueden crear particiones en las tablas y en los índices. 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 vuelven a generar un índice agrupado también necesitan espacio en disco adicional para generar y mantener un índice de asignación temporal. 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

Ejemplo de espacio en disco del índice

Cómo funcionan las operaciones de índice en línea

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 utilizar 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 transacciones 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.

Usar tipos de datos de valores grandes

Conjuntos de resultados activos múltiples (MARS)

En SQL Server 2005, 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.

Using Multiple Active Result Sets (MARS)

Notificaciones de consultas

Vea la sección "Service Broker" más abajo en esta tabla.

Usar notificaciones de consulta

Consultas

Las consultas que contienen instrucciones SELECT, INSERT, UPDATE y DELETE pueden utilizar objetos internos para almacenar los resultados intermedios de las combinaciones hash, agregados hash u operaciones de orden.

En SQL Server 2005, 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, Database Engine (Motor de base de datos) puede quitar el plan de ejecución y las tablas de trabajo asociadas.

Almacenar en caché y volver a utilizar un plan de ejecución

Control de versiones de fila

El control de versiones de fila es un marco general de SQL Server 2005 que se utiliza con las características siguientes:

• Desencadenadores
  
• Conjuntos de resultados activos múltiples (MARS)
  
• Operaciones de índice que especifican la opción ONLINE
  
• Niveles de aislamiento de transacciones basado en el control de versiones de fila:
  
  • Una nueva implementación del nivel de aislamiento de lectura confirmada que utiliza el control de versiones de fila para proporcionar una coherencia de lectura en las instrucciones.
    
  • Un nivel de aislamiento de instantánea que proporciona una coherencia de lectura en las transacciones.
    

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 utilizar 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 libre en Tempdb (KB) y Tamaño de almacén de versiones (KB) del objeto Transactions se pueden utilizar 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.

Ésta es una fórmula básica:

[Tamaño de almacén de versiones] = 2 *

[Datos del almacén de versiones generados por minuto] *

[Tiempo más largo (minutos) de ejecución de la transacción]

Descripción de los niveles de aislamiento basado en el control de versiones de filas

Uso de recursos del control de versiones de filas

Service Broker

Service Broker ayuda a los programadores 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 utiliza 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 utiliza 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.

Introducción a Service Broker

Procedimientos almacenados

Los procedimientos almacenados pueden crear objetos de usuario como tablas temporales locales o globales, y sus índices, variables o parámetros. En SQL Server 2005, los objetos temporales de los procedimientos almacenados se pueden almacenar en caché para optimizar las operaciones que quitan y crean estos 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.

Crear procedimientos almacenados (motor de base de datos)

Tablas temporales y variables table

• Índices y tablas definidos por el usuario
  
• Índices y tablas del sistema
  
• Índices y tablas temporales globales
  
• Índices y tablas temporales locales
  
• Variables table
  
• Tablas devueltas en funciones con valores de tabla
  

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 utiliza 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.

En SQL Server 2005, las tablas temporales locales y las variables se almacenan en caché cuando se cumplen las siguientes condiciones:

• No se han creado restricciones con nombre.
  
• Las instrucciones del lenguaje de definición de datos (DDL) que afectan a la tabla no se ejecutan después de la creación de la tabla temporal, por ejemplo, las instrucciones CREATE INDEX o CREATE STATISTICS.
  
• El objeto temporal no se crea mediante SQL dinámico, como:
  
• El objeto temporal se crea dentro de otro objeto, como un procedimiento almacenado, desencadenador o función definida por el usuario, o bien es la tabla devuelta de una función con valores de tabla definida por el usuario.
  

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 páginas por tabla temp

* número promedio de tablas temp por procedimiento

* número máximo de ejecuciones simultáneas del procedimiento

CREATE TABLE (Transact-SQL)

Usar variables y parámetros (motor de base de datos)

DECLARE @local_variable (Transact-SQL)

Desencadenadores

En versiones anteriores de SQL Server, la lógica del desencadenador se basaba en entradas del registro y tempdb no se utilizaba. En SQL Server 2005, 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.

CREATE TRIGGER (Transact-SQL)

Optimizar el rendimiento de la importación masiva

Uso de recursos del control de versiones de filas

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.

En SQL Server 2005, los tipos de datos que están permitidos para parámetros y valores devueltos en funciones escalares y funciones con valores de tabla incluyen 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.

En SQL Server 2005, los objetos temporales de funciones con valores de tabla definidas por el usuario se pueden almacenar en caché para optimizar las operaciones que quitan y crean estos objetos. Vea "Tablas temporales y variables table" anteriormente en esta tabla.

CREATE FUNCTION (Transact-SQL)

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.

Tipo de datos xml

sp_xml_preparedocument (Transact-SQL)

Consultar XML con OPENXML