Select Page

Configuraciones básicas en sql server

Configuraciones básicas en sql server

En esta ocasión os voy a mostrar algunas de las tareas básicas de configuración de SQL que hará que nuestro Server multiplique su rendimiento.

Presentación del Entorno

tunning sql 01Como siempre, vamos a poner el artículo en contexto mostrando el entorno. Partimos de una máquina virtual en la que acabamos de instalar nuestro SQL Server. Nosotros hemos puesto la versión 2012 pero todas las recomendaciones que demos aquí son válidas para versiones anteriores.

Como hemos dicho antes, hemos instalado desde cero un SQL en Windows Server 2012 (puedes ver cómo hemos realizado estos pasos en nuestros artículos anteriores “Instalar SQL Server 2012” y “Crear Plantilla en Hyper-V“). En cuanto a particularidades de la máquina virtual, como mostramos en la captura de la derecha se ve que hemos montado dos discos. El primero para el propio sistema operativo y los archivos de instalación y componentes compartidos de SQL, mientras que el segundo lo usaremos para albergar los ficheros de las distintas bases de datos que creemos. La memoria es otro punto clave que hemos configurado en nuestro entorno ya que hemos establecido un máximo de memoria de 8 GB. El resto de características es bastante estándar dejando 2 procesadores y 1 tarjeta LAN. Presentado el entorno, vamos al lío.

Nota: Recordad que nuestro entorno es de laboratorio por lo que nuestra máquina no necesita grandes configuraciones. No configuréis vuestros entornos de producción en base a esta mñaquina ya que podéis tener muchos problemas de rendimiento y fallos de SQL.

Configuración de la Memoria por Instancia

Tal vez este punto le parezca una tontería a mucha gente pero en realidad es de vital importancia. Si queremos que nuestro SQL funcione perfectamente no puede andar luchando constantemente con el Sistema Operativo por una “mijá” de memoria. Y es exactamente esto lo que sucede si dejamos los valores por defecto. Por eso vamos a empezar por establecer la pregunta clave:

¿Cuánta memoria máxima dejo para el SQL?

Y la respuesta es, depende. 🙂 Depende, principalmente, de tu entorno. El numero de procesadores, la cantidad total del memoria, la arquitectura de la plataforma, etc… Pero tranquilo. Algún gurú de SQL se curró una formulita con la que podemos obtener la cantidad de memoria máxima a configurar en nuestro entorno en función del entorno. esta formula es:

SQL Max Memory = TotalMem_enMB – (NumdeSQLThreads * ThreadStackSize) – (1024 * CEILING(NumCores/4))

Para saber que poner en NumdeSQLThreads y ThreadStackSize usad estas fórmulas:

ThreadStackSize = 1MB en x86 // 2MB en 64-bit (x64) // 4MB en 64-bit (IA64)

NumdeSQLThreads = 256 + (NumProcesadores – 4) * 8

Haciendo los cálculos con esas fórmulas os saldrá el tamaño máximo de memoria de instancias de SQL que, en total, vuestro servidor puede albergar. Es decir, si queremos montar 2 instancias de SQL en nuestro servidor, tendremos que configurar las instancias para que, sumando los 2 valores de memoria máxima que establezcamos, no supere el valor que nos devuelve esta fórmula. En nuestro caso, el valor devuelto por la fórmula es 6720 por lo que podría configurar una instancia con 4GB (4096MB) y la segunda instancia con un máximo de 2624MB.

No obstante, para facilitar el trabajo, hemos creado una tabla con los los resultados de un Servidor estándar con varias cantidades de memoria física.

Hemos tomado como base para estos cálculos un Servidor Quad-Core x64
Memoria Física del Servidor (en MB) Memoria de Todas las Instancias (en MB)
8192 MB (8GB) 6720 MB
12288 MB (12GB) 10816 MB
16384 MB (16GB) 14912 MB
24576 MB (24GB) 23104 MB
32768 MB (32GB) 31926 MB
65536 MB (64GB) 64064 MB
131072 MB (128GB) 129600 MB

No obstante, desde SQL Server 2008 no es necesario establecer estos valores si sólo vamos a tener una sola instancia en nuestro Servidor ya que, en el arranque del SQL se determina la cantidad de memoria que puede alojar dependiendo del Sistema Operativo pero sólo si se tiene una sola instancia.

tunning sql 02Con el valor calculado, vamos a ver dónde hay que configurarlo. Abrimos nuestro Management Studio, botón derecho sobre el nombre de la instancia y Propiedades. En la ventana de propiedades, cambiamos a la ventana de Memoria y establecemos el valor que queramos en el apartado “Maximum server memory”. Listo, ya tenemos limitada nuestra instancia. Si tuviésemos más intancias habría que repetir la operación en cada una de ellas. Tras este paso, yo suelo reiniciar la instancia. Para ello, botón derecho sobre el nombre de la instancia y seleccionamos Restart. Una vez iniciada, el cambio estará aplicado.

Memoria Mínima del Servidor

Puede que muchos os preguntéis para qué sirve el apartado de la memoria mínima que hay en la ventana de configuración que vimos antes. Esta configuración impide que el SQL libere memoria por debajo del límite que le establezcamos. Esto puede ser útil en instancias con mucha actividad, aunque habría que establecerlo con cuidado para evitar que el sistema consuma más memora de la que necesita. Por ejemplo, para instancias de SQL que sirvan en granjas de Sharepoint, yo recomiendo establecer este valor al 25% de la Memoria Max cuando los datos del Sharepoint cambian constantemente o el 60% de la memoria Max si los datos apenas se modifican.

Configurar Max Degree of Parallelism

tunning sql 02Aprovechando que estamos en la ventana de propiedades de la instancia, vamos a hablar de esta opción controvertida. Se ha escrito mucho acerca de esta opción avanzada y todavía no tengo claro hasta qué punto es beneficioso activarla. Pero antes de valorar si activarla o no, veamos qué es. Mediante esta opción se establece el número máximo de procesadores que el SQL Server puede usar para una consulta (query). Si el servidor SQL necesita muchos datos (o muchos conjuntos de registros) para una query determinada, en ocasiones divide esa consulta en consultas más pequeñas. Cada una de esas consultas devuelven un subconjunto del total de registros que tiene que devolver. De esta manera, el SQL puede usar más de un procesador para una misma consulta lo que, en teoría, aporta una mejora de rendimiento al devolver los resultados en menos tiempo. Esto es la teoría ya que en la práctica, el uso de esta opción puede resultar nefasta para el rendimiento del Servidor o, por ejemplo, puede impedirnos conocer el tiempo de ejecución de una query determinada. Por estos motivos, os dejamos a vosotros la elección de desactivarlo aunque os indiquemos cómo hacerlo.

Para desactivar esta opción, abrimos las propiedades de la instancia, nos vamos a Advanced y en la parte de abajo veremos que la opción por defecto es 0. Esto significa que está activada (el cero le indica al servidor que establezca automáticamente el grado de paralelismo), mientras que si ponemos 1 desactivaremos la opción.

Cambios en la Model Database

tunning sql 04Esta base de datos de sistema es usada por el SQL Server a modo de plantilla para crear nuevas bases de datos. Cuando se crea una nueva base de datos de usuario, el SQL copia el contenido de la base de datos model en la nueva y rellena el resto del espacio con páginas de datos vacías. Por eso es importante tener bien configurada esta BD antes de generar nuevas ya que así, al crearlas, heredaremos sus configuraciones correctas. Os indicamos algunas de las configuraciones que se deberían cambiar:

Configurar el Initial Size

tunning sql 02El tamaño inicial indica el tamaño de la base de datos en el momento de ser creada. Por defecto, en SQL Server 2012, está establecido a 4MB lo cual es bastante poco e implica que cuando la base de datos se cree va a necesitar más espacio. Por regla general, es recomendable establecer como tamaño inicial de las bases de datos el tamaño estimado para un año. Así el rendimiento de la misma aumentará ya que no tendrá que aumentar el espacio inicialmente. Para configurar correctamente el el tamaño tenemos que hacer lo siguiente:

Desplegamos el árbol de bases de datos de sistema, botón derecho sobre model y seleccionamos propiedades. Ahí veremos los valores para el fichero de datos y para el fichero de log. La recomendación es que se establezca el valor necesario para el fichero de datos primero y para el fichero de log, se calcule el 25% del tamaño del valor anterior.

Configurar el Auto Growth

