Optimización del plan de confidencialidad de parámetros

Se aplica a: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance Base de datos de Azure SQL de Microsoft Fabric

La optimización del plan confidencial a parámetros (PSP) forma parte de la familia de funciones de procesamiento inteligente de consultas. Aborda el escenario en el que un único plan almacenado en caché para una consulta con parámetros no es óptimo para todos los posibles valores de parámetros entrantes. Este es el caso de las distribuciones de datos no uniformes. Para obtener más información, consulte Confidencialidad de parámetros y Parámetros y reutilización de un plan de ejecución.

Para obtener más información sobre las soluciones existentes para este problema, consulte:

• Investigación y resolución de problemas de sensibilidad de parámetros
• Parámetros y reutilización de un plan de ejecución
• Consultas que tienen problemas con el plan de distinción de parámetros (PSP)

La optimización de PSP habilita automáticamente varios planes activos almacenados en caché para una sola instrucción parametrizada. Los planes de ejecución almacenados en caché se adaptan a distintos tamaños de datos en función de los valores de los parámetros de ejecución proporcionados por el cliente.

Comprender la parametrización

En el motor de base de datos SQL Server, el uso de parámetros o marcadores de parámetros en las instrucciones Transact-SQL (T-SQL) aumenta la capacidad del motor relacional para hacer coincidir las nuevas instrucciones T-SQL con los planes de ejecución existentes previamente compilados y promover la reutilización del plan. Para obtener más información, consulte Parametrización simple.

También puede anular el comportamiento de parametrización simple predeterminado de SQL Server especificando que todas las instrucciones SELECT, INSERT, UPDATE y DELETE en una base de datos estén parametrizadas, sujeto a ciertas limitaciones. Para obtener más información, vea Parametrización forzada.

Implementación de optimización de PSP

Durante la compilación inicial, los histogramas estadísticos de columnas identifican las distribuciones no uniformes y evalúan los predicados parametrizados de mayor riesgo, hasta tres de todos los predicados disponibles. En otras palabras, si varios predicados dentro de la misma consulta cumplen los criterios, la optimización del plan PSP elige los tres primeros. La característica de optimización del PSP limita el número de predicados que se evalúan, con el fin de evitar el sobredimensionamiento de la memoria caché del plan y el Almacén de consultas (si está habilitado) con demasiados planes.

Para los planes elegibles, la compilación inicial produce un plan de dispatcher que contiene la lógica de optimización PSP denominada expresión de dispatcher. Un plan de distribuidor se asigna a las variantes de consulta en función de los predicados de valores de límite del intervalo de cardinalidad.

Terminología

Expresión dispatcher

Evalúa la cardinalidad de los predicados en función de los valores de los parámetros en tiempo de ejecución y enruta la ejecución a diferentes variantes de consulta.

Plan dispatcher

plan que contiene la expresión dispatcher y que se almacena en caché para la consulta original. El plan del dispatcher es esencialmente una colección de los predicados que fueron seleccionados por la función, con algunos detalles extra. Para cada predicado seleccionado, algunos de los detalles que se incluyen en el plan del dispatcher son los valores límite alto y bajo. Estos valores se usan para dividir los valores de los parámetros en diferentes cubos o rangos. El plan del distribuidor también contiene las estadísticas que se usaron para calcular los valores de los límites.

Variante de consulta

Cuando un plan de dispatcher evalúa la cardinalidad de los predicados en función de los valores de los parámetros en tiempo de ejecución, agrupa dichos valores y genera consultas secundarias independientes para compilar y ejecutar. Estas consultas secundarias se denominan variantes de consulta. Las variantes de consulta tienen sus propios planes en la memoria caché del plan y el Almacén de consultas.

Intervalo de cardinalidad de predicado

