Optimiza el rendimiento de tus queries con DMV en SQL

Las vistas dinámicas de gestión (DMV) son de gran ayuda para que podamos solucionar diferentes problemas de nuestras queries SQL. Con su incorporación, seremos capaces de descubrir información que hasta ahora permanecía oculta relacionada con las sesiones de nuestras bases de datos.

¿Qué son las vistas dinámicas de gestión (DMV)?

Cada vez que se ejecuta una consulta en una base de datos, el servidor almacena valiosa información sobre toda la actividad que se desarrolla a su alrededor. A partir del año 2005 se introdujeron estas valiosas funciones que nos facilitan enormemente la tarea. Las DMV son funciones se encargan de buscar todos los datos que se han almacenado.

Posteriormente, nos devuelven información sobre los diferentes procesos internos de cada una de las consultas del servidor. Son útiles tanto para gestionar el propio servidor como las diferentes bases de datos que lo integran.

Los usos que podemos darles a los resultados que nos ofrecen son muy variados:

• Podremos conocer mejor el funcionamiento interno de nuestra instalación.
• Resolveremos problemas que vayan apareciendo con el tiempo y que ralenticen el flujo de trabajo.
• Optimizaremos el rendimiento en función del análisis que llevemos a cabo.

¿Dónde se almacena la información?

Toda la información se almacena como instancia. Es decir, cada consulta se almacena como copia en el servidor. No obstante, para facilitar el trabajo también podemos llevar a cabo un filtrado que solo analice una tabla, o incluso una única consulta.

¿Cómo pueden ayudarnos las DMV en nuestro día a día en SQL?

Las vistas dinámicas de gestión (DMV) son un add-in fundamental para el análisis de datos y resultan útiles para optimizar muchas áreas de trabajo.

1. Uso excesivo de memoria o de CPU

Hay varias razones que pueden hacer que nuestras queries saturen los recursos del servidor. Lo más habitual es que esto esté causado por una gran cantidad de consultas que se ejecutan de manera simultánea. En este caso, podemos encontrar las culpables mediante un código que seleccione aquellas que se encuentran a la cabeza en lo que se refiere al consumo de CPU de modo histórico.

Si la saturación se está produciendo en directo, también tenemos la posibilidad de hacer la misma búsqueda para las queries que se encuentran activas en ese preciso instante.

El consumo excesivo de memoria RAM es también muy habitual. Al igual que ocurre con los recursos del procesador, podemos encontrarnos problemas que se hayan dado de manera histórica o en directo. Ambos pueden encontrarse almacenados con una función que determine cuáles son los diez elementos con una mayor necesidad de este recurso.

Una vez hayamos encontrado las culpables, podemos eliminar esas consultas que están causando problemas para que todo vuelva a la normalidad. En muchas ocasiones, el elevado consumo de memoria RAM se debe a un tamaño excesivamente grande de las bases de datos.

2. Deficiencias de E/S

Cuando el rendimiento se ve perjudicado por problemas de E/S (entrada o salida) suele deberse a dos tiempos de espera principales:

• PAGEIOLATCH_*. Cuando esto sucede, es común que encontremos un uso de disco muy elevado. En ocasiones, puede llegar al 100 %, lo que conlleva la saturación del resto de las consultas.
• WRITE_LOG. Este tipo de espera suele aparecer cuando aparece un problema en el registro de transacciones y su caché se comienza a vaciar.

Para evaluar cualquiera de estos errores, podemos emplear dos herramientas de gestión:

• sys.dm_exec_requests
• sys.dm_os_waiting_tasks

Eso sí, solo aparecerán en el reporting si se están produciendo en ese mismo instante.

3. El problema está en un espacio de trabajo temporal

La base de datos tempdb se emplea mayoritariamente para tablas temporales, pero puede causar retrasos por saturación. En este caso, los errores no contendrán IO, por lo que serán, en gran parte, PAGELATCH_*. Podemos confirmar que se trata de esta base de datos con la vista siguiente:

• sys.dm_exec_requests

La mayoría de las veces esta situación se produce debido a un aumento del uso de esta base de datos. Para solucionarlo es necesario que reduzcamos su tamaño.

4. Poca eficiencia de concesión de recursos

No siempre las bases de datos producen saturaciones de la CPU. Un tipo de espera muy habitual es RESOURCE_SEMAPHORE. En estos casos, el fallo reside en la gestión que hace el sistema a la hora de conceder la memoria necesaria a las diferentes funciones. Mediante un sencillo código podremos descubrir si se trata de una espera principal o secundaria.

DMV para la toma de mejores decisiones

A partir de los datos que obtenemos con las diferentes vistas, podemos tomar decisiones fundamentadas en hechos. Es lo que se conoce como data-driven decisions. Para ello, debemos orientar nuestra estrategia a potenciar nuestros objetivos, pero siempre teniendo en cuenta los datos que hemos recopilado.

Las DMV ofrecen un entorno incomparable para tomar esta clase de decisiones. Nunca había sido tan sencillo extraer información de las bases de datos SQL. Además, la cantidad de datos que podemos almacenar es muy elevada, ya que gracias a la caché de estos sistemas tenemos la posibilidad de revisar eventos pasados sin perdernos nada.

¿Afectan las vistas dinámicas de gestión al rendimiento?

Puesto que trabajamos con datos que ya se encuentran en la memoria y no requieren de cálculos posteriores, su impacto es mínimo. Lo único que llevan a cabo cada una de las diferentes vistas es una recuperación de esos datos, que de otro modo permanecerían ocultos. Eso sí, si tratamos de ejecutar funciones que sí requieran algunos cálculos, puede sufrirse una saturación importante. Uno de estos ejemplos es la función que se encarga de calcular el porcentaje de fragmentación del índice (sys.dm_db_index_physical_stats).

Conclusión

El entorno de trabajo con bases de datos SQL puede beneficiarse enormemente de las ventajas que ofrecen las DMV a la hora de ahorrar tiempo y mejorar el rendimiento. El funcionamiento del servidor es fundamental para que todo cargue en el menor tiempo posible. Por eso, aprovechar toda la información que nos ofrecen para tomar las mejores decisiones es prioritario en cualquier instalación. Este recurso supondrá un soplo de aire fresco para los trabajos de desarrollo de la empresa.

Antes de irte...

• No te pierdas los 10 trucos SQL que cualquier developer debe conocer