Statistiques non créées

**zinzineti** · 21/09/2010, 16h36

Je repose ma question

Je constate que le moteur de base de données décide de ne pas créer de statistiques sur la colonne creation_date de la table T_TEST !
Sur les autres colonnes, les statistiques sont bien crées !
1.) Comment justifier ce choix du moteur ?
2.) Dans quel(s) cas le moteur de base de données ferme les yeux sur la création des statistiques sur une colonne ?

Voici la table T_TEST

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--Création de la table
SET NOCOUNT ON 
IF OBJECT_ID('dbo.T_TEST') IS NOT NULL 
DROP TABLE dbo.T_TEST 
CREATE TABLE dbo.T_TEST 
( 
  id int identity(1,1), 
  val varchar(10), 
  creation_date datetime 
)
 
--Chargement d'1 million de lignes dans la table 
DECLARE @counter int; 
SET @counter = 1; 
WHILE @counter <= 1000000 
  BEGIN 
  INSERT INTO T_TEST(val,creation_date) VALUES(convert(varchar(10),(LEFT(convert(bigint,RAND()*10000000),6))),getdate()); 
  SET @counter = @counter + 1 
END;

Pour mettre en évidence l’absence de statistiques sur la colonne creation_date je fais :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DBCC SHOW_STATISTICS ('dbo.T_TEST',creation_date)

et SSMS me renvoie le message d'erreur 2767:

Msg 2767, Level 16, State 1, Line 1
Impossible de localiser les statistiques 'create_date' dans le catalogue système.
Exécution de DBCC terminée. Si DBCC vous a adressé des messages d'erreur, contactez l'administrateur système.

Merci de m'éclairer

**SQLpro** · 21/09/2010, 16h49

Les stats ne sont créées que si des requêtes vont portées sur cette colonne.

A +

**zinzineti** · 21/09/2010, 17h09

Envoyé par SQLpro

Les stats ne sont créées que si des requêtes vont portées sur cette colonne.
A +

Des requêtes concernant cette colonne sont bien exécuter mais cela ne change rien. j'ai toujours le même d'erreur

Msg 2767, Level 16, State 1, Line 1
Impossible de localiser les statistiques 'create_date' dans le catalogue système.
Exécution de DBCC terminée. Si DBCC vous a adressé des messages d'erreur, contactez l'administrateur système.

A l'exécution de la commande

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DBCC SHOW_STATISTICS ('dbo.T_TEST',create_date)

**SQLpro** · 21/09/2010, 17h28

Les statistiques auto créée ne portent pas le nom de la colonne, mais un nom en "_WA_Sys_00000004_00000005"
Tu peut les voir avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM sys.stats

A +

**zinzineti** · 21/09/2010, 17h51

Envoyé par SQLpro

Les statistiques auto créée ne portent pas le nom de la colonne, mais un nom en "_WA_Sys_00000004_00000005"
Tu peut les voir avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM sys.stats

A +

D'accord, mais comment savoir qu'un _WA_Sys_0000..... concerne tel ou tel autre colonne ?

Et pourquoi mon

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DBCC SHOW_STATISTICS ('dbo.T_TEST',creation_date)

me renvoie un message d'erreur ?

Alors que pour les autres colonnes tout se passe bien ?

**zinzineti** · 21/09/2010, 21h21

Envoyé par zinzineti

D'accord, mais comment savoir qu'un _WA_Sys_0000..... concerne tel ou tel autre colonne ?

J'ai répondu très vite, voici comment obtenir les statistiques auto et leur colonne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT OBJECT_NAME(s.object_id) AS [Nom_Table]
    ,COL_NAME(sc.object_id, sc.column_id) AS [Nom_Colonne]
    ,s.name AS [Nom_Statistique]
    , STATS_DATE (s.object_id,s.stats_id) 	AS [Date MAJ des statistiques]
FROM sys.stats s INNER JOIN sys.stats_columns sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id 
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1 
AND s.name like '_WA%' -- Statistique automatique 
ORDER BY [Nom_Table];

