Обслуживание индексов

@4ygo · Регистрация: 04.08.2019

Студворк — интернет-сервис помощи студентам

Всем привет!
100 лет не возился с сиквелом, но вот - снова нужно.
Взял скрипт Тавалика (https://github.com/Tavalik/SQL_TScripts) на обслуживание баз данных(реорганизация и ребилд индексов)
Переделал под себя. (Автору отправил переделанный скрипт)

1) изменил работу выборки объектов(исключил повторные запросы к бд, теперь он работает только с рабочей таблицей. По факту - быстрее он стал работать на пару секунд, на моей тестовой)
2)скрипт включает и потом отключает блокировки страниц, если нужно(актуально для баз 1с)
3)обновляет статистику ИНДЕКСА после РЕОРГАНИЗАЦИИ этого индекса(ребилд сам это делает)
4)все это помещено внутрь другого скрипта от Тавалика - и теперь работает на нескольких базах.

Скрипт будет работать на server2019 и старше, с лицензией выше standart(иначе надо убирать доп.опции у REBUILD)
Все настройки ( вынесены в ТОП скрипта. Настраиваемые параметры и служебные переменные)

Прошу помощи вот в чем:
1)проверить правильность написания.(скрипты работают, но я мало ли какие я там косяки допустил)
2)мб что-то можно/нужно учесть и исправить?
Скрипт неплохо комментирован и вроде все понятно.

Первый скрипт для одной базы:

SQL
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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
-- Скрипт выполняет реорганизацию либо дефрагменатцию индексов базы данных
-- Автор: Онянов Виталий (Tavalik.ru)
-- Свежие версии скриптов: https://github.com/Tavalik/SQL_TScripts
 
-- Скрипт доработан Филиппов Сергей 05.01.2025г (автору послал копию скрипта)
 
USE [TEST1]
-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ 
DECLARE @object_id INT; -- ID объекта
DECLARE @index_id INT; -- ID индекса
DECLARE @partition_number BIGINT; -- количество секций если индекс секционирован
DECLARE @schemaname nvarchar(130); -- имя схемы в которой находится таблица
DECLARE @objectname nvarchar(130); -- имя таблицы 
DECLARE @indexname nvarchar(130); -- имя индекса
DECLARE @partitionnum BIGINT; -- номер секции
DECLARE @fragmentation_in_percent FLOAT; -- процент фрагментации индекса
DECLARE @command nvarchar(4000); -- инструкция T-SQL для дефрагментации либо ренидексации
DECLARE @command_stat nvarchar(4000); -- инструкция T-SQL для дефрагментации либо ренидексации
DECLARE @allow_page_locks bit; -- параметр блокировки
DECLARE @indextype nvarchar(130); -- ТИП данных таблицы (ищем text, ntext, image или FILESTREAM)
DECLARE @max_degree_of_parallelism nvarchar(3) = 8; --параллелизм
DECLARE @SORT_IN_TEMPDB nvarchar(3) = 'ON'; --сортировать в tempdb
DECLARE @ONLINE nvarchar(3) = 'ON'; -- онлайн перестроение (за исключением типов индекесов text, ntext, image или FILESTREAM)
DECLARE @ONLINEALT nvarchar(3) = 'OFF'; -- выключить онфлайн перестроение (типы индекесов text, ntext, image или FILESTREAM)
DECLARE @page_count INT = 128; --число страниц для фрагментации
DECLARE @avg_fragmentation_in_percent FLOAT = 5.0; --процент фрагментации для РЕОРГАНИЗАЦИИ индекса
DECLARE @fragmentation_in_percent_max FLOAT = 30.0; -- процент фрагментации индекса для РЕБИЛДА индекса
DECLARE @update_statistic nvarchar(3); --для обновления статистики таблицы после РЕОРГАНИЗАЦИИ индекса
-------------------------------------------
-- ТЕЛО СКРИПТА
 
-- Отключаем вывод количества возвращаемых строк, это несколько ускорит обработку
SET NOCOUNT ON;
 
-- Удалим временные таблицы, если вдруг они есть
IF OBJECT_ID('tempdb.dbo.#work_to_do') IS NOT NULL DROP TABLE #work_to_do
 
-- Отбор таблиц и индексов с помощью системного представления sys.dm_db_index_physical_stats
-- Отбор только тех объектов которые:
--   являются индексами (index_id > 0)
--   фрагментация которых более 5% 
--   количество страниц в индексе более 128 
 
SELECT
    dm.object_id,
    dm.index_id,
    dm.partition_number,
    dm.avg_fragmentation_in_percent AS fragmentation_in_percent,
    ind.allow_page_locks AS LOCK,
    ind.name AS INDEX_NAME,
    obj.name AS TABLE_NAME,
    schem.name AS SCHEM_NAME,
    (SELECT top 1 inf.DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS AS inf
    WHERE inf.TABLE_NAME = obj.name AND inf.DATA_TYPE IN ('text', 'ntext', 'image', 'FILESTREAM')) AS DATA_TYPE
INTO #work_to_do
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS dm
    LEFT JOIN sys.indexes AS ind ON dm.object_id = ind.object_id AND dm.index_id = ind.index_id
    LEFT JOIN sys.objects AS obj ON dm.object_id = obj.object_id
    LEFT JOIN sys.schemas AS schem ON obj.schema_id = schem.schema_id
        
WHERE dm.index_id > 0
   AND dm.avg_fragmentation_in_percent > @avg_fragmentation_in_percent
   AND dm.page_count > @page_count;
--select * from #work_to_do
-- Объявление Открытие курсора курсора для чтения секций
 
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
OPEN partitions;
 
-- Цикл по секциям
FETCH NEXT FROM partitions INTO @object_id, @index_id, @partition_number, @fragmentation_in_percent, @allow_page_locks, @indextype, @indexname, @objectname, @schemaname;
WHILE @@FETCH_STATUS = 0
BEGIN      
    
        -- Собираем имена объектов по ID
        
        SELECT @objectname = QUOTENAME(w.TABLE_NAME), @schemaname = QUOTENAME(w.SCHEM_NAME),
        @indexname = QUOTENAME(w.INDEX_NAME), @allow_page_locks = w.LOCK, @indextype = QUOTENAME(w.DATA_TYPE),
        @fragmentation_in_percent = w.fragmentation_in_percent
        FROM #work_to_do AS w 
        WHERE  w.object_id = @object_id AND w.index_id = @index_id;
 
        SET @partition_number = COUNT(@partition_number);
 
IF (@fragmentation_in_percent < @fragmentation_in_percent_max AND @allow_page_locks = 'FALSE')
-- включаем блокировки
SET @command = 
N'ALTER INDEX ' + @indexname + N' ON ' + @objectname + N' SET (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON)
ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE;
ALTER INDEX ' + @indexname + N' ON ' + @objectname + N' SET (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON);
UPDATE STATISTICS ' + @schemaname + '.' + @objectname + ' ' + @indexname + ' WITH FULLSCAN, maxdop = ' + @max_degree_of_parallelism +'';
 
IF (@fragmentation_in_percent < @fragmentation_in_percent_max AND @allow_page_locks = 'TRUE')
--реорганизуем индекс
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE;
UPDATE STATISTICS ' + @schemaname + '.' + @objectname + ' ' + @indexname + ' WITH FULLSCAN, maxdop = ' + @max_degree_of_parallelism +'';
 
IF @fragmentation_in_percent >=  @fragmentation_in_percent_max
--перестроение индекса
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, SORT_IN_TEMPDB = ' + @SORT_IN_TEMPDB + ', ONLINE = ' + @ONLINE + ', maxdop = ' + @max_degree_of_parallelism + ');' ;
 
IF @fragmentation_in_percent >=  @fragmentation_in_percent_max AND @indextype IN ('[text]', '[ntext]', '[image]', '[FILESTREAM]') --= 'text' OR @indextype = 'ntext' OR @indextype = 'image' OR @indextype = 'FILESTREAM'
--перестроение индекса
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, SORT_IN_TEMPDB = ' + @SORT_IN_TEMPDB + ', ONLINE = ' + @ONLINEALT + ', maxdop = ' + @max_degree_of_parallelism + ');' ;
 
IF (@partition_number > 1)
SET @command = @command + N' PARTITION=' + CAST(@partition_number AS nvarchar(10));
 
 
      -- Выполняем команду   
        
        EXEC sp_executesql @command
        PRINT N'Executed0: ' + @command;
        PRINT N'Index: object_id=' + STR(@object_id) + ', index_id=' + STR(@index_id) + ', fragmentation_in_percent=' + STR(@fragmentation_in_percent);
        PRINT N'-------------------------'
        /*print @indexname;
        PRINT + @partition_number
        print getdate();
        PRINT + @indextype;
        PRINT + @object_id;
        PRINT + @allow_page_locks;
        PRINT + @objectname;
        PRINT + @fragmentation_in_percent;*/
        
        SET @command = NULL;
        SET @indextype = NULL;
        -- Следующий элемент цикла
        FETCH NEXT FROM partitions INTO @object_id, @index_id, @partition_number, @fragmentation_in_percent, @allow_page_locks, @indextype, @indexname, @objectname, @schemaname;
 
    END;
 
-- Закрытие курсора
CLOSE partitions;
DEALLOCATE partitions;
 
