Recomendar configuraciones de base de datos

Completados

Para poder ajustar las consultas o solucionar problemas de simultaneidad, necesita la infraestructura adecuada subyacente. Azure SQL Database proporciona dos modelos de recursos y varios niveles de servicio. La combinación que elija establece el límite máximo en el proceso, la memoria, la E/S y el almacenamiento de la carga de trabajo. Si escoges demasiado poco, el rendimiento se ve afectado. Elige demasiado y desperdicias presupuesto.

Comparación de modelos de recursos

Azure SQL Database admite dos modelos de recursos: núcleo virtual y DTU. Miden y facturan recursos de forma diferente, por lo que comprender la distinción le ayuda a tomar la elección correcta desde el principio.

El modelo de núcleo virtual proporciona control directo sobre núcleos virtuales, memoria y almacenamiento. Elige la generación de hardware, el nivel de servicio y el nivel de proceso de forma independiente. Si va a migrar desde SQL Server local, este modelo se asigna limpiamente a la CPU y la memoria físicas, lo que hace que la planificación de la capacidad sea más sencilla. También admite los precios de las instancias reservadas y la Ventaja híbrida de Azure para ahorrar costos.

El modelo DTU agrupa la CPU, la memoria y la E/S en una sola unidad denominada Unidad de transacción de base de datos (DTU). Los niveles basados en DTU (Básico, Estándar y Premium) ofrecen paquetes de recursos preconfigurados. Este modelo funciona cuando no necesita un control específico sobre las dimensiones de recursos individuales.

Para la mayoría de las implementaciones nuevas, Microsoft recomienda el modelo de núcleo virtual. Proporciona mayores límites de recursos, mayor granularidad de escalado y mayor flexibilidad de precios.

Descripción de los niveles de servicio en el modelo de núcleo virtual

El modelo de núcleo virtual tiene tres niveles de servicio: Uso general, Crítico para la empresa e Hiperescala. Cada nivel usa una arquitectura diferente, que afecta al tipo de almacenamiento, el rendimiento de E/S y la disponibilidad.

General Purpose separa el cómputo y el almacenamiento. El motor de base de datos se ejecuta en un nodo de proceso mientras los archivos de datos residen en Azure Blob Storage. La latencia de almacenamiento suele estar entre 5 milisegundos y 10 milisegundos. Esta arquitectura proporciona precios asequibles y funciona bien para la mayoría de las cargas de trabajo empresariales. Si se produce un error en el nodo de proceso, Azure Service Fabric mueve el proceso a un nodo de reserva y vuelve a asociar los archivos de almacenamiento remoto.

Considere la aplicación de comercio electrónico de la introducción. Durante las horas laborables normales, General Purpose controla el volumen de pedidos sin problema. Pero durante una venta flash en festivos, es posible que la latencia de E/S de 5 a 10 milisegundos no sea lo suficientemente rápida para el flujo del proceso de pago.

Business Critical integra procesamiento y almacenamiento en cada nodo. Tanto el motor de base de datos como los archivos de datos usan SSD conectados localmente en un grupo de disponibilidad AlwaysOn con tres réplicas secundarias. Este diseño proporciona la latencia de E/S más baja (de 1 milisegundos a 2 milisegundos en promedio), la IOPS más alta (operaciones de entrada y salida por segundo) y una réplica de solo lectura gratuita que puede usar para las consultas de informes. La compensación es el coste, aproximadamente 2,7 veces más que la opción De uso general con el mismo número de núcleos virtuales. Para el equipo de comercio electrónico, Business Critical tiene sentido si sus transacciones de pago necesitan una latencia coherente de menos de 2 milisegundos.

Hiperescala usa una arquitectura de almacenamiento desacoplada con servidores de página independientes y una caché de varios niveles. Admite bases de datos de hasta 128 TB, permite de cero a cuatro réplicas de alta disponibilidad y escala los recursos de computación hacia arriba o hacia abajo sin copiar datos. Solo se le factura el almacenamiento asignado, no el almacenamiento máximo. Hiperescala quita los límites prácticos de almacenamiento y escalado de los demás niveles y es adecuado para la mayor variedad de cargas de trabajo.

