Capítulo 1. Instalación y configuración de SQL Server

Este trabajo se ha traducido utilizando IA. Agradecemos tus opiniones y comentarios: translation-feedback@oreilly.com

Base de datos Los servidores nunca viven en el vacío. Pertenecen a un ecosistema de una o varias aplicaciones utilizadas por los clientes. Las bases de datos de las aplicaciones se alojan en una o varias instancias de SQL Server, y estas instancias, a su vez, se ejecutan en hardware físico o virtual. Los datos se almacenan en discos que suelen compartirse con otros clientes y sistemas de bases de datos. Por último, todos los componentes utilizan una red para la comunicación y el almacenamiento.

La complejidad y las dependencias internas de los ecosistemas de bases de datos hacen que la resolución de problemas sea una tarea muy difícil. Desde el punto de vista del cliente, la mayoría de los problemas se presentan como problemas generales de rendimiento: las aplicaciones pueden parecer lentas y no responder, las consultas a la base de datos pueden agotarse o las aplicaciones pueden no conectarse a la base de datos. La causa de los problemas puede estar en cualquier parte. El hardware puede funcionar mal o estar mal configurado; la base de datos puede tener un esquema, una indexación o un código ineficaces; SQL Server puede estar sobrecargado; las aplicaciones cliente pueden tener errores o problemas de diseño. Esto significa que tendrás que adoptar una visión holística de todo el sistema para identificar y solucionar los problemas.

Este libro trata sobre la resolución de problemas de SQL Server. Sin embargo, siempre debes empezar el proceso de resolución de problemas analizando el ecosistema de tu aplicación y el entorno de SQL Server. Este capítulo te dará una serie de pautas sobre cómo realizar esa validación y detectar las ineficiencias más comunes en las configuraciones de SQL Server.

En primer lugar, hablaré de la configuración del hardware y del sistema operativo. A continuación, hablaré de SQL Server y de la configuración de la base de datos. También tocaré los temas de la consolidación de SQL Server y la sobrecarga que el monitoreo puede introducir en el sistema.

Consideraciones sobre el hardware y el sistema operativo

En la mayoría de los casos, los procesos de resolución de problemas y ajuste del rendimiento se producen en sistemas de producción que alojan muchos datos y funcionan con cargas pesadas. Tienes que enfrentarte a los problemas y poner a punto los sistemas en vivo. Sin embargo, es imposible evitar por completo una discusión sobre el aprovisionamiento de hardware, sobre todo porque tras la resolución de problemas puedes descubrir que tus servidores no pueden soportar la carga y necesitan actualizarse.

No voy a recomendar proveedores, piezas o números de modelo concretos; el hardware informático mejora rápidamente y cualquier consejo específico de este tipo estaría obsoleto para cuando se publique este libro. En su lugar, me centraré en consideraciones de sentido común con relevancia a largo plazo.

CPU

El coste de la licencia de un motor de base de datos comercial es, con diferencia, la parte más cara del sistema. SQL Server no es una excepción: podrías construir un servidor decente por menos del precio de venta al público de cuatro núcleos de SQL Server Enterprise Edition. Deberías comprar la CPU más potente que te permita tu presupuesto, sobre todo si utilizas una Edición no Empresarial de SQL Server, que limita el número de núcleos que puedes utilizar.

Presta atención al modelo de CPU. Cada nueva generación de CPU introducirá mejoras de rendimiento respecto a las generaciones anteriores. Puedes obtener mejoras de rendimiento del 10% al 15% sólo por elegir CPUs más nuevas, incluso cuando ambas generaciones de CPU tengan la misma velocidad de reloj.

En algunos casos, cuando el coste de las licencias no es un problema, puede que tengas que elegir entre CPU más lentas con más núcleos y CPU más rápidas con menos núcleos. En ese caso, la elección depende en gran medida de la carga de trabajo del sistema. En general, los sistemas de procesamiento transaccional en línea (OLTP), y especialmente el OLTP en memoria, se beneficiarían del mayor rendimiento de un solo núcleo. En cambio, un almacén de datos y una carga de trabajo analítica pueden funcionar mejor con un mayor grado de paralelismo y más núcleos.

Memoria

Hay un chiste en la comunidad SQL Server que dice así:

  • Q. ¿Cuánta memoria suele necesitar SQL Server?
  • A. Más.

Esta broma tiene mérito. SQL Server se beneficia de una gran cantidad de memoria, que le permite almacenar en caché más datos. Esto, a su vez, reducirá la cantidad de actividad de entrada/salida (E/S) del disco y mejorará el rendimiento de SQL Server. Por tanto, añadir más memoria al servidor puede ser la forma más barata y rápida de solucionar algunos problemas de rendimiento.

Por ejemplo, supongamos que el sistema sufre consultas no optimizadas. Podrías reducir el impacto de estas consultas añadiendo memoria y eliminando las lecturas de disco físico que introducen. Esto, obviamente, no resuelve la raíz del problema. También es peligroso, porque a medida que los datos crecen, es posible que finalmente no quepan en la caché. Sin embargo, en algunos casos puede ser aceptable como solución temporal.

La Edición Enterprise de SQL Server no limita la cantidad de memoria que puede utilizar. Las ediciones que no son Enterprise tienen limitaciones. En cuanto a la utilización de memoria, la Edición Estándar de SQL Server 2014 y posteriores puede utilizar hasta 128 GB de RAM para el grupo de búferes, 32 GB de RAM por base de datos para datos OLTP en memoria y 32 GB de RAM para almacenar segmentos de índice de almacén de columnas. El uso de memoria de la Edición Web está limitado a la mitad de lo que proporciona la Edición Estándar. Ten en cuenta estos límites en tu análisis cuando aprovisiones o actualices instancias de SQL Server que no sean de la Edición Empresarial. No olvides asignar algo de memoria adicional a otros componentes de SQL Server, como la caché de planes y el gestor de bloqueos.

Al final, añade tanta memoria como puedas permitirte. Hoy en día es barata. No es necesario sobreasignar memoria si tus bases de datos son pequeñas, pero piensa en el crecimiento futuro de los datos.

Subsistema de disco

Un subsistema de disco sano , rápido, es esencial para un buen rendimiento de SQL Server. SQL Server es una aplicación muy intensiva en E/S: está constantemente leyendo y escribiendo datos en el disco.

Hay muchas opciones para diseñar el subsistema de disco de las instalaciones de SQL Server. La clave es construirlo de forma que proporcione una baja latencia para las peticiones de E/S. Para los sistemas críticos de nivel 1, recomiendo no superar de 3 a 5 milisegundos (ms) de latencia para las lecturas y escrituras de archivos de datos, y de 1 ms a 2 ms de latencia para las escrituras del registro de transacciones. Afortunadamente, esas cifras se alcanzan ahora fácilmente con el almacenamiento basado en flash.

Sin embargo, hay un truco: cuando solucionas problemas de rendimiento de E/S en SQL Server, tienes que analizar las métricas de latencia dentro de SQL Server y no a nivel de almacenamiento. Es habitual ver cifras significativamente más altas en SQL Server que en los indicadores clave de rendimiento (KPI) del almacenamiento, debido a las colas que pueden producirse con cargas de trabajo de E/S intensivas.( Enel Capítulo 3 se explicará cómo capturar y analizar los datos de rendimiento de E/S).

Si tu subsistema de almacenamiento ofrece varios niveles de rendimiento, te recomiendo que pongas la base de datos tempdb en la unidad más rápida, seguida del registro de transacciones y los archivos de datos. La base de datos tempdb es el recurso compartido del servidor, y es esencial que tenga un buen rendimiento de E/S.

