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
)
GODeshabilitar 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:
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