Gain suite au suppression index

**Boubou2020** · 18/03/2015, 16h05

Bonjour a tous

suite a un état de saturation sur mon partition Data j'ai essayer de faire un audit sur les indexs non utilisés afin de les supprimer et gagner plus d'espace

pour cela j'ai interroger la DVM "sys.dm_db_index_usage_stats"

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
select OBJECT_NAME(IUS.object_id), I.name, IUS.user_seeks, IUS.user_scans, IUS.user_lookups, IUS.user_updates, 
       IUS.user_seeks + IUS.user_scans + IUS.user_lookups AS Total_use, 
       T.user_seeks + T.user_scans + T.user_lookups AS Table_use, 
       (CAST(IUS.user_seeks + IUS.user_scans + IUS.user_lookups AS FLOAT) / 
        NULLIF(CAST(T.user_seeks + T.user_scans + T.user_lookups AS FLOAT), 0)) * 100 AS efficacite_percent 
from   sys.dm_db_index_usage_stats AS IUS 
       INNER JOIN sys.indexes AS I 
             ON IUS.object_id = I.object_id 
                AND IUS.index_id = I.index_id   
       INNER JOIN sys.dm_db_index_usage_stats AS T 
             ON IUS.object_id = T.object_id 
                AND T.index_id IN (0, 1) 
where  IUS.database_id = DB_ID() 
  AND  I.name NOT IN (SELECT CONSTRAINT_NAME  
                      FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS) 
ORDER  BY efficacite_percent

et j'était concentré sur les colonnes user_seeks + user_scans + user_lookups dont leu valeur obtenu était Zéro ou très faible

ma question est ce que ce ci est suffisant pour prendre la décision de suppression ,et l'autre question comment je peux calculer l'espace occupé par mon index

et merci finalement pour vos aide

**ATMorphine** · 18/03/2015, 16h20

Bonjour
Personnellement j’utilise cette requête pour récupérer des informations d'utilisations sur mes indexs :

Conseil numéro 1, supprimer seulement les indexs non cluster.
Conseil numéro 2, supprimer les indexs non cluster seulement dans le cadre ou l'instance est démarrée depuis suffisamment longtemps pour estimer que l'ensemble des traitements qui peuvent tourner sur l'instance ont été lancés.
Conseil numéro 3, il y allait petit à petit car le coup de suppression est ridicule par rapport au coups de reconstruction en cas de besoin.
Conseil numéro 4, les indexs avec seulement du scan, il peut être intéressant des les revoir voir de les supprimer.

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
IF OBJECT_ID('tempdb..#StatIndex') IS NOT NULL 
	DROP TABLE #StatIndex
 
CREATE TABLE #StatIndex (
	[DB] SYSNAME
	, FG SYSNAME NULL
	, [Schema] SYSNAME
	, [Table] SYSNAME
	, [Index] SYSNAME NULL
	, [Type] NVARCHAR(60)
	, [is_disabled] BIT
	, [NumPartition] INT NULL
	, [NbReads] BIGINT
	, [NbWrites] BIGINT
	, [Seeks] BIGINT
	, [Scans] BIGINT
	, [Lookups] BIGINT
	, [Inserts] BIGINT
	, [Updates] BIGINT
	, [Deletes] BIGINT
	, [NumRows] BIGINT
	, [Reserved_KB] BIGINT
	, [Used_KB] BIGINT
	, last_user_seek DATETIME
	, last_user_scan DATETIME
	, last_user_lookup DATETIME
	, last_user_update DATETIME
)		
 
DECLARE List CURSOR FOR
	SELECT Name
	FROM sys.databases db  
 
DECLARE @ReqBase VARCHAR(MAX)
DECLARE @Name SYSNAME
 
OPEN List
 
FETCH NEXT FROM List 
INTO @Name 
 
WHILE @@FETCH_STATUS = 0
BEGIN	  
		SET @ReqBase = '
	USE ' + @name + '
		SELECT  ''' + @Name + '''
		, p.fg
		, s.name
		, t.name
		, i.name 		
		, i.type_desc
		, i.is_disabled
		, p.partition_number
		, ISNULL(ius.user_seeks,0) + ISNULL(ius.system_seeks,0)
			+ ISNULL(ius.user_scans,0) + ISNULL(ius.system_scans,0)
			+ ISNULL(ius.user_lookups,0) + ISNULL(ius.system_lookups,0)
		AS Reads
		, ISNULL(ius.user_updates,0) + ISNULL(ius.system_updates,0) AS Writes 
		, ISNULL(ius.user_seeks,0) + ISNULL(ius.system_seeks,0)
		, ISNULL(ius.user_scans,0) + ISNULL(ius.system_scans,0)
		, ISNULL(ius.user_lookups,0) + ISNULL(ius.system_lookups,0)
		, ISNULL(ios.leaf_insert_count,0) + ISNULL(ios.nonleaf_insert_count,0)
		, ISNULL(ios.leaf_update_count,0) + ISNULL(ios.nonleaf_update_count,0)
		, ISNULL(ios.leaf_delete_count,0) + ISNULL(ios.nonleaf_delete_count,0) 
		, p.rows
		, p.total_pages * 8192 / 1024
		, p.used_pages * 8192 / 1024
		, ius.last_user_seek
		, ius.last_user_scan
		, ius.last_user_lookup
		, ius.last_user_update
		FROM sys.indexes AS i 	
		INNER JOIN sys.tables AS t
			ON t.object_id = i.object_id
		INNER JOIN sys.schemas AS s
			ON s.schema_id = t.schema_id   
		LEFT JOIN (
			SELECT DISTINCT f.name AS fg, p.partition_number, p.object_id, p.index_id, f.is_read_only, p.rows, a.total_pages, a.used_pages
			FROM sys.partitions p			
			INNER JOIN sys.allocation_units a 
				ON (p.hobt_id = a.container_id AND a.type IN (1,3))
				OR (p.partition_id = a.container_id AND a.type IN (2))
			INNER JOIN sys.filegroups f
				ON a.data_space_id = f.data_space_id
		) p ON p.object_id = t.object_id 
			AND p.index_id = i.index_id
		LEFT JOIN sys.dm_db_index_operational_stats (DB_ID(), NULL,NULL,NULL) ios
			ON ios.object_id  = i.object_id
			AND ios.index_id = i.index_id  
			AND ios.partition_number = p.partition_number
		LEFT JOIN sys.dm_db_index_usage_stats AS ius 
			ON ius.object_id = i.object_id 
			AND ius.index_id = i.index_id
			AND ius.database_id = DB_ID()'
 
	PRINT @ReqBase
 
	INSERT INTO #StatIndex
		EXEC(@ReqBase)  
 
	FETCH NEXT FROM List 
	INTO @Name 
END
 
CLOSE List;
DEALLOCATE List;
 
SELECT sqlserver_start_time AS [LastBoot]
, DATEDIFF(DAY,sqlserver_start_time,GETDATE()) AS [NbDaysLastReboot] 
FROM sys.dm_os_sys_info
 
SELECT FG, [Table], [Index], [Type], [is_disabled], NbReads, NbWrites, NumRows, Reserved_KB, Used_KB
, Seeks, Scans, Lookups, Inserts, Updates, Deletes
, last_user_seek
, last_user_scan
, last_user_lookup
, last_user_update 
, 'DROP INDEX [' + [Index] + '] ON [' + [Schema] + '].[' + [Table] + ']'AS req_drop
FROM #StatIndex

**janlouk** · 18/03/2015, 16h30

J'avais trouvé ça ici, tu peux jeter un oeil : http://blog.developpez.com/zinzineti...udit_des_index

**SQLpro** · 18/03/2015, 16h40

Attention, ces données sont collectées depuis le dernier redémarrage de votre instance. Il faut donc avoir au moins 30 jours voir plus de rémanence pour être à peu près sur de son coup.
Voir la DMV :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

A +

Gain suite au suppression index

Administration SQL Server

Vue hybride

Discussions similaires

Partager

Partager