Exercice : optimiser les performances de l’application

10 minutes

Dans cet exercice, vous observerez un nouveau scénario relatif aux performances et le résoudrez en optimisant l’application et les requêtes.

Optimiser les performances des applications avec Azure SQL

Dans certains cas, la migration vers Azure d’une charge de travail existante associée à une application et à des requêtes SQL peut être propice à l’optimisation et au réglage des requêtes.

Vous souhaitez prendre en charge une nouvelle extension de site web pour ajouter un système d’évaluation des clients aux commandes passées sur AdventureWorks. Pour cela, vous devez ajouter une nouvelle table en réponse à un ensemble important d’activités INSERTION simultanées. Vous avez testé la charge de travail de requête SQL sur un ordinateur de développement avec SQL Server 2022 qui possède un lecteur SSD local pour la base de données et le journal des transactions.

Quand vous effectuez le test sur Azure SQL Database à l’aide du niveau Usage général (8 vCores), la charge de travail INSERTION est plus lente. Vous devez déterminer si vous devez changer l’objectif ou le niveau de service pour prendre en charge la nouvelle charge de travail ou si vous devez vous pencher sur l’application.

Vous trouverez tous les scripts de cet exercice dans le dossier 04-Performance\tuning_applications du référentiel GitHub que vous avez cloné ou le fichier zip que vous avez téléchargé.

Créer une nouvelle table pour l’application

Dans l’Explorateur d’objets, sélectionnez la base de données AdventureWorks. Utilisez Fichier>Ouvrir>Fichier pour ouvrir le script order_rating_ddl.sql et créer une table dans la base de données AdventureWorks. Votre fenêtre de l’éditeur de requête doit ressembler au texte suivant :

DROP TABLE IF EXISTS SalesLT.OrderRating;
GO
CREATE TABLE SalesLT.OrderRating
(OrderRatingID int identity not null,
SalesOrderID int not null,
OrderRatingDT datetime not null,
OrderRating int not null,
OrderRatingComments char(500) not null);
GO

Sélectionnez Exécuter pour exécuter le script.

Charger les requêtes pour surveiller l’exécution des requêtes

Nous allons maintenant charger des requêtes T-SQL pour les vues de gestion dynamique (DMV) afin d’observer les performances des requêtes actives, les attentes et les E/S. Chargez toutes ces requêtes dans le contexte de la base de données AdventureWorks.

Dans l’Explorateur d’objets, sélectionnez la base de données AdventureWorks. Utilisez Fichier>Ouvrir>Fichier pour ouvrir le script sqlrequests.sql et examiner les requêtes SQL actives. Votre fenêtre de l’éditeur de requête doit ressembler au texte suivant :
```
SELECT er.session_id, er.status, er.command, er.wait_type, er.last_wait_type, er.wait_resource, er.wait_time
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es
ON er.session_id = es.session_id
AND es.is_user_process = 1;
```
Dans l’Explorateur d’objets, sélectionnez la base de données AdventureWorks. Utilisez Fichier>Ouvrir>Fichier pour ouvrir le script top_waits.sql et examiner les principaux types d’attente par nombre. Votre fenêtre de l’éditeur de requête doit ressembler au texte suivant :
```
SELECT * FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC;
```
Dans l’Explorateur d’objets, sélectionnez la base de données AdventureWorks. Utilisez Fichier>Ouvrir>Fichier pour ouvrir le script tlog_io.sql et observer la latence des écritures dans le journal des transactions. Votre fenêtre de l’éditeur de requête doit ressembler au texte suivant :
```
SELECT io_stall_write_ms/num_of_writes as avg_tlog_io_write_ms, * 
FROM sys.dm_io_virtual_file_stats
(db_id('AdventureWorks'), 2);
```

Préparer le script de charge de travail pour l’exécution

Ouvrez et modifiez le script de charge de travail order_rating_insert_single.cmd.

Remplacez l’unique_id qui vous avez été fourni dans le premier exercice pour le nom du serveur pour le -S parameter.
Remplacez le mot de passe que vous avez fourni dans le déploiement de la base de données du premier exercice pour le -P parameter.
Enregistrez les modifications dans le fichier.

Exécuter la charge de travail

À partir d’une invite de commandes PowerShell, accédez au répertoire de cette activité du module :
```
cd c:<base directory>\04-Performance\tuning_applications
```
Exécutez la charge de travail avec la commande suivante :
```
.\order_rating_insert_single.cmd
```
Ce script utilise le programme ostress.exe pour exécuter 25 utilisateurs simultanés à l’aide de l’instruction T-SQL suivante (dans le script order_rating_insert_single.sql) :
```
DECLARE @x int;
SET @x = 0;
WHILE (@x < 500)
BEGIN
SET @x = @x + 1;
INSERT INTO SalesLT.OrderRating
(SalesOrderID, OrderRatingDT, OrderRating, OrderRatingComments)
VALUES (@x, getdate(), 5, 'This was a great order');
END
```
Comme vous pouvez le voir, ce script ne donne pas exactement une représentation réelle des données en provenance du site web. Toutefois, il simule l’ingestion de nombreuses évaluations de commandes dans la base de données.

Observer les performances des DMV et des charges de travail

Exécutez maintenant dans SQL Server Management Studio (SSMS) les requêtes que vous avez chargées précédemment pour observer les performances. Exécutez les requêtes pour sqlrequests.sql, top_waits.sql et tlog_io.sql.

Ces requêtes vous permettent d’observer les faits suivants :

De nombreuses demandes ont en permanence le wait_type WRITELOG avec une valeur > 0.
Le type d’attente WRITELOG est l’un des nombres les plus élevés pour les types d’attente.
La durée moyenne d’écriture dans le journal des transactions (la colonne avg_tlog_io_write_ms dans le jeu de résultats tlog_io.sql) est d’environ 2 ms.

La durée de cette charge de travail sur une instance SQL Server 2022 avec un lecteur SSD est de 10-12 secondes environ. La durée totale sur Azure SQL Database avec un noyau v8 Gen5 est d’environ 25 secondes.

Les types d’attente WRITELOG avec des temps d’attente plus élevés indiquent un vidage de la latence dans le journal des transactions. Un temps d’attente de 2 ms par écriture semble raisonnable, mais il peut être inférieur à 1 ms sur un disque SSD local.

Choisir une résolution

Le problème ne vient pas d’un pourcentage élevé des activités d’écriture dans le journal. Le Portail Azure et sys.dm_db_resource_stats ne présentent pas de valeurs supérieures à 20-25 % (vous n’avez pas besoin de les interroger). Le problème n’est pas non plus lié à une limite d’IOPS. Le problème est que cette charge de travail d’application est sensible à la faible latence des écritures dans le journal des transactions et que le niveau Usage général n’est pas conçu pour ce type de latence. La latence d’E/S attendue pour Azure SQL Database est de 5 à 7 ms.

Remarque

Les documents Azure SQL Database sur le niveau Usage général indiquent des moyennes de latence d’E/S de l’ordre de 5 à 7 pour les écritures et de l’ordre de 5 à 10 pour les lectures. Les latences que vous rencontrez peuvent donc se rapprocher plus de ces chiffres. Les latences pour le niveau Usage général d’Azure SQL Managed Instance sont similaires. Si votre application est très sensible aux latences d’E/S, songez à passer au niveau Critique pour l’entreprise.

Examinez le script T-SQL de charge de travail order_rating_insert_single.sql. Chaque INSERT est une validation de transaction unique qui nécessite un vidage du journal des transactions.

Une validation pour chaque insertion n’est pas efficace, mais l’application n’a pas été affectée sur un disque SSD local car chaque validation était très rapide. Le niveau tarifaire Critique pour l’entreprise (objectif de service ou référence SKU) fournit des lecteurs SSD locaux avec une latence faible. Il est possible que l’application soit optimisée afin que la charge de travail ne soit pas aussi sensible à la latence des E/S pour le journal des transactions.

Vous pouvez changer le lot T-SQL pour l’inclusion dans un wrapper d’un BEGIN TRAN/COMMIT TRAN par la charge de travail autour des itérations INSERT.

Exécuter une charge de travail modifiée plus efficace

Modifiez les scripts et exécutez-les pour obtenir des performances d’E/S supérieures. Vous trouverez la charge de travail modifiée dans le script order_rating_insert.sql.

Préparez le script de charge de travail en modifiant order_rating_insert.cmd pour utiliser le nom et le mot de passe corrects de votre serveur.
Exécutez la charge de travail modifiée en utilisant le script order_rating_insert.cmd, de la même façon que vous avez exécuté le script de charge de travail précédent.

Observer les nouveaux résultats

Examinez les résultats du script T-SQL pour sqlrequests.sql dans SSMS. Vous pouvez constater bien moins d’attentes WRITELOG et, dans l’ensemble, un temps d’attente inférieur pour ces attentes.

À présent, la charge de travail s’exécute beaucoup plus rapidement qu’avec l’exécution précédente. Il s’agit d’un exemple de réglage d’une application pour les requêtes SQL qui s’exécutent dans Azure ou en dehors.

Remarque

Cette charge de travail peut s’exécuter encore plus rapidement sur une instance d’Azure SQL Database avec un type de connexion Redirection. Le déploiement que vous avez effectué dans cet exercice utilise un type de connexion par défaut. Il s’agit ici d’un type de proxy car vous êtes connecté en dehors d’Azure. L’utilisation de la redirection peut accélérer de manière significative une charge de travail comme celle-ci, compte tenu des allers-retours entre le client et le serveur.
Observez la durée de la charge de travail. La charge de travail s’exécute si rapidement qu’il peut être difficile d’observer les données de diagnostic des requêtes utilisées précédemment dans cette activité.

Le concept de « traitement par lot » peut être bénéfique à la plupart des applications, notamment celles qui sont connectées à Azure SQL.

Conseil

La gouvernance des ressources sur Azure peut affecter les transactions très volumineuses et les symptômes sont LOG_RATE_GOVERNOR. Dans cet exemple, la colonne char(500) non-null remplit les espaces et génère des enregistrements volumineux dans le journal des transactions. Vous pouvez améliorer encore plus les performances en faisant de cette colonne une colonne de longueur variable.

Dans l’unité suivante, vous découvrirez les performances intelligentes dans Azure SQL.