gifts2017

Скрипты для реиндексации, перестройки индексов в SQL 2005

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

Пару скриптов, их доработка и обсуждение

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... 

 

Осталось сделать выбор на ночной скрипт выборочной дефрагментации, о которых я говорил выше... Какой посоветуете?

Да и на выходной в ночь, наверное нужно запускать полную перестройку индексов и обновление статистики?

См. также

PowerTools от 1 000
Подписаться Добавить вознаграждение
Комментарии
1. Александр Цегельников (markers) 07.07.10 06:56
Подсказать не могу, но однозначно плюс за сборку всего в 1 месте!
2. Наталия Мастербатова (zzz_natali) 07.07.10 10:28
Тебе сюда:
http://www.sql.ru/forum
Но там народ слишком манерный(профессиональный) - разжевывать не будут. Что-то на молекулярном уровне подскажут, а концептуально и академически прокачивайся сам.
3. Роман Озеряный (rozer) 07.07.10 13:04
Начинающим можно посоветовать использовать Maintenance Plans...
4. Александр Медведев (anig99) 07.07.10 20:07
(3) Maintenance Plans - вещь в данном случае неподходящая
5. Александр Медведев (anig99) 07.07.10 20:08
(2) вот-вот....я в зависать ещё в одном форуме... мне пока хватает тут (:
6. Vitaliy (idef) 10.07.10 08:50
Rebuild шести индексов каждые 3 часа с очисткой кэша чем чревата кроме устранения всех тормозов в проведении документов??

Череват тормозами базы на время перестройки индекса

Для какой-то оценки проделанной вами работы неплохо привести статистику: размер базы, размер соответствующих таблиц и индексов, время выполнения запроса до и после скрипта, скорость деградации производительности в течении 3 часов между запусками скриптов. Кроме того неплохо знать конфигурацию сервера и конфигурацию 1С.

ПС. А какой смысл очистки кэша каждые 3 часа?
Вы понимаете что этой операцией вы удаляете скомпилированные запросы?
7. Александр Медведев (anig99) 12.07.10 08:07
(6) Да... Понимаю, что очисткой кэша удаляю скомпилированные запросы. Но очистку кэша рекомендуют делать после обновления статистики. Практика показывает, что иногда достаточно только очистки кэша, чтобы скорость записи в регистр увеличилась.
Размер базы 100 гБ.
Деградация производительности... Снижение скорости оперативного проведения по регистру партии товаров на складах с 3-4 секунд до 60-90 секунд. Снижение скорости проведение по регистру партии товаров на складах в документах более 3-4 дней с 5-6 секунд до 500 и более секунд. При этом фрагментация индексов регистра партии товаров на складах не превышает 5 процентов...

Платформа 1с 8.2.11.236 Конфа УПП 1.3 Win 2003 x64 SQL 2005 x64. 2 четырех ядерных XEON по 2ghz, 16 Гб оперативы на IBM серваке.... Но... Эти же самые затыки наблюдались на всех версиях 8.1 и УПП 1.2. На 32х битной оси и SQL 2000.
8. Vitaliy (idef) 12.07.10 16:10
(7) ИМХО проблемы у вас не в фрагментации индексов, а в большом количестве приходов, в результате чего Скуль считает более эффективным сканирование таблиц, чем использование индексов. Тут надо смотреть план запроса и сам тормозной запрос, возможно его легче переделать, чем каждый раз "греметь винтами", кста, а дисковую систему почему сокрыли???
9. Александр Медведев (anig99) 13.07.10 08:36
(8) и как бы в 1с этого не учли? По-моему, тут больше проблема в том, что постоянно исправляют документы за прошлые числа (не будет развивать тему запрета изменений задним числом и т.д. - 1с тупо не учел специфику РЕАЛЬНОЙ работы и не сделал удобного механизма альтернативного неоперативному исправлению и перепроведения документов).
Согласен, что нужно анализировать запросы на уровне SQL. Только опыта в этом никакого. Да и после этого не совсем понятно, что делать.
10. Александр Медведев (anig99) 13.07.10 08:41
(8) Пока я вижу практический результат от реиндексации, обновления статистики и очистки кэша - устранения провала в производительности. Может попробовать только обновление статистики?

По дискам - tempdb, log и сама база данных разнесены на физически разные диски...
Думаете проблема может быть во фрагментации? На диске с базой ничего другого нет.
11. Vitaliy (idef) 13.07.10 19:36
(9) А вы считаете что в 1С всЁ учитывают ;-)
А когда через год база будет 200Гб - каждый час реиндексировать(дефрагментировать) базу???

