A los DBA se nos pide bastante a menudo que llevemos a cabo revisiones técnicas del entorno de los entornos SQL y de su rendimiento. Los clientes pueden solicitar una revisión por muchos motivos, pero el más común es el rendimiento.
Este artículo pretende cubrir las secciones principales de cualquier revisión de este tipo, las medidas típicas que deben examinarse y sus implicaciones.
Visión general del Servidor SQL Server
Básicamente la mayoría de las cosas en la sección consiste en documentar y examinar el sistema Windows y SQL Server para las mejores prácticas, o simplemente cosas que está fuera de lugar o no es lo óptimo
Servidor
VM vs Físico vs Nube
OS
¿Qué versión se utiliza? ¿En qué nivel de soporte se encuentra? ¿Está configurado el servidor para utilizar el modo de alimentación de alto rendimiento? (El modo equilibrado puede suprimir la CPU hasta un 30%)
CPU
¿Cuántos núcleos? ¿Nodos NUMA? Recuerde que SQL necesita licencias por núcleo para la mayoría de las implantaciones, a menos que tengan un acuerdo heredado basado en CAL o que estén ejecutando una máquina virtual en la que se hayan concedido licencias para la máquina física subyacente. En SQL, ¿se ha establecido y configurado la afinidad del procesador? Esto sólo debería configurarse en casos extremos, y no por defecto.
RAM
¿Cuánta memoria hay? ¿Cuánta se ha configurado para que use SQL (Max & min)? La mínima debería estar a cero a menos que tenga varias instancias por servidor. ¿Hay suficiente para el sistema operativo, se necesita un mínimo de 2 Gb, e idealmente 4 Gb, para los procesos no SQL.
https://sqlmax.chuvash.eu/ tiene una buena calculadora basada en el número de CPU, la memoria total del servidor, etc.
Otras aplicaciones
¿Han instalado algo más en el servidor SQL? SQL debe ser siempre la única cosa en una caja con la posible adición de la pila de Microsoft BI (SSIS, SSRS, SSAS). Cada aplicación adicional va a competir por los recursos con SQL. Si tienen suficientes servidores (y licencias), incluso la pila de BI debería estar en otro servidor.
Disco/almacenamiento
¿De qué tipo es? ¿RAID, SSD? ¿Es una máquina virtual o física? ¿Cumplen las mejores prácticas de MS ¿Tamaño de bloque?
Las siguientes unidades son las mejores prácticas (las letras pueden cambiar):
- C: Sólo SO
- D: Archivos de instalación SQL, archivos de registro, binarios y bases de datos del sistema, excepto tempdb.
- E: Sólo TempDB, configurado para utilizar todo el disco desde el principio.
- F: Una o más unidades para los archivos de datos de la base de datos.
- G: Una o más unidades para archivos de registro de bases de datos.
- H: unidad local para copias de seguridad si no las ejecuta una aplicación de terceros.
- I: archivo de páginas.
El tamaño del bloque de disco para los archivos de datos y de registro debe ser de 64kb (es decir, E:, F:, G: como arriba).
Configuración de la instancia SQL Server
Más arriba hemos tratado un par de puntos que pueden repetirse aquí.
Versión SQL
¿Tiene soporte (general o ampliado)? ¿Está actualizado?
Instancias con nombre
En general, el mejor consejo es una instancia por servidor.
Sin embargo, los clientes que no les guste mucho gastarse el dinero pueden tomar la decisión de tener más de una instancia en el mismo servidor, por lo que habrá que estar atentos al uso que hace cada una de las instancias de los recursos de ese servidor para asegurarnos de que todas están bien servidas.
CPU
Como se ha mencionado anteriormente, ¿se ha configurado la afinidad del procesador?
MAXDOP y CTFP: Controlan el paralelismo y cuántos nodos se utilizarán en una consulta grande.
Hilos de trabajo: Por defecto, esto se configura por el número de núcleos.
CTFP rec’d valor = 50 y sintonizar a partir de ahí MAXDOP rec’d valores
Referencia: MAXDOP
https://docs.microsoft.com/en-US/sql/database-engine/configure-windows/configure-the-max-degree-of-p arallelism-server-configuration-option?view=sql-server-ver15
Memoria
Como se mencionó anteriormente, se debe comprobar la configuración de memoria mínima y máxima. Mínimo debe ser cero, a menos que esté compartiendo recursos entre varias instancias.
El máximo debería ser de 2 a 4 Gb menos que el total, en función de la cantidad disponible y de la competencia que se produzca. Mi regla normalmente es que por cada 4GB de memoria disponibles, 1 se deja para el SO.
Unidades por defecto
Como se menciona en el punto anterior sobre almacenamiento, lo mejor es dividir los archivos en varias particiones.
TempDB
Debería haber 4-8 ficheros de datos tempdb y un fichero log, todos en su propia partición.
Deberían tener el mismo tamaño y estar configurados para llenar completamente el disco en el arranque sin autocrecimiento, o tener un valor de autocrecimiento «razonable», aunque esto cambiará dependiendo de las monitorizaciones de cada empresa (con esto quiero decir que quizás prefiráis que los archivos no ocupen todo el disco si no que dejen un 20-30% libre).
El número de archivos de datos debe estar relacionado con el número de CPUs (y la configuración MAXDOP), es decir, un archivo por CPU hasta un máximo de 8 archivos de tempdb.
Base de datos model
Esta base de datos es el modelo para cualquier base de datos nueva.
Su configuración depende en gran medida de cuál sea el uso previsto para el servidor, pero revisar si existen objetos preexistentes. Tamaño de los archivos y configuración del crecimiento automático.
Lo ideal es desactivar el autocrecimiento en % y dejarlo en un número fijo.
Seguridad
La cuenta «sa» debería ser renombrada y desactivada, y debería crearse una cuenta de autenticación sql independiente.
Si la autenticación sql está desactivada, debería haber una cuenta «sa» no de usuario. En segundo lugar, ¿cuántas cuentas tienen privilegios «sa»? ¿Cuál es el proceso de auditoría para esas cuentas?
SQL auth frente a Windows auth:
- Windows: es el modelo de mayor seguridad, pero muchas prácticas y aplicaciones siguen necesitando autenticación
- SQL: Sólo hay que minimizarla en la medida de lo posible.
Todos los usuarios «con nombre» deberían utilizar Windows auth y, en el mejor de los casos, tener permisos a través de los grupos de Windows AD.
Cuentas de servicio: Evite el uso de cuentas «NT SERVICE». Debe haber una cuenta de dominio con nombre que ejecute los diferentes Servicios SQL, ya sea única para la máquina, o incluso para el servicio. O, idealmente, deberían configurarse cuentas GMSA para cualquier servicio en el que no se requiera la contraseña.
Números de puerto SQL: Por defecto es 1433 para TCP, 80 para HTTP , 143 para HTTPS. Una buena práctica de seguridad es cambiar estos.
Cifrado, especialmente TDE. ¿Se utiliza? ¿Se han establecido especificaciones de auditoría?
Copias de seguridad
¿Qué proceso realiza las copias de seguridad de las bases de datos y dónde se almacenan? Las copias de seguridad deberían almacenarse fuera del servidor, en un lugar seguro (esto no lo hace ni la mitad de las empresas).
Los Planes de Mantenimiento están generalmente obsoletos, pero todavía se utilizan a menudo. Las copias de seguridad SQL Native a través de trabajos programados son comunes, por ejemplo, los scripts de Ola.
Herramientas de terceros (por ejemplo, Veeam, Commvault o Azure Backup), ¿dónde realizan las copias de seguridad? ¿Cuál es la política de retención a largo plazo? ¿Frecuencia? ¿Registro de copias de seguridad para bases de datos en el modelo de recuperación completa?
¿Utiliza compresión o archivos múltiples para mejorar el rendimiento de la copia de seguridad?
Procesos de mantenimiento
¿Están configurados? ¿Update de estadísticas? ¿Reconstrucción de índices? ¿DBCC? ¿Frecuencia?
Configuración de la base de datos
Autogrow: Como dije antes, lo mejor es especificar tamaños fijos y no % ya que esto dependería mucho de los tamaños de las bases de datos.
Tamaño de los archivos: ¿qué tamaño tienen, cuánto espacio libre hay y cuál es la configuración de crecimiento automático?
Propiedad: Que el propietario de la base de datos no sea un usuario nombrado, que luego dejan la empresa, les borran los login y hay jaleos. Que sea la cuenta «sa» por ejemplo.
Registro de errores
Compruebe la ubicación predeterminada.
Busque errores comunes o peligrosos en los archivos de registro
Yo suelo cambiar el registro de errores para conservar entre 15 y 20 archivos de logs anteriores y crear una tarea de mantenimiento para ciclar los logs. Pero esto es más una preferencia personal que una directriz.
Configuración HA/DR
AOAG, Log shipping, DB mirroring. ¿Está todo configurado y funciona correctamente? ¿Existe un listener para el AG?
SQL Job Agent
¿Cuenta de servicio de agente? ¿Propietario de los jobs (pref ‘sa’ o similar)? ¿Jobs de mantenimiento? (¿Ola?). Jobs de backups, si procede. ¿Algún fallo repetido evidente?
SQL Server Perfmon
El monitor de rendimiento de Windows nos permite realizar un seguimiento de un gran número de estadísticas relativas a cómo se está utilizando el servidor y detectar cualquier limitación de recursos o competencia.
Normalmente se divide la recopilación de estadísticas en 3 grupos: servidor/OS, disco y SQL específico.
Cuando se utiliza Perfmon, se tiene la salida de estadísticas a archivos en una unidad que o bien no está siendo monitorizada, o para la que no estamos preocupados de que los efectos de la salida de escritura de perfmon afecten seriamente de forma negativa al rendimiento.
No guardéis los archivos de Perfmon en los discos específicos de las bases de datos, con la posible excepción de un disco que sólo se utilice para guardar backups (a menos que se hayan identificado previamente como un problema de rendimiento).
Las estadísticas de Perfmon suelen estar disponibles como estadísticas individuales para elementos de interés y agregadas para grupos de elementos.
Por ejemplo, 1 estadística por núcleo de CPU, más una estadística agregada para el rendimiento de la CPU en su conjunto. Rara vez queremos la estadística agregada, ya que se nos ocultan demasiadas cosas cuando utilizamos esa estadística.
Nota: Las estadísticas de perfmon específicas de SQL se crean en el servidor para acceder a ellas cuando se instala SQL Server.
SQL Server Extended Events
Se introdujeron en SQL Server 2008R2 y se han expandido constantemente como un reemplazo para SQL Profiler y algunas estadísticas de Perfmon. Ya hay un par de medidas por defecto, eventos de salud del servidor (system_health) y uno para medir eventos AG.
Los Eventos Extendidos son fáciles de configurar, pero pueden ser difíciles de interpretar. Es recomendable tener una buena base de conocimientos sobre los eventos extendidos antes de intentar configurarlos, es decir, que lo haga/interprete el senior de vuestro equipo.
Se pueden configurar a través del SSMS, o puede ejecutar T-SQL para configurarlo.
Tenéis que seleccionar qué se quiere medir, donde se almacenarán los datos y por cuánto tiempo se ejecutará. Al igual que con SQL Profiler, hay plantillas incorporadas que puede utilizar.
Para una revisión genérica, puede utilizar la plantilla «Estándar». Sin embargo, ten cuidado, ya que captura mucha información sin filtros. Si está buscando problemas relacionados específicamente con el rendimiento de las consultas, puede utilizar la plantilla «TSQL_Duration».
En cualquier caso, es posible que necesiteis editar estas plantillas para adaptarlas al tiempo que el cliente (o vosotros) queráis que se ejecute, por lo que cuidadito.
….
Y esto es todo. Como dije al principio es una guía muy general que se debe adaptar a cada caso específico, pero creo que para tener un primer contacto no está nada mal.
Espero que os haya servido de ayuda.
1 comentario en «Revisión de un entorno SQL Server y su rendimiento»