gifts2017

План обслуживания БД в SQL

Опубликовал Кирилл Краснов (kirillkr) в раздел Программирование - Практика программирования

Прочитал статью http://infostart.ru/public/60740/ и решил поделиться планом, который действует у нас в компании.

Скрипт производит анализ данных на фрагментаию. В зависимости от степени фрагментации идет переиндексация или пересоздание индекса.
После этого обновляется статистика.

Пороги надо задать в первом запросе.

 

USE db_main;
GO
SET NOCOUNT ON;

-- Rebuild or reorganize indexes based on their fragmentation levels
DECLARE @work_to_do TABLE (
objectid int
, indexid int
, pagedensity float
, fragmentation float
, numrows int
)

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @numrows int
DECLARE @density float;
DECLARE @fragmentation float;
DECLARE @command nvarchar(4000);
DECLARE @fillfactorset bit
DECLARE @numpages int

-- Select indexes that need to be defragmented based on the following
-- * Page density is low
-- * External fragmentation is high in relation to index size
PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)
INSERT @work_to_do
SELECT
f.object_id
, index_id
, avg_page_space_used_in_percent
, avg_fragmentation_in_percent
, record_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f
WHERE
(f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1)
or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0)
or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)

PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))

PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)

SELECT @numpages = sum(ps.used_page_count)
FROM
@work_to_do AS fi
INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

-- Declare the cursor for the list of indexes to be processed.
DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do

-- Open the cursor.
OPEN curIndexes

-- Loop through the indexes
WHILE (1=1)
BEGIN
FETCH NEXT FROM curIndexes
INTO @objectid, @indexid, @density, @fragmentation, @numrows;
IF @@FETCH_STATUS < 0 BREAK;

SELECT
@objectname = QUOTENAME(o.name)
, @schemaname = QUOTENAME(s.name)
FROM
sys.objects AS o
INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE
o.object_id = @objectid;

SELECT
@indexname = QUOTENAME(name)
, @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
FROM
sys.indexes
WHERE
object_id = @objectid AND index_id = @indexid;

IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
ELSE IF @numrows >= 5000 AND @fillfactorset = 0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
ELSE
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;
EXEC (@command);
PRINT convert(nvarchar, getdate(), 121) + N' Done.';
END

-- Close and deallocate the cursor.
CLOSE curIndexes;
DEALLOCATE curIndexes;


IF EXISTS (SELECT * FROM @work_to_do)
BEGIN
PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))
SELECT @numpages = @numpages - sum(ps.used_page_count)
FROM
@work_to_do AS fi
INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))
END
GO


--Update all statistics
PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)
EXEC sp_updatestats
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)
GO

 

Скачать файлы

Наименование Файл Версия Размер Кол. Скачив.
SQL Maintance
.sql 3,98Kb
26.11.09
81
.sql 3,98Kb 81 Скачать

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Кирилл Краснов (kirillkr) 19.11.09 12:36
Сразу прошу прощения - это моя первая публикация на сайте.
И жду вопросов и предложений. Спасибо.
2. Александр Окулов (PowerBoy) 19.11.09 14:23
(0) Группу неверную задал. Причем тут "статистика"? Надо 1С+SQL.
а так с почином +.
3. Кирилл Краснов (kirillkr) 19.11.09 15:08
(2) Извини. Не нашел такую группу. Как точно она называется? Статистику убираю.
5. Игорь (VIA_1C) 26.11.09 07:30
SQL Server 2008, выдает ошибку:

Msg 102, Level 15, State 1, Line 36
Incorrect syntax near '('.
6. Кирилл Краснов (kirillkr) 26.11.09 09:21
(5) попробуй аттач, может что-то некорректно вставилось.
7. Виталий Глазунов (Altez) 20.01.10 03:18
(0)Дальнейших творческих успехов автору

по прежнему в розыске статьи по обслуживанию postgres
8. Яшин Антон (Anyxwar) 09.08.10 18:39
а для какого скуля это скрипт?2000 или 2005?
9. Кирилл Краснов (kirillkr) 10.08.10 07:20
(8) 2005, но должен и для 2000 подойти
10. Александр Чебаненко (alexchebanenko) 05.11.10 09:19
Тоже что и у VIA_1C:
SQL Server 2005:

Msg 102, Level 15, State 1, Line 36
Incorrect syntax near '('.
11. Кирилл Краснов (kirillkr) 08.11.10 12:54
(10) попробуйте скачать файл, а не копировать из браузера текст. На прошлой неделе как раз настраивал текст отсюда на новом сервере. Прекрасно работает.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа