Indicateurs du Magasin des requêtes

  • Article

S’applique à : SQL Server 2022 (16.x)base de données Azure SQL Azure 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. Les indicateurs du Magasin des requêtes 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 doit sélectionner le 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 OPTION et peuvent être utilisés pour influencer le comportement d’exécution de la requête. 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 et les développeurs de base de données ne peuvent pas toujours modifier directement le code Transact-SQL pour injecter un indicateur de requête. Le code Transact-SQL peut être codé en dur dans une application ou être généré automatiquement par l’application. Auparavant, un développeur devait s’appuyer sur des repères de plan, qui étaient parfois 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. Le Magasin des requêtes simplifie grandement l’expérience globale du client concernant le réglage des performances. SQL Server 2016 (13.x) introduit d’abord le Magasin des requêtes, et désormais il est activé par défaut dans SQL Server 2022 (16.x), Azure SQL Managed Instance et base de données Azure SQL.

Workflow des indicateurs du Magasin des requêtes.

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.
  • Limitation du 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 procéder 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’indicateur de requête n’est pas bloqué et il 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 au format de chaîne valide N'OPTION (...)'.

  • Lors de la création d’un indicateur du Magasin des requêtes, si aucun indicateur du Magasin des requêtes ne correspond à un query_id donné, un nouvel indicateur est créé.
  • Lors de la création ou de la mise à jour d’un indicateur du Magasin des requêtes, si un indicateur du Magasin des requêtes existe déjà pour un query_id donné, la dernière valeur fournie remplace les valeurs spécifiées précédemment pour la requête associée.
  • Si aucun query_id n’existe, 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

Quand 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 uniquement 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

  • Les indicateurs du Magasin des requêtes remplacent les autres indicateurs codés en dur au niveau de l’instruction ainsi que les repères de plan.
  • Les requêtes s’exécutent toujours. Les indicateurs du Magasin des requêtes opposés sont ignorés, ce qui entraînerait sinon une erreur.
  • Si des indicateurs du Magasin des requêtes se contredisent, SQL Server ne bloque pas l’exécution des requêtes et l’indicateur du Magasin des requêtes 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 paramétrisation forcée est définie pour la base de données et que l’indicateur RECOMPILE fait partie de la chaîne d’indicateurs définie dans le 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 du Magasin des requêtes créés manuellement sont exemptés du nettoyage. L’indicateur et la requête ne seront pas nettoyés du 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 du Magasin des requêtes associé.
    • Les indicateurs du Magasin des requêtes générés automatiquement par le Retour sur le CE sont soumis à un nettoyage par la rétention automatique de la stratégie de capture.
    • Commentaires DOP et comportement de requête du retour sur l’octroi de mémoire sans utiliser les indicateurs du Magasin des requêtes. Lorsque les requêtes sont nettoyées par rétention automatique de la stratégie de capture, les données des retours sur le DOP et sur l’octroi de mémoire sont également nettoyées.
    • Vous pouvez créer manuellement le même indicateur du Magasin des requêtes que les commentaires CE implémentés, puis la requête avec l’indicateur n’est plus soumise au nettoyage par la rétention automatique de la stratégie de capture.

Indicateurs et groupes de disponibilité du Magasin des requêtes

Magasin des requêtes indicateurs n’ont aucun effet sur les réplicas secondaires, sauf si Magasin des requêtes pour les réplicas secondaires est activé. Pour plus d’informations, consultez Magasin des requêtes pour réplicas secondaires.

  • Avant SQL Server 2022 (16.x), les indicateurs du Magasin des requêtes peuvent être appliqués au réplica principal d’un groupe de disponibilité.
  • À partir de SQL Server 2022 (16.x), lorsque le Magasin des requêtes pour les réplicas secondaires est activé, des indicateurs du Magasin des requêtes prennent également en charge 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 activé le Magasin des requêtes pour les réplicas secondaires. Dans sys.sp_query_store_set_query_hints, ceci est défini par le paramètre @query_hint_scope, 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.

Meilleures 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 niveau de compatibilité le plus récent avant d’exploiter les indicateurs du Magasin des requêtes.
    • Par exemple, l’optimisation PSP (Parameter Sensitive Plan) a été introduite dans SQL Server 2022 (16.x) (niveau de compatibilité 160), qui tire parti de plusieurs plans actifs par requête pour traiter les distributions de données non uniformes. Si votre environnement ne peut pas utiliser le niveau de compatibilité le plus récent, les indicateurs du Magasin des requêtes utilisant l’indicateur RECOMPILE peuvent ê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. Nous vous recommandons de n’exploiter les indicateurs du Magasin des requêtes que quand vous devez résoudre des 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. Si des modifications sont apportées à la distribution des données, les indicateurs du Magasin des requêtes risquent de 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;

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

Identifiez la requête dans les vues de catalogue système Magasin des 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