Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Дефрагментация индексов  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
Имею большую базу данных на MS SQL 2005. Модель восстановления full.
Каждую неделю на выходных делается дефрагментация индексов на таблицах с помощью
DBCC INDEXDEFRAG
При выполнении данной операции на больших таблицах лог вырастает и занимает все свободной место на диске (около 100 Гб). Джоб падает. Покупать под лог новый диск не хочется, тем более что в рабочее время больше 1 гб не вырастает. Что можно тут сделать?
15 май 08, 19:16    [5670502]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3217
Например, на время мэйнтенанса переводить базу в простой режим восстановления.

Если сиквел 2005 и позволяет редакция, то alter index ... with sort_in_tempdb = on. Правда, не знаю, поможет это или нет.
15 май 08, 20:59    [5670665]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
Fire83
Имею большую базу данных на MS SQL 2005. Модель восстановления full.
Каждую неделю на выходных делается дефрагментация индексов на таблицах с помощью
DBCC INDEXDEFRAG
При выполнении данной операции на больших таблицах лог вырастает и занимает все свободной место на диске (около 100 Гб). Джоб падает. Покупать под лог новый диск не хочется, тем более что в рабочее время больше 1 гб не вырастает. Что можно тут сделать?


А с какой целью так часто делается переиндексация БД?
15 май 08, 22:14    [5670763]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
Ennor Tiegael
Например, на время мэйнтенанса переводить базу в простой режим восстановления.


Вы прежде чем советовать оцените возможный ущерб от этих действий...
15 май 08, 22:15    [5670764]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3217
SanyL
Ennor Tiegael
Например, на время мэйнтенанса переводить базу в простой режим восстановления.

Вы прежде чем советовать оцените возможный ущерб от этих действий...
Что-то мне подсказывает, что оценить возможный ущерб топикстартер может и сам. Видимо, это из-за фразы "Модель восстановления full", которая имеется в исходном посте.
Кроме того, мне представляется очень маловероятным, что если делать реиндексацию реже, то прирост лога в процессе оной будет меньше. Больше - запросто, а вот меньше - вряд ли.

Fire83
Приведите лучше результаты sp_spaceused, оба рекордсета. Чую, где-то тут подстава.
15 май 08, 23:24    [5670879]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
ыыыыы
Guest
Fire83
Что можно тут сделать?


Дефрагментировать индексы поотдельности. Потаблично или поиндексно. Но я бы рекомендовал не дефрагментировать а перестраивать их. Причем в темпдб как было подсказано ранее.
16 май 08, 06:17    [5671158]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
ыыыыыы
Guest
Ennor Tiegael
SanyL
Ennor Tiegael
Например, на время мэйнтенанса переводить базу в простой режим восстановления.

Вы прежде чем советовать оцените возможный ущерб от этих действий...
Что-то мне подсказывает, что оценить возможный ущерб топикстартер может и сам. Видимо, это из-за фразы "Модель восстановления full", которая имеется в исходном посте.
Кроме того, мне представляется очень маловероятным, что если делать реиндексацию реже, то прирост лога в процессе оной будет меньше. Больше - запросто, а вот меньше - вряд ли.

Fire83
Приведите лучше результаты sp_spaceused, оба рекордсета. Чую, где-то тут подстава.


Что-то мне подсказывает что перевод в простую модель не решит проблемы роста лога. Транзакция, я там полагаю, бежит одна единственная, следовательно....
16 май 08, 06:19    [5671159]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Ну, раз у вас 2005 сервер, то рекомендую всё же начинать пользовать его новые возможности.
Если у вас база не является хранилищем для OLAP, то и реорганизовывать ВСЕ индексы не обязательно, достаточно это проделывать только с теми у которых средняя степень фрагментации, например, превышает процентов 10...
Так, в частности, в документации по SQL Server 2005 есть статья о замечательном системном представлении sys.dm_db_index_physical_stats, в которой приведен пример "Г. Использование функции sys.dm_db_index_physical_stats в сценарии для перестройки или реорганизации индексов".
Кстати, в этом сценарии для перестроения индексов используется опять же новая команда сервера ALTER INDEX вместо DBCC INDEXDEFRAG.

http://msdn.microsoft.com/ru-ru/library/ms188917.aspx

Сообщение было отредактировано: 16 май 08, 07:21
16 май 08, 07:20    [5671186]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
petsa
Member

Откуда:
Сообщений: 1708
tpg
Ну, раз у вас 2005 сервер, то рекомендую всё же начинать пользовать его новые возможности.
Если у вас база не является хранилищем для OLAP, то и реорганизовывать ВСЕ индексы не обязательно, достаточно это проделывать только с теми у которых средняя степень фрагментации, например, превышает процентов 10...
Так, в частности, в документации по SQL Server 2005 есть статья о замечательном системном представлении sys.dm_db_index_physical_stats, в которой приведен пример "Г. Использование функции sys.dm_db_index_physical_stats в сценарии для перестройки или реорганизации индексов".
Кстати, в этом сценарии для перестроения индексов используется опять же новая команда сервера ALTER INDEX вместо DBCC INDEXDEFRAG.

http://msdn.microsoft.com/ru-ru/library/ms188917.aspx

+1024
16 май 08, 09:25    [5671395]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
SanyL
Fire83
Имею большую базу данных на MS SQL 2005. Модель восстановления full.
Каждую неделю на выходных делается дефрагментация индексов на таблицах с помощью
DBCC INDEXDEFRAG
При выполнении данной операции на больших таблицах лог вырастает и занимает все свободной место на диске (около 100 Гб). Джоб падает. Покупать под лог новый диск не хочется, тем более что в рабочее время больше 1 гб не вырастает. Что можно тут сделать?


А с какой целью так часто делается переиндексация БД?

