TN229SQL - SQL Server Query Store

TN229SQL - SQL Server Query Store

En este documento se analizará a modo de introducción las posibilidades que nos brinda la herramienta Query Store para el análisis de queries ejecutadas contra una base de datos SQL.

Las principales características de esta herramienta son:
- Captura de queries y plans junto a sus respectivas estadísticas. Con lo que nos permite una revisión de la información en tiempo actual y de información histórica.
- Su activación supone un impacto bajo en el rendimiento del servidor de base de datos (3-5% según Microsoft). La información capturada se registra en tablas de sistema internas de la base de datos.
- Su activación se realiza a nivel de base de datos.
- Muestra la información en una serie de Views predefinidas y configurables totalmente integradas en Management Studio.
- Permite la creación de reports personalizados orientados a BI.
- Disponible a partir de la edición 2016 de SQL Server incluido en versiones Express.

Dicha herramienta se utiliza normalmente para:
- Identificar patrones de ejecución
- Analizar la actividad y carga de trabajo en la base de datos dependiendo del momento
- Detectar Queries que consumen recursos excesivamente
- Detectar Queries que suelan fallar
- Detectar Queries utilizadas con regularidad

Nos permitirá identificar cuales son las queries que más recursos consumen, las que son más lentas en su ejecución o fallan, y cuales son sus respectivos planes de ejecución entre otras opciones .

Un plan de ejecución es una colección de los distintos pasos involucrados en la ejecución de sentencias contra una tabla de la base de datos. Los planes de ejecución de queries pueden ser estimados o reales. Estimados son los que se generan sin realmente llegar a ejecutar la query y se basan en una estimación de comportamiento esperado. Las reales se generan una vez ejecutada la query.

Habilitar Query Store dentro de una base de datos

1. Abra SQL Server Management Studio. En el panel de Object Explorer expanda la información del servidor de SQL para visualizar las distintas bases de datos. Seleccione una base de datos con el botón secundario del ratón y elija la opción Properties del menú emergente:


2. En la ventana emergente, en el panel Select a page seleccione Query Store. Seguidamente en la sección General seleccione Read Write en el desplegable de la opción Operation Mode (Requested).


3. Seleccione el botón OK para aceptar los parámetros por defecto y cerrar la ventana.
4. Al desplegar el contenido de la base de datos veremos que se ha generado la carpeta Query Store y dentro una serie de reports.



Visualizar estadísticas de ejecución de queries
En este documento utilizaremos un ejemplo de report para visualizar la información general de las queries ejecutadas en cierto periodo de tiempo:
1. Dentro de la carpeta Query Store seleccione la opción Overall Resource Consumption.


2. En el panel principal se nos mostrará un informe con las diferentes estadísticas de la última hora. Pulse en el botón Configure, situado en la parte superior derecha, para cambiar la configuración y así mostrar diferentes estadísticas y cambiar el rango de tiempo de consulta. Seleccione OK.



El tipo de transacciones de las cuales Query Store puede registrar sus estadísticas son:
- Insert
- Select
- Update
- Delete

En el ejemplo mostrado en este documento se analiza las estadísticas de una query SELECT contra Historian la cual ha requerido de un tiempo elevado de ejecución.
3. Para conocer los valores de cada una de las estadísticas sitúe el cursos del ratón encima de la barra del gráfico de barras.


4. Mostrando las estadísticas por defecto se nos muestran la siguiente información:
  1. Duration. Donde se nos muestra el tiempo que ha tardado T-SQL en ejecutar la transacción de las distintas queries. En el ejemplo mostrado vemos que la SELECT ha tardado mucho más que el resto de queries del intervalo de tiempo seleccionado y que su tiempo total ha sido 154240.37 milisegundos.
  2. CPU TIme. Es el tiempo consumido por la CPU para procesar la transacción asociada a la queries. En este caso 118869.97 milisegundos.
  3. Physical Reads. Es el volumen de datos que han necesitado leer de disco las queries ejecutadas. En el caso de la SELECT de Historian ha necesitado leer 80 KB de los History Blocks en disco.
  4. Memory Consumption. Es el volumen de datos de memoria RAM que han necesitado las queries ejecutadas. En comparación con el resto de queries ejecutadas la SELECT de Historian vemos que es la que más recursos ha necesitado pero la variación no es tan alta respecto al resto.


5. A continuación se analizará  el uso de la CPU. Para ello seleccione la barra de la query SELECT que procederemos a analizar en el gráfico de barras CPU Time. Esto abrirá una pestaña nueva en el panel principal titulada Top Resource Consumers y en la que podemos identificar las siguientes secciones:
  1. Tabla comparativa con las distintas queries ejecutadas en el intervalo de tiempo configurado y el tiempo de CPU utilizado.
  2. El momento en el que se ha ejecutado el plan asociado a dicha query
  3. El detalle del plan ejecutado dividido en dos subsecciones: el texto de la query y los distintos procesos de la ejecución del plan y sus relaciones. En este ejemplo, vemos que el mayor coste del plan ha sido el proceso Remote Scan, el cual se utiliza para el acceso a Linked Servers como es el caso del INSQL utilizado por Historian.


Detalle del plan de ejecución
Para profundizar en la información mostrada en la ejecución del plan mostraremos el siguiente ejemplo, donde se realiza una consulta ordenada a una tabla con un clustered index.


En el ejemplo anterior, vemos que para la transacción SELECT se ha tardado 6.79 milisegundos en ejecutar, pero que el 78% del tiempo ha sido consumido por la ordenación de la consulta. El 22% del tiempo ha sido invertido en buscar los índices que coincidieran con la condición de la consulta.

Nota Importante: Esta Nota Técnica se entrega “as is”, es decir, como complemento a la documentación del producto, pero no incluido dentro del ámbito del Soporte Técnico. Por tanto, cualquier mal funcionamiento derivado del contenido de esta nota técnica no es responsabilidad de Wonderware Iberia.