Requisitos del subsistema de E/S de Microsoft SQL Server para la base de datos tempdb

  • Artículo

En este artículo se presentan los requisitos del subsistema de E/S para la base de datos tempdb en SQL Server.

Versión original del producto: Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2012 SQL Server 2014
Número de KB original: 917047

Resumen

Microsoft SQL Server requiere que el subsistema de E/S que se usa para almacenar bases de datos del sistema y de usuario cumpla completamente los requisitos de registro de Write-Ahead (WAL) a través de entidades de seguridad de E/S específicas. Estos requisitos son necesarios para respetar las propiedades ACID de las transacciones: Atomic, Consistent, Isolated y Durable. Los detalles sobre los requisitos de cumplimiento del subsistema de E/S se proporcionan en las siguientes referencias:

conceptos básicos de E/S de SQL Server 2000https://technet.microsoft.com/library/cc966500.aspx

Nota:

Este artículo también se aplica a SQL Server 2005 y versiones posteriores.

La lista siguiente es un resumen rápido de los requisitos:

  • Se debe mantener el orden de escritura.
  • Se debe mantener la coherencia de escritura dependiente.
  • Las escrituras siempre deben protegerse en o en medios estables.
  • Debe producirse una prevención de E/S desgarrada.

El mantenimiento de durabilidad sigue siendo fundamental para todas las demás bases de datos, pero puede ser relajado para la base de datos tempdb. En la tabla siguiente se resumen varios de los requisitos críticos de E/S para SQL Server bases de datos.

Requisito de E/S Breve descripción Sistema o usuario tempdb
Ordenación de escritura

Coherencia de escritura dependiente		 Capacidad del subsistema para mantener el orden correcto de las operaciones de escritura. Esto puede ser especialmente importante para las soluciones de creación de reflejo, los requisitos de coherencia del grupo y SQL Server uso del protocolo WAL. Obligatorio Recomendado
Lectura después de la escritura La capacidad del subsistema para atender las solicitudes de lectura con la imagen de datos más reciente cuando se emite la lectura después de que se complete correctamente cualquier escritura. Obligatorio Obligatorio
Supervivencia a través de una interrupción La capacidad de que los datos permanezcan totalmente intactos (Durable) durante una interrupción, como un reinicio del sistema. Obligatorio No aplicable
Prevención de E/S desgarrada La capacidad del sistema para evitar la división de solicitudes de E/S individuales. Obligatorio Recomendado
Reescritura del sector El sector solo se puede escribir en su totalidad y no se puede volver a escribir debido a una solicitud de escritura en un sector cercano. * Desalentado, solo se permite si es transaccional * Desalentado, solo se permite si es transaccional
Datos protegidos La expectativa de que cuando una solicitud de escritura o una operación FlushFileBuffers se completen correctamente, los datos se han guardado en medios estables. Obligatorio No aplicable
Alineación y tamaño del sector físico SQL Server interroga las ubicaciones de almacenamiento de archivos de registro y datos. Todos los dispositivos deben admitir atributos de sector que permitan SQL Server realizar escrituras en límites alineados con el sector físico y en múltiplos del tamaño del sector. Obligatorio Obligatorio

Las reescrituras del sector transaccional implican operaciones totalmente registradas por el subsistema que permiten que un sector se mueva, reemplace o revierta completamente a la imagen original. Estas reescrituras suelen desalentarse debido a la sobrecarga adicional necesaria para realizar estas acciones. Un ejemplo de esto sería una defragmentation utilidad que está moviendo los datos del archivo. El sector original del archivo no se puede reemplazar por la nueva ubicación del sector hasta que se protejan el nuevo sector y los datos. La reasignación del sector debe producirse de forma transaccional para que cualquier fallo, incluido un fallo de energía, provoque el restablecimiento de los datos originales. Asegúrese de que tiene mecanismos de bloqueo disponibles durante este tipo de proceso para evitar el acceso a datos no válidos, con lo que se mantienen los demás inquilinos de SQL Server E/S.

Supervivencia a través de una interrupción