Las escrituras en los archivos de registro de transacciones son síncronas. Es fundamental tener una baja latencia de escritura para esos archivos. Las escrituras en el registro de transacciones también son secuenciales; sin embargo, recuerda que colocar varios archivos de registro y/o datos en la misma unidad provocará una E/S aleatoria en varias bases de datos.

Como buenas prácticas, yo pondría los archivos de datos y de registro en unidades físicas diferentes por razones de mantenimiento y recuperabilidad. Sin embargo, debes fijarte en la configuración de almacenamiento subyacente. En algunos casos, cuando las matrices de discos no tienen suficientes spindles, dividirlos en varios LUN puede degradar el rendimiento de la matriz de discos.

En mis sistemas, no divido los índices agrupados y no agrupados en varios grupos de archivos colocándolos en unidades diferentes. Rara vez mejora el rendimiento de E/S, a menos que puedas separar completamente las rutas de almacenamiento entre los grupos de archivos. Por otra parte, esta configuración puede complicar considerablemente la recuperación ante desastres.

Por último, recuerda que algunas tecnologías de SQL Server se benefician de un buen rendimiento de E/S secuencial. Por ejemplo, In-Memory OLTP no utiliza E/S aleatoria en absoluto, y el rendimiento de las lecturas secuenciales suele convertirse en el factor limitante para el arranque y la recuperación de la base de datos. Los escaneos en almacenes de datos también se beneficiarían del rendimiento de la E/S secuencial cuando los índices B-Tree y columnstore no están muy fragmentados. La diferencia entre el rendimiento de la E/S secuencial y aleatoria no es muy significativa con el almacenamiento basado en flash; sin embargo, puede ser un factor importante con las unidades magnéticas.

Red

SQL Server se comunica con los clientes y otros servidores a través de la red. Obviamente, necesita proporcionar suficiente ancho de banda para soportar esa comunicación. Hay un par de cosas que quiero mencionar a este respecto.

En primer lugar, debes analizar toda la topología de la red cuando resuelvas problemas de rendimiento relacionados con ella. Recuerda que el rendimiento de una red estará limitado a la velocidad de su componente más lento. Por ejemplo, puedes tener un enlace ascendente de 10 Gbps desde el servidor; sin embargo, si tienes un conmutador de 1 Gbps en la ruta de la red, ése se convertiría en el factor limitador. Esto es especialmente crítico para el almacenamiento basado en red: asegúrate de que la ruta de E/S a los discos es lo más eficiente posible.

En segundo lugar, es una práctica habitual construir una red separada para el latido del clúster en AlwaysOn Failover Clusters y AlwaysOn Availability Groups. En algunos casos, también puedes considerar construir una red separada para todo el tráfico del Grupo de Disponibilidad. Este es un buen enfoque que mejora la fiabilidad del clúster en configuraciones sencillas, cuando todos los nodos del clúster pertenecen a la misma subred y pueden utilizar enrutamiento de Capa 2. Sin embargo, en configuraciones complejas multisubred, las redes múltiples pueden provocar problemas de enrutamiento. Ten cuidado con estas configuraciones y asegúrate de utilizar adecuadamente las redes en la comunicación entre nodos, especialmente en entornos virtuales, de los que hablaré en el Capítulo 15.

La virtualización añade aquí otra capa de complejidad. Considera una situación en la que tengas un clúster virtualizado de SQL Server con nodos ejecutándose en distintos hosts. Tendrías que comprobar que los hosts pueden separar y enrutar el tráfico de la red del clúster por separado del tráfico del cliente. Servir todo el tráfico vLan a través de la única tarjeta de red física anularía el propósito de una red heartbeat.

Sistemas operativos y aplicaciones

Como regla general, te sugiero que utilices la versión más reciente de un sistema operativo compatible con tu versión de SQL Server. Asegúrate de que tanto el sistema operativo como SQL Server están parcheados, e implanta un proceso para hacer parches con regularidad.

Si utilizas una versión antigua de SQL Server (anterior a 2016), utiliza la variante de 64 bits. En la mayoría de los casos, la versión de 64 bits supera a la de 32 bits y se adapta mejor al hardware.

Desde SQL Server 2017, es posible utilizar Linux para alojar el servidor de bases de datos. Desde el punto de vista del rendimiento, las versiones Windows y Linux de SQL Server son muy similares. La elección del sistema operativo depende del ecosistema empresarial y de lo que tu equipo se sienta más cómodo soportando. Ten en cuenta que las Implementaciones basadas en Linux pueden requerir una estrategia de Alta Disponibilidad (HA) ligeramente diferente en comparación con una configuración de Windows. Por ejemplo, puede que tengas que recurrir a Pacemaker en lugar de a Windows Server Failover Cluster (WSFC) para las conmutaciones por error automáticas.

Utiliza un host de SQL Server dedicado cuando sea posible. Recuerda que es más fácil y barato escalar los servidores de aplicaciones: ¡no desperdicies recursos valiosos en el host de la base de datos!

Del mismo modo, no ejecutes procesos no esenciales en el servidor. Veo continuamente a ingenieros de bases de datos ejecutando SQL Server Management Studio (SSMS) en sesiones de escritorio remoto. Siempre es mejor trabajar a distancia y no consumir recursos del servidor.

Por último, si tienes que ejecutar un software antivirus en el servidor, excluye cualquier carpeta de la base de datos del análisis.

Virtualización y Nubes

La infraestructura informática moderna depende en gran medida de la virtualización , que proporciona flexibilidad adicional, simplifica la gestión y reduce los costes de hardware. En consecuencia, lo más frecuente es que tengas que trabajar con una infraestructura virtualizada de SQL Server.

No hay nada malo en ello. La virtualización correctamente implementada te ofrece muchas ventajas, con una sobrecarga de rendimiento aceptable. Añade otra capa de HA con VMware vSphere vMotion o Hyper-V Live Migration. Te permite actualizar el hardware sin problemas y simplifica la gestión de la base de datos. A menos que el tuyo sea un caso perimetral en el que necesites exprimir al máximo el hardware, te sugiero que virtualices tu ecosistema SQL Server.

Nota

La sobrecarga de la virtualización aumenta en servidores grandes con muchas CPU. Sin embargo, puede ser aceptable en muchos casos.

La virtualización, sin embargo, añade otra capa de complejidad durante la resolución de problemas. Tienes que prestar atención a la salud y la carga del host, además de a las métricas de la máquina virtual (VM) huésped. Para empeorar las cosas, el impacto en el rendimiento de un host sobrecargado puede no ser claramente visible en las métricas de rendimiento estándar de un SO invitado.

Trataré varios enfoques para solucionar los problemas de la capa de virtualización en el Capítulo 15; sin embargo, puedes empezar trabajando con los ingenieros de infraestructura para confirmar que el host no está sobreaprovisionado. Presta atención al número de CPU físicas y vCPU asignadas en el host, junto con la memoria física y asignada. Las máquinas virtuales de SQL Server de misión crítica deben tener recursos reservados para evitar un impacto en el rendimiento.

Aparte de la capa de virtualización, la solución de problemas de las instancias virtualizadas de SQL Server es la misma que la de las físicas. Lo mismo se aplica a las instalaciones en la nube cuando SQL Server se ejecuta dentro de máquinas virtuales. Al fin y al cabo, la nube no es más que otro centro de datos gestionado por un proveedor externo.

Configurar tu servidor SQL

La configuración por defecto del proceso de configuración de SQL Server es relativamente decente y puede ser adecuada para cargas de trabajo ligeras e incluso moderadas. Sin embargo, hay varias cosas que debes validar y ajustar.

Versión de SQL Server y nivel de parcheado

SELECT @@VERSION es la primera sentencia que ejecuto durante las comprobaciones del estado del sistema SQL Server. Hay dos razones para ello. En primer lugar, me da una idea de la estrategia de aplicación de parches del sistema, de modo que puedo sugerir potencialmente algunas mejoras. En segundo lugar, me ayuda a identificar posibles problemas conocidos que puedan existir en el sistema.

Esta última razón es muy importante. Muchas veces, los clientes me han pedido que solucione problemas que ya se habían resuelto en paquetes de servicio y actualizaciones acumulativas. Consulta siempre las notas de la versión para ver si alguno de los problemas mencionados te resulta familiar; puede que tu problema ya se haya solucionado.

Considera la posibilidad de actualizarte a la versión más reciente de SQL Server siempre que sea posible. Cada versión introduce mejoras de rendimiento, funcionales y de escalabilidad. Esto es especialmente cierto si pasas a SQL Server 2016 o posterior desde versiones anteriores. SQL Server 2016 fue una versión hito que incluyó muchas mejoras de rendimiento. Según mi experiencia, la actualización de SQL Server 2012 a 2016 o posterior puede mejorar el rendimiento entre un 20% y un 40% sin necesidad de pasos adicionales.

También merece la pena señalar que, a partir de SQL Server 2016 SP1, muchas de las antiguas funciones exclusivas de Enterprise Edition pasaron a estar disponibles en las ediciones inferiores del producto. Algunas de ellas, como la compresión de datos, permiten a SQL Server almacenar en caché más datos en el buffer pool y mejorar el rendimiento del sistema.

Obviamente, tienes que probar el sistema antes de actualizarlo: siempre existe la posibilidad de que se produzcan regresiones. El riesgo suele ser pequeño con los parches menores; sin embargo, aumenta con las actualizaciones mayores. Puedes mitigar algunos riesgos con varias opciones de base de datos, como verás más adelante en este capítulo.

Inicialización instantánea de archivos

Cada vez que SQL Server hace crecer los archivos de datos y de registro de transacciones -ya sea automáticamente o como parte del comando ALTER DATABASE -, llena de ceros la parte recién asignada del archivo. Este proceso bloquea todas las sesiones que intentan escribir en el archivo correspondiente y, en el caso del registro de transacciones, deja de generar registros. También puede generar un pico en la carga de trabajo de escritura de E/S.

Ese comportamiento no puede cambiarse para los archivos del registro de transacciones; SQL Server siempre los pone a cero. Sin embargo, puedes desactivarlo para los archivos de datos activando la inicialización instantánea de archivos (IFI). Esto acelera el crecimiento de los archivos de datos y reduce el tiempo necesario para crear o restaurar bases de datos.

Puedes activar la IFI concediendo el permiso SA_MANAGE_VOLUME_NAME, también conocido como Tarea de mantenimiento del volumen, a la cuenta de inicio de SQL Server. Esto puede hacerse en la aplicación de gestión de la Política de Seguridad Local(secpol.msc). Tendrás que reiniciar SQL Server para que el cambio surta efecto .

En SQL Server 2016 y posteriores, también puedes conceder este permiso como parte del proceso de configuración de SQL Server, como se muestra en la Figura 1-1.

Figura 1-1. Activar IFI durante la configuración de SQL Server

Puedes comprobar si la IFI está activada examinando la columna instant_file​_initializa⁠tion_enabled de la vista de gestión dinámica (DMV) sys.dm_server_services . Esta columna está disponible en SQL Server 2012 SP4, SQL Server 2016 SP1 y versiones posteriores. En versiones anteriores, puedes ejecutar el código que se muestra en el Listado 1-1.

Listado 1-1. Comprobar si la inicialización instantánea de archivos está activada en versiones antiguas de SQL Server
DBCC TRACEON(3004,3605,-1);
GO
CREATE DATABASE Dummy;
GO
EXEC sp_readerrorlog 0,1,N'Dummy';
GO
DROP DATABASE Dummy;
GO
DBCC TRACEOFF(3004,3605,-1);
GO

Si IFI no está activado, el registro de SQL Server indicará que SQL Server está poniendo a cero el archivo de datos .mdf además de poner a cero el archivo de registro .ldf, como se muestra en la Figura 1-2. Si IFI está activado, sólo mostrará la puesta a cero del archivo de registro .ldf.

Figura 1-2. Comprobar si está activada la inicialización instantánea de archivos

Existe un pequeño riesgo de seguridad asociado a esta configuración. Cuando la IFI está activada, los administradores de la base de datos pueden ver algunos datos de archivos previamente borrados en el SO al mirar las páginas de datos recién asignadas en la base de datos. Esto es aceptable en la mayoría de los sistemas; si es así, actívalo.

Configuración tempdb

La tempdb es la base de datos del sistema que se utiliza para almacenar objetos temporales creados por los usuarios y por SQL Server internamente. Se trata de una base de datos muy activa y a menudo se convierte en una fuente de contención en el sistema. En el Capítulo 9 hablaré de cómo solucionar problemas relacionados con tempdb; en este capítulo, me centraré en la configuración de.

Como ya se ha mencionado, debes colocar tempdb en la unidad más rápida del sistema. En términos generales, esta unidad no necesita ser redundante ni persistente: la base de datos se vuelve a crear al iniciar SQL Server, y un disco SSD local o un almacenamiento efímero en la nube funcionarían bien. Recuerda, sin embargo, que SQL Server se caerá si tempdb no está disponible, así que tenlo en cuenta en tu diseño.

Si utilizas una versión de SQL Server que no sea Enterprise Edition y el servidor tiene más memoria de la que SQL Server puede consumir, puedes poner tempdb en la unidad RAM. Sin embargo, no lo hagas con SQL Server Enterprise Edition: normalmente conseguirás un mejor rendimiento si utilizas esa memoria para el buffer pool.

Nota

Preasigna los archivos tempdb al tamaño máximo de la unidad RAM y crea pequeños archivos adicionales de datos y de registro en disco para evitar quedarte sin espacio. SQL Server no utilizará los archivos pequeños en disco hasta que los archivos de la unidad RAM estén llenos.

La base de datos tempdb debería tener siempre varios archivos de datos. Por desgracia, la configuración por defecto creada en la configuración de SQL Server no es óptima, especialmente en las versiones antiguas del producto. En el Capítulo 9 hablaré de cómo afinar el número de archivos de datos en tempdb, pero puedes utilizar lo siguiente como regla general en la configuración inicial:

  • Si el servidor tiene ocho o menos núcleos de CPU, crea el mismo número de archivos de datos.

  • Si el servidor tiene más de ocho núcleos de CPU, utiliza ocho archivos de datos o un cuarto del número de núcleos, lo que sea mayor, redondeando en lotes de cuatro archivos. Por ejemplo, utiliza 8 archivos de datos en el servidor de 24 núcleos y 12 archivos de datos en el servidor de 40 núcleos.

Por último, asegúrate de que todos los archivos de datos de tempdb tienen el mismo tamaño inicial y los parámetros de crecimiento automático especificados en megabytes (MB) en lugar de en porcentajes. Esto permitirá a SQL Server equilibrar mejor el uso de los archivos de datos, reduciendo la posible contención en el sistema.

Banderas de rastreo

SQL Server utiliza banderas de rastreo para activar o cambiar el comportamiento de algunas características del producto. Aunque Microsoft ha introducido cada vez más opciones de configuración de la base de datos y del servidor en las nuevas versiones de SQL Server, los indicadores de seguimiento se siguen utilizando ampliamente. Tendrás que comprobar los indicadores de seguimiento que estén presentes en el sistema; puede que también tengas que activar algunos de ellos.

Puedes obtener la lista de banderas de rastreo habilitadas ejecutando el comando DBCC TRACESTATUS . Puedes habilitarlos en el Gestor de Configuración del Servidor SQL y/o utilizando la opción de inicio -T SQL Server.

Veamos algunas banderas de rastreo comunes:

T1118
Esta bandera de rastreo evita el uso de extensiones mixtas en SQL Server. Esto ayudará a mejorar el rendimiento de tempdb en SQL Server 2014 y versiones anteriores al reducir el número de cambios y, por tanto, la contención en los catálogos del sistema tempdb. Esta bandera de rastreo no es necesaria en SQL Server 2016 y posteriores, donde tempdb no utiliza extensiones mixtas por defecto.
T1117
Con esta bandera de rastreo, SQL Server hace crecer automáticamente todos los archivos de datos del grupo de archivos cuando uno de ellos se queda sin espacio. Proporciona una distribución de E/S más equilibrada entre los archivos de datos. Deberías activar esta bandera de rastreo para mejorar el rendimiento de tempdb en versiones antiguas de SQL Server; sin embargo, comprueba si las bases de datos de algún usuario tienen grupos de archivos con múltiples archivos de datos de tamaño desigual. Al igual que con T1118, esta bandera de rastreo no es necesaria en SQL Server 2016 y posteriores, donde tempdb auto-crece todos los archivos de datos por defecto.
T2371
Por defecto, SQL Server actualiza automáticamente las estadísticas sólo cuando se ha modificado el 20% de los datos del índice. Esto significa que, con tablas grandes, las estadísticas rara vez se actualizan automáticamente. La bandera de rastreo T2371 cambia este comportamiento, haciendo que el umbral de actualización de las estadísticas sea dinámico: cuanto mayor sea la tabla, menor será el porcentaje de cambios necesarios para activar la actualización. A partir de SQL Server 2016, también puedes controlar este comportamiento mediante el nivel de compatibilidad de la base de datos. No obstante, sigo recomendando activar este indicador de seguimiento a menos que todas las bases de datos del servidor tengan un nivel de compatibilidad de 130 o superior.
T3226
Con esta bandera de rastreo, SQL Server no escribe información sobre las copias de seguridad de bases de datos realizadas con éxito en el registro de errores. Esto puede ayudar a reducir el tamaño de los registros, haciéndolos más manejables.
T1222
Esta bandera de rastreo escribe gráficos de punto muerto en el registro de errores de SQL Server. Esta bandera es benigna; sin embargo, hace que los registros de SQL Server sean más difíciles de leer y analizar . También es redundante: puedes obtener un gráfico de bloqueo de una sesión de Eventos Extendidos de System_Health cuando lo necesites. Yo suelo eliminar esta bandera de rastreo cuando la veo.
T4199
Este indicador de seguimiento y la opción de base de datos QUERY_OPTIMIZER_HOTFIXES (en SQL Server 2016 y posteriores) controlan el comportamiento de las revisiones del Optimizador de Consultas. Cuando esta bandera de rastreo está activada, se utilizarán las revisiones introducidas en los paquetes de servicio y en las actualizaciones acumulativas . Esto puede ayudar a solucionar algunos errores del Optimizador de Consultas y mejorar el rendimiento de las consultas; sin embargo, también aumenta el riesgo de regresiones del plan después de aplicar los parches. Normalmente no activo esta bandera de rastreo en los sistemas de producción, a menos que sea posible realizar pruebas de regresión exhaustivas del sistema antes de parchear.
T7412
Esta bandera de rastreo permite la creación de perfiles de ejecución ligeros de la infraestructura en SQL Server 2016 y 2017. Esto te permite recopilar planes de ejecución y muchas métricas de ejecución para las consultas del sistema, con poca sobrecarga de CPU. Hablaré de ello con más detalle en el Capítulo 5.

En resumen, en SQL Server 2014 y anteriores, activa T1118, T2371 y, potencialmente, T1117. En SQL Server 2016 y posteriores, activa T2371 a menos que todas las bases de datos tengan un nivel de compatibilidad de 130 o superior. Después de eso, mira todas las demás banderas de rastreo del sistema y entiende lo que están haciendo. Algunas banderas de rastreo pueden ser instaladas inadvertidamente por herramientas de terceros y pueden afectar negativamente al rendimiento del servidor.

Opciones del servidor

SQL Server proporciona muchos ajustes de configuración. Cubriré muchas de ellas en profundidad más adelante en el libro; sin embargo, hay algunas configuraciones que merece la pena mencionar aquí.

Optimizar para cargas de trabajo ad hoc

El primer ajuste de configuración que trataré es Optimizar para cargas de trabajo ad hoc. Esta opción de configuración controla el modo en que SQL Server almacena en caché los planes de ejecución de las consultas ad-hoc (no parametrizadas). Cuando esta opción está desactivada (por defecto), SQL Server almacena en caché los planes de ejecución completos de esas sentencias, lo que puede aumentar significativamente el uso de memoria caché del plan. Cuando esta configuración está activada, SQL Server comienza almacenando en caché la pequeña estructura (sólo unos cientos de bytes), denominada stub del plan, sustituyéndola por el plan de ejecución completo si se ejecuta una consulta ad-hoc la segunda vez.

En la mayoría de los casos, las sentencias ad hoc no se ejecutan repetidamente, y es beneficioso activar el ajuste Optimizar para cargas de trabajo ad hoc en todos los sistemas. Podría reducir significativamente el uso de memoria caché del plan a costa de recompilaciones adicionales poco frecuentes de las consultas ad-hoc. Obviamente, esta configuración no afectaría al comportamiento de la caché de las consultas parametrizadas y del código de base de datos T-SQL.

Nota

A partir de SQL Server 2019 y en Azure SQL Database, puedes controlar el comportamiento del ajuste Optimizar para cargas de trabajo ad hoc a nivel de base de datos utilizando la configuración de ámbito de base de datos OPTIMIZE_FOR_AD_HOC_WORKLOADS .

Memoria máxima del servidor

El segundo ajuste importante es Memoria máxima del servidor, que controla cuánta memoria puede consumir SQL Server. A los ingenieros de bases de datos les encanta debatir sobre cómo configurar correctamente este parámetro, y existen distintos enfoques para calcular el valor adecuado para el mismo. Muchos ingenieros sugieren incluso dejar el valor por defecto y permitir que SQL Server lo gestione automáticamente. En mi opinión, lo mejor es ajustar esta configuración, pero es importante hacerlo correctamente(en el Capítulo 7 se tratarán los detalles). Una configuración incorrecta afectará más al rendimiento de SQL Server que si dejas el valor por defecto.

Un problema concreto que me encuentro a menudo durante las comprobaciones del estado del sistema es la grave infradotación de este parámetro. A veces, la gente se olvida de cambiarlo después de actualizar el hardware o la máquina virtual; otras veces, se calcula incorrectamente en entornos no dedicados, en los que SQL Server comparte el servidor con otras aplicaciones. En ambos casos, puedes obtener mejoras inmediatas aumentando la configuración de Memoria máxima del servidor o incluso reconfigurándola al valor por defecto hasta que realices un análisis completo más adelante.

Máscara de afinidad

Necesitas comprobar la afinidad de SQL Server y, potencialmente, establecer una máscara de afinidad si SQL Server se ejecuta en un hardware con múltiples nodos de acceso no uniforme a memoria (NUMA). En el hardware moderno, cada CPU física suele convertirse en un nodo NUMA independiente . Si restringes el uso de algunos de los núcleos físicos por parte de SQL Server, deberás equilibrar las CPU de SQL Server (o los programadores; véase el Capítulo 2) de manera uniforme entre los NUMA.

Por ejemplo, si ejecutas SQL Server en un servidor con dos procesadores Xeon de 18 núcleos y limitas SQL Server a 24 núcleos, debes configurar la máscara de afinidad para que utilice 12 núcleos de cada CPU física. Esto te dará mejor rendimiento que hacer que SQL Server utilice 18 núcleos del primer procesador y 6 núcleos del segundo.

El listado 1-2 muestra cómo analizar la distribución de programadores (CPUs) de SQL Server entre nodos NUMA. Observa el recuento de programadores para cada columna parent_node_id en la salida.

Listado 1-2. Comprobación de la distribución de programadores de nodos NUMA (CPUs)
SELECT
  parent_node_id
  ,COUNT(*) as [Schedulers]
  ,SUM(current_tasks_count) as [Current]
  ,SUM(runnable_tasks_count) as [Runnable]
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
GROUP BY parent_node_id;

Paralelismo

Es importante comprobar los ajustes de paralelismo en el sistema. Los ajustes por defecto, como MAXDOP = 0 y Cost Threshold for Parallelism = 5, no funcionan bien en los sistemas modernos. Al igual que con la Memoria Máxima del Servidor, es mejor afinar los ajustes en función de la carga de trabajo del sistema(el Capítulo 6 tratará esto en detalle). Sin embargo, mi regla general para los ajustes genéricos es la siguiente:

  • Ajusta MAXDOP a una cuarta parte del número de CPUs disponibles en sistemas OLTP y a la mitad en sistemas de almacén de datos. En servidores OLTP muy grandes, mantén MAXDOP a 16 o menos. No superes el número de programadores del nodo NUMA.

  • Ajusta Cost Threshold for Parallelism a 50.

A partir de SQL Server 2016 y en la base de datos Azure SQL Server, puedes establecer MAXDOP en el nivel de base de datos mediante el comando ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP. Esto es útil cuando la instancia aloja bases de datos que manejan diferentes cargas de trabajo.

Ajustes de configuración

Al igual que con las banderas de rastreo, analiza otros cambios en los ajustes de configuración que se hayan aplicado en el servidor. Puedes examinar las opciones de configuración actuales utilizando la vista sys.configurations. Desgraciadamente, SQL Server no proporciona una lista de valores de configuración predeterminados para comparar, por lo que tienes que codificarlos, como se muestra en el Listado 1-3. Sólo incluyo unos pocos valores de configuración para ahorrar espacio, pero puedes descargar la versión completa del script en los materiales complementarios de este libro.

Listado 1-3. Detectar cambios en los ajustes de configuración del servidor
DECLARE
    @defaults TABLE
    (
        name SYSNAME NOT NULL PRIMARY KEY, 
        def_value SQL_VARIANT NOT NULL
    )

INSERT INTO @defaults(name,def_value) 
VALUES('backup compression default',0); 
INSERT INTO @defaults(name,def_value) 
VALUES('cost threshold for parallelism',5); 
INSERT INTO @defaults(name,def_value) 
VALUES('max degree of parallelism',0);
INSERT INTO @defaults(name,def_value) 
VALUES('max server memory (MB)',2147483647);
INSERT INTO @defaults(name,def_value) 
VALUES('optimize for ad hoc workloads',0); 
/* Other settings are omitted in the book */

SELECT
    c.name, c.description, c.value_in_use, c.value
    ,d.def_value, c.is_dynamic, c.is_advanced
FROM
    sys.configurations c JOIN @defaults d ON
        c.name = d.name
WHERE
    c.value_in_use <> d.def_value OR
    c.value <> d.def_value
ORDER BY
    c.name;

La Figura 1-3 muestra un ejemplo de la salida del código anterior. La discrepancia entre las columnas value y value_in_use indica cambios de configuración pendientes que requieren un reinicio para surtir efecto. La columna is_dynamic muestra si la opción de configuración puede modificarse sin reiniciar.

Figura 1-3. Opciones de configuración del servidor no predeterminado

Configurar tus bases de datos

Como siguiente paso, tendrás que validar varios ajustes de la base de datos y opciones de configuración. Veámoslas.

Configuración de la base de datos

SQL Server te permite cambiar múltiples configuraciones de la base de datos, ajustando su comportamiento para satisfacer la carga de trabajo del sistema y otros requisitos. Cubriré muchos de ellos más adelante en el libro; sin embargo, hay algunos ajustes que me gustaría comentar aquí.

La primera es Reducción automática. Cuando esta opción está activada, SQL Server reduce periódicamente la base de datos y libera el espacio libre no utilizado de los archivos al SO. Aunque esto parece atractivo y promete reducir la utilización del espacio en disco, puede introducir problemas.

El proceso de reducción de la base de datos funciona a nivel físico. Localiza el espacio vacío al principio del archivo y desplaza los extents asignados desde el final del archivo al espacio vacío, sin tener en cuenta la propiedad de los extents. Esto introduce una carga notable y provoca una fragmentación grave del índice. Es más, en muchos casos es inútil: los archivos de la base de datos simplemente vuelven a expandirse a medida que crecen los datos. Siempre es mejor gestionar el espacio de los archivos manualmente y desactivar la Reducción Automática.

Otra opción de la base de datos, Cierre automático, controla el modo en que SQL Server almacena en caché los datos de la base de datos. Cuando está activado el Cierre Automático, SQL Server elimina las páginas de datos del buffer pool y los planes de ejecución de la caché de planes cuando la base de datos no tiene ninguna conexión activa. Esto provocará un impacto en el rendimiento con las nuevas sesiones cuando sea necesario almacenar datos en caché y volver a compilar las consultas.

Con muy pocas excepciones, debes desactivar el Cierre Automático. Una de estas excepciones puede ser una instancia que albergue un gran número de bases de datos a las que se accede raramente. Incluso en ese caso, yo consideraría mantener desactivada esta opción y permitir que SQL Server retire los datos almacenados en caché de la forma normal.

Asegúrate de que la opción Verificar páginaestá configurada en CHECKSUM. Esto detectará los errores de consistencia con mayor eficacia y ayudará a resolver los casos de corrupción de la base de datos.

Presta atención al modelo de recuperación de la base de datos. Si las bases de datos utilizan el modo de recuperación SIMPLE, sería imposible recuperar más allá de la última copia de seguridad de la base de datos FULL en caso de desastre. Si encuentras la base de datos en este modo, coméntalo inmediatamente con los interesados, asegurándote de que comprenden el riesgo de pérdida de datos.

