Fragmentation indexes important ?

Version imprimable

Bonjour à tous,

En lisant un des post récent, SQLPro propose d'exécuter cette requête :

Code:

1
2
3
4
5
6
7
8
9
10
SELECT s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME, i.name AS INDEX_NAME, avg_fragmentation_in_percent
FROM   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ips
       INNER JOIN sys.objects AS o
             ON ips.object_id = o.object_id
       INNER JOIN sys.schemas AS s
             ON o.schema_id = s.schema_id
       INNER JOIN sys.indexes AS i
             ON ips.object_id = i.object_id
             AND ips.index_id = i.index_id
ORDER BY 4 DESC;

Je suis très étonné des résultats pour une DB en Prod.

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
TABLE_SCHEMA	TABLE_NAME	INDEX_NAME	avg_fragmentation_in_percent
dbo	LSW_DUR_MSG_RECEIVED	NULL	99,6732026143791
dbo	LSW_PROCESS_LINK	LSWC_PROC_LINK_PK	99,6350364963504
dbo	LSW_USR_XREF	LSWC_UXREF_UQ	99,3548387096774
dbo	LSW_PROCESS_ITEM	LSWC_PROC_ITEM_PK	99,0353697749196
dbo	LSW_PROCESS_PARAMETER	LSWC_PRO_PARM_PK	98,9247311827957
dbo	LSW_PO_DEPENDENCY	IDX5_PO_DEPENDENCY	98,7551867219917
dbo	LSW_SCRIPT	LSWC_SCRIPT_PK	98,6842105263158
dbo	LSW_DUR_MSG_RECEIVED	LSWC_DMR_NUQ1	98,6486486486486
dbo	LSW_BPD_INSTANCE_DATA	NULL	98,3606557377049
dbo	LSW_PARAMETER_MAPPING	LSWC_PARAM_MAP_PK	98,2456140350877
dbo	LSW_PROCESS_LINK	LSWC_PROC_LINK_PK3	98,1981981981982
dbo	LSW_PO_REFERENCE	LSWC_PO_REFERENCE	97,9166666666667
dbo	LSW_PARAMETER_MAPPING	LSWC_PARAM_MAP_PK3	97,8723404255319
dbo	LSW_SCRIPT	LSWC_SCRIPT_PK3	97,7272727272727
dbo	LSW_INST_MSG_INCL	LSWC_MSG_INCL_NUQ1	97,7099236641221
dbo	LSW_PROCESS_PARAMETER	LSWC_PRO_PARM_PK3	97,6744186046512
dbo	LSW_PROCESS_VARIABLE	LSWC_PROC_VAR_PK	97,6
dbo	LSW_PROCESS_ITEM	LSWC_PROC_ITEM_PK3	97,5206611570248
dbo	LSW_PROCESS_PARAMETER	LSWC_PROPAR_NUQ	97,5
dbo	LSW_EPV_VAR	LSWC_EPV_VAR_PK	97,5
dbo	LSW_PROCESS_ITEM	IDX_PROC_ITEM_ID	97,2222222222222
dbo	LSW_SUBPROCESS	LSWC_SUBPROCESS_PK	97,2222222222222
dbo	LSW_PROCESS	LSWC_PROCESS_PK	97,2222222222222
dbo	LSW_EXIT_POINT	LSWC_EXIT_POINT_PK	97,1428571428571
dbo	LSW_PROCESS_ITEM	LSWC_PROC_ITEM_PK2	97,0588235294118
dbo	LSW_PROCESS_VARIABLE	LSWC_PROC_VAR_PK3	96,875
dbo	LSW_PARAMETER_MAPPING	LSWC_PARAM_MAP_NQ2	96,6666666666667
dbo	LSW_USR_GRP_XREF	LSWC_GXREF_UQ	96,6507177033493
dbo	LSW_PROCESS_LINK	LSWC_PROC_LINK_PK2	96,551724137931
dbo	LSW_PROCESS	LSWC_PROCESS_PK3	96,4285714285714
dbo	LSW_PROCESS_VARIABLE	IDX_PROC_VAR	96,1538461538462
dbo	BPM_COACH_VIEW_INLINE_SCRIPT	BPM_VIEW_SCRIPT_PK	96,1538461538462
dbo	LSW_USR_GRP_MEM_XREF	LSWC_UGXREF_UQ	96,085409252669
dbo	LSW_SWITCH_CONDITION	LSWC_SWITCH_CON_PK	96
dbo	LSW_PO_REFERENCE	IDX3_PO_REFERENCE	96
dbo	LSW_EXIT_POINT	LSWC_EXIT_POINT_PK3	96
dbo	LSW_PROCESS_PARAMETER	LSWC_PRO_PARM_PK2	95,8333333333333
dbo	LSW_PO_METADATA	IDX6_PO_METADATA	95,6521739130435
dbo	LSW_PARAMETER_MAPPING	LSWC_PARAM_MAP_NQ	95,4545454545455
dbo	LSW_SWITCH	LSWC_SWITCH_PK	94,7368421052632
dbo	LSW_PROCESS_VARIABLE	LSWC_PROC_VAR_PK2	94,4444444444444
dbo	BPM_COACHNG_BOUNDARY_EVENT	BPM_COACHNG_BE_PK	94,4444444444444
dbo	LSW_COACH_BUTTON	LSWC_COACH_BTN_PK	94,1176470588235
dbo	LSW_PO_REFERENCE	IDX2_PO_REFERENCE	94,1176470588235
dbo	LSW_PROCESS	LSW_IDX2_PROCESS	93,75
dbo	LSW_PO_METADATA	IDX5_PO_METADATA	93,75
dbo	LSW_PROCESS_ITEM_PRE_POST	LSWC_PIPP_PK3	93,75
dbo	LSW_SWITCH_CONDITION	LSWC_SWITCH_CON_PK3	93,75
dbo	BPM_COACH_VIEW_CONFIG_OPTION	BPM_VIEW_CONFIG_PK	93,3333333333333
dbo	LSW_SMART_FOLDER	LSWC_SF_NAME	93,3333333333333
dbo	BPM_COACH_VIEW_INLINE_SCRIPT	BPM_VIEW_SCRIPT_PK3	92,8571428571429
dbo	LSW_PO_DEPENDENCY	IDX4_PO_DEPENDENCY	92,7943760984183
dbo	LSW_CLASS	LSWC_CLASS_PK	92,3076923076923
dbo	LSW_SUBPROCESS	LSWC_SUBPROCESS_PK3	92
dbo	LSW_PROCESS_ITEM_PRE_POST	LSWC_PIPP_PK	92
dbo	LSW_SWITCH	LSWC_SWITCH_PK3	91,6666666666667
dbo	LSW_EPV_VAR	LSWC_EPV_VAR_PK3	91,6666666666667
dbo	LSW_ENV_VAR_DEFAULT	LSWC_ENV_DFLT_PK	91,6666666666667
dbo	LSW_RESOURCE_BUNDLE	LSWC_RBUNDLE_PK	91,6666666666667
dbo	LSW_PO_REFERENCE	IDX_PO_REFERENCE	91,1764705882353
dbo	LSW_BPD_INSTANCE	INDEX2_LSW_BPD_INST	90,1960784313726
dbo	LSW_LAYOUT_PARAM	LSWC_LOUT_PRM_PK	90
dbo	LSW_BPD_PARAMETER	LSWC_BPD_PARM_PK	90
dbo	LSW_ENV_VAR_DEFAULT	LSWC_ENV_DFLT_PK3	88,8888888888889
dbo	LSW_MANAGED_ASSET	LSWC_MGD_ASSET_PK	88,8888888888889
dbo	LSW_PO_DEPENDENCY	LSWC_PO_DEPENDENCY	87,9588839941263
dbo	LSW_BPD_INSTANCE	_dta_index_LSW_BPD_INSTANCE_51_802101898__K9_K1_K14_K4	87,7926421404682
dbo	LSW_RESOURCE_BUNDLE	LSWC_RBUNDLE_PK3	87,5
dbo	LSW_PROCESS	LSW_IDX_PROCESS	87,5

