Tips para optimizar el rendimiento de SQL Server

Julio 18, 2010. Por Figuretti. Categorizado en Bases de Datos, Empresas, Microsoft, MsSQL, SQL. 7 Comentarios. Leido 562 veces.

• Tweet
• Share

• Tweet
• Share

Buenas a todos una vez más… hace unos días tuvimos la visita de un grande en tecnologías Microsoft en mi trabajo, y este articulo es básicamente un resumen de algunos de los puntos más resaltantes de las recomendaciones que nos dio, para realizar sobre nuestro servidor actual, y para nuevos servidores que se quieran instalar. Sin más preámbulos, pasemos de pleno a las recomendaciones, a las cuales, les he dado un toque particular para explicarlas

Rendimiento de Disco:

Muchas veces, el problema de degradación de performance de nuestros servidores, gira en torno al rendimiento de nuestros dispositivos de almacenamiento.

1. Arreglo de Discos, hablando de velocidades de lectura y escritura:

Las configuraciones RAID (Redundant Array of Independent Disks – Arreglo Redundante de Discos Independientes) son en casi todos los casos, utilizadas en ambientes de producción y bases de datos OLTP. Estas bases de datos se caracterizan principalmente porque generan una cantidad importante de operaciones de lectura y escritura en disco, de forma aleatoria.

Muchas empresas utilizan el RAID 5 o 6 como sus estándares de almacenamiento, pero estos tienen sus efectos en el rendimiento del disco duro. La realización del calculo de paridad, y escritura de los datos en los discos, afecta enormemente el rendimiento de los mismos a la hora de la realización de operaciones de lectura y escritura intensivas.

Es por eso, que actualmente en las soluciones de almacenamiento RAID para ambientes de carga elevados, se recomienda el uso de RAID 10. Si bien resulta mas costoso el tener configurado un RAID 10, este funciona de una manera mucho más rápida para las operaciones de lecturas y escrituras aleatorias, y nos permitirá tener un rendimiento ampliamente mayor por un “pequeño” sobre costo sobre los RAID 5 o 6.

A modo de ejemplo:

RAID 5 de 6 Discos SAS de 1 Terabyte  (Aprox. 4,9 Terabytes efectivos en la partición) -> copiar un archivo de 1 Gb y pegarlo en otra ubicación, genera tasas de lectura y escritura que rondan los 70 Mb/s.

RAID 10 de 6 Discos SAS de 1 Terabyte (3 Terabytes efectivos en la partición) -> copiar un archivo de 1 Gb y pegarlo en otra ubicación, genera tasas de lectura y escritura que superan los 120 Mb/s.

Obs: los números son a fin de ejemplificar la diferencia de velocidades, no son valores reales tomados de alguna prueba, aunque estoy en proceso de realizar esa prueba en la brevedad

2. Alineamiento y Offset de Partición, para que evitemos doble lectura:

Este es un problema que todos los equipos que tiene versiones de Windows anteriores al Windows Vista / Server 2008, sufren.

El offset de partición, representa el sector del disco donde empieza la partición, y su correspondiente incidencia en el resto de los bloques de almacenamiento, y su consecuente efecto en el alineamiento de la partición.

La manera de determinar si el offset hizo que mi particion este o no correctamente alineada a los sectores del disco, es simple.

Se ingresa al “command prompt” del Windows, desde el menú Inicio -> Ejecutar -> cmd

En el se escribe lo siguiente, y luego se presiona Enter:

C:\\Windows\\System32\\wmic partition get BlockSize, StartingOffset, Name, Index

Esto, nos retornara como resultado, los datos del tamaño de bloques, offset, nombre de partición e indice de la misma en el disco.

Lo que se hace a continuación, es dividir el valor devuelto bajo la columna StartingOffset entre 1024. Si el nro. de resultado, es entero, la partición está alineada, de lo contrario, el offset hace que nuestra partición no este alineada con los sectores del disco duro.

Veamos un par de ejemplos.

Tomando como ejemplo, el valor 1048576 de esta captura realizada en un Windows Server 2008 SBS, si lo dividimos por 1024 nos da como resultado 1024. Esto quiere decir que la partición está alineada a los sectores del disco duro.
Ahora veamos esta otra captura realizada en un Windows Server 2003 SBS.

En este caso, al dividir 32256 entre 1024, tenemos como resultado 31,5. Por esto podemos deducir que la partición en este otro equipo, está desalineada.
¿Cómo influye este problema?

Al no tener la partición alineada, para acceder a la información, en vez de leer el bloque correspondiente, se debe leer además el bloque extra que estamos ocupando en una pequeña porción. Esto no tiene que ver con lógica de acceso a datos ni nada, sino al movimiento físico del cabezal de lectura al sector en el plato del disco duro. Para acceder a una información dentro de este disco desalineado que ocupe 1 bloque lógico, el cabezal debera hacer 2 lecturas porque en realidad consume 2 bloques físicos, mientras que en el caso anterior, solo debera realizar 1 lectura.

En analisis realizados sobre este problema, se encuentran valores bastante altos sobre perdida de performance, y haciendo la comparación:

Disco Alineado = 100%

Disco Desalineado = 60~70% del rendimiento, en comparación a si mismo, si estuviera alineado.

Hablamos de una perdida de rendimiento próxima al 40%.

¿Cómo solucionamos este problema?

Como nos dijeron en ese momento, “fácil, eliminas y creas de nuevo la partición con alguna herramienta en la que puedas determinarle el sector de inicio de la partición”.

Como podrán imaginarse, la solución no es exactamente la más agradable, porque hablamos de sistemas con información delicada, o que necesitan disponibilidad las 24 horas, y rehacer sus discos, normalmente no es una opción. No obstante, si se va a agregar un nuevo servidor al grupo de servidores de su empresa (o un nuevo juego de discos al servidor o al storage), es recomendable que cuide este detalle, antes de empezar a configurarlo correctamente para ponerlo en producción.

Si desean interiorizarse más sobre este problema en particular, no deje de leer la siguiente publicación, que explica en forma bastante amplia los detalles sobre este problema.

3. Data Files tú ven aquí, Transaction Log tú ve allá:

Quien no ha creado una base de datos, y ha dejado el transaction log en la misma ubicacion que el archivo de datos? Aquel que este libre de ese hecho, que tire la primera piedra.

Desde el punto de vista de ambientes de producción, ese es un error, principalmente por las características de ambos archivos, ya que lo más recomendable es que los tengamos en almacenamientos separados (sean estos discos o arreglos de discos).

¿Por qué esto?

Los Data Files o archivos de datos MDF, son archivos que sufren de grandes cantidades de lecturas y escrituras aleatorias, en cambio el Transaction Log o log de transacciones LDF, no recibe más que lecturas y escrituras secuenciales.

Es por ello que es recomendable tener los Data Files MDF en un arreglo de discos separado al que contiene el Transaction Log. Para el arreglo de discos que almacene los Transaction Log no es necesario un arreglo complejo, al contrario, un RAID 1 de 2 Discos normalmente bastaría ya que no todas las operaciones en los Data Files son de insert/update/delete que vayan a hacer uso del Log, y en general las ráfagas de lecturas y escrituras secuenciales de los discos son bastante altas.

Obs: al ser RAID 1, la configuración hace que los 2 discos trabajen como 1 solo, no obstante, la velocidad de transferencia sera siempre la más baja de los 2 discos del arreglo, pero esta a la vez, suele ser bastante alta en cualquier disco SAS de la actualidad. (Mayor a los 100 Mb/s).

Rendimiento de Memoria:

En otras, el problema radica en la memoria que tenemos disponible en el sistema, y que por ciertas opciones, no podemos dar uso.

1. SQL Server en Sistemas de 32 bits con más de 4 Gb de memoria (cuando 64 bits ya no es una opción):

Muchas veces nos encontramos con servidores relativamente viejos (o nuevos), que en su momento se amplio la capacidad de memoria, pero como se tenia inicialmente en ellos sistemas operativos de 32 bits, la ampliación de la memoria no surtia efecto real pasado los 4 Gb, ya que por restricciones de la arquitectura x86, no es posible utilizar mas de 32 bits de direccionamiento de memoria, y los servidores ya estaban en ambiente de producción, y con carga, por lo que hacer algo con ellos como reinstalarlos, etc, es imposible.

Como solución parcial a este problema, tenemos que los procesadores actuales permiten la utilización del PAE, que significa “Physical Adress Extensions” o Extensiones de Direccionamiento Físico. Con esta opción habilitada en los sistemas operativos de 32 bits (a nivel del boot.ini, agregar /PAE al mismo luego de las opciones del sistema operativo indicado para el inicio), reconoce que tiene mas de 4 Gb de ram, y lo permite hacer accesible a ciertos procesos del S.O., no obstante, para el SQL Server, esa memoria extra, nuevamente no es accesible.

Para poder hacer accesible ese excedente de ram, debemos setear en las propiedades del servidor, la utilizacion del AWE que viene a complementar al PAE.

AWE significa “Address Windowing Extensions” o Extensiones de Direccionamiento de Ventana, es un mecanismo de manejo de direcciones que permite a ciertas aplicaciones hacer uso de esa memoria ram que normalmente no les es accesible.

Seteando esa opción, e indicando los parámetros de Min y Max Memory, podemos indicar y/o limitar cuanta memoria ram sera utilizada por nuestras instancias de SQL Server en sistemas de 32 bits con mas de 4 Gb de ram.

Para mas información acerca del AWE se pueden leer los siguientes artículos en inglés: Artículo 1, Artículo 2.

Rendimiento de Procesador:

Y a veces, la culpa parece estar en el corazón de nuestros sistemas.

1. Paralelismo, ejecución de múltiples procesos:

Uno de los mayores problemas a la hora de realizar las configuraciones del motor, es que pensamos “Oh, mi servidor nuevo de 4P tiene 48 núcleos y sera demasiado veloz”.

La idea en general, es cierta, los 48 núcleos nos brindarán un rendimiento y performance bastante elevado, pero la pregunta que deberíamos hacernos siempre es ¿Puedo aprovechar aún mas esos 48 núcleos? Para muchos parecerá ridícula la respuesta, pero es “Sí, se puede”.

¿Cómo exploto el verdadero potencial de mi servidor multi-núcleo?

Por default, nuestros servidor SQL Server al terminar de instalarse, se configura con un nivel de paralelismo 0 (cero). Esto indica que el SQL Server será el encargado de determinar automáticamente el nivel de paralelismo adecuado para las consultas. El problema aquí, es que el valor mas alto de sus cálculos siempre lo obtiene ejecutando de forma serializada el proceso, esto indica que si el servidor esta realizando una consulta muy pesada, no dará cabida a otras consultas menores hasta no terminar antes la consulta pesada.

Para modificar este valor, debemos acceder a las opciones avanzadas de configuración del motor de base de datos, por medio del SQL Server Management Studio, y setear el valor “max degree of parallelism” en un valor distinto de 0 (cero).

En la imagen, los settings de una instalación de SQL Server 2008 R2 Express Edition.

¿Como determinamos que cantidad de núcleos indicar?

En la capacitación que nos dieron, nos dijeron que analicemos la cantidad de nucleos disponibles, por la cantidad de procesadores fisicos y de ellos obtengamos el valor que consideremos mas adecuado. Si tenes un server 2P, donde cada procesador es un HexaCore por ejemplo, deberias calcular nros. que multiplicados den 12.

Por ejemplo, 2×6, 3×4, 4×3 y 6×2.

¿Como se interpretaría estos valores?

2×6 = Administro 2 procesos y le destino 6 núcleos a cada 1.

3×4 = Administro 3 procesos, y cada uno utiliza 4 núcleos.

4×3 = Administro 4 procesos y cada uno usa 3 núcleos.

6×2 = Administro 6 procesos en paralelo, utilizando cada proceso un total de 2 núcleos.

La selección de cual es el valor mas adecuado, queda a criterio del DBA, previo análisis de carga del equipo, por supuesto.

En el ejemplo de la imagen de arriba, tenemos 8 nucleos y 2 procesadores. Los calculos disponibles serian 2×4 o 4×2 nada mas.

Si indicáramos el valor de “max degree of parallelism” igual a 2, el servidor seria capaz de ejecutar hasta 4 procesos al mismo tiempo, utilizando estos como máximo el potencial que les brinde 2 núcleos.

En el ejemplo con el cual empezamos este apartado, que decia 48 núcleos, tenemos un abanico de posibilidades mucho más amplio.

2×24, 3×16, 4×12, 6×8, 8×6, 12×4, 16×3 y 24×2. Creo que no hace falta explicar nuevamente esto

El análisis y establecimiento correcto de esta opción, permite que nuestro servidor trabaje bajo ciertas situaciones, de una forma aun más eficiente de lo que habría podido hacerlo, aun teniendo 48 núcleos disponibles para ello.

Para más información sobre paralelismo, puede visitar estos artículos sobre paralelismo en MSDN en inglés: Artículo 1, Artículo 2

2. TempDB, ¿Uno para todos, y todos para uno?:

Este es tal vez la parte más “extraña” de este post. ¿Que tiene que ver el TempDB con el rendimiento del procesador?

Lo mismo me pregunte, pero me dijeron que el TempDB era importante de setearlo respecto al procesador y al disco, y he aquí el porque de eso.

