Herramientas para la optimización

El plan de ejecución de una consulta

SQL Server analiza las consultas, los procedimientos y los triggers y el optimizador de consultas almacena el plan de ejecución en la memoria de SQL Server. Más concretamente, este almacenamiento se hace en el área de memoria llamada memoria caché del plan. Es posible analizar esta versión compilada de la consulta para entender mejor las elecciones realizadas por el optimizador de consultas y reaccionar para permitir una ejecución más rápida de la consulta. Esto puede resultar en una nueva redacción de la consulta, la adición de índices, la actualización de estadísticas, etc.

La optimización de las consultas no es el único punto a considerar para resolver problemas de rendimiento, pero tampoco es algo que se deba pasar por alto. Esto se debe a que centrarse en problemas de memoria cuando la consulta está mal escrita, puede enmascarar temporalmente tiempos de respuesta deficientes, pero el problema volverá a ocurrir cuando aumente el volumen de datos.

Para ver el plan de ejecución en SQL Server Management Studio, hay dos opciones:

El generador de perfiles

Para capturar la actividad del servidor y así poder analizar la carga de trabajo enviada a un servidor SQL, se ofrecen dos herramientas: el generador de perfiles (herramienta antigua marcada en desuso por Microsoft, pero muy ergonómica e integrada en otras herramientas de SQL Server) y eventos extendidos, que le permiten iniciar sesiones en segundo plano.

El generador de perfiles es, a las consultas, lo que una herramienta como WireShark es para la red: en lugar de capturar tramas de red, captura consultas SQL. Estas capturas se pueden configurar para indicar los tipos de consulta a registrar, así como la información necesaria (como los filtros WireShark). Esto se puede hacer a partir de plantillas predefinidas.

El generador de perfiles se puede iniciar desde Management Studio en el menú Herramientas. Entonces es necesario conectarse a una instancia de SQL Server (como para Management Studio). A continuación, se solicitan los parámetros de una nueva pista. El modelo Standard (predeterminado) es adecuado en la mayoría de los casos:

Tan pronto como se pulsa el botón Ejecutar, empieza el registro de la actividad:

Para detener la grabación de la traza, vaya al menú Archivo - Detener seguimiento. Entonces es posible guardarlo en forma de archivo .trc, de manera que puede ser leído nuevamente por el generador de perfiles o analizado por el asistente de configuración...

Eventos extendidos

1. La creación de sesiones

Los eventos extendidos reemplazarán al generador de perfiles, por lo que las nuevas funciones no aparecerán en el generador de perfiles. Una ventaja de los eventos extendidos es poder iniciar una sesión de análisis en segundo plano (sin necesidad de una sesión interactiva como con el generador de perfiles).

Para capturar la actividad, primero es necesario definir una nueva sesión desde la rama Administración - Eventos extendidos - Sesiones.

La creación de una nueva sesión se puede hacer con el asistente o parametrizando directamente la sesión. El asistente permite construir la sesión a partir de un modelo en el que algunos son equivalentes a los del generador de perfiles, mientras que el paso por el cuadro de diálogo permite crear la sesión definiendo las diferentes opciones en el orden deseado. La modificación de una sesión se realiza siempre desde el cuadro de diálogo de propiedades sea cual sea el modo de creación elegido.

Desde la sección Eventos es posible seleccionar los eventos. En el caso de utilizar una plantilla, los eventos están predefinidos para su análisis.

Ejemplo

Eventos definidos por defecto en una plantilla Estándar.

Por último, la sección Almacenamiento de datos es importante ya que define la ubicación donde se almacenarán estos datos...

El monitor de rendimiento (monitor de sistema)

Se trata del monitor de rendimiento de Windows, al que se han añadido numerosos contadores durante la instalación de SQL Server.

Los principales objetos específicos de SQL Server son:

Optimización de la memoria

Por defecto, SQL Server gestiona automática y dinámicamente la cantidad de memoria que necesita. Esta opción debe conservarse en la mayoría de los casos. Sin embargo, es posible fijar las cantidades de memoria mínima y máxima y el tamaño del trabajo.

El Monitor de rendimiento va a permitir vigilar la utilización de la memoria para asegurarse de que el servidor dispone de suficiente memoria.

El contador utilizado es Administración del buffer: Duración prevista de la página (en inglés Page Life Expectancy). Este contador indica la duración media de presencia en la caché de las páginas de datos. Entonces, cuanto más alto sea este contador, mayor será el porcentaje de lectura de la página de datos en la memoria (en lugar del archivo en el disco) es alto, por lo que mejor será el rendimiento. Por lo tanto, un valor de unos minutos será demasiado bajo porque tan pronto como las páginas hayan llegado a la caché, serán reemplazadas por nuevas páginas en la memoria. Por lo tanto, deben leerse desde el disco duro.

Tenga cuidado con la interpretación de este contador: una lectura al comienzo del día puede no ser representativa de la realidad si se ha realizado un trabajo con datos durante la noche. En este caso, los datos afectados por este trabajo "contaminaron"...

El asistente de Configuración del motor de base de datos

El asistente Configuración del motor de base de datos tiene como objetivo ofrecer la creación del índice y de estadísticas enfrentando la organización actual con una carga de trabajo.

La carga de trabajo corresponde a una traza capturada con anterioridad por el generador de perfil de SQL Server (archivo .trc) o a un script Transact SQL.

A partir de esta carga de trabajo, la herramienta va a ofrecer una reorganización del esquema lógico añadiendo índices adicionales, particionando algunas tablas o bien proponiendo además la creación de vistas indizadas. Las propuestas hechas por el asistente tienen como objetivo reducir el coste estimado por el optimizador de consultas para la carga de trabajo analizada.

Durante el análisis de una carga de trabajo, es necesario configurar tres elementos: