Supervisión del rendimiento mediante el almacén de consultasMonitoring performance by using the Query Store

SE APLICA A: síSQL Server síAzure SQL Database noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

La característica del Almacén de consultas de SQL ServerSQL Server ofrece datos detallados sobre el rendimiento y la elección del plan de consultas.The SQL ServerSQL Server Query Store feature provides you with insight on query plan choice and performance. Esta característica 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.It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. 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.Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. 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.It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server. El almacén de consultas se puede configurar con la opción ALTER DATABASE SET .You can configure query store using the ALTER DATABASE SET option.

Para más información sobre el funcionamiento del Almacén de consultas de Azure SQL DatabaseSQL Database, vea Funcionamiento del almacén de consultas de Base de datos SQL de Azure.For information about operating the Query Store in Azure SQL DatabaseSQL Database, see Operating the Query Store in Azure SQL Database.

Importante

Si usa el Almacén de consultas para conclusiones de la carga de trabajo just-in-time en SQL Server 2016 (13.x)SQL Server 2016 (13.x), tenga previsto instalar las correcciones de escalabilidad de rendimiento en KB 4340759 lo antes posible.If you are using Query Store for just in time workload insights in SQL Server 2016 (13.x)SQL Server 2016 (13.x), plan to install the performance scalability fixes in KB 4340759 as soon as possible.

Habilitar el Almacén de consultasEnabling the Query Store

El Almacén de consultas no está activo para nuevas bases de datos de manera predeterminada.Query Store is not active for new databases by default.

Use la página del Almacén de consultas de SQL Server Management StudioSQL Server Management Studio.Use the Query Store Page in SQL Server Management StudioSQL Server Management Studio

  1. En el Explorador de objetos, haga clic con el botón derecho en una base de datos y, luego, haga clic en Propiedades.In Object Explorer, right-click a database, and then click Properties.

    Nota

    Se requiere como mínimo la versión 16 de Management StudioManagement Studio.Requires at least version 16 of Management StudioManagement Studio.

  2. En el cuadro de diálogo Propiedades de la base de datos , seleccione la página Almacén de consultas .In the Database Properties dialog box, select the Query Store page.

  3. En el cuadro Modo de operación (solicitado) seleccione Lectura y escritura.In the Operation Mode (Requested) box, select Read Write.

Uso de instrucciones Transact-SQLUse Transact-SQL Statements

Use la instrucción ALTER DATABASE para habilitar el almacén de consultas.Use the ALTER DATABASE statement to enable the query store. Por ejemplo:For example:

ALTER DATABASE AdventureWorks2012 
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE); 

Para obtener más opciones de sintaxis relacionadas con el Almacén de consultas, vea Opciones de ALTER DATABASE SET (Transact-SQL).For more syntax options related to the Query Store, see ALTER DATABASE SET Options (Transact-SQL).

Nota

No se puede habilitar el Almacén de consultas para las bases de datos master o tempdb.Query Store cannot be enabled for the master or tempdb databases.

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.For information on enabling Query Store and keeping it adjusted to your workload, refer to Best Practice with the Query Store.

Información del Almacén de consultasInformation in the Query Store

Los planes de ejecución para cualquier consulta específica en SQL ServerSQL 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.Execution plans for any specific query in SQL ServerSQL Server typically evolve over time due to a number of different reasons such as statistics changes, schema changes, creation/deletion of indexes, etc. The procedure cache (where cached query plans are stored) only stores the latest execution plan. Los planes también se eliminan de la caché de planes debido a la presión de memoria.Plans also get evicted from the plan cache due to memory pressure. 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.As a result, query performance regressions caused by execution plan changes can be non-trivial and time consuming to resolve.

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.Since the Query Store retains multiple execution plans per query, it can enforce policies to direct the Query Processor to use a specific execution plan for a query. Esto se conoce como forzado de plan.This is referred to as plan forcing. 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.Plan forcing in Query Store is provided by using a mechanism similar to the USE PLAN query hint, but it does not require any change in user applications. 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.Plan forcing can resolve a query performance regression caused by a plan change in a very short period of time.

Nota

El Almacén de consultas recopila los planes para las instrucciones DML, como SELECT, INSERT, UPDATE, DELETE, MERGE y BULK INSERT.Query Store collects plans for DML Statements such as SELECT, INSERT, UPDATE, DELETE, MERGE, and BULK INSERT.

Nota

De forma predeterminada, el Almacén de consultas no recopila datos para los procedimientos almacenados compilados de forma nativa.Query Store does not collect data for natively compiled stored procedures by default. Use sys.sp_xtp_control_query_exec_stats para habilitar la recopilación de datos para los procedimientos almacenados compilados de forma nativa.Use sys.sp_xtp_control_query_exec_stats to enable data collection for natively compiled stored procedures.

Las estadísticas de espera son otra fuente de información que ayuda a solucionar problemas de rendimiento en Motor de base de datosDatabase Engine.Wait stats are another source of information that helps to troubleshoot performance in the Motor de base de datosDatabase Engine. 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.For a long time, wait statistics were available only on instance level, which made it hard to backtrack waits to a specific query. A partir de SQL Server 2017 (14.x)SQL Server 2017 (14.x) y Base de datos SQL de AzureAzure 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.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Base de datos SQL de AzureAzure SQL Database, Query Store includes a dimension that tracks wait stats. The following example enables the Query Store to collect wait stats.

ALTER DATABASE AdventureWorks2012 
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

Entre los escenarios comunes para usar la característica Almacén de consultas se encuentran:Common scenarios for using the Query Store feature are:

  • Buscar y corregir rápidamente una regresión de rendimiento de plan forzando el plan de consulta anterior.Quickly find and fix a plan performance regression by forcing the previous query plan. Corregir las consultas de las que se ha realizado regresión recientemente en el rendimiento debido a cambios del plan de ejecución.Fix queries that have recently regressed in performance due to execution plan changes.
  • 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.Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
  • Identificar las principales n consultas (por tiempo de ejecución, consumo de memoria, etc.) en las últimas x horas.Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.
  • Auditar el historial de planes de consulta para una consulta determinada.Audit the history of query plans for a given query.
  • Analizar los patrones de uso (CPU, E/S y memoria) de recursos para una base de datos determinada.Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.
  • Identificar las principales n consultas que están esperando recursos.Identify top n queries that are waiting on resources.
  • Comprender la naturaleza de espera de una consulta o un plan determinados.Understand wait nature for a particular query or plan.

El Almacén de consultas contiene tres almacenes:The Query Store contains three stores:

  • un almacén de planes para conservar la información del plan de ejecución,a plan store for persisting the execution plan information.
  • un almacén de estadísticas de runtime para conservar la información de las estadísticas de ejecución,a runtime stats store for persisting the execution statistics information.
  • un almacén de estadísticas de espera para conservar la información de las estadísticas de espera.a wait stats store for persisting wait statistics information.

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 .The number of unique plans that can be stored for a query in the plan store is limited by the max_plans_per_query configuration option. Para mejorar el rendimiento, la información se escribe en los almacenes de forma asincrónica.To enhance performance, the information is written to the stores asynchronously. 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.To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed time window. La información de estos almacenes se puede ver al consultar las vistas del catálogo del Almacén de consultas.The information in these stores is visible by querying the Query Store catalog views.

La siguiente consulta devuelve información sobre las consultas y los planes del Almacén de consultas.The following query returns information about queries and plans in the Query Store.

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 ;  

Uso de la característica Consultas devueltasUse the Regressed Queries feature

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.After enabling the Query Store, refresh the database portion of the Object Explorer pane to add the Query Store section.

Árbol de Almacén de consultas de SQL Server 2016 en Explorar de objetos de SSMS Árbol de Almacén de consultas de SQL Server 2017 en Explorador de objetos de SSMSSQL Server 2016 Query Store tree in SSMS Object Explorer SQL Server 2017 Query Store tree in SSMS Object Explorer

Seleccione Regressed Queries (Consultas devueltas) para abrir el panel del mismo nombre Regressed Queries en SQL Server Management StudioSQL Server Management Studio.Select Regressed Queries to open the Regressed Queries pane in SQL Server Management StudioSQL Server Management Studio. En el panel Regressed Queries (Consultas devueltas) se muestran las consultas y los planes del Almacén de consultas.The Regressed Queries pane shows you the queries and plans in the query store. 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).Use the drop down boxes at the top to filter queries based on various criteria: Duration (ms) (Default), CPU Time (ms), Logical Reads (KB), Logical Writes (KB), Physical Reads (KB), CLR Time (ms), DOP, Memory Consumption (KB), Row Count, Log Memory Used (KB), Temp DB Memory Used (KB), and Wait Time (ms).
Seleccione un plan para ver el plan de consulta gráfica.Select a plan to see the graphical query plan. 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.Buttons are available to view the source query, force and unforce a query plan, toggle between grid and chart formats, compare selected plans (if more than one is selected), and refresh the display.

Consultas devueltas de SQL Server 2016 en Explorador de objetos de SSMSSQL Server 2016 Regressed Queries in SSMS Object Explorer

Para aplicar un plan, seleccione una consulta y el plan y luego haga clic en Force Plan(Forzar plan).To force a plan, select a query and plan, and then click Force 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.You can only force plans that were saved by the query plan feature and are still retained in the query plan cache.

Buscar consultas en esperaFinding waiting queries

A partir de SQL Server 2017 (14.x)SQL Server 2017 (14.x) y Base de datos SQL de AzureAzure SQL Database, las estadísticas de espera por consulta a lo largo del tiempo están disponibles en el Almacén de consultas.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Base de datos SQL de AzureAzure SQL Database, wait statistics per query over time are available in Query Store.

En el Almacén de consultas, los tipos de espera se combinan en categorías de espera.In Query Store, wait types are combined into wait categories. La asignación de categorías de espera a tipos de espera está disponible en sys.query_store_wait_stats (Transact-SQL).The mapping of wait categories to wait types is available in 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 StudioSQL Server Management Studio v18 o superior.Select Query Wait Statistics to open the Query Wait Statistics pane in SQL Server Management StudioSQL Server Management Studio v18 or higher. 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.The Query Wait Statistics pane shows you a bar chart containing the top wait categories in the Query Store. 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).Use the drop down at the top to select an aggregate criteria for the wait time: avg, max, min, std dev, and total (default).

Estadísticas de espera de consulta de SQL Server 2017 en Explorador de objetos de SSMSSQL Server 2017 Query Wait Statistics in SSMS Object Explorer

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.Select a wait category by clicking on the bar and a detail view on the selected wait category displays. Este gráfico de barras nuevo contiene las consultas que han contribuido a esa categoría de espera.This new bar chart contains the queries that contributed to that wait category.

Vista de detalle de estadísticas de espera de consulta de SQL Server 2017 en Explorador de objetos de SSMSSQL Server 2017 Query Wait Statistics detail view in SSMS Object Explorer

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).Use the drop down box at the top to filter queries based on various wait time criteria for the selected wait category: avg, max, min, std dev, and total (default). Seleccione un plan para ver el plan de consulta gráfica.Select a plan to see the graphical query plan. 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.Buttons are available to view the source query, force, and unforce a query plan, and refresh the display.

Las categorías de espera combinan distintos tipos de espera en cubos similares por naturaleza.Wait categories are combining different wait types into buckets similar by nature. 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; el 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.Different wait categories require a different follow up analysis to resolve the issue, but wait types from the same category lead to very similar troubleshooting experiences, and providing the affected query on top of waits would be the missing piece to complete the majority of such investigations successfully.

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:Here are some examples how you can get more insights into your workload before and after introducing wait categories in Query Store:

Experiencia anteriorPrevious experience Nueva experienciaNew experience AcciónAction
Altas esperas RESOURCE_SEMAPHORE por base de datosHigh RESOURCE_SEMAPHORE waits per database Altas esperas de memoria en el Almacén de consultas para consultas concretasHigh Memory waits in Query Store for specific queries Busque las consultas del Almacén de consultas que consumen más memoria.Find the top memory consuming queries in Query Store. Estas consultas probablemente retrasan el progreso de las consultas afectadas.These queries are probably delaying further progress of the affected queries. Considere la posibilidad de usar la sugerencia de consulta MAX_GRANT_PERCENT para estas consultas o para las consultas afectadas.Consider using MAX_GRANT_PERCENT query hint for these queries, or for the affected queries.
Altas esperas LCK_M_X por base de datosHigh LCK_M_X waits per database Altas esperas de bloqueo en el Almacén de consultas para consultas concretasHigh Lock waits in Query Store for specific queries Compruebe los textos de consulta de las consultas afectadas e identifique las entidades de destino.Check the query texts for the affected queries and identify the target entities. 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.Look in Query Store for other queries modifying the same entity, which are executed frequently and/or have high duration. Tras identificar estas consultas, considere la posibilidad de cambiar la lógica de aplicación para mejorar la simultaneidad o use un nivel de aislamiento menos restrictivo.After identifying these queries, consider changing the application logic to improve concurrency, or use a less restrictive isolation level.
Altas esperas PAGEIOLATCH_SH por base de datosHigh PAGEIOLATCH_SH waits per database Altas esperas de E/S del búfer en el Almacén de consultas para consultas concretasHigh Buffer IO waits in Query Store for specific queries Busque las consultas con un gran número de lecturas físicas en el Almacén de consultas.Find the queries with a high number of physical reads in Query Store. 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.If they match the queries with high IO waits, consider introducing an index on the underlying entity, in order to do seeks instead of scans, and thus minimize the IO overhead of the queries.
Altas esperas SOS_SCHEDULER_YIELD por base de datosHigh SOS_SCHEDULER_YIELD waits per database Altas esperas de CPU en el Almacén de consultas para consultas concretasHigh CPU waits in Query Store for specific queries Busque las consultas del Almacén de consultas que consumen más CPU.Find the top CPU consuming queries in Query Store. Entre ellas, identifique las consultas cuya alta tendencia de CPU se correlaciona con altas esperas de CPU para las consultas afectadas.Among them, identify the queries for which high CPU trend correlates with high CPU waits for the affected queries. Céntrese en la optimización de las consultas: podría ser una regresión de plan o posiblemente un índice que falta.Focus on optimizing those queries - there could be a plan regression, or perhaps a missing index.

Opciones de configuraciónConfiguration Options

Tiene disponibles las siguientes opciones para configurar los parámetros del Almacén de consultas.The following options are available to configure Query Store parameters.

OPERATION_MODEOPERATION_MODE
Puede ser READ_WRITE (valor predeterminado) o READ_ONLY.Can be READ_WRITE (default) or READ_ONLY.

CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)
Configure el argumento STALE_QUERY_THRESHOLD_DAYS para especificar el número de días que se conservarán los datos en el Almacén de consultas.Configure the STALE_QUERY_THRESHOLD_DAYS argument to specify the number of days to retain data in the Query Store. El valor predeterminado es 30.The default value is 30. En la edición SQL DatabaseSQL Database Basic, el valor predeterminado es 7 días.For SQL DatabaseSQL Database Basic edition, default is 7 days.

DATA_FLUSH_INTERVAL_SECONDSDATA_FLUSH_INTERVAL_SECONDS
Determina la frecuencia con la que los datos escritos en el Almacén de consultas se conservan en el disco.Determines the frequency at which data written to the Query Store is persisted to disk. Para optimizar el rendimiento, los datos recopilados por el almacén de consultas se escriben de manera asincrónica en el disco.To optimize for performance, data collected by the query store is asynchronously written to the disk. La frecuencia con la que se produce esta transferencia asincrónica se configura mediante DATA_FLUSH_INTERVAL_SECONDS.The frequency at which this asynchronous transfer occurs is configured via DATA_FLUSH_INTERVAL_SECONDS. El valor predeterminado es 900 (15 minutos).The default value is 900 (15 min).

MAX_STORAGE_SIZE_MBMAX_STORAGE_SIZE_MB
Configura el tamaño máximo del Almacén de consultas.Configures the maximum size of the Query Store. Si los datos del Almacén de consultas alcanzan el límite de MAX_STORAGE_SIZE_MB, el Almacén de consultas cambia automáticamente el estado de lectura-escritura a solo lectura y deja de recopilar datos nuevos.If the data in the Query Store hits the MAX_STORAGE_SIZE_MB limit, the Query Store automatically changes the state from read-write to read-only and stops collecting new data. El valor predeterminado es de 100 MB para SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) hasta SQL Server 2017 (14.x)SQL Server 2017 (14.x)).The default value is 100 MB for SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017 (14.x)SQL Server 2017 (14.x)). A partir de SQL Server 2019 (15.x)SQL Server 2019 (15.x), el valor predeterminado es 1 GB.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the default value is 1 GB. En la edición SQL DatabaseSQL Database Premium, el valor predeterminado es 1 GB y en la edición SQL DatabaseSQL Database Basic, el valor predeterminado es 10 MB.For SQL DatabaseSQL Database Premium edition, default is 1 GB and for SQL DatabaseSQL Database Basic edition, default is 10 MB.

INTERVAL_LENGTH_MINUTESINTERVAL_LENGTH_MINUTES
Determina el intervalo de tiempo en el que se agregan los datos de estadísticas de ejecución en tiempo de ejecución al Almacén de consultas.Determines the time interval at which runtime execution statistics data is aggregated into the Query Store. Para optimizar 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.To optimize for space usage, the runtime execution statistics in the Runtime Stats Store are aggregated over a fixed time window. Este período fijo de tiempo se configura mediante INTERVAL_LENGTH_MINUTES.This fixed time window is configured via INTERVAL_LENGTH_MINUTES. El valor predeterminado es 60.The default value is 60.

SIZE_BASED_CLEANUP_MODESIZE_BASED_CLEANUP_MODE
Controla si el proceso de limpieza se activará automáticamente cuando la cantidad total se acerque al tamaño máximo.Controls whether the cleanup process will be automatically activated when total amount of data gets close to maximum size. Puede ser AUTO (valor predeterminado) u OFF.Can be AUTO (default) or OFF.

QUERY_CAPTURE_MODEQUERY_CAPTURE_MODE
Designa si el Almacén de consultas captura todas las consultas o las consultas pertinentes en función del consumo de recursos y el número de ejecuciones, o si deja de agregar nuevas consultas y solo realiza un seguimiento de las consultas actuales.Designates if the Query Store captures all queries, or relevant queries based on execution count and resource consumption, or stops adding new queries and just tracks current queries. Puede ser ALL (se capturan todas las consultas), AUTO (se omiten las consultas poco frecuentes y aquellas con una duración de compilación y ejecución insignificante), CUSTOM (directiva de captura definida por el usuario) o NONE (se detiene la captura de nuevas consultas).Can be ALL (capture all queries), AUTO (ignore infrequent and queries with insignificant compile and execution duration), CUSTOM (user defined capture policy), or NONE (stop capturing new queries). El valor predeterminado es ALL para SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) hasta SQL Server 2017 (14.x)SQL Server 2017 (14.x)).The default value is ALL for SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017 (14.x)SQL Server 2017 (14.x)). A partir de SQL Server 2019 (15.x)SQL Server 2019 (15.x), el valor predeterminado es AUTO.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the default value is AUTO. El valor predeterminado para Base de datos SQL de AzureAzure SQL Database es AUTO.The default value for Base de datos SQL de AzureAzure SQL Database is AUTO.

MAX_PLANS_PER_QUERYMAX_PLANS_PER_QUERY
Entero que representa el número máximo de planes que se tienen para cada consulta.An integer representing the maximum number of plans maintained for each query. El valor predeterminado es 200.The default value is 200.

WAIT_STATS_CAPTURE_MODEWAIT_STATS_CAPTURE_MODE
Controla si el Almacén de consultas captura información de estadísticas de espera.Controls if Query Store captures wait statistics information. Puede ser OFF u ON (valor predeterminado).Can be OFF or ON (default).

Consulte la vista sys.database_query_store_options para determinar las opciones actuales del Almacén de consultas.Query the sys.database_query_store_options view to determine the current options of the Query Store. Para obtener más información sobre los valores, vea sys.database_query_store_options.For more information about the values, see sys.database_query_store_options.

Para obtener más información sobre el establecimiento de opciones mediante instrucciones Transact-SQLTransact-SQL , vea Administración de opciones.For more information about setting options by using Transact-SQLTransact-SQL statements, see Option Management.

Vea y administre El Almacén de consultas mediante Management StudioManagement Studio o por medio de las siguientes vistas y procedimientos.View and manage Query Store through Management StudioManagement Studio or by using the following views and procedures.

Funciones del Almacén de consultasQuery Store Functions

Las funciones ayudan a las operaciones del Almacén de consultas.Functions help operations with the Query Store.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

Vistas de catálogo del almacén de consultasQuery Store Catalog Views

Las vistas de catálogo presentan información sobre el Almacén de consultas.Catalog views present information about the Query Store.

sys.database_query_store_options (Transact-SQL)sys.database_query_store_options (Transact-SQL) sys.query_context_settings (Transact-SQL)sys.query_context_settings (Transact-SQL)
sys.query_store_plan (Transact-SQL)sys.query_store_plan (Transact-SQL) sys.query_store_query (Transact-SQL)sys.query_store_query (Transact-SQL)
sys.query_store_query_text (Transact-SQL)sys.query_store_query_text (Transact-SQL) sys.query_store_runtime_stats (Transact-SQL)sys.query_store_runtime_stats (Transact-SQL)
sys.query_store_wait_stats (Transact-SQL)sys.query_store_wait_stats (Transact-SQL) sys.query_store_runtime_stats_interval (Transact-SQL)sys.query_store_runtime_stats_interval (Transact-SQL)

Procedimientos almacenados del almacén de consultasQuery Store Stored Procedures

Los procedimientos almacenados configuran el Almacén de consultas.Stored procedures configure the Query Store.

sp_query_store_flush_db (Transact-SQL)sp_query_store_flush_db (Transact-SQL) sp_query_store_reset_exec_stats (Transact-SQL)sp_query_store_reset_exec_stats (Transact-SQL)
sp_query_store_force_plan (Transact-SQL)sp_query_store_force_plan (Transact-SQL) sp_query_store_unforce_plan (Transact-SQL)sp_query_store_unforce_plan (Transact-SQL)
sp_query_store_remove_plan (Transct-SQL)sp_query_store_remove_plan (Transct-SQL) sp_query_store_remove_query (Transact-SQL)sp_query_store_remove_query (Transact-SQL)
sp_query_store_consistency_check (Transct-SQL)sp_query_store_consistency_check (Transct-SQL)

Escenarios de uso claveKey Usage Scenarios

Administración de opcionesOption Management

En esta sección se ofrecen algunas directrices sobre la administración de la propia característica Almacén de consultas.This section provides some guidelines on managing Query Store feature itself.

¿Está el Almacén de consultas activo actualmente?Is Query Store currently active?

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).Query Store stores its data inside the user database and that is why it has size limit (configured with 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.If data in Query Store hits that limit Query Store will automatically change state from read-write to read-only and stop collecting new data.

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.Query sys.database_query_store_options to determine if Query Store is currently active, and whether it is currently collects runtime stats or not.

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.Query Store status is determined by actual_state column. Si es diferente al estado deseado, la columna readonly_reason puede proporcionar más información.If it's different than the desired status, the readonly_reason column can give you more information.
Cuando el tamaño del Almacén de consultas supera la cuota, la característica cambiará al modo de readon_only.When Query Store size exceeds the quota, the feature will switch to readon_only mode.

Obtener opciones del Almacén de consultasGet Query Store options

Para averiguar información detallada sobre el estado del Almacén de consultas, ejecute lo siguiente en una base de datos de usuario.To find out detailed information about Query Store status, execute following in a user database.

SELECT * FROM sys.database_query_store_options;  

Establecimiento del intervalo del Almacén de consultasSetting Query Store interval

Puede invalidar el intervalo para agregar estadísticas de tiempo de ejecución de consultas (el valor predeterminado es 60 minutos).You can override interval for aggregating query runtime statistics (default is 60 minutes).

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

Nota

No se admiten valores arbitrarios para INTERVAL_LENGTH_MINUTES.Arbitrary values are not allowed for INTERVAL_LENGTH_MINUTES. Use uno de los siguientes: 1, 5, 10, 15, 30, 60 o 1440 minutos.Use one of the following: 1, 5, 10, 15, 30, 60, or 1440 minutes.

El nuevo valor de intervalo se expone a través de la vista sys.database_query_store_options .New value for interval is exposed through sys.database_query_store_options view.

Uso de espacio en el Almacén de consultasQuery Store space usage

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.To check current the Query Store size and limit execute the following statement in the user database.

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.If the Query Store storage is full use the following statement to extend the storage.

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

Establecer opciones del Almacén de consultasSet Query Store options

Puede establecer varias opciones del Almacén de consultas a la vez con una sola instrucción ALTER DATABASE.You can set multiple Query Store options at once with a single ALTER DATABASE statement.

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).For the full list of configuration options, see ALTER DATABASE SET Options (Transact-SQL).

Limpieza del espacioCleaning up the space

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.Query Store internal tables are created in the PRIMARY filegroup during database creation and that configuration cannot be changed later. 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.If you are running out of space you might want to clear older Query Store data by using the following statement.

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.Alternatively, you might want to clear up only ad-hoc query data, since it is less relevant for query optimizations and plan analysis but takes up just as much space.

Eliminar consultas ad hocDelete ad-hoc queries

Se eliminan las consultas que solo se ejecutaron una vez y que tienen más de 24 horas de antigüedad.This deletes the queries that were only executed only once and that are more than 24 hours old.

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  
GROUP BY q.query_id  
HAVING SUM(rs.count_executions) < 2   
AND MAX(rs.last_execution_time) < DATEADD (hour, -24, GETUTCDATE())  
ORDER BY q.query_id ;  
  
OPEN adhoc_queries_cursor ;  
FETCH NEXT FROM adhoc_queries_cursor INTO @id;  
WHILE @@fetch_status = 0  
    BEGIN   
        PRINT @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.You can define your own procedure with different logic for clearing up data you no longer want.

En el ejemplo anterior se usa el procedimiento almacenado extendido sp_query_store_remove_query para quitar datos innecesarios.The example above uses the sp_query_store_remove_query extended stored procedure for removing unnecessary data. También se puede usar:You can also use:

  • sp_query_store_reset_exec_stats para borrar las estadísticas de tiempo de ejecución de un plan determinado.sp_query_store_reset_exec_stats to clear runtime statistics for a given plan.
  • sp_query_store_remove_plan para quitar un único plan.sp_query_store_remove_plan to remove a single plan.

Auditoría del rendimiento y solución de problemasPerformance Auditing and Troubleshooting

El Almacén de consultas mantiene un historial de las métricas de compilación y tiempo de ejecución en todas las ejecuciones de consulta, lo que le permite realizar preguntas sobre la carga de trabajo.Query Store keeps a history of compilation and runtime metrics throughout query executions, allowing you to ask questions about your workload.

¿Últimas n consultas ejecutadas en la base de datos?Last n queries executed on the database?

SELECT TOP 10 qt.query_sql_text, q.query_id,   
    qt.query_text_id, p.plan_id, rs.last_execution_time  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id  
ORDER BY rs.last_execution_time DESC;  

¿Número de ejecuciones de cada consulta?Number of executions for each query?

SELECT q.query_id, qt.query_text_id, qt.query_sql_text,   
    SUM(rs.count_executions) AS total_execution_count  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id  
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text  
ORDER BY total_execution_count DESC;  

¿El número de consultas con el mayor tiempo medio de ejecución durante la última hora?The number of queries with the longest average execution time within last hour?

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,  
    qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,   
    rs.last_execution_time   
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id  
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())  
ORDER BY rs.avg_duration DESC;  

¿El número de consultas que han tenido la media máxima de lecturas de E/S físicas durante las últimas 24 horas, con la correspondiente media del número de filas y el número de ejecuciones?The number of queries that had the biggest average physical I/O reads in last 24 hours, with corresponding average row count and execution count?

SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text,   
    q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,   
    rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi   
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id  
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())   
ORDER BY rs.avg_physical_io_reads DESC;  

¿Consultas con varios planes?Queries with multiple plans? Estas consultas son especialmente interesantes porque son candidatas para las regresiones debido al cambio de elección del plan.These queries are especially interesting because they are candidates for regressions due to plan choice change. La siguiente consulta identifica estas consultas junto con todos los planes:The following query identifies these queries along with all plans:

WITH Query_MultPlans  
AS  
(  
SELECT COUNT(*) AS cnt, q.query_id   
FROM sys.query_store_query_text AS qt  
JOIN sys.query_store_query AS q  
    ON qt.query_text_id = q.query_text_id  
JOIN sys.query_store_plan AS p  
    ON p.query_id = q.query_id  
GROUP BY q.query_id  
HAVING COUNT(distinct plan_id) > 1  
)  
  
SELECT q.query_id, object_name(object_id) AS ContainingObject,   
    query_sql_text, plan_id, p.query_plan AS plan_xml,  
    p.last_compile_start_time, p.last_execution_time  
FROM Query_MultPlans AS qm  
JOIN sys.query_store_query AS q  
    ON qm.query_id = q.query_id  
JOIN sys.query_store_plan AS p  
    ON q.query_id = p.query_id  
JOIN sys.query_store_query_text qt   
    ON qt.query_text_id = q.query_text_id  
ORDER BY query_id, plan_id;  

¿Consultas que se han devuelto recientemente por motivo de rendimiento (en comparación con otro momento dado)?Queries that recently regressed in performance (comparing different point in time)? El siguiente ejemplo de consulta devuelve todas las consultas para las que se duplicó el tiempo de ejecución en las últimas 48 horas debido a un cambio de elección del plan.The following query example returns all queries for which execution time doubled in last 48 hours due to a plan choice change. La consulta compara todos los intervalos de estadísticas en tiempo de ejecución en paralelo.Query compares all runtime stat intervals side by side.

