Fonctionnalité de maintenance statistique (autostats) dans SQL Server

Traductions disponibles Traductions disponibles
Numéro d'article: 195565 - Voir les produits auxquels s'applique cet article
Agrandir tout | Réduire tout

Sommaire

Résumé

Les fonctionnalités de maintenance de statistique nouvellement introduites, AutoStat, peuvent générer indésirable surcharge sur un système de production en effectuant l'une des actions suivantes :
  • Initialisation de statistiques mises à jour pendant les périodes de production lourdes.

    - ou -
  • Lance un nombre excessivement élevé de processus UPDATE STATISTICS à un moment donné dans le temps.
Cet article vise à détailler les conditions dans lesquelles vous pouvez vous attendre à voir autostats généré et UPDATE STATISTICS est en cours d'exécution sur les tables dans une base de données.

Pour informations sur autostats dans SQL Server 2000, consultez «Statistiques utilisés par l'optimiseur de requête dans Microsoft SQL Server 2000» sur le site MSDN Web suivant :
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx


Remarque Si vous utilisez Microsoft SQL Server 2005, consultez le livre blanc Microsoft suivant pour plus d'informations sur comment les statistiques sont utilisées par l'optimiseur de requêtes dans SQL Server 2005 :
http://technet.microsoft.com/en-us/library/cc966419.aspx

Plus d'informations

Des informations générales

SQL Server utilise un optimiseur basé sur les coûts qui peut être extrêmement sensible aux informations statistiques fournies sur les tables et index. Sans les informations statistiques appropriées et à jour, SQL Server peut être contesté pour savoir le meilleur plan d'exécution pour une requête.

Statistiques mises à jour sur chaque table dans SQL Server afin de faciliter l'optimiseur basé sur les coûts décision comprennent les :
  • Nombre de lignes dans la table.
  • Nombre de pages utilisées par la table.
  • Nombre de modifications apportées aux clés de la table depuis la dernière mise à jour les statistiques.
Informations supplémentaires sont stockées pour les index, y compris (pour chaque index) :
  • Un histogramme équi-hauteur sur la première colonne.
  • Densités sur tous les préfixes de colonne.
  • Longueur moyenne de la clé.
Statistiques sur les index sont créés automatiquement chaque fois qu'un nouvel index est généré. En outre, il est désormais possible de créer et gérer les statistiques sur les autres colonnes également.

Pour conserver les informations statistiques de manière aussi à jour que possible, SQL Server apporte AutoStat, qui, par SQL Server suivi des modifications de la table, est capable de mettre à jour automatiquement les statistiques d'une table lorsqu'un certain seuil de modification a été atteint. En outre, SQL Server apporte auto-créer-statistics, qui fait que le serveur pour générer automatiquement toutes les statistiques requises pour l'optimisation précise d'une requête spécifique.

Déterminer si la génération AutoStat est imminente

Comme mentionné ci-dessus, AutoStat actualise automatiquement les statistiques pour une table particulière lorsqu'un «seuil de modification» a été atteint. La colonne sysindexes.rowmodctr maintient un cumulé total de toutes les modifications apportées à une table qui, au fil du temps, peut avoir un impact négatif sur processus décision du processeur de requête. Ce compteur est mis à jour chaque fois qu'un des événements suivants se produit :
  • Insertion d'une seule ligne est effectuée.
  • Une seule ligne suppression est effectuée.
  • Une mise à jour à une colonne indexée est effectuée.
Remarque : TRUNCATE TABLE n'est pas mise à jour rowmodctr.

Après mise à jour de statistiques de la table, la valeur de rowmodctr est réinitialisée à 0 et version de schéma de statistiques de la table est mis à jour.

En outre, dans les situations dans lesquelles plan d'exécution de la procédure stockée est extraite du cache et que le plan est sensible aux statistiques, la version de schéma de statistiques est comparée à la version actuelle. Si les nouvelles statistiques sont disponibles, le plan de la procédure stockée est recompilé.

L'algorithme de base pour la mise à jour automatique des statistiques est :
  • Si la cardinalité d'une table est inférieure à six et la table se trouve dans la base de données tempdb, automatiquement mise à jour avec chaque six modifications de la table.
  • Si la cardinalité d'une table est supérieure à 6 mais inférieure ou égale à 500, à jour état toutes 500 modifications.
  • Si la mise à la cardinalité d'une table est supérieure à 500, jour de statistiques lorsque (500 + 20 pour cent de la table) des changements ont eu lieu.
  • Pour les variables de table, les modifications de cardinalité ne déclenche pas la mise à jour automatique des statistiques.
Remarque : dans ce sens plus strict, SQL Server comptabilise cardinalité que le nombre de lignes de la table.

Remarque : en plus de cardinalité, la sélectivité du prédicat affecte également AutoStats génération. Cela signifie que statistiques peuvent ne pas être afer mis à jour toutes 500 modifications si cardinalité ont été < 500 ou chaque 20 % de modifications si cardinalité ont été > 500. Une échelle facteur (valeur est comprise entre 1 et 4, 1 et 4 inclus) est générée en fonction de la sélectivité et un produit de ce facteur et le nombre de modifications obtenues à partir de l'algorithme serait le nombre de modifications requises pour la génération de AutoStats.

L'algorithme ci-dessus peut être résumée sous forme d'une table :
_________________________________________________________________________________
 Table Type | Empty Condition | Threshold When Empty |Threshold When Not Empty 
_________________________________________________________________________________
 Permanent  | < 500 rows      | # of Changes >= 500  | # of Changes >= 500 + (20% of Cardinality)
___________________________________________________________________________
 Temporary  | < 6 rows        | # of Changes >= 6    | # of Changes >= 500 + (20% of Cardinality)
___________________________________________________________________________
Table
Variables   | Change in cardinality does not affect AutoStats generation.
___________________________________________________________________________
ci-dessous sont deux exemples pour illustrer ce concept :

Exemple 1

Envisagez la table authors de la base de données pubs, qui contient 23 lignes et a deux index. L'index unique ordonné en clusters, UPKCL_auidind, est indexé sur une colonne, au_id, et un index composite non ordonné en clusters, aunmind, a été créé sur les colonnes au_lname et au_fname. Étant donné que cette table contient moins de 500 lignes, AutoStat commencera suite 500 modifications aux données de la table. Les modifications peuvent être une de 500 ou plus insère, supprime, passe à une colonne indexée comme au_lname ou n'importe quelle combinaison de ces éléments.

Vous pouvez, par conséquent, prévoir lorsqu'UPDATE STATISTICS va être lancée en surveillant la valeur sysindexes.rowmodctr, qui sera incrémentée lors de chaque mise à jour. Lorsqu'il atteint ou dépasse 500, vous pouvez vous attendre à ce que UPDATE STATISTICS à démarrer.

Exemple 2

Prenons une deuxième table, t2, ce qui a une cardinalité de 1 000. Pour les tables de plus de 500 lignes, SQL Server va UPDATE STATISTICS lorsque (500 + 20 pour cent) a été modifiée. Cette les mathématiques, 20 pour cent de 1 000 est 200, afin que vous pouvez vous attendre à voir AutoStat Démarrer après que environ 700 modifications ont été apportées à la table.

Automatisation de détermination des statistiques automatiques

Pour automatiser la détermination de lorsque AutoStat sera exécuté, interroge la table sysindexes et identifier les modifications de la table atteignent le point de départ. Voici un algorithme de base pour ce faire :
   if (sysindexes.rows > 500)
      if (sysindexes.rows * 0.20 >= sysindexes.rowmodctr && production
      hours) //500 change leeway
         begin
            disable autostats
            log autostats disable
         end
      else
         begin
            stats ok
         end
   else
      if (sysindexes.rowmodctr >= 425) //75 change leeway
         begin
            disable autostats
            log autostats disable
         end
				

Vous a pu planifier ultérieurement un travail pour effectuer les opérations suivantes :
  • Exécutez UPDATE STATISTICS sur toutes les tables pour lesquelles vous deviez les désactiver au cours de la journée.

    - et -
  • Réactivez AutoStat, car le compteur de modification de chaque table va ont été réinitialisé à 0 lors de l'exécution d'UPDATE STATISTICS.

Contrôle si UPDATE STATISTICS sont exécutées sur une table

La solution la plus évidente à cette question, lorsque AutoStat s'est avérée Problematic, consiste à désactiver la génération de statistiques automatique, laissant ainsi les administrateurs de base de données libres de planifier UPDATE STATISTICS pendant les heures moins intrusives. Cela est possible à l'aide de l'instruction UPDATE STATISTICS ou la procédure sp_autostats stockées. La syntaxe de l'instruction UPDATE STATISTICS est la suivante :
   UPDATE STATISTICS <table>...with NORECOMPUTE
				

La syntaxe de la procédure sp_autostats stockées est la suivante :
sp_autostats <table_name>, <stats_flag>, <index_name>
où <stats_flag> est "on" ou "off".

Vous pouvez également utiliser sp_dboption pour désactiver l'occurrence automatique d'UPDATE STATISTICS ou CREATE STATISTICS sur un niveau par base de données :
sp_dboption <dbname>, 'auto update statistics' < sur | désactiver >

- ou -

sp_dboption <dbname>, 'automatiquement créer des statistiques,' < sur | désactiver >

Contrôle du nombre de processus simultanés UPDATE STATISTICS

Actuellement, de désactivation AutoStat pour des tables spécifiques, il n'est pas possible de configurer le nombre d'instructions UPDATE STATISTICS automatiques en cours d'exécution simultanément (DCR 51539 a été déposée pour cela). Le serveur est le cas, toutefois, limiter le nombre de processus simultanés UPDATE STATISTICS à quatre par processeur.

Déterminer quand des statistiques automatiques sont en cours d'exécution

Vous pouvez utiliser l'indicateur de trace 205 pour signaler lorsqu'une procédure stockée DEPENDANT de statistiques est être recompilée en AutoStat. Cet indicateur de suivi écrire des messages suivants dans le journal des erreurs :
1998-10-15 11:10:51.98 spid9 recompilation émise : NomProc : sp_helpindex
LineNo : 75 StmtNo : 29
Lorsque l'indicateur de trace 205 est activé, le message suivant encadrant également le message AutoStat 8721 lors de la mise à jour des statistiques. Le message d'ouverture de la tranche peut être distingué par la valeur RowModCnt qui sera supérieure à 0. Le crochet fermant, après UPDATE STATISTICS, aura une valeur RowModCnt 0 :
Changement de schéma spid8 11:38:43.68 1998-10-15 : ID de BDD Tbl: 7 ObjId :
RowModCnt 133575514 : RowModLimit 60500 : 60499
Pour ce message, «RowModCnt» est le nombre total de modifications de la table. Le seuil est de «RowModLimit» qui, en cas de dépassement, entraîne l'exécution d'une instruction de UPDATE STATISTICS pour la table.

Il est également possible d'activer l'indicateur de trace 8721, ce qui provoque le vidage d'informations dans le journal des erreurs lorsque AutoStat a été exécuté. Voici un exemple du type de message que vous pouvez vous attendre à voir :
1998-10-14 16:22:13.21 spid13 AUTOSTATS : UPDATED Tbl: [auteurs]
Lignes : 23 Mods : Bound 501 : durée 500 : 47ms UpdCount: 2
Pour ce message, «Mods» sont le nombre total de modifications de la table. Le seuil de modification est "Lié", "Durée" est la durée pendant laquelle l'instruction UPDATE STATISTICS nécessaire pour terminer et «UpdCount» est le nombre de statistiques mises à jour.

Vous pouvez également utiliser le Générateur de profils SQL Server pour identifier lors de l'exécution des instructions UPDATE STATISTICS. Pour ce faire, effectuez les opérations suivantes :
  1. Dans le menu Générateur de profils, cliquez sur Outils, puis cliquez sur Options.
  2. Sous l'onglet Général, accédez à des événements, puis sélectionnez Toutes les classes d'événements.
  3. Définir une nouvelle trace et sous événements, sélectionnez divers, sélectionnez le sub-event Les statistiques de mise à jour automatique.
Remarque : Si grand nombre de statistiques mis à jour par AutoStat, un grand nombre de messages peut être écrites dans le journal des erreurs. Tester soigneusement ces indicateurs de trace avant de les utiliser sur n'importe quel production ou d'un serveur critique dans le cas contraire.

Verrous de schéma

SQL Server utilise deux types de verrous de schéma, qui sont prises lorsqu'il met à jour les statistiques d'une table :
   Sch-S: Schema Stability Lock
   ----------------------------
   This lock ensures that a schema element, such as a table or index, will
   not be dropped while any session holds a schema stability lock on the
   schema element.

   Sch-M-UPD-STATS: Schema Modification Lock
   -----------------------------------------
   This is a non-blocking lock that is used by the system to ensure that
   only one automatic UPDATE STATISTICS process is run against a table at
   any given point in time. The sp_lock stored procedure will report this
   lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M.
				
vous pouvez afficher ces verrous en exécutant sp_lock ou en sélectionnant à partir de la table syslockinfo.

Propriétés

Numéro d'article: 195565 - Dernière mise à jour: vendredi 2 novembre 2007 - Version: 7.6
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 7.0 Standard
  • Microsoft SQL Server 2000 Édition Développeur
  • Microsoft SQL Server 2000 Standard
  • Microsoft SQL Server 2000 Édition Entreprise
  • Microsoft SQL Server 2000 Édition Personelle
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Enterprise Edition 64-bit
Mots-clés : 
kbmt kbinfo KB195565 KbMtfr
Traduction automatique
IMPORTANT : Cet article est issu du système de traduction automatique mis au point par Microsoft (http://support.microsoft.com/gp/mtdetails). Un certain nombre d?articles obtenus par traduction automatique sont en effet mis à votre disposition en complément des articles traduits en langue française par des traducteurs professionnels. Cela vous permet d?avoir accès, dans votre propre langue, à l?ensemble des articles de la base de connaissances rédigés originellement en langue anglaise. Les articles traduits automatiquement ne sont pas toujours parfaits et peuvent comporter des erreurs de vocabulaire, de syntaxe ou de grammaire (probablement semblables aux erreurs que ferait une personne étrangère s?exprimant dans votre langue !). Néanmoins, mis à part ces imperfections, ces articles devraient suffire à vous orienter et à vous aider à résoudre votre problème. Microsoft s?efforce aussi continuellement de faire évoluer son système de traduction automatique.
La version anglaise de cet article est la suivante: 195565
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com