Recomendaciones para las instancias de SQL Server

Puedes aplicar varias recomendaciones para optimizar las instancias de Compute Engine que ejecutan Microsoft SQL Server. Para aprender a configurar una instancia de SQL Server de alto rendimiento, lee la página sobre cómo crear una instancia de SQL Server de alto rendimiento.

Configura Windows

En esta sección, se cubren temas de configuración sobre cómo optimizar el sistema operativo Microsoft Windows para el rendimiento de SQL Server cuando se ejecuta en Compute Engine.

Configura el firewall de Windows

Recomendación: Usa el firewall avanzado de Windows Server y especifica las direcciones IP de las computadoras de los clientes.

El Firewall avanzado de Windows es un componente de seguridad importante en Windows Server. Cuando configures el entorno de SQL Server para poder conectarte a la base de datos desde otras máquinas cliente, configura el firewall a fin de permitir el tráfico entrante:

netsh advfirewall firewall add rule name="SQL Access" ^
dir=in action=allow ^
program="%programfiles%\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" ^
remoteip=[LOCAL_SUBNET]

Cuando usas esta regla de firewall, es una buena práctica especificar la dirección IP de las máquinas cliente. Especifica una lista delimitada por comas de direcciones IP sin espacios en blanco para el parámetro remoteip en lugar de [LOCAL_SUBNET]. Además, ten en cuenta que la ruta para el parámetro program puede cambiar según la versión de SQL Server que uses.

La imagen de la aplicación de SQL Server incluye una regla de firewall de Windows de SQL Server. Esta regla no tiene muchas restricciones, por lo que se recomienda que la inhabilites antes de que el sistema entre en producción.

Ajusta las conexiones de red

Recomendación: Usa la configuración de red predeterminada del sistema operativo.

La configuración de red predeterminada en la mayoría de los sistemas operativos está configurada para las conexiones en computadoras pequeñas que están conectadas a redes moderadamente rápidas. Esa configuración suele ser suficientes. Además, los valores predeterminados conservadores garantizan que el tráfico no sature la red y las computadoras conectadas.

En Compute Engine, las instancias de máquinas virtuales están conectadas a una red diseñada por Google que ofrece alta capacidad y rendimiento. Los servidores físicos que ejecutan las instancias de Compute Engine están muy optimizados para aprovechar esta capacidad de red. Los controladores de red virtual en tus instancias también están optimizados, lo que hace que los valores predeterminados sean suficientes para la mayoría de los casos prácticos.

Instala el antivirus

Recomendación: Sigue las instrucciones de Microsoft para el software antivirus.

Si ejecutas Windows, deberías ejecutar software de antivirus. Los virus de software y software malicioso presentan un riesgo significativo para cualquier sistema conectado a una red, y el software antivirus es un simple paso de mitigación que puede usarse para proteger tus datos. Sin embargo, si el software antivirus no está bien configurado, puede afectar el rendimiento de la base de datos de forma negativa. Microsoft proporciona consejos sobre cómo elegir el software antivirus.

Optimización de rendimiento y estabilidad

En esta sección, se proporciona información sobre cómo optimizar el rendimiento de SQL Server en Compute Engine y las actividades operativas para que funcione sin problemas.

Mueve archivos de datos y archivos de registro a un disco nuevo

Recomendación: Usa un disco persistente SSD aparte para los archivos de registro y datos.

De forma predeterminada, la imagen preconfigurada para SQL Server viene con todo lo que está instalado en el disco persistente de inicio, que se activa como la unidad ‘C:’. Considera conectar un disco persistente SSD secundario y mover los archivos de registro y los archivos de datos al disco nuevo.

Usa un SSD local para mejorar las IOPS

Recomendación: Crea instancias nuevas de SQL Server con uno o más SSD locales para almacenar los archivos de paginación de Windows y tempdb.

La naturaleza efímera de la tecnología SSD local hace que sea un mal candidato para usar en tus bases de datos y archivos importantes. Sin embargo, el archivo de paginación de Windows y tempdb son archivos temporales, por lo que ambos son excelentes candidatos para pasar a un SSD local. Esto descarga una cantidad significativa de operaciones de E/S de los discos persistentes SSD. Puedes leer más sobre cómo configurar esto aquí.

Procesamiento de consultas paralelas

Recomendación: Configura max degree of parallelism como 8.

La configuración predeterminada recomendada para max degree of parallelism es hacerla coincidir con la cantidad de CPU en el servidor. Sin embargo, hay un punto en el que dividir una consulta en 16 o 32 fragmentos, ejecutarlos en diferentes CPU virtuales y, luego, consolidarlo todo en un solo resultado lleva mucho más tiempo que si solo una CPU virtual hubiera ejecutado la consulta. En práctica, 8 funciona como un buen valor predeterminado.

Recomendación: Supervisa las esperas de CXPACKET y aumenta de forma gradual cost threshold for parallelism.

Esta configuración va de la mano con max degree of parallelism. Cada unidad representa una combinación de trabajo de CPU y E/S necesaria a fin de realizar una consulta con un plan de ejecución en serie antes de que se considere para un plan de ejecución paralelo. El valor predeterminado es 5. Aunque no tenemos ninguna recomendación específica para cambiar el valor predeterminado, vale la pena vigilar y, si es necesario, aumentarlo de forma gradual en 5 durante la prueba de carga. Un indicador clave de que es posible que sea necesario aumentar este valor es la presencia de esperas de CXPACKET. Aunque la presencia de esperas de CXPACKET no indica necesariamente que esta configuración deba cambiar, es un buen punto de partida.

Recomendación: Supervisa los diferentes tipos de espera y ajusta la configuración de procesamiento paralelo global o configúrala al nivel de base de datos individual.

Las bases de datos individuales pueden tener diferentes necesidades de paralelismo. Puedes establecer esta configuración de manera global y establecer Max DOP al nivel de base de datos individual. Deberías observar tus cargas de trabajo únicas, supervisar las esperas y ajustar los valores en consecuencia.

El sitio SQLSkills ofrece una guía de rendimiento útil que cubre las estadísticas de espera dentro de la base de datos. Seguir esta guía puede ayudarte a comprender qué es la espera y cómo mitigar las demoras.

Maneja registros de transacciones

Recomendación: Supervisa el crecimiento del registro de transacciones en tu sistema. Considera inhabilitar el crecimiento automático y configurar tu archivo de registro en un tamaño fijo, en función de la acumulación diaria promedio de registros.

Una de las fuentes más desapercibidas de pérdida de rendimiento y demoras intermitentes es el crecimiento no administrado del registro de transacciones. Cuando tu base de datos está configurada para usar el modelo de recuperación Full, puedes realizar un restablecimiento a cualquier momento pasado, pero los registros de transacciones se llenan más rápido. De manera predeterminada, cuando el archivo de registro de transacciones está lleno, SQL Server aumenta el tamaño del archivo a fin de agregar más espacio vacío para escribir más transacciones y bloquea toda la actividad en la base de datos hasta que finaliza. SQL Server aumenta cada archivo de registro en función de su Tamaño máximo de archivo y la configuración de Crecimiento de archivo.

Cuando el archivo alcanza su límite de tamaño máximo y no puede crecer, el sistema emite un error 9002 y pone la base de datos en modo de solo lectura. Si el archivo puede crecer, SQL Server expande el tamaño del archivo y pone a cero el espacio vacío. La configuración de Crecimiento de archivo tiene como valor predeterminado el 10% del tamaño actual del archivo de registro. Esta no es una buena configuración predeterminada para el rendimiento, ya que cuanto más grande sea el archivo, más tiempo llevará crear el nuevo espacio vacío.

Recomendación: Programa copias de seguridad periódicas del registro de transacciones.

Sin tener en cuenta la configuración de tamaño máximo y crecimiento, programa copias de seguridad del registro de transacciones regulares, que de manera predeterminada trunca las entradas de registro antiguas y permite que el sistema vuelva a usar el espacio de archivos existente. Esta sencilla tarea de mantenimiento puede ayudar a prevenir caídas de rendimiento en los momentos de mayor tráfico.

Optimiza los archivos de registro virtuales

Recomendación: Supervisa el crecimiento de los archivos de registro virtuales y toma medidas para evitar la fragmentación de los archivos de registro.

