Réduire la charge de paramétrage du serveur de production

S’applique à :SQL ServerAzure SQL Database

L’Assistant Paramétrage du moteur de base de données s’appuie sur l’optimiseur de requête pour analyser une charge de travail et faire des recommandations de réglage. Le fait d'effectuer cette analyse sur le serveur de production augmente la charge du serveur et peut dégrader les performances du serveur pendant la session de paramétrage. Vous pouvez réduire l'impact sur la charge du serveur pendant une session de paramétrage en utilisant un serveur de test en plus du serveur de production.

Utilisation d'un serveur de test par l'Assistant Paramétrage du moteur de base de données

En règle générale, pour utiliser un serveur de test, vous copiez toutes les données du serveur de production vers le serveur de test, paramétrez le serveur de test et implémentez la recommandation sur le serveur de production. Cette méthode élimine le problème d'impact sur les performances du serveur de production, mais elle ne constitue pas la solution la plus efficace. La copie d'une importante quantité de données du serveur de production vers le serveur de test, par exemple, s'avère longue et consomme un nombre substantiel de ressources. En outre, le matériel du serveur de test est rarement aussi puissant que le matériel déployé pour les serveurs de production. Le processus de paramétrage repose sur l'optimiseur de requêtes. En outre, les recommandations qu'il génère sont partiellement basées sur le matériel sous-jacent. Si le matériel du serveur de test et de production n’est pas identique, la qualité de recommandation de l’Assistant Paramétrage du moteur de base de données est réduite.

Pour éviter ces problèmes, l’Assistant Paramétrage du moteur de base de données ajuste une base de données sur un serveur de production en déchargeant la majeure partie de la charge de paramétrage sur un serveur de test. Il procède ainsi en utilisant les informations de configuration matérielle du serveur de production et sans copier réellement les données du serveur de production vers le serveur de test. L’Assistant Paramétrage du moteur de base de données ne copie pas les données réelles du serveur de production vers le serveur de test. Il copie uniquement les métadonnées et les statistiques nécessaires.

Les étapes suivantes expliquent le processus de paramétrage d'une base de données de production sur un serveur de test :

1. Vérifiez que l'utilisateur qui veut utiliser le serveur de test existe sur les deux serveurs.

   Avant de commencer, vérifiez que l'utilisateur qui veut utiliser le serveur de test pour paramétrer une base de données sur le serveur de production existe sur les deux serveurs. Ceci implique de créer l'utilisateur et sa connexion sur le serveur de test. Si vous êtes membre du rôle de serveur fixe sysadmin sur les deux ordinateurs, cette étape n’est pas nécessaire.

2. Paramétrez la charge de travail sur le serveur de test.

   Pour paramétrer une charge de travail sur un serveur de test, vous devez utiliser un fichier d’entrée XML avec l’utilitaire de ligne de commande dta . Dans le fichier d’entrée XML, définissez le nom du serveur de test avec le sous-élément TestServer et définissez les valeurs des autres sous-éléments sous l’élément parent TuningOptions .

   Au cours du paramétrage, l'Assistant Paramétrage du moteur de base de données crée une base de données shell sur le serveur de test. Pour créer cette base de données shell et la paramétrer, l'Assistant Paramétrage du moteur de base de données envoie des appels au serveur de production :

   1. L’Assistant Paramétrage du moteur de base de données importe les métadonnées de la base de données de production vers la base de données shell du serveur de test. Les métadonnées incluent des tables, des index, des vues des procédures stockées, des déclencheurs vides, etc. Ceci permet aux requêtes de charge de travail de s'exécuter par rapport à la base de données shell du serveur de test.

   2. L’Assistant Paramétrage du moteur de base de données importe des statistiques à partir du serveur de production afin que l’optimiseur de requête puisse optimiser avec précision les requêtes sur le serveur de test.

   3. L’Assistant Paramétrage du moteur de base de données importe les paramètres matériels spécifiant le nombre de processeurs et la mémoire disponible du serveur de production pour fournir à l’optimiseur de requête les informations dont il a besoin pour générer un plan de requête.

3. Une fois que l’Assistant Paramétrage du moteur de base de données a terminé le réglage de la base de données de l’interpréteur de commandes de serveur de test, il génère une recommandation de paramétrage.

4. Appliquez la recommandation résultant du paramétrage du serveur de test au serveur de production.

L'illustration suivante montre le scénario du serveur de test et du serveur de production :

Note

La fonctionnalité de paramétrage du serveur de test n’est pas prise en charge dans l’interface utilisateur graphique de l’Assistant Paramétrage du moteur de base de données (GUI).

Exemple

Tout d'abord, vérifiez que l'utilisateur qui veut effectuer le paramétrage existe sur le serveur de test et sur le serveur de production.

Une fois que les informations utilisateur sont copiées sur votre serveur de test, vous pouvez définir votre session de paramétrage de serveur de test dans le fichier d’entrée XML de l’Assistant Paramétrage du moteur de base de données. L’exemple de fichier d’entrée XML suivant montre comment spécifier un serveur de test pour régler une base de données avec l’Assistant Paramétrage du moteur de base de données.

Dans cet exemple, la base de données MyDatabaseName est paramétrée sur MyServerName. Le script Transact-SQL, MyWorkloadScript.sqlest utilisé comme charge de travail. Cette charge de travail contient les événements exécutés par rapport à MyDatabaseName. La plupart des appels de l'optimiseur de requêtes vers cette base de données, qui sont effectués au cours du paramétrage, sont gérés par la base de données shell qui réside sur MyTestServerName. La base de données shell est constituée de métadonnées et de statistiques. Ce processus permet de décharger la surcharge de paramétrage vers le serveur de test. Lorsque l’Assistant Paramétrage du moteur de base de données génère sa recommandation de paramétrage à l’aide de ce fichier d’entrée XML, il doit prendre uniquement en compte les index (<FeatureSet>IDX</FeatureSet>), aucun partitionnement et n’a pas besoin de conserver les structures de conception physique existantes dans MyDatabaseName.

<?xml version="1.0" encoding="utf-16" ?>  
<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/dta">  
  <DTAInput>  
    <Server>  
      <Name>MyServerName</Name>  
      <Database>  
        <Name>MyDatabaseName</Name>  
      </Database>  
    </Server>  
    <Workload>  
      <File>MyWorkloadScript.sql</File>  
    </Workload>  
    <TuningOptions>  
      <TestServer>MyTestServerName</TestServer>  
      <FeatureSet>IDX</FeatureSet>  
      <Partitioning>NONE</Partitioning>  
      <KeepExisting>NONE</KeepExisting>  
    </TuningOptions>  
  </DTAInput>  
</DTAXML>  

Voir aussi

Considérations relatives à l’utilisation de serveurs de test
Référence des fichiers d'entrée XML (Assistant Paramétrage du moteur de base de données)