Prácticas recomendadas para las instancias de SQL Server

---

Puedes aplicar varias recomendaciones para optimizar las instancias de Compute Engine que ejecutan Microsoft SQL Server. Para obtener más información sobre cómo configurar una instancia de SQL Server de alto rendimiento, consulta Crea 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

Práctica recomendada: 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 tu entorno de SQL Server para que pueda conectarse a la base de datos desde otras máquinas cliente, configura el firewall para 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 de acceso 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

Práctica recomendada: 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áquina virtual (VM) 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 altamente 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

Práctica recomendada: Sigue las instrucciones de Microsoft para el software antivirus.

Si ejecutas Windows, deberías ejecutar software de antivirus. Los virus y el software malicioso presentan un riesgo significativo para cualquier sistema conectado a una red, y el software antivirus representa 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 asesoramiento sobre cómo elegir el software antivirus.

Optimiza el rendimiento y la estabilidad

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

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

Práctica recomendada: 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 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

Práctica recomendada: Crea instancias de SQL Server nuevas 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 la convierte en una mala opción para usarla con tus bases de datos y archivos importantes. Sin embargo, los archivos de paginación de Windows y tempdb son temporales, por lo que ambos son excelentes candidatos para pasarlos a un SSD local. Esto disminuye de forma significativa la cantidad de operaciones de E/S de los discos persistentes SSD. Para obtener más información sobre esta configuración, consulta Configura TempDB.

Procesa consultas paralelas

Práctica recomendada: Establece max degree of parallelism en 8.

La configuración predeterminada recomendada para max degree of parallelism es hacerla coincidir con el número 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.

Práctica recomendada: Supervisa las esperas de CXPACKET y aumenta cost threshold for parallelism de forma gradual.

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 siempre indica que esta configuración deba cambiar, es un buen punto de partida.

Práctica recomendada: 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 a 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

Práctica recomendada: 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 forma 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 en 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.

Práctica recomendada: Programa copias de seguridad periódicas del registro de transacciones.

Sin tener en cuenta la configuración de tamaño máximo y de 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

Práctica recomendada: 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 un crecimiento entre 64 MB y 1 GB, y 16 VLF para un 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 YOUR_DB
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

Práctica recomendada: 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 con más modificaciones. Puedes ejecutar la siguiente secuencia de comandos de Transact-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 la 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 mediante una secuencia de comandos ALTER básica. Aquí hay una secuencia de comandos de ejemplo que imprime las declaraciones ALTER que puedes ejecutar para los índices de las 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.

Formatea los discos secundarios

Práctica recomendada: Formatea los discos secundarios con una unidad de asignación de 64 KB.

SQL Server almacena los datos en unidades de almacenamiento llamadas extensiones. Las extensiones tienen un tamaño de 64 KB y constan de ocho páginas de memoria contiguas, que tienen un tamaño de 8 KB. Formatear un disco con una unidad de asignación de 64 KB permite que SQL Server lea y escriba extensiones de manera más eficiente, lo que aumenta el rendimiento de E/S desde el disco.

Para formatear discos secundarios con una unidad de asignación de 64 KB, ejecuta el siguiente comando de PowerShell, que busca todos los discos nuevos y no inicializados de un sistema y los formatea con la unidad de asignación de 64 KB:

Get-Disk | Where-Object {$_.PartitionStyle -eq 'RAW'} | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -AssignDriveLetter -UseMaximumSize | Format-Volume -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$FALSE

Crea copias de seguridad

Práctica recomendada: Crea una copia de seguridad de tus datos con regularidad mediante las soluciones de copia de seguridad y recuperación ante desastres de Google para obtener una protección óptima. Te recomendamos crear una copia de seguridad de los datos al menos una vez al día.

Las soluciones de copia de seguridad y recuperación ante desastres de Google proporcionan los siguientes beneficios para Microsoft SQL Server:

• Copias de seguridad eficientes, incrementales y permanentes con recuperación de un momento determinado real que ayuda a realizar copias de seguridad en menos tiempo que las copias convencionales y, a su vez, reduce el impacto en los servidores de producción. También reduce el consumo de ancho de banda y almacenamiento para el objetivo de punto de recuperación (RPO) y el costo total de propiedad (TCO) bajos.
• Activa y migra recuperaciones (M&M) para copias de seguridad almacenadas en Cloud Storage para usar un RTO bajo.
• Integración completa en las capacidades de SQL Server, incluida la compatibilidad con los clústeres de grupos de disponibilidad de SQL Server y varias opciones de recuperación en todas las situaciones.
• Panel central de administración que incluye capacidades dedicadas de supervisión, informes y alertas para todas tus copias de seguridad.

Más información:

• Descripción general del producto servicio Backup and DR
• Lista de funciones para el servicio Backup and DR
• Proteger y recuperar las bases de datos de Microsoft SQL Server
• Precios del servicio Backup and DR
• Calculadora de precios

Supervisión

Práctica recomendada: Usa Cloud Monitoring.

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

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

