Примечание.
Для доступа к этой странице требуется авторизация. Вы можете попробовать войти или изменить каталоги.
Для доступа к этой странице требуется авторизация. Вы можете попробовать изменить каталоги.
Автоматическое сжатие индексов помогает сократить потребление дискового пространства, диск I/O, ЦП, память и повысить производительность рабочей нагрузки без необходимости тратить время и усилия на задачи обслуживания индексов. Сжатие индекса выполняется непрерывно и с низкими затратами при изменении данных в базе данных.
Замечание
Автоматическое сжатие индексов в настоящее время находится в предварительной версии в Базе данных SQL Azure, Управляемом экземпляре SQL Azure с политикой обновления Always-up-to-date и базой данных SQL в Fabric.
Ответы на распространенные вопросы см. в разделе часто задаваемые вопросы (часто задаваемые вопросы).
Включение и отключение
Автоматическое сжатие индекса по умолчанию отключено. Вы можете включить или отключить его для базы данных, выполнив следующие Transact-SQL инструкции.
Включите автоматическое сжатие индекса:
ALTER DATABASE [database-name-placeholder] SET AUTOMATIC_INDEX_COMPACTION = ON;Отключите автоматическое сжатие индекса:
ALTER DATABASE [database-name-placeholder] SET AUTOMATIC_INDEX_COMPACTION = OFF;
Чтобы узнать, включено ли автоматическое уплотнение индексов, используйте представление каталога sys.databases. Например, чтобы узнать, какие базы данных имеют автоматическое сжатие индекса, выполните следующий запрос:
SELECT database_id,
name,
is_automatic_index_compaction_on
FROM sys.databases;
Для проверки свойства можно также использовать функцию IsAutomaticIndexCompactionOn.
Преимущества и замечания
Автоматическое сжатие индекса обеспечивает следующие преимущества:
- Вам не нужно настраивать и поддерживать задания обслуживания индексов.
- Это позволяет избежать высокого потребления ресурсов по заданиям обслуживания индекса.
- Это уменьшает рост пространства, который может произойти при изменении данных в базе данных.
- Это повышает производительность запросов.
- Запрос, считывающий компактный индекс, считывает меньше страниц и поэтому требует меньше дискового ввода-вывода, ЦП и памяти.
- Компактный индекс, скорее всего, будет выбран для улучшения плана запроса.
Это важно
Автоматическое сжатие действует только на недавно измененных страницах. В результате затраты на сжатие минимальны по сравнению с перестроением индекса или реорганизацией индекса, которые обрабатывают все страницы.
Хотя накладные расходы на процесс сжатия минимальны, они не равны нулю. При включении автоматического сжатия индекса рассмотрите следующие возможности.
- Если процесс сжатия перемещает множество строк, может появиться увеличение объема операций ввода-вывода журнала транзакций и размера резервных копий журналов транзакций.
- Вы можете заметить небольшое увеличение использования ЦП при сжатии в пределах низкого диапазона однозначных процентов. Это увеличение не является общим.
- Как и в случае с реорганизацией индекса, процесс сжатия получает краткосрочные монопольные (
X) блокировки страниц для перемещения строк с одной страницы на другую.- Влияние параллелизма минимальное. Если блокировка на странице не может быть получена немедленно, страница пропускается, чтобы избежать блокировки других запросов и процессов.
- Иногда запросы могут подвергаться краткосрочной (миллисекундной) блокировке. Эта блокировка возникает, если запрос пытается получить страничную или строковую блокировку после того, как процесс компактизации уже захватил эксклюзивную краткосрочную блокировку на странице, что встречается нечасто.
- Влияние параллелизма минимальное. Если блокировка на странице не может быть получена немедленно, страница пропускается, чтобы избежать блокировки других запросов и процессов.
Принцип работы
Автоматическое сжатие индексов является частью фонового процесса очистки хранителя постоянных версий (PVS). Этот процесс периодически удаляет устаревшие версии строк на страницах данных. Если включить автоматическое сжатие индексов для базы данных, средство очистки PVS также сжимает индексы.
По мере того как процесс очистки посещает каждую страницу с недавно вставленными, обновленными или удаленными строками, он проверяет наличие свободного пространства на текущей странице и используемого пространства на нескольких последующих страницах. Если на текущей странице достаточно свободного места, то очистка перемещает строки со следующих страниц на текущую страницу, если это действие делает по крайней мере одну из следующих страниц пустыми.
Пустые страницы деаллокированы. В результате общее количество используемых страниц в базе данных уменьшается, увеличивается плотность страниц , а объем памяти дискового пространства, операций ввода-вывода диска, ЦП и буферного пула уменьшается.
На следующей схеме показано концептуальное представление страниц данных в индексе до и после сжатия.
Процесс сжатия продолжается в фоновом режиме по мере изменения данных и очистки устаревших версий строк.
Процесс сжатия может пропустить некоторые страницы из-за параллельного действия, например:
- Активная транзакция с помощью страницы.
- Выполняется сборка или реорганизация индекса.
- Операция сжатия выполняется.
- Большой размер PVS или большое количество прерванных транзакций для очистки данных из PVS.
- Очистка PVS имеет приоритет над автоматическим сжатием. Сжатие приостанавливается, если размер PVS превышает 150 ГБ, или если количество прерванных транзакций превышает 1000.
Менее распространенные причины, по которым процесс сжатия может пропускать страницы, см. в статье "Использование расширенного события для мониторинга статистики сжатия".
Если страница пропущена, она учитывается для сжатия при следующей обработке средством PVS.
Автоматическое сжатие индексов недоступно для системных таблиц и для системных баз данных, отличных от msdbсистемных. Индексы с отключенными блокировками страниц не имеют права на автоматическое сжатие.
Дополнительные сведения о страницах данных см. в руководстве по архитектуре страниц и экстентов.
Дополнительные сведения об индексах см. в руководстве по архитектуре и проектированию индексов.
Сравнение с реорганизацией индекса и перестроением индекса
Рассмотрим следующие различия между традиционными операциями обслуживания индексов (реорганизация индекса, перестроение индекса) и автоматической сжатием индексов:
| Рекомендации | Рекомендации |
|---|---|
| Сжатие происходит непрерывно и с минимальными затратами, если данные в базе данных изменяются. | Вам не нужно настраивать, отслеживать и поддерживать задания обслуживания индексов, чтобы получить преимущества, которые могут предоставить эти задания. |
| В отличие от реорганизации индекса и перестроения, который обрабатывает все страницы, процесс сжатия учитывает только страницы, измененные после включения автоматического сжатия индекса. | Если плотность страницы для индекса уже низка, рассмотрите возможность однократной реорганизации индекса или перестроения индекса, чтобы увеличить его. Эта одноразовая операция является дополнительной оптимизацией для увеличения плотности страниц сразу. С этого момента автоматическое сжатие сохраняет индексы компактными без каких-либо действий пользователя. |
| Для каждой операции перестроения индекса требуется значительное свободное место в файлах данных, обычно равное размеру перестроенного индекса или секции. | Не нужно выделять свободное пространство в файлах данных для автоматического сжатия индекса или для реорганизации индекса. |
| В отличие от перестроения индекса или реорганизации индекса, сжатие не уменьшает фрагментацию индекса. | Увеличение плотности страницы после сжатия является более важным, чем фрагментация индекса. Для большинства рабочих нагрузок более высокая фрагментация индекса не влияет на производительность запросов или потребление ресурсов. |
| Если коэффициент заполнения индекса меньше 100 процентов, но объем данных на странице превышает коэффициент заполнения, ни сжатие индекса, ни реорганизация не перемещает строки от страницы. Перестроение индекса создает новые страницы и заполняет их в соответствии с коэффициентом заполнения. | Для большинства рабочих нагрузок предпочтительнее высокая плотность страницы. Рабочие нагрузки, требующие более низкого коэффициента заполнения для уменьшения разбиения страниц, могут воспользоваться периодическим перестроением индекса. Перестроение создает страницы с более низкой плотностью страниц, которая соответствует коэффициенту заполнения. |
| В отличие от перестроения индекса, сжатие не обновляет статистику по индексу. | Если автоматическое обновление статистики недостаточно для ваших потребностей, и вы полагаетесь на перестроение индекса для обновления статистики, рассмотрите возможность использования автоматического сжатия в сочетании с заданием обновления статистики. |
Дополнительные сведения о реорганизации индекса и перестроении см. в статье "Оптимизация обслуживания индекса", чтобы повысить производительность запросов и сократить потребление ресурсов.
Плотность страниц и фрагментация индекса
Плотность страниц и фрагментация индекса — это две метрики, которые отражают потребление пространства индексом и могут повлиять на производительность запросов. Функция динамического управления (DMF) sys.dm_db_index_physical_stats сообщает эти метрики в avg_page_space_used_in_percent столбцах и avg_fragmentation_in_percent столбцах соответственно.
Сжатие увеличивает плотность страниц, сохраняя больше строк на одной странице, что повышает производительность и снижает потребление ресурсов.
При включении автоматического сжатия индекса можно наблюдать, что фрагментация индекса выше. Если это условие возникает, рассмотрите следующее:
| Рекомендации | Рекомендации |
|---|---|
| В некоторых рабочих нагрузках, требующих интенсивной записи, страницы могут снова разделиться вскоре после сжатия. Разделение страниц увеличивает фрагментацию индекса. | Если производительность рабочей нагрузки затронута в результате, используйте однократное перестроение индекса с немного сокращенным коэффициентом заполнения , чтобы уменьшить разделение страниц после сжатия. Например, задайте коэффициент заполнения в диапазоне от 70 до 95 процентов. Однако не уменьшайте коэффициент заполнения ненужно или не устанавливайте его слишком низко. Большинство рабочих нагрузок обеспечивают оптимальную производительность и использование ресурсов с помощью коэффициента заполнения по умолчанию 100 %. |
| Освобождение пустой страницы во время сжатия может создать пробел в последовательности нумерации страниц в экстенте. Пробелы в пределах экстентов увеличивают фрагментацию индекса, потенциально уменьшая объем операций чтения вперед. | Даже для рабочих нагрузок, которые пользуются преимуществами предварительного чтения, влияние более высокой фрагментации на производительность сводится к минимуму, так как после уплотнения запросы считывают меньше страниц. |
Подсказка
Для большинства рабочих нагрузок преимущества более высокой плотности страницы перевешивают любое влияние на производительность из-за более высокой фрагментации индекса.
Мониторинг автоматического сжатия индекса
Чтобы увидеть эффективность автоматического сжатия индекса, можно отслеживать метрики ключевых индексов, такие как количество страниц, средняя плотность страниц и среднее фрагментирование индекса с течением времени. Дополнительные сведения см. в примере Определение ключевых метрик индекса.
Вы также можете отслеживать подробную статистику сжатия с помощью расширенных событий. Дополнительные сведения см. в разделе "Использование расширенного события для мониторинга статистики сжатия ".
Ограничения
Процесс автоматического сжатия индексов рассматривает только страницы на лиственном уровне B-дерева, которые содержатся в единице IN_ROW_DATA выделения. Это включает страницы в:
- Кластеризованные индексы и ограничения.
- Некластеризованные индексы и ограничения.
- Индексы дерева B во внутренних таблицах, которые хранят специальные типы индексов, такие как XML, полнотекстовые, пространственные и внутренние наборы строк columnstore.
Следующие типы страниц не могут быть разрешены для автоматического сжатия индексов:
- Страницы в таблицах куч.
- Страницы в
ROW_OVERFLOW_DATAилиLOB_DATAединицах распределения. - Страницы в сжатых группах строков колонносторных индексов.
- Страницы в оптимизированных для памяти таблицах.
Часто задаваемые вопросы (FAQ)
В этом разделе приводятся ответы на распространенные вопросы об автоматическом уплотнении индексов.
Требуется ли перезапуск или монопольный доступ к базе данных для включения или отключения автоматического сжатия?
Нет. Сжатие начинается или останавливается в течение нескольких минут после выполнения ALTER DATABASE ... SET AUTOMATIC_INDEX_COMPACTION = ... команды.
Как он изменяет производительность запросов?
Запросы, как правило, выполняются быстрее и используют меньше операций ввода-вывода диска, памяти и ЦП. Это улучшение наиболее заметно в рабочих нагрузках с значительной активностью записи, которые в противном случае вызывают раздувание индекса.
Как это изменит потребление дискового пространства?
Сжатие уменьшает рост используемого пространства в файлах данных. Однако, в отличие от сжатия базы данных, он не уменьшает выделенный размер файлов данных.
Есть ли накладные расходы?
Для большинства рабочих нагрузок накладные расходы практически не заметны. Для рабочих нагрузок с интенсивной записью можно заметить увеличение операций ввода-вывода журнала транзакций и размера резервных копий журналов транзакций.
Может ли это вызвать блокировку?
Блокировка из-за автоматического сжатия маловероятна. При возникновении каких-либо блокировок они краткосрочные и временные (на миллисекунды).
Если запрос блокируется, проверьте команду главного блокировщика в sys.dm_exec_requests. Автоматическое сжатие может блокировать запросы, если команда VERSION_CLEANER_MAIN или VERSION_CLEANER_WORKER.
Учитывает ли он коэффициент заполнения?
Автоматическое сжатие никогда не заполняет страницу над коэффициентом заполнения. Однако если страница уже заполнена выше коэффициента заполнения предыдущими операторами DML, то сжатие не уменьшает плотность страницы. Страницы могут оставаться заполненными выше коэффициента заполнения после сжатия.
Работает ли он, если индекс использует сжатие строк или страниц?
Да. Автоматическое сжатие удаляет пустое пространство со страниц в индексе. Это не имеет значения, если данные на страницах сжимаются или нет.
Как это отличается от очистки теней?
Очистка призрака удаляет мягко удаленные строки со страниц, оставляя пустое место на странице. Автоматическое сжатие удаляет пустое пространство на страницах путем консолидации данных на меньшем количестве страниц.
Что происходит, если я запускаю перестроение индекса или реорганизацию индекса во время автоматического сжатия?
Автоматическое сжатие пропускает индексы, которые перестраиваются или реорганизуются, включая индексы в середине приостановленных возобновляемых операций индексов.
Примеры
Выполните следующие примеры T-SQL в пользовательской базе данных, а не в master базе данных.
Определение метрик ключевых индексов
Следующий запрос возвращает количество страниц на конечном уровне индекса, его средняя плотность страниц и фрагментация в page_countavg_page_space_used_in_percentavg_fragmentation_in_percent столбцах, соответственно, для индексов, которые имеют право на автоматическое сжатие. Запрос также возвращает итоговую строку, содержащую эти метрики в статистическом виде для всех индексов в базе данных.
SELECT COALESCE (OBJECT_SCHEMA_NAME(ips.object_id), '<Total>') AS schema_name,
COALESCE (OBJECT_NAME(ips.object_id), '<Total>') AS object_name,
COALESCE (i.name, '<Total>') AS index_name,
COALESCE (i.type_desc, '<Total>') AS index_type,
COALESCE (ips.partition_number, NULL) AS partition_number,
AVG(ips.avg_page_space_used_in_percent) AS avg_page_space_used_in_percent,
AVG(ips.avg_fragmentation_in_percent) AS avg_fragmentation_in_percent,
SUM(ips.record_count) AS record_count,
SUM(ips.page_count) AS page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
WHERE i.type_desc IN ('CLUSTERED', 'NONCLUSTERED', 'XML', 'SPATIAL')
AND ips.index_level = 0
AND ips.page_count > 0
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
GROUP BY ROLLUP(ips.object_id, i.name, i.type_desc, ips.partition_number)
HAVING ips.object_id IS NULL
AND ips.object_id IS NULL
AND i.name IS NULL
AND i.type_desc IS NULL
AND ips.partition_number IS NULL
OR ips.object_id IS NOT NULL
AND ips.object_id IS NOT NULL
AND i.name IS NOT NULL
AND i.type_desc IS NOT NULL
AND ips.partition_number IS NOT NULL
ORDER BY IIF (ips.object_id IS NULL, 0, 1), page_count DESC;
Запрос возвращает приблизительные результаты путем выборки подмножества страниц. Измените SAMPLED на DETAILED, чтобы получить более точные результаты. Использование DETAILED больших баз данных может занять гораздо больше времени, так как все соответствующие индексы в базе данных полностью сканируются. Более подробную информацию см. в sys.dm_db_index_physical_stats.
Используйте расширенное событие для мониторинга статистики сжатия
Расширенное auto_index_compaction_stats событие можно использовать для мониторинга статистики сжатия для базы данных. Событие срабатывает каждые 10 минут. Он содержит такие данные, как количество строк, перемещаемых между страницами для сжатия индекса, количество освобожденных страниц и количество попыток сжатия, пропущенных по различным причинам. Статистика, сообщаемая каждым событием, является совокупной с момента запуска ядра СУБД.
В следующем примере T-SQL создается и запускается сеанс событий, который собирает auto_index_compaction_stats данные события в целевом объекте ring_buffer . Запрос сравнивает текущее и предыдущее событие, чтобы возвращать статистику сжатия для каждого интервала в 10 минут. Так как запросу требуется по крайней мере два события для вычисления статистики за интервал времени, он может не возвращать данные до 20 минут после запуска сеанса событий.
/*
Create and start an event session collecting the auto_index_compaction_stats event
into a ring_buffer target
*/
IF NOT EXISTS (SELECT 1
FROM sys.dm_xe_database_sessions
WHERE name = N'automatic_index_compaction')
BEGIN
CREATE EVENT SESSION automatic_index_compaction ON DATABASE
ADD EVENT sqlserver.auto_index_compaction_stats
ADD TARGET package0.ring_buffer (SET MAX_MEMORY = 1024);
ALTER EVENT SESSION automatic_index_compaction ON DATABASE STATE = START;
END;
/* Get event data from the ring_buffer target */
DECLARE @EventData AS XML = (SELECT CAST (xst.target_data AS XML) AS TargetData
FROM sys.dm_xe_database_session_targets AS xst
INNER JOIN sys.dm_xe_database_sessions AS xs
ON xst.event_session_address = xs.address
WHERE xs.name = N'automatic_index_compaction');
/* Return statistics for each 10-minute interval */
WITH compaction_stats_event AS (
SELECT d.value('@timestamp', 'datetimeoffset') AS timestamp,
d.value('(data[@name = "database_id"]/value/text())[1]', 'smallint') AS database_id,
d.value('(data[@name = "compact_attempts"]/value/text())[1]', 'bigint') AS compact_attempts,
d.value('(data[@name = "compact_completed"]/value/text())[1]', 'bigint') AS compact_completed,
d.value('(data[@name = "pages_deallocated_compaction"]/value/text())[1]', 'bigint') AS pages_deallocated_compaction,
d.value('(data[@name = "rows_moved"]/value/text())[1]', 'bigint') AS rows_moved
FROM @EventData.nodes('/RingBufferTarget/event') AS e(d)
WHERE e.d.value('@name', 'sysname') = 'auto_index_compaction_stats'
),
timestamp_map AS (
SELECT database_id,
timestamp,
LAG(timestamp) OVER (PARTITION BY database_id ORDER BY timestamp) AS previous_timestamp
FROM compaction_stats_event
)
SELECT c.timestamp,
c.database_id,
c.compact_attempts - p.compact_attempts AS compact_attempts,
c.compact_completed - p.compact_completed AS compact_completed,
c.pages_deallocated_compaction - p.pages_deallocated_compaction AS pages_deallocated_compaction,
c.rows_moved - p.rows_moved AS rows_moved
FROM compaction_stats_event AS c
INNER JOIN timestamp_map AS tm
ON c.timestamp = tm.timestamp
AND c.database_id = tm.database_id
INNER JOIN compaction_stats_event AS p
ON tm.previous_timestamp = p.timestamp
AND tm.database_id = p.database_id
ORDER BY timestamp DESC;
Вы можете изменить предыдущий запрос, чтобы включить другие поля событий и вернуть больше статистики, например количество попыток сжатия, пропущенных по различным причинам. Используйте следующий запрос, чтобы просмотреть все поля auto_index_compaction_stats события и их описания.
SELECT name,
type_name,
description
FROM sys.dm_xe_object_columns
WHERE object_name = N'auto_index_compaction_stats'
AND column_type = N'data';
Отправка отзывов
Корпорация Майкрософт заинтересована в ваших отзывах об автоматическом уплотнении индекса. Отправьте отзыв о продукте, разместив новую идею на форуме отзывов SQL. Другие члены сообщества могут голосовать за и комментировать ваши идеи и предложения. Сообщества голосов и комментариев помогают Корпорации Майкрософт планировать и определять приоритеты улучшений продукта.