Mejores prácticas para instancias de SQL Server

Organízate con las colecciones Guarda y clasifica el contenido según tus preferencias.

Puedes aplicar varias prácticas recomendadas para optimizar las instancias de Compute Engine que ejecutan Microsoft SQL Server. Para aprender cómo configurar una instancia de SQL Server de alto rendimiento, lea Creación de una instancia de SQL Server de alto rendimiento .

Configurando ventanas

Esta sección cubre 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.

Configurar el firewall de Windows

Mejores prácticas: utilice el Firewall avanzado de Windows Server y especifique las direcciones IP de sus computadoras cliente.

El Firewall avanzado de Windows es un componente de seguridad importante en Windows Server. Cuando configura su entorno de SQL Server para que pueda conectarse a la base de datos desde otras máquinas cliente, configure 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 utiliza esta regla de firewall, es una buena práctica especificar la dirección IP de sus máquinas cliente. Especifique una lista de direcciones IP delimitadas por comas sin espacios en blanco para el parámetro remoteip en lugar de LOCAL_SUBNET . Además, tenga en cuenta que la ruta del parámetro del program puede cambiar según la versión de SQL Server que utilice.

La imagen de la aplicación SQL Server incluye una regla de firewall de Windows SQL Server . Esta regla no tiene restricciones, así que considere deshabilitarla antes de que su sistema entre en producción.

Ajuste de conexiones de red

Mejores prácticas: utilice 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 conexiones en computadoras pequeñas que están conectadas a redes moderadamente rápidas. Estos ajustes suelen ser suficientes. Además, los valores predeterminados conservadores garantizan que el tráfico de la red no abrume la red ni las computadoras conectadas.

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

Instalación de antivirus

Mejores prácticas: siga las instrucciones de Microsoft para software antivirus.

Si está ejecutando Windows, debería ejecutar algún software antivirus. El malware y los virus de software presentan un riesgo significativo para cualquier sistema conectado a una red, y el software antivirus es un paso de mitigación simple que puede utilizar para proteger sus datos. Sin embargo, si el software antivirus no está configurado correctamente, puede afectar negativamente el rendimiento de su base de datos. Microsoft brinda consejos sobre cómo elegir el software antivirus .

Optimización del rendimiento y la estabilidad

Esta sección proporciona información sobre cómo optimizar el rendimiento de SQL Server en Compute Engine y describe las actividades operativas para ayudar a que siga funcionando sin problemas.

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

Mejores prácticas: utilice un disco persistente SSD independiente para los archivos de registro y de datos.

De forma predeterminada, la imagen preconfigurada para SQL Server viene con todo lo instalado en el disco persistente de arranque, que se monta como la unidad `C:`. Considere conectar un disco persistente SSD secundario y mover los archivos de registro y de datos al nuevo disco.

Usar un SSD local para mejorar IOPS

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

La naturaleza efímera de la tecnología SSD local la convierte en una mala candidata para su uso con bases de datos críticas y archivos importantes. Sin embargo, el archivo de paginación tempdb y Windows 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 sus discos persistentes SSD. Para obtener más información sobre cómo configurar esto, consulte Configuración de TempDB .

Procesamiento de consultas en paralelo

Mejores prácticas: establezca el max degree of parallelism en 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 todos en diferentes vCPU y luego consolidarlo todo en un solo resultado lleva mucho más tiempo que si solo una vCPU hubiera ejecutado la consulta. En la práctica, 8 funciona como un buen valor predeterminado.

Mejores prácticas: supervise las esperas de CXPACKET y aumente gradualmente 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 necesarios para realizar una consulta con un plan de ejecución en serie antes de que se considere para un plan de ejecución en paralelo. El valor predeterminado es 5. Aunque no damos ninguna recomendación específica para cambiar el valor predeterminado, vale la pena vigilarlo y, si es necesario, aumentarlo gradualmente en 5 durante las pruebas de carga. Un indicador clave de que es posible que sea necesario aumentar este valor es la presencia de esperas CXPACKET . Aunque la presencia de esperas CXPACKET no indica necesariamente que esta configuración deba cambiar, es un buen punto de partida.

Mejores prácticas: supervise los diferentes tipos de espera y ajuste la configuración de procesamiento paralelo global o configúrela en el nivel de base de datos individual.