Ну вообще запускается это каждую неделю, но делается в зависимости от дефрагментации, получаемой с помощью SHOWCONTIG. Если > 10% то делается дефрагментация. Кроме того ещё настроен лог шипинг на резервный сервер, который в момент выполнения операции отключается.
16 май 08, 11:30    [5672434]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
ыыыыы
Fire83
Что можно тут сделать?


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

Полностью перестаивать хм.. таблицы на которых это делается имеют 500-800 млн записей.
Дефрагментация работает часа 2-3. Проверю на тестовом сервере сколько займет перестройка.
16 май 08, 11:33    [5672479]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
tpg
Ну, раз у вас 2005 сервер, то рекомендую всё же начинать пользовать его новые возможности.
Если у вас база не является хранилищем для OLAP, то и реорганизовывать ВСЕ индексы не обязательно, достаточно это проделывать только с теми у которых средняя степень фрагментации, например, превышает процентов 10...
Так, в частности, в документации по SQL Server 2005 есть статья о замечательном системном представлении sys.dm_db_index_physical_stats, в которой приведен пример "Г. Использование функции sys.dm_db_index_physical_stats в сценарии для перестройки или реорганизации индексов".
Кстати, в этом сценарии для перестроения индексов используется опять же новая команда сервера ALTER INDEX вместо DBCC INDEXDEFRAG.

http://msdn.microsoft.com/ru-ru/library/ms188917.aspx

Спасибо за хорошую ссылку. Решая проблему заодно перепишу процедуру запускающую дефрагментацию на возможности 2005. тем более что в след. версиях сиквела DBCC INDEXDEFRAG поддерживаться не будет больше
16 май 08, 11:39    [5672538]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
ыыыыыы
Guest
Fire83
ыыыыы
Fire83
Что можно тут сделать?


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

Полностью перестаивать хм.. таблицы на которых это делается имеют 500-800 млн записей.
Дефрагментация работает часа 2-3. Проверю на тестовом сервере сколько займет перестройка.


Я сказал как теоретически лучше... Что это может не совпадать с практически - никто не спорит. Ну и ваши 2-3 часа это для всей базы? Или для одной таблицы? Для всех индексов сразу? Или только для одного? Да, имейте ввиду что в 2000 в отличии от 2005 перестройка кластерного индекса равна перестройке всех индексов.
16 май 08, 11:49    [5672657]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
ыыыыыы
Да, имейте ввиду что в 2000 в отличии от 2005 перестройка кластерного индекса равна перестройке всех индексов.
Эээээ... Не совсем так... Вернее, данное утверждение о 2005 верно только применительно к команде ALTER INDEX, которой в 2000 нет вовсе.
При явном пересоздании кластеризованного индекса (DROP+CREATE) в 2005 остальные индексы перестроются тоже.
16 май 08, 12:15    [5672928]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
ыыыыыыыыыыыы
Guest
tpg
ыыыыыы
Да, имейте ввиду что в 2000 в отличии от 2005 перестройка кластерного индекса равна перестройке всех индексов.
Эээээ... Не совсем так... Вернее, данное утверждение о 2005 верно только применительно к команде ALTER INDEX, которой в 2000 нет вовсе.
При явном пересоздании кластеризованного индекса (DROP+CREATE) в 2005 остальные индексы перестроются тоже.


Спасибо - не знал
16 май 08, 12:33    [5673126]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
В общем буду делать расписание с сохранением истории выполения операции в таблице, чтобы не попали в один день несколько огромных индексов имхо самое нормальное решение.
т.к. я меня в топе по размерам таблицы и индексы:
tableName nRows IndexSpaceUsed
[dbo].[Table1] 373871377 50565024
[dbo].[Table2] 804110746 64326280
[dbo].[Table3] 793116351 35665080
[dbo].[Table4] 506441376 34910552
[dbo].[Table5] 441723088 19912784

Дефрагментация сейчас настроена по расписанию, но не совмес как надо, видимо:
1-я неделя Дефрагментация индексов "маленьких таблиц" от 1000 до 400 млн строк
2-я неделя Дефрагментация индексов "больших таблиц" от 400 млн до бесконечности
3-я неделя Дефрагментация индексов "больших таблиц" от 400 млн до бесконечности
4-я неделя Дефрагментация индексов "маленьких таблиц" от 1000 до 400 млн строк
Делается конечно если индекс более чем на 10% фрагментирован в каждом случае

Видимо попадают в одну неделю 2-3 индекса огромных и всё, 100 гб не хватает.
16 май 08, 12:49    [5673304]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
RedBird
Member

Откуда: Москва
Сообщений: 153
Fire83
Имею большую базу данных на MS SQL 2005. Модель восстановления full.
Каждую неделю на выходных делается дефрагментация индексов на таблицах с помощью
DBCC INDEXDEFRAG
При выполнении данной операции на больших таблицах лог вырастает и занимает все свободной место на диске (около 100 Гб). Джоб падает. Покупать под лог новый диск не хочется, тем более что в рабочее время больше 1 гб не вырастает. Что можно тут сделать?

Попробутете слелать так:
BACKUP DATABASE ...
BACKUP LOG ...
DBCC INDEXDEFRAG
BACKUP LOG ...
Случись, что восстанавливать будете в том же порядке.
16 май 08, 13:03    [5673454]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Дефрагментация индексов  [new]
Valentine
Member

Откуда:
Сообщений: 38
Помогите.
Сделал как в http://msdn.microsoft.com/ru-ru/library/ms188917.aspx

Часть индексов дефрагментировало и всё. Подождал (ну может процесс не быстрый) немного, проверяю- ничего не меняется.
Запускаю повторно- ещё пару индексов.
И так каждый раз только при повторном запуске ребилд делает.
29 май 09, 14:05    [7244468]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить