По материалам технической статьи, посвящённой SQL Server: The Data Loading Performance Guide

Авторы: Томас Кайзер (Thomas Kejser), Питер Карлин (Peter Carlin) и Стюарт Озер (Stuart Ozer)
Техническая рецензия и экспертиза: Sunil Agarwal, Ted Lee, David Schwartz, Chris Lee, Lindsey Allen, Hermann Daeubler, Juergen Thomas,Sanjay Mishra, Denny Lee,

Peter Carlin, Lubor Kollar
Особая благодарность: Henk van der Valk (Unisys), Alexei Khalyako и Marcel van der Holst
Перевод: Александр Гладченко, Ирина Наумова, Влад Щербинин и Алексей Халяко
Дата издания: январь 2009г.
Статья относится к продуктам: SQL Server 2008 и SQL Server 2005

Резюме: Этот документ описывает стратегию массовой загрузки больших объёмов информации в базы данных SQL Server. Статья охватывает два распространённых метода, а также методологии повышения производительности и оптимизации процесса массовой загрузки данных.

Введение

Настоящая техническая статья описывает существующие стратегии массовой загрузки данных, которые применяются для быстрого внесения масштабных изменений в базах данных Microsoft ® SQL Server ®.
Прежде, чем углубиться в подробности методов массовой загрузки, давайте освежим в памяти некоторые базовые принципы минимального протоколирования, которые будут представлены в главе: “Разъяснения по минимально протоколируемым операциям”.
Следующие две главы: “Методы массовой загрузки” и “Другие минимально протоколируемые операции и операции над метаданными” содержат краткий обзор двух ключевых и взаимосвязанных концепций высокопроизводительной загрузки данных, таких как массовый импорт и экспорт данных и операции только над метаданными.
После небольшого погружения в тему, мы приступим к описанию способов использования этих методов в пользовательских сценариях. Приводимые тут примеры сценариев призваны проиллюстрировать типовые подходы, которые можно найти в главе: “Решения для типовых задач массовой загрузки”. Особо будут рассмотрены такие сценарии, когда загрузка данных в таблицу должна выполняться при одновременном чтении из этой же таблицы. В главе “Массовая загрузка, запросы с NOLOCK и Read Committed Snapshot Isolation” описаны методы, которые могут использоваться для достижения параллельной загрузки и чтения данных.
Эта техническая статья заканчивается главой “Оптимизация массовой загрузки данных”, в которой рассказано о поиске и устранении сопутствующих загрузке данных проблем.

Разъяснения по минимально протоколируемым операциям

Для поддержки сценариев загрузки больших объёмов данных, в SQL Server реализована возможность минимального протоколирования операций. В отличие от полного протоколирования, которое интенсивно использует журнал транзакций для отслеживания всех изменений строк, минимальное протоколирование операций отслеживает только распределение экстентов и изменения метаданных. Из-за того, что в журнале транзакций отслеживается существенно меньше информации, минимально протоколируемые операция, зачастую, исполняются быстрее, чем полностью протоколируемые, т.е. само протоколирование вносит большой вклад во время исполнения операции. Кроме того, из-за уменьшения необходимости осуществлять запись в журнал транзакций, размер журнала будет заметно меньше, и требования к поддержке дисковых операций ввода-вывода для него будут существенно ниже.
Следует помнить, что операция может являться операцией массовой загрузки, но при этом выполняться без минимального протоколирования. Например, при массовой загрузке данных в кластеризованный индекс или кучу, минимальное протоколирование может не использоваться. Минимальное протоколирование обычно обеспечивает дополнительный выигрыш по производительности, но даже без минимального протоколирования, массовая загрузка потребует меньше ресурсов, чем традиционная вставка строк данных.
Вопреки мифам о SQL Server, минимально протоколируемые операции могут являться частью транзакции. Поскольку все изменения в структурах распределения запротоколированы, становится возможным откатить минимально протоколируемые операции.
Минимально протоколируемые операции доступны, только если база данных использует простую модель восстановления или модель восстановления с неполным протоколированием (bulk-logged). Для получения более подробной информации, обратитесь к статье: “Операции, для которых возможно минимальное протоколирование”. Обратите внимание, что при планировании стратегии резервного копирования базы данных нужно учитывать возможность выполнения массовых операций в базе данных с неполным протоколированием. Для получения более подробной информации, обратитесь к статье: Резервное копирование с использованием модели восстановления с неполным протоколированием.

Флаг трассировки 610

В SQL Server 2008 появился флаг трассировки 610, который управляет минимальным протоколированием вставок в проиндексированные таблицы. Этот флаг трассировки может быть включен с помощью одного из следующих методов:

Добавление флага к параметрам запуска SQL Server.
- Для получения более подробной информации, обратитесь к статье: “Как настроить параметрызапуска сервера (диспетчер конфигурации SQL Server)”.
Выполнение команды
- В случае локального включения, флаг трассировки действует только на текущий сеанс. Такое включение флага трассировки 610 бывает полезно, если он нужен толькодля ограниченного набора сценариев загрузки данных на экземпляре сервера, и действует только на те инструкции Transact-SQL, которые выполняются в текущемподключении.
- В случае глобального включения, этот флаг трассировки будет действовать на все подключения к серверу, пока он не будет выключен или произойдёт рестартсервера. Для получения более подробной информации об использовании команды DBCC для включения и выключения флагов трассировки, прочтите статьи: “DBCC TRACEON (Transact-SQL)” и “DBCC TRACEOFF (Transact-SQL)”.

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

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

Пример 1: Имеется таблица, кластеризованая по целочисленному ключу, содержащему четные числа от 0 до 14. Таблица имеет четыре страницы листового уровня, и эти страницы не заполнены полностью и могут содержать еще по две строки на каждой странице.
Выполняется массовая загрузка ещё восьми новых строк, со значениями ключа, представляющими собой возрастающую последовательность нечётных чисел от 1 до 15. Новые строки будут размещаться в существующих страницах. На представленной ниже иллюстрации показано, как таблица будет выглядеть до и после операции загрузки.

Рисунок 1: Полностью протоколируемая вставка при включённом флаге трассировки 610

В этом примере не происходит распределения новых страниц, и включение флага трассировки 610 не приведёт к повышению производительности вставки за счёт минимального протоколирования.

Пример 2: Рассмотрим альтернативный сценарий: теперь таблица первоначально занимает две страницы, обе заполнены полностью и содержат записи со значениями ключа от 0 до 7. Далее выполняется массовая загрузка строк со значениями ключа от 8 до 15.

Рисунок 2: Минимально протоколируемая вставка при включённом флаге трассировки 610

Этот пример демонстрирует, что происходит при включённом флаге трассировки 610. В данном случае, вставка записей со значениями ключа 8-15 на выделенные для этого новые страницы (на рисунке выше это светло-синие прямоугольники) будет проходить с минимальным протоколированием.
Один из способов проверки того, сколько новых страниц было распределено для размещения записей таблицы, состоит в выполнении запроса к sys.dm_db_partition_stats. Следующий сценарий Transact-SQL вернёт число страниц каждого индекса и таблицы:

SELECT OBJECT_NAME(p.object_id) AS object_name , i.name AS index_name , ps.in_row_used_page_count FROM sys.dm_db_partition_stats ps JOIN sys.partitions p ON ps.partition_id = p.partition_id JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id

Сравнивая возвращаемые этим сценарием результаты до и после выполнения операции массовой загрузки, можно увидеть, сколько было распределено новых страниц.
Если во время массовой заливки в В-дерево используется флаг трассировки 610, рекомендуется указывать наибольшее из возможных значений параметра BATCHSIZE. В таком случае, SQL Server сможет распределить в заполняемой секции большое число новых страниц для каждого блока загрузки и секции. В некоторых случаях, такое распределение с включённым флагом трассировки 610, даже если страницы потом будут возвращены обратно, может привести к увеличению ввода-вывода, по сравнению с аналогичной нагрузкой без включения этого флага трассировки. Однако, при загрузке небольшого количества секций и использовании большого размера пакета, включение флага трассировки 610 может привести к существенному повышению производительности по сравнению с полным протоколированием вставок. Для получения более подробной информации о поведении минимального протоколирования при включении флага трассировки 610, ознакомьтесь со статьёй: “New update on minimal logging for SQL Server 2008” в блоге SQL Server Storage Engine.

Влияние на ввод-вывод минимального протоколирования с включённым флагом 610

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

Сводка по возможным условиям минимального протоколирования

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

Индексы таблицы	Число строк в таблице	Подсказка оптимизатору	Без флага 610	С флагом 610	Возможность конкурентного доступа
Куча	Любое	TABLOCK	Минимальное	Минимальное	ДА
Куча	Любое	НЕТ	Полное	Полное	ДА
Куча + Индекс	Любое	TABLOCK	Полное	Выбор (3)	НЕТ
Кластерный	Пустая	TABLOCK, ORDER (1)	Минимальное	Минимальное	НЕТ
Кластерный	Пустая	НЕТ	Полное	Минимальное	ДА (2)
Кластерный	Любое	НЕТ	Полное	Минимальное	ДА (2)
Кластерный	Любое	TABLOCK	Полное	Минимальное	НЕТ
Кластерный + Индекс	Любое	НЕТ	Полное	Выбор (3)	ДА (2)
Кластерный + Индекс	Любое	TABLOCK	Полное	Выбор (3)	НЕТ

Таблица 1: Сводка по возможным условиям минимального протоколирования

(1) Если используется метод INSERT | SELECT, можно не использовать подсказку ORDER, однако вставляемые строки должны быть отсортированы в том же порядке, что и кластеризованный индекс. При использовании BULK INSERT необходимо использовать подсказку ORDER.
(2) Параллельная загрузка возможна только в некоторых случаях. См. главу “Массовая загрузка при наличии индексов”. Кроме того, минимальное протоколирование будет использовано только для тех строк, которые попали на вновь распределённые страницы.
(3) Вставка в таблицу с некластеризованным индексом может быть полностью или минимально протоколирована в зависимости от выбранного оптимизатором плана исполнения запроса.

Методы массовой загрузки

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

Импорт данных средствами Integration Services
BCP
BULK INSERT
INSERT…SELECT
SELECT INTO

В этой статье, термин “массовая загрузка” относится к использованию любого из представленных в текущем разделе методов. Термин “BULK INSERT” (в верхнем регистре) относится к инструкциям Transact-SQL, которые базируются на тех методах массовой загрузки, которые описаны в статье “BULK INSERT (Transact-SQL)“.
Использование этих методов требует понимания их возможностей и ограничений. В этой главе будут кратко описаны те варианты, которые возможны для массовой загрузки данных из внешних по отношению к Ядру SQL Server источников, а также из источников, обслуживаемых тем же самым Ядром.
Кроме того, доступные нам методы массовой загрузки данных имеют в SQL Server свои программные интерфейсы, в ADO.NET это класс SQLBulkCopy, в OLE DB это IRowsetFastload, а в ODBC это библиотека SQL Server Native Client. Несмотря на то, что тематика программных интерфейсов выходит за рамки данной статьи, многие рекомендации, лучшие практики, параметры настройки и т.п., которые рассматриваются в статье, пригодны и для случаев использования программных интерфейсов массовой загрузки.

Импорт данных средствами Integration Services

Служба SQL Server Integration Services предоставляет самые гибкие средства для массовой загрузки данных в SQL Server. Данные могут читаться из любого источника, который совместимым с Integration Services, с преобразованием и конвертацией данных в памяти, и массовой загрузкой напрямую в SQL Server, без промежуточной материализации данных на диске. Поскольку Integration Services является самостоятельным процессом, он потенциально может быть запущен на другом компьютере, что позволяет существенно разгрузить процессоры SQL Server, сняв с них нагрузку на преобразование данных. Это довольно простой способ масштабирования массовых операций, когда необходимо добиться повышения производительности.
Существует две возможности загрузки данных, которые обеспечивают минимальное протоколирование и доступ к функциональности массовой загрузки SQL Server: это компоненты загрузки с именами: “Назначение SQL Server” и “Назначение OLE DB” (SQL Server Destination и OLE DB Destination).

НАЗНАЧЕНИЕ SQL SERVER

Загрузка в Назначение SQL Server является самым быстрым способом массовой загрузки из потока данных SQL Server Integration Services. Эта возможность поддерживает все варианты параметров массовой загрузки SQL Server, кроме ROWS_PER_BATCH.
Следует помнить, что использование этой возможности может потребовать подключения к SQL Server через Общую память, а это означает, что необходимо запускать службу Integration Services на том же компьютере, что и службы Ядра SQL Server.

НАЗНАЧЕНИЕ OLE DB

Загрузка в Назначение OLE DB поддерживает все варианты массовой загрузки, которые доступны для загрузки в SQL Server. Однако, для поддержки упорядоченной массовой загрузки, требуется некоторая дополнительная настройка. Для получения более подробной информации, ознакомьтесь со статьями: “Управление порядком сортировки во время массового импортирования данных” и “Выполнение операций массового копирования“. В этих статьях описано, как использовать параметры команд и утилит, или интерфейсы OLE DB для загрузки отсортированных данных (при использовании интерфейсов массовой загрузки, нужно включить режим “fast load”).
Загрузка в OLE DB для подключения к SQL Server использует протокол TCP/IP или именованные каналы. Это означает, что Назначение OLE DB, в отличие от Назначения SQL Server, может работать на другом компьютере, не на том, на который идёт массовая загрузка. Поскольку пакеты службы Integration Services, которые используют OLE DB, не обязаны работать непосредственно на том компьютере, на котором запущен SQL Server, становится возможным масштабирование ETL потоков с помощью множества недорогих серверов.

BCP

BCP (Bulk Copy Program) – утилита командной строки, используемая для извлечения и импорта данных в SQL Server. Программа разработана с использованием интерфейсов массового копирования и позволяет быстро вставлять данные из текстовых файлов напрямую в SQL Server. Кроме того, она позволяет экспортировать данные из таблиц SQL Server или из запросов в текстовые файлы.
BCP может читать файлы данных в формате таблиц баз данных (собственный формат SQL Server), а так же и другие форматы. Использование собственного формата работает наиболее быстро, за счёт минимального синтаксического анализа входного текстового файла.

BULK INSERT

Команда BULK INSERT представляет собой внутрипроцессный метод для переноса данных из текстовых файлов в SQL Server. Поскольку всё это выполняется в процессе Sqlservr.exe, загрузка файлов данных в SQL Server происходит очень быстро.
BULK INSERT не может использоваться для задач экспорта данных, только для импорта. Кроме этого ограничения, команда наделена всеми возможностями, присущими BCP. Команда BULK INSERT вызывается в сценариях, написанных на Transact-SQL, что делает её идеальной для использования в хранимых процедурах, основанных на Transact-SQL задачах ETL, и заданиях по расписанию службы SQL Server Agent.

SELECT INTO

Инструкция SELECT INTO создаёт новую таблицу, основанную на результирующем наборе инструкции SELECT. Вставка новых строк протоколируется минимально, что делает такой способ загрузки данных очень быстрым. К сожалению, для этой инструкции не существует способов управления BATCHSIZE или ROWS_PER_BATCH.
Ещё одним ограничением инструкции SELECT INTO является то, что результирующая таблица должна находиться в файловой группе по умолчанию. Хотя можно временно изменить значение по умолчанию для файловых групп, перед тем, как выполнить инструкция SELECT INTO, широкое использование такого сценария маловероятно.

INSERT…SELECT

В SQL Server 2008 появился новый способ, который позволяет добиться минимального протоколирования для операций вставки. Речь идёт об инструкции INSERT (Transact-SQL), которая при некоторых условиях может быть минимально протоколируемой.
У этой возможности есть несколько ограничений. Прежде всего, нужно отметить, что с этим методом не могут использоваться такие параметры массовой загрузки, как указание размера фиксации (commit size), управление проверкой ограничений и отключение триггеров. Во-вторых, таблица, куда идёт заливка, должна быть заблокирована с помощью монопольной (X) блокировки, в то время как другие методы массовой загрузки (Integration Services, BCP и BULK INSERT), описанные в этой статье, используют блокировку массового обновления (BU). Поскольку монопольная (X) блокировка не совместима с другими монопольными (X) блокировками, единовременно может выполниться только одна вставка. Для получения более подробной информации о блокировках массового обновления (BU) и монопольных (X) блокировках, ознакомьтесь со статьёй в SQL Server Books Online: “Режимы блокировки“.
Несмотря на то, что операция вставки будет однопоточной, инструкция INSERT…SELECT может быть распараллелена. Распараллеливаться сможет та часть инструкции INSERT…SELECT, в которой Ядро SQL Server исполняет инструкцию SELECT. Ввиду того, что этот метод может преобразовать данные во время транзита, высокая скорость преобразования данных, которое основано на инструкции Transact-SQL, достигается за счёт того, что запрос оптимизируется средствами SQL Server. Для таких операций, которые основаны на инструкции SELECT, именно это определяет скорость работы.
Будет ли минимально протоколироваться операция INSERT…SELECT в кластеризованный индекс, зависит от того, включён ли флаг трассировки 610. Операция INSERT…SELECT в кучу может минимально протоколироваться даже без включения флага трассировки 610.

Куча

Инструкция INSERT, которая получает строки от операции SELECT и вставляет их в кучу, будет протоколироваться минимально, когда для таблицы назначения используется подсказка WITH (TABLOCK).
Для того чтобы получить минимальное протоколирование операций вставки в кучу, используйте следующий синтаксис Transact-SQL:

INSERT INTO <ТаблицаНазначения> (<Колонки>) WITH (TABLOCK) SELECT <Колонки> FROM <НекоторыеУтверждения>

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

Кластеризованные индексы

Если операция INSERT…SELECT направлена в кластеризованный индекс, эта операция будет протоколироваться минимально, при условии, что будет активизирован флаг трассировки 610. Обратите внимание, что минимальное протоколирование используется даже без подсказки TABLOCK. Это означает, что может существовать несколько параллельных инструкций INSERT…SELECT, которые вставляют в таблицу данные в одно и то же время и в режиме минимального протоколирования. У такой возможности тоже есть несколько ограничений. Для получения более подробной информации, прочтите главу: “Массовая загрузка при наличии индексов”.
Кроме этого, операция массовой загрузки будет минимально протоколироваться без флага трассировки, когда истинны оба представленные ниже условия:

Для таблицы, куда направлена вставка, определена подсказка WITH (TABLOCK)
Таблица, куда направлена вставка, пуста

В упомянутом выше случае, на получающую данные таблицу будет наложена блокировка схемы (Sch-M), что будет препятствовать любому параллелизму. В этом случае вставка будет выполняться упорядоченно. Для получения более подробной информации, прочтите главу “Вставка упорядоченных данных”.

INSERT…SELECT с чтением из текстовых файлов или данных OLE DB источников

Команду INSERT…SELECT в SQL Server можно использовать для чтения из любого OLE DB источника данных. Для реализации такой возможности используется команда OPENROWSET в качестве исходной инструкции SELECT.
Использование OPENROWSET позволяет добиться точно такого же поведения, как у BULK INSERT и INSERT…SELECT, кроме того, становится возможным использовать соединения или фильтровать входной набор данных до их вставки.
Следует помнить, что BULK INSERT позволяет направлять несколько минимально протоколируемых потоков в кучу, а INSERT…SELECT может использовать для вставки только один поток.
OPENROWSET также позволяет настраивать несколько параметров пакетной обработки вставки. При использовании OPENROWSET в качестве источника INSERT…SELECT, доступны для настройки следующие параметры:

- ORDER
- ROWS_PER_BATCH или BATCH_SIZE
- IGNORE_CONSTRAINTS и IGNORE_TRIGGERS
- FIRSTROW и LASTROW
- KEEPDEFAULTS и KEEPIDENTITY

Для получения более подробной информации, прочтите статью SQL Server Books Online OPENROWSET (Transact-SQL).

Возможности записи для собственного ПО

“ODBC”, “OLE DB”, “.NET” и “DB-library for SQL Server” позволяют реализовать свои собственные методы массовой загрузки данных. Полное описание программных интерфейсов (API) массовой загрузки и их возможности выходят за рамки настоящей статьи.
Для получения информации о разработке собственных методов массовой загрузки, ознакомьтесь со следующими материалами:

Варианты выбора средств массовой загрузки

В представленной ниже таблице приведен краткий обзор разных методов массовой загрузки, доступных в Integration Services и SQL Server.

Функциональность	Integration Services		BULK INSERT	BCP	INSERT…SELECT
Функциональность	Получатель SQL Server	Получатель OLE DB	BULK INSERT	BCP	INSERT…SELECT
Протокол	Общая память (Shared Memory)	TCP/IP, Named Pipes	В памяти	TCP/IP, Shared Memory, Named Pipes	В памяти
Скорость	Быстро / Быстрейший(4)	Быстро / Быстрейший (1)	Быстрейший	Быстро	Медленно / Быстрейший (2)
Источник данных	Любой	Любой	Только файл с данными	Только файл с данными	Любой OLE DB источник
Поддержка массовых интерфейсов	Нет поддержки собственного формата	Нет ORDER, Нет поддержки собственного формата	Все	Все	Нет подсказок
Блокировка с подсказкой TABLOCK для кучи	BU	BU	BU	BU	X
Преобразование при транзите	Да	Да	Нет	Нет	Да
Размер ввода-вывода читаемого блока	Зависит от источника (3)	Зависит от источника (3)	64Кб	64Кб	До 512Kб
Версии SQL Server	2005 и 2008	2005 и 2008	7.0, 2000, 2005 и 2008	6.0, 7.0, 2000, 2005 и 2008	2008
Вызывается из:	DTEXEC / BIDS	DTEXEC / BIDS	Transact-SQL	Командная строка	Transact-SQL

Таблица 2: Варианты выбора средств массовой загрузки

(1) Если DTEXEC выполняется не на том же сервере, на котором запущена служба SQL Server, Integration Services может работать очень быстро, за счет освобождения Ядра базы данных от операций преобразования данных.
(2) Обратите внимание, что INSERT…SELECT не позволяет выполнять вставки в одну и ту же таблицу параллельно. Если вставка идёт в одну таблицу, скорее всего наиболее быстрым вариантом будет Integration Services, потому что это позволяет выполнять несколько потоков параллельно.
(3) Размер блока чтения зависит от источника. Для текстовых файлов, используются размеры блока 128Кб.
(4) Если назначением является SQL Server, то процессорных циклов будет затрачено больше, чем у BULK INSERT, и это будет ограничением максимальной скорости. Но поскольку Ядро базы данных освобождается от операций преобразования данных, производительность однопоточной вставки будет быстрее, чем у BULK INSERT.

Немного о BATCHSIZE и ROWS_PER_BATCH

Эти два параметра массовой загрузки нуждаются в некоторых разъяснениях. Первый из них – BATCHSIZE, он предписывает, сколько строк будет передано единовременно во время массовой операции. Если оставить это значение по умолчанию, вся массовая операция будет выполнена в одной большой транзакции. Однако, если параметру задано не нулевое значение, для передачи указанного серверу количества строк будет создана и завершена новая транзакция. Обычно параметр BATCHSIZE используется для поэтапной передачи всех строк, чтобы в случае сбоя не передавать весь пакет целиком. Однако нужно понимать, что автоматическая “докачка” строк не предусмотрена.

Отслеживание того, сколько строк было уже вставлено, является задачей разработчика приложения. Обратите внимание на то, что слишком маленькие значения для параметра BATCHSIZE могут привести к снижению производительности, из-за неоправданного увеличения затрат ресурсов на обслуживание транзакций. На Рисунке 3 можно увидеть, как значение этого параметра влияло на массовую загрузку 17Гб в таблицу одним потоком. Обратите внимание, что для значений больше 10000 наблюдается лишь небольшое увеличение производительности.
Ненулевые значения BATCHSIZE наиболее полезны для вставки в индексированные таблицы. Если не указывать упорядочивание данных, операция SORT будет выполнена внутренними механизмами для каждого индекса при вставке каждого пакета. Т.е. достаточно маленькие значения BATCHSIZE могут позволить выполнить сортировку в памяти, и обойтись без промежуточной материализации на диске. Однако это может стать причиной роста фрагментации индексов, которая также может привести к падению производительности запросов, которым свойственны упорядоченные просмотры диапазонов ключа. Фрагментации из-за вставки в кластеризованный индекс можно избежать, если вставлять данные в уже отсортированном виде, как это описано в главе “Вставка упорядоченных данных”.
Стоит проверить несколько вариантов настройки параметра BATCHSIZE для свойственных вашим приложениям нагрузок, что позволит увидеть влияние разных значений на производительность загрузки и итоговую фрагментацию. Кроме того, ненулевое значение параметра BATCHSIZE может оказаться полезным, если не используется подсказка TABLOCK, и нужно избежать эскалации блокировок. Типичной для этого ситуацией является наличие запросов на чтение, параллельных загрузке, когда нежелательно их блокировать. Даже если для предотвращения эскалации блокировок используются другие методы (например, используется ALTER TABLE), ненулевой BATCHSIZE будет необходим для того, чтобы предотвратить утилизацию слишком большого объёма памяти, которая используется блокировками при большой нагрузке.

Рисунок 3: Зависимость минимального протоколирования от размера пакета в BCP

Вторым параметром является ROWS_PER_BATCH, он задаёт приблизительное число строк во всём пакете операции массовой загрузки данных. Этот параметр имеет смысл, когда параметр BATCHSIZE не задан. Кроме того, если в массовой загрузке используется BATCHSIZE больше нуля, параметру ROW_PER_BATCH нельзя присваивать значения, необходимо оставить его пустым или нулевым.
С помощью ROWS_PER_BATCH можно подсказать оптимизатору запросов объём загрузки, а тот, в свою очередь, постарается оптимизировать ход выполнения массовой операции. Если известно примерное число строк, оптимизатор сможет лучше распределить ресурсы и необходимый объем памяти для обслуживания пакета.
Поскольку информация о параметрах BATCHSIZE и ROWS_PER_BATCH может вас запутать, так как у этих параметров в разных инструментах массовой загрузки разные имена, мы свели эти два разных метода в одну таблицу, которая показывает способы их использования:

Метод массовой операции	BATCHSIZE	ROWS_PER_BATCH
Integration Services Назначение OLE DB	См. “Размеры пакетов в Integration Services”	Rows per Batch
Integration Services Назначение SQL Server	См. “Размеры пакетов в Integration Services”	Не применимо
BCP	-b <X> (1)	-h “ROWS_PER_BATCH = <X>”
BULK INSERT	BATCHSIZE = X	ROWS_PER_BATCH = <X>
INSERT…SELECT (2)	N/A	ROWS_PER_BATCH (3)

Таблица 3: BATCHSIZE и ROWS_PER_BATCH для разных массовых методов

(1) Если BATCH_SIZE не задан, BCP будет использовать в качестве значения по умолчанию 1000.
(2) Для INSERT…SELECT нельзя управлять параметрами BATCHSIZE и ROWS_PER_BATCH.
(3) В качестве источника нужно использовать OPENROWSET(BULK…).

Размеры пакетов в Integration Services

В Integration Services размеры пакетов используются по-другому по сравнению с методами массовой загрузки.
По умолчанию Integration Services создаёт один пакет на конвейерный буфер. После того, как буфер сбрасывается на диск, пакет (имеется в виду пакет вставки, а не пакет SSIS) фиксируется. Можно изменить такое поведение, задав значение параметру “Maximum Insert Commit Size” (Максимальный фиксируемый размер вставок) для получателя данных.
В представленной ниже таблице показаны возможные варианты использования “Maximum Insert Commit Size” (MICS).

Максимальный фиксируемый размер вставок	Эффект
MICS > размер буфера	Такая установка игнорируются. Одна фиксация выполняется для каждого буфера.
MICS = 0	Весь пакет фиксируется целиком, как это было бы при BATCHSIZE = 0.
MICS < размер буфера	Фиксация будет происходить каждый раз при посылке указанного в MICS числа строк. Кроме того, фиксация будет выполняться и после обработки каждого буфера.

Таблица 4: Максимальный фиксируемый размер вставок и его особенности

Обратите внимание, что единственным способом получения пакетов большего размера, чем размер буфера состоит в том, чтобы изменить размер буфера в свойствах потока данных.

Сжатие данных и массовая загрузка

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

Рисунок 4: BULK INSERT и компрессия данных

Красная линия графика показывает размер таблицы, а синие полосы показывают затраченное время.

Другие минимально протоколируемые операции и операции над метаданными

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

DROP TABLE

Операция уничтожения таблицы выполняется только над метаданными. Такое удаление таблицы позволяет очень быстро удалить большое количество строк, без затрат на протоколирование, как в случае инструкции DELETE.

MERGE

Команда MERGE является новой инструкцией Transact-SQL, и тоже позволяет вставлять строки с минимальным протоколированием. Это справедливо, когда вставка посредством MERGE направлена в кучу, в других случаях для минимального протоколирования вставок необходимо активировать флаг трассировки 610.

TRUNCATE TABLE

Точно так же как DROP TABLE, инструкция TRUNCATE TABLE является операцией, выполняемой только над метаданными. Следует помнить, что относительно TRUNCATE TABLE действуют некоторые ограничения. Вот они:

Вы не сможете выполнить усечение таблицы, на которую ссылается ограничение внешнего ключа.
Вы можете выполнить усечение строк только всей таблицы, но не для одной секции (но есть другие возможности удаления секции, о которых можно узнать из этойглавы: “Удаление всех строк из секции или таблицы”).

CREATE INDEX, DROP INDEX и REBUILD INDEX

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

Partition SWITCH (переключение секции)

В SQL Server 2005 появилось секционирование таблиц и переключение секций. Переключение секций физически не перемещает данные; эта операция выполняется только над метаданными, что позволяет загружать параллельно много однотипных потоков данных в несколько промежуточных таблиц. После того как загрузка будет закончена, можно выполнить команду SWITCH для создания одной большой таблицы.
Изменения значений строк в таблице в режиме минимального протоколирования невозможны, как и удаления из хранимого подмножества строк. Однако, как будет показано в главе “Решения для типовых задач массовой загрузки”, команда SWITCH может использоваться для эмуляции поведения минимально протоколируемых инструкций.
Создание промежуточной таблицы, которая будет переключена взамен секции главной таблицы, может оказаться довольно сложной задачей. Для того чтобы использовать SWITCH, необходимо, чтобы метаданные и распределение страниц промежуточной таблицы были точно такими же, как и у основной таблицы. Для этого нужно следовать представленному ниже алгоритму, который позволит выполнять манипуляции над метаданными согласовано:

Создание точной копии схемы главной таблицы.
Наиболее очевидным способом добиться этого результата является использование инструкции CREATE TABLE, которая должна соответствовать исходной схеме таблицы.
Также можно использовать показанный ниже простой метод, позволяющий быстро создать идентичную по схеме таблицу:
SELECT TOP(0) * INTO <TargetTable> FROM <SourceTable>
Однако при таком подходе новая таблица будет создана в файловой группе по умолчанию. Переместить таблицу в нужную файловую группу можно создав для неё кластеризованный индекс в нужном месте и удалив его.
CREATE CLUSTERED INDEX IX_temp ON <<argetTable> (
) ON []
DROP INDEX <TargetTable>.IX_Temp
Соответствие индексов главной и промежуточной таблиц.
После копирования схемы исходной таблицы, нужно добиться соответствия всех индексов промежуточной и главной таблиц. Согласованными должны быть следующие параметры индексов:
- Настройки сжатия
- Привязка к файловой группе
Создание ограничений для промежуточной таблицы.
После приведения в порядок схемы столбцов и индексов таблиц, нужно создать необходимые ограничения для промежуточной таблицы. Эти ограничения должны соответствовать требованиям функции секционирования промежуточной таблицы. Тут нужно быть очень внимательным, особенно к граничным значениям.
Для первой секции таблицы (с самыми маленькими значениями), используйте следующий шаблон:
ALTER TABLE <TargetTable> ADD CONSTRAINT CK_<X> CHECK (<PartCol> < <LeftValue>)
Для секций между первой и последней будет следующий формат ограничения:
ALTER TABLE <argetTable> ADD CONSTRAINT CK_<X> CHECK (<PartCol> IS NOT NULL AND <PartCol> >= <LeftValue> AND <PartCol> < <RightValue>)
Обратите внимание на ограничение NOT NULL. Значения NULL должны всегда попадать в первую секцию, потому что значения NULL не сравнимы друг с другом, нужно явно указывать это ограничение, что позволит исключить попадание в секции неопределённых значений.
Наконец, для последней секции нужно использовать следующий шаблон.
ALTER TABLE <TargetTable> ADD CONSTRAINT CK_<X> CHECK (<PartCol> IS NOT NULL AND <PartCol> >= <LeftValue>)
Все представленные выше шаблоны предполагают, что функция секционирования использует RANGE LEFT.
Исполнение инструкции SWITCH.
После всех необходимых подготовительных действий, можно исполнить команду SWITCH:
ALTER TABLE <SourceTable> SWITCH PARTITION <X> TO <TargetTable>
Для того чтобы облегчить понимание всех тонкостей управления секциями, воспользуйтесь бесплатным инструментом, который доступен для скачивания на сайте CodePlex: SQL Server Partition Management

Решения для типовых задач массовой загрузки

В этой главе будут рассмотрены некоторые наиболее типичные сценарии загрузки данных и представлены шаблоны для их реализации. В примерах будет использоваться отвлечённая таблица фактов Sales. Будут рассмотрены следующие сценарии:

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

Массовая загрузка в пустую, несекционированную таблицу

Поскольку загрузка данных в несекционированную таблицу является довольно простой операцией, оптимизирована она может быть несколькими способами.
Перед тем, как начать загрузку данных в пустую таблицу, считается хорошей практикой отключать у этой таблицы все индексы. После загрузки индексы нужно пересоздать.
Если исходные данные извлекаются из таблицы в SQL Server, и для этого используются SELECT INTO или INSERT…SELECT, загрузка пройдёт быстро и просто. Если исходные данные не хранятся в SQL Server, можно использовать то, что подойдёт лучше: BCP, BULKINSERT, INSERT…SELECT или Integration Services.
Выполнить много параллельных операций вставки в кучу можно в том случае, когда выбран такой сценарий массовой загрузки, который приведёт к выполнению операции c установкой на таблице блокировки массового обновления (BU). BU – блокировки совместимы, и следовательно две массовые операции могут выполняться одновременно.
Если ваша дисковая подсистема достаточно быстра, есть смысл рассмотреть возможность параллельного исполнения нескольких операций BULK INSERT. Одиночная массовая операция будет утилизировать ресурсы только одного процессорного ядра. Как мы увидим и в других сценариях, использование нескольких параллельных потоков массовых операций является ключом к масштабированию массовой загрузки данных.
В этом сценарии будет нецелесообразно использование инструкций INSERT…SELECT и SELECT INTO. Обе они приведут к исключительной (X) блокировке на уровне всей таблицы, куда идёт загрузка. А это означает, что единовременно может выполняться только одна операция массовой загрузки, что является ограничением для масштабируемости. Однако, такие средства, как: BCP, BULK INSERT и Integration Services способны обойтись BU – блокировкой, если указать оптимизатору подсказку TABLOCK.
Исполняется несколько операций массовой загрузки, типа:

BULKINSERT Sales FROM'Sales<X>.csv' WITH ( FIELDTERMINATOR=';' ,ROWTERMINATOR='\n' , TABLOCK )

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

“ETL World Record“, показали следующую зависимость:

Рисунок 5: Массовая вставка в кучу (одна таблица) и её кривая масштабируемости

16 параллельных массовых операций масштабируются практически линейно, правда, всё это зависит от размера данных. После этого, проявляется особенность работы в SQL Server распределения внутренних структур данных, что ограничивает производительность вставки. Наличие подобного узкого места сигнализируется сервером в виде появления в sys.dm_os_latch_stats ожидания ALLOC_FREESPACE_CACHE. Обратите внимание, что представленные на графике значения меняются в зависимости от размера вставляемых строк.
Если необходимо добиться линейного масштабирования вышеуказанного ограничения, то более высокой производительности можно добиться с помощью секционирования таблицы. Используя секционирование можно загружать несколько таблиц одновременно и потом с помощью переключения секций объединять данные, как это показано в главе “Массовая загрузка в секционированную таблицу”.

Вставка упорядоченных данных

Это особый случай, который нуждается в отдельном рассмотрении. Если у получающей данные таблицы имеется кластеризованный индекс, и исходные данные упорядочены точно так же, как задано ключом кластеризованного индекса, можно задействовать подсказку ORDER и не отключать кластеризованный индекс у таблицы на время массовой загрузки. Использование подсказки ORDER может значительно ускорить массовую загрузку, потому что это приводит к устранению в плане исполнения загрузки промежуточного шага сортировки по ключу кластеризованного индекса. Однако так поступать следует только в том случае, если данные поступают из источника уже упорядоченными; предварительная же сортировка перед вставкой не приведёт к увеличению скорости загрузки.
Если вначале таблица пуста, операция будет протоколироваться минимально. Однако если в ней уже есть данные, и, если не включен флаг трассировки 610, операция будет протоколироваться полностью.
Подсказки ORDER и TABLOCK можно указывать одновременно. Это не позволит одновременно направлять несколько потоков в одну таблицу (накладывается блокировка стабильности схемы Sch-M). Однако это будет наиболее быстрым решением для одного потока, сопоставимое по скорости с однопоточной массовой загрузкой в кучу, и при этом устранятся необходимость в дополнительном после загрузки шаге CREATE INDEX (использующего дорогую операцию сортировки).
Для одного потока, представленный ниже сценарий операции массовой вставки был близок по производительности и при вставке в кучу, и при вставке в кластеризованный индекс с ключом по OrderDate.

BULK INSERT Sales FROM 'C:\temp\Sales200401' WITH ( FIELDTERMINATOR = ';' , ROWTERMINATOR = '\n' , TABLOCK , ORDER(OrderDate) )

Высокая производительность получалась при вставке в кластеризованный индекс и кучу. Обратите внимание, что, если не включить флаг трассировки 610, то такая операция будет минимально протоколирована только в том случае, когда получающая данные таблица будет пуста, и используется параметр BATCHSIZE = 0. Для случая с ненулевым BATCHSIZE, минимально протоколироваться будет только первый пакет (первая фиксация).
Вставка в кластеризованный индекс отсортированных входных данных приводит к снижению фрагментации. Однако сортировка данных может потребовать большой объём памяти. Если нужно снизить фрагментацию и одновременно снизить утилизацию памяти сортировкой, можно воспользоваться следующей уловкой:

Создайте временную таблицу с такой же схемой как у таблицы, получающей данные.
Убедитесь, что у временной таблицы создан такой же кластеризованный индекс, как у получающей таблицы.
Выполните массовую загрузку данных в промежуточную таблицу. Если это окажется полезно для производительности, включите флаг трассировки 610.
- Данные массовой загрузки окажутся в промежуточной таблице, они уже будут отсортированы, но могут оказаться фрагментированными.
Используйте метод массовой загрузки INSERT…SELECT для перемещения данных из промежуточной таблицы в таблицу, куда изначально нужно было загрузить данные.
- Поскольку промежуточная таблица уже отсортирована, данные будут вставляться в таблицу назначения в отсортированном порядке.
- Этот подход обеспечит минимальную фрагментацию вставляемых данных в таблице назначения.

Описанная уловка потребует больше операций ввода-вывода, чем загрузка данных напрямую в таблицу назначения. Однако минимизация фрагментации может повысить скорость чтения для больших рабочих нагрузок при просмотре всей таблицы или диапазонов ключей.
Обратите внимание, что если при массовой загрузке в кластеризованный индекс используются маленькие пакеты, которые потенциально могут поместиться в памяти, SQL Server оценивает необходимый для сортировки объём памяти, предполагая, что в среднем столбцы переменной длины будут заполнены наполовину. Если для какого-либо пакета размер данных в памяти окажется большим, чем эта оценка, сортировка будет сопровождаться сбросом страниц на диск (протечкой), что серьёзно ухудшит производительность. Вы можете обнаружить события протечки сортировки (sort spill) используя SQL Server Profiler, отслеживая класс событий “Sort Warning”.
Если, вместо половинной наполненности столбцы varchar имеют тенденцию к полному заполнению, можно помочь SQL Server предотвратить протечку сортировки, используя следующий обходной манёвр:

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

Настройка Назначения OLE DB для вставки упорядоченных данных в Integration Services

Несмотря на то, что средствами графического пользовательского интерфейса настройка сортировки входного потока данных делается не совсем очевидным способом, такая возможность для Назначения OLE DB существует и настроить её в Integration Services можно в расширенных свойствах компонента.

Рисунок 6: Настройка сортировки входного потока для получателя OLE DB

Достаточно добавить в свойстве FastLoadOptions подсказку ORDER, как это показано на рисунке выше.

Массовая загрузка в несекционированную таблицу, в которой уже имеются данные

Если таблица, в которую выполняется загрузка, не содержит индексов, нужно руководствоваться советами из предыдущей главы. Напомним, что в уже имеющуюся таблицу не стоит загружать данные методом SELECT INTO.
Когда загрузка выполняется в таблицу, которая уже имеет индексы, это повод для принятия особых мер. Сначала, прочтите статью SQL Server Books Online: “Рекомендации по оптимизации массового импорта данных“.
В этой статье представлены рекомендации по отключению индексов перед массовой загрузкой данных в таблицу. Однако стоит помнить, что рекомендации в SQL Server Books Online применимы только для однопоточных операций массовой загрузки. По мере увеличения параллелизма и числа ядер, удаление индексов, одновременная загрузка в нескольких потоках, а затем восстановление индексов может выполниться быстрее, чем вставка данных в существующие индексы. Также, стоит помнить, что массовая загрузка в кластеризованный индекс будет протоколироваться минимально, только если таблица пуста или если включён флаг трассировки 610. Это означает, что протоколирование нескольких параллельных массовых загрузок не будет минимальным.
Стоит протестировать оба сценария: удаление/создание индексов и загрузка данных в таблицу с индексами. Если выбор падёт на параллельную загрузку всей таблицы, руководствуйтесь советами предыдущей главы.

Массовая загрузка при наличии индексов

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

ИНДЕКСИРОВАННЫЕ ТАБЛИЦЫ НЕ ПОДДЕРЖИВАЮТ БЛОКИРОВКИ BULK UPDATE (BU)

Таблицы с индексами не поддерживают BU-блокировку, вместо неё будет исключительная блокировка (X-блокировка). X-блокировки, в отличие от BU-блокировок, не совместимы с другими блокировками такого же типа. Массовая загрузка в таблицу с индексами потребует наложения X-блокировки уровня строки. В принципе, нет запретов на использование подсказки TABLOCK для выполнения массовой загрузки в индексированную таблицу, но это приведёт к тому, что X-блокировки поднимутся до уровня таблицы, что заблокирует другие потоки массовой загрузки в ту же самую таблицу. Для обеспечения параллелизма массовой загрузки в индексированную таблицу, не стоит использовать подсказку TABLOCK.

ОПРЕДЕЛИТЕ ОПТИМАЛЬНОЕ ЗНАЧЕНИЕ ДЛЯ BATCHSIZE

Эмпирическим путём подберите такое значение BATCHSIZE, при котором производительность массовых операций будет наивысшей. Нулевой размер BATCHSIZE полезен для вставки в кучу без индексов, и неприменим для вставки в таблицы, у которых индексы имеются. Если вставляемые данные предварительно не отсортированы (и указана подсказка ORDER), оптимизатор выполнит операцию сортировки вставляемых данных перед тем, как вставить данные пакета. Если такую сортировку не получается выполнить в памяти, будет выполнена промежуточная материализация сортируемых данных в tempdb, операции ввода-вывода в которой приведут к замедлению скорости вставки.

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

ИЗБЕГАЙТЕ УКРУПНЕНИЯ БЛОКИРОВОК

Укрупнение блокировок до уровня таблицы может начаться, если число X-блокировок уровня строки превысит пороговое значение. Если такое случается, параллелизм деградирует до однопоточной вставки. Методика борьбы с подобным сценарием укрупнения блокировок зависит от версии SQL Server:

Для SQL Server 2005, укрупнение блокировок происходит при достижении установки 5000 блокировок. Для борьбы с этим применяют такие способы:
- Установка настолько маленького BATCHSIZE, чтобы не происходило укрупнение блокировок.
- Другой способ радикальный, можно использовать флаг трассировки 1211, который полностью отключает укрупнение блокировок для всего сервера.
Для SQL Server 2008, также возможно полностью отключить укрупнение блокировок до уровня таблицы.

ИСПОЛЬЗОВАНИЕ НЕПЕРЕСЕКАЮЩИХСЯ ВХОДНЫХ ПОТОКОВ

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

ИСПОЛЬЗОВАНИЕ ФЛАГА ТРАССИРОВКИ 610

Если включить флаг трассировки 610, вставка в таблицу с индексами может стать минимально протоколируемой операцией. Таблицы с кластеризованными индексами поддерживают одновременную массовую загрузку в несколько потоков вставки, пока эти потоки содержат непересекающиеся данные. Если будет обнаружено пересечение данных, потоки будут блокировать друг друга (но это не будут взаимоблокировки).
Ниже показан пример, где на основе таблицы Sales в несколько шагов проиллюстрировано, как можно добиться максимума производительности при многопоточном исполнении BULK INSERT в таблицу с кластеризованным индексом:

Отключаем для таблицы эскалацию блокировок. ALTER TABLE Sales SET (LOCK_ESCALATION = DISABLE) В SQL Server 2005 можно отключить эскалацию блокировок,присвоив BATCHSIZE значение 5000. Иначе, этого можно было достичь включением флага трассировки 1211 (который полностью отключает эскалацию блокировок на сервере). Дляполучения более подробной информации о флаге трассировки 1211, см. Lock Escalation in SQL2005.

Запустите несколько команд BULK INSERT с непересекающимися диапазонами.

Секционирование данных на непересекающиеся диапазоны по столбцу Date:BULKINSERT Sales FROM'200101.csv' WITH ( FIELDTERMINATOR=';' ,ROWTERMINATOR='\n' ) BULKINSERT Sales FROM'200102.csv' WITH ( FIELDTERMINATOR=';' ,ROWTERMINATOR='\n' )… и т.д. …

Не используйте подсказку TABLOCK для отдельных потоков.

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

Проверка блокировок и параллелизма.
Вы должны наблюдать несколько незаблокированных потоков массовой загрузки. Каждый из этих потоков должен удерживать монопольную X-блокировку уровня строки. Для проверкиможно использовать запрос, подобный показанному ниже:SELECT lck.resource_type, lck.request_mode , lck.request_status , lck.request_session_id , COUNT(*) number_locks FROM sys.dm_tran_locks lck INNER JOIN sys.partitions par ;ON lck.resource_associated_entity_id = par.hobt_id INNER JOIN sys.objects obj ;ON par.object_id = obj.object_id WHERE obj.name = 'Sales' GROUP BY lck.resource_type, lck.request_mode , lck.request_status, lck.request_session_idОн должен возвращать нечто подобное:

Рисунок 7: Листинг блокировок, удерживаемых операциями массовой загрузки
Вы можете видеть X-блокировки строк (представленные значением KEY в колонке resource_type) и блокировки с намерением монопольного доступа (IX) на только что распределенных страницах данных.

Массовая загрузка в секционированную таблицу

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

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

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

После того, как секция изъята, она ведет себя точно так же, как обычная таблица, и можно применять все рекомендации из предыдущих глав. Однако на практике не следует забывать добавлять ограничения, согласующие добавляемые секции (и включить CHECK CONSTRAINTS в опциях загрузки) перед началом операции массовой загрузки. Можно добавить проверочное ограничение и после загрузки, но это потребует исполнения полного просмотра таблицы. Если же не добавить ограничение, не получится переключить секцию обратно в таблицу.
Однако есть особый случай, который нужно учитывать. Если планируется одновременная вставка данных в несколько секций, возможна дальнейшая оптимизация этого процесса. В таком случае, нужно переключить каждую секцию в отдельную таблицу. С каждой из этих таблиц можно параллельно исполнять несколько операций массовой загрузки. Этот метод проиллюстрирован ниже:

Создание временных таблиц для каскадирования

Создаются временные таблицы, в которые будут переключаться секции, как это было описано выше, в главе “Partition SWITCH (переключениесекции)”.
Переключение данных во временные таблицы:ALTER TABLE Sales_P SWITCH PARTITION<X> TO Sales_200<X>

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

BULK INSERT Sales_200 FROM'Sales200.csv' WITH ( FIELDTERMINATOR=';' ,ROWTERMINATOR='\n' )…и т.д.…
Переключите промежуточных таблиц назад в главную таблицу.

Возврат промежуточных таблиц назад, в виде секций:ALTER TABLE Sales_200<X> SWITCH TO Sales_P PARTITION <X>
Очистка промежуточных таблиц.

Удаление временных таблиц, используемых для распараллеливания загрузки::DROP TABLE Sales_200<X>
Выбор окончательного сценария загрузки.Представленная ниже блок-схема поможет сделать правильный выбор сценария массовой загрузки.

Рисунок 8: Блок-схема решений для выбора методов массовой загрузки.

Удаление всех строк из секции или таблицы

Этот сценарий, несмотря на его простоту, часто упускают из вида. Самым быстрым способом удаления всех данных из таблицы, будет выполнение инструкции:

TRUNCATE TABLE Sales

Удаление данных из секции немного сложнее:

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

Создаём временную таблицу, в которую будем переключать секцию, используя для этого описанный в главе “Partition SWITCH (переключениесекции)” метод.

Если исходная таблица имеет индексы, нужно воссоздать индексы и для временной таблицы.

Выполняем инструкцию SWITCH для той секции, данные которой нужно перенести во временную таблицу.
ALTERTABLE Sales_P SWITCH PARTITION 1 TO Sales_Temp
Исходная секция после этого опустеет.

DROP или TRUNCATE для временной таблицы.

Если временная таблица больше не нужна, удаляем её:DROP TABLE Sales_Temp

Иначе, можно очистить таблицу, после чего её можно будет использовать повторно, например, для удаления других секций:

TRUNCATE TABLE Sales_Temp

Удаление большого числа строк из секции или таблицы

До сих пор мы рассматривали только способы вставки и удаления данных с минимальным протоколированием и/или с массовой загрузкой.
Бывают ситуации, когда нежелательно использовать инструкцию TRUNCATE для всех секций, но нужно удалить большое число строк из этой таблицы. Инструкция DELETE протоколируется полностью. Однако, существует возможность получить эффект, который похож на DELETE с массовой операцией. Эта возможность представлена ниже, в виде

пошагового алгоритма.

Создайте копию основной таблицы.

Нужно создать временную таблицу, которая будет использоваться для переключения секции. Требования к этой таблице аналогичны тем, которыеуже были описаны выше, в главе “Partition SWITCH (переключение секции)”.

Массовая загрузка строк в промежуточное хранилище секции.

Чтобы заработала массовая вставка, можно использовать один из двух методов. Метод INSERT…SELECT:INSERT INTO Sales_Temp WITH (TABLOCK) SELECT*FROM Sales WHERE OrderDate >='20010101' AND OrderDate <'20020101' AND <Keep Criteria>Второй способ заключается в использовании возможностей Integration Services, позволяющих создать несколько параллельных, потоков в таблицу Sales_Temp.

Переключение секции и усечение старых данных

Если нужна транзакционность операции, запустите транзакцию с помощью:BEGIN TRAN

Чтобы удалить ненужные строки из главной таблицы, используйте методику, описанную в предыдущей главе: “Удаление всех строк из секции или таблицы”.

Воссоздание индексов и использование SWITCH.

Восстановить все индексы и ограничения таблицы Sales_Temp, чтобы они соответствовали тем, что присутствуют в главной таблице. Инструкция повыполнению операций над секциями была представлена выше, в главе “Partition SWITCH (переключение секции)”. Она поможет с задачей переключения таблицы Sales_Temp с пустой

секцией:

ALTER TABLE Sales_Temp TO Sales SWITCH PARTITION 1

Удалите временную таблицу.
Удалите временную таблицу:DROP TABLE Sales_Temp
Если Вы использовали транзакцию, самое время её завершить:
COMMIT TRAN

Обновление большой части данных секции или таблицы

До сих пор мы рассматривали способы вставки и, до некоторой степени, удаления данных в режиме минимального протоколирования.
Ни DELETE, ни UPDATE не могут протоколироваться минимально для операций над строками таблицы или секции. Однако, иногда можно создать такие сценарии с инструкциями UPDATE и DELETE, что позволит задействовать массовые операции.
Допустим, нужно удалить и/или изменить большое число строк таблицы или секции. Объём изменений оценивается так, что общее число строк, измененных этой операцией превышает 10 – 20 % от полного объёма таблицы. В этом случае, может быть полезна представленная ниже методика.

Создайте во временной таблице журнал изменений.

Создайте временную таблицу, которая будет содержать строки, представляющие собой изменённые данные в существующей секции главной таблицы. Подобным

образом создавалась таблица, в главе “Partition SWITCH (переключение секции)”.
Выполните массовую загрузку изменяемых строк в таблицу Sales_Delta. Впишите новые значения, которые должны быть после изменений.

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

Создайте временную таблицу, в которую будут переключены данные изменяемой секции. Для этого можно воспользоваться методом, который был описан выше, в главе “Partition SWITCH (переключение секции)”.
Переключить секцию, данные которой будут изменяться, можно так:

ALTER TABLE Sales_P SWITCH PARTITION 1 TO Sales_Old

После этого у вас будут две таблицы: Sales_Delta и Sales_Old.

Если таблица не секционирована, пропустите этот шаг и используйте далее основную таблицу вместо таблицы Sales_New, в качестве одного из источников следующего шага.

Слияние (merge) журнала изменений и изначальной таблицы, и массовая загрузка во временную таблицу.

Выполнение слияния:

Создайте временную таблицу, куда будет выполнено переключение секции, как это было описано выше, в главе “Partition SWITCH (переключение секции)”.
Выполните слияние старых записей в Sales_Old и разницы из Sales_Delta. Это легко реализуется с помощью Integration Services, если использовать такие компоненты, как merge или lookup. Иначе, слияние можно выполнить с помощью инструкции Transact-SQL, подобной этой:

SELECT o.PrimaryKey , COALESCE(d.Col1, o.Col1) , COALESCE(d.Col2, o.Col3)

…и т.д….

FROM Sales_Old o LEFT JOIN Sales_Delta d

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

Удаление журнала изменений и первоначальных данных.

Если необходимо выполнить изменение в транзакции, тогда этот шаг нужно начинать с инструкции:

BEGIN TRAN

Удалите промежуточную таблицу журнала изменений, созданную на шаге 1:

DROP TABLE Sales_Delta

Удалите промежуточную таблицу с оригинальными данными, которая появилась на шаге 2:

DROP TABLE Sales_Old

Если таблица не секционирована, выполните усечение Sales вместо удаления таблицы Sales_Old.

Переключение (SWITCH) результатов слияния

Использовать команду SWITCH, чтобы переместить новую версию данных таблицы взамен прежних данных:

ALTER TABLE Sales_New SWITCH TO Sales_P PARTITION 1

Удалите таблицу Sales_New, чтобы избавиться от промежуточных результатов:

DROP TABLE Sales_New

Если для завершающих шагов использовалась транзакция, её нужно завершить командой:

COMMIT TRAN

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

Массовая загрузка, запросы с NOLOCK и Read Committed Snapshot Isolation

Очень часто, когда нужно читать данные из таблицы, которая потенциально подвержена множественным блокировкам, запрос исполняется с указанием подсказки оптимизатору NOLOCK. Такое чтение известно как “грязное чтение”, а сами запросы с NOLOCK выполняются с уровнем изоляции Read Uncommitted и чреваты риском получения неполных или противоречивых результирующих наборов. Однако, поскольку с этой подсказкой не используется совмещаемая блокировка, такие запросы не будут заблокированы операциями записи.
Read committed snapshot isolation (RCSI) появился в SQL Server 2005 в качестве новой возможности чтения данных и наложение на это время блокировок, не мешающих работать другим процессам, в то время, как существуют другие запросы, изменяющие данные в той же таблице. Это хорошая альтернатива NOLOCK, потому что при использовании такого уровня изоляции гарантируется целостное, непротиворечивое с точки зрения транзакций представление данных, для которого не нужно использовать специальные подсказки оптимизатору. Изначально RCSI предназначался для характерных OLTP нагрузок, но этот уровень изоляции может стать мощным подспорьем в нагрузках, свойственных информационным хранилищам или для сценариев, в которых велика доля операций массовой вставки.
RCSI включается для базы данных. При включении этого уровня изоляции, запросы на чтение не накладывают разделяемую блокировку на строки, страницы или таблицу, и поэтому они не блокируются эксклюзивными X-блокировками или BU-блокировками. Вместо блокировок, новые или изменённые в таблице строки получают 17-байтный идентификатор версии, и ещё до того, как образ таких строк будет изменён в рамках транзакции (изменения или удаления) они будут скопированы в системную базу данных tempdb, которая в этом случае исполняет роль хранилища версий строк для всего экземпляра SQL Server. Когда исполняется запрос на чтение, извлекаются только те строки, которые существовали с самого начала исполнения запроса, при этом, игнорируя позднейшие номера версии строк, а для изменений извлекаются ссылки в tempdb для соответствующих предыдущих версий строк.
Транзакционная семантика для RCSI идентична обычному уровню изоляции Read Committed, с той разницей, что запросы на чтение всегда возвращают данные на момент начала запроса, вместо того, чтобы блокироваться до окончания конкурирующих запросов на запись. В результате этого, те приложения, которые требуют, чтобы не было никаких изменений во время выполнения запросов с уровнем изоляции RCSI, сохраняют точно такое же поведение, как и в предыдущих версиях. Транзакции, которые устанавливают уровень изоляции REPEATABLE READ, или когда инструкция SELECT выполняется с HOLDLOCK, будут по-прежнему запрашивать блокировки и потенциально подпадать под блокировки одновременных запросов на запись. Кроме того, не стоит путать RCSI с другим новшеством SQL Server 2005, речь идёт об ещё одном уровне изоляции: Snapshot Isolation (уровень изоляции моментального снимка). Этот уровень изоляции тоже использует версионность строк.
Для тех баз данных, которые подвержены большому числу операций UPDATE или DELETE, уровень изоляции RCSI может породить дополнительную нагрузку и повысить требования к пропускной способности tempdb. Однако, при изоляции RCSI, операции INSERT (включая BULK INSERT), не будут создавать нагрузки на tempdb и не станут приводить к дополнительным затратам ресурсов кроме добавления 17 байтов к каждой строке (обратите внимание на то, что в довершение ко всему эти 17 байт несжимаемы).
Хотя при RCSI чтение не страдает от X-блокировок, существует два случая при массовой загрузке данных, при которых в SQL Server 2008 запросы с уровнем RCSI будут блокироваться (так же как запросы с NOLOCK):

- При загрузке кучи с подсказкой TABLOCK, когда используются операции BULK INSERT, INSERT…SELECT или другие операции массовой загрузки. Это происходит, потомучто при загрузке накладывается BULK OPERATION блокировка с намерением монопольного доступа (IX-блокировка), а чтение с NOLOCK или RCSI накладывают разделяемую BULKOPERATION блокировку (S-блокировка). Для массовой загрузки в кучу с параллельными операциями чтения, несмотря на использование RCSI или NOLOCK, нужно убрать подсказкуTABLOCK, что приведёт к отказу от возможности минимального протоколирования этих операций.
- В начале загрузки в пустую таблицу с кластеризованным индексом, с подсказкой TABLOCK, и с использованием операций BULK INSERT, INSERT…SELECT или другихопераций массовой загрузки. Это происходит, потому что загрузка выполняется с блокировкой стабильности схемы (Sch-M блокировка), а чтение выполняется с блокировкойизменения схемы (Sch-S блокировка). В качестве альтернативы можно включить флаг трассировки 610, что обеспечит минимальное протоколирование и отказ от использованияподсказки TABLOCK.

Кроме этих исключений при уровне изоляции RCSI операции BULK INSERT (включая INSERT…SELECT) могут использоваться с подсказкой TABLOCK, или они могут использовать нормальные правила эскалации блокировок, и во всех этих случаях чтение не будет блокироваться. Производительность будет аналогична исполнению запросов с подсказкой NOLOCK, с тем преимуществом, что в отличие от способа с NOLOCK, чтение будет оперировать транзакционно целостным представлением данных таблицы. Кроме того, даже если одновременно будет существовать множество продолжительных запросов на чтение данных таблицы, вставка новых данных с помощью операции INSERT не будет блокироваться чтением. Чтобы включить для базы данных уровень изоляции RCSI, нужно выполнить представленную ниже команду, которая должна выполняться в отсутствие других подключений к изменяемой базе данных.

ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON

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

SELECT name, is_read_committed_snapshot_on FROM sys.databases WHERE name = '<dbname>'

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

Оптимизация массовой загрузки данных

В этой главе мы углубимся в те методы, которые обычно используются для оптимизации процесса массовой загрузки. SQL Server способен обеспечивать очень высокую производительность массовой загрузки. Но для этого и все остальные ресурсы должны обеспечивать должную поддержку и производительность.
Существует несколько направлений оптимизации массовой загрузки. Прежде всего, это параллелизм, который является ключевым фактором оптимизации для массовой загрузки, и который позволяет линейно масштабировать процесс по числу процессорных ядер.
Во время поиска решений по повышению производительности, статистика ожиданий и счетчики производительности являются основными источниками информации, необходимой для анализа первопричин возможных проблем. Наиболее критичные параметры производительности рассмотрены ниже, в главах: “Отслеживаемые типы ожиданий” и “Отслеживаемые счетчики производительности”.
Некоторые моменты настройки производительности и устранения критических проблем требуют более глубокого вмешательства. К таким моментам относится глава “Отслеживаемые счётчики производительности” с углублённым описанием оптимизации сетевого взаимодействия, главы: “Конкуренция не планировщике”, “Конкуренция PFS”. Последним направлением, но не последним по значимости, мы рассмотрим небольшой набор рекомендаций в главе: “Оптимизация ввода-вывода и размещения файлов”.

Параллелизм и секционирование загрузки

Основным фактором оптимизации производительности во время массовых операций является параллелизм. Лучше всего параллелизм достигается выполнением нескольких команд массовой загрузки в одно и то же время. Поскольку каждая массовая операция выполняется в собственном потоке, понадобится выполнить несколько таких операций, чтобы загрузить все процессорные ядра. Каждая команда массовой операции потенциально может утилизировать свой процессор на 100%. Следовательно, оптимальная производительность при загрузке всех ядер на 100% может быть достигнута в том случае, когда число параллельно выполняемых команд массовых операций будет таким же, как общее число процессорных ядер, выделенных в вашей системе для массовых операций.
При проектировании параллельных операций, также нужно учитывать то, какое влияние на процесс оказывает очерёдность исполнения операций, когда одна операция заканчивается перед началом другой. В идеале, получаемые данные должны секционироваться на части равного размера. Это будет гарантией того, что все параллельные задачи завершатся приблизительно в одно и то же время.
На иллюстрации ниже показано, как перекос при распределении размера секций может привести к увеличению времени исполнения.

Рисунок 9: Балансировка размера секций

Отслеживаемые типы ожиданий

Когда нужно повысить производительность массовых операций, вначале нужно проанализировать данные из системного административного представления sys.dm_os_wait_stats. Оно поможет определить, блокируются ли какие-либо процессорные ресурсы. Сначала внимание стоит сфокусировать на значениях с наиболее продолжительным временем ожидания и постепенно оптимизировать наиболее “узкие” места. По ссылке Вы найдёте сценарий с сайта TechNet, который может оказаться полезным для этих целей: Retrieve Waitstat Snapshots.
Также, будет полезен сбор данных из sys.dm_os_latch_stats, особенно если планируется реализовать много потоков массовой загрузки в одну таблицу.
Ниже представлена таблица, в которой перечислены некоторые наиболее распространённые при массовой загрузке типы ожиданий, указаны их возможные причины, и те действия, которое необходимо предпринять для исследования и устранения проблемы.

Тип ожидания	Типичная причина появления	Определение/устранение
LCK_<X>	Один процесс блокирует другой	Вы используете непересекающиеся потоки загрузки? Убедитесь в корректности использования TABLOCK? Найдите основную причину блокировок.
PAGEIOLATCH_<X>	Дисковая подсистема слишком слаба	Увеличьте число дисков или оптимизируйте ввод-вывод. См. “Оптимизация ввода-вывода и размещения файлов”.
IMPROV_IO	Данные из текстового файла поступают слишком медленно	Оптимизируйте дисковый ввод-вывод, для используемых файлов с загружаемыми данными.
PAGELATCH_UP	Конкуренция за страницы PFS	Убедитесь, что дисковая подсистема достаточно быстра. См. “Конкуренция PFS” Добавьте в параметры запуска флаг -E.
ASYNC_NETWORK_IO	Сеть не может обеспечить надлежащую пропускную способность	См. в главе “Отслеживаемые счётчики производительности” информацию о настройках сети
WRITELOG	Журнал транзакций недостаточно производителен	Убедитесь, что используется минимальное протоколирование операций. Убедитесь, что журнал транзакций находится на быстром диске.
OLEDB	Данные на вход поступают слишком медленно	Оптимизируйте производительность источника данных, поставляющего на вход информацию.
SOS_SCHEDULER_YIELD	Конкуренция за планировщик	См. “Конкуренция на планировщике”.
ALLOC_FREESPACE_CACHE	Конкуренция за распределение памяти в куче (выводятся только в sys.dm_os_latch_stats)	Слишком много потоков одновременно вставляет данные в кучу. Рассмотрите возможность секционирования таблицытаким образом, чтобы за счёт увеличения числа куч снизить конкуренцию вставки.
PREEMPTY_COM_<X>	Ничего	Эти ожидания вполне нормальное и ожидаемое явление. Игнорируйте их.

Таблица 5: Типичные ожидания для массовых операций

Отслеживаемые счетчики производительности

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

Объект производительности	Счётчик	Пояснения
Логический диск Logical Disk	Скорость записи на диск (байт/сек) Disk Write Bytes Bytes / sec	Показывает эффективную скорость записи на диски.
Логический диск Logical Disk	Скорость чтения с диска (байт/сек) Disk Read Bytes / sec	Показывает, как быстро данные читаются из источника.
Логический диск Logical Disk	Средний размер одной записи на диск (байт) Avg Disk Bytes / Write	Показывает, насколько большой размер блока логического диска. См. “Оптимизация ввода-вывода и размещенияфайлов”.
Процессор Processor	% загруженности процессора % Processor time – Total	Можно ожидать 100% утилизации процессоров, которые обслуживают задачи массовой загрузки, если для таких задачнет узких мест.
MSSQL::Databases	Массовое копирование строк/с Bulk copy rows / sec	Показывает число строк, заливающихся в базу данных. Оптимизация заливки приводит к увеличению этихзначений.
Сетевой интерфейс Network Interface	Всего байт/сек Bytes Total / sec	Показывает пропускную способность сети, которая подключена через сетевые адаптеры сервера.
SQL:Databases	Количество байтов журнала, сбрасываемых на диск/с Log bytes Flushed / Sec	Показывает производительность журнала транзакций.

Если для массовой загрузки данных по сети используется Integration Services или BCP, правильная настройка производительности становится критически важным фактором.
Взаимодействуя со своим отделом локальных и глобальных сетей, выясните инфраструктуру и конфигурацию сети. Стоит также удостовериться в том, что выбранная конфигурация обеспечивает минимально возможное время ожидания. Ниже перечислены несколько параметров, которые для этого можно оптимизировать:

Используйте высокопроизводительные сетевые платы и коммутаторы.
Установите последние версии сертифицированных драйверов используемых сетевых плат.
Включите полный дуплекс.
Включите поддержку “Гигантских” фреймов (Jumbo frames).
Используйте режим “TCP Chimney Offload”.
Используйте режим Receive Side Scaling (RSS).

Ниже перечислены ссылки на документы, где можно найти краткий обзор ключевых атрибутов сетевых интерфейсов и того, как они взаимодействуют с операционной системой Windows®:

Оптимизировать производительность сети непосредственно для клиента массовой загрузки можно, изменяя параметр размера сетевого пакета для подключения к SQL Server. В Integration Services размер пакета настраивается в свойствах менеджера подключения, как это проиллюстрировано ниже.

Рисунок 10: Настройка размера сетевого пакета

Если используется BCP, размер пакета позволяет задать ключ -a. Увеличение размера сетевого пакета сокращает число операций чтения и записи, потребляемых сетевым стеком SQL Server. Поскольку подобные изменения в настройках приведут к тому, что каждая операция будет чуть больше утилизировать процессоры, сокращение ввода-вывода может поднять производительность. В таблице, приведенной ниже, представлены несколько способов оптимизации одного потока, которые применялись для достижения мирового рекорда загрузки данных в ETL системе.

Размер сетевого пакета	Число сетевых чтений SQL Server	Число дисковых записей SQL Server	Число дисковых чтений Integration Services	Число сетевых записей Integration Services	Время исполнения
4096 (по умолча-нию)	465 тыс.	8 тыс. (256 Кб)	14 тыс. (128 Кб)	465 тыс.	2 мин. 56 сек.
32 Кб	58 тыс.	8 тыс. (256 Кб)	14 тыс. (128 Кб)	58 тыс.	2 мин. 36 сек.

Таблица 6: Эффект от размера сетевого пакета Если для достижения требуемого уровня пропускной способности по сети используется несколько сетевых плат, можно извлечь выгоду из привязки прерываний сетевых интерфейсов Windows к указанным процессорным ядрам. Для получения подробной информации о такой привязке ознакомьтесь со статьёй: Scaling Heavy Network Traffic with Windows.
Используя гигабитную сетевую плату можно достичь пропускной способности приблизительно в 70-100 мегабайт в секунду в зависимости от передаваемых при массовой загрузке данных. Для измерения производительности сети используйте счетчики из раздела Сетевой интерфейс (Network Interface).

Рисунок 11: Пример контроля производительности сети

Конкуренция на планировщике (Scheduler Contention)

Конкуренция на планировщике возникает тогда, когда две параллельные операции массовой загрузки исполняются на одном и том же планировщике SQLOS, за который они конкурируют, т.е. конкурируют за циклы одного и того же процессорного ядра. Обычно такое может случиться на компьютерах, у которых имеется много процессорных ядер. Наличие конкуренции на планировщике можно обнаружить по ожиданию SOS_SCHEDULER_YIELD, которое может появляться даже в тех случаях, когда число процессов загрузки данных не превышает число процессорных ядер.
Одним из способов решения подобной проблемы является программная настройка поддержки экземпляром SQL Server неоднородного доступа к памяти (Soft-NUMA). Каждый программно настроенный узел Soft-NUMA должен содержать по одному процессорному ядру. После этого, каждому Soft-NUMA узлу назначается индивидуальный порт TCP/IP. Это позволит выбирать явно, на какой процессор направляется нагрузка, указав необходимый порт в качестве части строки подключения к SQL Server. Обратите внимание, что изменение параметров настройки Soft-NUMA требует рестарта службы SQL Server. На рисунке ниже проиллюстрирован этот принцип.

Рисунок 12: Soft-NUMA и подключения массовых операций

Для получения дополнительной информации о настройке Soft-NUMA для подобных конфигураций, см. “Как настроить сервер SQL Server на использование программной архитектуры NUMA” и “Как сопоставить порты TCP/IP узлам NUMA“.
Когда настроена привязка портов к узлам, в журнале ошибок SQL Server эти порты TCP будут представлены так, как это показано на рисунке ниже.

Рисунок 13: Пример вывода конфигурации Soft-NUMA в журнале SQL Server

Также можно настроить отдельный порт, который будет отображаться на все процессорные ядра, и его можно будет использовать для обычных подключений, которые не задействованы в синхронизированной рабочей нагрузке массовой загрузки данных.
Можно предотвратить конкуренцию на планировщике с помощью других методик, например, с помощью методики, получившей название: “Отключись и подключись заново”. Эта методика описана в этой технической статье: “Resolving scheduler contention for concurrent BULK INSERT“.

Конкуренция PFS (Page Free Space)

Несмотря на то, что массовая загрузка с минимальным протоколированием сопровождается малыми объёмами записи в журнал транзакций, возможны трудности с распределением новых страниц для целей массовой загрузки. При таких распределениях, SQL Server использует специальный тип страниц – Page Free Space (PFS), с помощью которых отслеживается использование страниц в файле данных.
Поскольку для массовых операций новые страницы должны распределяться очень быстро, обращение к страницам PFS для выделения места под загружаемые данные может стать критически важным шагом. Если такие обращения будут проблематичными, сервер об этом будет сигнализировать ожиданием PAGELATCH_UP. С помощью запроса к sys.dm_os_buffer_descriptors можно удостовериться, что ожидается доступ к страницам PFS. Избавиться от ожиданий PAGELATCH_UP можно путём увеличения числа файлов данных в оперируемой файловой группе. Обратите внимание, что наличие нескольких файлов данных в файловой группе может сделать дисковый ввод-вывод менее эффективным, как это описано в идущей далее главе “Получатель массовой загрузки SQL Server”.
Для получения более подробной информации о страницах PFS и о том, как они могут влиять на производительность, обратитесь к техническому документу “How many files should a database have? -Part

1: OLAP workloads” и статье в SQL Server Books Online “Управление размещением экстента и свободным

местом”.

Оптимизация ввода-вывода и размещения файлов

Когда выполняется параллельная загрузка в несколько потоков, нужно тщательно продумать конфигурацию используемой подсистемы ввода-вывода. Поскольку нужно добиться увеличения скорости чтения и записи, при оптимизации следует фокусироваться на времени ожидания диска.
Процесс массовой загрузки должен одновременно читать данные из источника и выполнять их массовую загрузку средствами ядра СУБД. Это означает, что нагрузка ввода-вывода имеет преимущественно последовательный характер доступа. Существенный выигрыш в производительности могут дать такие приёмы, которые, например, использовались при установлении Мирового Рекорда в ETL, и заключались в усовершенствовании вторичных факторов производительности ввода-вывода массовой загрузки, например, таких как размещение файлов по дискам.
Часто, может оказаться выгодным размещать исходные данные на других дисках, не на тех, на которые идёт загрузка. Изоляции друг от друга нагрузок чтения и записи можно достичь путём разделения двух разных по характеру нагрузки последовательных потоков по разным шпинделям.
Если учитывать особенности операционной системы Windows, то нужно убедиться, что используемые разделы дисков отформатированы с размером кластера NTFS равным 64 Кб, и кластеры правильно выровнены по границам сектора относительно блоков дисковых массивов.
Источник и получатель данных массовой загрузки оптимизируются по-разному, что будет рассмотрено в следующих главах.

Источник данных

Для достижения максимальной производительности массовой загрузки, нужно организовать несколько параллельных потоков чтения из источника данных, по одному и более для каждой операции массовой загрузки.
Если источник секционирован, и чтение выполняется последовательно, можно оптимизировать источник, помещая каждую исходную секцию данных на собственный, выделенный для этой секции набор шпинделей. Однако если вы имеете много исходных секций, большое количество потоков может быстро стать неуправляемым.
В качестве наглядного примера сложности управления большим числом потоков можно привести гипотетический случай с использованием текстовых файлов в качестве исходных данных и выполнение 64 параллельных команд BULK INSERT для этих текстовых файлов.
Давайте рассмотрим две крайности в конфигурации дисковых массивов:

Just a Bunch Of Disks (JBOD) – это конфигурация с отдельными дисками (или возможно RAID1 или RAID 10 LUN), которые распределены в сегментированныймассив.
Stripe And Mirror Everything (SAME) – это конфигурация с распределением дисков в единственный большой LUN.

Just a Bunch Of Disks (JBOD)

С точки зрения производительности ввода-вывода, размещение JBOD обычно самое быстрое. В этом случае, каждый текстовый файл выкладывается на свой диск.

Эта конфигурация показана ниже.

Рисунок 14: Размещение дисков для JBOD

Поскольку BULK INSERT будет последовательно читать файл, для диска нагрузка ввода-вывода будет последовательной с очень маленьким временем задержки на операциях дискового поиска. Современный жесткий диск способен легко превысить скорость последовательного чтения в 100 Мб/сек, что более чем достаточно для нужд BULK INSERT. Однако следует учитывать повышение сложности управления подобным размещением файлов. В данном случае, понадобилось бы организовать в операционной системе 32 LUN, и это только для поддержки файлов исходных данных. Каждый из этих LUN должен быть соответствующим образом настроен на SAN или на встроенном SCSI контроллере. Кроме этого, добавится работы по секционированию, управлению файловой системой и резервному копированию, что может существенно усложнить поддержку и обслуживание такого решения. Если операции по обслуживанию и установке не будут выполняться автоматизированной системой, в отсутствии опытного персонала, работа с подобным сложным размещением файлов и большим числом аппаратных элементов повышает риски ошибок и проблем, вызванных человеческим фактором.

Stripe And Mirror Everything (SAME)

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

Рисунок 15: Размещение дисков для SAME

Однако несколько параллельных последовательных потоков чтения с одних и тех же шпинделей могут породить эффект, который известен под именем “ввод-вывод внахлёст” (I/O weaving). Даже притом, что рабочая нагрузка будет носить последовательный характер (с точки зрения ядра хранилища SQL Server), для диска такая нагрузка будет носить случайный характер. Поскольку ввод-вывод в реальности случайный, возрастает влияние времени поиска диска, что вынуждает вас увеличивать число шпинделей, чтобы обеспечить заданные требования к производительности.
Некоторые системы SAN и контроллеры SCSI способны использовать собственный кэш, чтобы преобразовать случайный ввод-вывод, и собрать несколько потоков в последовательный ввод-вывод. Стоит проконсультироваться у своего поставщика систем хранения, чтобы выбрать верные характеристики дисковой подсистемы для вашего характера ввода-вывода.
Наиболее удачная конфигурация для вашей системы может оказаться где-нибудь посередине между двумя описанными тут стратегиями SAME и JBOD. Вы должны добиться баланса между производительностью JBOD и управляемостью SAME. Также, обратите внимание, что стратегия JBOD опирается на тот факт, что массовая загрузка будет носить преимущественно последовательный характер. Если нагрузка носит смешанный характер, стратегия SAME может обеспечить наилучшую производительность системы в целом.

Получатель массовой загрузки SQL Server

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

Рисунок 16: Файловые группы и файлы

Когда SQL Server выполняет массовую загрузку в таблицу, новые экстенты распределяются между файлами файловой группы поочерёдно. В конфигурации по умолчанию, SQL Server распределяет один экстент для файла и переходит к следующему в цепочке файлу. Можно увеличить число распределяемых за раз экстентов в 64 раза, для этого необходимо включить параметр запуска сервера -E.

Файл	Штатное распределение экстента	С указанием в стартовых параметрах флага -E
Файл 0	Экстент 1	Экстенты 1-64
Файл 1	Экстент 2	Экстенты 65-128
Файл 0	Экстент 3	Экстенты 129-192
Файл 1	Экстент 4	Экстенты 192-256
…и.т.д…	…и.т.д…	…и.т.д…

Таблица 7: Эффект от использования флага -E

Поскольку каждый экстент занимает 64 Кб, это будет минимальный размер записи в файл во время массовой загрузки. На первый взгляд, можно было бы ожидать последовательный поток блоков по 64 Кб, записываемых на каждый LUN. Однако, реальное положение вещей несколько лучше этого предположения.
SQL Server использует штатную оптимизацию ввода-вывода Windows, которая склеивает (scatter/gather) блоки. Такая оптимизация используется при обслуживании нескольких запросов ввода-вывода, которые могут группироваться, чтобы сформировать один, большой блок запросов. Например, четыре запроса ввода-вывода по 64 Кб могут быть собраны в один запрос на 256 Кб. Поскольку исполнение одного большого запроса ввода-вывода происходит быстрее, чем нескольких маленьких, использование scatter/gather повышает производительность операций записи. При больших нагрузках массовых операций это способствует повышению производительности, и если такие возможности удастся задействовать, то средства мониторинга будут показывать использование запросов ввода-вывода размером до 256 Кб.
Чем больше файлов в файловой группе, тем больше времени понадобится SQL Server чтобы пробежаться по всем файлам и “возвратиться” к первому файлу для записи. Из-за этого, операция scatter/gather может решить сбросить на диск буфер ввода-вывода ещё до того, как SQL Server заполнит этот буфер до его лимита в 256 Кб. Наблюдая за счетчиком производительности “Средний размер одной записи на диск (байт)” (Logical Disk / Avg. Disk Bytes / Write), можно определить, как хорошо scatter/gather заполняет буфер записи.
Когда в файловой группе немного файлов, вероятность того, что scatter/gather соберёт большие запросы ввода-вывода, повышается. Запуск SQL Server с флагом -E также будет способствовать укрупнению размеров блока записи.
Для достижения мирового рекорда ETL, число файлов в файловой группе было небольшим, и выбиралось из соображений получения в результате работы scatter/gather и -E практически равных 256 Кб блоков ввода-вывода.

Рисунок 17: Измерение размеров блока

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

Оптимизация раскладки файлов по логическим дискам

Во время массовой загрузки, каждый файл базы данных получает последовательный поток запросов ввода-вывода на запись, с размерами блока между 64 Кб и 256 Кб.
Как было показано в главе “Источник данных”, оптимизация подсистемы ввода-вывода для получения максимально последовательной рабочей нагрузки может дать существенный выигрыш в производительности массовой загрузки. Тут мы снова должны рассмотреть уже описанные выше две крайности: JBOD против SAME. Что лучше: иметь один большой LUN или несколько, логических дисков меньшего размера?
Как правило, если положить один файл базы данных на один LUN, это обеспечивает самую высокую производительность операций ввода-вывода. Каждый такой LUN может быть сформирован из нескольких дисков, собранных в RAID. В отличие от оптимизации чтения, где каждый файл с данными для массовой загрузки нуждается в собственном LUN, есть смысл положиться на SQL Server, который направит поток данных массовой загрузки в несколько получающих данные таблиц, преобразовав его в последовательный вид для записи в один и тот же файл. Поскольку SQL Server во время массовой загрузки, пишет в файл последовательно, нет необходимости закреплять по одному LUN на каждый поток массовой операции и оптимизировать этим её производительность. Это означает, что можно создать небольшое число больших LUN на специализированных для этого шпинделях. Каждый LUN должен обслуживать один файл базы данных – то есть нужно обеспечить соотношение LUN к файлам базы данных близкое 1:1. Конечно, если SAN или кэш контроллера могут сгладить негативные последствия пересечения ввода-вывода, можно создавать более одного файла на одном LUN. До развертывания системы стоит проверить, сколько последовательных потоков записи будет обращено к одному и тому же LUN в используемой для этих целей подсистеме ввода-вывода.
Представленные выше соображения являются аргументами в пользу конфигурации SAME – один большой LUN для каждой файловой группы базы данных. Однако, как было упомянуто ранее в главе о конкуренции PFS, слишком маленькое число файлов (а значит и число LUN, потому что нужно сохранить соотношение 1:1) может вызвать проблемы с распределением страниц PFS. Поэтому, для файловой группы можете потребоваться использовать больше одного файла на каждом LUN. Например, в упомянутом уже проекте по мировому рекорду ETL, мы использовали по 4 файла базы данных на одном LUN компьютера, куда была направлена загрузка.

Описание проекта: Мировой Рекорд ETL

В рамках проекта по Мировому Рекорду ETL, 56 задач BULK INSERT исполнялись параллельно со средней скоростью 600 Мб в секунду и с пиками до 850 Мб в секунду.
Сервер базы данных SQL Server 2008:

Unisys ES7000/one Enterprise Server with 32 Dual core 3Ghz Intel 7140M CPUs
- Всего 64 ядра
- 8 x 4Gbit Emulex HBAs
- 8 x 1Gbit Intel Pro /1000MT Network cards
- 256 GB RAM (для операций BULK INSERT использовалось только 30Гб)

Оптимальное размещение файла SQL Server в этих условиях было таким:

Файлы данных SQL Server были расположены на EMC Clariion CX3-80 SAN с общим числом шпинделей 165.
- Были “нарезаны” 16 Мета-LUN. Каждый Мета-LUN был собран из 8 шпинделей как показано на рисунке 19

Рисунок 18: Мета-LUN

133Гб/4Гбит шпиндели с числом оборотов в минуту 15К.
56 файлов данных, по 4 файла данных на каждом LUN.
- Журнал SQL Server: single4x (1+1R1) Мета-LUN

Размещение файлов для чтения:

2 x EMC Clariion CX600 SAN
- 10 LUN в RAID5, по 5 на каждом CX600
- 7 x 36Гб шпинделей с числом оборотов в минуту 15К на один LUN
- На каждом LUN лежали 5 или 6 текстовых файлов.

Для получения более подробной информации, см. следующие материалы:

Дополнительные ссылки на статьи о вводе-выводе

Для получения более подробной информации о вводе-выводе, изучите эти материалы:

Заключение

Если время исполнения задачи пакетной загрузки данных выходит за отведённые рамки, или если нужно достичь максимальной производительности загрузки большого объёма данных, эта статья предоставляет руководство по налаживанию мониторинга и оптимизации производительности подобных задач.
В статье представлена информация о том, как выбрать метод массовой загрузки, чтобы этот метод лучше всего подходил для вашей задачи. Массовая загрузка лежит в основе некоторых распространённых задач. Описанные здесь решения помогают выбрать правильный сценарий, и помочь вам подступиться к решению и поддержке задачи массовой загрузки данных.
Для рабочих нагрузок, которые характеризуются высокой нагрузкой, исключение “узких мест” является ключом для обеспечения наилучшей производительности. В главе “Оптимизация массовой загрузки данных” были представлены рекомендации по оптимизации конфигурации SQL Server для повышения производительности операций массовой загрузки.

Там же можно найти соответствующие ссылки на дополнительные материалы по этой теме.
Совместное использование SQL Server и Integration Services позволяет организовать высокопроизводительную загрузку данных. Во время работ по проекту Мирового Рекорда ETL использовались руководства из настоящей статьи. После выполнения описанных здесь мер оптимизации, были втрое превышены показатели “коробочного” варианта установки. Кроме того, была достигнута близкая к линейной масштабируемости в пределах от 1 до 64 параллельных операций массовой загрузки на 64-х ядерной системе Unisys ES7000/one, которой управляла операционная система Windows Server ® 2008 Datacenter Edition.
Для получения более подробной информации:

http://www.microsoft.com/sqlserver/: SQL Server Web site
http://sqlcat.com/: SQL Customer Advisory Team Web site
http://technet.microsoft.com/ru-ru/sqlserver/: SQL Server TechCenter
http://msdn.microsoft.com/ru-ru/sqlserver/: SQL Server DevCenter

28.12.22

Руководство по производительности загрузки данных