Statistiques non créées

Version imprimable

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:

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:

DBCC SHOW_STATISTICS ('dbo.T_TEST',creation_date)

et SSMS me renvoie le message d'erreur 2767:

Citation:

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

21/09/2010, 16h49
SQLpro

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

A +
21/09/2010, 17h09
zinzineti

Citation:

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

Citation:

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:

DBCC SHOW_STATISTICS ('dbo.T_TEST',create_date)
21/09/2010, 17h28
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:

SELECT * FROM sys.stats

A +
21/09/2010, 17h51
zinzineti

Citation:

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:

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:

DBCC SHOW_STATISTICS ('dbo.T_TEST',creation_date)

me renvoie un message d'erreur ?

Alors que pour les autres colonnes tout se passe bien ?

Citation:

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:

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:

DBCC SHOW_STATISTICS ('dbo.T_TEST',create_date)

me renvoie cette erreur :

Citation:

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 ?

21/09/2010, 22h10
kagemaru

Citation:

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.

Bonjour,

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

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 ...

@++ ;)

Citation:

Envoyé par zinzineti

Code:

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 +

22/09/2010, 11h30
zinzineti
Citation:

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 :ccool:

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:

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:

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:

1 2 DBCC SHOW_STATISTICS ('dbo.T_TEST',id)
marche bien. Parce que j'avais fait :

Code:

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:

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:

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

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

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 ?!

Citation:
Envoyé par elsuket
Bonjour,

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

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 ;)

Citation:

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:

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 ;)

22/09/2010, 11h57
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).

++
22/09/2010, 12h06
zinzineti

Citation:

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 ?
22/09/2010, 12h35
zinzineti

Citation:

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.
22/09/2010, 13h50
SQLpro

Citation:

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:

SELECT * FROM sys.stats WHERE auto_created = 1

A +
22/09/2010, 14h01
elsuket

Citation:

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 ...

Citation:

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 ... ;)

@++;)

Citation:

Envoyé par SQLpro

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

Code:

SELECT * FROM sys.stats WHERE auto_created = 1

A +

Exact :ccool:

Code:

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 ;)

22/09/2010, 14h24
zinzineti

Citation:

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 :P

Citation:

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 :ccool:

Merci
22/09/2010, 15h35
elsuket

Citation:

Sorry ;)

Pas de problème :)

Citation:

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

Ha pâs biên ça :mouarf:

@++ ;)
23/09/2010, 11h35
zinzineti

Une petite synthèse sur le sujet