[2K5] Fragmentation d'Index

Version imprimable

21/12/2009, 17h55
Monstros Velu

[2K5] Fragmentation d'Index

Bonjour,

j'ai un soucis de fragmentation d'index.

J'ai bien vu la discussion http://www.developpez.net/forums/d84...ation-indexes/
mais j'ai par exemple un index de 137400 pages réparties en 10139 fragments (58% de fragmentation), ou un de 20371 pages en 2829 fragments (98%). Au final, j'ai 21 index de plus de 100 pages fragmentés (sur 641 indexes). Or, parmi ces 21, certains sont très utilisés...

J'ai bien lancé le script de la FAQ concernant la défragmentation, mais ça n'a rien changé (il y a de toutes façons un plan de maintenance chaque nuit, j'ai fait tourner le script par acquis de conscience).

Quelqu'un a une idée de s'il faut remédier à ça, de si je perd mon temps ou de comment faire pour améliorer les choses ? Merci d'avance !

Question subsidiaire : Quand on a l'ID d'un index (avec dm_db_index_physical_stats ), comment on trouve son nom ?
21/12/2009, 20h57
SQLpro

Impossible de savoir si c'est normal ou pas, sans la description de votre table et de son index et éventuellement un échantillon de données. En effet de par leur nature certaines fragmentations ne peuvent jamais se réduire. Enfin s'agit-il-il d'un index, d'un blob ou d'une table ? Car toutes les structures de données indexées ou pas se trouvent être des "index" !

A +

Bonjour,

La requête

Code:

1
2
3
4
select object_name("object_id"), *
from Sys.dm_db_index_physical_stats(5, NULL, NULL, NULL, 'LIMITED')
where page_count > 100
and avg_fragmentation_in_percent > 30

me renvoie le résultat suivant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
                                   database_id object_id   index_id    partition_number index_type_desc  alloc_unit_type_desc  index_depth index_level avg_fragmentation_in_percent fragment_count       avg_fragment_size_in_pages page_count           
---------------------------------- ----------- ----------- ----------- ---------------- ---------------- --------------------- ----------- ----------- ---------------------------- -------------------- -------------------------- -------------------- 
ANS_ANSWER                         5           81435364    0           1                HEAP             IN_ROW_DATA           1           0           84.8414985590778             1479                 7.86004056795132           11625                
RFT_RECEPTIONFORM_TIMER            5           106639623   0           1                HEAP             IN_ROW_DATA           1           0           96.7741935483871             31                   4.38709677419355           136                  
SCU_SERVICECUSTOMER                5           135671531   0           1                HEAP             IN_ROW_DATA           1           0           68.2151589242054             327                  7.80428134556575           2552                 
QIN_QUERYINPUT                     5           161435649   0           1                HEAP             IN_ROW_DATA           1           0           85.7294994675186             2421                 8.63444857496902           20904                
PVD_CIT                            5           174063806   0           1                HEAP             IN_ROW_DATA           1           0           91.7670682730924             471                  6.21868365180467           2929                 
EXQ_EXTERNALQUERY                  5           197015883   0           1                HEAP             IN_ROW_DATA           1           0           92.4242424242424             62                   2.25806451612903           140                  
PRH_PROVIDERHOURS                  5           240719910   0           1                HEAP             IN_ROW_DATA           1           0           99.6655518394649             300                  1.83333333333333           550                  
EMP_EMPLOYEE                       5           274816041   0           1                HEAP             IN_ROW_DATA           1           0           30.4522338912378             19829                19.9377174844924           395345               
QUC_QUALITYCOMMENT                 5           528720936   0           1                HEAP             IN_ROW_DATA           1           0           98.1622590766472             2194                 7.25843208751139           15925                
ORD_ORDER                          5           577541241   0           1                HEAP             IN_ROW_DATA           1           0           89.0214797136038             374                  6.6524064171123            2488                 
DSC_DSCLOSURE                      5           582293134   0           1                HEAP             IN_ROW_DATA           1           0           93.8063063063063             836                  6.76076555023923           5652                 
ORL_ORDERLINE                      5           673541583   0           1                HEAP             IN_ROW_DATA           1           0           90.0375939849624             481                  6.65072765072765           3199                 
PPG_PRICEPRODUCTGRID               5           721541754   0           1                HEAP             IN_ROW_DATA           1           0           92.1052631578947             36                   3.75                       135                  
CUS_CUSTOMER                       5           760545943   0           1                HEAP             IN_ROW_DATA           1           0           66.6666666666667             27                   7.18518518518519           194                  
EPR_EMPLOYEEPROFIL                 5           862626116   0           1                HEAP             IN_ROW_DATA           1           0           71.6312056737589             105                  7.77142857142857           816                  
CNT_CONTENT                        5           952390462   0           1                HEAP             IN_ROW_DATA           1           0           87.0967741935484             29                   4.06896551724138           118                  
CIN_CONTACTINFORMATION             5           1013578649  0           1                HEAP             IN_ROW_DATA           1           0           58.8689991863303             10139                13.5516323108788           137400               
ORI_ORDERINSURANCE                 5           1116687176  0           1                HEAP             IN_ROW_DATA           1           0           96.0757780784844             711                  6.62165963431786           4708                 
RAC_REPORTINGCONTENT               5           1224391431  0           1                HEAP             IN_ROW_DATA           1           0           98.9533011272142             1232                 7.39853896103896           9115                 
PVD_PROVIDER                       5           1251639652  0           1                HEAP             IN_ROW_DATA           1           0           89.9581589958159             432                  3.625                      1566                 
CTT_CONTACT                        5           1387151987  0           1                HEAP             IN_ROW_DATA           1           0           82.1428571428571             49                   3.69387755102041           181                  
CPS_CUSTOMERPROVIDERSERVICE        5           1440724185  0           1                HEAP             IN_ROW_DATA           1           0           87.9310344827586             57                   5.19298245614035           296                  
TLO_TOPLOG                         5           1610644981  0           1                HEAP             IN_ROW_DATA           1           0           40.0620582658162             2329                 19.8939458995277           46333                
SEA_SEARCH                         5           1623676832  0           1                HEAP             IN_ROW_DATA           1           0           91.3924050632911             723                  6.93360995850622           5013                 
CNT_PRO                            5           1679345047  0           1                HEAP             IN_ROW_DATA           1           0           89.4736842105263             37                   4.51351351351351           167                  
QUE_USR                            5           1694629080  0           1                HEAP             IN_ROW_DATA           1           0           93.2642487046632             361                  4.46814404432133           1613                 
SER_SEARCHRESULTELEMENT            5           1735677231  0           1                HEAP             IN_ROW_DATA           1           0           93.3059307105109             1599                 7.32082551594747           11706                
TSK_TASK                           5           1985546257  0           1                HEAP             IN_ROW_DATA           1           0           54.3933054393305             392                  12.8367346938776           5032                 
ASQ_ANSWERSCRIPTQUESTION           5           2096726522  0           1                HEAP             IN_ROW_DATA           1           0           98.4287709497207             2829                 7.20077765995051           20371                
PPG_PRICEPRODUCTGRID_BEFOREFUSION  5           2106646748  0           1                HEAP             IN_ROW_DATA           1           0           33.3333333333333             9                    12                         108                  
 
(30 row(s) affected)

Je ne sais pas à quoi correspondent les colonnes "HEAP" et "IN_ROW_DATA" par contre.

Pour prendre un exemple, la table QUE_USER :

Code:

1
2
3
4
5
6
7
8
9
CREATE TABLE [dbo].[QUE_USR](
	[QUE_ID] [uniqueidentifier] NOT NULL,
	[USR_ID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_QUE_USR] PRIMARY KEY NONCLUSTERED 
(
	[QUE_ID] ASC,
	[USR_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Elle contient 277 000 lignes et les index suivant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE NONCLUSTERED INDEX [LIEN_4434_FK] ON [dbo].[QUE_USR] 
(
	[QUE_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object:  Index [LIEN_4435_FK]    Script Date: 12/23/2009 17:43:26 ******/
CREATE NONCLUSTERED INDEX [LIEN_4435_FK] ON [dbo].[QUE_USR] 
(
	[USR_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object:  Index [PK_QUE_USR]    Script Date: 12/23/2009 17:43:26 ******/
ALTER TABLE [dbo].[QUE_USR] ADD  CONSTRAINT [PK_QUE_USR] PRIMARY KEY NONCLUSTERED 
(
	[QUE_ID] ASC,
	[USR_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

Si vous voyez quelque chose, je suis tout ouïe ! Merci ! ^^

24/12/2009, 03h53
elsuket

Bonjour,

Rajoutez le nom (name) de l'index en spécifiant une jointure sur sys.indexes sur les colonnes object_id et index_id, et donnez la structure complète de vos tables.
Voyez également cette petite procédure que j'ai écrite pour aider à défragmenter les indexes sur des bases à faibles contraintes opérationnelles.

@++ ;)
24/12/2009, 13h12
Ptit_Dje

Bonjour,

Pensez à ajouter un clustered index sur vos tables.
Le terme Heap est donné à une table ne contenant pas de clustered index. Or, celui-ci defini l'ordre logique de vos données au sein de la table. De plus, lorsqu'un seek est effectué sur celui-ci, cela donne des performances optimales.

Bonne journée

Un clustered index sur une table où il y a peu d'insert, ok, mais sur une table avec plein d'insert, c'est risqué, non ? Comment est-ce ordonné si je met un clustered index sur la clef primaire (un unique identifier) ? Et si la clef primaire est composée de 2 clustered index ?

Sinon, la requête

Code:

1
2
3
4
5
6
7
      select object_name (sdips.object_id), sind.name, sdips.*
      from Sys.dm_db_index_physical_stats(5, NULL, NULL, NULL, 'LIMITED') sdips
		inner join sys.indexes sind
			on sind.object_id = sdips.object_id and sind.index_id = sdips.index_id
		where avg_fragmentation_in_percent > 30
		and page_count > 100
		order by avg_fragmentation_in_percent desc

me renvoie :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
                                   name  database_id object_id   index_id    partition_number index_type_desc  alloc_unit_type_desc  index_depth index_level avg_fragmentation_in_percent fragment_count  avg_fragment_size_in_pages page_count  
---------------------------------- ----- ----------- ----------- ----------- ---------------- ---------------- --------------------- ----------- ----------- ---------------------------- --------------- -------------------------- ----------- 
PRH_PROVIDERHOURS                  NULL  5           240719910   0           1                HEAP             IN_ROW_DATA           1           0           99.6655518394649             300             1.83333333333333           550         
RAC_REPORTINGCONTENT               NULL  5           1224391431  0           1                HEAP             IN_ROW_DATA           1           0           98.9533011272142             1232            7.39853896103896           9115        
ASQ_ANSWERSCRIPTQUESTION           NULL  5           2096726522  0           1                HEAP             IN_ROW_DATA           1           0           98.4287709497207             2829            7.20077765995051           20371       
QUC_QUALITYCOMMENT                 NULL  5           528720936   0           1                HEAP             IN_ROW_DATA           1           0           98.1622590766472             2194            7.25843208751139           15925       
RFT_RECEPTIONFORM_TIMER            NULL  5           106639623   0           1                HEAP             IN_ROW_DATA           1           0           96.7741935483871             31              4.38709677419355           136         
ORI_ORDERINSURANCE                 NULL  5           1116687176  0           1                HEAP             IN_ROW_DATA           1           0           96.0757780784844             711             6.62165963431786           4708        
DSC_DSCLOSURE                      NULL  5           582293134   0           1                HEAP             IN_ROW_DATA           1           0           93.8063063063063             836             6.76076555023923           5652        
SER_SEARCHRESULTELEMENT            NULL  5           1735677231  0           1                HEAP             IN_ROW_DATA           1           0           93.3059307105109             1599            7.32082551594747           11706       
QUE_USR                            NULL  5           1694629080  0           1                HEAP             IN_ROW_DATA           1           0           93.2642487046632             361             4.46814404432133           1613        
EXQ_EXTERNALQUERY                  NULL  5           197015883   0           1                HEAP             IN_ROW_DATA           1           0           92.4242424242424             62              2.25806451612903           140         
PPG_PRICEPRODUCTGRID               NULL  5           721541754   0           1                HEAP             IN_ROW_DATA           1           0           92.1052631578947             36              3.75                       135         
PVD_CIT                            NULL  5           174063806   0           1                HEAP             IN_ROW_DATA           1           0           91.7670682730924             471             6.21868365180467           2929        
SEA_SEARCH                         NULL  5           1623676832  0           1                HEAP             IN_ROW_DATA           1           0           91.3924050632911             723             6.93360995850622           5013        
ORL_ORDERLINE                      NULL  5           673541583   0           1                HEAP             IN_ROW_DATA           1           0           90.0375939849624             481             6.65072765072765           3199        
PVD_PROVIDER                       NULL  5           1251639652  0           1                HEAP             IN_ROW_DATA           1           0           89.9581589958159             432             3.625                      1566        
CNT_PRO                            NULL  5           1679345047  0           1                HEAP             IN_ROW_DATA           1           0           89.4736842105263             37              4.51351351351351           167         
ORD_ORDER                          NULL  5           577541241   0           1                HEAP             IN_ROW_DATA           1           0           89.0214797136038             374             6.6524064171123            2488        
CPS_CUSTOMERPROVIDERSERVICE        NULL  5           1440724185  0           1                HEAP             IN_ROW_DATA           1           0           87.9310344827586             57              5.19298245614035           296         
CNT_CONTENT                        NULL  5           952390462   0           1                HEAP             IN_ROW_DATA           1           0           87.0967741935484             29              4.06896551724138           118         
QIN_QUERYINPUT                     NULL  5           161435649   0           1                HEAP             IN_ROW_DATA           1           0           85.7294994675186             2421            8.63444857496902           20904       
ANS_ANSWER                         NULL  5           81435364    0           1                HEAP             IN_ROW_DATA           1           0           84.8414985590778             1479            7.86004056795132           11625       
CTT_CONTACT                        NULL  5           1387151987  0           1                HEAP             IN_ROW_DATA           1           0           82.1428571428571             49              3.69387755102041           181         
EPR_EMPLOYEEPROFIL                 NULL  5           862626116   0           1                HEAP             IN_ROW_DATA           1           0           71.6312056737589             105             7.77142857142857           816         
SCU_SERVICECUSTOMER                NULL  5           135671531   0           1                HEAP             IN_ROW_DATA           1           0           68.2151589242054             327             7.80428134556575           2552        
CUS_CUSTOMER                       NULL  5           760545943   0           1                HEAP             IN_ROW_DATA           1           0           66.6666666666667             27              7.18518518518519           194         
CIN_CONTACTINFORMATION             NULL  5           1013578649  0           1                HEAP             IN_ROW_DATA           1           0           58.8689991863303             10139           13.5516323108788           137400      
TSK_TASK                           NULL  5           1985546257  0           1                HEAP             IN_ROW_DATA           1           0           54.3933054393305             392             12.8367346938776           5032        
TLO_TOPLOG                         NULL  5           1610644981  0           1                HEAP             IN_ROW_DATA           1           0           40.0723888314374             2330            19.8871244635193           46337       
PPG_PRICEPRODUCTGRID_BEFOREFUSION  NULL  5           2106646748  0           1                HEAP             IN_ROW_DATA           1           0           33.3333333333333             9               12                         108         
EMP_EMPLOYEE                       NULL  5           274816041   0           1                HEAP             IN_ROW_DATA           1           0           30.4522338912378             19829           19.9377174844924           395345      
 
(30 row(s) affected)

Pourquoi les index n'ont-ils pas de nom ? Il ne s'agit peut-être pas d'index ?

24/12/2009, 14h50
Ptit_Dje

Vos index ayant un ID 0 ou HEAP sont vos données et ne porte donc pas de nom, si ce n'est celui de la table elle même, vu qu'il n'existe pas d'index.
En gros la vos données ne sont pas du tout triées.
Mettre un clustered index, meme s'il y a beaucoup d'inserts ne peut, d'après moi, qu'etre une bonne chose.
Votre id est il une clé auto-incrémentée ? Vous servez vous de celle-ci pour des jointures ?
Si oui, vous pouvez y définir un clustered index. Cette explication n'est pas exhaustive, le choix de votre clustered index doit être optimisé en fonction de vos requêtes, cependant c'est un cas assez général de le faire de la sorte.

Coté performance, si vous avez peur d'avoir des pages split du à votre index (imaginons que vous insériez des rows d'une manière aléatoire et non séquentielle du point de vue de votre clustered index), vous pouvez toujours adapter le fill factor de cet index.
Dans le cas d'une table "read-only", vous pouvez définir celui-ci à 100%.
Dans le cas d'une table avec de fréquents insert/update/delete, vous pouvez penser à le définir entre 60 et 70%.
Dans le cas d'une table "mixte", vous pouvez le définir entre 75 et 85%.
24/12/2009, 14h56
Monstros Velu

Les clefs sont des uniques identifiers ( guid généré avec newguid(), la plupart du temps en valeur par défaut sur la colonne ). Comment réagit un clustered index dans ce cas ? Les insertions seront-elles toutes considérées comme aléatoire ?
24/12/2009, 15h08
Ptit_Dje

Avez vous un réel besoin d'utiliser ces clefs ?
Un simple ID ne pourrait il pas suffire ? C'est beaucoup plus performant... (clef de 4 bytes vs clef de 16 bytes)

Je ne sais pas trop comment un clustered index réagirait, je n'ai jamais été dans la situation.

Pour ce qui est de la génération des IDs en utilisant newguid(), je ne sais pas ce que fournit le retour en terme de séquence.
Cependant NEWSEQUENTIALID() génère un guid plus grand que tous les GUID générés sur la machine -> notion de séquentialité pour une table spécifique.

Cette fonction s'utilise uniquement comme valeur par défaut au niveau de la définistion de la table.

Dans ce cas, un clustered index sur cette colonne peut aider!
25/12/2009, 00h40
elsuket

Bonjour,

Je rejoins les propos de Ptit_Dje : les clés des index, qu'ils soient cluster ou non, doivent être les moins larges possible.
Vous devrez porter un soin particulier au choix de l'index cluster, puisque sa valeur de clé est porté dans :

- les pages de données de la table,
- les niveaux intermédiaires de l'index cluster
- toutes les pages des index non-cluster de la table.

Donc si vous utilisez une clé large comme le type UNIQUEIDENTIFIER qui fait 16 octets, vous répétez ces 16 octets autant de fois que d'index.
Il ne peut y avoir au plus qu'un seul index cluster par table, puisqu'il ordonne physiquement les lignes de la table suivant la valeur de sa clé.
C'est donc pour cela qu'il est très avantageux de choisir une clé numérique entière de préférence auto-incrémentée, car dans ce cas l'index cluster est moins sujet à la fragmentation.

Si en revanche vous utilisez le type UNIQUEIDENTIFIER sans la fonction NEWSEQUENTIALID(), l'index cluster est plus sujet aux splits de page, puisqu'il se peut que vous insériez une valeur de clé au milieu de celles qui existent déjà.
Cela peut être contre-performant si la table sous-jacente subit un nombre important d'insertions.

Citation:

Comment est-ce ordonné si je met un clustered index sur la clef primaire

Pour toute clé primaire, SQL Server crée implicitement un index cluster, sauf si vous mentionnez dans l'instruction de création de la clé primaire qu'il doit être non-cluster.

Citation:

Et si la clef primaire est composée de 2 clustered index ?

Cela est impossible car, comme je vous l'ai expliqué plus haut, il ne peut y avoir qu'un seul index cluster par table.

En cadeau de Noël je ne peux que vous conseiller de lire les articles de SQLPro à ce sujet ici, ici, ici et enfin ici (article 4/5)

@++ ;)
04/01/2010, 11h57
Monstros Velu

Tout d'abord, merci pour vos réponses.

Cette base de donnée et les applications qui tournent dessus sont anciennes, je ne nous vois pas changer les clefs UNIQUEIDENTIFIER pour des valeurs entières maintenant (même si je dois dire que j'aime les clefs identity, ça simplifie la vie).

Concernant

Citation:

Et si la clef primaire est composée de 2 clustered index ?

c'est un lapsus, ma question était : "Et si la clef est constituée de 2 UNIQUEIDENTIFIER ?

J'ai aussi une nouvelle question, suite à la lecture de http://sqlpro.developpez.com/optimis...eIndexVLDB.pdf
Si je passe mes clefs primaires (toujours mes UNIQUEIDENTIFIER) en CLUSTERED, je vais avoir du pagesplit (pas si souvent, d'ailleurs, si le fill factor est bien réglé). Mais, la nuit, il y a un recalcul des index : A ce moment, les pagesplits seront supprimés, non ?

edit : Je peux faire des tests sur la preprod. Mais je ne trouve pas la commande pour transformer une PRIMARY KEY NONCLUSTERED en CLUSTERED
04/01/2010, 18h01
SQLpro

Citation:

Un clustered index sur une table où il y a peu d'insert, ok, mais sur une table avec plein d'insert, c'est risqué, non ? Comment est-ce ordonné si je met un clustered index sur la clef primaire (un unique identifier) ? Et si la clef primaire est composée de 2 clustered index ?

Il vaut toujours mieux avoir toute vos tables avec un index clustered : il ne peut y en avoir qu'un car l'index clustered EST LA TABLE. Cecl économise une redondance d'index et ordonne les lignes physiquement. Comme tous les index secondaire (donc cluster) doivent reprendre l'index de la ligne, si votre organisation de table est sans cluster alors chaque défragmentation de table conduit à recalculer tous les index, alors que si vous avez un index cluster tous les index secondaires font référence à cette clef pour retrouver la liogne.

De plus utiliser un GUID comme clef est une hérésie. Lisez l'article que j'ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p7...nt-le-verdict/

A +
04/01/2010, 18h04
SQLpro

Citation:

transformer une PRIMARY KEY NONCLUSTERED en CLUSTERED

Là encore lisez mon blog... : http://blog.developpez.com/sqlpro/p5...pace-de-stock/
En particulier : CREATE INDEX ... WITH DROP EXISTING !

A +
04/01/2010, 18h35
Monstros Velu

Je sais bien que le guid est une hérésie en terme de performance. Mais le projet est bien plus ancien que ma présence dessus (j'ai trouvé des modifications datées de 2003), et je ne connais pas les raisons qui sont à l'origine de ce choix. Je suppose que c'est la sécurité qui a voulu ça. Dans tous les cas, il est trop tard pour faire marche arrière.

Si je comprends bien, il faut, de toutes façons, un index clustered par table. Je vais tâcher de faire ça...
05/01/2010, 13h30
SQLpro

Alors évitez de la faire sur le GUID, choissisez une date par exemple ou rajoutez artificiellement un auto incrément.

A +