Как часто индексы должны быть восстановлены в нашей базе данных SQL Server?
В настоящее время наша база данных имеет размер 10 ГБ и растет приблизительно на 3 ГБ в месяц. Часто я слышу, что нужно время от времени восстанавливать индексы, для улучшения времени выполнения запросов. Таким образом, как часто я должен восстановить индексы в данном сценарии?
4 ответа
Существует общее мнение, что вам следует реорганизовать («дефрагментировать») свои индексы, как только фрагментация индекса достигнет более 5 (иногда 10%), и вы следует полностью перестроить их, когда оно превышает 30% (по крайней мере, это числа, которые я слышал во многих местах).
Мишель Аффорд (также известная как «SQL Fool») имеет сценарий автоматической дефрагментации индекса , который использует эти точные ограничения для принятия решения о том, когда реорганизовывать или перестраивать индекс.
Также см. советы Брэда МакГихи по перестроению индексов с некоторыми полезными мыслями и советами о том, как справиться с перестроением индексов.
Я использую здесь этот сценарий (не могу вспомнить, когда я получил это от кого бы то ни было: большое спасибо! Действительно полезный материал), чтобы отобразить фрагментацию индекса по всем вашим индексам в данной базе данных:
SELECT
t.NAME 'Table name',
i.NAME 'Index name',
ips.index_type_desc,
ips.alloc_unit_type_desc,
ips.index_depth,
ips.index_level,
ips.avg_fragmentation_in_percent,
ips.fragment_count,
ips.avg_fragment_size_in_pages,
ips.page_count,
ips.avg_page_space_used_in_percent,
ips.record_count,
ips.ghost_record_count,
ips.Version_ghost_record_count,
ips.min_record_size_in_bytes,
ips.max_record_size_in_bytes,
ips.avg_record_size_in_bytes,
ips.forwarded_record_count
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN
sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN
sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
AVG_FRAGMENTATION_IN_PERCENT > 0.0
ORDER BY
AVG_FRAGMENTATION_IN_PERCENT, fragment_count
«Когда нужно» и «Когда можно»!
Например ...
Сначала проверьте фрагментацию и решите, ничего не делать, реорганизовать или перестроить. Скрипт SQL Fool делает это , например, имеет
Параметры @minFragmentationи@rebuildThresholdСтатистические данные, скажем, выполняются ежедневно, а по выходным - индексы. Какой у вас интервал техобслуживания?
Вы должны перестраивать индексы достаточно часто, чтобы деградация индексов не оказывала негативного влияния на производство. Я понимаю, что это кажется расплывчатым, но все базы данных разные и используются по-разному. Вам нужно регулярно перестраивать/дефрагментировать только те индексы, в которых происходят операции записи (вставки/обновления) - ваши статические или в основном читаемые таблицы не будут нуждаться в переиндексации.
Вам нужно использовать dbcc showcontig([Table]), чтобы проверить уровень фрагментации ваших индексов, определить, как часто они становятся фрагментированными и каков уровень фрагментации на самом деле.
Используйте dbcc dbreindex([Table]) для полного восстановления индексов, когда они становятся слишком фрагментированными (более 20%-30% или около того), но если вы не можете найти достаточно большое окно простоя, а уровень фрагментации относительно низкий (1%-25%), вам следует использовать dbcc indexdefrag([Database], [Table], [Index]) для дефрагментации индекса в режиме "онлайн". Также помните, что вы можете остановить операцию дефрагментации индекса и запустить ее снова в более позднее время без потери работы.
Для поддержания базы данных и ее индексов "в тонусе" требуется немного мониторинга, чтобы понять, когда и что нужно переиндексировать.
Учитывая размер вашей базы данных, вы можете легко перестраивать индексы один раз в месяц. Но по мере увеличения размера, скажем, до 500 ГБ, вы можете делать это дважды в месяц.