Compartir a través de

  • Artículo

Optimizar el rendimiento de tempdb

El tamaño y la ubicación física de la base de datos tempdb puede afectar al rendimiento de un sistema. Por ejemplo, si el tamaño definido en tempdb es demasiado pequeño, parte de la carga de procesamiento del sistema puede deberse al crecimiento automático de tempdb hasta el tamaño necesario para admitir la carga de trabajo cada vez que se reinicia la instancia de SQL Server. Para evitar esta sobrecarga, aumente el tamaño de los archivos de registro y de datos de tempdb. Para obtener información acerca de cómo determinar la cantidad de espacio en disco adecuada para tempdb, vea Planeamiento de capacidad para tempdb.

Recomendaciones sobre el tamaño y la ubicación de tempdb

Para obtener un rendimiento óptimo de tempdb, se recomienda la configuración siguiente al usar tempdb en un entorno de producción:

  • Establezca el modelo de recuperación de tempdb en SIMPLE. Este modelo recupera espacio del registro para mantener bajos los requisitos de espacio.

    Para obtener más información, vea ALTER DATABASE (Transact-SQL) o Cómo ver o cambiar el modelo de recuperación de una base de datos (SQL Server Management Studio).

  • Permita que los archivos de tempdb crezcan automáticamente cuando sea necesario. De este modo, el archivo podrá crecer hasta que el disco esté lleno.

    [!NOTA]

    Si el entorno de producción no puede tolerar los tiempos de espera de aplicaciones que podrían producirse durante las operaciones de crecimiento automático, asigne espacio previamente para acomodar la carga de trabajo esperada.

  • Establezca el incremento de crecimiento de archivos en un tamaño razonable para evitar que los archivos de la base de datos tempdb crezcan en un porcentaje demasiado pequeño. Si el crecimiento de los archivos es demasiado pequeño comparado con la cantidad de datos que se escriben en tempdb, es posible que sea necesario expandir tempdb constantemente. Esto afectará al rendimiento. Se recomienda seguir estas directrices generales al establecer el incremento de FILEGROWTH para archivos de tempdb.

    Tamaño de archivos de tempdb

    Incremento de FILEGROWTH

    De 0 a 100 MB

    10 MB

    De 100 a 200 MB

    20 MB

    200 MB o superior

    10%*

    * Podría ser necesario ajustar este porcentaje en función de la velocidad del subsistema de E/S en que estén ubicados los archivos de tempdb. Para evitar que se produzcan tiempos de espera de bloqueo temporal, se recomienda limitar la operación de crecimiento automático a unos dos minutos. Por ejemplo, si el subsistema de E/S puede inicializar un archivo a 50 MB por segundo, se deberá establecer el incremento de FILEGROWTH en 6 GB como máximo, independientemente del tamaño de los archivos de tempdb. Siempre que sea posible, use la inicialización de archivos de base de datos instantánea para mejorar el rendimiento de las operaciones de crecimiento automático.

  • Asigne espacio previamente para todos los archivos de tempdb estableciendo el tamaño de archivo en un valor lo suficientemente alto para acomodar la carga de trabajo habitual del entorno. Así se evita que tempdb se expanda con demasiada frecuencia, lo que puede afectar al rendimiento. La base de datos tempdb debe establecerse de modo que crezca automáticamente, pero sólo con el fin de aumentar el espacio en disco para las excepciones no previstas.

  • Cree tantos archivos como sea necesario para maximizar el ancho de banda del disco. El uso de varios archivos reduce la contención de almacenamiento de tempdb y produce una escalabilidad considerablemente superior. No obstante, no cree demasiados archivos porque podría disminuir el rendimiento y aumentar la carga administrativa. Como regla general, cree un archivo de datos por CPU del servidor (teniendo en cuenta la configuración de affinity mask) y, a continuación, aumente o disminuya el número de archivos según sea necesario. Tenga en cuenta que una CPU de doble núcleo se cuenta como dos CPU.

  • Asigne a cada archivo de datos el mismo tamaño para obtener un rendimiento óptimo de relleno proporcional.

  • Coloque la base de datos tempdb en un subsistema de E/S rápido. Cree bandas en disco si hay muchos discos conectados directamente.

  • Coloque la base de datos tempdb en discos diferentes de los que utilizan las bases de datos de usuario.

Modificar los parámetros de tamaño y de crecimiento de tempdb

Puede modificar los parámetros de tamaño y de crecimiento de archivos de los archivos de datos o de registro de tempdb mediante uno de los métodos siguientes:

Los valores de los parámetros de tamaño y de crecimiento de archivos se usan cuando se crea tempdb. Por ejemplo, si aumenta el tamaño del archivo de datos de tempdb a 20 MB y el incremento de crecimiento de archivos al 15%, los nuevos valores se aplican inmediatamente. Si las actividades transaccionales posteriores producen el crecimiento de tempdb, el archivo de datos volverá al tamaño de 20 MB cada vez que se reinicie la instancia de SQL Server.

Ver los parámetros de tamaño y de crecimiento de tempdb

Puede ver los parámetros de tamaño y de crecimiento de archivos de los archivos de datos o de registro de tempdb mediante uno de los métodos siguientes:

  • SQL Server Management Studio

  • Ejecutando la siguiente consulta.

    SELECT 
    name AS FileName, 
    size*1.0/128 AS FileSizeinMB,
    CASE max_size 
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' = 
        CASE
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'
            WHEN growth > 0 AND is_percent_growth = 0 
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

Detectar los errores de ruta de acceso de E/S de disco

Cuando se establece en CHECKSUM, la opción PAGE_VERIFY detecta páginas de base de datos dañadas producidas por errores de ruta de acceso de E/S de disco y notifica estos errores, como MSSQLSERVER_823, MSSQLSERVER_824 o MSSQLSERVER_825, en el registro de errores de SQL. Los errores de ruta de acceso de E/S de disco pueden producir daños en la base de datos debidos por lo general a problemas con el suministro eléctrico o a fallos del hardware del disco que ocurren en el momento en que se está escribiendo la página en el disco. Para obtener más información acerca de los errores de E/S, vea el capítulo 2 del documento sobre elementos fundamentales de E/S de Microsoft SQL Server (puede estar en inglés).

En versiones anteriores de SQL Server, la opción de base de datos PAGE_VERIFY está establecida a NONE para la base de datos tempdb y no se puede modificar. En SQL Server 2008, el valor predeterminado para la base de datos tempdb es CHECKSUM para las nuevas instalaciones de SQL Server. Al actualizar una instalación de SQL Server, el valor predeterminado sigue siendo NONE. Recomendamos que establezca la opción PAGE_VERIFY para la base de datos tempdb en CHECKSUM.