El TempDB, si bien es una de las bases de datos instaladas por default ya que es del grupo de bases de datos del sistema, muy poca gente le da poca importancia a la misma a la hora de analizar si seria bueno optimizarla para obtener el máximo de performance, hasta que finalmente nos encontramos con cuello de botellas grandes al realizar transacciones que trabajan con temporales que se almacenan en el TempDB. ¿Cómo los solucionamos?

Algunos tips que nos dieron en aquel momento fueron:

• Recovery Model: Simple: es recomendable que el modelo de recuperación de la base de datos sea Simple, para que el LOG de transacciones no crezca ni se mantenga demasiado para esta base de datos, después de todo, es una base de datos para almacenar información temporalmente mientras se ejecute algún proceso.
• Tamaño del TempDB: si el TempDB pesa comúnmente 22~23 Gb, debemos cambiarle el tamaño inicial a 24 Gb. Esto es necesario hacerlo para evitar que el servidor constantemente este escribiendo la misma al disco, sea para pedir espacio en disco, o para devolverlo al Sistema Operativo.
• Auto-incremento del TempDB: ya pusimos un tamaño inicial bastante alto, ahora, establezcamos el auto-incremento. Normalmente los valores de auto-incremento se definen en porcentajes o valores en Mb bastante reducidos, es por ello que lo recomendable es hacer que tu TempDB no crezca un %, sino un nro. de Mb fijo y que represente aprox. el 10% del tamaño inicial de la base de datos que esta establecido. En el ejemplo anterior, seteamos el tamaño inicial en 24 Gb, entonces para el incremento, se establecerán incrementos en Mb, y de 240 Mb cuando se requiera.
• Un archivo del TempDB por Núcleo de Procesador: este es el motivo por el cual, esta el “Uno para todos, y todos para uno” en el título de esta sección. Este tip, se debe a que cuando se ejecutan operaciones que requieran que el procesador acceda al TempDB, este lo hace “por turnos”, es decir, al ser normalmente un archivo único, accede un núcleo del procesador a él, y cuando este termina, accede el otro, y así, hasta finalizar todos los núcleos del sistema (en el ejemplo de mas arriba, 48 núcleos es mucho para este tipo de retrasos en el acceso). La forma de maximizar el ancho de banda del disco y los accesos sin problemas de los distintos núcleos al TempDB, es dividirlo en la cantidad de archivos suficientes, como para que se tenga 1 archivo por núcleo disponible en el sistema. Es decir, en nuestro ejemplo de 48 núcleos, tendríamos 48 archivos para el TempDB, cada uno de los cuales pesaría 500 Mb (24 Gb / 48 núcleos =500 Mb). También deberíamos dividir el incremento en 5 Mb por cada archivo (240 Mb / 48 núcleos = 5 Mb), para mantener los 240 Mb de crecimiento general.
• TempDb en otro arreglo de discos: de ser posible, al igual que los Transaction Logs, es recomendable que el TempDB se almacene en un arreglo de discos separado de las demás bases de datos del sistema y de Usuarios, y que este sea un arreglo de discos bien rápido para las operaciones de lectura/escritura aleatorias.

Mientras elaboraba esta parte del post, busque acerca de optimizaciones del TempDB, y encontré un articulo de MSDN que comenta en gran parte lo aquí descrito. Además de esto, incluyo este artículo, que nos permite saber como hacer para mover bases de datos del sistema a otras ubicaciones, para complementar el último ítem de esta sección. El texto está en inglés para los interesados en leerlo.

La idea de este breve articulo, es resumir en él, algunas de las diversas optimizaciones que podemos realizar a SQL Server para que el mismo se desempeñe mejor en nuestros entornos, sean de pruebas, desarrollo o producción.

Cualquier sugerencia o tip para ser añadido / corregido, que nos dejen como comentario al artículo, será bienvenido

Articulos Relacionados:

• Instalación SQL Server 2008… Una guía más? Porqué no :)
• Capturar Output de los Procedimientos Almacenados de SQL Server con Java
• Como programar en n-Capas con C# – SQL Server (Parte 2)
• Realizar Consultas Paginadas en SQL Server, MySQL, Oracle & PostgreSQL
• Importar datos de MS Excel 2007 [.xlsx] a SQL Server

Etiquetas: Bases de Datos, Microsoft, MsSQL, SQL, Windows

• Invocar métodos del Servidor con JavaScript en ASP.Net →
• ← Compilación condicional en dotNet