-- Удаление временной таблицы
DROP TABLE #work_to_do;
 
GO

скрип для нескольких баз:

SQL
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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
-- Скрипт выполняет реорганизацию либо дефрагменатцию индексов баз данных
--  Алгоритм работы:
--      1. Отбираются базы данных по задаваемому условнию
--      2. Для каждой из баз данных:
--          2.1. Собираются информация обо всех фрагментированных индексах (степерь фрагментации более 5%)
--          2.2. Если фрагментация менее или равна 30% тогда выполняется дефрагментация, иначе реиндексация индекса
--      3. Отправляется электронное сообщение о результате работы с использованием настроенного почтового профиля
-- Автор: Онянов Виталий (Tavalik.ru)
-- Версия от 09.08.2017
-- Свежие версии скриптов: https://github.com/Tavalik/SQL_TScripts
 
-- Скрипт доработан Филиппов Сергей 02.01.2025г (автору послал копию скрипта)
---------------------------------------------
-- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ
-- Условие для выборки, '%' - все базы данных 
DECLARE @namelike VARCHAR(100) = 'TEST%'
-- Имя почтового профиля, для отправки электонной почты                                    
DECLARE @profilename AS nvarchar(100) = 'postfix-sql'
-- Получатели сообщений электронной почты, разделенные знаком ";"               
DECLARE @recipients AS nvarchar(500) = 'ips@yandex.ru ; sql@yandex.ru'
 
-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ 
DECLARE @database_id VARCHAR(100) -- ID баз данных
DECLARE @database_name VARCHAR(100) -- Имена баз данных
DECLARE @command nvarchar(MAX) -- инструкция T-SQL для дефрагментации либо ренидексации
DECLARE @subject AS NVARCHAR(1000) = '' -- тема сообщения
DECLARE @finalmassage AS NVARCHAR(4000) = '' -- текст сообщения
 
-------------------------------------------
-- ТЕЛО СКРИПТА
USE master
 
-- Отключаем вывод количества возвращаемых строк, это несколько ускорит обработку
SET NOCOUNT ON;
 
-- Выбираем базы данных
DECLARE DBcursor CURSOR FOR 
(
    SELECT 
        database_id AS database_id,
        name AS database_name
    FROM sys.databases d
    WHERE 
        d.name <> 'tempdb'
        AND d.name <> 'master'
        AND d.name <> 'model'
        AND d.name <> 'msdb'
        AND d.state_desc = 'ONLINE' -- база должна быть в сети 
        AND d.name LIKE @namelike -- база должна содержать указанное слово
        --AND d.database_id > '4' -- база должна содержать указанное слово
)
 
-- Цикл по всем базам, попавшим в выборку
OPEN DBcursor
FETCH NEXT FROM DBcursor INTO @database_id, @database_name
WHILE @@FETCH_STATUS = 0
    BEGIN
 
    -- База данных из цикла
    PRINT N'----------------------------------------------------------';
    PRINT N'USE [' + @database_name + N']'
 
 
SET @command =
N'USE [' + @database_name + N']
-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ 
DECLARE @object_id int; -- ID объекта
DECLARE @index_id int; -- ID индекса
DECLARE @partition_number bigint; -- количество секций если индекс секционирован
DECLARE @schemaname nvarchar(130); -- имя схемы в которой находится таблица
DECLARE @objectname nvarchar(130); -- имя таблицы 
DECLARE @indexname nvarchar(130); -- имя индекса
DECLARE @partitionnum bigint; -- номер секции
DECLARE @fragmentation_in_percent float; -- процент фрагментации индекса
DECLARE @command nvarchar(4000); -- инструкция T-SQL для дефрагментации либо ренидексации
DECLARE @command1 nvarchar(4000); -- инструкция T-SQL для дефрагментации либо ренидексации
DECLARE @command2 nvarchar(4000); -- инструкция T-SQL для дефрагментации либо ренидексации
DECLARE @allow_page_locks bit; -- параметр блокировки
DECLARE @indextype nvarchar(130); -- ТИП индекса (ищем text, ntext, image или FILESTREAM)
DECLARE @max_degree_of_parallelism nvarchar(3) = 8; --параллелизм
DECLARE @SORT_IN_TEMPDB nvarchar(3) = ''ON''; --сортировать в tempdb
DECLARE @ONLINE nvarchar(3) = ''ON''; -- онлайн перестроение (за исключением типов индекесов text, ntext, image или FILESTREAM)
DECLARE @ONLINEALT nvarchar(3) = ''OFF''; -- выключить онфлайн перестроение (типы индекесов text, ntext, image или FILESTREAM)
DECLARE @page_count int = 128; --число страниц для фрагментации
DECLARE @avg_fragmentation_in_percent float = 5.0; --процент фрагментации для РЕОРГАНИЗАЦИИ индекса
DECLARE @fragmentation_in_percent_max float = 30.0; -- процент фрагментации индекса для РЕБИЛДА индекса
-------------------------------------------
-- ТЕЛО СКРИПТА
-- Отбор таблиц и индексов с помощью системного представления sys.dm_db_index_physical_stats
-- Отбор только тех объектов которые:
--   являются индексами (index_id > 0)
--   фрагментация которых более 5% 
--   количество страниц в индексе более 128 
 
SELECT
    dm.object_id,
    dm.index_id,
    dm.partition_number,
    dm.avg_fragmentation_in_percent AS fragmentation_in_percent,
    ind.allow_page_locks AS LOCK,
    ind.name AS INDEX_NAME,
    obj.name AS TABLE_NAME,
    schem.name AS SCHEM_NAME,
    (SELECT top 1 inf.DATA_TYPE from INFORMATION_SCHEMA.COLUMNS AS inf  Where inf.TABLE_NAME = obj.name AND inf.DATA_TYPE in (''text'', ''ntext'', ''image'', ''FILESTREAM'')) AS DATA_TYPE
INTO #work_to_do
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') AS dm
    LEFT JOIN sys.indexes AS ind ON dm.object_id = ind.object_id AND dm.index_id = ind.index_id
    LEFT JOIN sys.objects AS obj ON dm.object_id = obj.object_id
    LEFT JOIN sys.schemas AS schem ON obj.schema_id = schem.schema_id
WHERE dm.index_id > 0
    AND dm.avg_fragmentation_in_percent > @avg_fragmentation_in_percent
    AND dm.page_count > @page_count;
 
-- Объявление Открытие курсора курсора для чтения секций
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
OPEN partitions;
 
-- Цикл по секциям
FETCH NEXT FROM partitions INTO @object_id, @index_id, @partition_number, @fragmentation_in_percent, @allow_page_locks, @indexname, @objectname, @schemaname, @indextype;
WHILE @@FETCH_STATUS = 0
BEGIN      
 
-- Собираем имена объектов по ID
SELECT @objectname = QUOTENAME(w.TABLE_NAME), @schemaname = QUOTENAME(w.SCHEM_NAME),
@indexname = QUOTENAME(w.INDEX_NAME), @allow_page_locks = w.LOCK, @indextype = QUOTENAME(w.DATA_TYPE),
@fragmentation_in_percent = w.fragmentation_in_percent
FROM #work_to_do AS w 
WHERE  w.object_id = @object_id AND w.index_id = @index_id;
 
SET @partition_number = count(@partition_number);
 
IF (@fragmentation_in_percent < @fragmentation_in_percent_max and @allow_page_locks = ''FALSE'')
-- включаем блокировки
SET @command = 
N''ALTER INDEX '' + @indexname + N'' ON '' + @objectname + N'' SET (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON)
ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE;
ALTER INDEX '' + @indexname + N'' ON '' + @objectname + N'' SET (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON);
UPDATE STATISTICS '' + @schemaname + ''.'' + @objectname + '' '' + @indexname + '' WITH FULLSCAN, maxdop = '' + @max_degree_of_parallelism +'''';
 
IF (@fragmentation_in_percent < @fragmentation_in_percent_max AND @allow_page_locks = ''TRUE'')
--реорганизуем индекс
SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE;
UPDATE STATISTICS '' + @schemaname + ''.'' + @objectname + '' '' + @indexname + '' WITH FULLSCAN, maxdop = '' + @max_degree_of_parallelism +'''';
 
IF @fragmentation_in_percent >=  @fragmentation_in_percent_max
--перестроение индекса
SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, SORT_IN_TEMPDB = '' + @SORT_IN_TEMPDB + '', ONLINE = '' + @ONLINE + '', maxdop = '' + @max_degree_of_parallelism + '');'' ;
 
IF @fragmentation_in_percent >=  @fragmentation_in_percent_max AND @indextype in (''[text]'', ''[ntext]'', ''[image]'', ''[FILESTREAM]'') --= ''text'' OR @indextype = ''ntext'' OR @indextype = ''image'' OR @indextype = ''FILESTREAM''
--перестроение индекса
SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, SORT_IN_TEMPDB = '' + @SORT_IN_TEMPDB + '', ONLINE = '' + @ONLINEALT + '', maxdop = '' + @max_degree_of_parallelism + '');'' ;
 
IF (@partition_number > 1)
SET @command = @command + N'' PARTITION='' + CAST(@partition_number AS nvarchar(10));
 