La base de datos tempdb es un área temporal para SQL Server y se vuelve a generar en cada SQL Server inicio. La inicialización reemplaza cualquier necesidad de que los datos sobreviven a un reinicio.

Operaciones de reescritura del sector transaccional

Para garantizar el éxito de los procesos de recuperación, como la reversión y la recuperación de bloqueo, los registros deben almacenarse correctamente en medios estables antes de almacenar la página de datos y no se pueden volver a escribir sin respetar las propiedades transaccionales. Esto requiere que el subsistema y SQL Server mantengan atributos específicos, como el orden de escritura, las escrituras alineadas con el sector y el tamaño, y otros atributos de seguridad de E/S descritos en los documentos mencionados anteriormente. Para la base de datos tempdb, la recuperación de bloqueos no es necesaria porque la base de datos siempre se inicializa durante SQL Server inicio. Sin embargo, la base de datos tempdb todavía requiere funcionalidades de reversión. Por lo tanto, algunos atributos del protocolo WAL se pueden relajar.

La ubicación de almacenamiento de la base de datos tempdb debe actuar de forma estricta de acuerdo con los protocolos de unidad de disco establecidos. En todos los sentidos, el dispositivo en el que se almacena la base de datos tempdb debe aparecer y actuar como un disco físico que proporcione funcionalidades de lectura después de escritura. Las operaciones de reescritura del sector de transacciones pueden ser un requisito adicional de implementaciones específicas. Por ejemplo, SQL Server no admite modificaciones de base de datos mediante la compresión del sistema de archivos NTFS porque la compresión NTFS puede reescribir sectores del registro que ya se han escrito y considerado protegidos. Un error durante este tipo de reescritura puede hacer que la base de datos no se pueda usar, lo que puede dañar los datos que SQL Server considerar ya seguros.

Nota:

SQL Server compatibilidad o compresión extendidas de 2005 para leer solo bases de datos y grupos de archivos. Consulte los Libros en pantalla de SQL Server 2005 para obtener detalles completos.

Las operaciones de reescritura del sector transaccional son pertinentes para todas las bases de datos SQL Server que incluyen la base de datos tempdb. Una variedad creciente de tecnologías de almacenamiento extendido usan dispositivos y utilidades que pueden reescribir datos que SQL Server considera seguros. Por ejemplo, algunas de las tecnologías emergentes realizan almacenamiento en caché en memoria o compresión de datos. Para evitar daños graves en la base de datos, cualquier reescritura del sector debe tener soporte transaccional completo de forma que, si se produce un error, los datos se revierten a las imágenes del sector anteriores. Esto garantiza que SQL Server nunca se exponga a una interrupción inesperada o a una condición de daños en los datos.

Puede colocar la base de datos tempdb en subsistemas especializados, como discos RAM, estado sólido u otras implementaciones de alta velocidad que no se pueden usar para otras bases de datos. Sin embargo, los factores clave que se presentan en la sección Más información deben tenerse en cuenta al evaluar estas opciones.

Nota:

Los discos locales en entornos clústeres de conmutación por error solo se admiten con implementaciones de estado sólido o de alta velocidad. Esto se debe a que el disco RAM solo se puede crear a través de un destino iSCSI. Además, las características de destino iSCSI y clúster de conmutación por error no se pueden usar en el mismo host.

Más información

Se deben estudiar detenidamente varios factores al evaluar la ubicación de almacenamiento de la base de datos tempdb. Por ejemplo, el uso de la base de datos tempdb implica, pero no se limita a, la superficie de memoria, el plan de consulta y las decisiones de E/S. El ajuste y la implementación adecuados de la base de datos tempdb pueden mejorar la escalabilidad y la capacidad de respuesta de un sistema. En esta sección se describen los factores clave para determinar las necesidades de almacenamiento de la base de datos tempdb.

Subsistemas de alta velocidad

Hay varias implementaciones de subsistemas de alta velocidad disponibles en el mercado que proporcionan SQL Server requisitos de protocolo de subsistema de E/S, pero que no proporcionan durabilidad de los medios.

Importante

Confirme siempre con el proveedor del producto para garantizar el cumplimiento total con SQL Server necesidades de E/S.

Un disco RAM es un ejemplo común de esta implementación. Los discos RAM instalan los controladores necesarios y permiten que parte del disco RAM principal aparezca como y funcione como cualquier unidad de disco que esté conectada al sistema. Todos los subsistemas de E/S deben proporcionar el cumplimiento completo de los requisitos de E/S de SQL Server. Sin embargo, es obvio que un disco RAM no es un medio duradero. Por lo tanto, una implementación como un disco RAM solo se puede usar como ubicación de la base de datos tempdb y no se puede usar para ninguna otra base de datos.

Claves que se deben tener en cuenta antes de la implementación y la implementación

Hay varios puntos a tener en cuenta antes de la implementación de la base de datos tempdb en este tipo de subsistema. En esta sección se usa un disco RAM como base para la discusión, pero se producen resultados similares en otras implementaciones de alta velocidad.

Seguridad de E/S

El cumplimiento de las escrituras del sector de lectura después de escritura y transaccionales es imprescindible. Nunca implemente SQL Server en ningún sistema que no sea totalmente compatible con los requisitos de E/S de SQL Server, ni se corre el riesgo de que se produzcan daños y pérdida de los datos.

Páginas ya almacenadas en caché (caché de RAM doble)

Las tablas temporales son como todas las demás tablas de una base de datos. El grupo de búferes los almacena en caché y se controlan mediante operaciones de escritura diferida. El almacenamiento de páginas de tabla temporales en un disco RAM provoca un almacenamiento en caché de RAM doble, uno en el grupo de búferes y otro en el disco RAM. Esto quita directamente el tamaño total posible del grupo de búferes y, por lo general, reduce el rendimiento de SQL Server.

Entrega de RAM

El disco RAM designa una parte de la RAM principal como su nombre indica. Hay varias implementaciones de discos RAM y memorias caché de archivos basados en RAM disponibles. Algunas también habilitan operaciones de respaldo de E/S físicas. El elemento clave de la memoria caché de archivos basada en RAM es que quita directamente de la memoria física que puede usar SQL Server. Siempre tiene pruebas sólidas de que agregar una memoria caché de archivos basada en RAM mejora el rendimiento de la aplicación y no disminuye el rendimiento de otras consultas o aplicaciones.

Ajuste primero

Una aplicación debe ajustarse para quitar ordenaciones y hash innecesarios y no deseados que podrían provocar el uso de la base de datos tempdb. Muchas veces la adición de un índice puede quitar por completo la necesidad de ordenar o hash en el plan, lo que conduce a un rendimiento óptimo sin necesidad de usar la base de datos tempdb.

Posibles puntos de beneficio

Las ventajas de colocar la base de datos tempdb en un sistema de alta velocidad solo se pueden determinar mediante rigurosas pruebas y medidas de las cargas de trabajo de la aplicación. La carga de trabajo debe estudiarse detenidamente para conocer las características de las que puede beneficiarse la base de datos tempdb, y la seguridad de E/S debe confirmarse antes de la implementación.

Las operaciones de ordenación y hash funcionan junto con los administradores de memoria SQL Server para determinar el tamaño del área temporal en memoria para cada ordenación o operación hash. En cuanto los datos de ordenación o hash superen el área temporal en memoria asignada, los datos se pueden escribir en la base de datos tempdb. Este algoritmo se ha ampliado en SQL Server 2005, lo que reduce los requisitos de uso de la base de datos tempdb con respecto a las versiones anteriores de SQL Server.

Precaución

SQL Server está diseñado para tener en cuenta los niveles de memoria y las actividades de consulta actuales al tomar decisiones del plan de consulta que implican el uso de operaciones de base de datos tempdb. Por lo tanto, las mejoras de rendimiento varían significativamente en función de las cargas de trabajo y el diseño de la aplicación. Se recomienda encarecidamente completar las pruebas con la solución preferida para determinar las posibles ganancias y evaluar los requisitos de seguridad de E/S antes de dicha implementación.

SQL Server usa la base de datos tempdb para controlar varias actividades que implican ordenaciones, hashes, el almacén de versiones de fila y tablas temporales:

  • Las rutinas comunes del grupo de búferes mantienen las tablas temporales para las páginas de datos y, por lo general, no presentan ventajas de rendimiento de las implementaciones de subsistemas especializados.
  • La base de datos tempdb se usa como área temporal para hashes y ordenaciones. Reducir la latencia de E/S para estas operaciones puede ser beneficioso. Sin embargo, sabe que agregar un índice para evitar un hash o una ordenación puede proporcionar una ventaja similar.

Ejecute líneas base con y sin la base de datos tempdb almacenada en el subsistema de alta velocidad para comparar las ventajas. Parte de las pruebas debe incluir consultas en la base de datos de usuario que no impliquen ordenaciones, hashes o tablas temporales y, a continuación, confirmar que estas consultas no se ven afectadas negativamente. Al evaluar el sistema, los siguientes indicadores de rendimiento pueden ser útiles.

Indicador Descripción/uso
Lecturas y escrituras de páginas Mejorar el rendimiento de las E/S de la base de datos tempdb puede cambiar la tasa de lecturas y escrituras de páginas para las bases de datos de usuario debido a la latencia reducida asociada a la E/S de la base de datos tempdb. En el caso de las páginas de base de datos de usuario, el número general no debe variar en la misma carga de trabajo.
Bytes de lectura y escritura físicos en la base de datos tempdb Si al mover la base de datos tempdb a un dispositivo, como un disco RAM, aumenta la E/S real de la base de datos tempdb, indica que la memoria quitada del grupo de búferes está provocando un aumento de la actividad de base de datos tempdb. Este patrón es un indicador de que la esperanza de vida de la página de las páginas de base de datos también puede verse afectada de forma negativa.
Esperanza de vida de la página Una disminución de la esperanza de vida de la página puede indicar un aumento de los requisitos de E/S físicas para una base de datos de usuario. Es probable que la disminución de velocidad indique que la memoria quitada del grupo de búferes está obligando a las páginas de base de datos a salir del grupo de búferes de forma prematura. Combine con los demás indicadores y pruebe para comprender completamente los límites de los parámetros.
Rendimiento general
Uso de CPU
Escalabilidad
Tiempo respuesta		 El objetivo principal de un cambio de configuración de base de datos tempdb es aumentar el rendimiento general. Las pruebas deben incluir una combinación de cargas de trabajo repetibles que se pueden escalar horizontalmente para determinar cómo afecta el rendimiento.

Algo parecido a una implementación de disco RAM basada en compresión puede funcionar bien con 10 usuarios. Sin embargo, con el aumento de la carga de trabajo, esto puede aumentar los niveles de CPU más allá de los niveles deseados y tener efectos negativos en el tiempo de respuesta cuando las cargas de trabajo son altas. Se recomiendan las pruebas de esfuerzo verdaderas y futuras pruebas de predicción de carga.
Acciones de creación de archivos de trabajo y tablas de trabajo Si al mover la base de datos tempdb a un dispositivo, como un disco RAM, cambia el plan de consulta aumentando el número o el tamaño de los archivos de trabajo o las tablas de trabajo, indica que la memoria quitada del grupo de búferes está provocando un aumento de la actividad de base de datos tempdb. Este patrón es una indicación de que la esperanza de vida de la página de las páginas de base de datos también puede verse afectada de forma negativa.

Ejemplo de reescritura del sector transaccional

En el ejemplo siguiente se elabora la seguridad de datos necesaria para SQL Server bases de datos.

Supongamos que un proveedor de disco RAM usa una implementación de compresión en memoria. La implementación debe encapsularse correctamente proporcionando la apariencia física de la secuencia de archivos como si el sector estuviera alineado y dimensionado para que SQL Server no esté consciente y protegido correctamente de la implementación subyacente. Examine el ejemplo de compresión más cerca.

  • Sector 1 se escribe en el dispositivo y se comprime para ahorrar espacio.
  • Sector 2 se escribe en el dispositivo y se comprime con el sector 1 para ahorrar espacio.

