SQL Server 2012 : Défragmentation d'indexes

Version imprimable

28/05/2012, 11h06
Donpi

SQL Server 2012 : Défragmentation d'indexes

Bonjour,

Voila mon problème, je bataille depuis plusieurs jours sur des baisses de performances dans mon serveur.

J'ai fais différentes rechercher et je tombe sur la défragmentation des indexes, ça semble être une bonne idée, alors ni une ni deux, je lance un defrag sur tout les indexes d'une table de 45 mio de records...

Après 25 minutes il me dit qu'il a fini, alors je consulte l'état de fragmentation des indexes, et tous sont à 0%.

Je me dis super, mais la je me rend compte que j'ai des accès disque non stop et que le cpu est utilisé entre 5% et 20%.
Au bout de 2h-3h les accès disque s’arrêtent et le cpu arrête aussi de bossé.

La je re-consulte l'état de mes indexes et HO surprise :aie: il sont tous fragmenté à 99.9% (bien pir qu'avant)

Quelqu'un a une explication sur ce qui se passe ?

Pour info c'est un server test que je suis seul a utilisé, donc pas d'autres accès que le mien.

Merci pour vos réponses a+
28/05/2012, 11h15
darkelend

Si les indexs se fragmentent, ça veut dire qu'il y a de l’écriture sur les tables. Regarde les statistiques d'écriture de tes indexs. J'ai fait une application qui te permettra rapidement de consulter les statistiques d'utilisation de tes indexs. Regarde dans ma signature.
S'il n'y a pas d'écriture et que tes indexs se fragmentent, je ne vois pas d'où vient le problème
28/05/2012, 15h23
elsuket

Bonjour,

Citation:

S'il n'y a pas d'écriture et que tes indexs se fragmentent, je ne vois pas d'où vient le problème

Si les index on été défragmentés et que par la suite la base de données n'est accédée qu'en lecture, les index ne se fragmenteront pas.

Si vos index se fragmentent si vite, c'est qu'ils sont sur de nombreuses colonnes et/ou qu'il portent sur des colonnes de type chaîne ou uniqueidentifier.

Retenez que moins vos index comportent de colonnes, moins ils ont de chances de se fragmenter rapidement. D'autre part si vos index portent sur des colonnes de type entier, c'est encore mieux.
Les colonnes de type entier avec la propriété d'auto-incrémentation (IDENTITY) se fragmentent très peu.

J'ai donné quelques scripts pour connaître les différentes caractéristiques des index, mais le tout dernier, tout frais est le plus complet.

Pourriez-vous en poster le résultat lorsque vous avez des problèmes de performances ?
Maintenez vous les statistiques de cette base de données ?

@++ ;)

Citation:

Envoyé par elsuket

Bonjour,

Si les index on été défragmentés et que par la suite la base de données n'est accédée qu'en lecture, les index ne se fragmenteront pas.

Si vos index se fragmentent si vite, c'est qu'ils sont sur de nombreuses colonnes et/ou qu'il portent sur des colonnes de type chaîne ou uniqueidentifier.

Retenez que moins vos index comportent de colonnes, moins ils ont de chances de se fragmenter rapidement. D'autre part si vos index portent sur des colonnes de type entier, c'est encore mieux.
Les colonnes de type entier avec la propriété d'auto-incrémentation (IDENTITY) se fragmentent très peu.

J'ai donné quelques scripts pour connaître les différentes caractéristiques des index, mais le tout dernier, tout frais est le plus complet.

Pourriez-vous en poster le résultat lorsque vous avez des problèmes de performances ?
Maintenez vous les statistiques de cette base de données ?

@++ ;)

Re un petit explication sur moi avant d'allez plus loin.

J'ai été parachuté pseudo DBA car je suis celui qui gueule la plus fort sur ceux qui respectent pas les principe de base (genre vues avec 12 étages de select imbriqués qui retourne 200 colonnes dont la moitié ne servent à rien ou tables de 50mio de records sans clé primaire etc..)

Sans entré dans le détail car secret professionnel etc...
Nous avons plusieurs problèmes que j’essaie de réglé un par un en me "formant" un peu sur le tas.

Sinon j'ai lancé le script dont vous parler et j'ai le résultat suivant j'ai remplacer le nom de la table par table, les id par id1 etc et les valeurs indexées par Val1 etc (Secret professionnel oblige):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
NONCLUSTERED	0.001503	2.17E-05	31883669	Table_Id1_NDX			Id1				NULL				4	1	0	0	0	0	NULL	31914474	31883669	37591		31914474	631896	28.05.2012 08:57	0	26	0	26	21	28.05.2012 15:42	3	0.01	0	LEAF	NULL
NONCLUSTERED	0		0.007194245	31883669	Table_Id2_NDX			Id2				NULL				5	1	0	0	0	0	NULL	31914474	31883669	37591		31914474	632376	28.05.2012 09:05	1	3	0	4	21	28.05.2012 15:36	3	0.01	0	LEAF	NULL
NONCLUSTERED	0.003414	9.26E-06	31883669	Table_Id3Id4Id5Id6Id7Id2_NDX	Id3, Id4, Id5, Id6, Id7, Id2	NULL				6	1	0	1	0	0	NULL	31914474	31883669	37591		31914474	1225688	28.05.2012 09:15	1	0	0	1	21	28.05.2012 15:36	4	0.01	0	LEAF	NULL
NONCLUSTERED	0		0.111111112	31883669	Table_Id4Id5Id6_NDX		Id4, Id5, Id6			NULL				2	1	0	0	0	0	NULL	31914474	31883669	37591		31914474	771720	28.05.2012 08:44	0	0	0	0	21	NULL			4	0	0	LEAF	NULL
NONCLUSTERED	0		0.5		31883669	IX_NumTmp2			NumTmp				Id3, Id4, Id5, Id6, Id2, Id7	19	1	0	0	0	0	NULL	31914474	31883669	37591		31914474	1237192	28.05.2012 09:34	0	0	0	0	21	NULL			4	0	0	LEAF	NULL
STAT		0.226816	0.000640615	NULL		_WA_Sys_00000009_1A9995E3	Val1				NULL				NULL	NULL	NULL	NULL	NULL	NULL	NULL	40717264	40717264	27318531	321294		NULL	11.07.2011 17:56	NULL	NULL	NULL	NULL	NULL	NULL			NULL	NULL	NULL	NULL	NULL
STAT		0		0.083333336	NULL		_WA_Sys_Id5_1A9995E3		Id5				NULL				NULL	NULL	NULL	NULL	NULL	NULL	NULL	39691375	39691375	22722897	317883		NULL	20.05.2011 07:13	NULL	NULL	NULL	NULL	NULL	NULL			NULL	NULL	NULL	NULL	NULL
STAT		0		0.018518519	NULL		_WA_Sys_Id6_1A9995E3		Id6				NULL				NULL	NULL	NULL	NULL	NULL	NULL	NULL	39691375	39691375	22722897	317883		NULL	20.05.2011 07:05	NULL	NULL	NULL	NULL	NULL	NULL			NULL	NULL	NULL	NULL	NULL
STAT		0		0.039999999	NULL		_WA_Sys_Id7_1A9995E3		Id7				NULL				NULL	NULL	NULL	NULL	NULL	NULL	NULL	39691375	39691375	22722897	306995		NULL	20.05.2011 07:13	NULL	NULL	NULL	NULL	NULL	NULL			NULL	NULL	NULL	NULL	NULL
STAT		0.107266	0.004761905	NULL		_WA_Sys_Val2_1A9995E3		Val2				NULL				NULL	NULL	NULL	NULL	NULL	NULL	NULL	42894215	42894215	20104197	329226		NULL	10.11.2011 10:29	NULL	NULL	NULL	NULL	NULL	NULL			NULL	NULL	NULL	NULL	NULL
STAT		0.272		0.004716981	NULL		_WA_Sys_Val3_1A9995E3		Val3				NULL				NULL	NULL	NULL	NULL	NULL	NULL	NULL	42894215	42894215	20104197	329226		NULL	10.11.2011 10:29	NULL	NULL	NULL	NULL	NULL	NULL			NULL	NULL	NULL	NULL	NULL
STAT		0.5		0.013888889	NULL		_WA_Sys_Val4_1A9995E3		Val4				NULL				NULL	NULL	NULL	NULL	NULL	NULL	NULL	42894215	42894215	20104197	329226		NULL	2011-11-10 10:29:33.043	NULL	NULL	NULL	NULL	NULL	NULL			NULL	NULL	NULL	NULL	NULL
STAT		0.5		0.034482758	NULL		_WA_Sys_Val5_1A9995E3		Val5				NULL				NULL	NULL	NULL	NULL	NULL	NULL	NULL	44847916	44847916	21501187	334977		NULL	23.02.2012 08:31	NULL	NULL	NULL	NULL	NULL	NULL			NULL	NULL	NULL	NULL	NULL
STAT		0.4		0.028571429	NULL		_WA_Sys_0000000E_1A9995E3	Val6				NULL				NULL	NULL	NULL	NULL	NULL	NULL	NULL	44847916	44847916	21501187	334977		NULL	23.02.2012 08:31	NULL	NULL	NULL	NULL	NULL	NULL			NULL	NULL	NULL	NULL	NULL
STAT		0.738509	0.001081081	NULL		_WA_Sys_00000008_1A9995E3	Val7				NULL				NULL	NULL	NULL	NULL	NULL	NULL	NULL	40717271	40717271	27318528	321294		NULL	11.07.2011 17:56	NULL	NULL	NULL	NULL	NULL	NULL			NULL	NULL	NULL	NULL	NULL
STAT		0.320388	0.002320186	NULL		_WA_Sys_00000007_1A9995E3	Val8				NULL				NULL	NULL	NULL	NULL	NULL	NULL	NULL	40717273	40717273	27318528	321294		NULL	11.07.2011 17:56	NULL	NULL	NULL	NULL	NULL	NULL			NULL	NULL	NULL	NULL	NULL
STAT		0.142796	0.001883239	NULL		_WA_Sys_00000006_1A9995E3	Val9				NULL				NULL	NULL	NULL	NULL	NULL	NULL	NULL	40717273	40717273	27318528	321294		NULL	11.07.2011 17:56	NULL	NULL	NULL	NULL	NULL	NULL			NULL	NULL	NULL	NULL	NULL
STAT		0		0.200000003	NULL		_WA_Sys_00000013_1A9995E3	Val10				NULL				NULL	NULL	NULL	NULL	NULL	NULL	NULL	44941272	44941272	21381595	335849		NULL	28.02.2012 16:08	NULL	NULL	NULL	NULL	NULL	NULL			NULL	NULL	NULL	NULL	NULL

28/05/2012, 17h47
SQLpro

Toutes ces données ne nous servent à rien sans la méthode, c'est à dire la requête qui à servit à produire ces résultat...

En effet, nous ne sommes pas devins !

A +
29/05/2012, 07h29
Donpi

Citation:

Envoyé par SQLpro

Toutes ces données ne nous servent à rien sans la méthode, c'est à dire la requête qui à servit à produire ces résultat...

En effet, nous ne sommes pas devins !

A +

"Sinon j'ai lancé le script dont vous parler et j'ai le résultat suivant j'ai remplacer le nom de la table par table, les id par id1 etc et les valeurs indexées par Val1 etc (Secret professionnel oblige): "

Voir message de "elsuket" 2 post au dessus
30/05/2012, 09h46
elsuket

Citation:

Re un petit explication sur moi avant d'allez plus loin.

J'ai été parachuté pseudo DBA car je suis celui qui gueule la plus fort sur ceux qui respectent pas les principe de base (genre vues avec 12 étages de select imbriqués qui retourne 200 colonnes dont la moitié ne servent à rien ou tables de 50mio de records sans clé primaire etc..)

Sans entré dans le détail car secret professionnel etc...
Nous avons plusieurs problèmes que j’essaie de réglé un par un en me "formant" un peu sur le tas.

OK.

Merci pour les résultats; pour plus de lisibilité, il faudra à l'avenir ajouter le nom des colonnes :)
Dans SQL Server Management Studio, il suffit d'aller dans Tools > Options > Query Results > Results To Grid > cocher la case Include column headers when copying or saving the results

Vous pouvez également attacher un fichier Excel en cliquant le bouton Gérer les pièces jointes sous la boîte de texte quand vous postez un message.

Une première remarque pour vos index : votre tables n'est pas clustérisée, donc SQL Server passe son temps, lorsque cette table est spécifiée dans une requête, à lire les pages d'Index Allocation Map (+ là-dessus dans le billet de Mikedavem), et cela coûte très cher en CPU et en IO.

Pour avoir un index cluster, il faut qu'il soit posé sur une ou des colonnes qui identifient uniquement chaque ligne de votre table.
C'est typiquement le cas des index supportant les clé primaires, et éventuellement les contraintes d'unicité.
Si vous ajoutez un tel index maintenant, tous les index non-cluster seront reconstruits puisque les références physiques sont changées.
Pour connaître la différence entre un index cluster et un index non-cluster, c'est par ici.

