Поделиться через

Определение правильного количества корзин для хэш-индексов

При создании оптимизированной для памяти таблицы необходимо указать значение параметра BUCKET_COUNT . В этом разделе приведены рекомендации по определению соответствующего BUCKET_COUNT значения параметра. Если вы не можете определить правильное количество контейнеров, используйте вместо него некластеризованный индекс. Неправильное BUCKET_COUNT значение, особенно слишком низкое, может значительно повлиять на производительность рабочей нагрузки, а также время восстановления базы данных. Лучше переоценить количество ведер.

Повторяющиеся ключи индекса могут ухудшить производительность хэш-индекса, так как ключи хэшируются в одну и ту же корзину, что приводит к увеличению цепочки этой корзины.

Дополнительные сведения об некластеризованных хэш-индексах см. в разделе Хэш-индексы и рекомендации по использованию индексов в таблицах Memory-Optimized.

Одна хэш-таблица выделяется для каждого хэш-индекса в оптимизированной для памяти таблице. Размер хэш-таблицы, выделенной для индекса, задается параметром BUCKET_COUNTCREATE TABLE (Transact-SQL) или CREATE TYPE (Transact-SQL). Количество корзин будет округлено до следующего ступеня степени двойки. Например, указание количества контейнеров в 300 000 приведет к фактическому количеству контейнеров 524 288.

Ссылки на статью и видео о количестве сегментов см. в статье о том, как определить правильное количество сегментов для хэш-индексов (In-Memory OLTP).

Рекомендации

В большинстве случаев число корзин должно быть в 1-2 раза больше количества различных значений в ключе индекса. Если ключ индекса содержит много повторяющихся значений, в среднем есть более 10 строк для каждого значения ключа индекса, используйте вместо него некластеризованный индекс.

Возможно, вы не всегда сможете предсказать, сколько значений определенного ключа индекса может иметь или будет иметь. Производительность должна быть приемлемой, если BUCKET_COUNT значение находится в пределах 5 раз от фактического числа значений ключей.

Чтобы определить количество уникальных ключей индекса в существующих данных, используйте запросы, аналогичные следующим примерам:

Первичный ключ и уникальные индексы

Так как индекс первичного ключа является уникальным, число уникальных значений в ключе соответствует количеству строк в таблице. Пример первичного ключа на (SalesOrderID, SalesOrderDetailID) в таблице Sales.SalesOrderDetail в базе данных AdventureWorks: выполните следующий запрос, чтобы вычислить количество уникальных значений первичного ключа, что соответствует количеству строк в таблице.

SELECT COUNT(*) AS [row count]   
FROM Sales.SalesOrderDetail

В этом запросе отображается число строк 121,317. Используйте число сегментов 240 000, если число строк не изменится значительно. Используйте ведро ёмкостью 480 000, если ожидается, что число заказов на продажу в таблице увеличится в четыре раза.

Не уникальные индексы

Для других индексов, например индекс с несколькими столбцами (SpecialOfferID, ProductID), выполните следующий запрос, чтобы определить количество уникальных значений ключа индекса:

SELECT COUNT(*) AS [SpecialOfferID_ProductID index key count]  
FROM   
   (SELECT DISTINCT SpecialOfferID, ProductID   
    FROM Sales.SalesOrderDetail) t

Этот запрос возвращает число ключей индекса для (SpecialOfferID, ProductID) 484, указывающее, что некластеризованный индекс следует использовать вместо некластеризованного хэш-индекса.

Определение количества повторяющихся данных

Чтобы определить среднее число повторяющихся значений для значения ключа индекса, разделите общее количество строк на число уникальных ключей индекса.

Для примера индекса (SpecialOfferID, ProductID) это приводит к 121317 / 484 = 251. Это означает, что значения ключа индекса имеют среднее значение 251, поэтому это должен быть некластеризованный индекс.

Устранение неполадок с количеством контейнеров

Чтобы устранить проблемы с числом контейнеров в таблицах, оптимизированных для памяти, используйте sys.dm_db_xtp_hash_index_stats (Transact-SQL) для получения статистики о пустых контейнерах и длине цепочек строк. Следующий запрос можно использовать для получения статистики обо всех хэш-индексах в текущей базе данных. Запрос может занять несколько минут, если в базе данных есть большие таблицы.

SELECT   
   object_name(hs.object_id) AS 'object name',   
   i.name as 'index name',   
   hs.total_bucket_count,  
   hs.empty_bucket_count,  
   floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',  
   hs.avg_chain_length,   
   hs.max_chain_length  
FROM sys.dm_db_xtp_hash_index_stats AS hs   
   JOIN sys.indexes AS i   
   ON hs.object_id=i.object_id AND hs.index_id=i.index_id

Ниже приведены два ключевых индикатора работоспособности хэш-индекса:

процент пустых баков
empty_bucket_percent указывает количество пустых контейнеров в хэш-индексе.

Если empty_bucket_percent менее 10 процентов, число контейнеров, скорее всего, будет слишком низким. В идеале empty_bucket_percent должно быть 33 процента или больше. Если число сегментов соответствует количеству значений ключа индекса, то из-за хэш-распределения примерно 1/3 сегментов будет пустым.

средняя_длина_цепи
avg_chain_length указывает среднюю длину цепочек строк в хэш-контейнерах.

Если avg_chain_length больше 10 и empty_bucket_percent больше 10 процентов, скорее всего, будет много повторяющихся значений ключа индекса, а некластеризованный индекс будет более подходящим. Средняя длина цепочки 1 идеальна.

Существует два фактора, влияющие на длину цепочки:

  1. Дубликаты; все повторяющиеся строки являются частью одной цепочки в хэш-индексе.

  2. Несколько значений ключей сопоставляют с одним и тем же контейнером. Чем ниже число контейнеров, тем больше контейнеров, которые будут иметь несколько значений, сопоставленных с ними.

В качестве примера рассмотрим следующую таблицу и скрипт для вставки примеров строк в таблицу:

CREATE TABLE [Sales].[SalesOrderHeader_test]  
(  
   [SalesOrderID] [uniqueidentifier] NOT NULL DEFAULT (newid()),  
   [OrderSequence] int NOT NULL,  
   [OrderDate] [datetime2](7) NOT NULL,  
   [Status] [tinyint] NOT NULL,  
  
PRIMARY KEY NONCLUSTERED HASH ([SalesOrderID]) WITH ( BUCKET_COUNT = 262144 ),  
INDEX IX_OrderSequence HASH (OrderSequence) WITH ( BUCKET_COUNT = 20000),  
INDEX IX_Status HASH ([Status]) WITH ( BUCKET_COUNT = 8),  
INDEX IX_OrderDate NONCLUSTERED ([OrderDate] ASC),  
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )  
GO  
  
DECLARE @i int = 0  
BEGIN TRAN  
WHILE @i < 262144  
BEGIN  
   INSERT Sales.SalesOrderHeader_test (OrderSequence, OrderDate, [Status]) VALUES (@i, sysdatetime(), @i % 8)  
   SET @i += 1  
END  
COMMIT  
GO

Скрипт вставляет в таблицу 262 144 строки. Он вставляет уникальные значения в индекс первичного ключа и в индекс IX_OrderSequence. Он вставляет много повторяющихся значений в индекс IX_Status: скрипт создает только 8 уникальных значений.

Выходные данные запроса по устранению неполадок BUCKET_COUNT приведены следующим образом:

Имя индекса общее количество контейнеров счетчик пустых корзин процент пустого ведра средняя длина цепи максимальная_длина_цепи
IX_Status 8 4 50 65536 65536
IX_ПоследовательностьЗаказов 32768 13 (тринадцать) 0 8 26
PK_SalesOrd_B14003C3F8FB3364 262144 96319 36 1 8

Рассмотрим три хэш-индекса в этой таблице:

  • IX_Status: 50 процентов контейнеров пусты, что хорошо. Однако средняя длина цепочки очень высока (65 536). Это означает большое количество повторяющихся значений. Поэтому использование некластеризованного хэш-индекса в данном случае не подходит. Вместо этого следует пользоваться некластеризованным индексом.

  • IX_OrderSequence: 0 процентов емкостей пусты, что слишком мало. Кроме того, средняя длина цепочки составляет 8. Так как значения в этом индексе уникальны, это означает, что в среднем 8 значений сопоставляются с каждым контейнером. Число корзин должно быть увеличено. Так как ключ индекса имеет 262 144 уникальных значений, число контейнеров должно быть не менее 262 144. Если ожидается будущий рост, это число должно быть выше.

  • Индекс первичного ключа (PK__SalesOrder...): 36 процентов контейнеров пусты, что хорошо. Кроме того, средняя длина цепочки составляет 1, что также хорошо. Изменения не требуются.

Дополнительные сведения об устранении неполадок с хэш-индексами, оптимизированными для памяти, см. в статье "Устранение распространенных проблем с производительностью с помощью хэш-индексов Memory-Optimized".

Подробные рекомендации по дальнейшей оптимизации

В этом разделе рассматриваются дополнительные соображения по оптимизации количества ведер.

Чтобы добиться оптимальной производительности хэш-индексов, сбалансируйте объем памяти, выделенной хэш-таблице, и количество уникальных значений в ключе индекса. Существует также баланс между производительностью точечных запросов и сканированием таблиц.

  • Чем выше значение количества контейнеров, тем больше пустых контейнеров будет в индексе. Это влияет на использование памяти (8 байт на контейнер) и производительность сканирования таблиц, так как каждый контейнер сканируется в рамках сканирования таблицы.

  • Чем ниже число контейнеров, тем больше значений назначается одному контейнеру. Это снижает производительность поиска и вставки точек, так как SQL Server может потребоваться пройти по нескольким значениям в одном контейнере, чтобы найти значение, указанное предикатом поиска.

Если количество контейнеров значительно ниже числа уникальных ключей индекса, многие значения будут сопоставляться с каждым контейнером. Это снижает производительность большинства операций DML, особенно поиск по ключам (поиск индивидуальных ключей индекса) и операций вставки. Например, вы можете заметить низкую производительность запросов SELECT, а также операций UPDATE и DELETE с предикатами равенства, соответствующими ключевым столбцам индекса в операторе WHERE. Низкое количество контейнеров также влияет на время восстановления базы данных, так как индексы создаются при запуске базы данных.

Повторяющиеся значения ключа индекса

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

Все строки с одним ключом индекса будут попадать в одну и ту же цепь дубликатов. Если несколько ключей индекса находятся в одной корзине из-за хэш-столкновения, сканеры индексов всегда должны проверять всю цепочку дубликатов для первого значения, прежде чем они смогут определить первую строку, соответствующую второму значению. Повторяющиеся ключи также затрудняют сборщику мусора нахождение строки. Например, если для любого ключа существует 1000 дубликатов, а одна из строк удаляется, сборщик мусора должен проверить цепочку из 1000 дубликатов, чтобы отменить связь со строкой из индекса. Это верно, даже если запрос, обнаруживший удаление, использовал более эффективный индекс (индекс первичного ключа) для поиска строки, так как сборщик мусора должен отменить связь с каждым индексом.

Для хэш-индексов существует два способа уменьшить работу, вызванную повторяющимися значениями ключа индекса:

  • Вместо этого используйте некластеризованный индекс. Можно уменьшить дубликаты, добавив столбцы в ключ индекса без каких-либо изменений в приложении.

  • Укажите очень большое количество корзин для индекса. Например, от 20 до 100 раз больше числа уникальных ключей индекса. Это уменьшит количество хэш-столкновений.

Небольшие таблицы

Для небольших таблиц использование памяти обычно не является проблемой, так как размер индекса будет небольшим по сравнению с общим размером базы данных.

Теперь необходимо сделать выбор на основе типа производительности, которую вы хотите:

  • Если критически важные для производительности операции с индексом преимущественно являются операциями поиска и/или вставки, то для снижения вероятности конфликтов хэша будет подходить более высокое количество корзин. Три раза больше строк или даже больше будет лучшим вариантом.

  • Если полные сканирования индекса являются основными операциями, критично влияющими на производительность, используйте число бакетов, близкое к фактическому количеству значений ключа индекса.

Большие таблицы

Для больших таблиц использование памяти может стать проблемой. Например, с таблицей строк с 250 миллионами строк с 4 хэш-индексами, каждый из которых имеет количество сегментов в размере одного миллиарда, затраты на хэш-таблицы составляют 4 индекса * 1 млрд контейнеров * 8 байт = 32 гигабайт использования памяти. При выборе количества сегментов 250 миллионов для каждого из индексов общий объем затрат на хэш-таблицы будет составлять 8 гигабайт. Обратите внимание, что это дополнительно к 8 байтам памяти, которые каждый индекс добавляет к каждой отдельной строке, в результате чего общий объём памяти составляет 8 гигабайт в этом сценарии (4 индекса * 8 байт * 250 миллионов строк).

Полные сканирования таблиц обычно не находятся на критическом пути для рабочих нагрузок OLTP. Поэтому выбор между использованием памяти и производительностью поиска точек и операций вставки.

  • Если использование памяти является проблемой, выберите количество контейнеров, близкое к количеству значений ключа индекса. Количество сегментов не должно быть значительно ниже числа значений ключа индекса, так как это влияет на большинство операций DML, а также время, необходимое для восстановления базы данных после перезапуска сервера.

  • При оптимизации производительности поиска точек целесообразно увеличить число сегментов до двух или даже трех раз больше, чем количество уникальных значений индекса. Более высокое число сегментов означает увеличение использования памяти и увеличение времени, необходимого для полной проверки индекса.

См. также

Индексы в таблицах Memory-Optimized