none
Update des Statistiques sur colonnes indexées - SQL Server 2014 RRS feed

  • Question

  • Bonjour,

    J'ai une base de données OLTP SQL 2014 dont l'option Auto Update Statistics est à True. Or je constate que depuis plusieurs mois une statistique nommée _WA_Sys_...  (donc créé par le système) n'est pas mise à jour. Cette statistique concerne une colonne qui est indexée (index Non Unique, Non-Clustered). Il y a donc aussi une statistique dédiée à cette index qui, elle, est bien mise à jour (au moins une fois par jour)

    En explorant les autres statistiques, j'ai constaté que pour les colonnes indexées (donc ayant une statistique dédiée qui a pour nom le nom de l'index) je n'avais pas de statistique système en _WA_Sys correspondante.

    Ayant eu des ralentissements serveur sur cette base lors de recherches sur la colonne en cause, je me demande si les 2 statistiques (l'une à jour, l'autre pas à jour) n'y ont pas été pour quelque chose.

    Donc pour résumer mes questions:

    - Pourquoi la statistique système n'est pas mise à jour alors que l'Auto Update Statistics est activé  ?

    - Pourquoi j'ai 2 statistiques (l'une système, l'autre lié à l'index) portant sur la même colonne ?

    Merci pour votre réponse

    Pierre

    vendredi 6 novembre 2015 09:06

Toutes les réponses

  • Pourquoi la statistique système n'est pas mise à jour alors que l'Auto Update Statistics est activé  ?

    Depuis SQL Server 2005, par défaut quand la mise à jour auto est activé, voici quand l'update statistics se déclenche sur une statistics :

    • Après les 1eres insertions dans la table,
    • Quand la table vient de passer les 500 enregistrements,
    • Quand la table a plus de 500 lignes et qu'il y a eut 20% de modification de la valeur de la colonne.

    Avec l'intégration du traceflag 2371, le pourcentage de modification change en fonction du nombre de ligne de la table :

    Pourquoi j'ai 2 statistiques (l'une système, l'autre lié à l'index) portant sur la même colonne ?

    Si l'option auto create statistics est à ON, on a 1 statistique par colonne (déjà utilisé comme filtre dans une requête) et 1 statistiques par index. Les statisticspar index est une statistics combinant les colonnes de l'index.

    • Proposé comme réponse Grégory_Nail lundi 9 novembre 2015 08:43
    vendredi 6 novembre 2015 09:47
  • Le trace flag 2371 n'est pas activé sur l'instance. Ce qui veut dire, à priori, que le seuil est à 20%. Mais cela ne m'explique pas pourquoi l'une des 2 statistiques est mise à jour (= le seuil est dépassé) et l'autre pas. Alors que ces 2 statistiques pointent vers la même colonne. 

    La seule différence que je vois c'est dans la vue détail des propriétés des statistiques

    Pour la statistic de l'index (sur la colonne reference_produit)  j'ai ça:

    All Density                     Average Length                  Columns                        
    ------------------------------------------------------------------------------------------------------------------------

    0.0001016467                    11.4127                         reference_produit              
    0.0001016467                    20.92386                        reference_produit, id_produit  
    2.304625E-05                    30.63238                        reference_produit, id_produit, num_serie
    3.080381E-08                    38.63238                        reference_produit, id_produit, num_serie, Date_fabrication

    Les colonnes id_produit, num_serie et Date_fabrication ne font pas partie de l'index. Mais je demande si l'index à l'origine n'avait pas été créé avec ces colonnes (cela dit je l'ai reconstruit plus d'une fois)

    Alors que sur la statistic système j'ai ça:

    All Density                     Average Length                  Columns                        
    ------------------------------------------------------------------------------------------------------------------------------
    9.556411E-07                    11.48959                        reference_produit

    vendredi 6 novembre 2015 11:05
  • Si le seuil est dépassée, que la mise à jour auto est activée mais qu'une statistique n'est pas mis à jour par le moteur SQL, ça signifie qu'elle n'est pas utilisé. Aujourd'hui, lorsque dans vos requêtes vous filtrez sur la colonne reference_produit, il y a surement utilisation de l'index (car 1er colonne dans la définition de l'index) et donc c'est la statistique de la colonne qu'est utilisée.

    vendredi 6 novembre 2015 13:32
  • Le seul de 20% + 500 (ou les autres conditions pour une petite table) ne font qu'invalider la statistique. Ce n'est seulement que lorsque l'on veut compiler une requête et que l'optimiseur va vouloir utiliser une stat que celle ci va être mise à jour.

    Il se peut donc que la stat ne soit pas utilisée par l'optimiseur.

    mais ... car il y a un mais.

    Contrairement aux versions précédentes, SQL 2014 ne met pas TOUTES les stats à jour, contrairement aux versions antérieurs qui mettaient à jour à la fois les stats de l'index et les statistiques auto générées.

    les tests que j'ai pu mener montrent que seule la stat auto générée est mise à jours dans le SQL 2014. Il convient donc de temps en temps de dropper ces statistiques auto générées. Si besoin, elles seront de toute manière recréées.

    je prépare un billet sur le sujet, mais en attendant, j'ai animé une session sur le sujet lors des JSS 2013 (je crois). Les slides et démos doivent être disponibles sur le site du GUSS.

    cdlt

    Christophe


    Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

    mardi 22 décembre 2015 15:58