Procédure pas à pas pour les fonctionnalités relatives aux performances de SQL Server sur Linux

  • Article

S’applique à :SQL Server - Linux

Si vous êtes un utilisateur de Linux nouveau dans SQL Server, les tâches suivantes vous guident tout au long de certaines tâches relatives aux performances. Celles-ci ne sont pas uniques ni spécifiques à Linux, mais elles vous permettent de vous faire une idée des domaines à examiner en détail. Dans chaque exemple, un lien est fourni vers la documentation détaillée de cette zone.

Notes

Les exemples suivants utilisent l’exemple de base de données AdventureWorks2022. Pour obtenir des instructions sur l’obtention et l’installation de cet exemple de base de données, consultez Restaurer une base de données SQL Server à partir de Windows vers Linux.

Créer un index columnstore

Un index columnstore est une technologie permettant de stocker et d’interroger de grands magasins de données dans un format de données en colonnes, appelé columnstore.

  1. Ajoutez un index columnstore à la table SalesOrderDetail en exécutant les commandes Transact-SQL suivantes :

    CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore]
   ON Sales.SalesOrderDetail
   (UnitPrice, OrderQty, ProductID);
GO

  2. Exécutez la requête suivante qui utilise l'index columnstore pour scanner la table :

    SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
   SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM Sales.SalesOrderDetail
   GROUP BY ProductID
   ORDER BY ProductID;

  3. Vérifiez que l'index columnstore a été utilisé en recherchant la valeur object_id pour l'index Columnstore, puis en vérifiant que cette valeur apparaît dans les statistiques d'utilisation de la table SalesOrderDetail :

    SELECT * FROM sys.indexes WHERE name = 'IX_SalesOrderDetail_ColumnStore'
GO

SELECT *
FROM sys.dm_db_index_usage_stats
   WHERE database_id = DB_ID('AdventureWorks2022')
   AND object_id = OBJECT_ID('AdventureWorks2022.Sales.SalesOrderDetail');

Utiliser OLTP en mémoire

SQL Server fournit des fonctionnalités OLTP en mémoire qui peuvent améliorer considérablement les performances des systèmes d’applications. Cette section vous guidera à travers les étapes de création d'une table à mémoire optimisée stockée dans une mémoire et d'une procédure stockée compilée nativement capable d’accéder à la table sans avoir besoin d'être compilée ou interprétée.

Configurer la base de données pour OLTP en mémoire

  1. Il est recommandé de définir la base de données à un niveau de compatibilité d'au moins 130 pour utiliser OLTP en mémoire. Utilisez la requête suivante pour vérifier le niveau de compatibilité actuel de AdventureWorks2022 :

    USE AdventureWorks2022;
GO
SELECT d.compatibility_level
FROM sys.databases as d
    WHERE d.name = DB_NAME();
GO

    Si nécessaire, réglez le niveau à 130 :

    ALTER DATABASE CURRENT
SET COMPATIBILITY_LEVEL = 130;
GO

  2. Lorsqu’une transaction implique à la fois une table sur disque et une table à mémoire optimisée, il est essentiel que la partie optimisation en mémoire de la transaction fonctionne au niveau d’isolation de la transaction, nommé SNAPSHOT. Pour appliquer de manière fiable ce niveau aux tables optimisées en mémoire dans une transaction entre conteneurs, exécutez ce qui suit :

    ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

  3. Avant de créer une table optimisée en mémoire, vous devez créer un groupe de fichiers optimisé pour la mémoire et un conteneur pour les fichiers de données :

    ALTER DATABASE AdventureWorks2022
   ADD FILEGROUP AdventureWorks_mod
      CONTAINS memory_optimized_data;
GO
ALTER DATABASE AdventureWorks2022
   ADD FILE (NAME='AdventureWorks_mod',
   FILENAME='/var/opt/mssql/data/AdventureWorks_mod')
      TO FILEGROUP AdventureWorks_mod;
GO

Créer une table optimisée en mémoire

La banque principale des tables à mémoire optimisée est la mémoire principale et, contrairement aux tables sur disque, les données n'ont pas besoin d'être lues à partir du disque dans les tampons de mémoire. Pour créer une table optimisée en mémoire, utilisez la clause MEMORY_OPTIMIZED = ON.

  1. Exécutez la requête suivante pour créer la table optimisée en mémoire dbo.ShoppingCart. Par défaut, les données seront conservées sur le disque à des fins de durabilité (la DURABILITÉ peut également être définie pour ne conserver que le schéma).

    CREATE TABLE dbo.ShoppingCart (
ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
CreatedDate DATETIME2 NOT NULL,
TotalPrice MONEY
) WITH (MEMORY_OPTIMIZED=ON);
GO

  2. Insérez quelques enregistrements dans la table :

    INSERT dbo.ShoppingCart VALUES (8798, SYSDATETIME(), NULL);
INSERT dbo.ShoppingCart VALUES (23, SYSDATETIME(), 45.4);
INSERT dbo.ShoppingCart VALUES (80, SYSDATETIME(), NULL);
INSERT dbo.ShoppingCart VALUES (342, SYSDATETIME(), 65.4);

Procédures stockées compilées en mode natif

SQL Server prend en charge les procédures stockées compilées en mode natif qui accèdent aux tables mémoire optimisées. Les instructions T-SQL sont compilées en code machine et stockées sous forme de DLL natives, ce qui permet un accès plus rapide aux données et une exécution des requêtes plus efficace qu’avec des requêtes T-SQL traditionnelles. Les procédures stockées qui sont identifiées par NATIVE_COMPILATION sont compilées en mode natif.

  1. Exécutez le script suivant pour créer une procédure stockée compilée en mode natif qui insère un grand nombre d'enregistrements dans la table ShoppingCart :

    CREATE PROCEDURE dbo.usp_InsertSampleCarts @InsertCount INT
    WITH NATIVE_COMPILATION, SCHEMABINDING AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

DECLARE @i INT = 0

WHILE @i < @InsertCount
   BEGIN
       INSERT INTO dbo.ShoppingCart VALUES (1, SYSDATETIME(), NULL)

       SET @i += 1
   END
END

  2. Insérez 1 000 000 de lignes :

    EXEC usp_InsertSampleCarts 1000000;

  3. Vérifiez que les lignes ont été insérées :

    SELECT COUNT(*) FROM dbo.ShoppingCart;

Utiliser Magasin des requêtes

Magasin des requêtes collecte des informations détaillées sur les performances des requêtes, les plans d'exécution et les statistiques d'exécution.

Avant SQL Server 2022 (16.x), le Magasin des requêtes n’est pas activé par défaut et peut être activé avec ALTER DATABASE :

ALTER DATABASE AdventureWorks2022 SET QUERY_STORE = ON;

Exécutez la requête suivante pour retourner des informations sur les requêtes et les plans dans le magasin des requêtes :

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
   JOIN sys.query_store_query AS Qry
      ON Pl.query_id = Qry.query_id
   JOIN sys.query_store_query_text AS Txt
      ON Qry.query_text_id = Txt.query_text_id;

Interroger les vues de gestion dynamique

Les vues de gestion dynamique renvoient des informations sur l'état du serveur qu'il est possible d'utiliser pour surveiller l'état d'une instance du serveur, diagnostiquer des problèmes et améliorer les performances.

Pour interroger la vue de gestion dynamique dm_os_wait stats :

SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats;

Voir aussi

Contenu associé