Procedimientos recomendados con el almacén de consultasBest practices with Query Store

Se aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database SíInstancia administrada de Azure SQLAzure SQL Managed InstanceYesInstancia administrada de Azure SQLAzure SQL Managed Instance síAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse AnalyticsSe aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database SíInstancia administrada de Azure SQLAzure SQL Managed InstanceYesInstancia administrada de Azure SQLAzure SQL Managed Instance síAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

En este artículo se describen los procedimientos recomendados para usar el almacén de consultas de SQL Server con la carga de trabajo.This article outlines the best practices for using SQL Server Query Store with your workload.

Utilice la versión más reciente de SQL Server Management StudioSQL Server Management StudioUse the latest SQL Server Management StudioSQL Server Management Studio

SQL Server Management StudioSQL Server Management Studio tiene un conjunto de interfaces de usuario diseñadas para configurar el almacén de consultas y para consumir datos recopilados sobre la carga de trabajo.has a set of user interfaces designed for configuring Query Store and for consuming collected data about your workload. Descargue la última versión de Management StudioManagement Studio aquí.Download the latest version of Management StudioManagement Studio here.

Para obtener una descripción rápida sobre cómo usar el almacén de consultas en escenarios de solución de problemas, vea los blogs de Azure del Almacén de consultas.For a quick description on how to use Query Store in troubleshooting scenarios, see Query Store Azure blogs.

Uso de Información de rendimiento de consultas en Azure SQL DatabaseUse Query Performance Insight in Azure SQL Database

Si ejecuta Almacén de consultas en Azure SQL DatabaseAzure SQL Database, puede usar Información de rendimiento de consultas para analizar el consumo de recursos a lo largo del tiempo.If you run Query Store in Azure SQL DatabaseAzure SQL Database, you can use Query Performance Insight to analyze resource consumption over time. Aunque se puede usar Management StudioManagement Studio y Azure Data Studio para obtener el consumo de recursos detallado para todas las consultas (CPU, memoria y E/S) Información de rendimiento de consultas ofrece una forma rápida y eficaz de determinar su impacto en el consumo global de DTU de la base de datos.While you can use Management StudioManagement Studio and Azure Data Studio to get detailed resource consumption for all your queries, such as CPU, memory, and I/O, Query Performance Insight gives you a quick and efficient way to determine their impact on overall DTU consumption for your database. Para obtener más información, vea Información de rendimiento de consultas de Base de datos SQL de Azure.For more information, see Azure SQL Database Query Performance Insight.

En esta sección se describen los valores predeterminados de configuración óptimos que están diseñados para garantizar el funcionamiento confiable del Almacén de consultas y de las características que dependen de él.This section describes optimal configuration defaults that are designed to ensure reliable operation of the Query Store and dependent features. 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.Default configuration is optimized for continuous data collection, that is minimal time spent in OFF/READ_ONLY states. Para obtener más información sobre todas las opciones disponibles del Almacén de consultas, vea Opciones de ALTER DATABASE SET (Transact-SQL).For more information about all available Query Store options, see ALTER DATABASE SET options (Transact-SQL).

ConfiguraciónConfiguration DescripciónDescription Valor predeterminadoDefault ComentarioComment
MAX_STORAGE_SIZE_MBMAX_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.Specifies the limit for the data space that Query Store can take inside the customer database 100100 Se aplica a nuevas bases de datos.Enforced for new databases
INTERVAL_LENGTH_MINUTESINTERVAL_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.Defines size of time window during which collected runtime statistics for query plans are aggregated and persisted. Todos los planes de consulta activa tienen al menos una fila durante un período de tiempo definido con esta configuración.Every active query plan has at most one row for a period of time defined with this configuration 6060 Se aplica a nuevas bases de datos.Enforced for new databases
STALE_QUERY_THRESHOLD_DAYSSTALE_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.Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries 3030 Se aplica a nuevas bases de datos y bases de datos con la configuración predeterminada anterior (367).Enforced for new databases and databases with previous default (367)
SIZE_BASED_CLEANUP_MODESIZE_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.Specifies whether automatic data cleanup takes place when Query Store data size approaches the limit AUTOAUTO Se aplica a todas las bases de datos.Enforced for all databases
QUERY_CAPTURE_MODEQUERY_CAPTURE_MODE Especifica si se realiza el seguimiento de todas las consultas o solo de un subconjunto de estas.Specifies whether all queries or only a subset of queries are tracked AUTOAUTO Se aplica a todas las bases de datos.Enforced for all databases
FLUSH_INTERVAL_SECONDSFLUSH_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.Specifies maximum period during which captured runtime statistics are kept in memory, before flushing to disk 900900 Se aplica a nuevas bases de datos.Enforced for new databases

Importante

Estos valores predeterminados se aplican automáticamente en la fase final de la activación del Almacén de consultas en todo Azure SQL DatabaseAzure SQL Database.These defaults are automatically applied in the final stage of Query Store activation in all Azure SQL DatabaseAzure SQL Database. Una vez habilitado, Azure SQL DatabaseAzure 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.After it's enabled, Azure SQL DatabaseAzure SQL Database won't change configuration values that are set by customers, unless they negatively impact primary workload or reliable operations of the Query Store.

Nota

El Almacén de consultas no se puede deshabilitar en la base de datos única de Azure SQL DatabaseAzure SQL Database ni Grupo elástico.Query Store cannot be disabled in Azure SQL DatabaseAzure SQL Database single database and Elastic Pool. Al ejecutar ALTER DATABASE [database] SET QUERY_STORE = OFF se devuelve la advertencia 'QUERY_STORE=OFF' is not supported in this version of SQL Server..Executing ALTER DATABASE [database] SET QUERY_STORE = OFF will return the warning '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.If you want to stay with your custom settings, use ALTER DATABASE with Query Store options to revert configuration to the previous state. Vea Procedimientos recomendados con el almacén de consultas para aprender a elegir los parámetros de configuración óptimos.Check out Best Practices with the Query Store in order to learn how to choose optimal configuration parameters.

Uso del Almacén de consultas con bases de datos de Grupo elásticoUse Query Store with Elastic Pool databases

