Столкнулся с непонятной ситуацией. В планах обслуживания у меня настроена ежедневная реиндексация со следующими параметрами:
- Перестроение, если процент фрагментации > 30
- Реорганизация, если процент фрагментации > 5.
Обратил внимание, на огромный размер бэкапа журнала транзакций после реиндексации. Запустил скрипт, показывающий процент фрагментации. И он показал, что по ряду индексов фрагментация больше 20 (через несколько часов после ночной реиндексации), в том числе по таким большим таблицам, как регистры СебестоимостьТоваров и ТоварыОрганизации. В них явно не пишутся данные с такой интенсивностью, чтобы вызывать фрагментацию. В чем может быть дело?
Диски SSD. В статье на Хабре написано, что для SSD дисков частая реиндексация не нужна, и пороговые значения процента фрагментации можно делать выше. Кто какие настройки использует?
- Перестроение, если процент фрагментации > 30
- Реорганизация, если процент фрагментации > 5.
Обратил внимание, на огромный размер бэкапа журнала транзакций после реиндексации. Запустил скрипт, показывающий процент фрагментации. И он показал, что по ряду индексов фрагментация больше 20 (через несколько часов после ночной реиндексации), в том числе по таким большим таблицам, как регистры СебестоимостьТоваров и ТоварыОрганизации. В них явно не пишутся данные с такой интенсивностью, чтобы вызывать фрагментацию. В чем может быть дело?
Диски SSD. В статье на Хабре написано, что для SSD дисков частая реиндексация не нужна, и пороговые значения процента фрагментации можно делать выше. Кто какие настройки использует?
Скрипт реиндексации |
|---|
| USE ERP_WORK;
DECLARE @SQL NVARCHAR(MAX) DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR SEL ECT ' ALT ER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' + CASE WHEN s.avg_fragmentation_in_percent > 60 THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON' -- Enterprise, Developer + CASE WHEN SERVERPROPERTY('EditionID') IN (1804890536, -2117995310) THEN ', ON LINE = ON' ELSE '' END + ') --' + str(s.avg_fragmentation_in_percent) ELSE 'REORGANIZE --' + str(s.avg_fragmentation_in_percent) END + ';' FR OM ( SEL ECT s.[object_id] , s.index_id , avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent) FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') s --'' LIMITED DETAILED WH ERE s.page_count > 128 -- > 1 MB AND s.index_id > 0 -- <> HEAP AND s.avg_fragmentation_in_percent > 30 GROUP BY s.[object_id], s.index_id ) s JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id] OPEN cur FETCH NEXT FR OM cur INTO @SQL WHILE @@FETCH_STATUS = 0 BEGIN -- EXEC sys.sp_executesql @SQL PRINT @SQL FETCH NEXT FR OM cur INTO @SQL END |
По теме из базы знаний
- Обслуживание индексов и статистик MS SQL Server
- Регламентные операции с индексами в MS SQL Server (Скрипты для SQL-Server - Часть 2)
- Дефрагментация и реиндексация после перехода на платформу 8.3.22
- Дефрагментация индексов MS SQL для платформы 8.3.22
- Обслуживание индексов MS SQL Server: как, когда и, главное, зачем?
Ответы
Подписаться на ответы
Инфостарт бот
Сортировка:
Древо развёрнутое
Свернуть все
(1)
В принципе да, на ssd фрагментация не так критична. Слышал рекомендации ставить порогом 30% и 80%, но с ходу не найду сейчас где именно.
Такая частая реиндексация, как у вас, выглядит перебором. Как правило раза в неделю достаточно, плюс ежедневное обновление статистики
Диски SSD. В статье на Хабре написано, что для SSD дисков частая реиндексация не нужна, и пороговые значения процента фрагментации можно делать выше. Кто какие настройки использует?
В принципе да, на ssd фрагментация не так критична. Слышал рекомендации ставить порогом 30% и 80%, но с ходу не найду сейчас где именно.
Такая частая реиндексация, как у вас, выглядит перебором. Как правило раза в неделю достаточно, плюс ежедневное обновление статистики
1. Причем тут SSD диски и дефрагментация? Хотите из быстрых получать данные помедленней?
2. Тщательно не смотрел, может ошибаюсь или вы, вообще привели некий рекомендуемый скрипт, вместо своего, но сдается, что where ... AND s.avg_fragmentation_in_percent > 30 никак не согласуется с утверждением "
- Реорганизация, если процент фрагментации > 5.
а
CASE WHEN s.avg_fragmentation_in_percent > 60
THEN 'REBUILD
с утверждением - Перестроение, если процент фрагментации > 30
Т.е. по сути (если не ошибся в чтении скрипта), получаем, что индексы с фрагментацией менее 30, вообще у вас не обрабатываются, отсюда и возможно разгадка "что по ряду индексов фрагментация больше 20 "
3. В общем, кто на ком стоял я точно не понял, но если сразу после ночи процент фрагментации индексов СебестоимостьТоваров и ТоварыОрганизации стремится к нулю, то напрашивается совет - помониторить вашим скриптом и понять в какой момент все начинается.
2. Тщательно не смотрел, может ошибаюсь или вы, вообще привели некий рекомендуемый скрипт, вместо своего, но сдается, что where ... AND s.avg_fragmentation_in_percent > 30 никак не согласуется с утверждением "
- Реорганизация, если процент фрагментации > 5.
а
CASE WHEN s.avg_fragmentation_in_percent > 60
THEN 'REBUILD
с утверждением - Перестроение, если процент фрагментации > 30
Т.е. по сути (если не ошибся в чтении скрипта), получаем, что индексы с фрагментацией менее 30, вообще у вас не обрабатываются, отсюда и возможно разгадка "что по ряду индексов фрагментация больше 20 "
3. В общем, кто на ком стоял я точно не понял, но если сразу после ночи процент фрагментации индексов СебестоимостьТоваров и ТоварыОрганизации стремится к нулю, то напрашивается совет - помониторить вашим скриптом и понять в какой момент все начинается.
(4)
Вы почитайте комментарии по своей ссылке. Там многое объясняется. В том числе и то, что разница между случайным и последовательны чтением есть, и немалая, и на SSD.
Да и про упреждающее чтение, которое, вероятно, не будет работать на сильно фрагментированных индексах автор тоже "забыл".
Заодно и "забыл", что оптимизатор имеет дело с логической структурой индексов, отсюда и план выполнения запросов может отличаться драматически. И сбор статистики, особенно частичной, может не выручить.
В общем-то, вопрос интересный. Если есть желание, можете поставить себе SSD диски, RAID контроллер, залить туда реальную базу и поиграться и без рэйда и с разными видами оного, заодно и свою статью напишите "за" или "против".
Безотносительно к вышесказанному, сильная фрагментация за короткое время - это плохо не только с точки зрения производительности. Если этого явно не ожидается, то надо разбираться, а что вообще такого пишется в эти таблицы и почему. Если же так и должно быть, то попробуйте поиграться с fill factor.
Про пункт 1 не понял, можете пояснить подробнее?
Вы почитайте комментарии по своей ссылке. Там многое объясняется. В том числе и то, что разница между случайным и последовательны чтением есть, и немалая, и на SSD.
Да и про упреждающее чтение, которое, вероятно, не будет работать на сильно фрагментированных индексах автор тоже "забыл".
Заодно и "забыл", что оптимизатор имеет дело с логической структурой индексов, отсюда и план выполнения запросов может отличаться драматически. И сбор статистики, особенно частичной, может не выручить.
В общем-то, вопрос интересный. Если есть желание, можете поставить себе SSD диски, RAID контроллер, залить туда реальную базу и поиграться и без рэйда и с разными видами оного, заодно и свою статью напишите "за" или "против".
Безотносительно к вышесказанному, сильная фрагментация за короткое время - это плохо не только с точки зрения производительности. Если этого явно не ожидается, то надо разбираться, а что вообще такого пишется в эти таблицы и почему. Если же так и должно быть, то попробуйте поиграться с fill factor.
Для получения уведомлений об ответах подключите телеграм бот:
Инфостарт бот