Mais ma question initiale demeure.
Pourquoi la commande :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DBCC SHOW_STATISTICS ('dbo.T_TEST',create_date)

me renvoie cette erreur :

Msg 2767, Level 16, State 1, Line 1
Impossible de localiser les statistiques 'create_date' dans le catalogue système.
Exécution de DBCC terminée. Si DBCC vous a adressé des messages d'erreur, contactez l'administrateur système.

Pourquoi le moteur ne crée pas des stats sur la colonne create_date ?

**kagemaru** · 21/09/2010, 22h10

Pourquoi le moteur ne crée pas des stats sur la colonne create_date ?

Parce qu'elle n'a pas été utilisée comme prédicat dans aucune des requêtes (si on se réfère à ton post précédent). Et parce que les colonnes incluses n'ont pas de stats générées à la création d'un index nc.

**elsuket** · 22/09/2010, 07h38

Bonjour,

Vous pouviez agrémenter votre requête pour obtenir les colonnes qui participent à la statistique :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT        S.name AS statistic_name
        , C.name AS column_name
FROM        sys.stats AS S
INNER JOIN    sys.stats_columns AS SC
            ON S.object_id = SC.object_id
            AND S.stats_id = SC.stats_id
INNER JOIN    sys.columns AS C
            ON SC.object_id = C.object_id
            AND SC.column_id = C.column_id
INNER JOIN    sys.tables AS T
            ON T.object_id = C.object_id
WHERE        T.name = 'maTable'
ORDER BY    S.name
        , C.name
        , SC.stats_column_id

Les statistiques créées automatiquement ne le sont que sur une seule colonne.
Donc si aucune de vos requêtes ne filtre seulement sur celle-ci, et que le filtre de votre requête contient une colonne plus sélective que create_date, l'optimiseur choisira probablement celle-ci pour calculer le plan ...

@++

**SQLpro** · 22/09/2010, 10h55

Envoyé par zinzineti

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT OBJECT_NAME(s.object_id) AS [Nom_Table]
    ,COL_NAME(sc.object_id, sc.column_id) AS [Nom_Colonne]
    ,s.name AS [Nom_Statistique]
    , STATS_DATE (s.object_id,s.stats_id) 	AS [Date MAJ des statistiques]
FROM sys.stats s INNER JOIN sys.stats_columns sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id 
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1 
AND s.name like '_WA%' -- Statistique automatique 
ORDER BY [Nom_Table];

Ne te fie pas au nom _WA... mais prend la valeur de la colonne is_hypothetical.

A +

**zinzineti** · 22/09/2010, 11h30

Envoyé par kagemaru

Parce qu'elle n'a pas été utilisée comme prédicat dans aucune des requêtes (si on se réfère à ton post précédent). Et parce que les colonnes incluses n'ont pas de stats générées à la création d'un index nc.

YES

c'est bien ça et le BOL le dit clairement dans le paragraphe "Quand créer des statistiques", je cite le BOL :
1. L'optimiseur de requête crée des statistiques pour les index de tables ou de vues lors de la création des index. Ces statistiques sont créées sur les colonnes de clés de l'index

C'est qui explique que la commande

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
DBCC SHOW_STATISTICS ('dbo.T_TEST',val)

marche sans problème, vu que j'avais créé un index ayant comme clé la colonne val

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 CREATE INDEX IXNC_val_INCLUDE_creationdate_id ON T_TEST (val) INCLUDE (creation_date,id)

2.L'optimiseur de requête crée des statistiques pour les colonnes uniques des prédicats de requête lorsque l'option AUTO_CREATE_STATISTICS est activée

ce qui explique pourquoi la commande

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
DBCC SHOW_STATISTICS ('dbo.T_TEST',id)

marche bien. Parce que j'avais fait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UPDATE dbo.T_TEST SET val = '0' WHERE dbo.T_TEST.id <=10000

Et jusque là effectivement il n'y avait pas de requête avec la colonne create_date comme prédicat !

c'est pourquoi SSMS me renvoie le message d'erreur 2767.

Maintenant, quand je fais par exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT TOP 10 * 
FROM T_TEST
WHERE creation_date > '20100917'

Je constate que les statistiques sont créées sur la colonne
creation_date et la commande

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
DBCC SHOW_STATISTICS ('dbo.T_TEST',creation_date )

marche bien.

Mais pourquoi le fait de créer par exemple les index

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CREATE INDEX IXNC_val_id_creationdate ON T_TEST (val,id,creation_date) 
CREATE INDEX IXNC_val_creationdate_id ON T_TEST (val,creation_date,id)

où id, val et create_date sont les clés de l'index ne provoque pas la création de statistiques sur chacune des index ?! Parce que si on se refère à ce que dit le BOL dans mon 1. il devrait y avoir création de stats sur les colonnes clés de l'index ?!

**zinzineti** · 22/09/2010, 11h48

Envoyé par elsuket

Bonjour,

Vous pouviez agrémenter votre requête pour obtenir les colonnes qui participent à la statistique :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT        S.name AS statistic_name
        , C.name AS column_name
FROM        sys.stats AS S
INNER JOIN    sys.stats_columns AS SC
            ON S.object_id = SC.object_id
            AND S.stats_id = SC.stats_id
INNER JOIN    sys.columns AS C
            ON SC.object_id = C.object_id
            AND SC.column_id = C.column_id
INNER JOIN    sys.tables AS T
            ON T.object_id = C.object_id
WHERE        T.name = 'maTable'
ORDER BY    S.name
        , C.name
        , SC.stats_column_id

Oui ta requête renvoie effectivement les différentes colonnes et les stats correspondant. Mais le cas de ta requête le référentiel est la table

Par contre pour la requête que j'ai proposé le référentiel est la base .

Donc c'est deux visions des choses

et je suis tenté de dire que ta requête est INCLUSE dans la mienne

Envoyé par elsuket

Les statistiques créées automatiquement ne le sont que sur une seule colonne. Donc si aucune de vos requêtes ne filtre seulement sur celle-ci, et que le filtre de votre requête contient une colonne plus sélective que create_date, l'optimiseur choisira probablement celle-ci pour calculer le plan ...
@++

D'accord, mais le BOL dit ceci :

L'optimiseur de requête crée des statistiques pour les index de tables ou de vues lors de la création des index. Ces statistiques sont créées sur les colonnes de clés de l'index.

Dans mon exemple j'avais bien créé les index suivants :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CREATE INDEX IXNC_val_id_creationdate ON T_TEST (val,id,creation_date) 
CREATE INDEX IXNC_val_creationdate_id ON T_TEST (val,creation_date,id)

Où val, id et create_date constituent la clé de l'index.

Ma question : Pourquoi dans mon cas l'optimiseur ne crée pas des stats sur chacune des colonnes constituant la clé de l'index ???

Merci de m'éclairer

**mikedavem** · 22/09/2010, 11h57

En regardant la commande DBCC SHOW_STATISTICS sur les index concernés tu comprendras.

Il faut regarder la partie DENSITY_VECTOR

Les statistiques permettent de connaître les valeurs distinctes de chaque préfixe de colones composant la clé. Dans ton cas pour l'index IXNC_val_id_creationdate :

- (val)
- (val,id)
- (val,id,creation_date)

Un index étant vectorisé au départ il n'y a aucun intérêt à avoir les statistiques de chaque colonne seule. Ce qui est intéressant et utile pour le moteur est de connaître les statistiques de l'index (donc de connaître la répartition des valeurs des colonnes composant l'index dans son ensemble).

++

**zinzineti** · 22/09/2010, 12h06

Envoyé par SQLpro

Ne te fie pas au nom _WA... mais prend la valeur de la colonne is_hypothetical.
A +

Je n'ai pas bien compris ta proposition sur la colonne is_hypothetical.

Par ailleurs, le BOL dit ceci à propos de l'index hypothétique (is_hypothetical = 1)
L'index hypothétique ne peut être utilisé directement comme un chemin d'accès aux données. Les index hypothétiques conservent des statistiques au niveau des colonnes et sont gérés et utilisés par l'Assistant Paramétrage du moteur de base de données.
Donc ces index hypothétiques sont gérés par Assistant Paramétrage du moteur de base de données, ils ne m’intéressent donc pas !? Non ?

**zinzineti** · 22/09/2010, 12h35

Envoyé par mikedavem

En regardant la commande DBCC SHOW_STATISTICS sur les index concernés tu comprendras.

Il faut regarder la partie DENSITY_VECTOR

Les statistiques permettent de connaître les valeurs distinctes de chaque préfixe de colones composant la clé. Dans ton cas pour l'index IXNC_val_id_creationdate :

- (val)
- (val,id)
- (val,id,creation_date)

Un index étant vectorisé au départ il n'y a aucun intérêt à avoir les statistiques de chaque colonne seule. Ce qui est intéressant et utile pour le moteur est de connaître les statistiques de l'index (donc de connaître la répartition des valeurs des colonnes composant l'index dans son ensemble).

++

C'est ce que je pensais aussi. Comme la clé de l'index est une clé composite. Le moteur crée les stats sur cette clé composite.

**SQLpro** · 22/09/2010, 13h50

Envoyé par zinzineti

Je n'ai pas bien compris ta proposition sur la colonne is_hypothetical.

Je me suis gouré, c'est auto_created = 1 => _WA_Sys...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM sys.stats WHERE auto_created = 1

A +

**elsuket** · 22/09/2010, 14h01

Oui ta requête renvoie effectivement les différentes colonnes et les stats correspondant. Mais le cas de ta requête le référentiel est la table

Par contre pour la requête que j'ai proposé le référentiel est la base .

Je ne comprend pas ... Les statistiques ne sont ni sur une base de données, ni sur une table, mais sur des colonnes.
Il s'agit ici d'un filtre qui n'est pas le même ...

C'est ce que je pensais aussi. Comme la clé de l'index est une clé composite. Le moteur crée les stats sur cette clé composite.

Encore une fois tout dépend de la requête que vous exécutez sur la table.
Et jusqu'ici, nous n'avons pas cette requête ...
Celle de votre billet filtre, si je me rappelle bien, sur la colonne val, et non pas sur create_date ...

@++

**zinzineti** · 22/09/2010, 14h12

Envoyé par SQLpro

Je me suis gouré, c'est auto_created = 1 => _WA_Sys...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM sys.stats WHERE auto_created = 1

A +

Exact

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT OBJECT_NAME(s.object_id) AS [Nom_Table]
    ,COL_NAME(sc.object_id, sc.column_id) AS [Nom_Colonne]
    ,s.name AS [Nom_Statistique]
    , STATS_DATE (s.object_id,s.stats_id) 	AS [Date MAJ des statistiques]
FROM sys.stats s INNER JOIN sys.stats_columns sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id 
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1 
AND s.auto_created = 1   -- Statistique automatique

Merci

**zinzineti** · 22/09/2010, 14h24

Envoyé par elsuket

Je ne comprend pas ... Les statistiques ne sont ni sur une base de données, ni sur une table, mais sur des colonnes.
Il s'agit ici d'un filtre qui n'est pas le même ...

Oui c'est vrai. c'est une question de filtre
Quand je mets ton WHERE en commentaire on retrouve les mêmes résultats.

Sorry

Oublie donc ce que j'ai dis sur les référentiels c'est des histoires

Envoyé par elsuket

Encore une fois tout dépend de la requête que vous exécutez sur la table.
Et jusqu'ici, nous n'avons pas cette requête ...
Celle de votre billet filtre, si je me rappelle bien, sur la colonne val, et non pas sur create_date ...

@++

C'est bien ça, tu as raison

Merci