Pourtant, j'utilise les scripts OLA pour les indexes et statistiques. Ou alors, dans ce cas-ci, faire tourner qu'une fois par semaine n'est pas suffisant ? Voici les paramètres que je passe dans mon job.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES, -%EXT_Biztalk%,-%EXT_MOSS%, 
@Indexes = 'ALL_INDEXES',
@UpdateStatistics = 'ALL',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@OnlyModifiedStatistics = 'Y', --Update statistics only if any rows have been modified since the most recent statistics update
@StatisticsResample = 'N', --Update statistics with the most recent sample.
@SortInTempdb = 'N', --Use tempdb for sort operations when rebuilding indexes
@FillFactor= '90', --Indicate, as a percentage, how full the pages should be made when rebuilding indexes
@MSShippedObjects = 'Y',
@LogToTable = 'Y';

Je ne comprends donc pas trop ce pourcentage élevé.

Merci
Jean-Luc

22/01/2016, 15h31
Boubou2020

je doute que vos indexes sont trés petit c'est pour cela il sont vu toujour comme des indexes fragmenter
En général, la fragmentation sur les petits index n'est pas controlable
la fragmentation dans un petit index peut ne pas être réduite après la réorganisation ou la reconstruction de l'index.
22/01/2016, 15h45
SQLpro

ajoutez la colonne page_count dans la requête pour voir.....

A +

Citation:

Envoyé par SQLpro

ajoutez la colonne page_count dans la requête pour voir.....

A +

Voilà :

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
dbo	LSW_DUR_MSG_RECEIVED	NULL	99,6742671009772	2349
dbo	LSW_PROCESS_LINK	LSWC_PROC_LINK_PK	99,6350364963504	274
dbo	LSW_USR_XREF	LSWC_UXREF_UQ	99,3548387096774	155
dbo	LSW_PROCESS_ITEM	LSWC_PROC_ITEM_PK	99,0353697749196	311
dbo	LSW_PROCESS_PARAMETER	LSWC_PRO_PARM_PK	98,9247311827957	186
dbo	LSW_PO_DEPENDENCY	IDX5_PO_DEPENDENCY	98,7551867219917	482
dbo	LSW_SCRIPT	LSWC_SCRIPT_PK	98,6842105263158	76
dbo	LSW_DUR_MSG_RECEIVED	LSWC_DMR_NUQ1	98,6509274873524	593
dbo	LSW_BPD_INSTANCE_DATA	NULL	98,3606557377049	358
dbo	LSW_PARAMETER_MAPPING	LSWC_PARAM_MAP_PK	98,2456140350877	228
dbo	LSW_PROCESS_LINK	LSWC_PROC_LINK_PK3	98,1981981981982	111
dbo	LSW_PO_REFERENCE	LSWC_PO_REFERENCE	97,9166666666667	48
dbo	LSW_PARAMETER_MAPPING	LSWC_PARAM_MAP_PK3	97,8723404255319	94
dbo	LSW_SCRIPT	LSWC_SCRIPT_PK3	97,7272727272727	44
dbo	LSW_INST_MSG_INCL	LSWC_MSG_INCL_NUQ1	97,7099236641221	131
dbo	LSW_PROCESS_PARAMETER	LSWC_PRO_PARM_PK3	97,6744186046512	86
dbo	LSW_PROCESS_VARIABLE	LSWC_PROC_VAR_PK	97,6	125
dbo	LSW_PROCESS_ITEM	LSWC_PROC_ITEM_PK3	97,5206611570248	121
dbo	LSW_PROCESS_PARAMETER	LSWC_PROPAR_NUQ	97,5	40
dbo	LSW_EPV_VAR	LSWC_EPV_VAR_PK	97,5	40
dbo	LSW_PROCESS_ITEM	IDX_PROC_ITEM_ID	97,2222222222222	36
dbo	LSW_SUBPROCESS	LSWC_SUBPROCESS_PK	97,2222222222222	36
dbo	LSW_PROCESS	LSWC_PROCESS_PK	97,2222222222222	72
dbo	LSW_EXIT_POINT	LSWC_EXIT_POINT_PK	97,1428571428571	35
dbo	LSW_PROCESS_ITEM	LSWC_PROC_ITEM_PK2	97,0588235294118	34
dbo	LSW_PROCESS_VARIABLE	LSWC_PROC_VAR_PK3	96,875	64
dbo	LSW_PARAMETER_MAPPING	LSWC_PARAM_MAP_NQ2	96,6666666666667	30
dbo	LSW_USR_GRP_XREF	LSWC_GXREF_UQ	96,6507177033493	209
dbo	LSW_PROCESS_LINK	LSWC_PROC_LINK_PK2	96,551724137931	29
dbo	LSW_PROCESS	LSWC_PROCESS_PK3	96,4285714285714	28
dbo	LSW_PROCESS_VARIABLE	IDX_PROC_VAR	96,1538461538462	26

Je ne donne qu'une partie, car dans cette requête, j'ai 648 lignes. Mais il y en a beaucoup avec 0 comme fragmentation.

22/01/2016, 16h50
Boubou2020

Le taux de fragmentation des tables de très petite volumétrie ne pourra être réduit à 0

un article sur le sujet http://blog.capdata.fr/index.php/fra...kees-en-s-gam/
25/01/2016, 14h28
janlouk

Merci, je le lirai. Si je comprends bien, les petits indexes seront toujours fort fragmentés ?
26/01/2016, 15h50
SQLpro

Citation:

Envoyé par janlouk

Merci, je le lirai. Si je comprends bien, les petits indexes seront toujours fort fragmentés ?

Non... Il peuvent être fragmentés ou pas. Ce sont des mathématiques ! C'est la méthode de calcul des moyennes qui les montre fragmentés !

En effet, si avec un bidon de jus d'orange, vous remplissez :
49 verres pleins et le dernier à moitié plein, alors le taux de remplissage moyen est de 99% et la fragmentation (le résiduel vide) 1%
9 verres pleins et le dernier à moitié plein, alors le taux de remplissage moyen est de 95% et la fragmentation (le résiduel vide) 5%
1 verre plein et le second et dernier à moitié plein, alors le taux de remplissage moyen est de 75% et la fragmentation (le résiduel vide) 25%

Dans ce dernier cas, est-ce réellement une fragmentation ? Est-elle réfragable ??

A +
29/01/2016, 10h16
rudib

Hello,

Les tables qui n'ont pas d'index clustered ne seront pas défragmentées par le script d'Ola. Est-ce que ta table LSW_DUR_MSG_RECEIVED comporte un index clustered ? Est-ce une table dans laquelle on insère seulement, ou fait-on aussi des mises à jour ?
En 2005, tu ne peux pas reconstruire une table heap (sans index clustered), il me semble que c'était une nouveauté 2008. Essaie quand même de faire un ALTER TABLE ... REBUILD, sinon il faut passer par une création d'index clustered, et le supprimer ensuite ... si c'est bien une table heap.

Pense aussi au fait que les shrink fragmentent les index. J'espère que tu ne planifies pas des shrink de bases de données.