L'index Table_Id3Id4Id5Id6Id7Id2_NDX me semble un peu large, et il semble peu utilisé (comme d'autres index de cette table) : une seule fois.
Plus vous avez d'index sur une table, plus cela ralentit les écritures sur celle-ci.
Essayez autant que cela vous est possible de conserver des index sur des colonnes de type entier, avec le plus petit nombre de colonnes possible.
C'est simple à réaliser lorsque le modèle de données est correct.

Concernant les statistiques, la colonne rowmodctr indique une valeur autour de 300.000 pour une table qui comporte environ 30 millions de lignes, vous avez donc 1% de décalage sans compter l’échantillonnage : ce n'est pas catastrophique.
Existe-il néanmoins un job qui les maintienne de façon régulière ?

@++ ;)
30/05/2012, 11h47
Donpi

Citation:

Envoyé par elsuket

OK.
Concernant les statistiques, la colonne rowmodctr indique une valeur autour de 300.000 pour une table qui comporte environ 30 millions de lignes, vous avez donc 1% de décalage sans compter l’échantillonnage : ce n'est pas catastrophique.
Existe-il néanmoins un job qui les maintienne de façon régulière ?

@++ ;)

Re,

Il 'agit d'une table qui regroupe des ventes par volume en pièces et en prix.
Une ligne est unique dans la table sur la Date, le modèle et le point de vente.
Les écritures se font via un trigger sur une table qui contient une ligne par vente (Cette table-là est bien mieux conçue).

Concrètement cette table augmente entre 20'000 et 30'000 records par jour.

Ce qui veut dire que je ne peux pas stopper le système pour créer une clé primaire clustérisé. Ce qui me mène à mon problème du jour.

Comment créer cette clé? J’essaie plusieurs techniques sans succès.
Ajouter bêtement la clé prend trop de temps en 3 jours non-stop, SQL server n'est pas allé jusqu'au bout.

Ce que j'ai de plus efficace pour le moment c'est une copie dans une nouvelle table avec les clés telles que je les veux.
Le problème est qu'il lui faut 2 semaines de copie non-stop.

J'ai essayé une variante avec un trigger On Delete sur la table originale. Le trigger se charge de la copie. Je lance des Deletes par parquets de 5000 records. La Sql estime terminer en 4 ou 5 heures mais au bout de 10 minutes la boucle prend de plus en plus de temps...

PS: Les stat que j'ai posté hier viennent d'une base test j’essaie de mettre un excel avec les données de la prod dans l'après midi.
30/05/2012, 16h36
Donpi

1 pièce(s) jointe(s)

Voilà les données prod.

Je sais pas tout lire mais je me rend déjà compte que j'ai des stat qui ne sont pas a jours depuis 2011
31/05/2012, 11h50
elsuket

Bonjour,

Citation:

Concrètement cette table augmente entre 20'000 et 30'000 records par jour.

Ce qui veut dire que je ne peux pas stopper le système pour créer une clé primaire clustérisé. Ce qui me mène à mon problème du jour.

Comment créer cette clé? J’essaie plusieurs techniques sans succès.
Ajouter bêtement la clé prend trop de temps en 3 jours non-stop, SQL server n'est pas allé jusqu'au bout.

25,000 lignes par jour, ce n'est pas très méchant.
Bien sûr cela dépend de la configuration hardware que vous avez derrière (virtualisé ?).

Quelle est l'instruction que vous avez utilisé pour ajouter la clé primaire / contrainte d'unicité clustérisée ?
Avez-vous fait une construction en ligne ?
Avez-vous tenté de supprimer les index non-cluster, créer l'index cluster, puis recréer les index non-cluster ?
Est-ce que savez si cela a fait grossir les fichiers de données ou du journal des transactions ?
Faites cela de préférence aux heures de faible trafic sur la base de données ;)

Citation:

J'ai essayé une variante avec un trigger On Delete sur la table originale. Le trigger se charge de la copie. Je lance des Deletes par parquets de 5000 records. La Sql estime terminer en 4 ou 5 heures mais au bout de 10 minutes la boucle prend de plus en plus de temps...

C'est normal, puisqu'il faut à chaque fois lire des données avec une clause TOP sans ORDER BY ou un SET ROWCOUNT (:().
Il faut batcher avec un SELECT TOP n FROM maTable ORDER BY cle_unique_clustérisée.

Citation:

Je sais pas tout lire mais je me rend déjà compte que j'ai des stat qui ne sont pas a jours depuis 2011

Effectivement c'est bizarre pour votre table, mais généralement cela n'est pas forcément synonyme d'un manque de maintenance : si on a une table qui n'est jamais écrite (typiquement les tables qu'on dit "maître"), ses statistiques n'ont pas lieu d'être mises à jour.

Exécutez un UPDATE STATISTICS maTable WITH COLUMNS après avoir reconstruit les index : ça va peut-être aider ;)

@++ ;)
01/06/2012, 07h53
Donpi

Re,

Merci pour votre aide, je me suis inspiré de vos remarques et j'ai procédé comme suit:

-J'ai généré un script de création de la table que j'ai lancé avec un nouveau nom et sans les indexes ni les contraintes.
-J'ai supprimer toutes les clé et j'ai ajouté une clé primaire dans ma nouvelle table.
-J'ai lancé la copie par tranches de 5'000 records, avec un trigger on delete sur la table de base pour avoir une source la plus petite possible.
-En plus j'ai shrinker le fichier de log et fais un reset du cache tout les 100'000 records.

La copie à duré 4h30 et encore 2 petites heures pour recréer les indexes...

Une fois ma Primary Key créée la fragmentation de mes indexes est stable à moins de 0.1 %

Sinon pour répondre aux autres questions :
Il 'agit de serveur virtualisé c'est des petites configurations ce qui explique en parti nos problèmes.
03/06/2012, 06h39
elsuket

Citation:

-En plus j'ai shrinker le fichier de log et fais un reset du cache tout les 100'000 records.

ça par contre ça a du vous faire perdre du temps.
Le fichier du journal des transactions croit avec la quantité de données que vous manipulez.
L'espace que vous retrouvez une fois le fichier réduit sera donc tôt ou tard repris. Cela impliquera une attente de son grossissement pour la mise à zéro de l'incrément, et une possible fragmentation du fichier.

S'il grossit et que vous êtes constamment obligé de le réduire, et que le mode de récupération de la base de données est à FULL ou BULK_LOGGED, alors c'est que vous ne prenez pas suffisamment fréquemment de sauvegarde du fichier du journal des transactions.

Vider le cache sur un système de production ne peut avoir que des effets néfastes.
S'il s'agit du cache de procédures, il faudra les recompiler, et cela est consommateur de temps CPU.
S'il s'agit du cache de données, il faudra que SQL Server re-copie les pages de données depuis le disque vers la RAM, et comme vous vous en doutez cela est lent.

Citation:

Une fois ma Primary Key créée la fragmentation de mes indexes est stable à moins de 0.1 %

Est-ce à dire que même lorsque vous réalisez le chargement de données, la fragmentation des index reste faible (moins de 10-15%) ?
Notez par ailleurs que comme vous avez clustérisé la table, les colonnes qui participent à cet index cluster sont référencées dans le niveau feuille de tous les index non-cluster de cette table.
Veillez donc à ne pas créer des index non-cluster contenant les colonnes de l'index cluster ;)

Citation:

Il 'agit de serveur virtualisé c'est des petites configurations ce qui explique en parti nos problèmes.

Là dessus peut-être qu'utiliser un masque d'affinités CPU et/ou disque peut aider.
Je vous conseille de lire le billet de SQLPro sur le sujet, c'est très instructif.

@++ ;)
04/06/2012, 08h04
Donpi

Bonjour,

Pour une raison qui m’échappe, dans mon cas, reseter les caches évite un "collapse" des performances au bout d'un moment.

Sinon je suis obliger de shrinker régulièrement sinon le log se remplis. on est a l'étroit sur les disques et je n'ai que 30Go de libre qui se remplissent à la vitesse de la lumière suivant le traitement. En l’occurrence celui là ne va pas jusqu'au bout. Même avec un Set recovery simple au début de mon script.

Merci pour l'article qui soulève d'autre questions (dans un autre post)
04/06/2012, 09h39
elsuket

Citation:

Pour une raison qui m’échappe, dans mon cas, reseter les caches évite un "collapse" des performances au bout d'un moment.

- Soit parce que votre base de données est sous-indexée
- Soit parce que la quantité de RAM allouée à la machine virtuelle qui supporte votre instance de SQL Server est trop petite par rapport au volume de données à traiter
- Soit enfin parce que le modèle de données est incorrect ;)

@++ ;)