Hola a todos. Vamos a continuar conociendo la nueva herramienta de SQL Server Query Store, con el último capítulo, formado por dos post, dedicado a las vistas y procedimientos almacenados que se pueden utilizar para complementar el dashboard que vimos en el post anterior.
Podeis acceder a los post anteriores para conocer SQL Server 2016 Query Store en los siguientes links:
https://www.gpsos.es/2019/05/query-store-en-sql-server-introduccion-parte-i/
https://www.gpsos.es/2019/06/query-store-en-sql-server-introduccion-parte-ii/
https://www.gpsos.es/2019/08/dashboards-query-store-en-sql-server-parte-iii/
En este cuarto post sobre la aproximación a la herramienta vamos a repasar las vistas del catálogo de SQL Server que se utilizan tanto para revisar la configuracion como para trabajar con Query Store.
Para encontrar información detallada de las vistas y procedimientos almacenados en Query Store, se puede revisar la documentación oficial de Microsoft, en el siguiente enlace:
https://docs.microsoft.com/es-es/sql/relational-databases/system-catalog-views/query-store-catalog-views-transact-sql?view=sql-server-ver15
Vistas del catálogo
Hay un conjunto de vistas para revisar las opciones de configuración, así como las opciones de planes de ejecución.
Para todas las vistas del catálogo es necesario tener concedido el permiso VIEW DATABASE STATE
Estructura de la vista de database_query_store_options
- database_query_store_options: Devuelve las opciones de configuración generales de Query Store. Hay varias columnas interesantes en esta vista:
- desired_state: Indica el modo de operación deseado de Query Store, que es el estado configurado por el usuario al configurar Query Store. Puede tener varios estados:
- 0 = OFF
- 1 = READ_ONLY
- 2 = READ_WRITE
- actual_state: Indica el modo de operación de Query Store. Puede tener varios estados:
- 0 = OFF
- 1 = READ_ONLY
- 2 = READ_WRITE
- 3 = ERROR
- El estado que configura el usuario es el estado deseado, y hay algunos casos donde este puede diferir del estado real, como por ejemplo si se supera el tamaño máximo configurado. En estos casos la BBDD se puede poner en modo READ_ONLY el Query Store aunque esté configurado como READ_WRITE.
- También existe un estado de ERROR, por si hay algún error interno en Query Store. A partir de SQL Server 2017 (14.x) hay un procedimiento almacenado para recuperar el Query Store, que es sp_query_store_consistency_check. Si este procedimiento no funciona, o se trabaja con una versión anterior a 2017, se ejecuta por comando, con ALTER DATABASE SET QUERY_STORE CLEAR ALL;
- current_storage_size_mb: Indica el tamaño actual que ocupa Query Store en disco (en MB).
- interval_length_minutes: Indica el intervalo en que se agregan estadísticas en Query Store. Solo se pueden especificar los siguientes valores: 1, 5, 10, 15, 30, 60 y 1440 minutos, y por defecto es de 60 minutos.
- query_capture_mode: Indica el modo de captura de consultas que se encuentra activo:
- 1 = Se capturan todas las consultas, y es el valor utilizado por defecto.
- 2 = Se capturan las consultas según el consumo de recursos y el número de ejecuciones.
- 3 = Se detiene la captura de nuevas consultas, aunque se siguen recopilando estadisticas de las consultas ya capturadas.
- desired_state: Indica el modo de operación deseado de Query Store, que es el estado configurado por el usuario al configurar Query Store. Puede tener varios estados:
Más vistas de SQL Server Query Store
- query_context_settings: Esta vista contiene información sobre el contexto asociado a una consulta.
- query_store_plan: Esta vista devuelve información sobre cada uno de los planes asociados a una consulta.
- plan_id: Indica el id del plan.
- query_id: Indica el id de la query.
- query_plan: Devuelve el SHOWPLAN XML para el plan de consulta.
- query_store_query: Devuelve datos sobre las ejecuciones de la consulta.
- query_id: Indica el id de la query.
- object_id: Indica el id del objeto de base de datos del que forma parte la consulta.
- query_parameterization_type: Devuelve el tipo de parametrización. Puede tener varios valores:
- 0 = Ninguno
- 1 = usuario
- 2 = simple
- 3 = forzado
- query_parameterization_type_desc: Devuelve la descripción del tipo de parametrización.
- initial_compile_start_time: Indica la hora de inicio de la compilación.
- last_compile_start_time: Indica la hora de inicio de la compilación.
- query_store_query_text: Devuelve el texto de la consulta
- query_text_id: Indica el id de la query.
- query_sql_text: Almacena el texto SQL de la consulta, tal como lo proporciona el usuario. Incluye espacios en blanco, sugerencias y comentarios, aunque se omiten los comentarios y espacios antes y después del texto de consulta, pero no se omiten los comentarios y espacios dentro del texto.
- statement_sql_handle: Indica el id SQL de la consulta.
- query_store_wait_stats: Devuelve información sobre los eventos de espera de la consulta.
- wait_stats_id: Es el id del evento de espera.
- plan_id: Indica el id del plan.
- wait_category: Indica la categoria del tipo de espera. Los tipos de espera se clasifican en varias categorías de espera, y cada categoría requiere un análisis de seguimiento diferente para resolver el problema.
- wait_category_desc: Indica la categoría de espera en texto. La descripción de las distintas categorias se puede observar en la tabla en el siguiente link: https://docs.microsoft.com/es-es/sql/relational-databases/system-catalog-views/sys-query-store-wait-stats-transact-sql?view=sql-server-ver15#wait-categories-mapping-table
- query_store_runtime_stats: Devuelve información de estadísticas de ejecución en tiempo de ejecución de la consulta.
- execution_type: Indica el tipo de ejecución de la consulta. Hay varios tipos:
- 0 = ejecución normal (finalizada correctamente).
- 3 = ejecución de la anulada iniciada por el cliente.
- 4 = ejecución de excepción anulada.
- first_execution_time: Devuelve la hora de la primera ejecución de la consulta con el plan específico.
- last_execution_time: Devuelve la hora de finalización de la última ejecución de la consulta con el plan específico.
- count_executions: Indica el número de ejecuciones de la consulta, con el plan determinado.
- avg_duration: Devuelve la duración media de la consulta (en microsegundos).
- execution_type: Indica el tipo de ejecución de la consulta. Hay varios tipos:
- query_store_runtime_stats_interval: Devuelve información sobre las horas de inicio y fin de cada intervalo de recogida de estadísticas en tiempo de ejecución.
- runtime_stats_interval_id: Devuelve el id del intervalo de recogida de estadísticas en tiempo de ejecución.
- start_time: Indica la hora de inicio del intervalo.
- end_time: Indica la hora de finalización del intervalo.
No te pierdas la próxima entrada
En la próxima entrada veremos los procedimientos almacenados para configurar y utilizar las opciones de Query Store.
Esperamos que os sea de utilidad.
Un saludo,
Equipo de Base de datos
Si no quieres perderte trucos como este y estar al día de este mundo, suscríbete a nuestra newsletter y te mantendremos informado con un solo email al mes.
¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.
Sígue a GPS en LinkedIn