Práctica recomendada: 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 es crear una base de datos completamente separada que use el modelo de recuperación Simple y, luego, usar esa base de datos para realizar la etapa de pruebas del conjunto de datos masivo, y transformarlo, antes de insertarlo en tu base de datos de producción. También puedes colocar esta nueva base de datos en una unidad SSD local, si tienes suficiente espacio. El uso de un SSD local para la base de datos de recuperación reduce el consumo de recursos de tus operaciones masivas y el tiempo para completar los trabajos. El beneficio final es que tu trabajo de copia de seguridad para los datos de producción no tendrá que crear una copia de seguridad de 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

Práctica recomendada: 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 excelente recurso para evaluar tu 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 Google Cloud, existen algunas opciones de rendimiento que podrías considerar implementar.

Usa tablas comprimidas

Práctica recomendada: 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 de MSDN.

Habilita la extensión del grupo de búferes

Práctica recomendada: 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. Las páginas sucias se deben pasar 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 podrían descartar páginas limpias. Cuando se borran las páginas limpias, el sistema debe leer desde el disco la próxima vez que acceda a los datos que se quitaron.

La función de extensión del grupo de búferes te permite enviar páginas limpias a una 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 que usar el disco normal para recuperar 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.

Optimiza las licencias de SQL Server

Multiprocesamiento simultáneo (SMT)

Práctica recomendada: Configura la cantidad de subprocesos por núcleo en 1 para la mayoría de las cargas de trabajo de SQL Server

El multiprocesamiento simultáneo (SMT), que se conoce en los procesadores Intel como tecnología de hipersubprocesos (HTT), es una función que permite que un solo núcleo de CPU se comparta de forma lógica como dos subprocesos. En Compute Engine, el SMT está habilitado en la mayoría de las VMs de forma predeterminada, lo que significa que cada CPU virtual de la VM se ejecuta en un solo subproceso y cada núcleo de CPU física se comparte entre dos CPU virtuales.

En Compute Engine, puedes configurar la cantidad de subprocesos por núcleo, lo que desactiva los SMT de manera efectiva. Cuando la cantidad de subprocesos por núcleo se establece en 1, las CPU virtuales no comparten núcleos de CPUs físicos. Esta configuración afecta de manera significativa los costos de las licencias de Windows Server y SQL Server. Cuando la cantidad de subprocesos por núcleo se establece en 1, la cantidad de CPUs virtuales en una VM se reduce a la mitad, lo que también reduce a la mitad la cantidad de licencias de Windows Server y SQL Server necesarias. Esto puede disminuir de forma significativa el costo total de la carga de trabajo.

Sin embargo, la configuración de la cantidad de subprocesos por núcleo también afecta el rendimiento de la carga de trabajo. Las aplicaciones que están escritas con varios subprocesos pueden aprovechar esta función mediante la división del trabajo de procesamiento en fragmentos paralelizables más pequeños que se programan en varios núcleos lógicos. Esta paralelización del trabajo suele aumentar la capacidad de procesamiento general del sistema a través de un mejor uso de los recursos principales disponibles. Por ejemplo, cuando se detiene un subproceso, el otro subproceso puede usar el núcleo.

El impacto exacto en el rendimiento de SMT en SQL Server depende de las características de la carga de trabajo y de la plataforma de hardware que se usa, ya que la implementación de SMT difiere entre las generaciones de hardware. Las cargas de trabajo con un gran volumen de transacciones pequeñas, por ejemplo, las cargas de trabajo de OLTP, a menudo pueden aprovechar los SMT y beneficiarse de un mayor aumento del rendimiento. En cambio, las cargas de trabajo que son menos paralelizables, por ejemplo, las cargas de trabajo de OLAP, se benefician menos de los SMT. Aunque estos patrones se suelen observar, considera evaluar el impacto en el rendimiento de los SMT por carga de trabajo para determinar el impacto de configurar la cantidad de subprocesos por núcleo en 1.

La configuración más rentable para la mayoría de las cargas de trabajo de SQL Server implica establecer la cantidad de subprocesos por núcleo en 1. Cualquier disminución en el rendimiento se puede compensar con una VM más grande. En la mayoría de los casos, la disminución del 50% en el costo de licencias es mayor que el aumento del costo de la VM más grande.

Ejemplo: considera que SQL Server se implementa en la configuración n2-standard-16

De forma predeterminada, la cantidad de núcleos visibles en el sistema operativo es 16, lo que significa que se requieren 16 CPUs virtuales de Windows Server y 16 CPUs virtuales de licencias de SQL Server para ejecutar el servidor.

PS C:\> Get-WmiObject -Class Win32_processor | Select-Object NumberOfCores, @{Name="Thread(s) per core";Expression={$_.NumberOfLogicalProcessors/$_.NumberOfCores}}

NumberOfCores Thread(s) per core
------------- ------------------
            8                  2

Después de seguir los pasos para inhabilitar SMT en SQL Server, la configuración nueva es la siguiente:

PS C:\> Get-WmiObject -Class Win32_processor | Select-Object NumberOfCores, @{Name="Thread(s) per core";Expression={$_.NumberOfLogicalProcessors/$_.NumberOfCores}}

NumberOfCores Thread(s) per core
------------- ------------------
            8                  1

Ahora que solo 8 núcleos son visibles en el sistema operativo, el servidor solo requiere 8 CPU virtuales de Windows Server y SQL Server para ejecutarse.

¿Qué sigue?

• Crea una instancia de SQL Server de alto rendimiento
• Crea instancias de SQL Server
• Crea instancias de Windows.