El auto growth es una característica que permite al SQL aumentar el tamaño de las bases de datos de forma automática evitando que se queden sin espacio, lo cual obligaría al SQL a establecer la base de datos en Read Only, impidiendo a los usuarios la escritura de datos. Como era de esperar este crecimiento se puede modificar indicándole al SQL qué cantidad tiene que crecer cada vez que necesite espacio. Por defecto el valor está establecido en 1MB, lo cual no es muy optimo. El porque es muy sencillo. Imaginemos que tenemos el valor por defecto de 1MB, la base de datos ha llegado a su tamaño máximo establecido y un usuario necesita escribir un registro con 10MB (por ejemplo subir un documento de 10MB al Sharepoint). Como tenemos establecido el crecimiento en 1Mb tendrá que ejecutar el Auto growth 9 veces para alcanzar el tamaño necesario para poder escribir el dato. Al tener que ejecutar tareas adicionales el usuario detectará lentitud en el sistema. Vale, igual en el ejemplo con 10Mb no se nota demasiada lentitud, pero imaginad que queremos subir un fichero de 200MB. El SQL tendría que ejecutar 199 AutoGrowth y ya la cosa va cambiando.

Creo que ha quedado bastante claro que hay que cambiar este valor pero ¿qué valores poner?. Se pueden usar porcentajes o valores en megas. Mi recomendación es poner un valor fijo en megas ya que así siempre sabremos cuanto va a crecer nuestra base de datos de cara al aprovisionamiento en los discos. En cuanto al valor, para el fichero de datos, lo normal es poner la mitad del Initial Size. Para cambiar el valor hay que ir a la ventana que vimos en el apartado anterior. Ahí veremos el campo para el autogrowth.

Habilitar la “Instant File Initialization”

Cuando el SQL incrementa el tamaño de un archivo (es decir ejecuta un autogrowth), primero inicializa el nuevo espacio antes de poder usarlo. Esto es una operación en bloque que implica rellenar el nuevo espacio con páginas vacías (ceros). ¿Qué implica esto? pues que antes de que SQL pueda crear o hacer uso del auto growth, el SQL primero debe escribir el tamaño requerido con ceros y después podrá escribir el dato. Afortunadamente desde el SQL 2005 se implementó la función “Instant File Initialization” que permite crear el espacio requerido saltándose la inicialización del nuevo espacio. El resultado es que el alojamiento del nuevo fichero es instantáneo. Se han hecho mediciones de tiempo pero más o menos se puede concluir lo siguiente:

Sin Instant File Initialization

Crear una base de 20GB 14 minutos aproximadamenteAlterar una base de datos en 10GB 7 minutos aproximadamenteRestaurar una base de datos de 30GB 38 minutos aproximadamente

Con Instant File Initialization

Crear una base de 20GB 1,3 segundosAlterar una base de datos en 10GB 4 segundos aproximadamenteRestaurar una base de datos de 30GB 19 minutos aproximadamente

Como veis, las diferencias son enormes así que vamos a ver cómo habilitarlo. Lo cierto es que no es una característica que se habilite dentro del SQL, lo único que hay que hacer es dar privilegios de SE_MANAGE_VOLUME_NAME a la cuenta del servicio del SQL Server. Vamos a ver cómo:

tunning sql 08Lo primero que hay que hacer es identificar la cuenta de usuario del SQL, de modo que abrimos la consola de servicios (services.msc) y buscamos el servicio de SQL

tunning sql 09Con el usuario localizado, hay que abrir el Local Security Policy (secpol.msc). Una vez ahí nos situamos en en Local Policies -> User Rights Management -> abrimos la política Perform Volume Maintenance Task. Por defecto sólo estará el grupo de administradores (nuestra cuenta de usuario no debería ser miembro del grupo administradores) por lo que tendremos que añadirla.

Una vez añadido el usuario en la política, sólo nos queda reiniciar el SQL para que se aplique el cambio. Fácil y sencillo, para toda la familia ¿no?

Con esto damos por terminado este artículo sobre las configuraciones iniciales del SQL que, sin duda, mejorarán enormemente el rendimiento en nuestro servidor SQL. Espero que os haya gustado y que lo encontréis de ayuda.

Saludos!

About The Author

Crower

Mi nombre es Mariano de Pedro. Actualmente vivo en Guadalajara donde también trabajo aunque he trabajado durante mucho tiempo en diversas empresas en Madrid. Llevo trabajando con tecnologías Microsoft más de 15 años

Leave a reply

Si continuas utilizando este sitio, aceptas el uso de las cookies. Más información

Las opciones de cookie en este sitio web están configuradas para "permitir cookies" para ofrecerte una mejor experiéncia de navegación. Si sigues utilizando este sitio web sin cambiar tus opciones o haces clic en "Aceptar" estarás consintiendo las cookies de este sitio.

Cerrar