Когда речь идет об оптимизации производительности в SQL Server, необходимо учитывать различные факторы. Один из таких факторов – это значение хеш-ведра, используемое в таблицах InMemory OLTP. В этой статье мы рассмотрим влияние значений хеш-ведер на производительность и обсудим bewt практики для их установки.

Недавно я провел сессию с клиентом, который испытывал проблемы с производительностью, несмотря на то, что использовал InMemory OLTP в своей системе SQL Server 2014. При дальнейшем исследовании я обнаружил, что они установили очень низкое значение хеш-ведра в определении своей таблицы InMemory. Это вызвало мое любопытство и побудило меня углубиться в эту тему.

В SQL Server значения хеш-ведеров используются для равномерного распределения данных по таблицам, оптимизированным для работы в памяти. Рекомендуется установить значение хеш-ведра в два раза больше количества уникальных значений, чтобы избежать коллизий хешей, когда два значения попадают в одно хеш-ведро. Коллизии хешей могут негативно сказываться на производительности и должны быть избеганы.

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

Сначала я создал базу данных с именем “InMem_HashCollision” и определил таблицу InMemory с именем “InMem_Data1” с большим значением хеш-ведра – 1 048 576. Затем я вставил 1 миллион строк в эту таблицу и отследил время выполнения операции.

-- создание простой таблицы XTP с размером ведра 1048576
CREATE TABLE dbo.InMem_Data1
(
	Col1 INT NOT NULL
		PRIMARY KEY NONCLUSTERED 
		HASH WITH (BUCKET_COUNT = 1048576),
	Col2 INT NOT NULL,
	Col3 INT NOT NULL
)
WITH
(
	MEMORY_OPTIMIZED = ON, 
	DURABILITY = SCHEMA_AND_DATA
);

-- создание нативной скомпилированной хранимой процедуры для вставки данных
CREATE PROCEDURE dbo.InsertInMem_Data1
(
	@Rows [int]
)
WITH 
    NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS 
BEGIN
	ATOMIC WITH 
	(	TRANSACTION ISOLATION LEVEL = SNAPSHOT,
		LANGUAGE = 'us_english'
	)
	DECLARE @counter INT = 0;
	WHILE @counter < @Rows
	BEGIN
		INSERT INTO dbo.InMem_Data1 (Col1, Col2, Col3) 
		VALUES (@counter, @counter, @counter);
		SET @counter += 1;
	END
END;

-- выполнение хранимой процедуры для вставки 1000000 строк
DECLARE
	@starttime DATETIME = CURRENT_TIMESTAMP,
	@endtime DATETIME;
EXECUTE dbo.InsertInMem_Data1 1000000;
SET @endtime = CURRENT_TIMESTAMP;
PRINT CONCAT('Запрос выполнен за ', 
DATEDIFF(millisecond, @starttime, @endtime), ' миллисекунд.');

Вышеуказанный скрипт завершил операцию примерно за 1 секунду.

Затем я создал другую таблицу с именем “InMem_Data2” с меньшим значением хеш-ведра – 1 024. Я вставил те же 1 миллион строк в эту таблицу и отследил время выполнения.

-- создание таблицы с меньшим количеством хеш-ведеров
CREATE TABLE dbo.InMem_Data2
(
	Col1 INT NOT NULL
		PRIMARY KEY NONCLUSTERED HASH WITH 
		(BUCKET_COUNT = 1024),
	Col2 INT NOT NULL,
	Col3 INT NOT NULL
)
WITH
(
	MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA
);

-- создание нативной скомпилированной хранимой процедуры для вставки данных
CREATE PROCEDURE dbo.InsertInMem_Data2
(
	@Rows [int]
)
WITH 
    NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS 
BEGIN
	ATOMIC WITH 
	(
		TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
		LANGUAGE = 'us_english'
	)
	DECLARE @counter INT = 0;
	WHILE @counter < @Rows
	BEGIN
		INSERT INTO dbo.InMem_Data2 (Col1, Col2, Col3) 
		VALUES (@counter, @counter, @counter);
		SET @counter += 1;
	END
END;

-- выполнение хранимой процедуры для вставки 1000000 строк
DECLARE
	@starttime DATETIME = CURRENT_TIMESTAMP,
	@endtime DATETIME;
EXECUTE dbo.InsertInMem_Data2 1000000;
SET @endtime = CURRENT_TIMESTAMP;
PRINT CONCAT('Запрос выполнен за ', 
DATEDIFF(millisecond, @starttime, @endtime), ' миллисекунд.');

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

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

Как лучшую практику, я настоятельно рекомендую следить за этими настройками перед внедрением таблиц InMemory OLTP в ваше среду SQL Server. Установив соответствующие значения хеш-ведеров, вы можете обеспечить оптимальную производительность и избежать ненужных узких мест производительности.

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

Счастливой оптимизации!