Discussion :

[guigui69]

Bonsoir à tous,

je voudrais savoir si dans sqlserver 2008r2 (ou avec requete) il est possible sur une base de lister(/connaitre) le taux de fragmentation > 30% pour tout les index de la base?

merci d'avance pour votre aide et conseils

guigui69

[mikedavem]

Hello,

Par exemple:

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
SELECT 
	s.[name] as 'Schema', 
	t.[name] as 'Table', 
	i.[name] as 'Index',
	ist.avg_fragmentation_in_percent,
	ist.page_count
FROM 
	sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS is
INNER JOIN 
	sys.tables t on t.[object_id] = ist.[object_id]
INNER JOIN 
	sys.schemas s on t.[schema_id] = s.[schema_id]
INNER JOIN 
	sys.indexes AS i ON i.[object_id] = ist.[object_id]
							 AND ist.index_id = i.index_id
WHERE 
	ist.database_id = DB_ID()
	AND ist.avg_fragmentation_in_percent >= 30
	AND ist.page_count > 8000
ORDER BY 
	ist.avg_fragmentation_in_percent desc

++

[guigui69]

Bonjour,

cela signifie quoi "AND ist.page_count > 8000" ?

merci

guigui69

[mikedavem]

Cela signifie qu'on ne considère pas les tables <= ~8MB (8K * 1000 pages). Tu peux mettre plus en fonction de ton environnement mais c'est en général ce que l'on met chez nous de manière générique. On exclut les (petites) tables car la fragmentation qui les concerne n'est pas vraiment un problème et essayer de la supprimer est souvent inefficace (ALTER INDEX REBUILD / REORGANIZE)

++

[janlouk]

Voici ce que j'ai pour toi :

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
--Script d'Ola Hallengren
DECLARE @DatabaseID int
 
SET @DatabaseID = DB_ID()
 
SELECT DB_NAME(@DatabaseID) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000

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
--Attention, peut durer plus de 10 minutes
USE TEMPDB
GO
create table #IndexFragmentation  (
[Database Name] sysname,
[Table Name] sysname,
[Index Name] sysname NULL,
[Index_type_desc] varchar(256),
[avg_fragmentation_in_percent] decimal(15,2),
[Page_count] int
)
DECLARE @CMD VARCHAR(MAX)
SET @CMD = N'use [?] 
SELECT DB_NAME(ps.database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Table Name], 
i.name AS [Index Name], ps.index_type_desc, ps.avg_fragmentation_in_percent, 
ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , N''LIMITED'') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id] 
AND ps.index_id = i.index_id
WHERE ps.database_id = DB_ID()
AND ps.page_count > 2500
ORDER BY ps.avg_fragmentation_in_percent DESC OPTION (RECOMPILE);'
 
Insert into #IndexFragmentation
exec sp_MSForEachDB @CMD
 
select * from #IndexFragmentation
order by avg_fragmentation_in_percent desc
drop table #IndexFragmentation