Puede usar el Almacén de consultas en todas las bases de datos sin problemas, incluso en grupos densamente empaquetados.You can use Query Store in all databases without concerns, in even densely packed pools. Se han solucionado todas las incidencias relacionadas con el uso excesivo de los recursos que es posible que hayan surgido cuando el almacén de consultas estaba habilitado para el gran número de bases de datos en los grupos elásticos.All issues related to excessive resource usage that might have occurred when Query Store was enabled for the large number of databases in the elastic pools have been resolved.

Mantener el Almacén de consultas ajustado a la carga de trabajoKeep Query Store adjusted to your workload

Configure el Almacén de consultas en función de la carga de trabajo y los requisitos de solución de problemas de rendimiento.Configure Query Store based on your workload and performance troubleshooting requirements. 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.The default parameters are good enough to start, but you should monitor how Query Store behaves over time and adjust its configuration accordingly.

Propiedades del almacén de consultasQuery Store properties

A continuación se indican algunas instrucciones para establecer valores de parámetro:Here are guidelines to follow for setting parameter values:

Tamaño máximo (MB) : especifica el límite del espacio de datos que el almacén de consultas toma dentro de la base de datos.Max Size (MB): Specifies the limit for the data space that Query Store takes inside your database. Es el valor de configuración más importante que afecta directamente al modo de operación del almacén de consultas.This is the most important setting that directly affects the operation mode of Query Store.

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.While Query Store collects queries, execution plans, and statistics, its size in the database grows until this limit is reached. Cuando esto ocurre, el Almacén de consultas cambia automáticamente el modo de operación a solo lectura y deja de recopilar datos nuevos, lo que significa que el análisis de rendimiento ya no es preciso.When that happens, Query Store automatically changes the operation mode to read-only and stops collecting new data, which means that your performance analysis is no longer accurate.

El valor predeterminado en SQL Server 2016 (13.x)SQL Server 2016 (13.x) y SQL Server 2017 (14.x)SQL Server 2017 (14.x) es 100 MB.The default value in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x) is 100 MB. Es posible que este tamaño no sea suficiente si la carga de trabajo genera gran cantidad de planes y consultas diferentes, o bien si quiere conservar el historial de consultas durante un período de tiempo más largo.This size might not be sufficient if your workload generates a large number of different queries and plans or if you want to keep query history for a longer period of time. A partir de SQL Server 2019 (15.x)SQL Server 2019 (15.x), el valor predeterminado es de 1 GB.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the default value is 1 GB. Realice el seguimiento del uso de espacio actual y aumente el valor de Tamaño máximo (MB) para impedir que el almacén de consultas cambie al modo de solo lectura.Keep track of current space usage and increase the Max Size (MB) value to prevent Query Store from transitioning to read-only mode.

Importante

El límite Tamaño máximo (MB) no se aplica de forma estricta.The Max Size (MB) limit isn't strictly enforced. El tamaño de almacenamiento solo se comprueba cuando el almacén de consultas escribe datos en el disco.Storage size is checked only when Query Store writes data to disk. Este intervalo lo establece el valor de Intervalo de vaciado de datos (minutos) .This interval is set by the Data Flush Interval (Minutes) option. Si Almacén de consultas ha infringido el límite de tamaño máximo entre las comprobaciones de tamaño de almacenamiento, pasa al modo de solo lectura.If Query Store has breached the maximum size limit between storage size checks, it transitions to read-only mode. Si Modo de limpieza basada en tamaño está habilitado, también se desencadena el mecanismo de limpieza para aplicar el límite de tamaño máximo.If Size Based Cleanup Mode is enabled, the cleanup mechanism to enforce the maximum size limit is also triggered.

Utilice Management StudioManagement Studio o ejecute el siguiente script para obtener la información más reciente sobre el tamaño del Almacén de consultas:Use Management StudioManagement Studio or execute the following script to get the latest information about Query Store size:

USE [QueryStoreDB];
GO

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

En el script siguiente se establece un nuevo valor para Tamaño de máximo (MB) :The following script sets a new value for Max Size (MB):

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

Intervalo del vaciado de datos (minutos) : define la frecuencia para conservar en disco las estadísticas en tiempo de ejecución recopiladas.Data Flush Interval (Minutes): It defines the frequency to persist collected runtime statistics to disk. Se expresa en minutos en la interfaz gráfica de usuario (GUI), pero en Transact-SQLTransact-SQL se expresa en segundos.It's expressed in minutes in the graphical user interface (GUI), but in Transact-SQLTransact-SQL it's expressed in seconds. El valor predeterminado es 900 segundos, equivalente a 15 minutos en la interfaz gráfica de usuario.The default is 900 seconds, which is 15 minutes in the graphical user interface. 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.Consider using a higher value if your workload doesn't generate a large number of different queries and plans, or if you can withstand longer time to persist data before a database shutdown.

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.Using trace flag 7745 prevents Query Store data from being written to disk in case of a failover or shutdown command. Para obtener más información, vea la sección Uso de marcas de seguimiento en servidores críticos.For more information, see the Use trace flags on mission-critical servers section.

Use SQL Server Management StudioSQL Server Management Studio o Transact-SQLTransact-SQL para establecer otro valor para Intervalo de vaciado de datos:Use SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL to set a different value for Data Flush Interval:

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.Statistics Collection Interval: Defines the level of granularity for the collected runtime statistic, expressed in minutes. El valor predeterminado es 60 minutos.The default is 60 minutes. 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.Consider using a lower value if you require finer granularity or less time to detect and mitigate issues. Recuerde que el valor afecta directamente al tamaño de los datos del almacén de consultas.Keep in mind that the value directly affects the size of Query Store data. Use SQL Server Management StudioSQL Server Management Studio o Transact-SQLTransact-SQL para establecer otro valor para Intervalo de recopilación de estadísticas:Use SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL to set a different value for Statistics Collection Interval:

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.Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries, expressed in days. 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.By default, Query Store is configured to keep the data for 30 days, which might be unnecessarily long for your scenario.

Evite mantener datos históricos que no tenga pensado usar.Avoid keeping historical data that you don't plan to use. Este procedimiento reduce los cambios al estado de solo lectura.This practice reduces changes to read-only status. 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.The size of Query Store data and the time to detect and mitigate the issue will be more predictable. Use Management StudioManagement Studio o el siguiente script para configurar la directiva de limpieza basada en el tiempo:Use Management StudioManagement Studio or the following script to configure time-based cleanup policy:

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.Size Based Cleanup Mode: Specifies whether automatic data cleanup takes place when Query Store data size approaches the limit. 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.Activate size-based cleanup to make sure that Query Store always runs in read-write mode and collects the latest data. Tenga en cuenta que no hay ninguna garantía en las cargas de trabajo pesadas de que la limpieza de Almacén de consultas mantenga el tamaño de los datos en el límite.Note that there is no guarantee under heavy workloads that Query Store cleanup will consistently maintain the data size under the limit. Es posible que la limpieza automática de datos quede fuera y cambie (temporalmente) al modo de solo lectura.It is possible for the automatic data cleanup to fall behind and to switch (temporarily) into read-only mode.

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

Modo de captura de Almacén de consultas: especifica la directiva de captura de consultas para el almacén de consultas.Query Store Capture Mode: Specifies the query capture policy for Query Store.

  • Todos: Captura todas las consultas.All: Captures all queries. Es la opción predeterminada en SQL Server 2016 (13.x)SQL Server 2016 (13.x) y SQL Server 2017 (14.x)SQL Server 2017 (14.x).This option is the default in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)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.Auto: Infrequent queries and queries with insignificant compile and execution duration are ignored. Los umbrales para la duración del tiempo de ejecución, compilación y recuento de ejecuciones se determinan de forma interna.Thresholds for execution count, compile, and runtime duration are internally determined. A partir de SQL Server 2019 (15.x)SQL Server 2019 (15.x), esta es la opción predeterminada.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), this is the default option.
  • Ninguna: el almacén de consultas deja de capturar consultas nuevas.None: Query Store stops capturing new queries.
  • Personalizado: permite un control adicional y la capacidad de ajustar la directiva de recopilación de datos.Custom: Allows additional control and the capability to fine-tune the data collection policy. La nueva configuración personalizada define lo que sucede durante el umbral de tiempo de la directiva de captura interna.The new custom settings define what happens during the internal capture policy time threshold. 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.This is a time boundary during which the configurable conditions are evaluated and, if any are true, the query is eligible to be captured by Query Store.

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.Cursors, queries inside stored procedures, and natively compiled queries are always captured when Query Store Capture Mode is set to All, Auto, or Custom. Para capturar consultas compiladas de forma nativa, habilite la recopilación de estadísticas por consulta mediante sys.sp_xtp_control_query_exec_stats.To capture natively compiled queries, enable collection of per-query statistics by using sys.sp_xtp_control_query_exec_stats.

En el script siguiente se establece QUERY_CAPTURE_MODE en AUTO:The following script sets QUERY_CAPTURE_MODE to AUTO:

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

EjemplosExamples

En el ejemplo siguiente, se establece QUERY_CAPTURE_MODE en AUTO y se configuran otras opciones recomendadas en SQL Server 2016 (13.x)SQL Server 2016 (13.x):The following example sets QUERY_CAPTURE_MODE to AUTO and sets other recommended options in SQL Server 2016 (13.x)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)SQL Server 2017 (14.x) para incluir estadísticas de espera:The following example sets QUERY_CAPTURE_MODE to AUTO and sets other recommended options in SQL Server 2017 (14.x)SQL Server 2017 (14.x) to include wait statistics:

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 QUERY_CAPTURE_MODE en AUTO y se configuran otras opciones recomendadas en SQL Server 2019 (15.x)SQL Server 2019 (15.x). De manera opcional, se establece la directiva de captura CUSTOM con los valores predeterminados, en lugar del nuevo modo de captura AUTO predeterminado:The following example sets QUERY_CAPTURE_MODE to AUTO and sets other recommended options in SQL Server 2019 (15.x)SQL Server 2019 (15.x), and optionally sets the CUSTOM capture policy with its defaults, instead of the new default AUTO capture mode:

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
      )
    );

Inicio de la solución de problemas de rendimiento de consultasStart with query performance troubleshooting

El flujo de trabajo de solución de problemas con el almacén de consultas es sencillo, como se muestra en el diagrama siguiente:The troubleshooting workflow with Query Store is simple, as shown in the following diagram:

Solución de problemas del almacén de consultasQuery Store troubleshooting

Habilite el almacén de consultas mediante Management StudioManagement Studio como se ha descrito en la sección anterior, o bien ejecute la instrucción Transact-SQLTransact-SQL siguiente:Enable Query Store by using Management StudioManagement Studio, as described in the previous section, or execute the following Transact-SQLTransact-SQL statement:

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

El almacén de consultas tarda algún tiempo en recopilar el conjunto de datos que representa con precisión la carga de trabajo.It takes some time until Query Store collects the data set that accurately represents your workload. Normalmente, un día es suficiente incluso para cargas de trabajo muy complejas.Usually, one day is enough even for very complex workloads. Pero puede empezar a explorar los datos e identificar las consultas que requieran atención inmediatamente después de habilitar la característica.However, you can start exploring the data and identify queries that need your attention immediately after you enable the feature. Vaya a la subcarpeta Almacén de consultas del nodo de la base de datos en el Explorador de objetos de Management StudioManagement Studio para abrir las vistas de solución de problemas de escenarios concretos.Go to the Query Store subfolder under the database node in Object Explorer of Management StudioManagement Studio to open troubleshooting views for specific scenarios.

El Almacén de consultas deManagement StudioManagement Studio funciona con el conjunto de métricas de ejecución, cada una expresada como cualquiera de las siguientes funciones estadísticas:Management StudioManagement Studio Query Store views operate with the set of execution metrics, each expressed as any of the following statistic functions:

Versión de SQL ServerSQL ServerSQL ServerSQL Server version Métrica de ejecuciónExecution metric Función estadísticaStatistic function
SQL Server 2016 (13.x)SQL Server 2016 (13.x) Tiempo de CPU, Duración, Recuento de ejecuciones, Lecturas lógicas, Escrituras lógicas, Consumo de memoria, Lecturas físicas, Tiempo de CLR, Grado de paralelismo (DOP) y Recuento de filasCPU time, Duration, Execution count, Logical reads, Logical writes, Memory consumption, Physical reads, CLR time, Degree of parallelism (DOP), and Row count Promedio, máximo, mínimo, desviación estándar y totalAverage, Maximum, Minimum, Standard Deviation, Total
SQL Server 2017 (14.x)SQL Server 2017 (14.x) Tiempo de CPU, Duración, Recuento de ejecuciones, Lecturas lógicas, Escrituras lógicas, Consumo de memoria, Lecturas físicas, Tiempo de CLR, Grado de paralelismo, Recuento de filas, Memoria de registro, Memoria de TempDB y Tiempos de esperaCPU time, Duration, Execution count, Logical reads, Logical writes, Memory consumption, Physical reads, CLR time, Degree of parallelism, Row count, Log memory, TempDB memory, and Wait times Promedio, máximo, mínimo, desviación estándar y totalAverage, Maximum, Minimum, Standard Deviation, Total

En el gráfico siguiente se muestra cómo localizar vistas del Almacén de consultas:The following graphic shows how to locate Query Store views:

Vistas del almacén de consultasQuery Store views

En la siguiente tabla se explica cuándo usar cada una de las vistas del Almacén de consultas:The following table explains when to use each of the Query Store views:

Vista de SQL Server Management StudioSQL Server Management Studio view EscenarioScenario
Consultas devueltasRegressed Queries Localice consultas para las que las métricas de ejecución se han devuelto recientemente (es decir, han cambiado a peor).Pinpoint queries for which execution metrics have recently regressed (for example, changed to worse).
Use esta vista para poner en correlación los problemas de rendimiento observados en la aplicación con las consultas reales que se deben corregir o mejorar.Use this view to correlate observed performance problems in your application with the actual queries that need to be fixed or improved.
Consumo total de recursosOverall Resource Consumption Analice el consumo total de recursos para la base de datos para cualquiera de las métricas de ejecución.Analyze the total resource consumption for the database for any of the execution metrics.
Use esta vista para identificar patrones de recursos (cargas de trabajo por el día frente a cargas de trabajo por la noche) y optimizar el consumo total para la base de datos.Use this view to identify resource patterns (daily vs. nightly workloads) and optimize overall consumption for your database.
Consultas que consumen más recursosTop Resource Consuming Queries Elija una métrica de ejecución de interés e identifique las consultas que tenían los valores más extremos para un intervalo de tiempo proporcionado.Choose an execution metric of interest, and identify queries that had the most extreme values for a provided time interval.
Use esta vista para centrar la atención en las consultas más importantes que tienen el mayor impacto en el consumo de recursos de base de datos.Use this view to focus your attention on the most relevant queries that have the biggest impact to database resource consumption.
Consultas con planes forzadosQueries With Forced Plans Enumera los planes forzados anteriormente mediante el Almacén de consultas.Lists previously forced plans using Query Store.
Use esta vista para obtener acceso rápidamente a todos los planes forzados actualmente.Use this view to quickly access all currently forced plans.
Consultas con gran variaciónQueries With High Variation Analice consultas con una gran variación de ejecución en lo referente a cualquiera de las dimensiones disponibles, como la duración, el tiempo de CPU, la E/S y el uso de memoria, en el intervalo de tiempo deseado.Analyze queries with high-execution variation as it relates to any of the available dimensions, such as Duration, CPU time, IO, and Memory usage, in the desired time interval.
Use esta vista para identificar consultas con un rendimiento muy variable que puedan afectar a la experiencia del usuario en las aplicaciones.Use this view to identify queries with widely variant performance that can be affecting user experience across your applications.
Estadísticas de espera de consultaQuery Wait Statistics Analice las categorías de espera más activas de una base de datos y qué consultas contribuyen más a la categoría de espera seleccionada.Analyze wait categories that are most active in a database and which queries contribute most to the selected wait category.
Use esta vista para analizar las estadísticas de espera e identificar las consultas que puedan afectar a la experiencia del usuario en las aplicaciones.Use this view to analyze wait statistics and identify queries that might be affecting user experience across your applications.

Se aplica a: A partir de SQL Server Management StudioSQL Server Management Studio v18.0 y SQL Server 2017 (14.x)SQL Server 2017 (14.x).Applies to: Starting with SQL Server Management StudioSQL Server Management Studio v18.0 and SQL Server 2017 (14.x)SQL Server 2017 (14.x).
Consultas con seguimientoTracked Queries Realice un seguimiento de la ejecución de las consultas más importantes en tiempo real.Track the execution of the most important queries in real time. Normalmente, esta vista se utiliza cuando tiene consultas con planes forzados y desea asegurarse de que el rendimiento de las mismas es estable.Typically, you use this view when you have queries with forced plans and you want to make sure that query performance is stable.

Sugerencia

Para obtener una descripción detallada sobre cómo usar Management StudioManagement Studio para identificar las consultas que consumen más recursos y corregir aquellas devueltas debido al cambio de una opción de plan, vea los blogs de Azure sobre el almacén de consultas.For a detailed description of how to use Management StudioManagement Studio to identify the top resource-consuming queries and fix those that regressed due to the change of a plan choice, see Query Store Azure Blogs.

Cuando identifique una consulta con un rendimiento deficiente, la acción depende de la naturaleza del problema.When you identify a query with suboptimal performance, your action depends on the nature of the problem.

  • Si la consulta se ha ejecutado con varios planes y el último plan es mucho peor que el anterior, puede usar el mecanismo que fuerza el plan.If the query was executed with multiple plans and the last plan is significantly worse than the previous plan, you can use the plan forcing mechanism to force it. SQL ServerSQL Server intenta forzar el plan en el optimizador.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.

    Forzar plan del almacén de consultasQuery Store force plan

    Nota

    Es posible que en el gráfico anterior se presenten otras formas para planes de consulta específicos, con los significados siguientes para cada estado posible:The previous graphic might feature different shapes for specific query plans, with the following meanings for each possible status:

    FormaShape SignificadoMeaning
    CircleCircle Consulta completada, lo que significa que una ejecución normal ha finalizado correctamente.Query completed, which means that a regular execution successfully finished.
    SquareSquare Cancelada, lo que significa una ejecución iniciada por el cliente anulada.Cancelled, which means that a client-initiated aborted execution.
    TriangleTriangle Con error, lo que significa que una excepción ha anulado la ejecución.Failed, which means that an exception aborted execution.

    Además, el tamaño de la forma refleja el recuento de ejecución de consultas dentro del intervalo de tiempo especificado.Also, the size of the shape reflects the query execution count within the specified time interval. El tamaño aumenta con un número mayor de ejecuciones.The size increases with a higher number of executions.

  • Se podría concluir que a la consulta le falta un índice para que la ejecución sea óptima.You might conclude that your query is missing an index for optimal execution. Esta información aparece en el plan de ejecución de la consulta.This information is surfaced within the query execution plan. Cree el índice que falta y compruebe el rendimiento de la consulta mediante el almacén de consultas.Create the missing index, and check the query performance by usingQuery Store.

    Mostrar plan del almacén de consultasQuery Store show plan

Si ejecuta la carga de trabajo en SQL DatabaseSQL Database, suscríbase al Asesor de índices de SQL DatabaseSQL Database para recibir automáticamente las recomendaciones de índices.If you run your workload on SQL DatabaseSQL Database, sign up for SQL DatabaseSQL Database Index Advisor to automatically receive index recommendations.

  • En algunos casos, podría exigir la recompilación estadística si ve que la diferencia entre el número de filas estimado y el real en el plan de ejecución es significativa.In some cases, you might enforce statistic recompilation if you see that the difference between the estimated and the actual number of rows in the execution plan is significant.
  • Vuelva a escribir las consultas con problemas, por ejemplo, para aprovechar las ventajas de la parametrización de la consulta o implementar lógica más óptima.Rewrite problematic queries, for example, to take advantage of query parameterization or to implement more optimal logic.

Comprobación de que el almacén de consultas recopila datos de consulta de forma continuaVerify that Query Store collects query data continuously

El almacén de consultas puede cambiar el modo de operación automáticamente.Query Store can silently change the operation mode. Supervise periódicamente el estado del almacén de consulta para asegurarse de que funciona y tomar medidas para evitar errores debido a causas evitables.Regularly monitor the state of Query Store to ensure that Query Store is operating, and to take action to avoid failures due to preventable causes. Ejecute la siguiente consulta para determinar el modo de operación y ver los parámetros más importantes:Execute the following query to determine the operation mode and view the most relevant parameters:

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

La diferencia entre actual_state_desc y desired_state_desc indica que se ha producido un cambio de modo de operación automáticamente.The difference between the actual_state_desc and desired_state_desc indicates that a change of the operation mode occurred automatically. El cambio más frecuente es que el almacén de consultas cambie al modo de solo lectura automáticamente.The most common change is for Query Store to silently switch to read-only mode. En circunstancias extremadamente raras, el almacén de consultas puede terminar en el estado ERROR debido a errores internos.In extremely rare circumstances, Query Store can end up in the ERROR state because of internal errors.

Cuando el estado real es de solo lectura, use la columna readonly_reason para determinar la causa raíz.When the actual state is read-only, use the readonly_reason column to determine the root cause. Normalmente, encontrará que el almacén de consultas ha cambiado al modo de solo lectura porque se ha superado la cuota de tamaño.Typically, you find that Query Store transitioned to read-only mode because the size quota was exceeded. En ese caso, readonly_reason se establece en 65536.In that case, the readonly_reason is set to 65536. Por otros motivos, vea sys.database_query_store_options (Transact-SQL).For other reasons, see sys.database_query_store_options (Transact-SQL).

Tenga en cuenta los pasos siguientes para cambiar el Almacén de consultas al modo de lectura y escritura y activar la recopilación de datos:Consider the following steps to switch Query Store to read-write mode and activate data collection:

  • Aumente el tamaño de almacenamiento máximo mediante la opción MAX_STORAGE_SIZE_MB de ALTER DATABASE.Increase the maximum storage size by using the MAX_STORAGE_SIZE_MB option of ALTER DATABASE.

  • Limpie los datos del Almacén de consultas mediante la siguiente instrucción:Clean up Query Store data by using the following statement:

    ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;
    

Puede aplicar uno de estos pasos o los dos si ejecuta la instrucción siguiente que vuelve a cambiar de forma explícita el modo de operación a lectura y escritura:You can apply one or both of these steps by executing the following statement that explicitly changes the operation mode back to read-write:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Siga estos pasos para ser proactivo:Take the following steps to be proactive:

  • Puede evitar cambios automáticos de modo de operación aplicando procedimientos recomendados.You can prevent silent changes of operation mode by applying best practices. Asegúrese de que el tamaño del almacén de consultas es siempre menor que el valor máximo permitido para reducir considerablemente la posibilidad de cambiar al modo de solo lectura.Ensure that Query Store size is always below the maximally allowed value to dramatically reduce a chance of transitioning to read-only mode. Active la directiva basada en el tamaño como se describe en la sección Configuración del almacén de consultas para que el almacén de consultas limpie automáticamente los datos cuando el tamaño se aproxime al límite.Activate size-based policy as described in the Configure Query Store section so that Query Store automatically cleans data when the size approaches the limit.
  • Para asegurarse de que se retienen los datos más recientes, configure la directiva basada en tiempo para quitar información obsoleta frecuentemente.To make sure that most recent data is retained, configure time-based policy to remove stale information regularly.
  • Por último, considere la posibilidad de establecer Modo de captura de Almacén de consultas en Automático ya que filtra las consultas que suelen ser menos relevantes para la carga de trabajo.Finally, consider setting Query Store Capture Mode to Auto because it filters out queries that are usually less relevant for your workload.

Estado ERRORERROR state

Para recuperar el almacén de consultas, intente establecer de forma explícita el modo de lectura y escritura, y vuelva a comprobar el estado real.To recover Query Store, try explicitly setting the read-write mode and check the actual state again.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Si el problema continúa, significa que los datos del almacén de consultas siguen dañados en el disco.If the problem persists, it indicates that corruption of Query Store data is persisted on the disk.

A partir de SQL Server 2017 (14.x)SQL Server 2017 (14.x), el Almacén de consultas se puede recuperar si se ejecuta el procedimiento sp_query_store_consistency_check almacenado en la base de datos afectada.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x), Query Store can be recovered by executing the sp_query_store_consistency_check stored procedure within the affected database. El almacén de consultas se debe deshabilitar antes de intentar la operación de recuperación.Query Store must be disabled before you attempt the recovery operation. Para SQL Server 2016 (13.x)SQL Server 2016 (13.x), tendrá que borrar los datos del almacén de consultas, como se muestra a continuación.For SQL Server 2016 (13.x)SQL Server 2016 (13.x), you need to clear the data from Query Store as shown.

Si la recuperación no se ha realizado correctamente, puede intentar borrar el almacén de consultas antes de establecer el modo de lectura y escritura.If the recovery was unsuccessful, you can try clearing Query Store before you set the read-write mode.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Establecimiento del modo óptimo de captura del almacén de consultasSet the optimal Query Store Capture Mode

Mantenga los datos más relevantes en el Almacén de consultas.Keep the most relevant data in Query Store. En la tabla siguiente se describen los escenarios típicos para cada modo de captura del almacén de consultas:The following table describes typical scenarios for each Query Store Capture Mode:

Modo de captura del almacén de consultasQuery Store Capture Mode EscenarioScenario
TodoAll 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.Analyze your workload thoroughly in terms of all queries' shapes and their execution frequencies and other statistics.

Identifique nuevas consultas en la carga de trabajo.Identify new queries in your workload.

Detecte si las consultas ad-hoc se usan para identificar oportunidades de parametrización automática o manual.Detect if ad-hoc queries are used to identify opportunities for user or auto parameterization.

Nota: Este es el modo de captura predeterminado en SQL Server 2016 (13.x)SQL Server 2016 (13.x) y SQL Server 2017 (14.x)SQL Server 2017 (14.x).Note: This is the default capture mode in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x).
AutomáticoAuto Centre su atención en las consultas pertinentes y accionables.Focus your attention on relevant and actionable queries. Un ejemplo son las que se ejecutan con regularidad o las que tienen un consumo significativo de recursos.An example is those queries that execute regularly or that have significant resource consumption.

Nota: A partir de SQL Server 2019 (15.x)SQL Server 2019 (15.x), este es el modo de captura predeterminado.Note: Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), this is the default capture mode.
NoneNone 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.You've already captured the query set that you want to monitor in runtime and you want to eliminate the distractions that other queries might introduce.

El modo Ninguno es adecuado para entornos de pruebas y evaluación comparativa.None is suitable for testing and benchmarking environments.

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.None is also appropriate for software vendors who ship Query Store configuration configured to monitor their application workload.

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.None should be used with caution because you might miss the opportunity to track and optimize important new queries. Evite el uso del modo Ninguno a menos que tenga un escenario específico que lo requiera.Avoid using None unless you have a specific scenario that requires it.
PersonalizadaCustom SQL Server 2019 (15.x)SQL Server 2019 (15.x) introduce un modo de captura Personalizado en el comando ALTER DATABASE SET QUERY_STORE.introduces a Custom capture mode under the ALTER DATABASE SET QUERY_STORE command. Cuando se habilita, una nueva configuración de la directiva de captura del almacén de consultas incluye más configuraciones del almacén de consultas para ajustar la recopilación de datos en un servidor específico.When enabled, additional Query Store configurations are available under a new Query Store capture policy setting to fine-tune data collection in a specific server.

La nueva configuración personalizada define lo que sucede durante el umbral de tiempo de la directiva de captura interna.The new custom settings define what happens during the internal capture policy time threshold. 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.This is a time boundary during which the configurable conditions are evaluated and, if any are true, the query is eligible to be captured by Query Store. Para obtener más información, vea ALTER DATABASE SET Options (Transact-SQL).For more information, see ALTER DATABASE SET Options (Transact-SQL).

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.Cursors, queries inside stored procedures, and natively compiled queries are always captured when Query Store Capture Mode is set to All, Auto, or Custom. Para capturar consultas compiladas de forma nativa, habilite la recopilación de estadísticas por consulta mediante sys.sp_xtp_control_query_exec_stats.To capture natively compiled queries, enable collection of per-query statistics by using sys.sp_xtp_control_query_exec_stats.

Conservación de los datos más relevantes en el Almacén de consultasKeep the most relevant data in Query Store

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.Configure Query Store to contain only the relevant data so that it runs continuously and provides a great troubleshooting experience with a minimal impact on your regular workload. La tabla siguiente proporciona prácticas recomendadas:The following table provides best practices:

Procedimiento recomendadoBest practice ConfiguraciónSetting
Limitar los datos históricos retenidos.Limit retained historical data. Configurar la directiva basada en tiempo para activar la limpieza automática.Configure time-based policy to activate autocleanup.
Filtrar las consultas no pertinentes.Filter out nonrelevant queries. Configurar Modo de captura de Almacén de consultas en Automático.Configure Query Store Capture Mode to Auto.
Eliminar las consultas menos relevantes cuando se alcanza el tamaño máximo.Delete less relevant queries when the maximum size is reached. Activar la directiva de limpieza basada en el tamaño.Activate size-based cleanup policy.

Evitar el uso de consultas sin parámetrosAvoid using non-parameterized queries

El uso de consultas sin parámetros cuando no es necesario no es un procedimiento recomendado.Using non-parameterized queries when that isn't necessary isn't a best practice. Un ejemplo es el caso del análisis ad hoc.An example is in the case of ad-hoc analysis. Los planes en caché no se pueden reutilizar, lo que obliga al optimizador de consultas a compilar consultas para cada texto de consulta única.Cached plans can't be reused, which forces Query Optimizer to compile queries for every unique query text. Para más información, vea Instrucciones para usar la parametrización forzada.For more information, see Guidelines for using forced parameterization.

Además, el almacén de consultas puede superar rápidamente la cuota de tamaño debido a la posibilidad de un gran número de textos de consulta diferentes y, por tanto, un gran número de planes de ejecución distintos con forma similar.Also, Query Store can rapidly exceed the size quota because of a potentially large number of different query texts and consequently a large number of different execution plans with similar shape. Como resultado, el rendimiento de la carga de trabajo será deficiente y el almacén de consultas podría cambiar al modo de solo lectura o eliminar datos constantemente en un intente de mantenerse al día con las consultas entrantes.As a result, performance of your workload is suboptimal, and Query Store might switch to read-only mode or constantly delete data to try to keep up with the incoming queries.

Considere las opciones siguientes:Consider the following options:

  • Parametrice las consultas cuando proceda.Parameterize queries where applicable. Por ejemplo, encapsule las consultas dentro de un procedimiento almacenado o sp_executesql.For example, wrap queries inside a stored procedure or sp_executesql. Para más información, vea Parámetros y reutilización de un plan de ejecución.For more information, see Parameters and execution plan reuse.
  • Use la opción Optimizar para cargas de trabajo ad hoc si la carga de trabajo contiene muchos lotes ad hoc de un solo uso con distintos planes de consulta.Use the optimize for ad hoc workloads option if your workload contains many single-use ad-hoc batches with different query plans.
    • Compare el número de valores query_hash distintos con el número total de entradas en sys.query_store_query.Compare the number of distinct query_hash values with the total number of entries in sys.query_store_query. Si la relación es cercana a 1, la carga de trabajo ad hoc genera consultas diferentes.If the ratio is close to 1, your ad-hoc workload generates different queries.
  • Aplique la parametrización forzada para la base de datos o para un subconjunto de consultas si el número de planes de consulta diferentes no es grande.Apply forced parameterization for the database or for a subset of queries if the number of different query plans isn't large.
    • Use una guía de plan para forzar la parametrización solo para la consulta seleccionada.Use a plan guide to force parameterization only for the selected query.
    • Configure la parametrización forzada mediante el comando opción de base de datos de parametrización si hay un pequeño número de planes de consulta diferentes en la carga de trabajo.Configure forced parameterization by using the parameterization database option command, if there are a small number of different query plans in your workload. Un ejemplo es cuando la relación entre el recuento de valores query_hash distintos y el número total de entradas de sys.query_store_query es mucho menor que 1.An example is when the ratio between the count of distinct query_hash and the total number of entries in sys.query_store_query is much less than 1.
  • Establezca QUERY_CAPTURE_MODE en AUTO para filtrar de forma automática las consultas ad hoc con un consumo de recursos reducido.Set QUERY_CAPTURE_MODE to AUTO to automatically filter out ad-hoc queries with small resource consumption.

Evitar un patrón DROP y CREATE para objetos contenedoresAvoid a DROP and CREATE pattern for containing objects

El almacén de consultas asocia la entrada de consulta a un objeto contenedor, como un procedimiento almacenado, una función y un desencadenador.Query Store associates query entry with a containing object, such as stored procedure, function, and trigger. Cuando se vuelve a crear un objeto contenedor, se genera una nueva entrada de consulta para el mismo texto de consulta.When you re-create a containing object, a new query entry is generated for the same query text. Esto impide realizar el seguimiento de las estadísticas de rendimiento para esa consulta a lo largo del tiempo y usar un mecanismo para forzar el plan.This prevents you from tracking performance statistics for that query over time and using a plan forcing mechanism. Para evitar esta situación, use el proceso ALTER <object> para cambiar la definición de un objeto contenedor siempre que sea posible.To avoid this situation, use the ALTER <object> process to change a containing object definition whenever it's possible.

Comprobación periódica del estado de los planes forzadosCheck the status of forced plans regularly

Forzar el plan es un mecanismo conveniente para corregir el rendimiento de las consultas críticas y hacer que sean más predecibles.Plan forcing is a convenient mechanism to fix performance for the critical queries and make them more predictable. Como sucede con las sugerencias de plan y las guías de plan, forzar un plan no es una garantía de que se va a usar en ejecuciones futuras.As with plan hints and plan guides, forcing a plan isn't a guarantee that it will be used in future executions. Normalmente, cuando se cambia el esquema de base de datos de forma que se modifican o se quitan objetos a los que hace referencia el plan de ejecución, al forzar el plan se empiezan a generar errores.Typically, when database schema changes in a way that objects referenced by the execution plan are altered or dropped, plan forcing starts failing. En ese caso, SQL ServerSQL Server vuelve a la recompilación de consultas mientras el motivo real del error de la operación de forzado aparece en sys.query_store_plan.In that case, SQL ServerSQL Server falls back to query recompilation while the actual forcing failure reason is surfaced in sys.query_store_plan. La siguiente consulta devuelve información sobre planes forzados.The following query returns information about forced plans:

USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

Para obtener una lista completa de los motivos, vea sys.query_store_plan.For a full list of reasons, see sys.query_store_plan. También puede usar el XEvent query_store_plan_forcing_failed para realizar un seguimiento de los errores forzados del plan y solucionarlos.You can also use the query_store_plan_forcing_failed XEvent to track and troubleshoot plan forcing failures.

Evitar el cambio de nombre de las bases de datos para las consultas con planes forzadosAvoid renaming databases for queries with forced plans

Los planes de ejecución hacen referencia a objetos mediante nombres de tres partes como database.schema.object.Execution plans reference objects by using three-part names like database.schema.object.

Si cambia el nombre de una base de datos, al forzar el plan se produce un error que provoca la recompilación en todas las ejecuciones de consulta posteriores.If you rename a database, plan forcing fails, which causes recompilation in all subsequent query executions.

Uso de Almacén de consultas en servidores críticosUsing Query Store in mission-critical servers

Las marcas de seguimiento globales 7745 y 7752 se pueden usar para mejorar la disponibilidad de las bases de datos mediante el almacén de consultas.The global trace flags 7745 and 7752 can be used to improve availability of databases by using Query Store. Para más información, vea Marcas de seguimiento.For more information, see Trace flags.

  • La marca de seguimiento 7745 evita el comportamiento predeterminado en el que el almacén de consultas escribe datos en el disco antes de que SQL ServerSQL Server se pueda apagar.Trace flag 7745 prevents the default behavior where Query Store writes data to disk before SQL ServerSQL Server can be shut down. Esto significa que los datos del almacén de consultas que se han recopilado, pero que todavía no han almacenado en el disco, se perderán, hasta la ventana de tiempo definida con DATA_FLUSH_INTERVAL_SECONDS.This means that Query Store data that has been collected but not yet persisted to disk will be lost, up to the time window defined with DATA_FLUSH_INTERVAL_SECONDS.
  • La marca de seguimiento 7752 permite la carga asincrónica del Almacén de consultas.Trace flag 7752 enables asynchronous load of Query Store. Esto permite que una base de datos vuelva a estar en línea y que las consultas se ejecuten antes de que el almacén de consultas se haya recuperado completamente.This allows a database to become online and queries to be executed before Query Store has been fully recovered. El comportamiento predeterminado consiste en realizar la carga sincrónica del Almacén de consultas.The default behavior is to do a synchronous load of Query Store. Este comportamiento impide que se ejecuten las consultas antes de que el almacén de consultas se haya recuperado, pero también impide que se pierdan consultas en la colección de datos.The default behavior prevents queries from executing before Query Store has been recovered but also prevents any queries from being missed in the data collection.

Nota

A partir de SQL Server 2019 (15.x)SQL Server 2019 (15.x), este comportamiento se controla mediante el motor, y la marca de seguimiento 7752 no tiene ningún efecto.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), this behavior is controlled by the engine, and trace flag 7752 has no effect.

Importante

Si va a usar el Almacén de consultas para resultados de la carga de trabajo Just-In-Time en SQL Server 2016 (13.x)SQL Server 2016 (13.x), prevea la instalación de las mejoras de escalabilidad de rendimiento descritas en SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU2 (KB 4340759) lo antes posible.If you're 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 improvements in SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU2 (KB 4340759) as soon as possible. Sin estas mejoras, cuando la base de datos está sometida a cargas de trabajo intensas, puede producirse la contención de bloqueo por subproceso y el rendimiento del servidor puede resultar lento.Without these improvements, when the database is under heavy workloads, spinlock contention may occur and server performance may become slow. En concreto, puede ver una contención intensa en el bloqueo por subproceso QUERY_STORE_ASYNC_PERSIST o SPL_QUERY_STORE_STATS_COOKIE_CACHE.In particular, you may see heavy contention on the QUERY_STORE_ASYNC_PERSIST spinlock or SPL_QUERY_STORE_STATS_COOKIE_CACHE spinlock. Después de aplicar esta mejora, el Almacén de consultas ya no producirá la contención de bloqueo por subproceso.After this improvement is applied, Query Store will no longer cause spinlock contention.

Importante

Si va a usar el Almacén de consultas para los resultados de la carga de trabajo Just-In-Time en SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) a través de SQL Server 2017 (14.x)SQL Server 2017 (14.x)), prevea la instalación de la mejora de escalabilidad de rendimiento descrita en SP2 CU15 de SQL Server 2016 (13.x)SQL Server 2016 (13.x), CU22 de SQL Server 2017 (14.x)SQL Server 2017 (14.x) y CU8 de SQL Server 2019 (15.x)SQL Server 2019 (15.x) lo antes posible.If you're using Query Store for just-in-time workload insights in SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017 (14.x)SQL Server 2017 (14.x)), plan to install the performance scalability improvement in SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU15, SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU22, and SQL Server 2019 (15.x)SQL Server 2019 (15.x) CU8 as soon as possible. Sin esta mejora, cuando la base de datos se encuentra sometida a cargas de trabajo ad hoc intensas, el Almacén de consultas puede usar una gran cantidad de memoria y el rendimiento del servidor puede ser lento.Without this improvement, when the database is under heavy ad-hoc workloads, the Query Store may use a large amount of memory and server performance may become slow. Después de aplicar esta mejora, el Almacén de consultas impone límites internos a la cantidad de memoria que pueden usar sus distintos componentes y puede cambiar automáticamente el modo de operación a solo lectura hasta que se devuelva suficiente memoria a Motor de base de datosDatabase Engine.After this improvement is applied, Query Store imposes internal limits to the amount of memory its various components can use, and can automatically change the operation mode to read-only until enough memory has been returned to the Motor de base de datosDatabase Engine. Tenga en cuenta que los límites de memoria interna del Almacén de consultas no están documentados porque están sujetos a cambios.Note that Query Store internal memory limits are not documented because they are subject to change.

Uso de Almacén de consultas en la replicación geográfica activa de Azure SQL DatabaseUsing Query Store in Azure SQL Database active geo-replication

Almacén de consultas en una réplica de replicación geográfica activa secundaria de Azure SQL Database será una copia de solo lectura de la actividad en la réplica principal.Query Store on a secondary active geo-replica of Azure SQL Database will be a read-only copy of the activity on the primary replica.

Evite los niveles no coincidentes de las instancias de Azure SQL Database que participan en la replicación geográfica.Avoid mismatched tiers of Azure SQL Databases participating in geo-replication. Una base de datos secundaria debe tener el mismo tamaño de proceso que la base de datos principal o similar, y debe estar en el mismo nivel de servicio.A secondary database should be at or near the same compute size of the primary database, and in the same service tier of the primary database. Busque el tipo de espera HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO en sys.dm_db_wait_stats que indica la limitación de velocidad del registro de transacciones en la réplica principal debido a un retraso secundario.Look for the HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO wait type in sys.dm_db_wait_stats which indicates transaction log rate throttling on the primary replica due to secondary lag.

Para obtener más información sobre la estimación y configuración del tamaño de la base de datos de Azure SQL secundaria de replicación geográfica activa, vea Configuración de la base de datos secundaria.For more on estimating and configuring the size of the secondary Azure SQL database of active geo-replication, see Configuring secondary database.

Consulte tambiénSee also