Устранение неполадок хэш-индексов для оптимизированных для памяти таблиц

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Предварительные требования

Сведения, необходимые для понимания этой статьи, доступны в следующих статьях:

• Индексы для оптимизированных для памяти таблиц
• Хэш-индексы для оптимизированных для памяти таблиц

Практические величины

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

Однако даже если количество контейнеров BUCKET_COUNT умеренно ниже или выше предпочитаемого диапазона, производительность хэш-индекса, скорее всего, будет допустимой или приемлемой. Рекомендуется присвоить параметру BUCKET_COUNT для хэш-индекса значение, примерно равное количеству строк, которое оптимизированная для памяти таблица будет иметь после увеличения согласно прогнозам, или большее.
Предположим, что ваша растущая таблица имеет 2000 000 строк, но прогноз будет расти в 10 раз до 20 000 000 строк. Начните с числа контейнеров, которое в 10 раз превышает количество строк в таблице. Так вы получите запас для увеличения количества строк.

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

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

• Для такого индекса можно установить количество контейнеров в 2 000 000. Степень снижения производительности может быть приемлемой.

В индексе слишком много повторяющихся значений?

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

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

-- Calculate ratio of:  Rows / Unique_Values.  
DECLARE @allValues float(8) = 0.0, @uniqueVals float(8) = 0.0;  
  
SELECT @allValues = Count(*) FROM SupportEvent;  
  
SELECT @uniqueVals = Count(*) FROM  
  (SELECT DISTINCT SupportEngineerName  
      FROM SupportEvent) as d;  
  
    -- If (All / Unique) >= 10.0, use a nonclustered index, not a hash.   
SELECT Cast((@allValues / @uniqueVals) as float) as [All_divby_Unique];  
go  

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

Устранение неполадок, связанных с числом контейнеров хэш-индекса

В этом разделе рассказывается, как устранять неполадки, связанные с числом контейнеров хэш-индекса.

Отслеживание статистики для цепочек и пустых контейнеров

Для отслеживания показателей работоспособности хэш-индексов можно выполнить следующую инструкцию T-SQL SELECT. Эта инструкция SELECT использует динамическое административное представление с именем sys.dm_db_xtp_hash_index_stats.

SELECT  
  QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],   
  i.name                   as [index],   
  h.total_bucket_count,  
  h.empty_bucket_count,  
    
  FLOOR((  
    CAST(h.empty_bucket_count as float) /  
      h.total_bucket_count) * 100)  
                            as [empty_bucket_percent],  
  h.avg_chain_length,   
  h.max_chain_length  
FROM  
        sys.dm_db_xtp_hash_index_stats  as h   
  JOIN sys.indexes                     as i  
          ON h.object_id = i.object_id  
          AND h.index_id  = i.index_id  
JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type=1
ORDER BY [table], [index];  

Сравните результаты, полученные от инструкции SELECT, со следующими статистическими рекомендациями.

• Пустые контейнеры:
  • 33 % является хорошим целевым значением, но обычно подходит более высокий процент (даже 90 %).
  • Если количество контейнеров равно количеству уникальных значений ключей, то примерно 33 % контейнеров пусты.
  • Значение ниже 10 % считается слишком маленьким.
• Цепочки в контейнерах:
  • Средняя длина цепочки, равная 1, является оптимальной в случае, когда нет повторяющихся значений ключей индекса. Обычно приемлемыми являются цепочки длиной до 10.
  • Если средняя длина цепочки превышает 10 и доля пустых контейнеров превышает 10 %, это означает, что данные содержат так много дубликатов, что хэш-индекс может быть не самым подходящим типом индекса.

Демонстрация цепочек и пустых контейнеров

Следующий блок кода T-SQL позволяет легко протестировать SELECT * FROM sys.dm_db_xtp_hash_index_stats;. Выполнения блока кода занимает 1 минуту. Блок кода включает следующие этапы:

1. Создает оптимизированную для памяти таблицу, которая имеет несколько хэш-индексов.
2. Заполняет эту таблицу несколькими тысячами строк.
   a. Для настройки частоты повторяющихся значений в столбце StatusCode используется оператор остатка от деления.
   b. В цикле в таблицу вставляется (INSERT) 262 144 строки примерно за 1 минуту.
3. Выводит приглашение выполнить предыдущую инструкцию SELECT из sys.dm_db_xtp_hash_index_stats.

DROP TABLE IF EXISTS SalesOrder_Mem;  
go  
  
  
CREATE TABLE SalesOrder_Mem  
(  
  SalesOrderId   uniqueidentifier  NOT NULL  DEFAULT newid(),  
  OrderSequence  int               NOT NULL,  
  OrderDate      datetime2(3)      NOT NULL,  
  StatusCode     tinyint           NOT NULL,  
  
  PRIMARY KEY NONCLUSTERED  
      HASH (SalesOrderId)  WITH (BUCKET_COUNT = 262144),  
  
  INDEX ix_OrderSequence  
      HASH (OrderSequence) WITH (BUCKET_COUNT = 20000),  
  
  INDEX ix_StatusCode  
      HASH (StatusCode)    WITH (BUCKET_COUNT = 8),  
  
  INDEX ix_OrderDate       NONCLUSTERED (OrderDate DESC)  
)  
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
go  
  
--------------------  
  
SET NOCOUNT ON;  
  
-- Same as PK bucket_count.  68 seconds to complete.  
DECLARE @i int = 262144;  
  
BEGIN TRANSACTION;  
  
WHILE @i > 0  
BEGIN  
  
  INSERT SalesOrder_Mem  
      (OrderSequence, OrderDate, StatusCode)  
    Values  
      (@i, GetUtcDate(), @i % 8);  -- Modulo technique.  
  
  SET @i -= 1;  
END  
COMMIT TRANSACTION;  
  
PRINT 'Next, you should query:  sys.dm_db_xtp_hash_index_stats .';  
go  

Предыдущий цикл INSERT выполняет указанные ниже действия.

• Вставляет уникальные значения в индекс первичного ключа и в ix_OrderSequence.
• Вставляет несколько сотен тысяч строк, представляющих только восемь разных значений.StatusCode Следовательно, существует высокая доля дублирования значений в индексе ix_StatusCode.

Если число контейнеров не является оптимальным, изучите следующие выходные данные инструкции SELECT из sys.dm_db_xtp_hash_index_statsдля решения проблемы. Для этих результатов мы добавили WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' в операцию SELECT, скопированную из раздела Г.1.

Результаты операции SELECT отображаются после кода и искусственно разбиваются на две таблицы более узких результатов для более удобного отображения.

• Приведем результаты для числа контейнеров.

IndexName	total_bucket_count	empty_bucket_count	EmptyBucketPercent
ix_OrderSequence	32768	13	0
ix_StatusCode	8	4	50
PK_SalesOrd_B14003...	262144	96525	36

• Приведем результаты для длины цепочки.

IndexName	avg_chain_length	max_chain_length
ix_OrderSequence	8	26
ix_StatusCode	65536	65536
PK_SalesOrd_B14003...	1	8

Разберем предыдущие таблицы результатов для трех хэш-индексов.

ix_StatusCode:

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

ix_OrderSequence:

• 0 % контейнеров пусты, это слишком мало.
• Средняя длина цепочки составляет 8 даже несмотря на то, что все значения в этом индексе являются уникальными.
  • Поэтому число контейнеров следует увеличить, чтобы уменьшить среднюю длину цепочки до 2 или 3.
• Поскольку ключ индекса имеет 262 144 уникальных значения, число контейнеров должно быть не менее 262 144.
  • Если в будущем количество строк увеличится, количество контейнеров должно быть больше.

Индекс первичного ключа (PK_SalesOrd_...):

• 36 % контейнеров пусты, это хорошо.
• Средняя длина цепочки равна 1, что тоже является хорошим показателем. Изменения не требуются.

Достижение компромисса

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

Если использование памяти имеет большее значение:

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

Если производительность проверок на равенство имеет большее значение:

• допустимо увеличить число контейнеров, так чтобы оно превышало количество уникальных значений индекса в 2–3 раза. Более высокое число означает:
  • Более быстрое получение значений при поиске конкретного значения.
  • Увеличенное использование памяти.
  • Увеличение времени, необходимого для полного сканирования хэш-индекса.

Дополнительные материалы

Хэш-индексы для оптимизированных для памяти таблиц
Некластеризованные индексы для таблиц, оптимизированных для памяти