El nivel de compatibilidad de la base de datos controla la compatibilidad y el comportamiento de SQL Server a nivel de base de datos. Por ejemplo, si estás ejecutando SQL Server 2019 y tienes una base de datos con un nivel de compatibilidad de 130 (SQL Server 2016), SQL Server se comportará como si la base de datos se estuviera ejecutando en SQL Server 2016. Mantener las bases de datos en los niveles de compatibilidad más bajos simplifica las actualizaciones de SQL Server al reducir las posibles regresiones; sin embargo, también te impide obtener algunas funciones y mejoras nuevas.

Como norma general, ejecuta las bases de datos en el último nivel de compatibilidad que coincida con la versión de SQL Server. Ten cuidado cuando lo cambies: como con cualquier cambio de versión, esto puede provocar regresiones. Prueba el sistema antes del cambio y asegúrate de que puedes revertirlo si es necesario, sobre todo si la base de datos tiene un nivel de compatibilidad de 110 (SQL Server 2012) o inferior. Aumentar el nivel de compatibilidad a 120 (SQL Server 2014) o superior habilitará un nuevo modelo de estimación de cardinalidad y puede cambiar significativamente los planes de ejecución de las consultas. Prueba el sistema a fondo para comprender el impacto del cambio.

Puedes forzar a SQL Server a utilizar modelos de estimación de cardinalidad heredados con los nuevos niveles de compatibilidad de bases de datos estableciendo la opción de base de datos LEGACY_CARDINALITY_ESTIMATION en ON en SQL Server 2016 y posteriores, o activando el indicador de rastreo a nivel de servidor T9481 en SQL Server 2014. Este enfoque te permitirá realizar la actualización o los cambios de nivel de compatibilidad por fases, reduciendo el impacto en el sistema.( Enel Capítulo 5 se tratará con más detalle la estimación de la cardinalidad y se explicará cómo reducir los riesgos durante las actualizaciones de SQL Server y los cambios de nivel de compatibilidad de la base de datos).

Configuración del registro de transacciones

SQL Server utiliza el registro de escritura anticipada, persistiendo la información sobre todos los cambios de la base de datos en un registro de transacciones. SQL Server trabaja con los registros de transacciones secuencialmente, en forma de carrusel. En la mayoría de los casos, no necesitarás varios archivos de registro en el sistema: complican la administración de la base de datos y rara vez mejoran el rendimiento.

Internamente, SQL Server divide los registros de transacciones en trozos llamados Archivos de Registro Virtuales (VLF) y los gestiona como unidades individuales. Por ejemplo, SQL Server no puede truncar y reutilizar un VLF si sólo contiene un único registro de registro activo. Presta atención al número de VLF de la base de datos. Muy pocos VLF muy grandes harán que la gestión y el truncado de registros no sean óptimos. Demasiados VLF pequeños degradarán el rendimiento de las operaciones del registro de transacciones. Intenta no superar varios cientos de VLF en los sistemas de producción.

El número de VLF que añade SQL Server cuando hace crecer un registro depende de la versión de SQL Server y del tamaño del crecimiento. En la mayoría de los casos, crea 8 VLF cuando el tamaño del crecimiento está entre 64 MB y 1 GB, o 16 VLF con crecimientos superiores a 1 GB. No utilices la configuración de crecimiento automático basada en porcentajes, porque genera muchos VLF de tamaño desigual. En su lugar, cambia la configuración del crecimiento automático del registro para que el archivo crezca en trozos. Yo suelo utilizar trozos de 1.024 MB, que generan VLF de 128 MB, a menos que necesite un registro de transacciones muy grande.

Puedes contar los VLF de la base de datos con el DMV sys.dm_db_log_info en SQL Server 2016 y posteriores. En versiones anteriores de SQL Server, puedes obtener esa información ejecutando DBCC LOGINFO. Si el registro de transacciones no está bien configurado, considera la posibilidad de reconstruirlo. Puedes hacerlo reduciendo el registro al tamaño mínimo y haciéndolo crecer en trozos de 1.024 MB a 4.096 MB.

No reduzcas automáticamente los archivos del registro de transacciones. Volverán a crecer y afectarán al rendimiento cuando SQL Server ponga a cero el archivo. Es mejor preasignar el espacio y gestionar manualmente el tamaño del archivo de registro. Sin embargo, no restrinjas el tamaño máximo ni el crecimiento automático: quieres que los registros crezcan automáticamente en caso de emergencia.( Enel Capítulo 11 encontrarás más detalles sobre cómo solucionar los problemas del registro de transacciones).

Ficheros de datos y grupos de ficheros

Por defecto, SQL Server crea nuevas bases de datos utilizando el grupo de archivos de un solo archivo PRIMARY y un archivo de registro de transacciones. Por desgracia, esta configuración no es óptima desde el punto de vista del rendimiento, la gestión de bases de datos y la HA.

SQL Server realiza un seguimiento del uso del espacio en los archivos de datos a través de unas páginas del sistema llamadas mapas de asignación. En sistemas con datos muy volátiles, los mapas de asignación pueden ser una fuente de contención: SQL Server serializa el acceso a ellos durante sus modificaciones (más sobre esto en el Capítulo 10). Cada archivo de datos tiene su propio conjunto de páginas de mapas de asignación, y puedes reducir la contención creando varios archivos en el grupo de archivos con los datos modificables activos.

Asegúrate de que los datos se distribuyen uniformemente en varios archivos de datos del mismo grupo de archivos. SQL Server utiliza un algoritmo llamado Relleno Proporcional, que escribe la mayor parte de los datos en el archivo que tiene más espacio libre disponible. Unos archivos de datos de tamaño uniforme ayudarán a equilibrar esas escrituras, reduciendo la contención del mapa de asignación. Asegúrate de que todos los archivos de datos del grupo de archivos tienen el mismo tamaño y los mismos parámetros de crecimiento automático, especificados en megabytes.

También puedes activar la opción de grupo de archivos AUTOGROW_ALL_FILES (disponible en SQL Server 2016 y posteriores), que activa el crecimiento automático de todos los archivos del grupo de archivos simultáneamente . Puedes utilizar el indicador de seguimiento T1117 para esto en versiones anteriores de SQL Server, pero recuerda que este indicador se establece a nivel de servidor y afectará a todas las bases de datos y grupos de archivos del sistema.

A menudo es poco práctico o imposible cambiar la disposición de las bases de datos existentes. Sin embargo, puede que necesites crear nuevos grupos de archivos y mover datos durante el ajuste del rendimiento. Aquí tienes algunas sugerencias para hacerlo con eficacia:

  • Crea varios archivos de datos en grupos de archivos con datos volátiles. Yo suelo empezar con cuatro archivos y aumentar el número si observo problemas de enclavamiento (consulta el Capítulo 10). Asegúrate de que todos los archivos de datos tienen el mismo tamaño y los parámetros de crecimiento automático especificados en megabytes; activa la opción AUTOGROW_ALL_FILES. Para los grupos de archivos con datos de sólo lectura, suele bastar con un archivo de datos.

  • No distribuyas índices agrupados, índices no agrupados o datos de objetos grandes (LOB) en varios grupos de archivos. Esto raramente ayuda al rendimiento y puede introducir problemas en caso de corrupción de la base de datos.

  • Coloca las entidades relacionadas (por ejemplo, Orders y OrderLineItems) en el mismo grupo de archivos. Esto simplificará la gestión de la base de datos y la recuperación ante desastres.

  • Si es posible, mantén vacío el grupo de archivos PRIMARY.

