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

Откуда:
Сообщений: 662
Добрый день.
В целях поиска добровольных бетта тестеров и в качестве удовлетворения малочисленных просьб,
решил поделиться своим набором SP_шек (4 штуки), позволяющих реализовать любой сценарий обслуживания индексов одной или всех баз SQL-инстанса.
Асинхронность работы реализована через Service Broker (SB).
В прилагаемом zip_е два скрипта - 1_й создает все SP_шки, а второй для SB.
Т.к. работа SP_шек связана со всеми базами, то я предлагаю использовать msdb, если Вы считаете по другому, то поправте скрипты заменив msdb на выбранную Вами базу.
Параметры SP_шек задокументированны в скриптах.

Примеры использования:
1) Если мы просто хотим узнать, что творится с индексами у таблиц.
EXECUTE [msdb].[dbo].[GatheringFragmentation] 
  @DB               = 'TestDB'-- DB_NAME() or DB_ID()
  ,@ObjectMaskList  = DEFAULT -- Список масок обрабатываемых таблиц/въюшек. NULL - "все включено"
  ,@ExcludeMaskList = DEFAULT -- Список масок исключаемых таблиц/въюшек. NULL - пустой список
  ,@IndexID         = DEFAULT -- Список ID индексов. NULL - все индексы
  ,@SectionID       = DEFAULT -- Список номеров секций. NULL - все секции
  ,@ResTBL          = DEFAULT -- Имя таблицы в которую будут записаны задания для SB и результаты выполнения.
  ,@Mode            = 2       -- Режим работы ф-ии sys.dm_db_index_physical_stats()
                                            -- 0 - LIMITED; 1 - SAMPLED; 2 - DETAILED
  ,@AsyncMode       = 0       -- Режим работы этой SP. 
                              --  0- Синхронный режим
                              --  1- Асинхронный режим с ожиданием результата от SB
                              --  2- Асинхронный режим без ожидания результата, только выдача заданий в SB.
  ,@MaxTimeOut      = DEFAULT -- Максимальная длительность ожидания ответов от SB в секундах. -1 = INFINITE
-- Это, почти, аналогично
select * 
from sys.dm_db_index_physical_stats(DB_ID('TestDB'), DEFAULT, DEFAULT, DEFAULT, 'DETAILED' )
2) Сценарий, аналогичный примеру Г из BOL_а
set nocount on
CREATE TABLE ##Res(
  [database_id] [smallint],       --- обратите внимание - НЕ INT
  [object_id] [int],
  [index_id] [int],
  [partition_number] [int],
  [avg_fragmentation_in_percent] [float] NULL,
  [operation] int NULL,
  [Error] int NULL,
  [ErrorDescr] nvarchar(512) NULL,
  [hDLG] UNIQUEIDENTIFIER NULL );

EXECUTE [msdb].[dbo].[GatheringFragmentation]
   @DB              = 'TestDB'
  ,@ObjectMaskList  = DEFAULT
  ,@ExcludeMaskList = DEFAULT
  ,@IndexID         = DEFAULT
  ,@SectionID       = DEFAULT
  ,@ResTBL          = '##Res'
  ,@Mode            = 0
  ,@AsyncMode       = 1
  ,@MaxTimeOut      = DEFAULT

--------------------  Принятие решений о перестройке или реорганизации индексов
UPDATE ##Res set [operation]= case 
  when [avg_fragmentation_in_percent] < 10.0 then 0             -- Ничего не делаем
  when [avg_fragmentation_in_percent] < 30.0 then 1 else 2 end  -- 1 - Reorginize; 2 - Rebuild
WHERE ([operation] is NULL)
-------------------
EXECUTE [msdb].[dbo].[RunDefragmentation] 
   @ResTBL        = '##Res'
  ,@RebuildSwith  = 1
  ,@AsyncMode     = 1
  ,@MaxTimeOut    = DEFAULT
/* @RebuildSwith - битовая маска используется только для операций Rebuild
  (0) - SORT_IN_TEMPDB          0=OFF; 1=ON -- Работает на любых таблицах
  (1) - STATISTICS_NORECOMPUTE  0=OFF; 1=ON -- Остальные биты на секционированных таблицах игнорируются.
  (2) - ONLINE                  0=OFF; 1=ON
  (3) - ALLOW_ROW_LOCKS         0=OFF; 1=ON
  (4) - ALLOW_PAGE_LOCKS        0=OFF; 1=ON
*/
-- Просмотр результатов, где [operation] 3 - была проведена реорганизация; 4 - дефрагментация
select *
from ##Res
order by object_id, index_id, partition_number

