Bonjour à tous,

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

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 : 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
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 : Sélectionner tout - Visualiser dans une fenêtre à part
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