En la tabla siguiente se resumen las diferencias clave:

Característica General Purpose Crítico para la empresa Hiperescala
Tipo de almacenamiento Remoto (Azure Blob Storage) SSD local Desacoplado con caché SSD local
Almacenamiento máximo 4 TB 4 TB 128 TB
Número máximo de IOPS por núcleo virtual 320 4\.000 5.500 (SSD local)
Réplicas de disponibilidad 1 (sin réplicas de lectura) 3 + 1 réplica de lectura De 0 a 4 (configurable)
Más adecuado para Cargas de trabajo orientadas al presupuesto Baja latencia, E/S elevada Bases de datos grandes, escalado flexible

Elija un nivel de computación

En el modelo de núcleo virtual, también puede elegir entre dos niveles de proceso: aprovisionados y sin servidor.

La provisión computacional asigna un número fijo de núcleos virtuales que permanecen disponibles independientemente de la actividad de la carga de trabajo. Usted paga una tarifa por hora fija. Este nivel se ajusta a las cargas de trabajo con un consumo de recursos coherente o predecible, como la aplicación de comercio electrónico que procesa los pedidos durante todo el día.

El proceso sin servidor escala automáticamente los núcleos virtuales en función de la demanda y factura por segundo por el proceso usado. Cuando la base de datos está inactiva, puede autopausarse y eliminar los costos de cómputo por completo, aunque la autopausa solo se admite actualmente en General Purpose. El propio cómputo sin servidor está disponible tanto para los niveles de Uso General como de Hiperescala. Funciona bien para entornos de desarrollo, herramientas internas o aplicaciones con tráfico intermitente.

Ajusta la configuración a tu carga de trabajo

Ahora que comprende las opciones, ¿cómo decide? Evalúe la carga de trabajo con estos factores:

  • Requisitos de latencia: si la aplicación necesita latencia de E/S con menos de 2 milisegundos de forma coherente, elija Crítico para la empresa. Para la tolerancia de latencia moderada, la opción De uso general es suficiente.
  • Tamaño de almacenamiento: si la base de datos supera los 4 TB o espera un crecimiento rápido, Hiperescala es la única opción que admite hasta 128 TB.
  • Cargas de trabajo intensivas de lectura: la opción Crítico para la empresa incluye una réplica de solo lectura gratuita. La opción Hiperescala admite réplicas con nombre para el escalado horizontal de lectura flexible.
  • Sensibilidad a los costos: Propósito General con cómputo aprovisionado ofrece precios predecibles. La computación sin servidor en Propósito General o Hiperescala reduce los costos de las cargas de trabajo esporádicas.
  • Requisitos de disponibilidad: la opción Crítico para el negocio proporciona la máxima resiliencia con tres réplicas sincronizadas y la conmutación por error más rápida. Hiperescala le permite configurar el número de réplicas para equilibrar la resistencia con el costo.

Sugerencia

Al migrar desde SQL Server local, use el modelo vCore porque se corresponde directamente con la CPU y la memoria físicas. El modelo de DTU no expone dimensiones de recursos individuales, lo que dificulta el planeamiento de la capacidad para las migraciones.

Configuración de los valores de nivel de base de datos

Ha elegido su nivel y modelo de computación. Ahora busque dentro de la propia base de datos. Varias opciones de configuración afectan a cómo Azure SQL Database controla el paralelismo, la optimización de consultas y la recuperación. Puede ajustar esta configuración sin cambiar el nivel de servicio.

Control del paralelismo con MAXDOP

El grado máximo de paralelismo (MAXDOP) controla cuántos subprocesos de procesador asigna el motor a una sola consulta. Azure SQL Database tiene como valor predeterminado 8, que funciona para la mayor variedad de cargas de trabajo. Antes de septiembre de 2020, las nuevas bases de datos tenían como valor predeterminado 0, paralelismo ilimitado, lo que causó problemas. Una sola consulta analítica podría consumir todos los subprocesos disponibles, dejando al flujo de compra sin recursos de CPU.

Establezca MAXDOP en el nivel de base de datos con ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP. También puede establecer un valor diferente para las réplicas secundarias cuando las cargas de trabajo de lectura-escritura y de solo lectura tienen diferentes necesidades de concurrencia. Para una consulta específica, use la OPTION (MAXDOP) sugerencia . La única regla: evite MAXDOP 0 en producción. El paralelismo ilimitado conduce al agotamiento de recursos, a los tiempos de espera de consulta y a las interrupciones de aplicaciones.

Permitir que el ajuste automático capture regresiones

El optimizador de consultas no siempre elige el mejor plan. Las estadísticas pasan obsoletas, las distribuciones de datos cambian y un plan que fue rápido ayer se vuelve lento hoy. El ajuste automático supervisa el rendimiento de las consultas y aplica correcciones sin esperar a que se note.

Azure SQL Database admite tres opciones:

  • FORCE_LAST_GOOD_PLAN detecta regresiones de plan y fuerza el plan rápido anterior. Habilitado de forma predeterminada.
  • CREATE_INDEX identifica los índices que faltan, los crea y comprueba la mejora. Deshabilitado de forma predeterminada.
  • DROP_INDEX quita índices sin usar y duplicados. Deshabilitado de forma predeterminada. Los índices únicos, incluidos los índices compatibles con la clave principal y las restricciones únicas, nunca se eliminan.

Cada cambio pasa por una ventana de validación, de 30 minutos a 72 horas en función de la frecuencia de consulta. Si el rendimiento empeora, el cambio se revierte automáticamente.

ALTER DATABASE CURRENT
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON,
                      CREATE_INDEX = ON,
                      DROP_INDEX = OFF);

Imagina la aplicación de comercio electrónico durante una venta especial de vacaciones. Los patrones de consulta cambian a medida que las distintas páginas de producto se disparan en popularidad. FORCE_LAST_GOOD_PLAN detecta automáticamente esas regresiones, por lo que un cambio de plan incorrecto a las 2 a.m. no ralentiza el proceso de pago hasta que alguien se dé cuenta el lunes por la mañana. Es probable que quiera dejar fuera CREATE_INDEX y DROP_INDEX hasta que se haya revisado lo que se propone.

Desbloquear las funciones del optimizador con el nivel de compatibilidad

Cada base de datos tiene un nivel de compatibilidad que determina qué comportamientos del optimizador de consultas están disponibles. Las nuevas bases de datos de Azure SQL Database están predeterminadas en el nivel 170 o en el nivel más alto disponible. Cada nivel desbloquea un conjunto de características de procesamiento de consultas inteligentes (IQP):

  • Nivel 150: modo batch en rowstore, compilación diferida de variables de tabla, inserción en línea de funciones escalares definidas por el usuario (UDF).
  • Nivel 160: optimización de planes sensibles a parámetros (PSP), retroalimentación de estimación de cardinalidad.
  • Nivel 170: optimización opcional del plan de parámetros.

Las bases de datos existentes se pueden ejecutar en un nivel de compatibilidad inferior porque Microsoft nunca actualiza automáticamente esta configuración. Una base de datos creada cuando un valor predeterminado inferior estaba en vigor mantiene su nivel original. ** Por ejemplo, si creó una base de datos de Azure SQL en 2024, la base de datos permanece en el nivel 160 si el nivel no se actualiza manualmente. Del mismo modo, si importó una base de datos a través de un archivo BACPAC, el nivel de compatibilidad de la base de datos importada se basa en el nivel de compatibilidad de la base de datos de origen. Para subir:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 170;

