Notes
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier des répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de modifier des répertoires.
Dans cet article, vous allez apprendre à générer un script de statistiques à l’aide de métadonnées de base de données pour créer une base de données de statistiques uniquement dans SQL Server.
Version du produit d’origine : SQL Server
Numéro de base de connaissances d’origine : 914288
Présentation
DBCC CLONEDATABASE est la méthode préférée pour générer un clone de schéma uniquement d’une base de données pour examiner les problèmes de performances. Utilisez la procédure 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 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 à suivre pour générer le script de statistiques et décrit également comment 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 sur celle-ci, la valeur clé élevée de chaque étape se trouve dans le script de statistiques généré.
Scripter l’ensemble de la base de données
Lorsque vous générez une base de données clone uniquement des statistiques, il peut être plus facile et plus fiable de scripter l’ensemble de la base de données au lieu de générer des scripts d’objets individuels. Lorsque vous scriptez 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 requis 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 scripter chaque base de données référencée par votre requête, procédez comme suit :
Ouvrez SQL Server Management Studio.
Dans l’Explorateur d’objets, développez Bases de données, puis recherchez la base de données que vous souhaitez scripter.
Cliquez avec le bouton droit sur la base de données, pointez sur Tâches, puis sélectionnez Générer des scripts.
Dans l’Assistant Script, vérifiez que la base de données correcte est sélectionnée. Cliquez pour sélectionner l’intégralité de la base de données script et tous les objets de base de données, puis sélectionnez Suivant.
Dans la boîte de dialogue Choisir les options de script, sélectionnez le bouton Avancé pour modifier les paramètres suivants de la valeur par défaut à 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 Connexions de script et l’option Autorisations au niveau de l’objet script peuvent ne pas être requises, sauf si le schéma contient des objets appartenant à des connexions autres que dbo.
Sélectionnez OK pour enregistrer les modifications, puis fermez la page Options de script avancées.
Sélectionnez Enregistrer dans fichier et sélectionnez l’option Fichier unique.
Passez en revue vos sélections, puis sélectionnez Suivant.
Sélectionnez Terminer.
Script d’objets individuels
Vous ne pouvez scripter que les objets individuels référencés par une requête particulière au lieu de scripter 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 toujours être 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 le script correctement, vous devez modifier manuellement le script généré.
Par conséquent, il n’est pas recommandé de générer un script d’objets individuels, sauf si la base de données comporte de nombreux objets et que le script prend autrement trop de temps. Si vous devez utiliser des objets individuels de script, procédez comme suit :
Dans SQL Server Management Studio, développez Bases de données, puis recherchez la base de données que vous souhaitez scripter.
Cliquez avec le bouton droit sur la base de données, pointez sur Script Database As, pointez sur CREATE To, puis sélectionnez Fichier.
Entrez un nom de fichier, puis sélectionnez Enregistrer.
Le conteneur de base de données principal est scripté. Ce conteneur inclut des fichiers, des groupes de fichiers, la base de données et les propriétés.
Cliquez avec le bouton droit sur la base de données, pointez sur Tâches, puis sélectionnez Générer des scripts.
Vérifiez que la base de données correcte est sélectionnée, puis sélectionnez Suivant.
Dans la boîte de dialogue Choisir des 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 référencés par la requête problématique.
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 Functions.
Lorsque vous avez sélectionné tous les types d’objets référencés par la requête, sélectionnez Suivant.
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 dans 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 BASE DE DONNÉES Vrai Index de script Vrai Déclencheurs de script Vrai Remarque
Notez que les options Connexions de script et Autorisations au niveau de l’objet de script peuvent ne pas être requises, sauf si le schéma contient des objets appartenant à des connexions autres que dbo.
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.
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.
Sélectionnez l’option Script vers fichier , puis spécifiez le même nom de fichier que celui que vous avez entré à l’étape 3.
Sélectionnez Terminer pour démarrer le script.
Une fois le script terminé, envoyez le fichier de script au Support Microsoft Engineer. 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 qui existe.
Options SET actives lors de l’exécution de 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
| Choix | 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 correspond. L’optimiseur de requête effectue des types similaires de déductions sur la nullabilité. Par exemple, la WHERE col IS NULL clause est connue pour être true ou false en fonction de la nullabilité 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 des informations de contrainte pour éliminer les jointures ou simplifier les prédicats. Ces modifications peuvent supprimer la nécessité d’accéder aux tables de base. |
| Statistiques | Les informations sur les statistiques contiennent une densité et un histogramme qui indique la distribution de la colonne principale 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. Les statistiques à jour sont requises pour des estimations précises de cardinalité. 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 de bonnes estimations de cardinalité 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 de la table ou de 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 la base de données | Plusieurs options de base de données peuvent affecter l’optimisation. Les AUTO_CREATE_STATISTICS options et AUTO_UPDATE_STATISTICS les options affectent si l’optimiseur de requête crée de nouvelles statistiques ou met à jour les statistiques obsolètes. Le niveau de paramétrage affecte la façon dont la requête d’entrée est paramétrée avant la remise de la requête d’entrée à 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 entraîne la recherche de corrélations entre les colonnes par l’optimiseur. Ce paramètre affecte la cardinalité et l’estimation des coûts. |
Environnement
| Choix | Explication |
|---|---|
| Options SET de session | Le ANSI_NULLS paramètre affecte si l’expression NULL = NULL est évaluée comme true. L’estimation de 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 évalue différemment en fonction du paramètre. Toutefois, l’expression col IS NULL évalue toujours 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 pool 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 prend également en compte 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. |