drop table ##Res
Жду Ваших пожеланий, замечаний и предложений ...

P.S. с HEAP_ом я не работаю и работать не собираюсь ...

К сообщению приложен файл (DefragSetup.zip - 14Kb) cкачать
15 окт 09, 16:21    [7792294]     Ответить | Цитировать Сообщить модератору
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Хм. А зачем его "запускать" вообще?
Нужно (по-хорошему), чтобы дефрагментация начиналась просто "сама собой" - в часы простоя.

Можно реализовать в виде цикла (или просто job), который будет определять по какому-либо критерию (текущая загруженность сервера, наличие "критических" бизнес процессов в это время, %фрагментации или сложной комбинации этих критериев) просто будет либо запускать очередной rebuild, либо тупо ждать "еще минуту".
15 окт 09, 16:42    [7792491]     Ответить | Цитировать Сообщить модератору
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
mike909
Member

Откуда:
Сообщений: 662
DeColo®es
Хм. А зачем его "запускать" вообще?
Нужно (по-хорошему), чтобы дефрагментация начиналась просто "сама собой" - в часы простоя.

Можно реализовать в виде цикла (или просто job), который будет определять по какому-либо критерию (текущая загруженность сервера, наличие "критических" бизнес процессов в это время, %фрагментации или сложной комбинации этих критериев) просто будет либо запускать очередной rebuild, либо тупо ждать "еще минуту".

Это годится, так-же как и пример из BOL_а, для очень маленьких баз, в которых проанализировать и перестроить индекс можно "в моменты простоя" за считанные минуты.
Увы, если базы, как у меня, весят пару - тройку терабайт, то сей процесс занимает часы.
Так, например, на одной из VLDB_шек скриптик, подобный BOL_овскому, проработал чуть более пяти часов. Для меня это неприемлемо, в связи с чем я и написал эти SP_шки. Максимальное время работы равно времени обработки одной секции, что составляет примерно 30 минут.

P.S. Под сценариями я как раз и подразумевал шаги JOB_а.
15 окт 09, 17:01    [7792676]     Ответить | Цитировать Сообщить модератору
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
mike909
Это годится, так-же как и пример из BOL_а, для очень маленьких баз, в которых проанализировать и перестроить индекс можно "в моменты простоя" за считанные минуты.
Увы, если базы, как у меня, весят пару - тройку терабайт, то сей процесс занимает часы.
Для маленьких баз просто перестраиваются все индексы ночью за полчаса.

А для больших, да еще и режим 24х7 как раз нужна система, которая будет сама ждать "окна" и при появлении оного - делать дефрагментацию одного очередного индекса. Потом - снова ждать "окна".

А то, что это "асинхронно" - особой пользы нет, я могу и просто из отдельного соединения/приложения/job запустить дефрагментацию - пускай ждет, нет проблем.
15 окт 09, 17:24    [7792886]     Ответить | Цитировать Сообщить модератору
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
mike909
Member

Откуда:
Сообщений: 662
DeColo®es
А для больших, да еще и режим 24х7 как раз нужна система, которая будет сама ждать "окна" и при появлении оного - делать дефрагментацию одного очередного индекса. Потом - снова ждать "окна".

А то, что это "асинхронно" - особой пользы нет, я могу и просто из отдельного соединения/приложения/job запустить дефрагментацию - пускай ждет, нет проблем.


У меня система 24х7 в которую в том-же режиме льются данные, т.е. окон нет небыло и не предвидится. Зато фрагментация растет постоянно.
Вот я и предлагаю систему, которая позволит за минимальный срок (30 мин вместо 5 часов в моем случае) бороться с фрагментацией.

Ээээх, везет же людям, могут не задумываясь использовать примеры из BOL_а в качестве JOB_ов ...
15 окт 09, 17:33    [7792959]     Ответить | Цитировать Сообщить модератору
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
mike909
Вот я и предлагаю систему, которая позволит за минимальный срок (30 мин вместо 5 часов в моем случае) бороться с фрагментацией.
Вопрос не в том, что она сроки сокращает.
Вопрос в том, зачем делать "асинхронность".
15 окт 09, 18:13    [7793303]     Ответить | Цитировать Сообщить модератору
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
mike909
Member

Откуда:
Сообщений: 662
DeColo®es
mike909
Вот я и предлагаю систему, которая позволит за минимальный срок (30 мин вместо 5 часов в моем случае) бороться с фрагментацией.
Вопрос не в том, что она сроки сокращает.
Вопрос в том, зачем делать "асинхронность".