No cambie esta configuración ciegamente en producción. Utilice el Almacén de Consultas para capturar una línea base de rendimiento en el nivel actual, actualizar en un entorno de prueba y comparar. Si una consulta retrocede, puede forzar el plan anterior mientras investiga.

Reducción del sobredimensionamiento de la memoria caché del plan con OPTIMIZE_FOR_AD_HOC_WORKLOADS

La aplicación de comercio electrónico genera consultas de búsqueda de productos con docenas de combinaciones de filtros. Cada texto de consulta único obtiene su propio plan compilado en la memoria caché, incluso si esa consulta nunca se ejecuta de nuevo. Con el tiempo, la memoria caché del plan se rellena con miles de planes de uso único, lo que desplaza los planes frecuentemente ejecutados que realmente importan.

OPTIMIZE_FOR_AD_HOC_WORKLOADS resuelve este problema. Cuando se habilita, el motor almacena un pequeño stub de plan compilado en la primera ejecución en lugar del plan completo. Solo cuando la misma consulta se ejecuta una segunda vez, el motor compila y almacena en caché el plan completo.

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

Esta configuración mantiene la memoria caché ajustada y garantiza que los planes para las consultas más importantes permanezcan residentes en la memoria.

Descripción de la recuperación acelerada de bases de datos

La recuperación acelerada de bases de datos siempre está habilitada en Azure SQL Database. No se puede desactivar y no es necesario. La recuperación acelerada de bases de datos (ADR) rediseña el proceso de recuperación para que el tiempo de recuperación permanezca constante independientemente del número de transacciones activas que se estaban ejecutando cuando se produjo un error. También proporciona reversión instantánea de transacciones y truncamiento agresivo del registro.

ADR almacena las versiones de fila en un almacén de versiones persistente (PVS) dentro de la base de datos en lugar de en tempdb. Dependiendo del tamaño de la fila que se va a modificar, las versiones se almacenan en fila en páginas de datos o fuera de fila en una tabla interna independiente. Las cargas de trabajo con un uso intensivo de escritura pueden ver mayores divisiones de página y una mayor generación de registros porque se registra cada versión de fila. Para minimizar esa sobrecarga, mantenga las transacciones cortas y reduzca las transacciones anuladas innecesarias.

PvS comparte el almacenamiento asignado de la base de datos, por lo que un PVS creciente reduce el espacio disponible para los datos. Para supervisar la sobrecarga de PVS fuera de la fila, consulte sys.dm_tran_persistent_version_store_stats y compruebe la columna persistent_version_store_size_kb, que indica el tamaño de las versiones fuera de fila solamente y no incluye versiones en fila almacenadas en páginas de datos. Para establecer una línea base durante las cargas de trabajo típicas, compare ese valor con el tamaño total de la base de datos. Si PVS crece significativamente más allá de esa línea base, esté atento a transacciones de larga duración o a altas tasas de anulación que puedan retrasar la limpieza de versiones.

Conclusiones clave

Azure SQL Database proporciona dos modelos de recursos, tres niveles de servicio y dos niveles de proceso. El modelo de núcleo virtual con uso general cubre la mayoría de las cargas de trabajo. Crítico empresarial añade una latencia inferior a 2 milisegundos. Hiperescala quita los límites de almacenamiento y escalado. Dentro de la base de datos, MAXDOP 8 es el valor predeterminado seguro, el ajuste automático detecta regresiones del plan y la actualización del nivel de compatibilidad desbloquea las características más recientes de procesamiento de consultas inteligentes (IQP). Habilite OPTIMIZE_FOR_AD_HOC_WORKLOADS para mantener la caché del plan limpia y supervisar el uso del almacenamiento de PVS desde ADR mediante sys.dm_tran_persistent_version_store_stats, especialmente en escenarios con un uso intensivo de escritura. A continuación, explorará cómo afectan los niveles de aislamiento y el control de simultaneidad a las consultas que se ejecutan dentro de esta infraestructura.