Analyse des Index d'une base

Version imprimable

21/12/2016, 12h05
dari68

Analyse des Index d'une base

Bonjour,

Je cherche à avoir le taux de fragmentation de tous les index de ma base ? dans quelle vue chercher ?

Merci.

J'ai ce vieux bout de code sous le coude :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- recherche d'index fragmenté
declare @bd_id int
set @bd_id = db_id() ;
with SR as (
	SELECT u.NAME AS table_schema, OBJECT_NAME(i.object_id) AS table_name, i.name AS table_index_name, phystat.avg_fragmentation_in_percent, ROWS
	FROM sys.dm_db_index_physical_stats(@bd_id, NULL, NULL, NULL,'SAMPLED') phystat
	INNER JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id
	INNER JOIN sys.partitions p WITH ( NOLOCK ) ON p.OBJECT_ID = i.OBJECT_ID
	INNER JOIN sys.sysobjects o WITH ( NOLOCK ) ON o.ID = i.OBJECT_ID
	INNER JOIN sys.schemas u WITH ( NOLOCK ) ON u.schema_id = o.uid
	WHERE rows >= 100000
)
select table_schema, table_name, table_index_name, avg_fragmentation_in_percent, rows,
	'ALTER INDEX ['+table_index_name+'] ON ['+table_schema+'].['+table_name+'] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )' SQL_Commande
from SR
WHERE avg_fragmentation_in_percent >= 5
group by table_schema, table_name, table_index_name, avg_fragmentation_in_percent, rows
order by avg_fragmentation_in_percent desc ,table_schema, table_name, table_index_name

21/12/2016, 14h39
dari68

Bonjour,

Merci bcp, grâce à cette requête je récupère 2 index avec 92% et 84 %.

@+

Bonjour,

J'ai celle-ci, l'avantage c'est qu'elle donne un % de fragmentation pour tous les indexes de toutes les DB sur l'instance.

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
 
-- Attention, cela peut tourner plus de 10 minutes voir plus...
 
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