-- Выполняем команду
        
        EXEC sp_executesql @command
        PRINT N''Executed0: '' + @command;
        PRINT N''Index: object_id='' + STR(@object_id) + '', index_id='' + STR(@index_id) + '', fragmentation_in_percent='' + STR(@fragmentation_in_percent);
        PRINT N''-------------------------''
 
        set @command = NULL;
        set @indextype = NULL;
 
-- Следующий элемент цикла
        FETCH NEXT FROM partitions INTO @object_id, @index_id, @partition_number, @fragmentation_in_percent, @allow_page_locks, @indexname, @objectname, @schemaname, @indextype;
 
    END;
 
-- Закрытие курсора
CLOSE partitions;
DEALLOCATE partitions;
 
DROP TABLE #work_to_do;'
 
    BEGIN TRY
        EXEC sp_executesql @command 
        SET @finalmassage = @finalmassage + 'Успешное выполнение операций обслуживания индексов для базы данных ' + @database_name + CHAR(13) + CHAR(13)
    END TRY
    BEGIN CATCH  
    
        -- Ошбика выполнения операции
        SET @subject = 'БЫЛИ ОШИБКИ при выполнении операций обслуживания индексов '
        SET @finalmassage = @finalmassage + 'ОШИБКА обслуживания индекса для базы данных ' + @database_name + CHAR(13) + CHAR(13)
            + 'Код ошибки: ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + CHAR(13) + CHAR(13)
            + 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
            + 'Текст T-SQL: ' + CHAR(13) + @command + CHAR(13) + CHAR(13) 
        
        PRINT @finalmassage;
    END CATCH;
    
    
    -- Следующая база данных
    FETCH NEXT FROM DBcursor INTO @database_id, @database_name
    END;
 
CLOSE DBcursor;
DEALLOCATE DBcursor;
 
-- Формируем сообщение об успешном или не успешном выполнении операций
IF @subject = ''
BEGIN
    -- Успешное выполнение всех операций
    SET @subject = 'Успешное выполнение операций обслуживания индексов '
END
 
-- Если задан профиль электронной почты, отправим сообщение
PRINT N'----------------------------------------------------------'
IF @profilename <> ''
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profilename,
    @recipients = @recipients,
    @body = @finalmassage,
    @subject = @subject;
 
 
-- Выводим сообщение о результате
SELECT
    @subject AS subject, 
    @finalmassage AS finalmassage 
 
GO

запрос фрагментации(для проверки)

SQL
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
--ЗАПРОС ФРАГМЕНТАЦИИ
USE [TEST2]
 
-------------------------------------------
-- ТЕЛО СКРИПТА
 
-- Отбираем объекты, которые:
--    являются индексами (index_id > 0)
--   фрагментация которых более 5%
--   количество страниц в индексе более 128
SELECT
   OBJECT_NAME(object_id) AS TableName,
   object_id,
    index_id,
    partition_number,
   page_count,
   partition_number,
   index_type_desc,
    avg_fragmentation_in_percent,
   (SELECT allow_page_locks FROM sys.indexes WHERE sys.dm_db_index_physical_stats.object_id = sys.indexes.object_id AND sys.dm_db_index_physical_stats.index_id = sys.indexes.index_id) AS LOCK
   
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
   
WHERE index_id > 0
   AND avg_fragmentation_in_percent > 5.0
   AND page_count > 128
ORDER BY avg_fragmentation_in_percent DESC
 
GO

@invm · 05.01.2025, 15:30

Все велосипеды давно изобретены и проверены в работе
https://ola.hallengren.com/

@uaggster · 05.01.2025, 15:46

А зачем делать обслуживание индексов?

@4ygo · 05.01.2025, 16:41 **[ТС]**

Забыл!

скрипт еще включает оффлайн режим режим ребилда для таблиц с типом данных text, ntext, image или FILESTREAM (это не для баз 1с)

ребилд, как и статистика могут выполняться с параллелизмом-задается вручную, не зависит от настроек вашего сервера

остальное не менял. если фрагментация более 5% реорганизовать, если более 30% перестроить
отправляет сообщение на почту, если настроить DataMail и вписать данные в скрипт

в планах - сделать многозадачность(не многопоточность! видел у кого-то такую идею в интернете). т.т. чтобы скрипт сам разделял задачу по подзадачи и разбрасывал их по потокам. Интересная идея у ребят

Добавлено через 1 минуту
invm,
гляну! спасибо!)
не могу ничего сказать пока не разберусь что и как он делает)

uaggster,
если вы считаете что это не нужно - значит Вам это не нужно, вот и все)

Добавлено через 17 минут
uaggster,
почитал ваши сообщения про обслуживание индексов. Вероятно, вы в чем-то правы.

но! для меня написание этого скрипта было интересной задачей.

и, не вижу ничего плохого в том, что индексы будут не фрагментированы.
планы запросов будут оптимальные строиться, как минимум.

Добавлено через 31 минуту
иииии....
похоже, я где-то накосячил, ибо размер тестовых баз увеличился))) зараза)))
не понимаю чем вызвано....

@katamoto · 05.01.2025, 17:26

Сообщение от 4ygo

и, не вижу ничего плохого в том, что индексы будут не фрагментированы.
планы запросов будут оптимальные строиться, как минимум.

Это очень слабо зависимые вещи

@Аватар · 05.01.2025, 18:43

Сообщение от 4ygo

ибо размер тестовых баз увеличился

а чего им не увеличивается? например куда система засунет нефрагментированный индекс если исходный фрагментирован. это так из общих соображений

@4ygo · 05.01.2025, 18:48 **[ТС]**

katamoto,
ок.
тогда еще - последовательные чтение/запись на SSD носителях намного быстрее, чем рандомные.
это не я придумал. Это приводят результаты тестов.
н/р
Sequential Read (Q= 32,T= 1) : 1486.188 MB/s
Sequential Write (Q= 32,T= 1) : 814.253 MB/s
Random Read 4KiB (Q= 8,T= 8) : 461.785 MB/s [ 112740.5 IOPS]
Random Write 4KiB (Q= 8,T= 8) : 49.699 MB/s [ 12133.5 IOPS]
Random Read 4KiB (Q= 32,T= 1) : 475.963 MB/s [ 116201.9 IOPS]
Random Write 4KiB (Q= 32,T= 1) : 53.530 MB/s [ 13068.8 IOPS]
Random Read 4KiB (Q= 1,T= 1) : 21.178 MB/s [ 5170.4 IOPS]
Random Write 4KiB (Q= 1,T= 1) : 21.353 MB/s [ 5213.1 IOPS]

и как бы никто не отказывается от регламентных работ, несмотря на SSD

Давайте без холиваров. Мне реально интересен скрипт. И я не понимаю, за счет чего у меня база после этого скрипта выросла с 9гб до 11гб..... Сейчас проверю, но думаю - это пересчет статистики как раз влияет. Мб у кого-то есть ответ?)

Добавлено через 4 минуты
Аватар, т.е. надо ждать пока отработает служба, и удалит "удаленные фрагментированные индексы"
а мб знаете, как ее запустить в ручном режиме? как почистить? DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE не помогают

@katamoto · 05.01.2025, 19:20

Сообщение от 4ygo

И я не понимаю, за счет чего у меня база после этого скрипта выросла с 9гб до 11гб.....

При ребилде, условно говоря, строится копия индекса, старый потом удаляется. На первом этапе, если в базе не было достаточно свободного места, вполне естественно, что он построился в конце файла с соотв. приростом. На втором этапе старый индекс удалился уже "внутри" файла и место в ФС не отдал. Это нормально, так часто бывает

@4ygo · 05.01.2025, 20:03 **[ТС]**

katamoto, да, уже прочитал про то, что ядро удаляет старый индекс сразу. понятно, почему не уменьшается размер базы - оно как бы 'частично пустое', после удаления.
но у меня в скрипте стоит - сортировка в TempDB. Т.е. временный индекс должен был формироваться не в основной базе.
Это могло бы звучать логично, но на деле так:

Code
1
2
Требования к месту на диске: при установке параметра SORT_IN_TEMPDB в значение ON требуется достаточно свободного места на диске в базе данных tempdb, чтобы хранить запуски промежуточной сортировки, и достаточно места на диске в целевой файловой группе, чтобы хранить новый индекс.
https://learn.microsoft.com/ru-ru/sql/relational-databases/indexes/sort-in-tempdb-option-for-indexes?view=sql-server-ver16

Спасибо) с увеличением размера базы, вроде разобрались. Сейчас гляну, как это можно победить. Просто на будущее. Мало ли.

Добавлено через 13 минут
все норм. DBCC SHRINKDATABASE ('DBName') . и размер вернулся к нужному. Т.е. мы были правы - там просто пустое пространство оставалось)

вопрос по теме - никто не хочет покопаться в этом скрипте,да?) совсем-совсем?) там реально немного, и все очень расписано. буквально 2-3 относительно сложных места.))

@katamoto · 05.01.2025, 20:10

Сообщение от 4ygo

DBCC SHRINKDATABASE ('DBName') . и размер вернулся к нужному.

Не нужно так делать. При сжатии индексы обратно фрагментируются. Просто оставьте место как есть, и всё

@4ygo · 05.01.2025, 20:15 **[ТС]**