El dispositivo puede realizar las siguientes acciones para ayudar a proteger los datos del sector 1 cuando se combinan con los datos del sector 2.

  • Bloquee todas las escrituras en los sectores 1 y 2.
  • Descomprima el sector 1 en un área temporal, dejando el almacenamiento del sector 1 actual como los datos activos que se van a recuperar.
  • Comprima los sectores 1 y 2 en un nuevo formato de almacenamiento.
  • Bloquee todas las lecturas y escrituras de los sectores 1 y 2.
  • Intercambio de almacenamiento antiguo para los sectores 1 y 2 con nuevo almacenamiento. Si se produce un error en el intento de intercambio (reversión):
    • Restaure el almacenamiento original para los sectores 1 y 2.
    • Quite los datos combinados de los sectores 1 y 2 del área temporal.
    • Error en la operación de escritura del sector 2.
  • Desbloquee las lecturas y escrituras de los sectores 1 y 2.

La capacidad de proporcionar mecanismos de bloqueo en torno a las modificaciones del sector y revertir los cambios cuando se produce un error en el intento de intercambio del sector se considera transitoriamente conforme. En el caso de las implementaciones que usan almacenamiento físico para el respaldo extendido, incluiría los aspectos del registro de transacciones adecuados para ayudar a proteger y revertir los cambios que se aplicaron a las estructuras en disco para mantener la integridad de los archivos de base de datos de SQL Server.

Cualquier dispositivo que permita la reescritura de sectores debe admitir las reescrituras de forma transaccional para que SQL Server no se exponga a la pérdida de datos.

Nota:

La instancia de SQL Server se reinicia cuando se producen errores de E/S y reversión en línea en la base de datos tempdb.

Tenga cuidado al mover la base de datos tempdb.

Tenga cuidado al mover la base de datos tempdb porque, si no se puede crear la base de datos tempdb, SQL Server no se iniciará. Si no se puede crear la base de datos tempdb, inicie SQL Server mediante el parámetro de inicio (-f) y mueva la base de datos tempdb a una ubicación válida.

Para cambiar la ubicación física de la base de datos tempdb, siga estos pasos:

  1. Use la ALTER DATABASE instrucción y la MODIFY FILE cláusula para cambiar los nombres de archivo físicos de cada archivo de la base de datos tempdb para hacer referencia a la nueva ubicación física, como el nuevo disco.

    ALTER DATABASE tempdb MODIFY FILE 
(name = tempdev, filename = 'C:\MyPath\tempdb.mdf')

ALTER DATABASE tempdb MODIFY FILE 
(name = templog, filename = 'C:\MyPath\templog.ldf')

  2. Detenga y reinicie SQL Server.

Las certificaciones de productos asociados no son garantía de compatibilidad ni seguridad

Un producto de terceros o un proveedor determinado pueden recibir una certificación de logotipo de Microsoft. Sin embargo, la certificación de asociados o un logotipo de Microsoft específico no certifica la compatibilidad ni la adecuación para un propósito determinado en SQL Server.

Soporte técnico

Si usa un subsistema con SQL Server que admita las garantías de E/S para el uso de bases de datos transaccionales como se describe en este artículo, Microsoft proporcionará compatibilidad con aplicaciones basadas en SQL Server y SQL Server. Sin embargo, los problemas con o causados por el subsistema se remitirán al fabricante.

En el caso de problemas relacionados con la base de datos tempdb, Soporte técnico de Microsoft Services le pedirá que vuelva a colocar la base de datos tempdb. Póngase en contacto con el proveedor del dispositivo para comprobar que ha implementado y configurado correctamente el dispositivo para el uso de la base de datos transaccional.

Microsoft no certifica ni valida que los productos de terceros funcionan correctamente con SQL Server. Además, Microsoft no proporciona ninguna garantía, garantía o declaración de idoneidad de ningún producto de terceros para su uso con SQL Server.

Referencias

Para obtener más información, consulte los siguientes artículos de Microsoft Knowledge Base: