Utilisation du Magasin des requêtes avec l’OLTP en mémoire

  • Article

S’applique à : SQL Server base de données Azure SQL

Le Magasin des requêtes SQL Server vous permet de superviser les performances du code compilé en mode natif pour les charges de travail exécutant l’OLTP en mémoire.

Des statistiques de compilation et d’exécution sont collectées et exposées de la même manière que pour les charges de travail sur disque. Lorsque vous migrez vers l’OLTP en mémoire, vous pouvez continuer à utiliser les affichages du magasin de requêtes dans SQL Server Management Studio ainsi que les scripts personnalisés que vous avez développés pour les charges de travail sur disque avant la migration. Cela vous permet de réutiliser vos investissements d’apprentissage de la technologie du magasin de requêtes et les rend plus généralement utilisables pour la résolution des problèmes de toutes les charges de travail.
Pour obtenir des informations générales sur l'utilisation du magasin de requêtes, consultez Monitoring Performance By Using the Query Store.

L’utilisation du magasin de requêtes avec l’OLTP en mémoire ne nécessite pas de configuration de fonctionnalités supplémentaires. Lorsque vous l’activez sur votre base de données, il fonctionne pour tous les types de charges de travail.
Toutefois, il existe quelques aspects spécifiques que les utilisateurs doivent connaître lors de l'utilisation du magasin de requêtes avec l'OLTP en mémoire :

  • Lorsque le magasin de requêtes est activé, les requêtes, les plans et les statistiques de compilation sont collectées par défaut. Toutefois, la collecte de statistiques d’exécution n’est pas activée, sauf si vous l’activez explicitement avec sys.sp_xtp_control_query_exec_stats (Transact-SQL).

  • Lorsque vous attribuez la valeur new_collection_value à 0, le magasin de requêtes arrête de collecter les statistiques d’exécution pour la procédure concernée ou pour l’intégralité de l’instance SQL Server.

  • La valeur configurée avec sys.sp_xtp_control_query_exec_stats (Transact-SQL) n’est pas rendue persistante. Veillez à vérifier et configurer à nouveau la collecte de statistiques après le redémarrage de SQL Server.

  • Comme pour la collecte de statistiques de requête standard, les performances peuvent diminuer lorsque vous utilisez le magasin de requêtes pour effectuer le suivi de l’exécution des charges de travail. Tenez compte du fait qu’il est possible de n’activer la collecte de statistiques que pour un sous-ensemble important de procédures stockées compilées en mode natif.

  • Les requêtes et les plans sont capturés et stockés sur la première compilation native et mis à jour à chaque recompilation.

  • Si vous avez activé le magasin de requêtes ou effacé son contenu une fois toutes les procédures stockées natives compilées, vous devez les recompiler manuellement afin qu’elles soient capturées par le magasin de requêtes. Ceci est également valable si vous avez supprimé manuellement des requêtes à l’aide de sp_query_store_remove_query (Transact-SQL) ou de sp_query_store_remove_plan (Transact-SQL). Pour forcer la recompilation de la procédure, utilisez sp_recompile (Transact-SQL).

  • Le magasin de requêtes tire parti des mécanismes de génération de plan de l'OLTP en mémoire pour capturer le plan d'exécution de requête lors de la compilation. Le plan stocké est sémantiquement équivalent à celui que vous pourriez obtenir avec SET SHOWPLAN_XML ON , à une différence près : les plans du magasin de requêtes sont fractionnés et stockés par instruction individuelle.

  • Lorsque vous exécutez le magasin de requêtes dans une base de données avec une charge de travail mixte, vous pouvez utiliser le champ is_natively_compiled à partir de sys.query_store_plan (Transact-SQL) pour trouver rapidement les plans de requête générés par la compilation de code en mode natif.

  • Le magasin de requêtes (paramètre QUERY_CAPTURE_MODE dans l’instruction ALTER TABLE) n’affecte pas les requêtes à partir des modules compilés en mode natif, car elles sont toujours capturées, quelle que soit la valeur configurée. Cela comprend la définition de QUERY_CAPTURE_MODE = NONE.

  • La durée de compilation des requêtes capturée par le magasin de requêtes comprend uniquement le temps passé à l’optimisation des requêtes, avant la génération du code natif. Plus précisément, elle n'inclut pas le temps de compilation du code C et de la génération des structures internes nécessaires à la génération du code C.

  • Les métriques d’allocation de mémoire au sein de sys.query_store_runtime_stats (Transact-SQL) ne sont pas renseignées pour les requêtes compilées en mode natif. Elles ont toujours la valeur 0. Les colonnes d’allocation de mémoire sont les suivantes : avg_query_max_used_memory, last_query_max_used_memory, min_query_max_used_memory, max_query_max_used_memory et stdev_query_max_used_memory.

Activer et utiliser le Magasin des requêtes avec l’OLTP en mémoire

L'exemple simple suivant illustre l'utilisation du magasin de requêtes avec l'OLTP en mémoire dans un scénario utilisateur de bout en bout. Dans cet exemple, nous supposons qu’une base de données (MemoryOLTP) est activée pour l’OLTP en mémoire.
Pour plus d’informations sur la configuration requise des tables optimisées en mémoire, consultez Création d’une table optimisée en mémoire et d’une procédure stockée compilée en mode natif.

USE MemoryOLTP;
GO

-- Create a simple memory-optimized table
CREATE TABLE dbo.Ord
   (OrdNo INTEGER not null PRIMARY KEY NONCLUSTERED,
    OrdDate DATETIME not null,
    CustCode NVARCHAR(5) not null)
WITH (MEMORY_OPTIMIZED=ON);
GO

-- Enable Query Store before native module compilation
ALTER DATABASE MemoryOLTP SET QUERY_STORE = ON;
GO

-- Create natively compiled stored procedure
CREATE PROCEDURE dbo.OrderInsert(@OrdNo integer, @CustCode nvarchar(5))
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
    BEGIN ATOMIC WITH
    (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English')

    DECLARE @OrdDate DATETIME = GETDATE();
    INSERT INTO dbo.Ord (OrdNo, CustCode, OrdDate)
        VALUES (@OrdNo, @CustCode, @OrdDate);
END;
GO

-- Enable runtime stats collection for queries from dbo.OrderInsert stored procedure
DECLARE @db_id INT = DB_ID()
DECLARE @proc_id INT = OBJECT_ID('dbo.OrderInsert');
DECLARE @collection_enabled BIT;

EXEC [sys].[sp_xtp_control_query_exec_stats] @new_collection_value = 1,
    @database_id = @db_id, @xtp_object_id = @proc_id;

-- Check the state of the collection flag
EXEC sp_xtp_control_query_exec_stats @database_id = @db_id,
    @xtp_object_id = @proc_id,
    @old_collection_value= @collection_enabled output;
SELECT @collection_enabled AS 'collection status';

-- Execute natively compiled workload
EXEC dbo.OrderInsert 1, 'A';
EXEC dbo.OrderInsert 2, 'B';
EXEC dbo.OrderInsert 3, 'C';
EXEC dbo.OrderInsert 4, 'D';
EXEC dbo.OrderInsert 5, 'E';

-- Check Query Store Data
-- Compile time data
SELECT q.query_id, plan_id, object_id, query_hash, p.query_plan,
    p.initial_compile_start_time, p.last_compile_start_time,
    p.last_execution_time, p.avg_compile_duration,
    p.last_force_failure_reason, p.force_failure_count
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
    ON q.query_id = p.plan_id
WHERE q.object_id = OBJECT_ID('dbo.OrderInsert');

-- Get runtime stats
-- Check count_executions field to verify that runtime statistics
-- have been collected by the Query Store
SELECT q.query_id, p.plan_id, object_id, rsi.start_time, rsi.end_time,
    p.last_force_failure_reason, p.force_failure_count, rs.*
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
    ON q.query_id = p.plan_id
JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE q.object_id = OBJECT_ID('dbo.OrderInsert');

Voir aussi