Utiliser le Magasin des requêtes avec OLTP en mémoire

  • Article

S’applique à :SQL ServerAzure SQL Database

SQL Server Magasin des requêtes vous permet de surveiller les performances du code compilé en mode natif pour les charges de travail exécutant 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 OLTP en mémoire, vous pouvez continuer à utiliser des vues Magasin des requêtes dans SQL Server Management Studio et des scripts personnalisés que vous avez développés pour les charges de travail sur disque avant la migration. Cela permet d’économiser votre investissement dans l’apprentissage Magasin des requêtes technologie et le rend utilisable pour résoudre tous les 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 des requêtes avec OLTP en mémoire ne nécessite aucune configuration de fonctionnalité supplémentaire. 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 Magasin des requêtes est activé, les requêtes, les plans et les statistiques de compilation sont collectées par défaut. Toutefois, la collecte des statistiques d’exécution est activée uniquement si vous l’activez explicitement avec sys.sp_xtp_control_query_exec_stats (Transact-SQL).

  • Lorsque vous définissez @new_collection_value sur 0, le Magasin des requêtes cesse de collecter des statistiques d’exécution pour la procédure affectée ou l’ensemble de l’instance SQL Server.

  • La valeur configurée avec sys.sp_xtp_control_query_exec_stats (Transact-SQL) n’est pas conservée. Vérifiez que vous case activée et configurez à nouveau la collecte de statistiques après le redémarrage de SQL Server.

  • Comme pour la collecte régulière des statistiques de requête, les performances peuvent diminuer lorsque vous utilisez Magasin des requêtes pour suivre l’exécution de la charge de travail. Envisagez d’activer la collecte des statistiques uniquement 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é Magasin des requêtes ou effacé son contenu une fois que toutes les procédures stockées natives ont été compilées, vous devez les recompiler manuellement pour les capturer par le Magasin des requêtes. Cela s’applique 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). Utilisez sp_recompile (Transact-SQL) pour forcer la recompilation de procédure.

  • 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 Magasin des 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 rechercher rapidement des plans de requête générés par la compilation de code natif.

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

  • La durée de la compilation de requêtes capturée par l’Magasin des requêtes inclut uniquement le temps passé dans 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 dans sys.query_store_runtime_stats (Transact-SQL) ne sont pas remplies pour les requêtes compilées en mode natif . Leurs valeurs sont toujours 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 Magasin des requêtes avec 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 partons du principe qu’une base de données (MemoryOLTP) est activée pour OLTP en mémoire.
Pour plus d’informations sur les prérequis pour les tables mémoire optimisées, 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