Las bases de datos individuales pueden tener diferentes necesidades de paralelismo. Puede establecer estas configuraciones globalmente y configurar Max DOP en el nivel de base de datos individual. Debe observar sus cargas de trabajo únicas, monitorear las esperas y luego 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 ayudarle a comprender qué está esperando y cómo mitigar los retrasos.

Manejo de registros de transacciones

Mejores prácticas: Supervise el crecimiento del registro de transacciones en su sistema. Considere deshabilitar el crecimiento automático y configurar su archivo de registro en un tamaño fijo, según su acumulación de registros diaria promedio.

Una de las fuentes de pérdida de rendimiento y desaceleraciones intermitentes que más se pasa por alto es el crecimiento no administrado del registro de transacciones. Cuando su base de datos está configurada para utilizar el modelo de recuperación Full , puede realizar una restauración en cualquier momento, pero sus 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 para agregar más espacio vacío para escribir más transacciones y bloquea toda la actividad en la base de datos hasta que finalice. SQL Server aumenta cada archivo de registro según su tamaño máximo de archivo y la configuración de crecimiento del archivo .

Cuando el archivo ha alcanzado 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 para Crecimiento de archivos 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 porque cuanto más grande sea el archivo, más tiempo llevará crear el nuevo espacio vacío.

Mejores prácticas: programe copias de seguridad periódicas del registro de transacciones.

Independientemente del tamaño máximo y la configuración de crecimiento, programe copias de seguridad regulares del registro de transacciones , que, de forma predeterminada, trunca las entradas de registro antiguas y permite que el sistema reutilice el espacio de archivos existente. Esta sencilla tarea de mantenimiento puede ayudar a evitar caídas de rendimiento en las horas de mayor tráfico.

Optimización de archivos de registro virtuales

Mejores prácticas: Supervise el crecimiento del archivo de registro virtual y tome medidas para evitar la fragmentación del archivo de registro.

El archivo de registro de transacciones físicas está segmentado en archivos de registro virtuales (VLF). Se crean nuevos VLF cada vez que el archivo de registro de transacciones físicas tiene que crecer. Si no deshabilitó 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 negativamente al rendimiento.

SQL Server 2014 introdujo un algoritmo más eficiente para determinar cuántos VLF crear durante el crecimiento automático. Generalmente, si el crecimiento es inferior a 1/8 del tamaño del archivo de registro actual, SQL Server crea un VLF dentro de ese nuevo segmento. Anteriormente, crearía 8 VLF para un crecimiento de entre 64 MB y 1 GB, y 16 VLF para un crecimiento de más de 1 GB. Puede utilizar el script TSQL a continuación para comprobar cuántos VLF tiene actualmente su base de datos. Si tiene miles de archivos, considere reducir y cambiar el tamaño manualmente de su archivo de registro.

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

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

Evitar la fragmentación del índice

Mejores prácticas: desfragmente periódicamente los índices de las tablas más modificadas.

Los índices de sus tablas pueden fragmentarse, lo que puede provocar un rendimiento deficiente de cualquier consulta que utilice estos índices. Un programa de mantenimiento regular debe incluir la reorganización de los índices en las tablas más modificadas. Puede ejecutar el siguiente script Transact-SQL para que su base de datos muestre los índices y su porcentaje de fragmentación. Puede ver en los resultados del ejemplo que el índice PK_STOCK está fragmentado en un 95%. En la siguiente declaración 'SELECT', reemplace ' YOUR_DB ' con el nombre de su 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 sus índices están demasiado fragmentados, puede reorganizarlos utilizando un script ALTER básico. Aquí hay un script de ejemplo que imprime las declaraciones ALTER que puede ejecutar para cada uno de los índices de sus tablas:

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

Elija las tablas del conjunto de resultados que tengan la mayor fragmentación y luego ejecute esas declaraciones de forma incremental. Considere programar este script o uno similar como uno de sus trabajos de mantenimiento habituales.

Formatear discos secundarios

Mejores prácticas: formatee los discos secundarios con una unidad de asignación de 64 KB.

SQL Server almacena 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. 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 del disco.

Para formatear discos secundarios con una unidad de asignación de 64 KB, ejecute el siguiente comando de PowerShell, que busca todos los discos nuevos y no inicializados en un sistema y formatea los discos 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

Copia de seguridad

Mejores prácticas: haga una copia de seguridad de sus datos periódicamente utilizando las soluciones de copia de seguridad y recuperación ante desastres de Google para una protección óptima. Recomendamos hacer una copia de seguridad de sus datos al menos una vez al día.

Las soluciones de respaldo y recuperación ante desastres de Google brindan los siguientes beneficios para Microsoft SQL Server:

• Copia de seguridad incremental permanente y eficiente con recuperación en un momento real que ayuda a realizar la copia 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 almacenamiento para lograr un objetivo de punto de recuperación (RPO) y un costo total de propiedad (TCO) bajos.
• Monte y migre recuperaciones (M&M) para copias de seguridad almacenadas en Cloud Storage para un RTO bajo.
• Integración integral con capacidades de SQL Server, incluida la compatibilidad con clústeres de grupos de disponibilidad de SQL Server y múltiples opciones de recuperación en todos los escenarios.
• Panel central de administración que incluye capacidades dedicadas de monitoreo, alertas y generación de informes para todas sus copias de seguridad.

Más información:

• Descripción general del producto del servicio de respaldo y recuperación ante desastres
• Lista de funciones para el servicio de copia de seguridad y recuperación ante desastres
• Proteja y recupere bases de datos de Microsoft SQL Server
• Precios del servicio de respaldo y recuperación ante desastres
• Calculadora de precios

Escucha

Mejores prácticas: utilizar Cloud Monitoring.

Puede instalar el agente de Cloud Monitoring para Microsoft Windows para enviar varios puntos de datos de monitoreo al sistema Cloud Monitoring.

Al utilizar las capacidades de recopilación de datos , puede ajustar la información que desea monitorear y enviarla al almacén de datos de administración integrado . El almacén de datos de administración puede ejecutarse en el mismo servidor que está monitoreando o los datos pueden transmitirse a otra instancia de SQL Server que ejecute el almacén.

Carga masiva de datos

Mejores prácticas: utilice una base de datos separada para preparar y transformar datos masivos antes de moverlos a servidores de producción.

Es probable que necesite cargar grandes cantidades de datos en su sistema al menos una vez, si no con regularidad. Esta es una operación que consume muchos recursos y es posible que alcance el límite de IOPS del disco persistente cuando realiza cargas masivas.

Existe una forma sencilla de reducir el consumo de E/S de disco y de CPU de las operaciones de carga masiva, con el beneficio adicional de acelerar el tiempo de ejecución de los trabajos por lotes. La solución es crear una base de datos completamente separada que utilice el modelo de recuperación Simple y luego usar esa base de datos para preparar y transformar el conjunto de datos masivo antes de insertarlo en su 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 sus operaciones masivas y el tiempo necesario para completar los trabajos. El beneficio final es que su trabajo de respaldo 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.

Validando su configuración

Mejores prácticas: Pruebe su configuración para validar que funcione según lo esperado.

Siempre que configure un nuevo sistema, debe planear validar la configuración y ejecutar algunas pruebas de rendimiento. Este procedimiento almacenado es un gran recurso para evaluar la configuración de SQL Server. Tómese un tiempo más tarde para leer acerca de los indicadores de configuración y ejecute el procedimiento.

Optimización de SQL Server Enterprise Edition

SQL Server Enterprise Edition tiene una larga lista de capacidades agregadas sobre Standard Edition. Si está migrando una licencia existente aGoogle Cloud, existen algunas opciones de rendimiento que podría considerar implementar.

Usando tablas comprimidas

Mejores prácticas: habilite la compresión de tablas e índices.

Puede parecer contradictorio que comprimir tablas pueda hacer que su sistema funcione más rápido, pero, en la mayoría de los casos, eso es lo que sucede. La compensación es utilizar una pequeña cantidad de ciclos de CPU para comprimir los datos y eliminar la E/S de disco adicional necesaria para leer y escribir los bloques más grandes. Generalmente, cuanto menos E/S de disco utilice su sistema, mejor será su rendimiento. Las instrucciones para estimar y habilitar la compresión de índices y tablas se encuentran en el sitio web de MSDN .

Habilitación de la extensión del grupo de búfer

Mejores prácticas: utilice la extensión del grupo de búfer para acelerar el acceso a los datos.

El grupo de buffer es donde el sistema almacena páginas limpias . En términos simples, almacena copias de sus datos, reflejando su apariencia en el disco. Cuando los datos cambian en la memoria, se llama página sucia . Las páginas sucias se deben vaciar en el disco para guardar los cambios. Cuando su base de datos es más grande que su memoria disponible, eso ejerce presión sobre el grupo de búfer y es posible que se eliminen páginas limpias. Cuando se eliminan las páginas limpias, el sistema debe leer desde el disco la próxima vez que acceda a los datos eliminados.

La función de extensión del grupo de búfer le permite enviar páginas limpias a un SSD local, en lugar de descartarlas. Esto funciona de la misma manera que la memoria virtual, es decir, mediante intercambio , y le brinda acceso a las páginas limpias en el SSD local, lo cual es más rápido que ir al disco normal para recuperar los datos.

Esta técnica no es tan rápida como tener suficiente memoria, pero puede brindarle un modesto aumento en el rendimiento cuando la memoria disponible es baja. Puede leer más sobre las extensiones del grupo de almacenamiento intermedio y revisar algunos resultados de evaluaciones comparativas en el sitio de Brent Ozar .

Optimización de las licencias de SQL Server

Multiproceso simultáneo (SMT)

Mejores prácticas: establezca el número de subprocesos por núcleo en 1 para la mayoría de las cargas de trabajo de SQL Server

El multiproceso simultáneo (SMT), comúnmente conocido como tecnología Hyper-Threading (HTT) en los procesadores Intel, es una característica que permite compartir lógicamente un único núcleo de CPU como dos subprocesos. En Compute Engine, SMT está habilitado en la mayoría de las VM de forma predeterminada, lo que significa que cada vCPU en la VM se ejecuta en un solo subproceso y cada núcleo de CPU físico es compartido por dos vCPU.

En Compute Engine, puedes configurar la cantidad de subprocesos por núcleo , lo que efectivamente desactiva SMT. Cuando la cantidad de subprocesos por núcleo se establece en 1, las vCPU no comparten núcleos de CPU físicos. Esta configuración afecta significativamente los costos de licencia para Windows Server y SQL Server. Cuando la cantidad de subprocesos por núcleo se establece en 1, la cantidad de vCPU 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 requeridas. Esto puede reducir significativamente el costo total de la carga de trabajo.

Sin embargo, configurar la cantidad de subprocesos por núcleo también afecta el rendimiento de la carga de trabajo. Las aplicaciones escritas para ser multiproceso pueden aprovechar esta característica dividiendo el trabajo informático en fragmentos paralelizables más pequeños que se programan en múltiples núcleos lógicos. Esta paralelización del trabajo a menudo aumenta el rendimiento general del sistema al utilizar mejor los recursos centrales disponibles. Por ejemplo, cuando un subproceso se detiene, el otro subproceso puede utilizar 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 utilizada porque la implementación de SMT difiere entre generaciones de hardware. Las cargas de trabajo con un gran volumen de pequeñas transacciones, por ejemplo, las cargas de trabajo OLTP, a menudo pueden aprovechar SMT y beneficiarse de un mayor aumento de rendimiento. Por el contrario, las cargas de trabajo que son menos paralelizables, por ejemplo las cargas de trabajo OLAP, se benefician menos de SMT. Aunque estos patrones se han observado en general, considere evaluar el impacto en el rendimiento de SMT por carga de trabajo para determinar el impacto de establecer el número 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 el número de subprocesos por núcleo en 1. Cualquier disminución del rendimiento se puede compensar utilizando una máquina virtual más grande. En la mayoría de los casos, la reducción del 50 % en el costo de la licencia es mayor que el aumento del costo de la máquina virtual más grande.

Ejemplo: Considere que SQL Server está implementado 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 vCPU de Windows Server y 16 vCPU 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 deshabilitar SMT en SQL Server, la nueva configuración es:

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 hay 8 núcleos visibles en el sistema operativo, el servidor solo requiere 8 vCPU para ejecutar Windows Server y SQL Server.

¿Qué sigue?

• Crear una instancia de SQL Server de alto rendimiento
• Crear instancias de SQL Server
• Creando instancias de Windows