Analyse de la charge de travail d'une base de données sur une instance de base de données Amazon RDS for SQL Server avec l'Assistant Paramétrage du moteur de base de données

L'Assistant Paramétrage du moteur de base de données est une application cliente fournie par Microsoft qui analyse la charge de travail de la base de données et recommande un ensemble optimal d'index pour vos bases de données Microsoft SQL Server en fonction des types de requêtes que vous exécutez. Comme SQL Server Management Studio, vous exécutez l'Assistant Paramétrage à partir d'un ordinateur client qui se connecte à votre instance de base de données Amazon RDS exécutant SQL Server. L'ordinateur client peut être un ordinateur local que vous exécutez sur site au sein de votre propre réseau ou une instance Amazon EC2 Windows qui s'exécute dans la même région que votre instance de base de données Amazon RDS.

Cette section montre comment capturer une charge de travail pour que l'Assistant Paramétrage l'analyse. Il s'agit du processus privilégié pour capturer une charge de travail parce que Amazon RDS limite l'accès de l'hôte à l'instance SQL Server. Pour plus d'informations, consultez Assistant Paramétrage du moteur de base de données dans la documentation Microsoft.

Pour utiliser l'Assistant Paramétrage, vous devez lui fournir ce qu'on appelle une charge de travail. Une charge de travail est un ensemble d'instructions Transact-SQL qui s'exécutent sur une base de données ou des bases de données que vous voulez régler. L'Assistant Paramétrage du moteur de base de données utilise les fichiers trace, les tables de trace, les scripts Transact-SQL ou les fichiers XML comme entrées de charge de travail lors du réglage des bases de données. Lors de l'utilisation de Amazon RDS, une charge de travail peut être un fichier sur un ordinateur client ou une table de base de données sur une instance de base de données Amazon RDS for SQL Server accessible à votre ordinateur client. Le fichier ou la table doit contenir des requêtes sur les bases de données que vous voulez régler dans un format adapté à la relecture.

Pour que l'Assistant Paramétrage soit le plus efficace, une charge de travail doit être aussi réaliste que possible. Vous pouvez générer un fichier de charge de travail ou une table en exécutant une trace sur votre instance de base de données. Pendant l'exécution d'une trace, vous pouvez simuler une charge sur votre instance de base de données ou exécuter vos applications avec une charge normale.

Il existe deux types de trace : côté client et côté serveur. Une trace côté client est plus facile à configurer et vous pouvez observer les événements de trace capturés en temps réel dans SQL Server Profiler. Une trace côté serveur est plus complexe à configurer et nécessite une tâche de scripting Transact-SQL. De plus, comme la trace est écrite dans un fichier de l'instance de base de données Amazon RDS, l'espace de stockage est utilisé par la trace. Il importe de tracer la quantité d'espace de stockage qu'une trace côté serveur utilise, parce que l'instance de base de données peut entrer dans un état de stockage complet et n'être plus disponible si elle se trouve à court d'espace de stockage.

Pour une trace côté client, quand une quantité suffisante de données de trace a été capturée dans SQL Server Profiler, vous pouvez générer le fichier de charge de travail en enregistrant la trace sur un fichier de votre ordinateur local ou dans une table de base de données d'une instance de base de données accessible à votre ordinateur client. Le principal désavantage de l'utilisation d'une trace côté client est que la trace peut ne pas capturer toutes les requêtes quand elle est soumise à de lourdes charges. Cela pourrait affaiblir l'efficacité de l'analyse exécutée par l'Assistant Paramétrage du moteur de base de données. Si vous devez exécuter une trace soumise à des charges massives et que vous voulez vous assurer qu'elle capture chaque requête pendant une session de trace, vous devez utiliser une trace côté serveur.

Pour une trace côté serveur, vous devez obtenir les fichiers de trace de l'instance de base de données en un fichier de charge de travail adapté ou vous pouvez enregistrer la trace sur une table de l'instance de base de données une fois la trace terminée. Vous pouvez utiliser SQL Server Profiler pour enregistrer la trace sur un fichier de votre ordinateur local ou faire en sorte que l'Assistant Paramétrage lise à partir de la table de trace sur l'instance de base de données.

Exécution d'une trace côté client sur une instance de base de données SQL Server

Pour exécuter une trace côté client sur une instance de base de données SQL Server

1. Démarrez SQL Server Profiler. Il est installé dans le dossier Outils de performance de votre dossier d'instances SQL Server. Vous devez charger ou définir un modèle de définition de trace pour démarrer une trace côté client.

2. Dans le menu du fichier SQL Server Profiler, choisissez New Trace (Nouvelle trace). Dans la boîte de dialogue Connect to Server (Se connecter au serveur), entrez le point de terminaison de l'instance de base de données, le port, l'identifiant principal et le mot de passe de la base de données sur laquelle vous souhaitez exécuter une trace.

3. Dans la boîte de dialogue Propriétés de la trace, entrez un nom de trace et choisissez un modèle de définition de trace. Un modèle par défaut, TSQL_Replay, est fourni avec l'application. Vous pouvez modifier ce modèle pour définir votre trace. Modifiez les événements et les informations relatives aux événements sous l'onglet Sélection des événements de la boîte de dialogue Propriétés de la trace.

   Pour plus d'informations sur les modèles de définition de trace et l'utilisation de SQL Server Profiler pour spécifier une trace côté client, consultez Assistant Paramétrage du moteur de base de données dans la documentation Microsoft.

4. Démarrez la trace côté client et observez les requêtes SQL en temps réel tandis qu'elles s'exécutent sur votre instance de base de données.

5. Sélectionnez Stop Trace (Arrêter la trace) dans le menu Fichier lorsque vous avez terminé la trace. Enregistrez les résultats comme fichier ou comme table de trace sur votre instance de base de données.

Exécution d'une trace côté serveur sur une instance de base de données SQL Server

L'écriture de scripts pour créer une trace côté serveur peut être complexe et au-delà de la portée de ce document. Cette section contient des scripts que vous pouvez utiliser comme exemples. Comme pour une trace côté client, l'objectif est de créer un fichier de charge de travail ou une table de trace que vous pouvez ouvrir à l'aide de l'Assistant Paramétrage du moteur de base de données.

L'exemple abrégé suivant est un script qui démarre une trace côté serveur et capture les détails dans un fichier de charge de travail. La trace s'enregistre initialement sur le fichier RDSTrace.trc du répertoire D:\RDSDBDATA\Log et se réinitialise tous les 100 Mo, si bien que les fichiers de trace suivants se nomment RDSTrace_1.trc, RDSTrace_2.trc, etc.

DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace';
DECLARE @max_file_size BIGINT = 100;
DECLARE @on BIT = 1
DECLARE @rc INT
DECLARE @traceid INT

EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size
IF (@rc = 0) BEGIN
   EXEC sp_trace_setevent @traceid, 10, 1, @on
   EXEC sp_trace_setevent @traceid, 10, 2, @on
   EXEC sp_trace_setevent @traceid, 10, 3, @on
 . . .
   EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler'
   EXEC sp_trace_setstatus @traceid, 1
   END

L'exemple suivant illustre un script qui arrête une trace. Notez qu'une trace créée par le précédent script continue à s'exécuter jusqu'à ce que vous arrêtiez explicitement la trace ou que le processus ne dispose plus d'espace disque suffisant.

DECLARE @traceid INT
SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) 
WHERE property = 5 AND value = 1 AND traceid <> 1 

IF @traceid IS NOT NULL BEGIN
   EXEC sp_trace_setstatus @traceid, 0
   EXEC sp_trace_setstatus @traceid, 2
END 

Vous pouvez enregistrer les résultats de la trace côté serveur sur une table de base de données et utiliser celle-ci comme charge de travail pour l'Assistant Paramétrage à l'aide de la fonction fn_trace_gettable. Les commandes suivantes chargent les résultats de tous les fichiers nommés RDSTrace.trc dans le répertoire D:\rdsdbdata\Log, y compris tous les fichiers de substitution comme RDSTrace_1.trc, dans une table nommée RDSTrace de la base de données active.

SELECT * INTO RDSTrace
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default); 

Pour enregistrer un fichier de substitution spécifique dans une table, par exemple le fichier RDSTrace_1.trc, spécifiez le nom du fichier de substitution et remplacez le dernier paramètre par défaut de fn_trace_gettable par 1.

SELECT * INTO RDSTrace_1
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1); 

Exécution de l'Assistant Paramétrage avec une trace

Une fois que vous créez une trace, comme fichier local ou comme table de base de données, vous pouvez exécuter l'Assistant Paramétrage sur votre instance de base de données. L'utilisation de l'Assistant Paramétrage avec Amazon RDS repose sur le même processus que l'utilisation d'une instance SQL Server autonome et distante. Vous pouvez utiliser l'interface utilisateur de l'Assistant Paramétrage sur votre ordinateur client ou choisir l'utilitaire dta.exe à partir de la ligne de commande. Dans les deux cas, vous devez vous connecter à l'instance de base de données Amazon RDS à l'aide du point de terminaison de l'instance de base de données, et fournir votre nom d'utilisateur maître et votre mot de passe utilisateur maître lors de l'utilisation de l'Assistant Paramétrage.

L'exemple de code suivant illustre l'utilisation de l'utilitaire de ligne de commande dta.exe sur une instance de base de données Amazon RDS avec le point de terminaison dta.cnazcmklsdei.us-east-1.rds.amazonaws.com. L'exemple inclut le nom d'utilisateur principal admin et le mot de passe de l'utilisateur principal test. L'exemple de base de données à régler se nomme ordinateur nommé C:\RDSTrace.trc. L'exemple de code de ligne de commande spécifie également une session de trace nommée RDSTrace1, ainsi que les fichiers de sortie sur l'ordinateur local nommés RDSTrace.sql pour le script de sortie SQL, RDSTrace.txt pour un fichier résultat et RDSTrace.xml pour un fichier XML de l'analyse. Il existe aussi une table d'erreur spécifiée sur la base de données RDSDTA et nommée RDSTraceErrors.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -if C:\RDSTrace.trc -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors 

Voici le même exemple de code de ligne de commande, à ceci près que la charge de travail en entrée est une table de l'instance Amazon RDS distante nommée RDSTrace qui se trouve sur la base de données RDSDTA.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -it RDSDTA.dbo.RDSTrace -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors

Pour obtenir la liste complète des paramètres de ligne de commande de l'utilitaire dta, consultezUtilitaire dta dans la documentation Microsoft.