La Figura 1-4 muestra un ejemplo de disposición de la base de datos de un hipotético sistema de comercio electrónico. Los datos están particionados y repartidos en varios grupos de archivos con el objetivo de minimizar el tiempo de inactividad y utilizar la disponibilidad parcial de la base de datos en caso de desastre.1 También te permitirá mejorar la estrategia de copia de seguridad implementando copias de seguridad parciales de la base de datos y excluyendo los datos de sólo lectura de las copias de seguridad completas .

Figura 1-4. Estructura de la base de datos de un sistema de comercio electrónico

Analizar el registro de errores de SQL Server

El Registro de Errores del Servidor SQL es otro lugar que suelo consultar al principio de la resolución de problemas. Me gusta ver los errores que tiene, que pueden señalar algunas áreas que requieren seguimiento. Por ejemplo, los errores 823 y 824 pueden indicar problemas con el subsistema de disco y/o corrupción de la base de datos.

Puedes leer el contenido del registro de errores en SSMS. También puedes obtenerlo mediante programación utilizando el procedimiento almacenado del sistema xp_readerrorlog. El reto aquí es la cantidad de datos del registro: el ruido de los mensajes de información puede ocultar datos útiles.

El código del Listado 1-4 te ayuda a resolver ese problema. Te permite filtrar el ruido innecesario y centrarte en los mensajes de error. Puedes controlar el comportamiento del código con las siguientes variables:

@StartDate y @EndDate
Define el tiempo de análisis.
@NumErrorLogs
Especifica el número de archivos de registro que hay que leer si SQL Server los traspasa.
@ExcludeLogonErrors
Omite los mensajes de auditoría de inicio de sesión.
@ShowSurroundingEvents y @ExcludeLogonSurroundingEvents
Te permite recuperar del registro los mensajes de información que rodean a las entradas de error. La ventana de tiempo para esos mensajes está controlada por las variables @Surrounding​E⁠ventsBeforeSeconds y @SurroundingEventsAfterSeconds.

El script produce dos salidas. La primera muestra las entradas del registro de errores que incluyen la palabra error. Cuando el parámetro @ShowSurroundingEvents está activado, también proporciona entradas de registro alrededor de esas líneas de error. Puedes excluir de la salida algunas entradas del registro que contengan la palabra error insertándolas en la tabla @ErrorsToIgnore.

Listado 1-4. Analizar el registro de errores de SQL Server
IF OBJECT_ID('tempdb..#Logs',N'U') IS NOT NULL DROP TABLE #Logs;
IF OBJECT_ID('tempdb..#Errors',N'U') IS NOT NULL DROP TABLE #Errors;
GO

CREATE TABLE #Errors
(
  LogNum INT NULL,
  LogDate DATETIME NULL,
  ID INT NOT NULL identity(1,1),
  ProcessInfo VARCHAR(50) NULL,
  [Text] NVARCHAR(MAX) NULL,
  PRIMARY KEY(ID)
);

CREATE TABLE #Logs
(
  [LogDate] DATETIME NULL,
  ProcessInfo VARCHAR(50) NULL,
  [Text] NVARCHAR(MAX) NULL
);

DECLARE 
  @StartDate DATETIME = DATEADD(DAY,-7,GETDATE())
  ,@EndDate DATETIME = GETDATE()
  ,@NumErrorLogs INT = 1
  ,@ExcludeLogonErrors BIT = 1
  ,@ShowSurroundingEvents BIT = 1
  ,@ExcludeLogonSurroundingEvents BIT = 1
  ,@SurroundingEventsBeforeSecond INT = 5
  ,@SurroundingEventsAfterSecond INT = 5
  ,@LogNum INT = 0;

DECLARE
  @ErrorsToIgnore TABLE
  (
    ErrorText NVARCHAR(1024) NOT NULL
  );

INSERT INTO @ErrorsToIgnore(ErrorText)
VALUES
  (N'Registry startup parameters:%'),
  (N'Logging SQL Server messages in file%'),
  (N'CHECKDB for database%finished without errors%');

WHILE (@LogNum <= @NumErrorLogs) 
BEGIN 
  INSERT INTO #Errors(LogDate,ProcessInfo,Text)
    EXEC [master].[dbo].[xp_readerrorlog] 
      @LogNum, 1, N'error', NULL, @StartDate, @EndDate, N'desc';
  IF @@ROWCOUNT > 0
    UPDATE #Errors SET LogNum = @LogNum WHERE LogNum IS NULL;  
  SET @LogNum += 1;
END;

IF @ExcludeLogonErrors = 1
  DELETE FROM #Errors WHERE ProcessInfo = 'Logon';

DELETE FROM e
FROM #Errors e
WHERE EXISTS
(
  SELECT *
  FROM @ErrorsToIgnore i
  WHERE e.Text LIKE i.ErrorText
);

-- Errors only
SELECT * FROM #Errors ORDER BY LogDate DESC;

IF @@ROWCOUNT > 0 AND @ShowSurroundingEvents = 1
BEGIN
  DECLARE
    @LogDate DATETIME
    ,@ID INT = 0

  WHILE 1 = 1
  BEGIN
    SELECT TOP 1 @LogNum = LogNum, @LogDate = LogDate, @ID = ID 
    FROM #Errors 
    WHERE ID > @ID
    ORDER BY ID;

    IF @@ROWCOUNT = 0
      BREAK;

    SELECT 
      @StartDate = DATEADD(SECOND, -@SurroundingEventsBeforeSecond, @LogDate)
      ,@EndDate = DATEADD(SECONd, @SurroundingEventsAfterSecond, @LogDate);

    INSERT INTO #Logs(LogDate,ProcessInfo,Text)
      EXEC [master].[dbo].[xp_readerrorlog] 
        @LogNum, 1, NULL, NULL, @StartDate, @EndDate;
  END;

  IF @ExcludeLogonSurroundingEvents = 1
    DELETE FROM #Logs WHERE ProcessInfo = 'Logon';

  DELETE FROM e
  FROM #Logs e
  WHERE EXISTS
  (
    SELECT *
    FROM @ErrorsToIgnore i
    WHERE e.Text LIKE i.ErrorText
  );

  SELECT * FROM #Logs ORDER BY LogDate DESC;
END

No voy a mostrar aquí la lista completa de posibles errores; puede ser excesiva y, en muchos casos, es específica del sistema. Pero debes analizar cualquier dato sospechoso de la salida y comprender su posible impacto en el sistema.

Por último, te sugiero que configures alertas para errores de alta gravedad en SQL Server Agent, si no lo has hecho ya. Puedes leer la documentación de Microsoft sobre cómo hacerlo.

Consolidar instancias y bases de datos

No se puede hablar de la solución de problemas de SQL Server sin hablar de la consolidación de bases de datos e instancias de SQL Server . Aunque la consolidación suele reducir los costes de hardware y licencias, no sale gratis; tienes que analizar su posible impacto negativo en el rendimiento actual o futuro del sistema.

No existe una estrategia de consolidación universal que pueda utilizarse en todos los proyectos. Debes analizar la cantidad de datos, la carga, la configuración del hardware y tus requisitos empresariales y de seguridad al tomar esta decisión. Sin embargo, como regla general, evita consolidar las bases de datos OLTP y de almacén de datos/informes en el mismo servidor cuando trabajen con una gran carga (o, si están consolidadas, considera la posibilidad de dividirlas). Las consultas del almacén de datos suelen procesar grandes cantidades de datos, lo que provoca una gran actividad de E/S y vacía el contenido del buffer pool. En conjunto, esto afecta negativamente al rendimiento de otros sistemas.

Además, analiza tus requisitos de seguridad al consolidar bases de datos. Algunas funciones de seguridad, como la Auditoría, afectan a todo el servidor y añaden sobrecarga de rendimiento para todas las bases de datos del servidor. El Cifrado Transparente de Datos (TDE) es otro ejemplo: aunque el TDE es una función a nivel de base de datos, SQL Server cifra tempdb cuando cualquiera de las bases de datos del servidor tiene activado el TDE. Esto conlleva una sobrecarga de rendimiento para el resto de sistemas.

Como norma general, no mantengas bases de datos con requisitos de seguridad diferentes en la misma instancia de SQL Server. Observa las tendencias y los picos en las métricas y separa las bases de datos entre sí cuando sea necesario. (Más adelante en el libro te proporcionaré código para ayudarte a analizar el uso de CPU, E/S y memoria por base de datos).

Te sugiero que utilices la virtualización y consolides varias máquinas virtuales en uno o varios hosts, en lugar de poner varias bases de datos independientes y activas en una única instancia de SQL Server. Esto te dará mucha más flexibilidad, capacidad de gestión y aislamiento entre los sistemas, especialmente si varias instancias de SQL Server se ejecutan en el mismo servidor. Es mucho más fácil gestionar su consumo de recursos cuando las virtualizas.

Efecto Observador

La implementación en producción de todo sistema SQL Server serio requiere aplicar una estrategia de monitoreo . Puede incluir herramientas de monitoreo de terceros, código basado en tecnologías estándar de SQL Server, o ambas cosas.

Una buena estrategia de monitoreo es esencial para el soporte de producción de SQL Server. Te ayuda a ser más proactivo y reduce los tiempos de detección y recuperación de incidencias. Por desgracia, no es gratis: todo tipo de monitoreo añade sobrecarga al sistema. En algunos casos, esta sobrecarga puede ser insignificante y aceptable; en otros, puede afectar significativamente al rendimiento del servidor.

Durante mi carrera como consultor de SQL Server, he visto muchos casos de monitoreo ineficiente. Por ejemplo, un cliente utilizaba una herramienta que proporcionaba información sobre la fragmentación de los índices llamando a la función sys.dm_db_index_physical_stats, en modo DETAILED, cada cuatro horas para cada índice de la base de datos. Esto introducía enormes picos de E/S y vaciaba la reserva de memoria intermedia, lo que provocaba un notable deterioro del rendimiento. Otro cliente utilizaba una herramienta que sondeaba constantemente varias DMV, añadiendo una carga significativa de CPU al servidor.

Afortunadamente, en muchos casos, podrás ver esas consultas y evaluar su impacto durante la resolución de problemas del sistema. Sin embargo, no siempre es así con otras tecnologías; un ejemplo es el monitoreo basado en Eventos Extendidos (xEvents). Aunque los Eventos Extendidos son una gran tecnología que te permite solucionar problemas complejos en SQL Server, no es la mejor opción como herramienta de elaboración de perfiles. Algunos eventos son pesados y pueden introducir una gran sobrecarga en entornos con mucho tráfico.

Veamos un ejemplo que crea una sesión xEvents que captura las consultas que se ejecutan en el sistema, como se muestra en el Listado 1-5.

Listado 1-5. Crear una sesión xEvents para capturar consultas en el sistema
CREATE EVENT SESSION CaptureQueries ON SERVER
ADD EVENT sqlserver.rpc_completed
(
  SET collect_statement=(1)
  ACTION
  (
    sqlos.task_time
    ,sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_name
    ,sqlserver.nt_username
    ,sqlserver.sql_text
  )
),
ADD EVENT sqlserver.sql_batch_completed
(
  ACTION
  (
    sqlos.task_time
    ,sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_name
    ,sqlserver.nt_username
    ,sqlserver.sql_text
  )
),
ADD EVENT sqlserver.sql_statement_completed
ADD TARGET package0.event_file
(SET FILENAME=N'C:\PerfLogs\LongSql.xel',MAX_FILE_SIZE=(200))
WITH
(
  MAX_MEMORY =4096 KB
  ,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
  ,MAX_DISPATCH_LATENCY=5 SECONDS
);

A continuación, impleméntalo en un servidor que funcione con una carga pesada y un gran número de solicitudes simultáneas. Mide el rendimiento del sistema, con y sin la sesión xEvents en ejecución. Obviamente, ten cuidado, ¡y no la ejecutes en el servidor de producción!

La Figura 1-5 ilustra la carga de la CPU y el número de solicitudes por lotes por segundo en ambos escenarios en uno de mis servidores. Como puedes ver, activar la sesión xEvents disminuyó el rendimiento en un 20% aproximadamente. Para colmo, sería muy difícil detectar la existencia de esa sesión en el servidor.

Figura 1-5. Rendimiento del servidor con y sin una sesión xEvents activa

Obviamente, el grado de impacto dependerá de la carga de trabajo del sistema. En cualquier caso, comprueba si hay herramientas de monitorización o recopilación de datos innecesarias cuando resuelvas el problema.

En resumen: evalúa la estrategia de monitoreo y calcula su sobrecarga como parte de tu análisis, especialmente cuando el servidor aloja varias bases de datos. Por ejemplo, xEvents funciona a nivel de servidor. Aunque puedes filtrar los eventos basándote en el campo database_id, el filtrado se produce después de que se haya disparado un evento. Esto puede afectar a todas las bases de datos del servidor .

Resumen

La solución de problemas del sistema es un proceso holístico que requiere que analices todo tu ecosistema. Tienes que examinar las capas de hardware, SO y virtualización, así como las configuraciones de SQL Server y de la base de datos, y ajustarlas según sea necesario.

SQL Server proporciona muchos ajustes que puedes utilizar para ajustar la instalación a la carga de trabajo del sistema. También hay buenas prácticas que se aplican a la mayoría de los sistemas, como activar los ajustes IFI y Optimizar para cargas de trabajo ad hoc, aumentar el número de archivos en tempdb, activar algunos indicadores de rastreo, desactivar la Reducción automática y configurar correctamente los parámetros de crecimiento automático de los archivos de base de datos.

En el próximo capítulo, hablaré de uno de los componentes más importantes de SQL Server -SQLOS- y de una técnica de solución de problemas llamada Estadísticas de Espera.

Lista de comprobación para la resolución de problemas

  • Realiza un análisis de alto nivel del subsistema de hardware, red y disco.

  • Discute la configuración del host y la carga en entornos virtualizados con ingenieros de infraestructura.

  • Comprueba las versiones, ediciones y niveles de parcheado del SO y de SQL Server.

  • Comprueba si está activada la inicialización instantánea de archivos.

  • Analiza las banderas de rastreo.

  • Activar Optimizar para cargas de trabajo ad hoc.

  • Comprueba los ajustes de memoria y paralelismo del servidor.

  • Consulta la configuración de tempdb (incluido el número de archivos); comprueba si existe el indicador de rastreo T1118, y potencialmente T1117, en las versiones de SQL Server anteriores a 2016.

  • Desactiva la reducción automática de las bases de datos.

  • Valida la configuración de los datos y del archivo de registro de transacciones.

  • Comprueba el número de VLF en los archivos de registro de transacciones.

  • Comprueba los errores en el registro del Servidor SQL.

  • Comprueba si hay monitoreo innecesario en el sistema.

1 Para profundizar en las estrategias de partición de datos y recuperación ante desastres, consulta mi libro Pro SQL Server Internals, Segunda Edición (Apress, 2016).

Get Solución avanzada de problemas y ajuste del rendimiento de SQL Server now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial