Query Store en SQL Server (Parte II)

Query Store en SQL Server (Parte II)

Hola a todos.

Vamos a continuar la serie de entradas sobre la nueva herramienta de SQL Server 2016 Query Store. La anterior entrada, introduciendo un poco la herramienta,  se puede encontrar en el siguiente link:

https://www.gpsos.es/2019/05/query-store-en-sql-server-introduccion-parte-i/

En esta entrada vamos a ver cómo se puede habilitar la herramienta y las diferentes opciones de puesta en marcha, por medio de la herramienta Management Studio como por medio de comandos en Transact-SQL

Parámetros de uso de Query Store

Para habilitar Query Store es necesario establecer un valor para algunos parámetros, que configuran las diferentes opciones del almacenamiento.

Los parámetros son los siguientes

  • OPERATION_MODE

Es el modo en que está trabajando Query Store. Por defecto es READ_WRITE, pero también puede estar READ_ONLY, por ejemplo si se llena el almacen y no puede seguir escribiendo.

  • STALE_QUERY_THRESHOLD_DAYS

Indica el número de días que se almacenan los datos en el Query Store. El valor predeterminado es de 30 días.

  • DATA_FLUSH_INTERVAL_SECONDS

Frecuencia (en segundos) en la que los datos son escritos desde memoria a Query Store. Para optimizar el rendimiento del sistema, los datos se escriben de manera asíncrona. El valor por defecto es 900 segundos (15 minutos).

  • MAX_STORAGE_SIZE_MB

Tamaño máximo que puede alcanzar el Query Store, en MB. El valor por defecto es 100Mb. Si se llena este espacio reservado, el OPERATION_MODE cambia a READ_ONLY y deja de recoger nuevos datos.

  • INTERVAL_LENGTH_MINUTES

Intervalo de tiempo en el que se agregan los datos de estadísticas de ejecución en tiempo de ejecución al Query Store. El valor predeterminado es 60. Solo se puede especificar uno de los siguientes valores: 1, 5, 10, 15, 30, 60 o 1440 minutos.

  • SIZE_BASED_CLEANUP_MODE

Establece el modo de funcionamiento del proceso de limpieza cuando se alcanza el tamaño máximo en Query Store. Puede tener dos valores, AUTO (valor por defecto) y OFF

  • QUERY_CAPTURE_MODE

Determina si Query Store captura todas las consultas o solamente un conjunto de ellas, en función del consumo de recursos y número de ejecuciones, o bien si realiza un seguimiento de las consultas almacenadas actualmente. Este parametro puede tomar varios valores:

          •  ALL -> Se capturan todas las consultas.
          •  AUTO -> Se capturan las consultas frecuentes y aquellas con una duración de compilación y ejecución considerable.
          •  NONE -> No hay captura de nuevas consultas.

  El valos predeterminado en SQL Server es ALL, mientras que en Azure es AUTO.

  • MAX_PLANS_PER_QUERY

Número máximo de planes que se almacenan para cada consulta. El valor por defecto es 200

  • WAIT_STATS_CAPTURE_MODE

Especifica si se captura información de estadísticas de espera. Tiene dos valores ON (Por defecto) y OFF

Para revisar los valores cuando está habilitado el Query Store se puede utilizar la vista del sistema sys.database_query_store_options

Habilitar Query Store desde Management Studio

En Management Studio, para el uso de Query Store se requiere como mínimo que sea version 16.

Query Store se ejecuta por BBDD, y para habilitarlo hay que entrar en Propiedades de la BBDD donde se quiera dar de alta

Una vez aqui, hay que buscar la propiedad Query Store

 

 

 

 

 

 

 

 

 

 

Se ve que el modo de trabajo esta a OFF y no hay establecido ningún valor para los parámetros comentados de funcionamiento de Query Store.

Se cambia el modo de trabajo a READ_WRITE

Con esto se habilita Query Store y empieza a recoger los datos, y a nivel de propiedades se establecen los valores para los parámetros

Habilitar Query Store desde comandos Transact-SQL

Mediante comandos, para habilitar Query Store se hace con el comando ALTER DATABASE SET

ALTER DATABASE <database_name>
SET QUERY_STORE = ON
GO

Una vez puesto en marcha, hay que configurarlo. Los parámetros que no se especifiquen se inician con el valor por defecto.

ALTER DATABASE <database_name>
SET QUERY_STORE
(
        OPERATION_MODE = READ_WRITE,
        CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
        DATA_FLUSH_INTERVAL_SECONDS = 900,
        INTERVAL_LENGTH_MINUTES = 1,
        MAX_STORAGE_SIZE_MB = 100,
        QUERY_CAPTURE_MODE = ALL,
        SIZE_BASED_CLEANUP_MODE = OFF
)
GO

Deshabilitar Query Store desde Management Studio

Para deshabilitar Query Store desde Management Studio se hace igual que para habilitarlo, pero poniendo el modo a OFF

Deshabilitar Query Store desde comandos Transact-SQL

Al igual que en el caso anterior, la forma de deshabilitar Query Store es similar a la de habilitarlo, basta con poner el comando a OFF.

ALTER DATABASE <database_name>
SET QUERY_STORE = OFF
GO

Se puede ampliar esta información en la documentación oficial de Microsoft sobre Query Store en el siguiente enlace:

https://docs.microsoft.com/es-es/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-2017

En próximas entradas seguiremos revisando cómo trabajar con el Dashboard de Query Store y las posibilidades que nos ofrece la herramienta.

Esperamos que os sea de utilidad.

Un saludo,

Equipo de Base de datos