Prácticas recomendadas para instancias de SQL Server

Puedes aplicar varias prácticas recomendadas para optimizar las instancias de Compute Engine que ejecutan Microsoft SQL Server. Para saber cómo configurar una instancia de alto rendimiento de SQL Server, consulta el artículo Crear una instancia de alto rendimiento de SQL Server.

Usar Workload Manager para evaluar e implementar SQL Server

La evaluación de SQL Server en Workload Manager te permite analizar tus implementaciones de SQL Server con un conjunto de recomendaciones predefinidas Google Cloud para obtener un rendimiento óptimo directamente desde la consolaGoogle Cloud . Para obtener más información, consulta las instrucciones de configuración del agente de SQL Server.

La herramienta Automatización de la implementación guiada de Gestor de cargas de trabajo te permite configurar e implementar aplicaciones empresariales en Google Cloud. También puedes usar la automatización de la implementación guiada para configurar una implementación de tu carga de trabajo y, a continuación, generar infraestructura como código (IaC) de Terraform y Ansible que puedes exportar para personalizarla aún más o usarla en una canalización de implementación. Para obtener más información, consulta Automatización de la implementación guiada.

Configurar Windows

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

Configurar el cortafuegos de Windows

Práctica recomendada: Usa el Firewall avanzado de Windows Server y especifica las direcciones IP de tus ordenadores cliente.

Windows Firewall con seguridad avanzada es un componente de seguridad importante de Windows Server. Cuando configure su entorno de SQL Server para que pueda conectarse a la base de datos desde otros equipos cliente, configure el cortafuegos 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 uses esta regla de cortafuegos, te recomendamos que especifiques la dirección IP de tus máquinas cliente. Especifica una lista de direcciones IP separadas por comas y sin espacios en blanco para el parámetro remoteip en lugar de LOCAL_SUBNET. Además, ten en cuenta que la ruta del parámetro program puede cambiar en función de la versión de SQL Server que utilices.

La imagen de aplicación de SQL Server incluye una SQL Serverregla de firewall de Windows. Esta regla no tiene muchas restricciones, por lo que te recomendamos que la inhabilite antes de que tu sistema pase a producción.

Ajustar las conexiones de red

Práctica recomendada: Utiliza la configuración de red predeterminada del sistema operativo.

Los ajustes de red predeterminados de la mayoría de los sistemas operativos están configurados para conexiones en ordenadores pequeños que están conectados a redes moderadamente rápidas. Estos ajustes suelen ser suficientes. Además, los valores predeterminados conservadores aseguran que el tráfico de red no sobrecargue la red ni los ordenadores conectados.

En Compute Engine, las instancias de máquina virtual (VM) se adjuntan a una red diseñada por Google que ofrece una gran capacidad y rendimiento. Los servidores físicos que ejecutan tus instancias de Compute Engine están muy optimizados para aprovechar esta capacidad de red. Los controladores de red virtual de tus instancias también están optimizados, por lo que los valores predeterminados son suficientes para la mayoría de los casos prácticos.

Instalar un antivirus

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

Si usas Windows, deberías tener instalado algún software antivirus. El malware y los virus de software suponen un riesgo importante para cualquier sistema conectado a una red, y el software antivirus es una medida de mitigación sencilla que puedes usar para proteger tus datos. Sin embargo, si el software antivirus no está configurado correctamente, puede afectar negativamente al rendimiento de la base de datos. Microsoft ofrece consejos sobre cómo elegir un software antivirus.

Optimizar el rendimiento y la estabilidad

En esta sección se explica cómo optimizar el rendimiento de SQL Server en Compute Engine y se describen las actividades operativas que te ayudarán a que funcione correctamente.

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

Práctica recomendada: usa un disco persistente SSD independiente para los archivos de registro y de datos.

De forma predeterminada, la imagen preconfigurada de SQL Server incluye todo lo que se ha instalado en el disco persistente de arranque, que se monta como unidad `C:`. Te recomendamos que conectes un disco persistente SSD secundario y que muevas los archivos de registro y los archivos de datos al nuevo disco.

Usar una SSD local para mejorar las IOPS

Práctica recomendada: crea instancias de SQL Server con una o varias SSDs locales para almacenar los archivos de paginación de tempdb y Windows.

La naturaleza efímera de la tecnología SSD local hace que no sea una buena opción para usarla con tus bases de datos críticas y archivos importantes. Sin embargo, tanto el tempdb como el archivo de paginación de Windows son archivos temporales, por lo que ambos son buenos candidatos para moverlos a una SSD local. De esta forma, se descargan un número significativo de operaciones de E/S de tus discos persistentes SSD. Para obtener más información sobre cómo configurarlo, consulta Configurar TempDB.

Procesamiento de consultas en paralelo

Práctica recomendada: define el max degree of parallelism como 8.

La configuración predeterminada recomendada para max degree of parallelism es que coincida con el número de CPUs del servidor. Sin embargo, llega un punto en el que dividir una consulta en 16 o 32 fragmentos, ejecutarlos todos en diferentes vCPUs y, a continuación, consolidarlos en un único resultado lleva mucho más tiempo que si solo una vCPU hubiera ejecutado la consulta. En la práctica, 8 es un buen valor predeterminado.

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

Este ajuste va de la mano de max degree of parallelism. Cada unidad representa una combinación de trabajo de CPU y E/S necesaria para realizar una consulta con un plan de ejecución en serie antes de que se considere un plan de ejecución en paralelo. El valor predeterminado es 5. Aunque no recomendamos cambiar el valor predeterminado, merece la pena vigilarlo y, si es necesario, aumentarlo de forma gradual en 5 durante las pruebas de carga. Un indicador clave de que puede que sea necesario aumentar este valor es la presencia de esperas CXPACKET. Aunque la presencia de CXPACKET esperas no indica necesariamente que este ajuste deba cambiarse, es un buen punto de partida.

Práctica recomendada: monitoriza los distintos tipos de espera y ajusta la configuración de procesamiento paralelo global o configúrala a nivel de base de datos individual.

Cada base de datos puede tener diferentes necesidades de paralelismo. Puedes definir estos ajustes de forma global y Max DOP a nivel de base de datos. Debes observar tus cargas de trabajo únicas, monitorizar las esperas y, a continuación, ajustar los valores en consecuencia.

El sitio de SQLSkills ofrece una guía de rendimiento útil que abarca las estadísticas de espera de la base de datos. Si sigues esta guía, podrás saber qué está pendiente y cómo reducir los retrasos.

Gestionar registros de transacciones

Práctica recomendada: monitoriza el crecimiento del registro de transacciones de tu sistema. Te recomendamos que inhabilites el crecimiento automático y definas un tamaño fijo para el archivo de registro en función de la acumulación media de registros diarios.

Una de las fuentes más ignoradas de pérdida de rendimiento y ralentizaciones intermitentes es el crecimiento no gestionado del registro de transacciones. Si tu base de datos está configurada para usar el modelo de recuperación Full, puedes realizar una restauración a cualquier momento, pero tus registros de transacciones se llenarán más rápido. De forma predeterminada, cuando el archivo de registro de transacciones está lleno, SQL Server aumenta el tamaño del archivo para añadir 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 el tamaño de cada archivo de registro en función de su tamaño máximo de archivo y del ajuste Crecimiento de archivo.

Cuando el archivo alcanza el límite de tamaño máximo y no puede crecer más, el sistema emite un error 9002 y pone la base de datos en modo de solo lectura. Si el archivo puede crecer, SQL Server aumenta su tamaño y rellena el espacio vacío con ceros. El valor predeterminado de Crecimiento de archivo es el 10% del tamaño actual del archivo de registro. No es una buena opción predeterminada para el rendimiento, ya que cuanto más grande sea el archivo, más tiempo tardará en crearse el nuevo espacio vacío.

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

Independientemente de la configuración del tamaño máximo y del crecimiento, programa copias de seguridad de los registros de transacciones periódicas, que, de forma predeterminada, truncan las entradas de registro antiguas y permiten que el sistema reutilice el espacio de archivo. Esta sencilla tarea de mantenimiento puede ayudarte a evitar que el rendimiento disminuya en los momentos de mayor tráfico.

Optimizar archivos de registro virtuales

Práctica recomendada: monitoriza el crecimiento del archivo de registro virtual y toma medidas para evitar la fragmentación del archivo de registro.

El archivo de registro de transacciones físico se segmenta en archivos de registro virtuales (VLFs). Se crean nuevos VLF cada vez que tiene que aumentar el tamaño del archivo de registro de transacciones físico. Si no has inhabilitado el crecimiento automático y este se produce con demasiada frecuencia, se crearán demasiados VLF. Esta actividad puede provocar la fragmentación de los archivos de registro, que es similar a la fragmentación del disco y puede afectar negativamente al rendimiento.

SQL Server 2014 introdujo un algoritmo más eficiente para determinar cuántos VLFs se deben crear durante el crecimiento automático. Por lo general, si el crecimiento es inferior a 1/8 del tamaño del archivo de registro actual, SQL Server crea un VLF en ese nuevo segmento. Antes, se creaban 8 VLF para un crecimiento entre 64 MB y 1 GB, y 16 VLF para un crecimiento superior a 1 GB. Puedes usar la secuencia de comandos de T-SQL que se muestra a continuación para comprobar cuántos VLF tiene tu base de datos. Si tiene miles de archivos, considere la posibilidad de reducir y cambiar el tamaño del archivo de registro manualmente.

--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 consultar más información sobre los VLF en el sitio web de Brent Ozar.