En tiempo de ejecución, la cardinalidad de cada predicado se evalúa en función de los valores de los parámetros en tiempo de ejecución. El dispatcher agrupa los valores de cardinalidad en tres rangos de cardinalidad de predicado en tiempo de compilación. Por ejemplo, la característica de optimización del PSP puede crear tres intervalos que representarían una cardinalidad baja, media y alta, como se muestra en el siguiente diagrama.

En otras palabras, cuando se compila inicialmente una consulta con parámetros, la característica de optimización del PSP genera un plan de shell conocido como plan del distribuidor. La expresión dispatcher tiene la lógica que agrupa las consultas en variantes de consulta basadas en los valores de los parámetros en tiempo de ejecución. Cuando se inicia la ejecución real, el distribuidor realiza dos pasos:

• el dispatcher evalúa su expresión dispatcher para el conjunto de parámetros dado con el fin de calcular el rango de cardinalidad.

• el dispatcher asigna estos rangos a variantes de consulta específicas y compila y ejecuta las variantes. Al tener múltiples variantes de consulta, la característica de optimización del PSP consigue tener varios planes para una sola consulta.

Los límites del intervalo de cardinalidad pueden verse en el plan de presentación de XML de un plan de envío:

<Dispatcher>
  <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1000000">
    <StatisticsInfo Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Statistics="[NCI_Property_AgentId]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-09-07T15:32:16.89" />
    <Predicate>
      <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="@AgentId" />
            </Identifier>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Predicate>
  </ParameterSensitivePredicate>
</Dispatcher>

Se adjunta una sugerencia de optimización PSP generada a la instrucción SQL en el plan de presentación de XML de una variante de consulta. La sugerencia no se puede usar directamente y no se analiza si se agrega manualmente. La sugerencia contiene los siguientes elementos:

option ( PLAN PER VALUE ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [databaseName].[schemaName].[tableName].[columnName] = @paramName, lowBoundaryValue, highBoundaryValue ) ) )

• ObjectID proviene del módulo (es decir, procedimiento almacenado, función, dispatcher) del que forma parte la instrucción actual; con la suposición de que la instrucción se ha generado a partir de un módulo. Si la instrucción es el resultado de SQL dinámico o ad hoc (es decir, sp_executesql) el elemento ObjectID es igual a 0.
• QueryVariantID equivale aproximadamente a la combinación de rangos para todos los predicados que la optimización PSP ha seleccionado. Por ejemplo, si una consulta tiene dos predicados elegibles para PSP y cada predicado tiene tres rangos, habrá nueve rangos de variantes de consulta numerados del 1 al 9.
• El intervalo de predicado es la información del intervalo de cardinalidad del predicado generada a partir de la expresión dispatcher.

Y, dentro del plan de presentación de XML de una variante de consulta (dentro del elemento Dispatcher):

<Batch>
  <Statements>
    <StmtSimple StatementText="SELECT PropertyId,&#xD;&#xA;       AgentId,&#xD;&#xA;       MLSLinkId,&#xD;&#xA;       ListingPrice,&#xD;&#xA;       ZipCode,&#xD;&#xA;       Bedrooms,&#xD;&#xA;       Bathrooms&#xD;&#xA;FROM dbo.Property&#xD;&#xA;WHERE AgentId = @AgentId&#xD;&#xA;ORDER BY ListingPrice DESC option (PLAN PER VALUE(ObjectID = 613577224, QueryVariantID = 1, predicate_range([PropertyMLS].[dbo].[Property].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090085E4372DFC69BB9E7EBA421561DE8E1E0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="0.021738" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x476167A000F589CD" QueryPlanHash="0xDE982107B7C28AAE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160">
      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

      <Dispatcher>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2019-09-07T15:32:16.89" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_Property_AgentId]" Table="[Property]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
      </Dispatcher>

    </StmtSimple>
  </Statements>
</Batch>

Comentarios

• Actualmente, la función de optimización PSP solo funciona con predicados de igualdad.

• Los planes del distribuidor se vuelven a generar automáticamente si hay cambios significativos en la distribución de datos. Los planes de variantes de consulta se recompilan independientemente según sea necesario, como con cualquier otro tipo de plan de consulta, sujeto a eventos de volver a compilar predeterminados. Para obtener más información sobre la recompilación, consulte Recompilación de planes de ejecución.

• Las vistas de catálogo del sistema Query Store sys.query_store_plan (Transact-SQL) se ha modificado para diferenciar entre un plan compilado normal, un plan dispatcher y un plan de variante de consulta. La nueva vista de catálogo del sistema del Almacén de consultas, sys.query_store_query_variant (Transact-SQL), contiene información sobre las relaciones de elementos primarios y secundarios entre las consultas con parámetros originales (también conocidas como consultas primarias), los planes del distribuidor y sus variantes de consulta secundarias.

• Cuando hay varios predicados que forman parte de la misma tabla, la optimización PSP selecciona el predicado que tiene la mayor asimetría de datos basándose en el histograma estadístico subyacente. Por ejemplo, con SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2, puesto que tanto column1 = @predicate1 como column2 = @predicate2 pertenecen a la misma tabla, table1, la función solo evaluará el predicado más asimétrico. Sin embargo, si la consulta de ejemplo incluye un operador como UNION, PSP evalúa más de un predicado. Por ejemplo, si una consulta tiene características similares a SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicate, PSP elige como máximo dos predicados en este caso, porque el sistema trata este escenario como si fueran dos tablas diferentes.. El mismo comportamiento puede observarse en las consultas de autocombinación mediante alias de tablas.

• El plan de presentación de XML de una variante de consulta tiene un aspecto similar al siguiente ejemplo, en el que los dos predicados seleccionados tienen su información respectiva agregada a la sugerencia relacionada con PLAN PER VALUE PSP.

  <Batch>
    <Statements>
      <StmtSimple StatementText="SELECT  b.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty a join  PropertyDetails b on a.PropertyId = b.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id&#xD;&#xA;UNION&#xD;&#xA;          SELECT  c.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty c join  PropertyDetails d on c.PropertyId = d.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id option (PLAN PER VALUE(ObjectID = 981578535, QueryVariantID = 9, predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0),predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090051FBCD918F8DFD60D324887356B422D90000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="29.2419" StatementEstRows="211837" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6D2A4E407085C01E" QueryPlanHash="0x72101C0A0DD861AB" CardinalityEstimationModelVersion="160" BatchModeOnRowStoreUsed="true">
        <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
        <Dispatcher>
          <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
            <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
            <Predicate>
              <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [a].[AgentId]=[@AgentId]">
                <Compare CompareOp="EQ">
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[a]" Column="AgentId" />
                    </Identifier>
                  </ScalarOperator>
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Column="@AgentId" />
                    </Identifier>
                  </ScalarOperator>
                </Compare>
              </ScalarOperator>
            </Predicate>
          </ParameterSensitivePredicate>
          <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
            <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
            <Predicate>
              <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [c].[AgentId]=[@AgentId]">
                <Compare CompareOp="EQ">
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[c]" Column="AgentId" />
                    </Identifier>
                  </ScalarOperator>
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Column="@AgentId" />
                    </Identifier>
                  </ScalarOperator>
                </Compare>
              </ScalarOperator>
            </Predicate>
          </ParameterSensitivePredicate>
        </Dispatcher>
        <QueryPlan CachedPlanSize="160" CompileTime="17" CompileCPU="17" CompileMemory="1856" QueryVariantID="9">
  

