Comment générer un script de statistiques pour créer une base de données de statistiques uniquement dans SQL Server

  • Article

Dans cet article, vous allez apprendre à générer un script de statistiques à l’aide des métadonnées de base de données pour créer une base de données de statistiques uniquement dans SQL Server.

Version d’origine du produit : SQL Server 2014, SQL Server 2012, SQL Server 2008

Numéro de la base de connaissances d’origine : 914288

Introduction

DBCC CLONEDATABASE est la méthode recommandée pour générer un clone de schéma uniquement d’une base de données afin d’examiner les problèmes de performances. Utilisez la procédure décrite dans cet article uniquement lorsque vous n’êtes pas en mesure d’utiliser DBCC CLONEDATABASE.

L’optimiseur de requête dans Microsoft SQL Server utilise les types d’informations suivants pour déterminer un plan de requête optimal :

  • métadonnées de base de données
  • environnement matériel
  • état de la session de base de données

En règle générale, vous devez simuler tous ces mêmes types d’informations pour reproduire le comportement de l’optimiseur de requête sur un système de test.

Les services de support technique Microsoft peuvent vous demander de générer un script des métadonnées de base de données pour examiner un problème d’optimiseur de requête. Cet article décrit les étapes de génération du script de statistiques et décrit également la façon dont l’optimiseur de requête utilise les informations.

Remarque

Les clés enregistrées dans ces données peuvent contenir des informations d’identification personnelle. Par exemple, si votre table contient une colonne Numéro de téléphone avec une statistique, la valeur clé élevée de chaque étape se trouve dans le script de statistiques généré.

Générer un script pour l’ensemble de la base de données

Lorsque vous générez une base de données clone de statistiques uniquement, il peut être plus facile et plus fiable de générer un script pour l’ensemble de la base de données au lieu de générer des scripts pour des objets individuels. Lorsque vous créez un script pour l’ensemble de la base de données, vous bénéficiez des avantages suivants :

  • Vous évitez les problèmes liés aux objets dépendants manquants qui sont nécessaires pour reproduire le problème.
  • Vous avez besoin de moins d’étapes pour sélectionner les objets nécessaires.

Notez que si vous générez un script pour une base de données et que les métadonnées de la base de données contiennent des milliers d’objets, le processus de script consomme des ressources processeur importantes. Il est recommandé de générer le script pendant les heures creuses, ou vous pouvez utiliser la deuxième option Script d’objets individuels pour générer le script pour des objets individuels.

Pour générer un script pour chaque base de données référencée par votre requête, procédez comme suit :

  1. Ouvrez le SQL Server Management Studio.

  2. Dans la Explorateur d'objets, développez Bases de données, puis recherchez la base de données que vous souhaitez scripter.

  3. Cliquez avec le bouton droit sur la base de données, pointez sur Tâches, puis sélectionnez Générer des scripts.

  4. Dans l’Assistant Script, vérifiez que la base de données correcte est sélectionnée. Cliquez pour sélectionner scripter la base de données entière et tous les objets de base de données, puis sélectionnez Suivant.

  5. Dans la boîte de dialogue Choisir les options de script , sélectionnez le bouton Avancé pour remplacer les paramètres suivants de la valeur par défaut par la valeur répertoriée dans le tableau suivant.

    Option de script Valeur à sélectionner
    Remplissage Ansi Vrai
    Continuer le script en cas d’erreur Vrai
    Générer un script pour les objets dépendants Vrai
    Inclure des noms de contraintes système Vrai
    Classement de script Vrai
    Connexions de script Vrai
    Autorisations au niveau de l’objet de script Vrai
    Statistiques de script Statistiques de script et histogrammes
    Index de script Vrai
    Déclencheurs de script Vrai

    Remarque

    Notez que l’option Script de connexions et l’option Script d’autorisations au niveau de l’objet peuvent ne pas être requises, sauf si le schéma contient des objets appartenant à des connexions autres que dbo.

  6. Sélectionnez OK pour enregistrer les modifications, puis fermez la page Options de script avancées .

  7. Sélectionnez Enregistrer dans un fichier , puis sélectionnez l’option Fichier unique .

  8. Passez en revue vos sélections, puis sélectionnez Suivant.

  9. Sélectionnez Terminer.

Scripter des objets individuels

Vous pouvez uniquement générer un script pour les objets individuels référencés par une requête particulière au lieu de générer un script pour la base de données complète. Toutefois, sauf si tous les objets de base de données ont été créés à l’aide de la WITH SCHEMABINDING clause , les informations de dépendance dans la sys.depends table système peuvent ne pas être toujours précises. Cette inexactitude peut entraîner l’un des problèmes suivants :

  • Le processus de script ne scripte pas un objet dépendant.

  • Le processus de script peut scripter des objets dans l’ordre incorrect. Pour exécuter correctement le script, vous devez modifier manuellement le script généré.

Par conséquent, il n’est pas recommandé de générer des scripts pour des objets individuels, sauf si la base de données contient de nombreux objets et que les scripts prendrait trop de temps. Si vous devez utiliser des objets individuels de script, procédez comme suit :

  1. Dans la SQL Server Management Studio, développez Bases de données, puis recherchez la base de données que vous souhaitez scripter.

  2. Cliquez avec le bouton droit sur la base de données, pointez sur Script de base de données en tant que, pointez sur CREATE To, puis sélectionnez Fichier.

  3. Entrez un nom de fichier, puis sélectionnez Enregistrer.

    Le conteneur de base de données principal sera scripté. Ce conteneur inclut des fichiers, des groupes de fichiers, la base de données et des propriétés.

  4. Cliquez avec le bouton droit sur la base de données, pointez sur Tâches, puis sélectionnez Générer des scripts.

  5. Vérifiez que la base de données appropriée est sélectionnée, puis sélectionnez Suivant.

  6. Dans la boîte de dialogue Choisir les types d’objets , choisissez Sélectionner des objets de base de données spécifiques, puis sélectionnez tous les types d’objets de base de données auxquels la requête problématique fait référence.

    Par exemple, si la requête référence uniquement des tables, sélectionnez Tables. Si la requête fait référence à une vue, sélectionnez Vues et tables. Si la requête problématique utilise une fonction définie par l’utilisateur, sélectionnez Fonctions.

  7. Une fois que vous avez sélectionné tous les types d’objets référencés par la requête, sélectionnez Suivant.

  8. Dans la boîte de dialogue Définir les options de script , sélectionnez le bouton Avancé et remplacez les paramètres suivants par la valeur par défaut par la valeur répertoriée dans le tableau suivant de la page Options de script avancées .

    Option de script Valeur à sélectionner
    Ansi Padding Vrai
    Continuer le script en cas d’erreur Vrai
    Inclure des noms de contraintes système Vrai
    Générer un script pour les objets dépendants Vrai
    Classement de script Vrai
    Connexions de script Vrai
    Autorisations au niveau de l’objet de script Vrai
    Statistiques de script Statistiques de script et histogrammes
    Script USE DATABASE Vrai
    Index de script Vrai
    Déclencheurs de script Vrai

    Remarque

    Notez que les options Script Logins et Script Object Level Permissions peuvent ne pas être requises, sauf si le schéma contient des objets appartenant à des connexions autres que dbo.

  9. Sélectionnez OK pour enregistrer et fermer la page Options de script avancées .

    Une boîte de dialogue s’affiche pour chaque type d’objet de base de données que vous avez sélectionné à l’étape 7.

  10. Dans chaque boîte de dialogue, sélectionnez les tables, vues, fonctions ou autres objets de base de données spécifiques, puis sélectionnez Suivant.

  11. Sélectionnez l’option Script to File (Script to File ), puis spécifiez le même nom de fichier que celui que vous avez entré à l’étape 3.

  12. Sélectionnez Terminer pour démarrer le script.

    Une fois le script terminé, envoyez le fichier de script à l’ingénieur Support Microsoft. L’ingénieur Support Microsoft peut également demander les informations suivantes :

    • Configuration matérielle, y compris le nombre de processeurs et la quantité de mémoire physique existante.

    • Options SET qui étaient actives lorsque vous avez exécuté la requête.

    Notez que vous avez peut-être déjà fourni ces informations en envoyant un rapport SQLDiag ou une trace SQL Profiler. Vous avez peut-être également utilisé une autre méthode pour fournir ces informations.

Utilisation des informations

Les tableaux suivants expliquent comment l’optimiseur de requête utilise ces informations pour sélectionner un plan de requête.

Métadonnées

Option Explication
Contraintes L’optimiseur de requête utilise fréquemment des contraintes pour détecter les contradictions entre la requête et le schéma sous-jacent. Par exemple, si la requête contient la WHERE col = 5 clause et qu’une CHECK (col < 5) contrainte existe sur la table sous-jacente, l’optimiseur de requête sait qu’aucune ligne ne correspondra. L’optimiseur de requête effectue des types similaires de déductions sur la possibilité de null. Par exemple, la WHERE col IS NULL clause est connue pour être true ou false en fonction de la possibilité de null de la colonne et si la colonne provient de la table externe d’une jointure externe. La présence de contraintes FOREIGN KEY est utile pour déterminer la cardinalité et l’ordre de jointure approprié. L’optimiseur de requête peut utiliser les informations de contrainte pour éliminer les jointures ou simplifier les prédicats. Ces modifications peuvent supprimer l’obligation d’accéder aux tables de base.
Statistiques Les informations statistiques contiennent la densité et un histogramme qui montre la distribution de la colonne de début de l’index et de la clé de statistiques. Selon la nature du prédicat, l’optimiseur de requête peut utiliser la densité, l’histogramme ou les deux pour estimer la cardinalité d’un prédicat. Des statistiques à jour sont nécessaires pour obtenir des estimations de cardinalité précises. Les estimations de cardinalité sont utilisées comme entrée pour estimer le coût d’un opérateur. Par conséquent, vous devez disposer d’estimations de cardinalité correctes pour obtenir des plans de requête optimaux.
Taille de la table (nombre de lignes et de pages) L’optimiseur de requête utilise les histogrammes et la densité pour calculer la probabilité qu’un prédicat donné soit vrai ou faux. L’estimation de cardinalité finale est calculée en multipliant la probabilité par le nombre de lignes retournées par l’opérateur enfant. Le nombre de pages dans la table ou l’index est un facteur d’estimation du coût des E/S. La taille de la table est utilisée pour calculer le coût d’une analyse, et elle est utile lorsque vous estimez le nombre de pages accessibles pendant une recherche d’index.
Options de base de données Plusieurs options de base de données peuvent affecter l’optimisation. Les AUTO_CREATE_STATISTICS options et AUTO_UPDATE_STATISTICS déterminent si l’optimiseur de requête crée des statistiques ou met à jour des statistiques obsolètes. Le niveau paramétrage affecte la façon dont la requête d’entrée est paramétrée avant que la requête d’entrée ne soit remise à l’optimiseur de requête. Le paramétrage peut affecter l’estimation de la cardinalité et peut également empêcher la correspondance avec les vues indexées et d’autres types d’optimisations. Le DATE_CORRELATION_OPTIMIZATION paramètre amène l’optimiseur à rechercher des corrélations entre les colonnes. Ce paramètre affecte la cardinalité et l’estimation des coûts.

Environnement

Option Explication
Options SET de session Le ANSI_NULLS paramètre détermine si l’expression NULL = NULL a la valeur true. L’estimation de la cardinalité pour les jointures externes peut changer en fonction du paramètre actuel. En outre, les expressions ambiguës peuvent également changer. Par exemple, l’expression col = NULL s’évalue différemment en fonction du paramètre . Toutefois, l’expression col IS NULL est toujours évaluée de la même façon.
Ressources matérielles Le coût des opérateurs de tri et de hachage dépend de la quantité relative de mémoire disponible pour SQL Server. Par exemple, si la taille des données est supérieure au cache, l’optimiseur de requête sait que les données doivent toujours être mises en file d’attente sur le disque. Toutefois, si la taille des données est beaucoup plus petite que le cache, l’opération est susceptible d’être effectuée en mémoire. SQL Server envisage également différentes optimisations si le serveur a plusieurs processeurs et si le parallélisme n’a pas été désactivé à l’aide d’un MAXDOP indicateur ou de l’option de configuration max degree of parallelism.

Voir aussi