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
| USE MA_BASE; --> remplacer MA_BASE par le nom de votre base
GO
--> 1) sauvegarde FULL de la base
BACKUP DATABASE MA_BASE TO DISK = 'BACKUP_MA_BASE_BEFORE INDEX.BAK' WITH COMPRESSION; --> remplacer MA_BASE par le nom de votre base
--> 2) journalisation en mode simple de la base
ALTER DATABASE MA_BASE SET RECOVERY SIMPLE;
--> 3) défragmentation, reconstruction intelligente
DECLARE @SQL NVARCHAR(max) = N''
SELECT @SQL = @SQL +
CASE
WHEN i.name IS NULL
THEN N'ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' REBUILD;'
WHEN avg_fragmentation_in_percent < 30
THEN N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' REORGANIZE;'
ELSE N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' REBUILD;'
END + ' BACKUP LOG MA_BASE TO DISK = ''NUL'';' --> remplacer MA_BASE par le nom de votre base
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) AS ips
JOIN sys.objects AS o ON ips.object_id = o.object_id
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE page_count > 64
AND avg_fragmentation_in_percent > 10;
PRINT @SQL
EXEC (@SQL);
GO
--> 4) création des index manquant selon seuil de gain
DECLARE @SQL NVARCHAR(max) = N'';
WITH
T AS
(
SELECT d.*,
ROW_NUMBER() OVER (ORDER BY avg_user_impact * avg_total_user_cost DESC) AS INDICE,
COUNT(*) OVER() AS NBR
FROM sys.dm_db_missing_index_details AS d
JOIN sys.dm_db_missing_index_groups AS g
ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats AS s
ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID()
)
SELECT @SQL = @SQL
+ N'CREATE INDEX X_gunnar_' + N'_' + CAST(NEWID() AS VARCHAR(38))
+ N'_' + CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112)
+ N' ON ' + statement
+ ' (' + COALESCE(T.equality_columns + ', ' + T.inequality_columns,
T.equality_columns, T.inequality_columns) + ')'
+ CASE WHEN included_columns IS NULL THEN N''
ELSE ' INCLUDE (' + included_columns + ')' END + ';'
+ N' BACKUP LOG MA_BASE TO DISK = ''NUL'';' --> remplacer MA_BASE par le nom de votre base
FROM T
WHERE INDICE <= NBR / 2;
EXEC (@SQL);
--> 5) journalisation en mode full de la base
ALTER DATABASE MA_BASE SET RECOVERY FULL; --> remplacer MA_BASE par le nom de votre base
--> 6) nouvelle sauvegarde FULL de la base
BACKUP DATABASE MA_BASE TO DISK = 'BACKUP_MA_BASE AFTER INDEX.BAK' WITH COMPRESSION; --> remplacer MA_BASE par le nom de votre base |
SQL server 2016 index et log
Répondre avec citation
Envoyé par gunnar
Partager