Скрипты для реиндексации, перестройки индексов в SQL 2005
SQL я знаю достаточно мало. Особенно тонкости. Поэтому просьба к знающим людям - дополните меня и ответьте на вопросы, которые есть в статье.
Как советуют знающие люди в своих статьях (в частности Гилев в статье по созданию базы 1с в SQL 2005) нужно регулярно дефрагментировать индексы и обновлять статистику. Для этого есть штатные средства и простой скрипт
http://www.gilev.ru/1c/mssql/dbreindex.htm
USE db1cut
GO
DECLARE @MyTable varchar(252)
DECLARE @MyIndex varchar(252)
DECLARE @DSQL varchar(8000)
DECLARE MyCursor CURSOR FOR
SELECT o.name, i.name
FROM sysobjects o INNER JOIN sysindexes i ON o.id = i.id
WHERE (o.xtype = 'U') AND (INDEXPROPERTY(i.id, i.name, 'isStatistics') = 0) AND (i.dpages > 0)
ORDER BY o.name, i.indid
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @MyTable, @MyIndex
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'Перестройка индекса '+@MyIndex+' из таблицы '+@MyTable
SET @DSQL = ' BEGIN TRY
ALTER INDEX '+@MyIndex+' ON '+@MyTable+
' REBUILD WITH (ONLINE = ON,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
END TRY
BEGIN CATCH ALTER INDEX '+@MyIndex+' ON '+@MyTable+
' REBUILD WITH (ONLINE = OFF,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON) END CATCH;'
EXEC(@DSQL)
FETCH NEXT FROM MyCursor INTO @MyTable, @MyIndex
END
CLOSE MyCursor
DEALLOCATE MyCursor
Тут вроде всё понятно - пробегает по индексам и перестраивает их... Обычно перестройка (Rebuild) сопровождается обновлением статистики, но параметр STATISTICS_NORECOMPUTE = ON отключает пересчет статистики. В связи с этим вопрос: из каких соображение в данном скрипте отключен пересчет статистики? Ведь в любом случае после реорганизации/перестройки требуется пересчитать статистику...Или всё-таки нет?
Но данный скрип имеет недостаток - на больших базах долго выполняется. При круглосуточной работе неприменим. Для этого есть скрипты с избирательной перестройкой, дефрагментацией
http://mamyshev.spaces.live.com/blog/cns!89D4F9A2EB82023D!127.trak
use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_defragment_frag_indexes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_defragment_frag_indexes]
go
CREATE PROCEDURE sp_defragment_frag_indexes @maxfrag DECIMAL, @whatif VARCHAR (6) = '' AS
-- Объявляем необходимые переменные
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @objectowner VARCHAR(255)
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @indexname CHAR(255)
DECLARE @dbname sysname
DECLARE @tableid INT
DECLARE @tableidchar VARCHAR(255)
-- На всякий случай проверяем,что база данных пользовательская
SELECT @dbname = db_name();
IF @dbname IN ('master', 'msdb', 'model', 'tempdb')
BEGIN
PRINT ' Эта процедура не может быть запущена для системных БД';
RETURN
END
-- Начинаем проверку уровня фрагментации
-- Вначале объявляем курсор
DECLARE tables CURSOR FOR
SELECT convert(varchar,so.id) FROM sysobjects so JOIN sysindexes si ON so.id = si.id WHERE so.type ='U' AND si.indid < 2 AND si.rows > 0;
-- Затем создаем временную таблицу для хранения информации о фрагментации
CREATE TABLE #fraglist (ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255),IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL);
-- Открываем курсор
OPEN tables
-- Для каждой таблицы в базе данных выполняем команду DBCC SHOWCONTIG
FETCH NEXT FROM tables INTO @tableidchar
WHILE @@FETCH_STATUS = 0
BEGIN
-- Проходим по всем индексам для таблицы
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tableidchar
END
-- Закрываем курсор
CLOSE tables
DEALLOCATE tables
-- Для проверки выводим информацию из временной таблицы
SELECT * FROM #fraglist
-- Теперь необходимо произвести дефрагментацию
-- Вначале опять объявляем курсор
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity FROM #fraglist f JOIN sysobjects so ON f.ObjectId=so.id WHERE ScanDensity <= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Выводим для проверки информацию о начале дефрагментации
SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())
-- Открываем курсор
OPEN indexes
-- Проходим циклом по всем индексам
FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER ON
SELECT @execstr = 'DBCC INDEXDEFRAG (' + @dbname + ', ' + RTRIM(@tablename) + ', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
SELECT 'Выполняем: ' + @execstr;
if @whatif <> 'whatif' EXEC (@execstr);
SET QUOTED_IDENTIFIER OFF
FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag
END
-- Затем закрываем курсор
CLOSE indexes;
DEALLOCATE indexes;
-- Отчитываемся о времени завершения
SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE());
-- Удаляем временную таблицу
DROP TABLE #fraglist;
GO
---------------------------------------------------------------------------------------------
Использование:
---------------------------------------------------------------------------------------------
-- Дефрагментировать индексы таблиц базы данных Test, для которых значение параметра Scan Density < 80%
USE Test
EXEC sp_defragment_frag_indexes 80.00;
Скрипт производит выборочную дефрагментацию регистров...НО! Скрип предназначен для SQL 2000 и для дефрагментации использует конструкцию
DBCC INDEXDEFRAG
Для 2005 она подходит, но советуют использовать конструкцию
Alter Index....Reorganize. Соответственно строчка заменяется.
SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON [' + RTRIM(@tablename) + '] REORGANIZE;'
Кроме того, в скрипте нет обновления статистики
Если добавить, то кусок скрипта нужно исправить на
-- Проходим циклом по всем индексам
FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER ON
SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON [' + RTRIM(@tablename) + '] REORGANIZE;'
PRINT 'Выполняем: ' + @execstr;
if @whatif <> 'whatif' EXEC (@execstr);
SELECT @execstr = 'UPDATE STATISTICS '+RTRIM(@tablename) + ' ' + RTRIM(@indexname)
PRINT 'Выполняем: ' + @execstr;
if @whatif <> 'whatif' EXEC (@execstr);
SET QUOTED_IDENTIFIER OFF
FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag
END
Чуть поискав ещё можно найти скрипт, которые более подробнее анализирует индексы перед дефрагментацией. И кроме того для некоторых из них делает перестройку.
http://msmvps.com/blogs/gladchenko/archive/2008/03/31/1563721.aspx
-- INDEX REBUILD or REORGANIZE
DECLARE @SQL varchar(256), @DB_ID int;
SET @DB_ID = (SELECT DB_ID());
DECLARE reindex CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR
SELECT 'ALTER INDEX ALL ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (SORT_IN_TEMPDB = ON);' AS [Инструкция T-SQL]
FROM sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, 'SAMPLED') AS afp
WHERE afp.database_id = @DB_ID
AND afp.index_type_desc IN ('CLUSTERED INDEX')
AND (afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent <= 60)
AND afp.page_count > 12
UNION ALL
SELECT [Инструкция T-SQL] =
CASE
WHEN afp.avg_fragmentation_in_percent >= 15
OR afp.avg_page_space_used_in_percent <= 60
THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (SORT_IN_TEMPDB = ON);'
WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10)
OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REORGANIZE;'
END
FROM sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, 'SAMPLED') AS afp
JOIN sys.indexes AS i
ON (afp.OBJECT_ID = i.OBJECT_ID AND afp.index_id = i.index_id)
AND afp.database_id = @DB_ID
AND afp.index_type_desc IN ('NONCLUSTERED INDEX')
AND (
(afp.avg_fragmentation_in_percent >= 10 AND afp.avg_fragmentation_in_percent < 15)
OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
)
AND afp.page_count > 12
AND afp.OBJECT_ID NOT IN (
SELECT OBJECT_ID
FROM sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, 'SAMPLED')
WHERE database_id = @DB_ID
AND index_type_desc IN ('CLUSTERED INDEX')
AND (avg_fragmentation_in_percent >= 15 OR avg_page_space_used_in_percent < 60)
AND page_count > 1
)
ORDER BY [Инструкция T-SQL]
OPEN GLOBAL reindex
WHILE 1 = 1
BEGIN
FETCH reindex INTO @SQL
IF @@fetch_status <> 0 BREAK
-- EXEC(@SQL)
PRINT @SQL
END
CLOSE GLOBAL reindex
DEALLOCATE reindex
Скрипт опять-таки забывает про обновление статистики при реорганизации...Может всё-таки обновлять статистику не надо? Если я правильно изменил (ну не знаю я это язык, а проверить ещё не успел), то кусок с обновление статистики будет таким:
CASE
WHEN afp.avg_fragmentation_in_percent >= 15
OR afp.avg_page_space_used_in_percent <= 60
THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (SORT_IN_TEMPDB = ON);'
WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10)
OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REORGANIZE; UPDATE STATISTICS ['+OBJECT_NAME(afp.OBJECT_ID) + '] [' + i.name + '] ;'
END
Но и эти скрипты с выборочной дефрагментацией, как показала моя частная практика, неэффективен по времени и результату... Его, конечно, можно ночью запускать, но уже днем через пару часов интенсивной работы начинаются ЖУТКИЕ тормоза. Вплоть до того, что проведение по управленческому учету документов реализации длится более 10 МИНУТ! Ещё год назад я выяснил, что задержка происходит в одном месте - регистр "Партии товаров на складах". Именно он фрагментируется до состояния каши каждые 3 часа...
Тогда же был написан скрипт по точечной дефрагментации индексов этого регистра. Таблицу sql этого регистра можно посмотреть с помощью какой-нибудь обработки (той, которой пользуюсь, в инете больше не нашел). После этого выполняются последовательно реорганизация и обновления статистики для каждого индекса самой таблицы и таблицы итогов. Это было на SQL 2000.
После перехода на SQL 2005 я решил отказаться от точечной дефрагментации, т.к. на бухгалтерской копии этой же базы, которая крутится на SQL 2005 уже 2 года, таких тормозов с проведением не было (хотя железо там слабее). Но фиг... опять словил те же самые грабли... И написал этот скрипт заново, только теперь с rebuild... В связи с этим вопрос. Rebuild шести индексов каждые 3 часа с очисткой кэша чем чревата кроме устранения всех тормозов в проведении документов??
Скрипт получился такой:
ALTER INDEX _Accum12425_ByDims19545_RTRN ON dbo._AccumRg12425 REBUILD WITH (SORT_IN_TEMPDB = ON);
go
ALTER INDEX _Accum12425_ByPeriod_TRN ON dbo._AccumRg12425 REBUILD WITH (SORT_IN_TEMPDB = ON);
go
ALTER INDEX _Accum12425_ByProperty18792_RTRN ON dbo._AccumRg12425 REBUILD WITH (SORT_IN_TEMPDB = ON);
go
ALTER INDEX _Accum12425_ByRecorder_RN ON dbo._AccumRg12425 REBUILD WITH (SORT_IN_TEMPDB = ON);
go
ALTER INDEX _Accum12443_ByDims_TRRRRRRRRRN ON dbo._AccumRgT12443 REBUILD WITH (SORT_IN_TEMPDB = ON);
go
ALTER INDEX _Accum12443_ByDims19545_TR ON dbo._AccumRgT12443 REBUILD WITH (SORT_IN_TEMPDB = ON);
go
DBCC FREEPROCCACHE
Выполняется каждые 3 часа в пиковое время: с 7 до 21...
источник: https://infostart.ru/public/72532/