Supervisión del rendimiento mediante el Almacén de consultas

Se aplica a:SQL Server 2016 (13.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics (solo en grupo de SQL dedicado)

La característica de Almacén de consultas proporciona información sobre la elección y el rendimiento del plan de consulta para SQL Server, Azure SQL Database, Azure SQL Managed Instance y Azure Synapse Analytics. El Almacén de consultas simplifica la solución de problemas de rendimiento al permitirle encontrar rápidamente las diferencias de rendimiento provocadas por cambios en los planes de consulta. El Almacén de consultas captura automáticamente un historial de consultas, planes y estadísticas en tiempo de ejecución y las conserva para su revisión. Además, separa los datos por ventanas de tiempo, lo que permite ver patrones de uso de la base de datos y comprender cuándo se produjeron cambios del plan de consultas en el servidor. El almacén de consultas se puede configurar con la opción ALTER DATABASE SET .

• Para más información sobre el funcionamiento del Almacén de consultas de Azure SQL Database, vea Funcionamiento del almacén de consultas de Base de datos SQL de Azure.
• Para obtener información sobre cómo detectar información procesable y ajustar el rendimiento con el almacén de consultas, consulte Optimización del rendimiento con el Almacén de consultas.
• Para obtener información sobre cómo dar forma a los planes de consulta sin cambiar el código de la aplicación, consulte Sugerencias del Almacén de consultas.

Importante

Si usa el Almacén de consultas para conclusiones de la carga de trabajo just-in-time en SQL Server 2016 (13.x), tenga previsto instalar las correcciones de escalabilidad de rendimiento en KB 4340759 lo antes posible.

Habilitación del Almacén de consultas

• El Almacén de consultas está habilitado de manera predeterminada para las nuevas bases de datos de Azure SQL Database y Azure SQL Managed Instance.
• El Almacén de consultas no está habilitado de manera predeterminada para SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x) o SQL Server 2022 (16.x) en versión preliminar. Para permitir que las características realicen un mejor seguimiento del historial de rendimiento, solucione problemas relacionados con el plan de consulta y habilite nuevas funcionalidades en SQL Server 2022 (16.x) en versión preliminar, se recomienda habilitar el Almacén de consultas en bases de datos nuevas y existentes.
• Almacén de consultas no está habilitado de forma predeterminada para nuevas bases de datos de Azure Synapse Analytics.

Uso de la página del Almacén de consultas de SQL Server Management Studio

1. En el Explorador de objetos, haga clic con el botón derecho en una base de datos y, luego, seleccione Propiedades.

   Nota:

   Se requiere como mínimo la versión 16 de Management Studio.

2. En el cuadro de diálogo Propiedades de la base de datos , seleccione la página Almacén de consultas .

3. En el cuadro Modo de operación (solicitado) seleccione Lectura y escritura.

Uso de instrucciones Transact-SQL

Use la instrucción ALTER DATABASE para habilitar el almacén de consultas para una base de datos concreta. Por ejemplo:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

En Azure Synapse Analytics, habilite el Almacén de consultas sin opciones adicionales, por ejemplo:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

Para obtener más opciones de sintaxis relacionadas con el Almacén de consultas, consulte Opciones de ALTER DATABASE SET (Transact-SQL).

Nota:

No se puede habilitar el Almacén de consultas para las bases de datos master o tempdb.

Importante

Para obtener información sobre cómo habilitar el Almacén de consultas y hacer que siga ajustándose a su carga de trabajo, consulte Procedimiento recomendado con el Almacén de consultas.

Información del Almacén de consultas

Los planes de ejecución para cualquier consulta específica en SQL Server suelen evolucionar con el tiempo debido a una serie de motivos diferentes, como cambios en las estadísticas, cambios de esquema, creación o eliminación de los índices, etc. La caché de procedimientos (donde se almacenan los planes de consulta almacenados en caché) solo almacena el plan de ejecución más reciente. Los planes también se eliminan de la caché de planes debido a la presión de memoria. Como resultado, es posible que las regresiones de rendimiento de consultas provocadas por los cambios de planes de ejecución no sean triviales y que su resolución lleve mucho tiempo.

Como el Almacén de consultas conserva varios planes de ejecución por consulta, puede aplicar directivas para dirigir el procesador de consultas para que use un plan de ejecución concreto para una consulta. Esto se conoce como forzado de plan. El forzado de plan en la consulta de almacenes se ofrece mediante un mecanismo similar al de la sugerencia de consulta USE PLAN , pero no requiere ningún cambio en las aplicaciones de usuario. El plan de forzado puede resolver una regresión del rendimiento de consultas provocado por un cambio de plan en un período de tiempo muy breve.

Nota:

El Almacén de consultas recopila los planes para las instrucciones DML, como SELECT, INSERT, UPDATE, DELETE, MERGE y BULK INSERT.

De forma predeterminada, el Almacén de consultas no recopila datos para los procedimientos almacenados compilados de forma nativa. Use sys.sp_xtp_control_query_exec_stats para habilitar la recopilación de datos para los procedimientos almacenados compilados de forma nativa.

Las estadísticas de espera son otra fuente de información que ayuda a solucionar problemas de rendimiento en Motor de base de datos. Durante mucho tiempo, las estadísticas de espera solo han estado disponibles en el nivel de instancia, lo que dificultaba hacer un seguimiento hacia atrás de las esperas a una consulta específica. A partir de SQL Server 2017 (14.x) y Azure SQL Database, el Almacén de consultas incluye una dimensión que realiza un seguimiento de las estadísticas de espera. En el ejemplo siguiente se habilita el Almacén de consultas para recopilar estadísticas de espera.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

Entre los escenarios comunes para usar la característica Almacén de consultas se encuentran:

• Buscar y corregir rápidamente una regresión de rendimiento de plan forzando el plan de consulta anterior. Corregir las consultas de las que se ha realizado regresión recientemente en el rendimiento debido a cambios del plan de ejecución.
• Determinar el número de veces en que se ha ejecutado una consulta en una ventana de tiempo determinado, ayudando a un DBA en la solución de problemas de rendimiento de recursos.
• Identificar las principales n consultas (por tiempo de ejecución, consumo de memoria, etc.) en las últimas x horas.
• Auditar el historial de planes de consulta para una consulta determinada.
• Analizar los patrones de uso (CPU, E/S y memoria) de recursos para una base de datos determinada.
• Identificar las principales n consultas que están esperando recursos.
• Comprender la naturaleza de espera de una consulta o un plan determinados.

El Almacén de consultas contiene tres almacenes:

• un almacén de planes para conservar la información del plan de ejecución,
• un almacén de estadísticas de runtime para conservar la información de las estadísticas de ejecución,
• un almacén de estadísticas de espera para conservar la información de las estadísticas de espera.

El número de planes únicos que se pueden almacenar para una consulta en el almacén de planes se limita por la opción de configuración max_plans_per_query . Para mejorar el rendimiento, la información se escribe en los almacenes de forma asincrónica. Para minimizar el uso del espacio, se agregan las estadísticas de ejecución en tiempo de ejecución en el almacén de estadísticas de tiempo de ejecución en una ventana de tiempo fijo. La información de estos almacenes se puede ver al consultar las vistas del catálogo del Almacén de consultas.

La siguiente consulta devuelve información sobre las consultas y los planes del Almacén de consultas.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id;

Almacén de consultas en réplicas secundarias

SE APLICA A: SQL Server (a partir de SQL Server 2022 (16.x) en versión preliminar)

La característica de Almacén de consultas para réplicas secundarias habilita la misma funcionalidad del Almacén de consultas en las cargas de trabajo de réplicas secundarias que hay disponibles para las réplicas principales. Cuando se habilita el Almacén de consultas para réplicas secundarias, las réplicas envían la información de ejecución de consultas que normalmente se almacenaría en el Almacén de consultas de la réplica principal. A continuación, la réplica principal conserva los datos en el disco dentro de su propio Almacén de consultas. Básicamente, hay un Almacén de consultas compartido entre la réplica principal y todas las secundarias. El Almacén de consultas existe en la réplica principal y almacena los datos de todas las réplicas en un mismo lugar.

Nota:

Conjunto de réplicas o grupo de réplicas: un conjunto de réplicas se define como todas las réplicas sin nombre que comparten un rol (principal, secundario, secundario geográfico, principal geográfico) o como una réplica con nombre individual.

Los datos almacenados sobre las consultas se pueden analizar como cargas de trabajo en conjuntos de réplicas. El Almacén de consultas para réplicas proporciona la capacidad de supervisar y ajustar el rendimiento de las cargas de trabajo únicas de solo lectura que puedan ejecutarse en réplicas secundarias.

Activación del Almacén de consultas en réplicas secundarias

Antes de usar el Almacén de consultas para réplicas secundarias, debe tener instalado y configurado un grupo de disponibilidad Always On.

Importante

SE APLICA A: SQL Server 2022 (16.x) CTP 2.x

Debe habilitar el siguiente conjunto de marcas de seguimiento para poder habilitar el Almacén de consultas para réplicas secundarias: 12606, 12606, 12607, 12608, 12610, T12624. Para habilitar estas marcas de seguimiento:

1. Abra la consola de administración de servicios (services.msc desde el menú Ejecutar).
2. Haga clic con el botón derecho en el servicio SQL Server para SQL Server 2022 CTP 2 y seleccione Propiedades.
3. Si el estado del servicio es En ejecución, seleccione Detener. Se detendrá la instancia instalada.
4. En el cuadro Parámetros de inicio, agregue los valores: -T12606 -T12607 -T12608 -T12610 -T12624
5. Seleccione Iniciar para iniciar el servicio.
6. Seleccione Aceptar.

Habilite el Almacén de consultas para réplicas secundarias mediante las opciones ALTER DATABASE SET (Transact-SQL). En el ejemplo siguiente se habilita el Almacén de consultas en la base de datos principal y, a continuación, en réplicas secundarias. Para ejecutar este código, conéctese a la base de datos de la réplica principal.

ALTER DATABASE CURRENT SET QUERY_STORE = ON;
GO

ALTER DATABASE CURRENT  
FOR SECONDARY SET QUERY_STORE = ON ( 
        OPERATION_MODE = READ_WRITE 
);
GO

Puede comprobar que el Almacén de consultas esté habilitado en una réplica secundaria mediante la conexión a la base de datos en la réplica secundaria y la ejecución de las siguientes instrucciones de Transact-SQL:

SELECT desired_state, desired_state_desc, actual_state, actual_state_desc, readonly_reason
FROM sys.database_query_store_options;
GO

El ejemplo siguiente se genera después de consultar sys.database_query_store_options, e indica que el Almacén de consultas se encuentra en un estado de lectura y escritura para la base de datos secundaria. El readonly_reason de 8 indica que la consulta se ejecutó en una réplica secundaria. Estos resultados indican que el Almacén de consultas se ha habilitado correctamente en la réplica secundaria.

desired_state	desired_state_desc	actual_state	actual_state_desc	readonly_reason
2	READ_WRITE	2	READ_WRITE	8

Consideraciones de rendimiento para el Almacén de consultas en réplicas secundarias

El canal utilizado por las réplicas secundarias para enviar información de consulta a la réplica principal es el mismo canal que se usa para mantener actualizadas las réplicas secundarias. Los datos se almacenan en las mismas tablas de la réplica principal que el Almacén de consultas usa para las consultas ejecutadas en la réplica principal, lo que hace que aumente el tamaño del Almacén de consultas.

Por lo tanto, cuando un sistema está bajo una carga significativa, es posible que observe una ralentización debido a la sobrecarga del canal. Además, los mismos problemas de captura de consultas ad hoc que existen para el Almacén de consultas ahora continuarán para las cargas de trabajo que se ejecuten en réplicas secundarias. Obtenga más información sobre la Conservación de los datos más relevantes en el Almacén de consultas.

Desactivación del Almacén de consultas en réplicas secundarias

Para deshabilitar el Almacén de consultas para réplicas secundarias, conéctese a la base de datos en la réplica principal y ejecute el código siguiente:

ALTER DATABASE CURRENT  
FOR SECONDARY SET QUERY_STORE = OFF;
GO

Uso de la característica Consultas devueltas

Después de habilitar el Almacén de consultas, actualice la parte de la base de datos del panel del Explorador de objetos para agregar la sección Almacén de consultas.

Nota

Para Azure Synapse Analytics, las vistas del Almacén de consultas están disponibles en Vistas del sistema en la sección de la base de datos del panel Explorador de objetos.

Seleccione Regressed Queries (Consultas devueltas) para abrir el panel del mismo nombre Regressed Queries en SQL Server Management Studio. En el panel Regressed Queries (Consultas devueltas) se muestran las consultas y los planes del Almacén de consultas. Use los cuadros desplegables de la parte superior para filtrar las consultas en función de varios criterios: Duración (ms) (valor predeterminado), Tiempo de CPU (ms), Lecturas lógicas (KB), Escrituras lógicas (KB), Lecturas físicas (KB), Tiempo de CLR (ms), DOP, Consumo de memoria (KB), Recuento de filas, Memoria usada (KB), Memoria de base de datos temporal utilizada (KB) y Tiempo de espera (ms).

Seleccione un plan para ver el plan de consulta gráfica. Los botones están disponibles para ver la consulta de origen, forzar y no forzar un plan de consulta, alternar entre los formatos de cuadrícula y gráfico, comparar los planes seleccionados (si se ha seleccionado más de uno) y actualizar la pantalla.

Para forzar un plan, seleccione una consulta y un plan y, luego, haga clic en Forzar plan. Solo puede forzar planes que se guardaron mediante la característica del plan de consulta y que todavía se conservan en la caché del plan de consulta.

Búsqueda de consultas en espera

A partir de SQL Server 2017 (14.x) y Azure SQL Database, las estadísticas de espera por consulta a lo largo del tiempo están disponibles en el Almacén de consultas.

En el Almacén de consultas, los tipos de espera se combinan en categorías de espera. La asignación de categorías de espera a tipos de espera está disponible en sys.query_store_wait_stats (Transact-SQL).

Haga clic en Estadísticas de espera de consulta para abrir el panel Estadísticas de espera de consulta en SQL Server Management Studio v18 o superior. En el panel Estadísticas de espera de consulta se muestra un gráfico de barras que contiene las categorías de espera principales del Almacén de consultas. Use la lista desplegable de la parte superior para seleccionar un criterio de agregado para el tiempo de espera: avg, max, min, std dev y total (valor predeterminado).

Para seleccionar una categoría de espera, haga clic en la barra; se mostrará una vista de detalle de la categoría de espera seleccionada. Este gráfico de barras nuevo contiene las consultas que han contribuido a esa categoría de espera.

Use el cuadro desplegable de la parte superior para filtrar las consultas según diversos criterios de tiempo de espera para la categoría de espera seleccionada: avg, max, min, std dev y total (valor predeterminado). Seleccione un plan para ver el plan de consulta gráfica. Los botones están disponibles para ver la consulta de origen, aplicar y eliminar la aplicación de un plan de consulta, y actualizar la pantalla.

Las categorías de espera combinan distintos tipos de espera en cubos similares por naturaleza. Las distintas categorías de espera exigen un análisis de seguimiento diferente para resolver el problema, pero los tipos de espera de la misma categoría dan lugar a experiencias de solución de problemas muy similares; proporcionar la consulta afectada además de las esperas sería la pieza que falta para completar correctamente la mayoría de las investigaciones de este tipo.

Estos son algunos ejemplos de cómo se puede obtener más información sobre la carga de trabajo antes y después de introducir categorías de espera en el Almacén de consultas:

Experiencia anterior	Nueva experiencia	Acción
Altas esperas RESOURCE_SEMAPHORE por base de datos	Altas esperas de memoria en el Almacén de consultas para consultas concretas	Encuentre las consultas que más memoria consumen en el Almacén de consultas. Estas consultas probablemente retrasan el progreso de las consultas afectadas. Considere la posibilidad de usar la sugerencia de consulta MAX_GRANT_PERCENT para estas consultas o para las consultas afectadas.
Altas esperas LCK_M_X por base de datos	Altas esperas de bloqueo en el Almacén de consultas para consultas concretas	Compruebe los textos de consulta para las consultas afectadas e identifique las entidades de destino. En el Almacén de consultas, busque otras consultas que modifiquen la misma entidad, que se ejecuten con frecuencia o que tengan una gran duración. Tras identificar estas consultas, considere la posibilidad de cambiar la lógica de aplicación para mejorar la simultaneidad o usar un nivel de aislamiento menos restrictivo.
Altas esperas PAGEIOLATCH_SH por base de datos	Altas esperas de E/S del búfer en el Almacén de consultas para consultas concretas	Encuentre las consultas con un gran número de lecturas físicas en el Almacén de consultas. Si coinciden con las consultas con altas esperas de E/S, considere la posibilidad de introducir un índice en la entidad subyacente para llevar a cabo búsquedas en lugar de análisis y así minimizar la sobrecarga de E/S de las consultas.
Altas esperas SOS_SCHEDULER_YIELD por base de datos	Altas esperas de CPU en el Almacén de consultas para consultas concretas	Busque las consultas del Almacén de consultas que consumen más CPU. Entre ellas, identifique las consultas cuya alta tendencia de CPU se correlaciona con altas esperas de CPU para las consultas afectadas. Céntrese en la optimización de las consultas: podría ser una regresión de plan o posiblemente un índice que falta.

Opciones de configuración

Para consultar las opciones disponibles a fin de configurar los parámetros del Almacén de consultas, vea Opciones de ALTER DATABASE SET (Transact-SQL).

Consulte la vista sys.database_query_store_options para determinar las opciones actuales del Almacén de consultas. Para obtener más información sobre los valores, vea sys.database_query_store_options.

Para obtener ejemplos sobre cómo establecer opciones de configuración mediante instrucciones Transact-SQL, consulte Administración de opciones.

Nota:

En Azure Synapse Analytics, el Almacén de consultas se puede habilitar como en otras plataformas, pero no se admiten opciones de configuración adicionales.

Vistas, funciones y procedimientos relacionados

Vea y administre El Almacén de consultas mediante Management Studio o por medio de las siguientes vistas y procedimientos.

Funciones del Almacén de consultas

Las funciones ayudan a las operaciones del Almacén de consultas.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

Vistas de catálogo del Almacén de consultas

Las vistas de catálogo presentan información sobre el Almacén de consultas.

sys.database_query_store_options (Transact-SQL)

sys.query_context_settings (Transact-SQL)

sys.query_store_plan (Transact-SQL)

sys.query_store_query (Transact-SQL)

sys.query_store_query_text (Transact-SQL)

sys.query_store_runtime_stats (Transact-SQL)

sys.query_store_wait_stats (Transact-SQL)

sys.query_store_runtime_stats_interval (Transact-SQL)

sys.database_query_store_internal_state (Transact-SQL)

Procedimientos almacenados del Almacén de consultas

Los procedimientos almacenados configuran el Almacén de consultas.

sp_query_store_flush_db (Transact-SQL)

sp_query_store_reset_exec_stats (Transact-SQL)

sp_query_store_force_plan (Transact-SQL)

sp_query_store_unforce_plan (Transact-SQL)

sp_query_store_remove_plan (Transact-SQL)

sp_query_store_remove_query (Transact-SQL)

sp_query_store_clear_message_queues (Transact-SQL)

sp_query_store_consistency_check (Transact-SQL)¹

¹ En escenarios extremos, Almacén de consultas puede entrar en el estado ERROR debido a errores internos. A partir de SQL Server 2017 (14.x), si esto sucede, el Almacén de consultas se puede recuperar mediante la ejecución del procedimiento almacenado sp_query_store_consistency_check en la base de datos afectada. Consulte sys.database_query_store_options para conocer más detalles de la descripción de columna actual_state_desc.

Escenarios de uso claves

Administración de opciones

En esta sección se ofrecen algunas directrices sobre la administración de la propia característica Almacén de consultas.

Estado del Almacén de consultas

El Almacén de consultas almacena sus datos dentro de la base de datos del usuario, y ese es el motivo por el que tiene limitado el tamaño (configurado con MAX_STORAGE_SIZE_MB). Si los datos del Almacén de consultas alcanzan ese límite, el Almacén de consultas cambiará automáticamente el estado de lectura-escritura a solo lectura y dejará de recopilar datos nuevos.

Ejecute la consulta sys.database_query_store_options para saber si el almacén de consultas está activo actualmente y si está recopilando estadísticas en tiempo de ejecución o no.

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

El estado del Almacén de consultas viene determinado por la columna actual_state. Si es diferente al estado deseado, la columna readonly_reason puede proporcionar más información. Cuando el tamaño del Almacén de consultas supere la cuota, la característica cambiará al modo read_only y proporcionará un motivo. Para conocer los motivos, consulte sys.database_query_store_options (Transact-SQL).

Obtener opciones del almacén de consultas

Para averiguar información detallada sobre el estado del Almacén de consultas, ejecute lo siguiente en una base de datos de usuario.

SELECT * FROM sys.database_query_store_options;

Establecimiento del intervalo del Almacén de consultas

Puede invalidar el intervalo para agregar estadísticas de tiempo de ejecución de consultas (el valor predeterminado es 60 minutos). El nuevo valor de intervalo se expone a través de la vista sys.database_query_store_options.

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

No se admiten valores arbitrarios para INTERVAL_LENGTH_MINUTES. Use uno de los siguientes: 1, 5, 10, 15, 30, 60 o 1440 minutos.

Nota:

Para Azure Synapse Analytics, no se admite la personalización de las opciones de configuración del Almacén de consultas, como se muestra en esta sección.

Uso de espacio en el Almacén de consultas

El límite y el tamaño del Almacén de consultas se pueden comprobar con la siguiente instrucción en la base de datos de usuario.

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Si el almacenamiento del Almacén de consultas está completamente lleno, use la siguiente instrucción para ampliar el almacenamiento.

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

Establecer opciones del Almacén de consultas

Puede establecer varias opciones del Almacén de consultas a la vez con una sola instrucción ALTER DATABASE.

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

Para obtener la lista completa de opciones de configuración, vea Opciones de ALTER DATABASE SET (Transact-SQL).

Limpieza del espacio

Las tablas internas del Almacén de consultas se crean en el grupo de archivos PRIMARY durante la creación de la base de datos y esa configuración no se podrá cambiar más adelante. Si se está quedando sin espacio, puede que quiera borrar los datos más antiguos del Almacén de consultas con la siguiente instrucción.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Como alternativa, puede que quiera borrar solo datos de consultas ad hoc, porque resulta menos relevantes para optimizaciones de consultas y análisis de plan, pero ocupa el mismo espacio.

En Azure Synapse Analytics, no está disponible la opción para borrar el Almacén de consultas. Los datos se conservan automáticamente durante los últimos 30 días.

Eliminar consultas ad hoc

Esto purga las consultas ad hoc e internas del Almacén de consultas para que no se quede sin espacio y se quiten las consultas de las que realmente es necesario realizar el seguimiento.

SET NOCOUNT ON
-- This purges adhoc and internal queries from 
-- the Query Store in the current database 
-- so that the Query Store does not run out of space 
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

Puede definir su propio procedimiento con una lógica diferente para borrar los datos que ya no necesite.

En el ejemplo anterior se usa el procedimiento almacenado extendido sp_query_store_remove_query para quitar datos innecesarios. También puede:

• Use sp_query_store_reset_exec_stats para borrar las estadísticas en tiempo de ejecución correspondientes a un plan determinado.
• Use sp_query_store_remove_plan para quitar un plan.

Auditoría del rendimiento y solución de problemas

Para obtener más información sobre cómo optimizar el rendimiento con el Almacén de consultas, consulte Optimización del rendimiento con el Almacén de consultas.

Otros temas relacionados con el rendimiento:

• Query Store Usage Scenarios (Escenarios de uso del Almacén de consultas)

Consulte también

• Procedimientos almacenados en el almacén de datos de consultas (Transact-SQL)
• Vistas de catálogo del almacén de datos de consultas (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• Estadísticas de consultas activas
• Monitor de actividad
• Introducción a la recopilación de datos del Almacén de consultas
• Supervisión y optimización del rendimiento
• Herramientas de optimización y supervisión del rendimiento
• Uso del almacén de consultas con OLTP en memoria

Pasos siguientes

• Procedimiento recomendado con el Almacén de consultas
• Abrir el Monitor de actividad (SQL Server Management Studio)
• Ajustar el rendimiento con el almacén de consultas