Exercice : analyser et résoudre les problèmes de performances

  • 10 minutes

Dans cet exercice, vous allez découvrir comment monitorer et résoudre des problèmes de performances liés à Azure SQL en tirant parti de fonctionnalités et d’outils à la fois familiers et nouveaux.

Configurer : Utiliser des scripts pour déployer Azure SQL Database

La session de terminal sur la droite, Azure Cloud Shell, vous permet d’interagir avec Azure à l’aide d’un navigateur. Pour cet exercice, vous exécuterez un script pour créer votre environnement, une instance d’Azure SQL Database avec la base de données AdventureWorks. (L’exemple de base de données AdventureWorksLT le plus simple et le plus petit, mais nous l’appelons AdventureWorks pour éviter toute confusion.) Dans le script, vous êtes invité à entrer un mot de passe et votre adresse IP locale pour permettre à votre appareil de se connecter à la base de données.

L’exécution de ce script prend 3 à 5 minutes. N’oubliez pas de noter votre mot de passe, votre ID unique et votre région. Ceux-ci ne seront plus affichés.

  1. Commencez par obtenir votre adresse IP locale. Vérifiez que vous êtes déconnecté de tout service VPN, puis ouvrez un terminal PowerShell local sur votre appareil. Exécutez la commande suivante et notez l’adresse IP qui s’affiche :

    (Invoke-WebRequest -Uri "https://ipinfo.io/ip").Content

  2. Dans Azure Cloud Shell à droite, entrez le code suivant et, lorsque vous y êtes invité, fournissez un mot de passe complexe et votre adresse IP publique locale que vous avez récupérée à l’étape précédente. Appuyez sur Entrée pour exécuter la dernière ligne du script.

    $adminSqlLogin = "cloudadmin"
$password = Read-Host "Your username is 'cloudadmin'. Please enter a password for your Azure SQL Database server that meets the password requirements"
# Prompt for local ip address
$ipAddress = Read-Host "Disconnect your VPN, open PowerShell on your machine and run '(Invoke-WebRequest -Uri "https://ipinfo.io/ip").Content'. Please enter the value (include periods) next to 'Address':"
# Get resource group and location and random string
$resourceGroup = Get-AzResourceGroup | Where ResourceGroupName -like "<rgn>Sandbox resource group name</rgn>"
$resourceGroupName = "<rgn>Sandbox resource group name</rgn>"
$uniqueID = Get-Random -Minimum 100000 -Maximum 1000000
$storageAccountName = "mslearnsa"+$uniqueID
$location = $resourceGroup.Location
$serverName = "aw-server$($uniqueID)"

  3. Exécutez le script suivant dans Azure Cloud Shell. Enregistrez la sortie. Vous aurez besoin de ces informations dans l’ensemble du module. Appuyez sur Entrée après avoir collé le code, de sorte que la dernière ligne de code affiche la sortie dont vous avez besoin.

    Write-Host "Please note your unique ID for future exercises in this module:"  
Write-Host $uniqueID
Write-Host "Your resource group name is:"
Write-Host $resourceGroupName
Write-Host "Your resources were deployed in the following region:"
Write-Host $location
Write-Host "Your server name is:"
Write-Host $serverName

    Conseil

    Enregistrez la sortie et notez votre mot de passe, votre ID unique et votre serveur. Vous aurez besoin de ces éléments tout au long du module.

  4. Exécutez le script suivant pour déployer une instance d’Azure SQL Database et un serveur logique avec l’exemple AdventureWorks . Ce script ajoute votre adresse IP comme règle de pare-feu, active Advanced Data Security et crée un compte de stockage que vous utilisez dans le reste des exercices de ce module. L’exécution du script peut prendre plusieurs minutes et sera suspendue plusieurs fois. Attendez une invite de commandes.

    # The logical server name has to be unique in the system
$serverName = "aw-server$($uniqueID)"
# The sample database name
$databaseName = "AdventureWorks"
# The storage account name has to be unique in the system
$storageAccountName = $("sql$($uniqueID)")
# Create a new server with a system wide unique server name
$server = New-AzSqlServer -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -Location $location `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
# Create a server firewall rule that allows access from the specified IP range and all Azure services
$serverFirewallRule = New-AzSqlServerFirewallRule `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -FirewallRuleName "AllowedIPs" `
    -StartIpAddress $ipAddress -EndIpAddress $ipAddress 
$allowAzureIpsRule = New-AzSqlServerFirewallRule `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -AllowAllAzureIPs
# Create a database
$database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -SampleName "AdventureWorksLT" `
    -Edition "GeneralPurpose" -Vcore 2 -ComputeGeneration "Gen5"
# Enable Advanced Data Security
$advancedDataSecurity = Enable-AzSqlServerAdvancedDataSecurity `
    -ResourceGroupName $resourceGroupName `
    -ServerName $serverName
# Create a Storage Account
$storageAccount = New-AzStorageAccount -ResourceGroupName $resourceGroupName `
    -AccountName $storageAccountName `
    -Location $location `
    -Type "Standard_LRS"

  5. Sur votre appareil local, ouvrez SQL Server Management Studio (SSMS) pour créer une nouvelle connexion à votre serveur logique.

  6. Dans la boîte de dialogue de connexion Se connecter au serveur, fournissez les informations suivantes :

    Champ Valeur
    Type de serveur Moteur de base de données (par défaut).
    Nom du serveur Le nom $serverName qui a été retourné dans Cloud Shell, plus le reste de l’URI. Par exemple : aw-server<unique ID>.database.windows.net.
    Authentification Authentification SQL Server (par défaut).
    Connexion cloudadmin Le nom adminSqlLogin affecté à l’étape 1 de cet exercice.
    Mot de passe Le mot de passe que vous avez fourni à l’étape 1 de cet exercice.
    Se souvenir du mot de passe checked

  7. Sélectionnez Se connecter.

    Screenshot of connection dialog for SQL Database in SSMS.

    Remarque

    En fonction de votre configuration locale (par exemple un VPN), l’adresse IP de votre client peut différer de l’adresse IP que le Portail Azure a utilisé lors du déploiement. Si c’est le cas, vous obtenez le message suivant : « L’adresse IP de votre client n’a pas accès au serveur. Connectez-vous à un compte Azure et créez une règle de pare-feu pour activer l’accès. » Si vous recevez ce message, connectez-vous en utilisant le compte que vous utilisez pour le bac à sable et ajoutez une règle de pare-feu pour l’adresse IP de votre client. Vous pouvez effectuer toutes ces étapes en utilisant l’Assistant dans SSMS.

Préparer l’exercice en chargeant et en modifiant des scripts

Vous trouverez tous les scripts de cet exercice dans le dossier 04-Performance\monitor_and_scale dans le référentiel GitHub que vous avez cloné ou le fichier zip que vous avez téléchargé. Préparons l’exercice en chargeant et en modifiant des scripts.

  1. Dans SSMS, dans l’Explorateur d’objets, développez le dossier Bases de données et sélectionnez la base de données AdventureWorks.

  2. Sélectionnez Fichier>Ouvrir>Fichier pour charger le script dmexecrequests.sql. 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;

  3. Utilisez la même méthode dans SSMS pour charger le script dmdbresourcestats.sql. Une nouvelle fenêtre de l’éditeur de requête doit ressembler au texte suivant :

    SELECT * FROM sys.dm_db_resource_stats;

    Cette vue de gestion dynamique (DMV) effectue le suivi de l’utilisation globale des ressources de votre charge de travail sur Azure SQL Database. Par exemple, elle effectue le suivi du processeur, des E/S et de la mémoire.

  4. Ouvrez et modifiez le script sqlworkload.cmd (qui utilise le programme ostress.exe).

    • Remplacez votre unique_id que vous avez enregistré à partir du script de déploiement dans le nom du serveur.
    • Remplacez le mot de passe que vous avez utilisé pour la connexion du serveur Azure SQL Database par -P parameter.
    • Enregistrez les changements apportés au script.

Exécuter la charge de travail

Dans cette tâche, vous allez exécuter une charge de travail dans une requête T-SQL pour observer ses performances lors de la simulation des utilisateurs simultanés.

  1. Utilisez SSMS pour ouvrir le fichier de script topcustomersales.sql et observer la requête. Vous n’exécutez pas la requête à partir de SSMS. Votre fenêtre de l’éditeur de requête doit ressembler au texte suivant :

    DECLARE @x int
DECLARE @y float
SET @x = 0;
WHILE (@x < 10000)
BEGIN
SELECT @y = sum(cast((soh.SubTotal*soh.TaxAmt*soh.TotalDue) as float))
FROM SalesLT.Customer c
INNER JOIN SalesLT.SalesOrderHeader soh
ON c.CustomerID = soh.CustomerID
INNER JOIN SalesLT.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN SalesLT.Product p
ON p.ProductID = sod.ProductID
GROUP BY c.CompanyName
ORDER BY c.CompanyName;
SET @x = @x + 1;
END
GO

    Cette base de données est de petite taille. La requête de récupération de la liste des clients et des informations de ventes associées, classées selon les clients totalisant le plus de ventes, ne doit pas générer un grand jeu de résultats. Vous pouvez régler cette requête en réduisant le nombre de colonnes du jeu de résultats, mais celles-ci sont nécessaires à des fins de démonstration pour cet exercice.

  2. À partir d’une invite de commandes PowerShell, entrez la commande suivante pour accéder au répertoire approprié pour cet exercice. Remplacez <base directory> par votre ID d’utilisateur et le chemin d’accès de ce module :

    cd <base directory>\04-Performance\monitor_and_scale

  3. Exécutez la charge de travail avec la commande suivante :

    .\sqlworkload.cmd

    Ce script utilise 10 utilisateurs simultanés qui exécutent la requête de charge de travail deux fois. Notez que le script lui-même exécute un lot unique, mais qu’il s’exécute en boucle 10 000 fois. Il affecte également le résultat à une variable, ce qui élimine la quasi-totalité du trafic du jeu de résultats à destination du client. Ce n’est pas obligatoire, mais cela permet de montrer une charge de travail de processeur « pure » exécutée entièrement sur le serveur.

    Conseil

    Si vous ne voyez pas le comportement d’utilisation du processeur avec cette charge de travail pour votre environnement, vous pouvez ajuster le -n parameter pour le nombre d’utilisateurs et le -r parameter pour les itérations.

    À l’invite de commandes, la sortie doit ressembler à ce qui suit :

    [datetime] [ostress PID] Max threads setting: 10000
[datetime] [ostress PID] Arguments:
[datetime] [ostress PID] -S[server].database.windows.net
[datetime] [ostress PID] -isqlquery.sql
[datetime] [ostress PID] -U[user]
[datetime] [ostress PID] -dAdventureWorks
[datetime] [ostress PID] -P********
[datetime] [ostress PID] -n10
[datetime] [ostress PID] -r2
[datetime] [ostress PID] -q
[datetime] [ostress PID] Using language id (LCID): 1024 [English_United States.1252] for character formatting with NLS: 0x0006020F and Defined: 0x0006020F
[datetime] [ostress PID] Default driver: SQL Server Native Client 11.0
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_1.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_2.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_3.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_4.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_5.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_6.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_7.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_8.out]
[datetime] [ostress PID] Attempting DOD5015 removal of [directory]\sqlquery_9.out]
[datetime] [ostress PID] Starting query execution...
[datetime] [ostress PID]  BETA: Custom CLR Expression support enabled.
[datetime] [ostress PID] Creating 10 thread(s) to process queries
[datetime] [ostress PID] Worker threads created, beginning execution...

Observer les performances de la charge de travail

Nous allons utiliser les requêtes DMV que vous avez chargées précédemment pour observer les performances.

  1. Exécutez la requête précédemment chargée dans SSMS pour superviser dm_exec_requests (dmexecrequests.sql) et observer les demandes actives. Exécutez cette requête cinq ou six fois et observez les résultats :

    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;

    Vous devez voir que beaucoup de requêtes ont l’état RUNNABLE et que last_wait_type est SOS_SCHEDULER_YIELD. Un indicateur de nombreuses requêtes RUNNABLE et un grand nombre des attentes SOS_SCHEDULER_YIELD sont un manque possible de ressources du processeur pour les requêtes actives.

    Remarque

    Il est possible que vous voyiez une ou plusieurs requêtes actives avec une commande SELECT et un wait_type de XE_LIVE_TARGET_TVF. Ces requêtes sont exécutées par des services gérés par Microsoft. Elles contribuent notamment à générer des insights sur les performances au moyen d’événements étendus. Microsoft ne publie pas les détails de ces sessions.

    Laissez cette fenêtre de l’éditeur de requête ouverte. Vous en aurez besoin dans l’exercice suivant.

  2. Exécutez la requête dans SSMS précédemment chargée pour surveiller sys.dm_db_resource_stats (dmdbresourcestats.sql). Exécutez la requête pour afficher les résultats de cette DMV trois ou quatre fois.

    SELECT * FROM sys.dm_db_resource_stats;

    Cette DMV enregistre un instantané de l’utilisation des ressources pour la base de données toutes les 15 secondes (l’instantané est conservé pendant 1 heure). La colonne avg_cpu_percent doit être proche de 100 % pour plusieurs instantanés. Il s’agit d’un symptôme d’une charge de travail qui pousse les limites des ressources du processeur pour la base de données.

    Dans le cas d’un environnement local SQL Server, vous utilisez généralement un outil spécifique au système d’exploitation pour effectuer un suivi de l’utilisation globale des ressources (par exemple, un processeur). Vous pouvez par exemple utiliser l’Analyseur de performances Windows. Si vous avez exécuté cet exemple sur un serveur SQL Server local ou dans une machine virtuelle avec deux processeurs, vous devriez constater une utilisation du processeur proche de 100 % sur le serveur.

    Remarque

    Vous pouvez exécuter une autre DMV, sys.resource_stats, dans le contexte de la base de données master du serveur Azure SQL Database pour voir l’utilisation des ressources de toutes les bases de données Azure SQL Database associées au serveur. Cette vue est moins granulaire et affiche l’utilisation des ressources toutes les cinq minutes (conservée pendant 14 jours).

    Laissez cette fenêtre de l’éditeur de requête ouverte. Vous en aurez besoin dans l’exercice suivant.

  3. Laissez la charge de travail se terminer et prenez note de sa durée globale. Une fois la charge de travail terminée, vous devriez voir des résultats semblables aux suivants et un retour à l’invite de commandes :

    [datetime] [ostress PID] Total IO waits: 0, Total IO wait time: 0 (ms)
[datetime] [ostress PID] OSTRESS exiting normally, elapsed time: 00:01:22.637

    La durée peut varier, mais l’opération prend généralement 1 à 3 minutes. Veillez à laisser cette opération s’exécuter jusqu’à la fin. Une fois la charge de travail terminée, vous revenez à l’invite de commandes.

Utiliser le Magasin des requêtes pour une analyse plus poussée

Le Magasin des requêtes est une fonctionnalité de SQL Server pour suivre l’exécution des requêtes de performances. Les données de performances sont stockées dans la base de données utilisateur. Le Magasin des requêtes n’est pas activé par défaut pour les bases de données créées dans SQL Server, mais il l’est pour Azure SQL Database (et Azure SQL Managed Instance).

Le Magasin des requêtes est fourni avec une série de vues de catalogue système pour afficher les données de performances. SSMS fournit des rapports mettant en avant ces vues.

  1. À l’aide de l’Explorateur d’objets dans SSMS, ouvrez le dossier Magasin des requêtes pour rechercher le rapport des principales requêtes consommatrices de ressources.

    Screenshot of the Query Store.

  2. Sélectionnez le rapport pour savoir quelles requêtes ont consommé les ressources les plus moyennes et les détails de l’exécution de ces requêtes. En fonction de la charge de travail exécutée à ce stade, votre rapport doit ressembler à l’image suivante :

    Screenshot of the top query report.

    La requête affichée est la requête SQL de la charge de travail pour les ventes client. Ce rapport a trois composants : les requêtes avec une durée totale élevée (vous pouvez modifier la métrique), le plan de requête et les statistiques d’exécution associés, ainsi que le plan de requête associé dans un mappage visuel.

  3. Sélectionnez le graphique à barres pour la requête (le query_id peut être différent pour votre système). Les résultats doivent ressembler à l’image suivante :

    Screenshot of the query ID.

    Vous pouvez voir la durée totale de la requête et du texte de la requête.

  4. À droite de ce graphique à barres se trouve un graphique des statistiques pour le plan de requête associé à la requête. Placez le curseur sur le point associé au plan. Les résultats doivent ressembler à l’image suivante :

    Screenshot of slow query statistics.

    Notez la durée moyenne de la requête. Vos durées peuvent varier, mais comparez cette durée moyenne au temps d’attente moyen pour cette requête. Plus tard, nous introduirons une amélioration des performances, et vous effectuerez à nouveau cette comparaison pour voir la différence.

  5. Le composant final est le plan de requête visuel. Le plan de requête de cette requête ressemble à l’image suivante :

    Screenshot of the workload query plan.

    Cette table de base de données contient si peu de lignes qu’elle n’a pas besoin d’un plan, cela peut s’avérer inefficace. Le réglage de la requête n’améliorera pas les performances d’une quantité mesurable. Un avertissement peut s’afficher dans le plan concernant un manque de statistiques pour l’une des colonnes de recherche d’index cluster. Les performances globales ne sont pas prises en compte.

  6. Après le rapport Principales requêtes consommatrices de ressources dans SSMS, il existe un rapport intitulé Statistiques d’attente des requêtes. De par vos diagnostics précédents, vous savez qu’un grand nombre de demandes étaient constamment dans un état RUNNABLE avec une utilisation du processeur avoisinant les 100 %. Les rapports fournis dans le Magasin des requêtes vous permettent d’examiner les goulots d’étranglement causés par des attentes sur les ressources. Sélectionnez ce rapport et placez le curseur sur le graphique à barres. Les résultats doivent ressembler à l’image suivante :

    Screenshot of the top wait statistics.

    Vous pouvez voir que la catégorie d’attente principale est CPU (ce qui équivaut au wait_type SOS_SCHEDULER_YIELD, disponible dans sys.dm_os_wait_stats) et le temps d’attente moyen.

  7. Cliquez sur le graphique à barres du processeur dans le rapport. La requête principale, en attente du processeur, est la requête issue de la charge de travail que vous utilisez.

    Screenshot of the top wait statistics query.

    Notez que le temps d’attente moyen du processeur dans cette requête est un pourcentage élevé de la durée moyenne globale de la requête.

    Compte tenu des informations dont nous disposons, sans aucun réglage de la requête, notre charge de travail nécessite une capacité de processeur supérieure à celle que nous avons déployée pour notre instance d’Azure SQL Database.

  8. Fermez les deux rapports de magasin de données des requêtes. Vous utiliserez ces mêmes rapports dans l’exercice suivant.

Observer les performances avec Azure Monitor

Utilisons une autre méthode pour afficher l’utilisation des ressources de notre charge de travail. Azure Monitor fournit des métriques de performances que vous pouvez consulter de différentes façons, notamment via le portail Azure.

  1. Ouvrez le portail Azure, puis recherchez votre instance de la base de données AdventureWorks SQL. Sur le volet Vue d’ensemble de la base de données, sélectionnez l’onglet Monitoring. La vue par défaut dans le volet Monitoring est Utilisation du calcul :

    Screenshot of the Azure portal with a slow query.

    Dans cet exemple, le pourcentage du processeur est proche de 100 % pour un intervalle de temps récent. Ce graphique montre l’utilisation des ressources (processeur et E/S sont les paramètres par défaut) au cours de la dernière heure, et il est actualisé de façon continue. Sélectionnez le graphique pour que vous puissiez le personnaliser et examiner l’utilisation d’autres ressources.

  2. Dans le menu de base de données SQL, sélectionnez Ajouter des métriques. Un autre moyen de voir les métriques Utilisation de calcul et d’autres métriques collectées automatiquement par Azure Monitor pour Azure SQL Database consiste à utiliser Metrics Explorer.

    Notes

    La vue Utilisation de calcul est une vue prédéfinie de Metrics Explorer. Si vous sélectionnez la liste déroulante Métriques dans la fenêtre Ajouter des métriques, vous verrez les résultats suivants :

    Screenshot of Azure Monitor metrics.

    Comme l’illustre cette capture d’écran, vous pouvez utiliser plusieurs métriques à afficher avec Metrics Explorer. La vue par défaut de Metrics Explorer couvre une période de 24 heures, avec une granularité de cinq minutes. La vue Utilisation du calcul est la dernière heure avec une granularité d’une minute (que vous pouvez modifier). Pour voir la même vue, sélectionnez Pourcentage de processeur et choisissez une capture de une heure. La granularité passe à une minute et doit ressembler à l’image suivante :

    Screenshot of Azure Monitor metrics, including CPU after 1 minute.

    La vue par défaut est un graphique en courbes, mais l’affichage Explorateur vous permet de modifier le type de graphique. Metrics Explorer offre de nombreuses options, y compris la capacité d’afficher plusieurs métriques sur le même graphique.

Journaux d’activité Azure Monitor

Dans cet exercice, vous n’avez pas configuré de journal Azure Monitor, mais il est utile de déterminer à quoi peut ressembler un journal pour un scénario d’utilisation des ressources du processeur. Les journaux Azure Monitor peuvent fournir un enregistrement historique bien plus long que les métriques Azure.

Si vous avez configuré les journaux Azure Monitor avec un espace de travail Log Analytics, vous pouvez utiliser la requête Kusto suivante pour voir les mêmes résultats d’utilisation de processeur pour la base de données :

AzureMetrics
| where MetricName == 'cpu_percent'
| where Resource == "ADVENTUREWORKS"
| project TimeGenerated, Average
| render columnchart

Les résultats doivent ressembler à l’image suivante :

Screenshot of a query measuring CPU.

Les journaux Azure Monitor sont générés avec un délai lors de la configuration initiale des diagnostics de journal pour une base de données. L’affichage des résultats peut donc prendre du temps.

Dans cet exercice, vous avez appris à observer un scénario de performances SQL courant et à étudier plus en détail une solution possible pour améliorer les performances. Dans l’unité suivante, vous allez découvrir des méthodes permettant d’accélérer et d’optimiser les performances.