Проблема здесь в том что производительность в v8 надо рассматривать в рамках всей системы, т.е. аппаратная платформа+сервер БД+Сервер приложений+платформа 1С.
Базы размером более 50Г уже можно считать большими и настройка таких баз должна выполнятся индивидуально. Возьмите к примеру файловые БД - у них очень хорошая производительность для объемов до 1-2Гб в однопользовательском режиме, а с некоторого объема уже начинаются приколы.
Я не считаю что ваш путь не правильный. Но, может стоит начать разбираться с индексами и статистикой? Это даст гораздо больший выиграш в скорости и масштабируемости. При этом все те регламенты, которые предусмотрены в сервере БД никто не отменял.
Если много INSERT/UPDATE/DELETE в базе, то статистика очень быстро устаревает(характерно для OLTP баз) ее нужно обновлять периодически.
А вы знаете, что обновление статистики выполняется сервером автоматически для некоторых индексов, а для некоторых автообновление отключено. Проверьте включено-ли автообновление статистики примерно так:
DBCC SHOW_STATISTICS (N'dbo._acc1', _Acc1_ByCode_SR)
или так:
EXEC sp_autostats 'dbo._acc1'
Смысл параметров я думаю понятен.
Еще есть синхронное и асинхронное обновление статистик.
Статистика очень тонкий инструмент в сервере SQL. Теперь я думаю понятно почему большинство скриптов которые вы нашли не обновляет статистики.

(10) Диски разные, но это могут быть RAID[0,1,5,6,10]? Нужное подчеркнуть.
Монитор производительности - очередь записи на диск, например???
объем оперативы = Объем базы/4, ИМХО конечно.
12. Александр Медведев (anig99) 14.07.10 12:54
(11) зачем всю? В том то и дело, что провал в производительности происходит только для 1 конкретного регистра...
13. Александр Медведев (anig99) 14.07.10 12:57
(11) а в остальном буду копать дальше
14. Александр Медведев (anig99) 16.07.10 11:52
(11) уточняю... automatically recompute statistics в параметрах индекса - это и есть автообновление статистики для конкретного индекса? Если да, то они были включены. Сейчас для пробы я их отключил и оставил только скрипт по перестройке этих конкретных индексов с цикличностью в час.
15. Vitaliy (idef) 20.07.10 17:53
(14) Да. Но зачем отключать? Для экспериментов? И каковы результаты?
16. Александр Медведев (anig99) 21.07.10 07:56
(15) так как знания мои малы - прибегаю к шаманству (: Уже по результатам отключения можно сделать предположение. Автообновление статистики вещь хорошая, но при определенных условиях статистика может формироваться неверная, что приводит к резкому увеличению времени записи в регистр. А чем чаще обновлять статистику - тем больше вероятность, что это произойдет.
Сейчас периодичность обновления статистики - 3 часа. Полет нормальный.
17. Vitaliy (idef) 22.07.10 18:48
(16) А можно текст задания, который обновляет статистику на всеобщее рассмотрение?
18. Александр Медведев (anig99) 22.07.10 22:59
(17) если я правильно все понял, то после перестройки индексов статистики обновляется в любом случае. Поэтому использую скрипт, который идет последний в статье. На данный момент перешел на исполнение этого скрипта раз в сутки + 1-2 раза в день вручную, если начинаются затыки.
19. Vitaliy (idef) 23.07.10 12:55
(18) Однозначно после ALTER INDEX статистика будет обновлена, но только если включено автообновление, а оно у вас похоже включено.
А какие размеры имеют индексы и таблицы и сколько времени это занимает?
Какая утилизация ресурсов?
Я это к тому, что может допустимый вариант решения: ночью реиндексация, а в течении дня обновление статистики несколько раз.
20. MICK77 Владислав (MICK77) 13.10.11 12:12
уже проскакивало :
"общее
- Обновление статистики - каждые 2 часа
- DBCC FREEPROCCACHE - каждые 2 часа"

только вот общее - это для всех баз включая системные или только пользовательские?
21. Александр Медведев (anig99) 13.10.11 21:37
(20) всю статистику обновлять - это долго. очистку кэша - можно. Перестраивать несколько индексов и обновлять по ним статистику с очисткой кэша - вот всё, что нужно. У меня это происходит сейчас 3 раза в день. Занимает меньше 2 минут - результат поразительный.
На другой базе иногда вручную делаю очистку кэша. Для чего? У меня есть проверка, которая делате много-много мелких запросов. Иногда один этот запрос начинает выполнятся больше секунды - запрос должен выполнится больше сотни тысяч раз. Такая производительность не устраивает. Очистка кэша (иногда не с первого раза) снижает время выполнения до приемлемых 0,08-0,04 сек в зависимости от близости к текущей дате по времени.