Да, спасибо. Это на тестовой, ради эксперимента))
Кроме того, можно базу реорганизовать (я делал сжатие через ms sms, там есть галочка - реорганизовать)

@katamoto · 05.01.2025, 20:32

Сообщение от 4ygo

Кроме того, можно базу реорганизовать (я делал сжатие через ms sms, там есть галочка - реорганизовать)

Если вы считаете, что при этом будет реорганизация индексов, то вы ошибаетесь. С этой опцией, как раз, начнётся перемещение страниц данных из конца файла во все свободные "дырки" внутри файла, вызывая фрагментацию. Без этой опции просто усечётся свободное место в конце файла (если оно там есть)

@4ygo · 05.01.2025, 20:34 **[ТС]**

кстати. после сжатия, запрос по фрагментации таблиц выдал 60+ объектов, и скрипт их отработал за 17сек. база выросла с 8.6 до 9.2. Т.е. шринк на полностью ДЕфрагментированной базе навредил, но не сказать, что критично. Т.е. иногда может быть выгодно так делать(мы уменьшили базу на 2гб). Смысла в этом особого не вижу, но ситуации бывают разными.

Добавлено через 1 минуту
katamoto, класс! сейчас так и сделаю! посмотрим, сколько по времени скрипт будет приводить в порядок индексы!

@Аватар · 05.01.2025, 20:40

Сообщение от 4ygo

иногда может быть выгодно так делать(мы уменьшили базу на 2гб)

если емкость диска измеряется в терабайтах то как то смешно, вроде того что каждая сэкономленная спичка удар по экономике заклятых, была такая прибаутка то ли в шутку, то ли всеръез лет 40 назад )

@4ygo · 05.01.2025, 20:52 **[ТС]**

Сообщение от Аватар

если емкость диска измеряется в терабайтах то как то смешно, вроде того что каждая сэкономленная спичка удар по экономике заклятых, была такая прибаутка то ли в шутку, то ли всеръез лет 40 назад )

)))хах) это та самая спичка, которая ломает спину верблюда, имелась ввиду))) есть знакомые парни, у которых много-много баз, и вечная нехватка дискового пространства, но в целом да - затея фигня)))

ладно. пока беру скрипт в работу. может, кто-нить еще и проверит его)

буду гонять его каждую ночь(благо, что 5 минут, которые он работает, а в последствии и меньше, мне не мешают), а раз в 2-4 недели буду гонять его же, но чтобы отрабатывал ВСЕ объекты. Ибо встроенные инструменты почему то выдают ошибки на моих базах. печаль какая-то. иначе я бы не потратил 2 недели на этот скрипт. хых)

Новые блоги и статьи Все статьи Все блоги /
Модель микоризы: классовый агентный подход 3 anaschu 06.01.2026 aa0a7f55b50dd51c5ec569d2d10c54f6/ O1rJuneU_ls https:/ / vkvideo. ru/ video-115721503_456239114	Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ФедосеевПавел 06.01.2026 Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ВВЕДЕНИЕ Введу сокращения: аналоговый ПИД — ПИД регулятор с управляющим выходом в виде числа в диапазоне от 0% до. . .	Модель микоризы: классовый агентный подход 2 anaschu 06.01.2026 репозиторий https:/ / github. com/ shumilovas/ fungi ветка по-частям. коммит Create переделка под биомассу. txt вход sc, но sm считается внутри мицелия. кстати, обьем тоже должен там считаться. . . .	Расчёт токов в цепи постоянного тока igorrr37 05.01.2026 / * Дана цепь постоянного тока с сопротивлениями и напряжениями. Надо найти токи в ветвях. Программа составляет систему уравнений по 1 и 2 законам Кирхгофа и решает её. Последовательность действий:. . .
Новый CodeBlocs. Версия 25.03 palva 04.01.2026 Оказывается, недавно вышла новая версия CodeBlocks за номером 25. 03. Когда-то давно я возился с только что вышедшей тогда версией 20. 03. С тех пор я давно снёс всё с компьютера и забыл. Теперь. . .	Модель микоризы: классовый агентный подход anaschu 02.01.2026 Раньше это было два гриба и бактерия. Теперь три гриба, растение. И на уровне агентов добавится между грибами или бактериями взаимодействий. До того я пробовал подход через многомерные массивы,. . .	Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Programma_Boinc 28.12.2025 Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Налог на собак: https:/ / ********/ gallery/ V06K53e Финансовый отчет в Excel: https:/ / ********/ gallery/ bKBkQFf Пост отсюда. . .	Кто-нибудь знает, где можно бесплатно получить настольный компьютер или ноутбук? США. Programma_Boinc 26.12.2025 Нашел на реддите интересную статью под названием Anyone know where to get a free Desktop or Laptop? Ниже её машинный перевод. После долгих разбирательств я наконец-то вернула себе. . .

@invm 3614 / 2135 / 756 Регистрация: 02.06.2013 Сообщений: 5,169
	05.01.2025, 15:30
	Все велосипеды давно изобретены и проверены в работе https://ola.hallengren.com/ 1

@uaggster 1304 / 358 / 97 Регистрация: 14.10.2022 Сообщений: 1,087
	05.01.2025, 15:46
	А зачем делать обслуживание индексов? 0

@4ygo 0 / 0 / 0 Регистрация: 04.08.2019 Сообщений: 10
	05.01.2025, 16:41 [ТС]
	Забыл! скрипт еще включает оффлайн режим режим ребилда для таблиц с типом данных text, ntext, image или FILESTREAM (это не для баз 1с) ребилд, как и статистика могут выполняться с параллелизмом-задается вручную, не зависит от настроек вашего сервера остальное не менял. если фрагментация более 5% реорганизовать, если более 30% перестроить отправляет сообщение на почту, если настроить DataMail и вписать данные в скрипт в планах - сделать многозадачность(не многопоточность! видел у кого-то такую идею в интернете). т.т. чтобы скрипт сам разделял задачу по подзадачи и разбрасывал их по потокам. Интересная идея у ребят Добавлено через 1 минуту invm, гляну! спасибо!) не могу ничего сказать пока не разберусь что и как он делает) uaggster, если вы считаете что это не нужно - значит Вам это не нужно, вот и все) Добавлено через 17 минут uaggster, почитал ваши сообщения про обслуживание индексов. Вероятно, вы в чем-то правы. но! для меня написание этого скрипта было интересной задачей. и, не вижу ничего плохого в том, что индексы будут не фрагментированы. планы запросов будут оптимальные строиться, как минимум. Добавлено через 31 минуту иииии.... похоже, я где-то накосячил, ибо размер тестовых баз увеличился))) зараза))) не понимаю чем вызвано.... 0

@4ygo 0 / 0 / 0 Регистрация: 04.08.2019 Сообщений: 10
	05.01.2025, 18:48 [ТС]
	katamoto, ок. тогда еще - последовательные чтение/запись на SSD носителях намного быстрее, чем рандомные. это не я придумал. Это приводят результаты тестов. н/р Sequential Read (Q= 32,T= 1) : 1486.188 MB/s Sequential Write (Q= 32,T= 1) : 814.253 MB/s Random Read 4KiB (Q= 8,T= 8) : 461.785 MB/s [ 112740.5 IOPS] Random Write 4KiB (Q= 8,T= 8) : 49.699 MB/s [ 12133.5 IOPS] Random Read 4KiB (Q= 32,T= 1) : 475.963 MB/s [ 116201.9 IOPS] Random Write 4KiB (Q= 32,T= 1) : 53.530 MB/s [ 13068.8 IOPS] Random Read 4KiB (Q= 1,T= 1) : 21.178 MB/s [ 5170.4 IOPS] Random Write 4KiB (Q= 1,T= 1) : 21.353 MB/s [ 5213.1 IOPS] и как бы никто не отказывается от регламентных работ, несмотря на SSD Давайте без холиваров. Мне реально интересен скрипт. И я не понимаю, за счет чего у меня база после этого скрипта выросла с 9гб до 11гб..... Сейчас проверю, но думаю - это пересчет статистики как раз влияет. Мб у кого-то есть ответ?) Добавлено через 4 минуты Аватар, т.е. надо ждать пока отработает служба, и удалит "удаленные фрагментированные индексы" а мб знаете, как ее запустить в ручном режиме? как почистить? DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE не помогают 0

@4ygo 0 / 0 / 0 Регистрация: 04.08.2019 Сообщений: 10
	05.01.2025, 20:15 [ТС]
	Да, спасибо. Это на тестовой, ради эксперимента)) Кроме того, можно базу реорганизовать (я делал сжатие через ms sms, там есть галочка - реорганизовать) 0

@4ygo 0 / 0 / 0 Регистрация: 04.08.2019 Сообщений: 10
	05.01.2025, 20:34 [ТС]
	кстати. после сжатия, запрос по фрагментации таблиц выдал 60+ объектов, и скрипт их отработал за 17сек. база выросла с 8.6 до 9.2. Т.е. шринк на полностью ДЕфрагментированной базе навредил, но не сказать, что критично. Т.е. иногда может быть выгодно так делать(мы уменьшили базу на 2гб). Смысла в этом особого не вижу, но ситуации бывают разными. Добавлено через 1 минуту katamoto, класс! сейчас так и сделаю! посмотрим, сколько по времени скрипт будет приводить в порядок индексы! 0