OpenQueryStore es una nueva implementación de código abierto de la popular funcionalidad de almacén de consultas introducida en el SQL Server 2016 CTP2. OpenQueryStore se introdujo por primera vez en junio de 2017. Sus principales colaboradores son William Durkin y Enrico van de Laar.
OpenQueryStore recopila las estadísticas de ejecución en tiempo de ejecución de consultas, espera de información de estadísticas en una base de datos específica. Esta información nos ayuda a identificar problemas sobre el rendimiento y solución de problemas para encontrar rápidamente las diferencias de rendimiento.
La principal diferencia entre el OpenQueryStore y la característica de Query Store es que el OpenQueryStore admite desde el SQL Server 2005 al SQL Server 2014, mientras que Query Store solamente admite SQL Server 2016 y versiones posteriores. Además, OpenQueryStore es de código abierto, lo cual significa que el código de OpenQueryStore se puede cambiar cuando uno lo desee.
Puede encontrar mucha más información sobre el almacén de consultas en la página de información general del almacén de consultas de SQL Server.
En este artículo vamos a explicar el proceso de instalación de OpenQueryStore, la configuración de OpenQueryStore Dashboards y algunos de los procedimientos y opciones de OpenQueryStored.
Instalación
Existen dos formas de instalar OpenQueryStore. Una de ella es mediante el uso de la configuración automatizada y la otra forma es la instalación manual mediante la modificación y ejecución de un par de scripts SQL.
Antes de que empiece la instalación de OpenQueryStore, primero se debe elegir el modo de recopilación de datos.
Tenemos dos modos de recopilación de datos, uno de ellos es el clásico y el otro el centralizado.
Modo clásico
Cuando se va a usar el modo Clásico, OpenQueryStore se instalará en un nivel de base de datos muy parecido al del Almacén de consultas. Lo que significa que se recopilarán las estadísticas de ejecución en tiempo de ejecución de consultas para las todas las consultas que se ejecutan en la base de datos en la que está instalado OpenQueryStore. Todos los datos recopilados se van a almacenar dentro de esa base de datos bajo los objetos oqs.
Modo centralizado
Cuando se utiliza el modo centralizado, OpenQueryStore recopila estadísticas de ejecución en tiempo de ejecución de consultas para múltiples bases de datos dentro de una misma instancia y almacena los datos recopilados en una base de datos central separada.
Adicionalmente, antes de instalar OpenQueryStore, debe que tener que elegir uno de los dos métodos de programación, Service Broker o SQL Server Agent.
Estos dos métodos se utilizan para poder controlar la programación de la recopilación de datos y el mantenimiento en segundo plano.
Si se escogen los métodos de programación de Service Broker para la programación de la recopilación de datos y el mantenimiento en segundo plano, antes de que empiece el proceso de instalación de OpenQueryStore, primero hay que habilitar el Service Broker para la base de datos en la que se desea instalar el OpenQueryStore.
Para poder hacer eso, hay que ejecutar el siguiente código en un editor de consultas:
|
1 2 3 4 |
ALTER DATABASE Test SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE; GO |
Para ver si Service Broker ya está habilitado o no, ejecute el siguiente código:
|
1 2 3 4 |
SELECT is_broker_enabled FROM sys.databases WHERE name = 'Test' GO |
Para poder utilizar el Agente SQL Server para poder controlar la programación de la recopilación de datos y el mantenimiento en un segundo plano, asegúrese de que el Agente SQL Server esté habilitado primero. Además, tiene que tener en cuenta que el Agente SQL Server no está disponible en SQL Server Express Edition.
Cuando se selecciona el modo y se establece el método, podemos instalar OpenQueryStore.
Desde la página de lanzamiento de OpenQueryStore, tiene que descargar y descomprima la última versión de OpenQueryStore:
Instalación automatizada
Abra el cuadro de diálogo de Windows PowerShell y navegue hasta llegar a la carpeta OpenQueryStore descomprimida y pegue el siguiente código:
.\Install.ps1 -SqlInstance <Instance> -Database <dbName> -OQSMode Classic -SchedulerType “Service Broker” -CertificateBackupPath “<path>”
Reemplace los siguientes valores <Instance>, <dbName> y <path> con uno apropiado:
Y pulse la tecla Enter.
Instalación Manual
Diríjase a la carpeta OpenQueryStore ya descomprimida y por debajo de la carpeta de configuración, ubique el archivo install_open_query_store_base.sql scrpt:
En SQL Server Management Studio (SSMS), conéctese a la instancia de SQL Server y a la base de datos en la que desea instalar OpenQueryStore. Después de eso, tiene que abrir el script install_open_query_store_base.sql y reemplazar los siguientes valores entre llaves {} con los apropiados.
Como por ejemplo, {DatabaseWhereOQSIsRunning} reemplaza con el nombre de la base de datos, en este caso la base de datos [AdventureWorks2014].
Reemplace el marcador de posición {OQSMode} con el modo clásico o centralizado y ejecute el siguiente script:
Después de ejecutar el script install_open_query_store_base.sql, en la carpeta OpenQueryStore, tiene que buscar en SSMS y abra el script install_gather_statistics.sql:
Tiene que reemplazar los marcadores de posición entre llaves {} con los valores necesarios, lo mismo que se hizo con el script install_open_query_store_base.sql y presione el botón Ejecutar:
Si se elige utilizar Service Broker como un método de planificación, los scripts install_service_broker.sql y install_service_broker_certificate.sql deben modificarse y poder ejecutarse como lo hicimos con install_open_query_store_base.sql y install_gather_statistics.sql scripts.
Puede poder encontrar más información sobre el proceso de instalación de OpenQueryStore en la página de instalación de OQS.
Después de que ya esté instalado el OpenQueryStore para iniciar el proceso de recolección de datos, tiene que ejecute el procedimiento almacenado [master]. [Dbo]. [Open_query_store_startup]:
|
1 |
EXECUTE [master].[dbo].[open_query_store_startup] |
Esto hará que se active la recopilación de datos de OpenQueryStored utilizando Service Broker. El procedimiento [open_query_store_startup] se llama en cada inicio de SQL Server.
Incluso la instalación va bien y la recopilación de datos está activada, es muy probable que OpenQueryStore no comience a recopilar datos.
Para poder comprobar por qué OpenQueryStore no inició la recopilación de datos, tiene que ejecutar el siguiente código:
|
1 |
SELECT * FROM oqs.activity_log |
En la cuadrícula de resultados debajo de la columna log_message y vea el mensaje que se ha generado:
Para así poder resolver el problema mencionado anteriormente, en un editor de consultas, ejecute el siguiente código:
|
1 |
SELECT collection_active FROM oqs.collection_metadata |
Si el resultado en la cuadrícula de resultados para la columna collection_active es falso o cero (0):
Luego, en esta columna en la tabla oqs.collection_metadata debe tener que cambiarse (actualizarse) para que OpenQueryStore pueda comenzar a recopilar datos:
|
1 |
UPDATE oqs.collection_metadata SET collection_active=1 |
Para poder verificar que OpenQueryStore comience a recopilar la verificación de datos que se recopiló en la columna log_message de la tabla oqs.activity_log:
|
1 |
SELECT * FROM oqs.activity_log |
Todos los datos que recopila el OpenQueryStore, así como la configuración, se colocan en las siguientes tablas:
- oqs.activity_log
- oqs.collection_metadata
- oqs.excluded_queries
- oqs.intervals
- oqs.monitored_databases
- oqs.plan_dbid
- oqs.plans
- oqs.queries
- oqs.query_runtime_stats
- oqs.wait_stats
- oqs.wait_type_filter
Tiene que tener en cuenta que esto puede cambiarse con el tiempo ya que OpenQueryStore está en constante desarrollo activo.
La información de la configuración para OpenQueryStore se coloca por debajo de la tabla oqs.collection_metadata:
La columna de comando contiene la llamada al procedimiento almacenado gather_statistics. OpenQueryStore llama a este procedimiento en intervalos regulares para obtener estadísticas de tiempo de ejecución y poder así almacenarlas en OpenQueryStore.
La columna collection_interval contiene el intervalo de recopilación que se utiliza para controlar el intervalo de datos de recopilación. De una forma predeterminada, la columna collection_interval se establece en el tiempo de 60 segundos.
La columna oqs_mode se utiliza para informar a OpenQueryStore qué modo se está usando (clásico o centralizado)
La columna oqs_classic_db contiene el nombre de la base de datos en la que está instalado OpenQueryStore y ordena que el modo clásico solo se ejecute en esa base de datos.
La columna collection_active indica si la recopilación de datos en OpenQueryStore está permitida o no. Durante el proceso de instalación, esta columna se establece en 0 y tiene que cambiarse a 1 para que OpenQueryStore inicie la recopilación de datos.
La columna operation_threshold contiene el número que presenta las ejecuciones mínimas de un plan de consulta antes de que OpenQueryStore empiece a capturar. Por defecto, el umbral de ejecución es 2 para poder omitir los planes de un solo uso.
La columna data_cleanup_active nos indica si el proceso de limpieza de automatización de datos antiguos en OpenQueryStore ya está activado o no. De una forma predeterminada, esto se establece en 1 (activar)
La columna data_cleanup_threshold nos muestra cuántos días debe mantener los datos OpenQueryStore; el proceso de limpieza automatizado va a eliminar los datos anteriores a este. De una forma predeterminada, esto se establece en 30 días.
La columna data_cleanup_throttle determina cuántas filas se pueden eliminar en una sola pasada. Esto nos va a evitar que las grandes eliminaciones destruyan el registro de transacciones y puedan bloquear las tablas de OpenQueryStore. De una forma predeterminada, esto se establece en 5000 filas.
Tableros de instrumentos de OpenQueryStore
Los cuadros de mando de OpenQueryStore visualizan toda la información que OpenQueryStore ha recopilado mostrándolos en varios formatos de gráficos.
Para poder instalar cuadros de mando, tiene que ir a la página de GitHub de cuadros de mando de OpenQueryStore y desde allí, tiene que descargar los siguientes archivos: OpenQueryStoreDashboard.rdl y OpenQueryStoreWaitStatsDashboard.rdl:
O si ya ha descargado la última instalación de la carpeta OpenQueryStore, estos dos cuadros de mandos se encuentran en la carpeta Cuadros de mandos:
Después de que ya hayan descargado los paneles, abra SSMS 2016 o SSMS v17, navegue a la base de datos en la que está instalado OpenQueryStore, haga clic derecho y, en el menú contextual, debajo del submenú de Informes, tiene que elegir el comando Informes personalizados:
Navegue a los archivos OpenQueryStoreDashboard.rdl y OpenQueryStoreWaitStatsDashboard.rdl y tiene que presionar el botón Abrir:
Ahora aparecerá el cuadro de mensaje de advertencia de Ejecutar informe personalizado. Presiona el botón Ejecutar para continuar:
Esto abrirá los cuadros de mando de OpenQueryStore.
Tenga en cuenta que si sigue estos pasos en SSMS 2012 o SSMS 2014, puede aparecer el siguiente mensaje:
Un error ocurrió durante el proceso del reporte local. (Microsoft.ReportViewer.WinForms)
INFORMACIÓN ADICIONAL:
La definición del informe no es válida. (Microsoft.ReportViewer.Common)
La definición de este informe no es válida y ni está respaldada por esta versión de Reporting Services. Es muy probable que la definición del informe se haya creado con una versión posterior de Reporting Services o que contenga contenido que no esté bien formado o que no sea válido en función de los esquemas de Reporting Services. Detalles: la definición del informe tiene un espacio de nombres de destino no válido “http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition” que no se puede actualizar. (Microsoft.ReportViewer.Common)
Al principio, estos dos paneles no mostrarán ninguna información, ya que OpenQueryStore necesita algo de tiempo para poder recopilar todos los datos y mostrarlos:
Después de un tiempo, los paneles se archivarán con gráficos y datos:
La sección de la información general por hora muestra solo información recopilada por OpenQueryStore en la última hora.
En el primer gráfico en OpenQueryStoreDashboard nos muestra las 10 consultas principales que se han ejecutado en la última hora y según el tiempo promedio transcurrido de la consulta:
La siguiente tabla muestra las 10 consultas principales en la última hora según la cantidad de ejecuciones:
El siguiente gráfico nos brinda el número total de consultas únicas ejecutadas en la última hora. Este gráfico no devuelve un número total de ejecuciones, sino el número de consultas únicas y distintas que se han ejecutado:
El gráfico de la diversidad del plan de ejecución nos muestra el recuento de los diferentes tipos de planes de ejecución en la última hora. Cuando pase el mouse sobre los bloques, le aparecerá una ventana emergente que le mostrará el tipo de plan (Preparado o Adhoc):
En la sección de información general sobre el uso de recursos muestra las métricas de rendimiento de todas las consultas agregadas en los intervalos de recopilación.
El gráfico de área nos muestra los valores mínimos y máximos, mientras que la línea muestra el valor promedio. Todos estos los gráficos se basan en las métricas de rendimiento que se van recopilando en las últimas ocho horas.
La parte inferior derecha es el gráfico de Consultas con varios planes, que muestra que los ID de consulta de las declaraciones de consulta que han generado más de un Plan de ejecución:
En la sección de información de OpenQueryStore, se muestra información sobre el propio OpenQueryStore. El primer gráfico muestra la cantidad total de espacio que ocupa la tabla de OpenQueryStore en KB dentro de la base de datos. El gráfico en el medio retorna el número de elementos en OpenQueryStore. La tabla muestra la información que hizo OpenQueryStore en la última iteración de recopilación de datos:
El panel de estadísticas de espera muestra el estado de espera de las últimas horas:
Todos los tipos de espera disponibles se colocan en la tabla wait_type_filter:
|
1 |
SELECT * FROM oqs.wait_type_filter |
Además, todos estos paneles se pueden imprimir o exportar en Excel, PDF, archivo de Word:
Eliminando el OpenQueryStore
Para eliminar OpenQueryStore de la base de datos, en SSMS, tiene que abrir el archivo uninstall_open_query_store.sql, ingrese el nombre de la base de datos para la cual se eliminará OpenQueryStore y ejecute el script:
OpenQueryStore está en constante desarrollo activo y para obtener actualizaciones o consultar cualquier problema, puede consultar la cuenta de Twitter o la página web de OpenQueryStore GitHub
Otras Implementaciones
OpenQueryStore se ha implementado en ApexSQL Plan, una herramienta gratuita para el análisis del plan de ejecución de consultas de SQL Server y el ajuste del rendimiento. Las características y actualizaciones de OpenQueryStore se lanzarán con las nuevas versiones de ApexSQL Plan
Referencias
- OQS Dashboard
- Funcionalidad de Query Store en versiones anteriores de SQL Server.
- Introducción a Open Query Store
- Funcionalidad de Query Store sin SQL Server 2016
Actualmente trabajando para ApexSQL LLC como un Ingeniero de Ventas de Software, él está ayudando a los clientes con problemas técnicos y hace aseguramiento de calidad para los complementos ApexSQL Complete, ApexSQL Refactor y ApexSQL Search.
Ver todas las entradas de Marko Zivkovic