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

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

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

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

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

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

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

Однако даже если количество контейнеров BUCKET_COUNT умеренно ниже или выше предпочитаемого диапазона, производительность хэш-индекса, скорее всего, будет допустимой или приемлемой. Рекомендуется задать параметру BUCKET_COUNT в хэш-индексе значение, примерно равное предполагаемому количеству строк, которые могут появиться в оптимизированной по памяти таблице.
Предположим, что ваша таблица, которая увеличивается, имеет 2 000 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 отображаются после кода и искусственно разбиваются на две более узкие таблицы для более удобного отображения.

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

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

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

ix_StatusCode:

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

ix_OrderSequence:

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

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

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

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

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

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

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

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

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

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

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