• Puede influir en los umbrales de asimetría actuales que usa la función de optimización PSP, con uno o más de los siguientes métodos:

  • Marcas de seguimiento del estimador de cardinalidad (CE), como la Marca de seguimiento 9481 (a nivel global, de sesión o de consulta)

  • Opciones de configuración del ámbito de la base de datos que intentan reducir el modelo CE en uso o influir en las suposiciones que hace el modelo CE con respecto a la independencia de múltiples predicados. Esto es especialmente útil en casos en los que no existen estadísticas de varias columnas, lo que afecta a la capacidad de la optimización PSP para evaluar la candidatura de esos predicados.

  • Para obtener más información, consulte la sección Suposición de correlación aumentada para varios predicados del documento técnico Optimización de los planes de consulta con el estimador de cardinalidad de SQL Server 2014. El modelo CE más reciente intenta asumir cierta correlación y menos independencia para la conjunción y disyunción de predicados. El uso del modelo CE heredado puede afectar a la forma de calcular la selectividad de los predicados en un escenario de unión de varias columnas. Esta acción solo debería tenerse en cuenta para escenarios específicos, y no se recomienda usar el modelo CE heredado para la mayoría de las cargas de trabajo.

• La optimización PSP actualmente compila y ejecuta cada variante de consulta como una nueva instrucción preparada, que es una de las razones por las que las variantes de consulta pierden su asociación con el object_id de cualquier módulo primario si el plan dispatcher se basó en un módulo (es decir, procedimiento almacenado, desencadenador, función, vista, etc.). Como instrucción preparada, el object_id no es algo que se pueda asignar a un objeto en sys.objects directamente, sino que es esencialmente un valor calculado basado en un hash interno del texto del lote. Para más información, consulte la sección Tabla devuelta de la documentación DMV de sys.dm_exec_plan_attributes.

  Los planes de variantes de consulta se colocan en el almacén de objetos de caché de planes (CACHESTORE_OBJCP) mientras que los planes dispatcher se colocan en el almacén de caché de planes SQL (CACHESTORE_SQLCP). Sin embargo, la función PSP almacenará el object_id principal de una variante de consulta en el atributo ObjectID que forma parte de la sugerencia PLAN PER VALUE que PSP agrega al plan de presentación XML si la consulta primaria forma parte de un módulo y no es T-SQL dinámico o específico. Las estadísticas de rendimiento agregadas para procedimientos, funciones y desencadenadores almacenados en caché se pueden seguir usando para sus respectivos fines. Las estadísticas más granulares relacionadas con la ejecución, como las que se encuentran en vistas similares a la DMV sys.dm_exec_query_stats, siguen conteniendo datos para las variantes de consulta, sin embargo, la asociación entre object_id para las variantes de consulta y los objetos dentro de la tabla sys.objects no se alinean actualmente, sin un procesamiento adicional del plan de presentación de XML para cada una de las variantes de consulta en las que se requieren estadísticas de tiempo de ejecución más granulares. La información de estadísticas de tiempo de ejecución y de espera para las variantes de consulta se puede obtener del Almacén de consultas sin técnicas adicionales de análisis sintáctico del plan de presentación de XML si el Almacén de consultas está activado.

• Ya que las variantes de consulta PSP se ejecutan como una nueva instrucción preparada, el object_id no se expone automáticamente en los diversos DMV sys.dm_exec_*relacionados con la caché de planes sin analizar el plan de presentación de XML y aplicar técnicas de concordancia de patrones de texto (es decir, procesamiento XQuery adicional). Actualmente, solo los planes dispatcher de optimización PSP emiten el id. del objeto primario apropiado. El object_id se expone dentro del Almacén de Consultas, ya que el Almacén de Consultas permite un modelo más relacional que el que proporciona la jerarquía de caché de planes. Para obtener más información, consulte la vista del catálogo del sistema del Almacén de Consultas sys.query_store_query_variant (Transact-SQL).

Consideraciones

• Para habilitar la optimización del PSP, habilite el nivel de compatibilidad 160 para la base de datos a la que se conecta cuando ejecuta la consulta.

• Para obtener más información sobre la función de optimización PSP, recomendamos que se habilite la integración con el Almacén de Consultas, para lo cual se debe activar el Almacén de Consultas. El siguiente ejemplo habilita el Almacén de Consultas para una base de datos preexistente llamada MyNewDatabase:

ALTER DATABASE [MyNewDatabase]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO
);

Nota:

A partir de SQL Server 2022 (16.x), el Almacén de consultas está ahora habilitado de manera predeterminada para todas las bases de datos recién creadas.

• Para deshabilitar la optimización de PSP en el nivel de base de datos, use la configuración ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF con ámbito de base de datos.

• Para deshabilitar la optimización de PSP en el nivel de consulta, use la sugerencia de consulta DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION.

• Si el seguimiento de parámetros está deshabilitado por el marcador de seguimiento 4136, la configuración de la base de datos PARAMETER_SNIFFING, o la sugerencia de consulta USE HINT('DISABLE_PARAMETER_SNIFFING'), la optimización PSP está deshabilitada para las cargas de trabajo y contextos de ejecución asociados. Para obtener más información, consulte Sugerencias (Transact-SQL): consulta y ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

• El número de variantes únicas de plan por distribuidor almacenadas en la memoria caché de planes se limita para evitar el sobredimensionamiento de la memoria caché. El umbral interno no está documentado. Como cada lote SQL tiene el potencial de crear múltiples planes, y cada plan de variante de consulta tiene una entrada independiente en la caché de planes, es posible alcanzar el número máximo predeterminado de entradas de planes permitidas. Si la tasa de expulsión de la caché de planes es considerablemente alta, o los tamaños de los almacenes de caché CACHESTORE_OBJCP y CACHESTORE_SQLCP son excesivos, debería considerar aplicar la marca de seguimiento 174.

• El número de variantes únicas de plan que se pueden almacenar para una consulta en el almacén de planes se limita con la opción de configuración max_plans_per_query. Como las variantes de consulta pueden tener más de un plan, puede haber un total de 200 planes por consulta en el Almacén de Consultas. Este número incluye todos los planes de variantes de consulta para todos los dispatchers que pertenecen a una consulta primaria. Considere aumentar la opción de configuración max_plans_per_query del Almacén de Consultas.

  • Un ejemplo de cómo el número de planes únicos puede exceder el límite max_plans_per_query predeterminado del Almacén de Consultas sería un escenario en el que tuviera el siguiente comportamiento. Supongamos que tiene una consulta con un ID de consulta de 10, que tiene dos planes dispatcher y cada plan de dispatcher tiene 20 variantes de consulta cada uno (40 variantes de consulta en total). El número total de planes para la consulta ID 10 es de 40 planes para las variantes de consulta y los dos planes dispatcher. También es posible que la consulta primaria (ID de consulta 10) tenga 5 planes normales (no dispatcher). Esto hace 47 planes (40 de variantes de consulta, 2 dispatcher y 5 planes no relacionados con PSP). Además, si cada variante de consulta también tiene una media de cinco planes, en este escenario es posible tener más de 200 planes en el almacén de consultas para una consulta primaria. Esto también dependería de la gran asimetría de datos en el conjunto o conjuntos de datos a los que podría estar haciendo referencia esta consulta primaria de ejemplo.

• Para cada asignación de variantes de consulta a un distribuidor determinado:

  • El query_plan_hash es único. Esta columna está disponible en sys.dm_exec_query_stats y otras vistas de administración dinámica y tablas de catálogo.
  • El plan_handle es único. Esta columna está disponible en sys.dm_exec_query_stats, sys.dm_exec_sql_text y sys.dm_exec_cached_plans, y en otras vistas y funciones de administración dinámica y tablas de catálogo.
  • El query_hash es común a otras variantes que se asignan al mismo distribuidor, por lo que es posible determinar el uso agregado de recursos para las consultas que solo difieren según los valores de parámetro de entrada. Esta columna está disponible en sys.dm_exec_query_stats, sys.query_store_query y en otras tablas de catálogo y vistas de administración dinámica.
  • El sql_handle es único debido a que se agregan identificadores de optimización de PSP especiales al texto de la consulta durante la compilación. Esta columna está disponible en sys.dm_exec_query_stats, sys.dm_exec_sql_text y sys.dm_exec_cached_plans, y en otras vistas y funciones de administración dinámica y tablas de catálogo. La misma información de identificador está disponible en el almacén de consultas como la columna last_compile_batch_sql_handle de la tabla sys.query_store_query de catálogo.
  • El query_id es único en el almacén de consultas. Esta columna está disponible en sys.query_store_query y otras tablas de catálogo de almacén de consultas.

Forzar un plan en el almacén de consultas

Usa los mismos procedimientos almacenados sp_query_store_force_plan y sp_query_store_unforce_plan para operar en planes de distribuidor o variantes.

Si se fuerza una variante, no se fuerza el dispatcher primario. Si se fuerza un distribuidor, solo las variantes de ese distribuidor se consideran aptas para su uso:

• Las variantes previamente forzadas de otros dispatchers se vuelven inactivas pero conservan el estado de forzadas hasta que su dispatcher se vuelva a forzar
• Las variantes forzadas anteriormente en el mismo distribuidor, y que se habían vuelto inactivas, se vuelven a forzar

Comportamiento de las sugerencias de consulta del Almacén de consultas

• Cuando se agrega una sugerencia del Almacén de consultas a una variante de consulta (consulta secundaria), la sugerencia se aplica de la misma manera que a una consulta no PSP. Las sugerencias de variante de consulta tienen mayor prioridad si también se ha aplicado una sugerencia a la consulta primaria en el Almacén de consultas.

• Cuando se agrega una sugerencia del Almacén de consultas a la consulta primaria y la consulta secundaria (variante de consulta) no tiene una sugerencia existente del Almacén de consultas, la consulta secundaria (variante de consulta) hereda la sugerencia de la consulta primaria.

• Si se elimina una sugerencia de consulta del Almacén de consultas de la consulta primaria, también se eliminará la sugerencia de las consultas secundarias (variantes de consulta).

• Si se agrega una sugerencia de RECOMPILE a la consulta primaria, el sistema generará planes no PSP después de que se hayan eliminado de la caché del plan todos los planes de variantes de consulta existentes, ya que la función PSP no funciona en consultas que tienen una sugerencia de RECOMPILE.

• Los resultados de las sugerencias del Almacén de Consultas pueden observarse mediante los Eventos Extendidos query_store_hints_application_success y query_store_hints_application_failed. La tabla sys.query_store_query_hints contiene información sobre la sugerencia de consulta que se ha aplicado. Si la sugerencia solo se ha aplicado a una consulta primaria, el catálogo del sistema contiene la información de la sugerencia para la consulta primaria, pero no para sus consultas secundarias, aunque las secundarias heredan la sugerencia de consulta primaria.

El PSP con sugerencias de consulta y el comportamiento de forzado del plan pueden resumirse en la siguiente tabla:

Sugerencia o plan de variante de consulta	El elemento primario tiene una sugerencia aplicada por el usuario	El elemento primario tiene una sugerencia aplicada	El elemento primario ha forzado manualmente el plan	El elemento primario tiene un plan forzado de APC 1
Sugerencia a través del usuario	Sugerencia de variante de consulta	Sugerencia de variante de consulta	Sugerencia de variante de consulta	N/D
Sugerencia mediante comentarios	Sugerencia de variante de consulta	Sugerencia de variante de consulta	Sugerencia de variante de consulta	N/D
Plan forzado por el usuario	Variante de consulta Plan forzado	Variante de consulta Plan forzado	Variante de consulta Plan forzado	Variante de consulta Plan forzado
Planear forzado por APC	Variante de consulta Plan forzado	Variante de consulta Plan forzado	Variante de consulta Plan forzado	Variante de consulta Plan forzado
Sin sugerencia ni plan forzado	Sugerencia del usuario primario	Sin sugerencia	Ninguna acción	Ninguna acción

¹ Componente de corrección automática del plan de ajuste automático

Eventos extendidos

• parameter_sensitive_plan_optimization_skipped_reason: se produce cuando se omite la característica de plan confidencial de parámetros. Use este evento para supervisar el motivo por el que se ha omitido la optimización del PSP.

  La siguiente consulta muestra todas las posibles razones por las que se omitió PSP:

  SELECT map_value FROM sys.dm_xe_map_values WHERE [name] ='psp_skipped_reason_enum' ORDER BY map_key;
  

• parameter_sensitive_plan_optimization: se desencadena cuando una consulta usa la característica de optimización de un PSP. Solo canal de depuración. Algunos campos de interés podrían ser:

  • is_query_variant: describe si se trata de un plan dispatcher (primario) o de un plan variante de consulta (secundario)
  • predicate_count: número de predicados seleccionados por PSP
  • query_variant_id: muestra el id de la variante de consulta. Un valor de 0 significa que el objeto es un plan dispatcher (primario).

Comportamiento de auditoría de SQL Server

La optimización de PSP proporciona datos de auditoría para la instrucción del plan dispatcher y cualquier variante de consulta asociada con el dispatcher. La columna additional_information de la auditoría de SQL Server también proporciona la información de pila T-SQL adecuada para las variantes de consulta. Si se usa la base de datos MyNewDatabase como ejemplo, si esta base de datos tiene una tabla llamada T2 y un procedimiento almacenado con el nombre usp_test, tras la ejecución del procedimiento almacenado usp_test, el registro de auditoría podría contener las siguientes entradas:

action_id	object_name	instrucción	additional_information
AUSC			<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[Audit_Testing_Psp$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info>
EX	usp_test	exec usp_test 300
SL	T2	seleccione * from dbo.t2 donde ID=@id	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL	T2	seleccione * from dbo.t2 donde ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 1, predicate_range([MyNewDatabase].[dbo].[T2].[ID] = @id, 100.0, 100000.0)))	tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
EX	usp_test	exec usp_test 60000
SL	T2	seleccione * from dbo.t2 donde ID=@id	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL	T2	selecccione * from dbo.t2 donde ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 3, predicate_range([TestDB_FOR_PSP].[dbo].[T2].[ID] = @id, 100.0, 100000.0)))	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>

Problemas conocidos

Problema	Fecha de detección	Estado	Fecha de resolución
Se produce una excepción de infracción de acceso en el almacén de consultas de SQL Server 2022 (16.x) en determinadas condiciones. Es posible que se produzcan excepciones de infracción de acceso cuando esté activada la integración del almacén de consultas de optimización de PSP. Para más información, consulte la actualización en Optimización del plan confidencial de parámetros: Razones.	Marzo de 2023	Resuelto	agosto de 2023 (CU 7)

Resuelto

Se produce una excepción de infracción de acceso en el almacén de consultas de SQL Server 2022 en determinadas condiciones

Nota:

A partir de la actualización acumulativa 7 de SQL Server 2022 (16.x), se han publicado varias correcciones para una condición de carrera que puede provocar una infracción de acceso. Si se producen infracciones de acceso relacionadas con la optimización de PSP con la integración del almacén de consultas después de aplicar la actualización acumulativa 7 para SQL Server 2022 (16.x), tenga en cuenta la siguiente sección de soluciones.

Este problema se produce debido a una condición de carrera que puede originarse cuando las estadísticas en tiempo de ejecución de una consulta ejecutada se transfieren de la representación en memoria del almacén de consultas (que se encuentra en el distribuidor de memoria MEMORYCLERK_QUERYDISKSTORE_HASHMAP) a la versión en disco del almacén de consultas. Las estadísticas en tiempo de ejecución, mostradas como Estadísticas en tiempo de ejecución, se mantienen en memoria durante un periodo definido por la opción DATA_FLUSH_INTERVAL_SECONDS de la instrucción SET QUERY_STORE (el valor predeterminado es 15 minutos). Puede usar el cuadro de diálogo del almacén de consultas de Management Studio para especificar un valor para el intervalo de vaciado de datos (minutos), que se convierte internamente en segundos. Si el sistema está bajo presión de memoria, las estadísticas en tiempo de ejecución pueden volcarse al disco antes de lo definido con la opción DATA_FLUSH_INTERVAL_SECONDS. Cuando subprocesos en segundo plano adicionales del Almacén de consultas relacionados con la limpieza del plan de consultas del Almacén de consultas (es decir, las opciones STALE_QUERY_THRESHOLD_DAYS y/o MAX_STORAGE_SIZE_MB del Almacén de consultas), realizan consultas desde el Almacén de consultas, existe un escenario en el que una variante de consulta y/o su instrucción dispatcher asociada pueden ser desreferenciadas prematuramente. Esto puede dar lugar a una infracción de acceso durante las operaciones de inserción o eliminación de variantes de consulta en el Almacén de consultas.

Consulte la sección Comentarios del artículo Cómo recopila datos el almacén de consultas para obtener más información sobre las operaciones del almacén de consultas.

Solución alternativa: si el sistema sigue experimentando infracciones de acceso en el almacén de consultas con la integración de PSP activada después de aplicar la actualización acumulativa 7 para SQL Server 2022 (16.x), se pueden eliminar las variantes de consulta que se encuentran en el almacén de consultas o se puede desactivar temporalmente la función PSP en el nivel de consulta o base de datos hasta que se disponga de correcciones adicionales.

• Para deshabilitar la optimización de PSP en el nivel de base de datos, use la configuración ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF con ámbito de base de datos.
• Para deshabilitar la optimización de PSP en el nivel de consulta, use la sugerencia de consulta DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION.

Para eliminar todas las variantes de consulta del almacén de consultas, no solo las variantes de consulta que aparecen en la vista de catálogo sys.query_store_query_variant (Transact-SQL), se puede usar una consulta similar a la siguiente. Reemplace [<database>] por la base de datos apropiada que estaba experimentando problemas:

USE master;
GO

--Temporarily turn Query Store off in order to remove query variant plans as well as to
--clear the Query Store in-memory representation of Query Store (HashMap) for a particular database
ALTER DATABASE [<database>] SET QUERY_STORE = OFF;
GO

USE [<database>];
GO

DECLARE @QueryIDsCursor CURSOR;
DECLARE @QueryID BIGINT;
BEGIN
 -- Getting the cursor for query IDs for query variant plans
    SET @QueryIDsCursor = CURSOR FAST_FORWARD FOR
    SELECT query_id
        FROM sys.query_store_plan
    WHERE plan_type = 2 --query variant plans
    ORDER BY query_id;
 
 -- Using a non-set based method for this example query
    OPEN @QueryIDsCursor
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
        WHILE @@FETCH_STATUS = 0
    BEGIN

 -- Deleting query variant(s) from the Query Store
        EXEC sp_query_store_remove_query @query_id = @QueryID;
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
    END;
    CLOSE @QueryIDsCursor ;
    DEALLOCATE @QueryIDsCursor;
END;

--Turn Query Store back on
ALTER DATABASE [<database>] SET QUERY_STORE = ON;
GO

Si el almacén de consultas es grande, o si el sistema tiene una carga de trabajo considerable y/o un número elevado de consultas especiales con parámetros que pueden ser capturadas por el almacén de consultas, desconectar el almacén de consultas podría tardar un poco. Para desconectar el almacén de consultas de forma forzada en estos casos, use en su lugar el comando ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED), en el ejemplo anterior de T-SQL. Para buscar consultas sin parámetros, consulte Buscar consultas sin parámetros en el almacén de consultas.

Contenido relacionado

• Volver a compilar planes de ejecución
• Parámetros y reutilización de un plan de ejecución
• Parametrización simple
• Parametrización forzada
• Sugerencias (Transact-SQL): consulta
• Procesamiento de consultas inteligente en bases de datos SQL
• Sensibilidad de los parámetros
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)