1_е Вопрос именно в том, чтобы сократить сроки
2_е Именно для этого асинхронность и нужна.

P.S. Кстати, SP_шки могут работать и в синхронном режиме (параметр @AsyncMode=0).
В этом режиме даже SB можно не устанавливать.
15 окт 09, 18:37    [7793396]     Ответить | Цитировать Сообщить модератору
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
mike909
1_е Вопрос именно в том, чтобы сократить сроки
2_е Именно для этого асинхронность и нужна.
И в чем проблема просто запустить дефрагментацию в отдельном соединении/job-ом?
15 окт 09, 19:35    [7793601]     Ответить | Цитировать Сообщить модератору
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
mike909
Member

Откуда:
Сообщений: 662
DeColo®es
mike909
1_е Вопрос именно в том, чтобы сократить сроки
2_е Именно для этого асинхронность и нужна.
И в чем проблема просто запустить дефрагментацию в отдельном соединении/job-ом?

Проблемы !
Какие проблемы ?
Ну подумаешь вся работа встанет часов на пять. Этож ночью,"в отдельном соединении/job-ом"- никто и не увидит ж-(. Только на утро вопросы начнут задавать: А почему это мое ночное задание до сих пор не отработало? А раньше к утру уже все отчеты были готовы. Какая такая дефрагментация ? Это так в Вашем понимании вы ускоряете выполнение запросов? А не хотите ли поискать работу в другом месте ?
mike909
У меня система 24х7 в которую в том-же режиме льются данные, т.е. окон нет, не было и не предвидится. Зато фрагментация растет постоянно.
Вот я и предлагаю систему, которая позволит за минимальный срок (30 мин вместо 5 часов в моем случае) бороться с фрагментацией.
Попробуйте всетаки прочитать выделенное.

P.S. только не предлагайте мне запускать 5_ти часовую дефрагментацию в рабочее время.
Ну очень большая вероятность, что даже вопросов не будет ...
И еще один момент, большие данные хранятся, как и у меня, в секционированных таблицах.
А для них ключик "ONLINE=ON" при rebuild_е не работает, даже в sql2k8. Следовательно пока ALTER INDEX не отработает, талица будет заблокирована. Даже ReadOnly секции ж-(
15 окт 09, 20:27    [7793737]     Ответить | Цитировать Сообщить модератору
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Так, еще раз. Выделенное читал, не беспокойтесь. :)

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

От того, что я инициирую запуск дефрагментации через ServiceBroker или job, она быстрее/медленнее выполняться не будет. И блокировать данные не перестанет волшебным образом.
При чем тут асинхронность-то?

Ваше сокращение времени по сравнению с примером из BOL говорит о том, что Вы либо дефрагментируете меньше таблиц, либо "параметры" дефрагментации другие.
15 окт 09, 20:43    [7793772]     Ответить | Цитировать Сообщить модератору
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
mike909
Member

Откуда:
Сообщений: 662
DeColo®es
Так, еще раз. Выделенное читал, не беспокойтесь. :)

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

От того, что я инициирую запуск дефрагментации через ServiceBroker или job, она быстрее/медленнее выполняться не будет. И блокировать данные не перестанет волшебным образом.
При чем тут асинхронность-то?

Ваше сокращение времени по сравнению с примером из BOL говорит о том, что Вы либо дефрагментируете меньше таблиц, либо "параметры" дефрагментации другие.

No Coments
Кроме - "Включите четырех мерное воображение" (С) "Сумасшедший профессор" из к/ф "Назад в будущее". Согласен, с этим у Вас туго ...
Разжуем на примере:
Пусть у Вас будет две секционированные таблицы с пятью индексами в каждой. В одной 1.5 миллиарда записей в другой чуть меньше 7 милиарда записей (это одна моих VLDB_шек).
Первое утверждение, проверенное неоднократно практикой - Во время команды ALTER INDEX ... Rebuild ... таблица блокируется для любых операций. Т.к. обычно приходится работать только с конкретными секциями (у меня одна секция весит 150 - 200 млн записей и примерно 100 Гб) ключик "ONLINE=ON" НЕ РАБОТАЕТ !!! Соответственно вся работа с ЭТОЙ ТАБЛИЦЕЙ встала.
Второе утверждение - т.к. заблокированна только одна таблица, то ничто не мешает, кроме религии, в это же время поработать с другой таблицей на предмет rebuilda_а индексов.
Итого: Если занятся rebuild_ом индексов параллельно по двум таблицам, то время простоя бизнесс задач будет равно времени rebuild_а самой большой секции таблицы, А НЕ СУММЕ !!!
Как я уже говорил ранне, разница существенна - 30 минт и 5 часов.

А алгоритм дефрагментации действительно влияет на скорость, но не влияет на формулу конечного времени:
При параллельном выполнении операций над двумя и более таблицами = время операции над самой большой таблицей.
При последовательном выполнении операций над двумя и более таблицами = СУММЕ !!!
Неужели это так сложно понять !!!

Параллельность выполнения и обеспечивает reader_ы SB. В скрипте я выставил 10, но, в принципе, достаточно и трех (при двух больших таблицах).
15 окт 09, 22:16    [7793954]     Ответить | Цитировать Сообщить модератору
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
Crimean
Member

Откуда:
Сообщений: 13147
интересно. дисковая подсистема я так понимаю у вас как минимум "без узких мест"?
с вашего позволения чуть-чуть "потерзаю" скриптики в личных целях ;)
15 окт 09, 23:01    [7794088]     Ответить | Цитировать Сообщить модератору
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
mike909
Member

Откуда:
Сообщений: 662
Crimean
интересно. дисковая подсистема я так понимаю у вас как минимум "без узких мест"?
с вашего позволения чуть-чуть "потерзаю" скриптики в личных целях ;)

Буду только рад
mike909
Жду Ваших пожеланий, замечаний и предложений ...

А вот дисковая подсистема - сугубо у каждого своя. Следует лишь пнимать, ЧТО происходит.
Например, если выставите ключик, как в примере, @RebuildSwith = 1, т.е. SORT_IN_TEMPDB, то ентого tempdb должно хватить на ваши эксперименты...
15 окт 09, 23:15    [7794119]     Ответить | Цитировать Сообщить модератору
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
автор
При параллельном выполнении операций над двумя и более таблицами = время операции над самой большой таблицей.
При последовательном выполнении операций над двумя и более таблицами = СУММЕ !!!
Неужели это так сложно понять !!!
Конечно, сложно, если об этом не рассказывать. :)
Понятие асинхронность относится как правило к тому, что вызывающая процесс сторона просто не ждет его завершения. А когда она запускает при этом несколько процессов - это уже скорее "многопоточность".

Если есть возможность/желание запустить несколько процессов дефрагментации одновременно - это хорошо.
Но я присоединюсь к Cremean - более одного-двух процессов скорее всего упрутся в скорость дискового интерфейса (если только это не SSD RAID непосредственно на 16-ти канальной PCI-E)

А раз уж Вы озадачились сим вопросом - все-таки подумайте над вариантом, когда дефрагментация запускается "сама" (хоть в одном, хоть 2-х, хоть в 10-ти процессах параллельно).
16 окт 09, 14:51    [7797323]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
dalex1973
Member

Откуда: Польша
Сообщений: 287
Скрипт замечательный.
Идея тоже интересная - в "асинхроне" высылаем сообщение дла ServiceBroker, в "синxроне" - классический курсор.
Вопрос: теоретически дав MAX_QUEUE_READERS = X получаем "многозадачность в X потоков". Зная количество нашиx физически допустимых "потоков"(дисков) можно бы было обеспечить оптимальное быстродействие.(Поправьте, если я ошибаюсь)
Но есть одно "но": сейчас для определения "жертвы" дефрагментации используется

GROUP BY [database_id], [object_id]


Это конечно работает для многодисковой системы(таблицы на разныч дискаx). Но например если только два диска? Как обеспечить одновременное выполнение двуx "потоков" из разныx файловыx групп?
  • в Receive добавить WHERE
  • повторное использование диалогов (разные диалоги для разныx файл-групп)
    либо спасут только две очереди?
  • 28 ноя 12, 14:55    [13543294]     Ответить | Цитировать Сообщить модератору
     Re: Асинхронный дефрагментатор для SQL2k5(8)  [new]
    МуМу
    Member

    Откуда:
    Сообщений: 1134
    Изначально в топике была указанно асинхронность а не параллельность или многопоточность. В этом видимо суть непонимания. А вообщем по практике реально ускорение получается раз в пять.(дальше дисковая система начинает тормозить) К тому же процессорное время переиндексация тоже отъедает. Проблема основная что в рабочее время можно запускать не более двух потоков потому как запуск большего количества приводит к большой нагрузке и деградации производительности системы.
    28 ноя 12, 16:13    [13544103]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить