Предположим, каждую ночь вам нужно создавать совершенно новую копию таблицы.
Да, вы не раз слышали, что положено обнаруживать и отслеживать изменения и загружать лишь дельты, но по требованиям вашего бизнеса приходится копировать таблицу целиком с нуля — и делать это как можно быстрее.
Блокировки, ожидания блокировок и уровни изоляции
Коротко суть такова:
- Если вы можете управлять запросом — используйте кучу, но критически важно применить подсказку
TABLOCKXили выполнитьselect-into. - Если управлять запросом вы не можете, а кто-то другой выполняет обычную вставку без подсказок — используйте кластерный индекс.
Вот и всё. Это и есть главный вывод. Если у вас найдётся время понять, почему так происходит, и узнать больше о настройке производительности самого процесса, — засучим рукава и разберёмся.
Ручек, которые здесь можно покрутить, немало.
Причём куда больше, чем можно ожидать. Часть из них Microsoft описала в руководстве SQL Server 2008 Data Loading Performance Guide (копия в Wayback Machine — на случай, если оно пропадёт, а ещё лучше сразу в переводе на этом же сайте), но с тех пор его ни разу не обновляли, а за прошедшие годы многое изменилось. Пол Уайт написал несколько статей (часть 1, часть 2, часть 3), проясняющих некоторые нюансы минимального журналирования.
Вот лишь некоторые из этих ручек:
- Какая модель восстановления у целевой базы данных?
- Является ли вставка межбазовым запросом? От этого зависит, доступна ли вам отложенная фиксация.
- Целевая таблица — это кластерный индекс или куча?
- Есть ли у целевой таблицы какие-нибудь индексы?
- Загружаем таблицу с нуля или в ней уже есть строки?
- Загрузка идёт из одного сеанса или из нескольких параллельно?
- Можем ли мы изменить запрос, наполняющий таблицу, — например, добавить подсказки?
Две из них, на которых я здесь и сосредоточусь, дают по-настоящему большую разницу при минимальных изменениях — особенно когда вы загружаете пустую таблицу:
- Есть у таблицы кластерный индекс или нет?
- Применяется ли при загрузке таблицы подсказка
TABLOCKXили нет?
Если решите повторить эти тесты, делайте это на разогретом SQL Server, где таблица Comments уже находится в кэше, а в файлах данных и журнале транзакций достаточно свободного места, — так вы избежите посторонних ожиданий, связанных с выделением памяти или пространства файлов.
Тест 1: вставка в кластерный индекс
В моём случае клиент делал примерно следующее с таблицей на 20 ГБ, поэтому я возьму таблицу Comments из базы данных Stack Overflow.
/* Test 1: Insert into a table with a clustered primary key */
DROP TABLE IF EXISTS dbo.Comments_PK;
GO
CREATE TABLE [dbo].[Comments_PK](
[Id] [int] PRIMARY KEY CLUSTERED NOT NULL,
[CreationDate] [datetime] NOT NULL,
[PostId] [int] NOT NULL,
[Score] [int] NULL,
[Text] [nvarchar](700) NOT NULL,
[UserId] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.Comments_PK(Id, CreationDate, PostId, Score, Text, UserId)
SELECT Id, CreationDate, PostId, Score, Text, UserId
FROM dbo.Comments;
GO
В моей лаборатории (4-ядерный инстанс AWS с 32 ГБ оперативной памяти, локальный NVMe-накопитель, SQL Server 2025) запрос выполнялся 98 секунд, израсходовав при этом 86 секунд процессорного времени. План выполнения предельно прост:
Тест 2: вставка в кучу
Возможно, вы слышали, что таблица без кластерного индекса — она же куча — лучше подходит для промежуточных сред в хранилищах данных. Казалось бы, объект, где не нужно ничего сортировать, по определению должен быть более быстрым приёмником, чем таблица с упорядоченным кластерным индексом. Проверим:
/* Test 2: Insert into a heap */
DROP TABLE IF EXISTS dbo.Comments_Heap;
GO
CREATE TABLE [dbo].[Comments_Heap](
[Id] [int] NOT NULL,
[CreationDate] [datetime] NOT NULL,
[PostId] [int] NOT NULL,
[Score] [int] NULL,
[Text] [nvarchar](700) NOT NULL,
[UserId] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.Comments_Heap(Id, CreationDate, PostId, Score, Text, UserId)
SELECT Id, CreationDate, PostId, Score, Text, UserId
FROM dbo.Comments;
GO
Этот тест занимает 150 секунд и 131 секунду процессорного времени. На 50% медленнее. Почему? План запроса выглядит предельно просто:
Главная беда вовсе не в том, что вставка в кучу медленна: 150 секунд на загрузку примерно 20 ГБ данных — это вполне достойные 130 МБ/с, быстрее, чем способна Azure SQL DB Hyperscale при любом уровне масштабирования.
«Проблема» в другом: у SQL Server есть оптимизация. Если источник гарантированно отдаёт данные ровно в том порядке, который нужен целевой таблице, загрузка идёт быстрее. Это работало ещё со времён руководства 2008 года, но, похоже, со временем производительность стала только лучше — тогда как кучи при обычных инструкциях INSERT так и не ускорились.
Пока что первый вывод такой: если вы выполняете обычные INSERT без подсказок, целевой должна быть таблица с кластерным индексом. Однако всё меняется, когда мы начинаем добавлять в запросы подсказки.
Тест 3: вставка в кластерный индекс с TABLOCKX
Подсказка TABLOCKX гарантирует, что кроме нас целевую таблицу никто не трогает:
/* Test 3: Clustered primary key, with tablock */
DROP TABLE IF EXISTS dbo.Comments_PK;
GO
CREATE TABLE [dbo].[Comments_PK](
[Id] [int] PRIMARY KEY CLUSTERED NOT NULL,
[CreationDate] [datetime] NOT NULL,
[PostId] [int] NOT NULL,
[Score] [int] NULL,
[Text] [nvarchar](700) NOT NULL,
[UserId] [int] NULL
) ON [PRIMARY]
GO
INSERT dbo.Comments_PK WITH (TABLOCKX) (Id, CreationDate, PostId, Score, Text, UserId)
SELECT Id, CreationDate, PostId, Score, Text, UserId
FROM dbo.Comments;
GO
Этот тест занимает всего 53 секунды и лишь 47 секунд процессорного времени. Вдвое быстрее, чем без подсказки TABLOCKX, — хотя план запроса выглядит так же:
Ну хорошо, звучит логично: у SQL Server есть оптимизации, которые он может применить, когда уверен, что таблицу никто больше не трогает. Но так ли это логично на самом деле? Мне — нет. Ведь эскалация блокировок всё равно случится почти мгновенно, и мы получим монопольную блокировку на таблицу. Так почему бы, если оценка числа строк в исходной таблице превышает 5 000, не взять TABLOCKX автоматически? Зачем заставлять пользователя указывать её вручную? И зачем мне стирать банные полотенца? Чем они пачкаются, если я лишь вытираюсь после чистого душа? Иные загадки так и не разгадать.
Тест 4: вставка в кучу с TABLOCKX
Судя по увиденному, этот тест должен оказаться медленнее предыдущей вставки в кластерный индекс с TABLOCKX: у кучи нет тех изящных оптимизаций, которые даёт гарантия упорядоченности источника. Но проверим:
/* Test 4: Insert into a heap, tablock hint */
DROP TABLE IF EXISTS dbo.Comments_Heap;
GO
CREATE TABLE [dbo].[Comments_Heap](
[Id] [int] NOT NULL,
[CreationDate] [datetime] NOT NULL,
[PostId] [int] NOT NULL,
[Score] [int] NULL,
[Text] [nvarchar](700) NOT NULL,
[UserId] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.Comments_Heap WITH (TABLOCKX) (Id, CreationDate, PostId, Score, Text, UserId)
SELECT Id, CreationDate, PostId, Score, Text, UserId
FROM dbo.Comments;
GO
НУ ВЫ ДАЁТЕ: запрос отрабатывает всего за 18 секунд! Но... при этом сжигается 67 секунд процессорного времени — верный признак того, что здесь сработала совершенно иная оптимизация:
Это первый план запроса в статье с параллелизмом, причём параллельно и чтение из исходной таблицы, и запись в целевую. Вот оно что! На моей лабораторной машине всего 4 ядра, и чем мощнее сервер, на котором крутится ваш ночной процесс, тем больший выигрыш даст этот приём.
Забавный совет: запрос выполнится ещё быстрее — за считанные СЕКУНДЫ, — если подсказку tablockx поставить и на исходную таблицу, вот так:
INSERT INTO dbo.Comments_Heap WITH (TABLOCKX)
(Id, CreationDate, PostId, Score, Text, UserId)
SELECT Id, CreationDate, PostId, Score, Text, UserId
FROM StackOverflow.dbo.Comments WITH (TABLOCKX);
Потому что тогда запрос впадает во взаимоблокировку — с самим собой, ха-ха. Оценочный план для такого запроса вы получите, но выполняться он не будет — по крайней мере, на SQL Server 2025 CU10. Глупые взаимоблокировки параллелизма. Впрочем, можно взять подсказку TABLOCK вместо TABLOCKX — тогда всё сработает.
Тест 5: SELECT INTO в кучу
Кое-кто из вас ленив и не любит заранее объявлять таблицы — попробуем и такой вариант:
DROP TABLE IF EXISTS dbo.Comments_Heap;
GO
SELECT Id, CreationDate, PostId, Score, Text, UserId
INTO dbo.Comments_Heap
FROM dbo.Comments;
GO
Неплохо: 21 секунда выполнения и 83 секунды процессорного времени. Не так хорошо, как при явном создании таблицы, но всё же лучше, чем возиться с кластерным индексом.
Я лично не любитель select-into: он часто медленнее, как мы только что видели, а ещё с ним можно нарваться на неточные типы данных в целевой таблице — особенно когда источником служит объединение.
Финальная таблица результатов: кучи с TABLOCKX
Результаты — от худшего к лучшему:
- INSERT INTO в кучу: 150 секунд
- INSERT INTO в кластерный индекс: 98 секунд
- INSERT INTO в кластерный индекс с подсказкой TABLOCKX: 53 секунды
- SELECT INTO в кучу: 21 секунда
- INSERT INTO в кучу с подсказкой TABLOCKX: 18 секунд
Меня забавляет, что куча оказывается одновременно и самым медленным, и самым быстрым способом — в зависимости от того, применяете ли вы ту самую волшебную подсказку. Именно поэтому в начале статьи я сказал:
- Если вы можете управлять запросом — используйте кучу, но критически важно применить подсказку
TABLOCKXили выполнитьselect-into. - Если управлять запросом вы не можете, а кто-то другой выполняет обычную вставку без подсказок — используйте кластерный индекс.
Если вы выбрали путь кучи, а конечная таблица должна в итоге получить кластерные индексы, тестирование ещё не закончено. Возможно, вам окажется быстрее писать сразу в кластерный индекс с TABLOCKX и на этом остановиться. Но в случае моего клиента всё было иначе: сначала над одной и той же таблицей выполнялось несколько проходов обновлений с соединениями с другими таблицами, чтобы что-то изменить и удалить, поэтому разумнее было:
- Быстро загрузить данные в кучу с TABLOCKX.
- Сделать несколько проходов по куче, выполняя обновления и удаления.
- Считать ночной процесс завершённым в том смысле, что может стартовать следующий этап ETL, и при этом
- Построить на таблице кластерный индекс в оперативном режиме с высоким
MAXDOP, пока другие процессы продолжают работать (и даже могут читать из этой таблицы).
При загрузке данных предстоит ещё масса решений по проектированию и последствий для производительности, и по-хорошему стоит заниматься обнаружением изменений, а не устраивать «День сурка», раз за разом перезаливая одну и ту же таблицу с нуля. Но если уж приходится грузить данные с нуля — попробуйте пустую кучу с подсказкой TABLOCKX.






Комментариев нет:
Отправить комментарий