Indicateurs du Magasin des requêtes

  • Article

S’applique à : SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance

Cet article explique comment appliquer des indicateurs de requête avec le Magasin des requêtes. Les indicateurs du Magasin des requêtes fournissent une méthode facile à utiliser pour mettre en forme les plans de requête sans changer le code de l’application.

Les indicateurs du Magasin des requêtes sont disponibles dans Azure SQL Database et Azure SQL Managed Instance. Magasin des requêtes indicateurs sont également une fonctionnalité introduite dans SQL Server dans SQL Server 2022 (16.x).

Attention

Comme l’optimiseur de requête SQL Server sélectionne généralement le meilleur plan d’exécution pour une requête, nous recommandons de ne recourir aux indicateurs qu’en dernier ressort, et à condition d’être un développeur ou un administrateur de base de données expérimenté. Pour plus d’informations, consultez Indicateurs de requête.

Regardez cette vidéo pour voir une présentation des indicateurs du Magasin des requêtes :

Vue d’ensemble

Dans l’idéal, l’optimiseur de requête sélectionne un plan d’exécution optimal pour une requête.

Si un plan optimal n’est pas sélectionné, un développeur ou un administrateur de base de données peut souhaiter optimiser manuellement des conditions spécifiques. Les indicateurs de requête sont spécifiés via la clause et peuvent être utilisés pour affecter le OPTION comportement d’exécution des requêtes. Bien que les indicateurs de requête aident à fournir des solutions localisées à divers problèmes liés aux performances, ceux-ci nécessitent la réécriture du texte de la requête d’origine. Les administrateurs de base de données et les développeurs peuvent ne pas toujours être en mesure d’apporter des modifications directement au code Transact-SQL pour injecter un indicateur de requête. Transact-SQL peut être codé en dur dans une application ou généré automatiquement par l’application. Auparavant, un développeur peut avoir besoin de s’appuyer sur des repères de plan, qui peuvent être complexes à utiliser.

Pour plus d’informations sur les indicateurs de requête qui peuvent être appliqués, consultez Indicateurs de requête pris en charge.

Quand utiliser les indicateurs du Magasin des requêtes ?

Comme son nom l’indique, cette fonctionnalité étend le Magasin des requêtes et dépend de celui-ci. Le Magasin des requêtes permet la capture des requêtes, des plans d’exécution et des statistiques d’exécution associées. Magasin des requêtes simplifie considérablement l’expérience client d’optimisation des performances globale. SQL Server 2016 (13.x) introduit d’abord Magasin des requêtes, et désormais il est activé par défaut dans SQL Server 2022 (16.x), Azure SQL Managed Instance et Azure SQL Database.

The workflow for Query Store Hints.

Voici des exemples où les indicateurs du Magasin des requêtes peuvent aider à résoudre les problèmes de performances au niveau de la requête :

  • Recompilation d’une requête à chaque exécution.
  • Limitation de la taille de l’allocation de mémoire pour une opération d’insertion en bloc.
  • Limitez le degré maximal de parallélisme lors de la mise à jour des statistiques.
  • Utilisation d’une jointure hachée au lieu d’une jointure de boucles imbriquées.
  • Utilisation du niveau de compatibilité 110 pour une requête précise, tout en gardant le niveau de compatibilité 150 pour tous les autres éléments de la base de données.
  • Désactivation de l’optimisation des objectifs de lignes pour une requête SELECT TOP.

Pour utiliser les indicateurs du Magasin des requêtes :

  1. Identifiez le query_id du Magasin des requêtes dans l’instruction de requête que vous souhaitez modifier. Vous pouvez effectuer cette opération de différentes manières :
    • En interrogeant les affichages catalogue du Magasin des requêtes.
    • En utilisant des rapports du Magasin des requêtes intégrés à SQL Server Management Studio.
    • En utilisant Query Performance Insight pour Azure SQL Database dans le portail Azure.
  2. Exécutez sys.sp_query_store_set_hints avec le query_id et la chaîne d’indicateur de requête que vous souhaitez appliquer à la requête. Cette chaîne peut contenir un ou plusieurs indicateurs de requête. Pour obtenir des informations complètes, consultez sys.sp_query_store_set_hints.

Une fois créés, les indicateurs du Magasin des requêtes sont persistants et survivent aux redémarrages ainsi qu’aux basculements. Les indicateurs du Magasin des requêtes remplacent les indicateurs codés en dur au niveau de l’instruction ainsi que les indicateurs de repère de plan existants.

Si un indicateur de requête contredit ce qui est possible pour l’optimisation des requêtes, l’exécution de la requête n’est pas bloquée et l’indicateur n’est pas appliqué. Si un indicateur provoque l’échec d’une requête, il sera ignoré. En outre, vous pourrez voir les détails concernant le dernier échec dans sys.query_store_query_hints.

Procédures stockées système des indicateurs du Magasin des requêtes

Pour créer ou mettre à jour des indicateurs, utilisez sys.sp_query_store_set_hints. Les indicateurs sont spécifiés dans un format N'OPTION (...)'de chaîne valide.

  • Lorsque vous créez un indicateur Magasin des requêtes, si aucun indicateur Magasin des requêtes n’existe pour un indicateur spécifiquequery_id, un nouvel indicateur de Magasin des requêtes est créé.
  • Lorsque vous créez ou mettez à jour un indicateur Magasin des requêtes, si un indicateur Magasin des requêtes existe déjà pour un indicateur spécifiquequery_id, la dernière valeur fournie remplace les valeurs précédemment spécifiées pour la requête associée.
  • query_id S’il n’existe pas, une erreur est générée.

Remarque

Pour obtenir la liste complète des indicateurs pris en charge, consultez sys.sp_query_store_set_hints.

Pour supprimer les indicateurs associés à un query_id, utilisez sys.sp_query_store_clear_hints.

Attributs XML du plan d’exécution

Lorsque des indicateurs sont appliqués, le jeu de résultats suivant apparaît dans l’élément StmtSimple du plan d’exécution au format XML :

Attribut Description
QueryStoreStatementHintText Indicateur(s) du Magasin des requêtes appliqué(s) à la requête
QueryStoreStatementHintId Identificateur unique d’un indicateur de requête
QueryStoreStatementHintSource Source de l’indicateur du Magasin des requêtes (par exemple : « utilisateur »)

Remarque

Ces éléments XML sont disponibles via la sortie des commandes Transact-SQL SET STATISTICS XML et SET SHOWPLAN XML.

Indicateurs du Magasin des requêtes et interopérabilité des fonctionnalités

  • Magasin des requêtes indicateurs remplacent d’autres indicateurs de niveau d’instruction codés en dur et repères de plan.
  • Les requêtes s’exécutent toujours. Les indicateurs de Magasin des requêtes opposés sont ignorés, ce qui entraînerait sinon une erreur.
  • Si Magasin des requêtes indicateurs contredisent, SQL Server ne bloque pas l’exécution des requêtes et Magasin des requêtes indicateur n’est pas appliqué.
  • Paramétrisation simple : les indicateurs du Magasin des requêtes ne sont pas pris en charge pour les instructions qui bénéficient d’une paramétrisation simple.
  • Paramétrisation forcée : l’indicateur RECOMPILE n’est pas compatible avec la paramétrisation forcée définie au niveau de la base de données. Si la base de données a un jeu de paramétrage forcé et que l’indicateur RECOMPILE fait partie de la chaîne d’indicateurs définie dans Magasin des requêtes pour une requête, SQL Server ignore l’indicateur RECOMPILE et applique tous les autres indicateurs s’ils sont appliqués.
  • Les indicateurs de Magasin des requêtes créés manuellement sont exemptés de propre up. L’indicateur et la requête ne sont pas propre à partir de Magasin des requêtes par la rétention automatique de la stratégie de capture.
    • Les requêtes peuvent être supprimées manuellement par les utilisateurs, ce qui supprime également l’indicateur de Magasin des requêtes associé.
    • Magasin des requêtes indicateurs générés automatiquement par le Les commentaires CE sont soumis à propre par la rétention automatique de la stratégie de capture.
    • Commentaires DOP et comportement de requête d’octroi de commentaires d’octroi de mémoire sans utiliser Magasin des requêtes indicateurs. Lorsque les requêtes sont propre up par rétention automatique de la stratégie de capture, les données de commentaires DOP et d’octroi de mémoire sont également propre mises en place.
    • Vous pouvez créer manuellement le même indicateur Magasin des requêtes que les commentaires CE implémentés, puis la requête avec l’indicateur n’est plus soumise à propre par la rétention automatique de la stratégie de capture.

Magasin des requêtes indicateurs et groupes de disponibilité

Pour plus d’informations, consultez Magasin des requêtes pour les réplicas secondaires.

  • Avant SQL Server 2022 (16.x), Magasin des requêtes indicateurs peuvent être appliqués au réplica principal d’un groupe de disponibilité.
  • À compter de SQL Server 2022 (16.x), lorsque Magasin des requêtes pour les réplicas secondaires est activé, Magasin des requêtes indicateurs sont également compatibles avec les réplicas pour les réplicas secondaires dans les groupes de disponibilité.
  • Vous pouvez ajouter un indicateur de Magasin des requêtes à un réplica ou un jeu de réplicas spécifique lorsque vous avez Magasin des requêtes pour les réplicas secondaires activés. Dans sys.sp_query_store_set_query_hints, il est défini par le @query_hint_scope paramètre, qui a été introduit dans SQL Server 2022 (16.x).
  • Recherchez les jeux de réplicas disponibles en interrogeant sys.query_store_replicas.
  • Recherchez les plans forcés sur les réplicas secondaires avec sys.query_store_plan_forcing_locations.

Bonnes pratiques relatives aux indicateurs du Magasin des requêtes

  • Effectuez la maintenance des index et des statistiques avant d’évaluer les requêtes pour déterminer l’existence éventuelle de nouveaux indicateurs du Magasin des requêtes.
  • Testez votre base de données d’application sur le dernier niveau de compatibilité avant d’utiliser Magasin des requêtes indicateurs.
    • Par exemple, l’optimisation du plan sensible aux paramètres (PSP) a été introduite dans SQL Server 2022 (16.x) (niveau de compatibilité 160), qui utilise plusieurs plans actifs par requête pour traiter les distributions de données non-uniformes. Si votre environnement ne peut pas utiliser le dernier niveau de compatibilité, Magasin des requêtes indicateurs utilisant l’indicateur RECOMPILE peut être utilisé sur n’importe quel niveau de compatibilité de prise en charge.
  • Les indicateurs du Magasin des requêtes remplacent le comportement du plan de requête SQL Server. Il est recommandé d’utiliser uniquement Magasin des requêtes indicateurs quand il est nécessaire de résoudre les problèmes liés aux performances.
  • Nous vous recommandons de réévaluer les indicateurs du Magasin des requêtes, les indicateurs au niveau de l’instruction, les repères de plan et les plans forcés du Magasin des requêtes chaque fois que les distributions de données changent et pendant les projets de migration de base de données. Les modifications apportées à la distribution des données peuvent entraîner Magasin des requêtes indicateurs pour générer des plans d’exécution non optimaux.

Exemples

R. Démonstration des indicateurs du Magasin des requêtes

La procédure pas à pas suivante concernant les indicateurs du Magasin des requêtes dans Azure SQL Database utilise une base de données importée par le biais d’un fichier BACPAC (.bacpac). Pour savoir comment importer une nouvelle base de données dans un serveur Azure SQL Database, consultez Démarrage rapide : Importer un fichier BACPAC dans une base de données.

-- ************************************************************************ --
-- Query Store hints demo

-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store

-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --

/*
    Demo prep, connect to the PropertyMLS database
*/

ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE  (QUERY_CAPTURE_MODE = ALL);
GO

-- Should be READ_WRITE
SELECT actual_state_desc
FROM sys.database_query_store_options;
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints.
    Checking if any already exist (should be none).
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
     The PropertySearchByAgent stored procedure has a parameter
     used to filter AgentId.  Looking at the statistics for AgentId,
     you will see that there is a big skew for AgentId 101.
*/
SELECT	hist.range_high_key AS [AgentId],
        hist.equal_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'NCI_Property_AgentId';


-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;

-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;


/*
    Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%' and query_sql_text not like N'%query_store%';
GO

/*
     We can set the hint associated with the query_id returned in the previous result set, as below.
     Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
    We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO

/*
    That Query Store Hint is now removed
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

B. Identifier une requête dans le Magasin des requêtes

L’exemple suivant interroge sys.query_store_query_text et sys.query_store_query afin de retourner le query_id d’un fragment de texte d’une requête exécutée.

Dans cette démonstration, la requête que nous essayons d’ajuster se trouve dans l’exemple de base de données SalesLT :

SELECT * FROM SalesLT.Address as A 
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;

Magasin des requêtes ne reflète pas immédiatement les données de requête à ses vues système.

Identifiez la requête dans les affichages catalogue système du magasin de requêtes :

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
INNER JOIN sys.query_store_query q ON 
    qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%PostalCode =%'  
  AND query_sql_text not like N'%query_store%';
GO

Dans les exemples suivants, l’exemple de requête précédent dans la base de données SalesLT a été identifié comme query_id 39.

Une fois l’identification effectuée, appliquez l’indicateur pour imposer une taille d’allocation de mémoire maximale en pourcentage de la limite de mémoire configurée au query_id :

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

Vous pouvez également appliquer des indicateurs de requête avec la syntaxe suivante, comme l’option permettant de forcer l’estimateur de cardinalité héritée :

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

Vous pouvez appliquer plusieurs indicateurs de requête avec une liste séparée par des virgules :

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

Examinez l’indicateur du Magasin des requêtes qui est appliqué pour le query_id  39 :

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc 
FROM sys.query_store_query_hints 
WHERE query_id = 39;

Enfin, supprimez l’indicateur du query_id 39, avec sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Contenu connexe