El archivo de registro de transacciones físicas se segmenta en archivos de registro virtuales (VLF). Se crean VLF nuevos cada vez que el archivo de registro de transacciones físicas debe crecer. Si no inhabilitaste el crecimiento automático y el crecimiento ocurre con demasiada frecuencia, se crean demasiados VLF. Esta actividad puede provocar la fragmentación del archivo de registro, que es similar a la fragmentación del disco y puede afectar de forma negativa el rendimiento.

SQL Server 2014 introdujo un algoritmo más eficiente para determinar cuántos VLF crear durante el crecimiento automático. Por lo general, si el crecimiento es menor que 1/8 del tamaño del archivo de registro actual, SQL Server crea un VLF dentro de ese segmento nuevo. Antes, se crearían 8 VLF para el crecimiento de entre 64 MB y 1 GB, y 16 VLF para el crecimiento de más de 1 GB. Puedes usar la siguiente secuencia de comandos de TSQL para verificar cuántos VLF tienes en tu base de datos. Si tienes miles de archivos, considera reducir y cambiar el tamaño de tu archivo de registro de forma manual.

--Check VLFs substitute your database name below
USE 
DECLARE @vlf_count INT
DBCC LOGINFO
SET @vlf_count = @@ROWCOUNT
SELECT VLFs = @vlf_count

Puedes leer más sobre VLF en el sitio web de Brent Ozar.

Evita la fragmentación del índice

Recomendación: Desfragmenta con regularidad los índices en las tablas más modificadas.

Los índices de tus tablas pueden fragmentarse, lo que puede conducir a un bajo rendimiento de cualquier consulta que use estos índices. Un programa de mantenimiento regular debe incluir la reorganización de los índices en las tablas más modificadas. Puedes ejecutar la siguiente secuencia de comandos de T-SQL para que tu base de datos muestre los índices y su porcentaje de fragmentación. Puedes ver en los resultados del ejemplo que el índice PK_STOCK está fragmentado en un 95%. En la siguiente instrucción ‘SELECT’, reemplaza ‘[YOUR_DB]’ por el nombre de tu base de datos:

SELECT stats.index_id as id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'[YOUR_DB]'), NULL, NULL, NULL, NULL) AS stats
    JOIN sys.indexes AS indx ON stats.object_id = indx.object_id
      AND stats.index_id = indx.index_id AND name IS NOT NULL;

RESULTS
-------------------------------
Id    name          avg_fragmentation_in_percent
-------------------------------
1 ORDERS_I1 0
2 ORDERS_I2 0
1 ORDER_LINE_I1 0.01
1 PK_STOCK95.5529819557039
1 PK_WAREHOUSE0.8

Cuando los índices están demasiado fragmentados, puedes reorganizarlos con una simple secuencia de comandos ALTER. Aquí hay una secuencia de comandos de ejemplo que imprimirá las instrucciones ALTER que puedes ejecutar para cada uno de los índices de tus tablas.

SELECT
'ALTER INDEX ALL ON ' + table_name + ' REORGANIZE;
GO'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'YOUR_DB'

Elige las tablas del conjunto de resultados que tengan la fragmentación más alta y ejecuta esas instrucciones de forma incremental. Considera programar esta o una secuencia de comandos similar como uno de tus trabajos de mantenimiento regulares.

Crea una copia de seguridad

Recomendación: Ten un plan para las copias de seguridad y realízalas con regularidad.

El sitio de Ola Hallengren proporciona un buen punto de partida para comprender cómo implementar un plan sólido de copia de seguridad y mantenimiento.

Cuando realizas copias de seguridad de bases de datos regulares, ten cuidado de no consumir demasiadas IOPS de disco persistente. Usa el SSD local para habilitar por etapas las copias de seguridad y enviarlas a un depósito de Cloud Storage.

Supervisa

Recomendación: Usa Stackdriver Monitoring.

Puedes instalar el agente de Stackdriver Monitoring para Microsoft Windows a fin de enviar varios datos de supervisión al sistema de Stackdriver.

Cuando usas las capacidades de recopilación de datos, puedes ajustar la información que deseas supervisar y enviarla al almacén de datos de administración incorporado. El almacén de datos de administración se puede ejecutar en el mismo servidor que supervisas, o los datos se pueden transmitir a otra instancia de SQL Server que ejecuta el almacén.

Carga datos de forma masiva

Recomendación: Usa una base de datos aparte para habilitar por etapas y transformar datos masivos antes de moverlos a servidores de producción.

Es probable que necesites cargar grandes cantidades de datos en tu sistema al menos una vez, si no con regularidad. Esta es una operación de uso intensivo de recursos, y puedes alcanzar el límite de IOPS de disco persistente cuando realizas cargas masivas.

Hay una manera fácil de reducir el consumo de CPU y E/S del disco de las operaciones de carga masiva, con el beneficio adicional de acelerar el tiempo de ejecución de tus trabajos por lotes. La solución aquí es crear una base de datos aparte que use el modelo de recuperación Simple y usar esa base de datos para habilitar por etapas y transformar el conjunto de datos en masa antes de insertarlo en la base de datos de producción. También puedes colocar esta nueva base de datos en una unidad SSD local, si tienes suficiente espacio. Esto reducirá el consumo de recursos de tus operaciones masivas, así como el tiempo para completar los trabajos. El beneficio final es que el trabajo de copia de seguridad para los datos de producción no tendrá que respaldar todas esas operaciones masivas en el registro de transacciones y, por lo tanto, será más pequeño y se ejecutará más rápido.

Valida la configuración

Recomendación: Prueba la configuración para validar que tiene el rendimiento esperado.

Siempre que configures un sistema nuevo, debes planear la validación de la configuración y ejecutar algunas pruebas de rendimiento. Este procedimiento almacenado es un gran recurso para evaluar una configuración de SQL Server. Tómate un tiempo después para leer sobre las marcas de configuración y ejecutar el procedimiento.

Optimiza SQL Server Enterprise Edition

SQL Server Enterprise Edition tiene una larga lista de capacidades adicionales por sobre Standard Edition. Si migras una licencia existente a GCP, hay algunas opciones de rendimiento que podrías considerar implementar.

Usa tablas comprimidas

Recomendación: Habilita la compresión de tablas y de índices.

Parece paradójico que comprimir tablas haga que el sistema funcione más rápido, pero en la mayoría de los casos eso es lo que sucede. La compensación usa una pequeña cantidad de ciclos de CPU a fin de comprimir los datos y eliminar la E/S de disco adicional necesaria para leer y escribir los bloques más grandes. Por lo general, cuanto menos E/S de disco use el sistema, mejor será su rendimiento. Las instrucciones para estimar y habilitar la compresión de tablas y de índices se encuentran en el sitio web MSDN.

Habilita la extensión del grupo de búferes

Recomendación: Usa la extensión del grupo de búferes para acelerar el acceso a los datos.

El grupo de búferes es en donde el sistema almacena las páginas limpias. En términos simples, almacena copias de tus datos y refleja cómo se ven en el disco. Cuando los datos cambian en la memoria, se llama página sucia, que debe ser vaciada al disco para guardar los cambios. Cuando la base de datos es más grande que la memoria disponible, se ejerce presión sobre el grupo de búferes y se pueden descartar páginas limpias. Cuando se descartan las páginas limpias, el sistema tendrá que leer desde el disco la próxima vez que tengas que acceder a los datos descartados.

La característica de extensión del grupo de búferes permite enviar páginas limpias a un SSD local, en lugar de descartarlas. Esto funciona de la misma forma que la memoria virtual, es decir, mediante el intercambio, y te da acceso a las páginas limpias en el SSD local, que es más rápido de lo que obtendrías si usaras el disco normal para obtener los datos.

Esta técnica no es tan rápida como tener suficiente memoria, pero puede darte un aumento moderado en el rendimiento cuando la memoria disponible es baja. Puedes leer más sobre las extensiones de los grupos de búferes y revisar algunos resultados comparativos en el sitio de Brent Ozar.

Pasos siguientes

¿Te ha resultado útil esta página? Enviar comentarios:

Enviar comentarios sobre...

Documentación de Compute Engine