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
| -- EXEC sp__IndexMaintenance 1, 1, 0, 0
ALTER PROCEDURE sp__IndexMaintenance
@_minutes_of_REBUILD int
, @_minutes_of_REORGANIZE int
, @_maintain_LOB_index bit = 0
, @_index_max_size_kb int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @start_date_time datetime = GETDATE()
, @end_date_time_REBUILD datetime
, @end_date_time_REORGANIZE datetime
, @index_name sysname
, @table_name sysname
, @fill_factor tinyint
, @sql nvarchar(max)
, @index_frag_id int
, @cr char(2)
, @tab char(1)
, @rebuild bit
, @sql_begin varchar(512)
SELECT @end_date_time_REBUILD = DATEADD(minute, @_minutes_of_REBUILD, @start_date_time)
, @end_date_time_REORGANIZE = DATEADD(minute, @_minutes_of_REBUILD + @_minutes_of_REORGANIZE, @start_date_time)
, @cr = CHAR(13) + CHAR(10)
, @tab = CHAR(9)
, @rebuild = 1
, @index_frag_id = 0
-- Rebuilding or reorganizing indexes
WHILE @index_frag_id IS NOT NULL
BEGIN
SET @index_frag_id = 0
-- Searching an index to be rebuilt or reorganized
SELECT @sql = 'SELECT TOP 1 @index_frag_id = index_frag_id' + @cr
+ @tab + ', @index_name = index_name' + @cr
+ @tab + ', @table_name = table_name' + @cr
+ @tab + ', @fill_factor = fill_factor' + @cr
+ 'FROM index_frag' + @cr
+ 'WHERE frag_pct ' + CASE @rebuild WHEN 1 THEN ' > 30' WHEN 0 THEN ' BETWEEN 10 AND 30' END + @cr
+ CASE @_maintain_LOB_index
WHEN 0 THEN 'AND (is_not_online = 0 AND has_lob_column = 0)'
ELSE ''
END + @cr
+ CASE
WHEN @_index_max_size_kb = 0 THEN ''
ELSE 'AND index_size_kb <= ' + CAST(@_index_max_size_kb AS varchar(10))
END + @cr
+ 'AND maintained_date_time IS NULL' + @cr
+ 'ORDER BY user_searches DESC'
PRINT @sql
PRINT '-------------------'
EXEC sp_executeSQL
@sql
, N'@index_frag_id int OUTPUT, @index_name sysname OUTPUT, @table_name sysname OUTPUT, @fill_factor tinyint OUTPUT'
, @index_frag_id = @index_frag_id OUTPUT
, @index_name = @index_name OUTPUT
, @table_name = @table_name OUTPUT
, @fill_factor = @fill_factor OUTPUT
/*
When @index_frag_id = 0 (no index found matching the query filters)
- if we were in the REBUILD phase, we jump to the REORGANIZE phase
- if we were in the REORGANIZE phase, we quit
*/
IF @index_frag_id = 0
BEGIN
IF @rebuild = 1
SET @rebuild = 0
ELSE
SET @index_frag_id = NULL
END
ELSE
BEGIN
/*
There still are some indexes to be maintained,
but we need to check if we won't be out of the maintenance period, so :
- if we were in the REBUILD phase, we jump to the REORGANIZE phase
- if we were in the REORGANIZE phase, we quit
*/
IF GETDATE() >= @end_date_time_REBUILD
SET @rebuild = 0
IF GETDATE() >= @end_date_time_REORGANIZE
SET @index_frag_id = NULL
END
-- Building and executing the sql statement to maintain the index
SET @sql_begin = 'ALTER INDEX ' + @index_name + ' ON ' + @table_name
IF @rebuild = 0
SET @sql = @sql_begin + ' REORGANIZE'
ELSE
SET @sql = @sql_begin + ' REBUILD '
+ CASE
WHEN @fill_factor = 0 AND @_maintain_LOB_index = 0 THEN 'WITH (ONLINE = ON)'
WHEN @fill_factor > 0 AND @_maintain_LOB_index = 0 THEN 'WITH (ONLINE = ON, FILLFACTOR = ' + CAST(@fill_factor AS varchar(3)) + ')'
WHEN @fill_factor > 0 AND @_maintain_LOB_index = 1 THEN 'WITH (FILLFACTOR = ' + CAST(@fill_factor AS varchar(3)) + ')'
WHEN @fill_factor = 0 AND @_maintain_LOB_index = 1 THEN ''
END
PRINT @sql
-- Mark the index as maintained
UPDATE index_frag
SET maintained_date_time = GETDATE()
WHERE index_frag_id = @index_frag_id
END
END |
Tutorial procédures stockées
Répondre avec citation
Partager