Evitar la fragmentación de índices

Práctica recomendada: desfragmenta periódicamente los índices de las tablas que se modifican con más frecuencia.

Los índices de tus tablas pueden fragmentarse, lo que puede provocar un rendimiento deficiente de las consultas que los usen. Una programación de mantenimiento periódica debe incluir la reorganización de los índices de las tablas que se modifican con más frecuencia. Puedes ejecutar la siguiente secuencia de comandos de Transact-SQL en tu base de datos para mostrar los índices y su porcentaje de fragmentación. En los resultados de ejemplo, se puede ver que el índice PK_STOCK está fragmentado en un 95 %. En la siguiente instrucción "SELECT", sustituye "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 secuencia de comandos básica de ALTER. Aquí tienes un ejemplo de una secuencia de comandos que imprime las ALTER instrucciones 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, a continuación, ejecuta esas instrucciones de forma incremental. Te recomendamos que programes esta secuencia de comandos u otra similar como una de tus tareas de mantenimiento periódicas.

Formatear discos secundarios

Práctica recomendada: Formatee 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 se componen de ocho páginas de memoria contiguas que también tienen un tamaño de 8 KB. Si se da formato a un disco con una unidad de asignación de 64 KB, SQL Server puede leer y escribir extensiones de forma más eficiente, lo que aumenta el rendimiento de E/S del 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

Creando copia de seguridad

Práctica recomendada: haz copias de seguridad de tus datos con regularidad mediante las soluciones de copia de seguridad y recuperación ante desastres de Google para disfrutar de una protección óptima. Te recomendamos que hagas una copia de seguridad de tus datos al menos una vez al día.

Las soluciones de copia de seguridad y recuperación tras fallos de Google ofrecen las siguientes ventajas para Microsoft SQL Server:

  • Copia de seguridad incremental para siempre eficiente con recuperación en un momento dado que ayuda a realizar copias de seguridad en menos tiempo que las copias de seguridad convencionales, al tiempo que reduce el impacto en los servidores de producción. También reduce el consumo de ancho de banda y de almacenamiento para conseguir un objetivo de punto de recuperación (RPO) y un coste total de propiedad (TCO) bajos.
  • Montaje y migración de recuperaciones (M&M) para copias de seguridad almacenadas en Cloud Storage para RTOs bajos.
  • Integración completa con las funciones de SQL Server, incluida la compatibilidad con clústeres de grupos de disponibilidad de SQL Server y varias opciones de recuperación en diferentes situaciones.
  • Panel de gestión centralizado que incluye funciones de monitorización, alertas y generación de informes específicas para todas tus copias de seguridad.

Más información:

Supervisión

Práctica recomendada: Usa Cloud Monitoring.

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

Si usas las funciones de recogida de datos, puedes ajustar la información que quieras monitorizar y enviarla al almacén de datos de gestión integrado. El almacén de datos de gestión puede ejecutarse en el mismo servidor que estás monitorizando o los datos se pueden transmitir a otra instancia de SQL Server que ejecute el almacén.

Carga de datos en bloque

Práctica recomendada: Usa una base de datos independiente para organizar y transformar los datos en bloque antes de moverlos a los servidores de producción.

Es probable que tengas que cargar grandes cantidades de datos en tu sistema al menos una vez, si no de forma periódica. Se trata de una operación que requiere muchos recursos, por lo que es posible que alcances el límite de IOPS de disco persistente al realizar cargas masivas.

Hay una forma sencilla de reducir el consumo de E/S de disco y de CPU de las operaciones de carga masiva, con la ventaja añadida de acelerar el tiempo de ejecución de los trabajos por lotes. La solución consiste en crear una base de datos completamente independiente que utilice el modelo de recuperación Simple y, a continuación, usar esa base de datos para organizar y transformar el conjunto de datos en bloque 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. Si usas una SSD local para la base de datos de recuperación, se reduce el consumo de recursos de tus operaciones masivas y el tiempo necesario para completar los trabajos. La última ventaja es que la tarea de copia de seguridad de los datos de producción no tendrá que crear copias de seguridad de todas esas operaciones masivas en el registro de transacciones, por lo que será más pequeña y se ejecutará más rápido.

Validar la configuración

Práctica recomendada: prueba la configuración para validar que funciona como se espera.

Cada vez que configures un sistema nuevo, debes validar la configuración y realizar algunas pruebas de rendimiento. Este procedimiento almacenado es un recurso muy útil para evaluar la configuración de SQL Server. Más adelante, dedica un tiempo a leer sobre las marcas de configuración y ejecuta el procedimiento.

Optimizar SQL Server Enterprise Edition

SQL Server Enterprise Edition tiene una larga lista de funciones añadidas en comparación con Standard Edition. Si vas a migrar una licencia aGoogle Cloud, hay algunas opciones de rendimiento que puedes implementar.

Usar tablas comprimidas

Práctica recomendada: Habilita la compresión de tablas e índices.

Puede parecer contradictorio que comprimir tablas pueda hacer que tu sistema funcione más rápido, pero, en la mayoría de los casos, eso es lo que ocurre. La ventaja es que se usa una pequeña cantidad de ciclos de CPU para comprimir los datos y eliminar las operaciones de E/S de disco adicionales necesarias para leer y escribir los bloques más grandes. Por lo general, cuanto menos E/S de disco use tu sistema, mejor será su rendimiento. Las instrucciones para estimar y habilitar la compresión de tablas e índices se encuentran en el sitio web de MSDN.

Habilitar la extensión del grupo de búferes

Práctica recomendada: Usa la extensión del grupo de almacenamiento intermedio para acelerar el acceso a los datos.

El grupo de búferes es donde el sistema almacena páginas limpias. En pocas palabras, almacena copias de tus datos, reflejando su aspecto en el disco. Cuando los datos cambian en la memoria, se denomina página sucia. Las páginas sucias deben escribirse en el disco para guardar los cambios. Si tu base de datos es más grande que la memoria disponible, se ejercerá presión sobre el grupo de búferes y es posible que se eliminen páginas limpias. Cuando se eliminan las páginas limpias, el sistema debe leer del disco la próxima vez que acceda a los datos eliminados.

La función de extensión del búfer de memoria te permite enviar páginas limpias a un SSD local en lugar de eliminarlas. Funciona de forma similar a la memoria virtual, es decir, mediante el intercambio, y te da acceso a las páginas limpias del SSD local, que es más rápido que ir al disco normal para obtener los datos.

Esta técnica no es tan rápida como tener suficiente memoria, pero puede aumentar ligeramente el rendimiento cuando la memoria disponible es baja. Puedes leer más información sobre las extensiones del grupo de búferes y consultar algunos resultados de pruebas comparativas en el sitio de Brent Ozar.

Optimizar las licencias de SQL Server

Multihilo simultáneo (SMT)

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

El multihilo simultáneo (SMT), conocido como tecnología Hyper-Threading (HTT) en los procesadores Intel, es una función que permite que un único núcleo de CPU se comparta lógicamente como dos hilos. En Compute Engine, la función SMT está habilitada en la mayoría de las VMs de forma predeterminada, lo que significa que cada vCPU de la VM se ejecuta en un solo hilo y que cada núcleo de CPU física se comparte entre dos vCPUs.

En Compute Engine, puedes configurar el número de hilos por núcleo, lo que desactiva SMT. Cuando el número de hilos por núcleo es 1, las vCPUs no comparten núcleos de CPU físicos. Esta configuración influye significativamente en los costes de las licencias de Windows Server y SQL Server. Si el número de hilos por núcleo es 1, el número de vCPUs de una VM se reduce a la mitad, lo que también reduce a la mitad el número de licencias de Windows Server y SQL Server necesarias. Esto puede reducir significativamente el coste total de la carga de trabajo.

Sin embargo, configurar el número de hilos por núcleo también influye en el rendimiento de la carga de trabajo. Las aplicaciones que se escriben para ser multihilo pueden aprovechar esta función dividiendo el trabajo de computación en fragmentos más pequeños que se pueden paralelizar y que se programan en varios núcleos lógicos. Esta paralelización del trabajo suele aumentar el rendimiento general del sistema al utilizar mejor los recursos de los núcleos disponibles. Por ejemplo, si un hilo está bloqueado, el otro puede utilizar el núcleo.

El impacto exacto del SMT en el rendimiento de SQL Server depende de las características de la carga de trabajo y de la plataforma de hardware utilizada, ya que la implementación del SMT varía entre las generaciones de hardware. Las cargas de trabajo con un gran volumen de transacciones pequeñas, como las cargas de trabajo OLTP, suelen aprovechar la tecnología SMT y beneficiarse de un mayor aumento del rendimiento. Por el contrario, las cargas de trabajo que se pueden paralelizar menos, como las cargas de trabajo de procesamiento analítico en línea (OLAP), se benefician menos de SMT. Aunque estos patrones se han observado de forma general, te recomendamos que evalúes el impacto en el rendimiento de SMT en cada carga de trabajo para determinar el impacto de definir el número de hilos por núcleo en 1.

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

Ejemplo: supongamos que se ha implementado un servidor SQL en la configuración n2-standard-16

De forma predeterminada, el número de núcleos visibles en el sistema operativo es 16, lo que significa que se necesitan 16 vCPUs de Windows Server y 16 vCPUs 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 nueva configuración 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 se ven 8 núcleos en el sistema operativo, el servidor solo necesita 8 vCPUs para que se ejecuten Windows Server y SQL Server.

Siguientes pasos