Compartir a través de

Procedimientos recomendados para administrar el almacén de consultas

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

En este artículo se describe la administración del almacén de consultas de SQL Server y las características circundantes.

Nota:

En SQL Server 2022 (16.x), ahora el almacén de consultas está habilitado de forma predeterminada para todas las bases de datos SQL Server recién creadas con el fin de ayudar a realizar un mejor seguimiento del historial de rendimiento, solucionar problemas relacionados con el plan de consulta y habilitar nuevas funcionalidades del procesador de consultas.

Valores predeterminados del almacén de consultas en Azure SQL Database

En esta sección se describen los valores predeterminados de configuración óptimos de Azure SQL Database que están diseñados para garantizar el funcionamiento confiable del Almacén de consultas y de las características que dependen de él. La configuración predeterminada está optimizada para una recopilación continua de los datos, es decir, un tiempo mínimo en los estados OFF y READ_ONLY. Para obtener más información sobre todas las opciones disponibles del Almacén de consultas, vea Opciones de ALTER DATABASE SET (Transact-SQL).

Configuración Descripción Valor predeterminado Comentario
MAX_STORAGE_SIZE_MB Especifica el límite del espacio de datos que puede tomar el Almacén de consultas dentro de la base de datos de cliente. 100 antes de SQL Server 2019 (15.x)
1000 a partir de SQL Server 2019 (15.x)		 Se aplica a nuevas bases de datos.
INTERVAL_LENGTH_MINUTES Define el tamaño de la ventana de tiempo durante la que se agregan y conservan las estadísticas recopiladas en tiempo de ejecución para los planes de consulta. Todos los planes de consulta activa tienen al menos una fila durante un período de tiempo definido con esta configuración. 60 Se aplica a nuevas bases de datos.
STALE_QUERY_THRESHOLD_DAYS Directiva de limpieza basada en el tiempo que controla el período de retención de las estadísticas en tiempo de ejecución guardadas y las consultas inactivas. 30 Se aplica a nuevas bases de datos y bases de datos con la configuración predeterminada anterior (367).
SIZE_BASED_CLEANUP_MODE Especifica si limpieza automática de los datos se lleva a cabo cuando el tamaño de los datos del Almacén de consultas se aproxima al límite. AUTO Se aplica a todas las bases de datos.
QUERY_CAPTURE_MODE Especifica si se realiza el seguimiento de todas las consultas o solo de un subconjunto de estas. AUTO Se aplica a todas las bases de datos.
DATA_FLUSH_INTERVAL_SECONDS Especifica el período máximo durante el que las estadísticas en tiempo de ejecución capturadas se conservan en memoria, antes de vaciarlas en el disco. 900 Se aplica a nuevas bases de datos.

Importante

Estos valores predeterminados se aplican automáticamente en la fase final de la activación del almacén de consultas en Azure SQL Database. Una vez habilitado, Azure SQL Database no cambia los valores de configuración establecidos por los clientes, a no ser que influyan de manera negativa en la carga de trabajo principal o en las operaciones de confianza del almacén de consultas.

Nota:

El Almacén de consultas no se puede deshabilitar en la base de datos única de Azure SQL Database ni Grupo elástico. Ejecutar ALTER DATABASE [database] SET QUERY_STORE = OFF devuelve la advertencia 'QUERY_STORE=OFF' is not supported in this version of SQL Server.

Si quiere permanecer con su configuración personalizada, utilice ALTER DATABASE con las opciones del Almacén de consultas para revertir la configuración al estado anterior. Vea Procedimientos recomendados con el almacén de consultas para aprender a elegir los parámetros de configuración óptimos.

Establecimiento del modo óptimo de captura del almacén de consultas

Mantenga los datos más relevantes en el Almacén de consultas. En la tabla siguiente se describen los escenarios típicos para cada modo de captura del almacén de consultas:

Modo de captura del almacén de consultas Escenario
All Analice la carga de trabajo exhaustivamente en cuanto a todas las formas de las consultas y sus frecuencias de ejecución, y otras estadísticas.

Identifique nuevas consultas en la carga de trabajo.

Detecte si las consultas ad-hoc se usan para identificar oportunidades de parametrización automática o manual.

Nota: Este es el modo de captura predeterminado en SQL Server 2016 (13.x) y SQL Server 2017 (14.x).
Automático Centre su atención en las consultas pertinentes y accionables. Un ejemplo son las que se ejecutan con regularidad o las que tienen un consumo significativo de recursos.

Nota: Este es el modo de captura predeterminado en SQL Server 2019 (15.x) y versiones posteriores.
None Ya ha capturado el conjunto de consultas que quiere supervisar en tiempo de ejecución y quiere eliminar los objetos innecesarios que otras consultas podrían introducir.

El modo Ninguno es adecuado para entornos de pruebas y evaluación comparativa.

El modo Ninguno también es adecuado para los proveedores de software que incluyen la configuración del Almacén de consultas definida para supervisar la carga de trabajo de la aplicación.

El modo Ninguno se debe usar con precaución, ya que podría perder la oportunidad de realizar el seguimiento de consultas nuevas importantes y de optimizarlas. Evite el uso del modo Ninguno a menos que tenga un escenario específico que lo requiera.
Personalizada SQL Server 2019 (15.x) introdujo un modo de captura personalizado bajo el comando ALTER DATABASE ... SET QUERY_STORE. Aunque Auto es el valor predeterminado y recomendado, si todavía hay algún problema sobre la sobrecarga que el almacén de consultas puede introducir, los administradores de bases de datos pueden utilizar las directivas de captura personalizadas para optimizar aún más el comportamiento de captura del almacén de consultas. Para obtener más información y recomendaciones, consulte Directivas de captura personalizadas más adelante en este artículo. Para obtener más información sobre esta sintaxis, consulte Opciones de ALTER DATABASE SET.

Nota:

Los cursores, las consultas dentro de procedimientos almacenados y las consultas compiladas de forma nativa siempre se capturan cuando Modo de captura de Almacén de consultas se establece en Todo, Automático o Personalizado. Para capturar consultas compiladas de forma nativa, habilite la recopilación de estadísticas por consulta mediante sys.sp_xtp_control_query_exec_stats.

Conservación de los datos más relevantes en el Almacén de consultas

Configure el almacén de consultas para que contenga solo los datos pertinentes de modo que se ejecute de forma continua y proporcione una magnífica experiencia de solución de problemas con un impacto mínimo en la carga de trabajo normal.

La tabla siguiente proporciona prácticas recomendadas:

Procedimiento recomendado Configuración
Limitar los datos históricos retenidos. Configurar la directiva basada en tiempo para activar la limpieza automática.
Filtrar las consultas no pertinentes. Configurar Modo de captura de Almacén de consultas en Automático.
Eliminar las consultas menos relevantes cuando se alcanza el tamaño máximo. Activar la directiva de limpieza basada en el tamaño.

Directivas de captura personalizadas

Cuando el modo de captura CUSTOM del almacén de consultas está habilitado, hay configuraciones del almacén de consultas adicionales disponibles en una nueva configuración de directiva de captura del almacén de consultas para ajustar la recopilación de datos en un servidor específico.

La nueva configuración personalizada define lo que sucede durante el umbral de tiempo de la directiva de captura interna. Es un límite de tiempo durante el que se evalúan las condiciones configurables y, si alguna de ellas es verdadera, la consulta se puede registrar en el almacén de consultas.

El modo de captura del almacén de consultas especifica la directiva de captura de consultas para el almacén de consultas.

  • Todos: captura todas las consultas. Es la opción predeterminada en SQL Server 2016 (13.x) y SQL Server 2017 (14.x).
  • Automático: se omiten las consultas poco frecuentes y aquellas con una duración de compilación y ejecución insignificante. Los umbrales para la duración del tiempo de ejecución, compilación y recuento de ejecuciones se determinan de forma interna. A partir de SQL Server 2019 (15.x), esta es la opción predeterminada.
  • Ninguna: el almacén de consultas deja de capturar consultas nuevas.
  • Personalizado: permite un control adicional y la capacidad de ajustar la directiva de recopilación de datos. La nueva configuración personalizada define lo que sucede durante el umbral de tiempo de la directiva de captura interna. Es un límite de tiempo durante el que se evalúan las condiciones configurables y, si alguna de ellas es verdadera, la consulta se puede registrar en el almacén de consultas.

El ajuste de una directiva de captura personalizada adecuada para su entorno debe tenerse en cuenta cuando:

  • La base de datos es muy grande.
  • La base de datos tiene un gran número de consultas únicas y ad hoc.
  • La base de datos tiene limitaciones específicas de tamaño o crecimiento.

Descargar la última versión de SQL Server Management Studio (SSMS)

Para ver la configuración actual en Management Studio:

  1. En el explorador de objetos de SQL Server Management Studio, haga clic con el botón derecho en la base de datos.
  2. Selecciona Propiedades.
  3. Seleccione Almacén de consultas. En la página Almacén de consultas, compruebe que el modo de operación (solicitado) es Lectura y escritura.
  4. Cambie el modo de captura del almacén de consultas a Personalizado.
  5. Tenga en cuenta que los cuatro campos de directiva de captura en Directiva de captura de Almacén de consultas ahora están habilitados y configurables.

Ejemplo de directivas de captura personalizadas

En el ejemplo siguiente se establece QUERY_CAPTURE_MODE en AUTO y se establece un modo de captura personalizado. Cada uno de los siguientes establece las directivas de captura personalizadas en su valor predeterminado en SQL Server 2022 (16.x). Considera la posibilidad de ajustar estos valores para reducir el número de consultas capturadas y, por tanto, reducir la superficie en disco del almacén de consultas. Se recomienda cambiar gradualmente estos valores por incrementos pequeños.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

La siguiente consulta de ejemplo modifica un almacén de consultas existente para usar una directiva de captura personalizada que invalida la configuración predeterminada de EXECUTION_COUNT y TOTAL_COMPILE_CPU_TIME_MS.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

Almacén de consultas tamaño máximo

El valor de tamaño máximo predeterminado del almacén de consultas es de 1000 MB a partir de SQL Server 2019 (15.x). En versiones anteriores, el valor predeterminado era de 100 MB. Aumentar el tamaño límite máximo del almacén de consultas es lo más adecuado en una base de datos ocupada con muchos planes de consulta únicos. Ajustar la directiva de captura (consulte la sección anterior) es una consideración más importante para limitar el tamaño en disco del almacén de consultas y evitar que este entre en modo READ_ONLY. Mientras que el almacén de consultas recopila consultas, planes de ejecución y estadísticas, su tamaño en la base de datos crece hasta que se alcanza este límite. Cuando esto sucede, el almacén de consultas cambia automáticamente el modo de operación a READ_ONLY y deja de recopilar nuevos datos, lo que significa que el análisis de rendimiento ya no es preciso.

  • En SQL Server y Azure SQL Managed Instance, el límite MAX_STORAGE_SIZE_MB no se aplica estrictamente.
  • En Azure SQL Database, el valor MAX_STORAGE_SIZE_MB máximo permitido es de 10 240 MB.

El tamaño de almacenamiento solo se comprueba cuando el almacén de consultas escribe datos en el disco. Este intervalo se establece mediante la opción DATA_FLUSH_INTERVAL_SECONDS o la opción de diálogo Intervalo de vaciado de datos del Almacén de consultas de Management Studio.

  • El valor predeterminado de intervalo es 900 segundos (o 15 minutos).
  • Si el Almacén de consultas ha infringido el límite de MAX_STORAGE_SIZE_MB entre las comprobaciones de tamaño de almacenamiento, pasa al modo de solo lectura.
  • Si SIZE_BASED_CLEANUP_MODE está habilitado, también se desencadena el mecanismo de limpieza para aplicar el límite de MAX_STORAGE_SIZE_MB.
    • Una vez que se haya borrado suficiente espacio, el modo del almacén de consultas cambiará automáticamente al modo READ_WRITE.

Para obtener más información, consulta la opción ALTER DATABASE SET OPTION MAX_STORAGE_SIZE_MB.

Intervalo de vaciado de datos (minutos)

El intervalo de vaciado de datos define la frecuencia antes de que las estadísticas de tiempo de ejecución recopiladas se conserven en el disco. En SQL Server Management Studio, el valor está en minutos, pero en Transact-SQL se expresa en segundos. El valor predeterminado es 15 minutos (900 segundos).

  • Aumentar el intervalo de vaciado de datos puede reducir en general el impacto de E/S de almacenamiento del almacén de consultas, pero provocar que la carga de trabajo de E/S de almacenamiento tenga más picos, con menos impactos pero más pesados en el uso del disco. Considere la posibilidad de usar un valor más alto si la carga de trabajo no genera gran cantidad de consultas y planes diferentes, o bien si puede soportar más tiempo de conservación de los datos antes de cerrar la base de datos.
  • Al disminuir el intervalo de vaciado de datos, se reduce la cantidad de datos del almacén de consultas que se perdería en caso de apagado, pérdida de energía o conmutación por error. También puede suavizar el impacto de E/S de almacenamiento del almacén de consultas escribiendo en el disco con más frecuencia, pero con menos datos.

Nota:

El uso de la marca de seguimiento 7745 impide que los datos del almacén de consultas se escriban en el disco en el caso de un comando de conmutación por error o apagado. Para obtener más información, consulte Uso del almacén de consultas en servidores críticos.

Modificar los valores predeterminados del almacén de consultas

Configure el Almacén de consultas en función de la carga de trabajo y los requisitos de solución de problemas de rendimiento. Los parámetros predeterminados son suficientemente buenos como punto de partida, pero debe supervisar el comportamiento del almacén de consultas en el tiempo y ajustar su configuración en consecuencia.

Ver la configuración actual del almacén de consultas

Vea la configuración actual del almacén de consultas en SQL Server Management Studio (SSMS) o T-SQL.

Descargar la última versión de SQL Server Management Studio (SSMS)

Para ver la configuración actual en Management Studio:

  1. En el explorador de objetos de SQL Server Management Studio, haga clic con el botón derecho en la base de datos.
  2. Selecciona Propiedades.
  3. Seleccione Almacén de consultas.

En el script siguiente se establece un nuevo valor para Tamaño de máximo (MB):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

Use SQL Server Management Studio o Transact-SQL para establecer un valor diferente para Intervalo de vaciado de datos:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

Intervalo de la recopilación de estadísticas: define el nivel de granularidad de la estadística en tiempo de ejecución recopilada y se expresa en minutos. El valor predeterminado es 60 minutos. Considere la posibilidad de usar un valor más bajo si necesita una granularidad más precisa o menos tiempo para detectar y mitigar las incidencias. Recuerde que el valor afecta directamente al tamaño de los datos del almacén de consultas. Use SQL Server Management Studio o Transact-SQL para establecer un valor diferente para Intervalo de recopilación de estadísticas:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

Umbral de consultas obsoletas (días): directiva de limpieza basada en el tiempo que controla el período de retención de las estadísticas en tiempo de ejecución persistentes y las consultas inactivas; se expresa en días. De forma predeterminada, el almacén de consultas se configura para conservar los datos durante 30 días, que podría ser un período innecesariamente largo para su escenario.

Evite mantener datos históricos que no tenga pensado usar. Este procedimiento reduce los cambios al estado de solo lectura. El tamaño de los datos del almacén de consultas y el tiempo para detectar y mitigar la incidencia serán más predecibles. Use Management Studio o el siguiente script para configurar la directiva de limpieza basada en el tiempo:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

Modo de limpieza basado en el tamaño: especifica si la limpieza automática de los datos se lleva a cabo cuando el tamaño de datos del almacén de consultas se aproxime al límite. Active la limpieza basada en el tamaño para asegurarse de que el almacén de consultas siempre se ejecuta en modo de lectura y escritura, y recopila los datos más recientes. No hay ninguna garantía de que la limpieza del almacén de consultas, en caso de cargas de trabajo elevadas, mantenga el tamaño de los datos dentro del límite. Es posible que la limpieza automática de datos se quede atrás y cambie (temporalmente) al modo de solo lectura.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

Modo de captura del almacén de consultas: especifica la directiva de captura de consultas para el almacén de consultas.

  • Todos: captura todas las consultas. Es la opción predeterminada en SQL Server 2016 (13.x) y SQL Server 2017 (14.x).
  • Automático: se omiten las consultas poco frecuentes y aquellas con una duración de compilación y ejecución insignificante. Los umbrales para la duración del tiempo de ejecución, compilación y recuento de ejecuciones se determinan de forma interna. A partir de SQL Server 2019 (15.x), esta es la opción predeterminada.
  • Ninguna: el almacén de consultas deja de capturar consultas nuevas.
  • Personalizado: permite un control adicional y la capacidad de ajustar la directiva de recopilación de datos. La nueva configuración personalizada define lo que sucede durante el umbral de tiempo de la directiva de captura interna. Es un límite de tiempo durante el que se evalúan las condiciones configurables y, si alguna de ellas es verdadera, la consulta se puede registrar en el almacén de consultas.

Importante

Los cursores, las consultas dentro de procedimientos almacenados y las consultas compiladas de forma nativa siempre se capturan cuando Modo de captura de Almacén de consultas se establece en Todo, Automático o Personalizado. Para capturar consultas compiladas de forma nativa, habilite la recopilación de estadísticas por consulta mediante sys.sp_xtp_control_query_exec_stats.

En el script siguiente se establece QUERY_CAPTURE_MODE en AUTO:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Ejemplos

En el ejemplo siguiente, se establece QUERY_CAPTURE_MODE en AUTO y se configuran otras opciones recomendadas en SQL Server 2016 (13.x):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

En el ejemplo siguiente, se establece QUERY_CAPTURE_MODE en AUTO y se configuran otras opciones recomendadas en SQL Server 2017 (14.x) para incluir estadísticas de espera:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

En el ejemplo siguiente se establece la directiva de captura CUSTOM en los valores predeterminados de SQL Server 2019 (15.x), en lugar del nuevo modo de captura AUTO predeterminado. Para obtener más información sobre las opciones y los valores predeterminados de la directiva de captura personalizada, consulte <query_capture_policy_option_list>.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Mantenimiento del almacén de consultas

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 obtener información sobre los motivos, consulte sys.database_query_store_options.

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. Usa uno de los siguientes intervalos: 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, es posible que desee borrar los datos del almacén de consultas antiguos mediante la instrucción siguiente.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Como alternativa, es posible que desee borrar solo los datos de consulta ad hoc, ya que es menos relevante para las optimizaciones de consulta y el análisis del plan, pero ocupa tanto 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 siete días.

Eliminación de 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 quitan 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.

El ejemplo anterior 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.

Contenido relacionado