SELECT   
    qt.query_sql_text,   
    q.query_id,   
    qt.query_text_id,   
    rs1.runtime_stats_id AS runtime_stats_id_1,  
    rsi1.start_time AS interval_1,   
    p1.plan_id AS plan_1,   
    rs1.avg_duration AS avg_duration_1,   
    rs2.avg_duration AS avg_duration_2,  
    p2.plan_id AS plan_2,   
    rsi2.start_time AS interval_2,   
    rs2.runtime_stats_id AS runtime_stats_id_2  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p1   
    ON q.query_id = p1.query_id   
JOIN sys.query_store_runtime_stats AS rs1   
    ON p1.plan_id = rs1.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi1   
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id   
JOIN sys.query_store_plan AS p2   
    ON q.query_id = p2.query_id   
JOIN sys.query_store_runtime_stats AS rs2   
    ON p2.plan_id = rs2.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi2   
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id  
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())   
    AND rsi2.start_time > rsi1.start_time   
    AND p1.plan_id <> p2.plan_id  
    AND rs2.avg_duration > 2*rs1.avg_duration  
ORDER BY q.query_id, rsi1.start_time, rsi2.start_time;  

Si quiere ver el rendimiento de todas las regresiones (no solo de aquellas relacionadas con el cambio de elección de plan), solo tiene que eliminar la condición AND p1.plan_id <> p2.plan_id de la consulta anterior.If you want to see performance all regressions (not only those related to plan choice change) than just remove condition AND p1.plan_id <> p2.plan_id from the previous query.

¿Consultas que más esperan?Queries that are waiting the most? Esta consulta devolverá las diez principales consultas que más esperan.This query will return top 10 queries that wait the most.

 SELECT TOP 10
   qt.query_text_id,
   q.query_id,
   p.plan_id,
   sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
GROUP BY qt.query_text_id, q.query_id, p.plan_id
ORDER BY sum_total_wait_ms DESC

¿Consultas que se han devuelto recientemente por motivo de rendimiento (comparando la ejecución de las recientes con las del historial)?Queries that recently regressed in performance (comparing recent vs. history execution)? La siguiente consulta compara períodos de ejecución basados en ejecución de consultas.The next query compares query execution based periods of execution. En este ejemplo concreto, la consulta compara la ejecución en el período reciente (1 hora) con el período del historial (último día) e identifica las que han especificado additional_duration_workload.In this particular example the query compares execution in recent period (1 hour) vs. history period (last day) and identifies those that introduced additional_duration_workload. Esta métrica se calcula como una diferencia entre la media de las ejecuciones recientes y la media de las ejecuciones del historial multiplicada por el número de ejecuciones recientes.This metrics is calculated as a difference between recent average execution and history average execution multiplied by the number of recent executions. En realidad representa la cantidad de ejecuciones recientes de duración adicional introducidas en comparación con el historial:It actually represents how much of additional duration recent executions introduced compared to history:

--- "Recent" workload - last 1 hour  
DECLARE @recent_start_time datetimeoffset;  
DECLARE @recent_end_time datetimeoffset;  
SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME());  
SET @recent_end_time = SYSUTCDATETIME();  
  
--- "History" workload  
DECLARE @history_start_time datetimeoffset;  
DECLARE @history_end_time datetimeoffset;  
SET @history_start_time = DATEADD(hour, -24, SYSUTCDATETIME());  
SET @history_end_time = SYSUTCDATETIME();  
  
WITH  
hist AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
     FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @history_start_time   
               AND rs.last_execution_time < @history_end_time)  
        OR (rs.first_execution_time <= @history_start_time   
               AND rs.last_execution_time > @history_start_time)  
        OR (rs.first_execution_time <= @history_end_time   
               AND rs.last_execution_time > @history_end_time)  
    GROUP BY p.query_id  
),  
recent AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
    FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @recent_start_time   
               AND rs.last_execution_time < @recent_end_time)  
        OR (rs.first_execution_time <= @recent_start_time   
               AND rs.last_execution_time > @recent_start_time)  
        OR (rs.first_execution_time <= @recent_end_time   
               AND rs.last_execution_time > @recent_end_time)  
    GROUP BY p.query_id  
)  
SELECT   
    results.query_id query_id,  
    results.query_text query_text,  
    results.additional_duration_workload additional_duration_workload,  
    results.total_duration_recent total_duration_recent,  
    results.total_duration_hist total_duration_hist,  
    ISNULL(results.count_executions_recent, 0) count_executions_recent,  
    ISNULL(results.count_executions_hist, 0) count_executions_hist   
FROM  
(  
    SELECT  
        hist.query_id query_id,  
        qt.query_sql_text query_text,  
        ROUND(CONVERT(float, recent.total_duration/  
                   recent.count_executions-hist.total_duration/hist.count_executions)  
               *(recent.count_executions), 2) AS additional_duration_workload,  
        ROUND(recent.total_duration, 2) total_duration_recent,   
        ROUND(hist.total_duration, 2) total_duration_hist,  
        recent.count_executions count_executions_recent,  
        hist.count_executions count_executions_hist     
    FROM hist   
        JOIN recent   
            ON hist.query_id = recent.query_id   
        JOIN sys.query_store_query AS q   
            ON q.query_id = hist.query_id  
        JOIN sys.query_store_query_text AS qt   
            ON q.query_text_id = qt.query_text_id      
) AS results  
WHERE additional_duration_workload > 0  
ORDER BY additional_duration_workload DESC  
OPTION (MERGE JOIN);  

Mantener la estabilidad del rendimiento de consultasMaintaining query performance stability

En las consultas ejecutadas varias veces, puede observar que SQL ServerSQL Server usa diferentes planes que han generado diferente duración y uso de los recursos.For queries executed multiple times you may notice that SQL ServerSQL Server uses different plans, resulting in different resource utilization and duration. Con el Almacén de consultas puede detectar cuándo se ha devuelto el rendimiento de las consultas y determinar el plan óptimo en un período de interés.With Query Store you can detect when query performance regressed and determine the optimal plan within a period of interest. Luego puede forzar ese plan óptimo para futuras ejecuciones de consultas.You can then force that optimal plan for future query execution.

También puede identificar el rendimiento incoherente de una consulta con parámetros (ya sea con parámetros automáticos o con parámetros manuales).You can also identify inconsistent query performance for a query with parameters (either auto-parameterized or manually parameterized). Entre los distintos planes puede identificar el plan que es lo suficientemente rápido y óptimo para todos o la mayoría de los valores de parámetros y forzar ese plan, manteniendo un rendimiento predecible para el conjunto más amplio de escenarios de usuario.Among different plans you can identify the plan which is fast and optimal enough for all or most of the parameter values and force that plan, keeping predictable performance for the wider set of user scenarios.

Forzar un plan para una consulta (aplicar directiva de forzado)Force a plan for a query (apply forcing policy)

Cuando se fuerza un plan para una consulta determinada, SQL ServerSQL Server intenta forzarlo en el optimizador.When a plan is forced for a certain query, SQL ServerSQL Server tries to force the plan in the optimizer. Si se produce un error al exigir el plan, se producirá un evento XEvent y el optimizador realizará su trabajo de forma normal.If plan forcing fails, an XEvent is fired and the optimizer is instructed to optimize in the normal way.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;  

Al usar sp_query_store_force_plan solo puede forzar los planes que se grabaron por el almacén de consultas como un plan para esa consulta.When using sp_query_store_force_plan you can only force plans that were recorded by Query Store as a plan for that query. Es decir, los únicos planes disponibles para una consulta son aquellos que ya se han usado para ejecutar la consulta mientras el Almacén de consultas estaba activo.In other words, the only plans available for a query are those that were already used to execute that query while Query Store was active.

Plan para forzar la compatibilidad con cursores estáticos y de avance rápido Plan forcing support for fast forward and static cursors

A partir de SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.3, el Almacén de consultas admite la capacidad de forzar planes de ejecución de consulta para los cursores de API y Transact-SQLTransact-SQL estáticos y de avance rápido.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.3, the Query Store supports the ability to force query execution plans for fast forward and static Transact-SQLTransact-SQL and API cursors. Se admite el forzado mediante sp_query_store_force_plan o a través de los informes del Almacén de consultas de SQL Server Management StudioSQL Server Management Studio.Forcing is supported via sp_query_store_force_plan or through SQL Server Management StudioSQL Server Management Studio Query Store reports.

Quitar el forzado de un plan para una consultaRemove plan forcing for a query

Para volver a confiar en el optimizador de consultas SQL ServerSQL Server para calcular el plan de consulta óptimo, use sp_query_store_unforce_plan para eliminar la aplicación del plan que se seleccionó para la consulta.To rely again on the SQL ServerSQL Server query optimizer to calculate the optimal query plan, use sp_query_store_unforce_plan to unforce the plan that was selected for the query.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;  

Consulte tambiénSee Also

Procedimiento recomendado con el Almacén de consultas Best Practice with the Query Store
Uso del almacén de consultas con OLTP en memoria Using the Query Store with In-Memory OLTP
Escenarios de uso del Almacén de consultas Query Store Usage Scenarios
Introducción a la recopilación de datos del almacén de consultas How Query Store Collects Data
Query Store Stored Procedures (Transact-SQL) (Procedimientos almacenados del Almacén de consultas (Transact-SQL)) Query Store Stored Procedures (Transact-SQL)
Query Store Catalog Views (Transact-SQL) (Vistas de catálogo del Almacén de consultas (Transact-SQL)) Query Store Catalog Views (Transact-SQL)
Supervisión y optimización del rendimiento Monitor and Tune for Performance
Herramientas de supervisión y optimización del rendimiento Performance Monitoring and Tuning Tools
Abrir el Monitor de actividad (SQL Server Management Studio) Open Activity Monitor (SQL Server Management Studio)
Estadísticas de consultas activas Live Query Statistics
Monitor de actividad Activity Monitor
sys.database_query_store_options (Transact-SQL)sys.database_query_store_options (Transact-SQL)
Funcionamiento del almacén de consultas de Azure SQL DatabaseOperating the Query Store in Azure SQL Database