Управление пакетами для массового импорта данных

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

Чтобы избежать проблем при импорте больших файлов данных в виде единого пакета, программа bcp и инструкция BULK INSERT позволяют загружать данные в виде последовательности пакетов, размер которых меньше размера файла данных. Импорт и загрузка каждого пакета производится отдельной транзакцией, после фиксации которой загруженные строки остаются в базе данных. Если при выполнении операции происходит сбой, то откатываются только строки, импортированные из текущего пакета, и импорт данных может быть возобновлен с начала сбойного пакета, а не с начала файла данных.

Примечание.
Сведения о работе пакетов см. в разделе Пакеты.

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

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

В следующей таблице дается сводка квалификаторов, поддерживающих эти решения.

Команда

Размер пакета

Количество строк в пакете

Количество килобайт в пакете

bcp1

-bbatch_size

-h "ROWS_PER_BATCH = bb"

-h "KILOBYTES_PER_BATCH = cc"

BULK INSERT2

BATCHSIZE = batch_size

ROWS_PER_BATCH = rows_per_batch

KILOBYTES_PER_BATCH = kilobytes_per_batch

Примечание.

При использовании этого параметра вместе с BATCHSIZE формируется ошибка.

INSERT ... SELECT * FROM OPENROWSET (BULK...)

—3

ROWS_PER_BATCH = rows_per_batch

—3

1 В команде bcp не следует использовать одновременно аргумент -bbatch_size и подсказку ROWS_PER_BATCH или KILOBYTES_PER_BATCH. При их совместном использовании формируется ошибка.

2 В команде BULK INSERT, если аргумент BATCHSIZE используется вместе с ROWS_PER_BATCH или KILOBYTES_PER_BATCH, у BATCHSIZE преимущество.

3 У функции OPENROWSET нет ни аргумента BATCHSIZE, ни KILOBYTES_PER_BATCH.

В следующих разделах описывается использование этих квалификаторов.

Задание приблизительного размера пакета

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

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

Чтобы импортировать файл данных в виде последовательности пакетов приблизительно заданного размера, используйте следующий квалификатор.

• Для команды bcp: -b
• Для инструкции BULK INSERT: BATCHSIZE

Каждый пакет строк вставляется как отдельная транзакция. Если по каким-либо причинам операция массового импорта заканчивается до нормального завершения, производится откат только текущей транзакции. Например, если файл данных содержит 1000 строк, а размер пакета равен 100, Microsoft SQL Server протоколирует операцию в виде десяти отдельных транзакций, каждая из которых вставляет в целевую таблицу 100 строк. Если операция массового импорта заканчивается при загрузке 750-строки, SQL Server выполняет откат текущей транзакции и повторно вставляются только 49 предыдущих строк. В целевой таблице сохраняются первые 700 строк.

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

Примечание.
Размеры пакета не применяются при выполнении экспорта данных из экземпляра SQL Server в файл данных.

Задание приблизительного размера файла данных

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

• Оценка количества строк в пакете
  Чтобы оценить количество строк, используйте подсказку или параметр ROWS_PER_BATCH. При задании значения > 0 обработчик запросов использует значение ROWS_PER_BATCH в качестве подсказки для назначения ресурсов в плане запроса. Значение должно быть того же порядка, что и реальное количество строк.
  Хотя все строки из файла данных копируются в экземпляр SQL Server одним пакетом, программа bcp через каждые 1000 строк отображает сообщение: «SQL Server передано 1 000 строк». Это сообщение служит только в информационных целях и возникает независимо от размера пакета.
• Оценка количества килобайт в пакете
  Чтобы оценить объем файла данных в килобайтах, используйте подсказку или параметр KILOBYTES_PER_BATCH. SQL Server оптимизирует операцию массового импорта согласно заданному значению.

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

См. также

Основные понятия

Оптимизация производительности массового импорта данных

Другие ресурсы

BACKUP (Transact-SQL)
Программа bcp
BULK INSERT (Transact-SQL)
ImportRowsPerBatch Property
OPENROWSET (Transact-SQL)
sp_dboption (Transact-SQL)

Справка и поддержка

Получение помощи по SQL Server 2005