Рекомендации по оптимизации массового импорта данных

Изменения: 14 апреля 2006 г.

В этом разделе содержатся рекомендации по оптимизации производительности массового импорта для нескольких сценариев:

• Импорт данных с одного клиента (или потока) в пустую таблицу
• Импорт данных с одного клиента (или потока) в частично заполненную непустую таблицу.

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

• Импорт данных совместно с блокировкой на уровне таблицы из нескольких клиентов (или потоков).
• Копирование данных между экземплярами Microsoft SQL Server.

В этом разделе предоставляется сводка методов блокировки таблиц и ведения журнала во время операций массового импорта.

Импорт данных с одного клиента (или потока) в пустую таблицу.

При импорте данных с одного клиента (или потока) в пустую таблицу корпорация Microsoft рекомендует следующее:

• Укажите квалификатор TABLOCK (который доступен как подсказка или параметр на дереве методов массового импорта). Использование квалификатора TABLOCK приводит к блокировке на уровне таблиц на период массовой операции и устраняет издержки, связанные с блокированием отдельных строк. Дополнительные сведения см. в разделе Управление блокировкой при массовом импорте.
• Минимизируйте ведение журнала. Дополнительные сведения см. в разделе Предварительные условия для минимального ведения журнала массового импорта данных.
• Обрабатывайте индексы следующим образом.
  При использовании bcp, инструкций BULK INSERT или INSERT ... SELECT * FROM OPENROWSET(BULK...), если таблица пуста и у нее есть кластеризованный индекс, а данные в файле данных упорядочены так, чтобы соответствовать ключевым столбцам кластеризованного индекса, дополнительно выполните следующие действия:
  • Выполните массовое копирование данных с уже готовым кластеризованным индексом.
  • Задайте подсказки ORDER и TABLOCK. Дополнительные сведения см. в разделе Управление порядком сортировки во время массового импортирования данных.
    Для пустых таблиц такой способ оказывается значительно более быстрым, чем создание кластеризованного индекса после импорта данных, так как при таком способе отсутствует шаг сортировки.

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

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

  Примечание.

  Если размер пакета не указан, оптимизатор запросов SQL Server по умолчанию принимает в качестве размера файла данных значение размера по умолчанию. Для улучшения производительности можно задать квалификатор ROWS_PER_BATCH или KILOBYTES_PER_BATCH в качестве подсказки для оптимизатора о приблизительном количестве строк в файле данных. Дополнительные сведения см. в разделе Managing Bulk Copy Batch Sizes.

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

Импорт данных с одного клиента (или потока) в непустую таблицу.

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

При импорте данных с одного клиента (потока) в непустую таблицу вопрос, сохранять ли индексы, зависит от относительного размера импортируемых данных по сравнению с размером существующих в таблице данных:

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

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

Индексы	Относительное количество новых данных
Только кластеризованный индекс	30 процентов
Кластеризованный и некластеризованный индекс	25 процентов
Кластеризованный и два некластеризованных индекса	25 процентов
Только один некластеризованный индекс	100 процентов
Два некластеризованных индекса	60 процентов

Импорт данных с одновременной блокировкой на уровне таблицы из нескольких клиентов (или потоков)

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

При импорте данных в одну таблицу с нескольких клиентов следует учесть:

• Возможна взаимная блокировка нескольких потоков массового импорта.
  Для предотвращения этого в SQL Server поддерживается специальная внутренняя блокировка, которая называется блокировкой массового обновления (BU). Чтобы получить блокировку массового обновления (BU), необходимо указать параметр TABLOCK для каждого потока массового импорта, не блокируя другие потоки массового импорта. Благодаря этому будут исключены межтабличные конфликты среди клиентов. Однако блокировка массового обновления BU применима только для таблицы (пустой либо не пустой), у которой отсутствуют индексы. Если TABLOCK задается на таблице с индексами, параллельный массовый импорт невозможен. Дополнительные сведения см. в разделе Управление блокировкой при массовом импорте.
  Если в таблице есть индексы, то блокировку массового обновления BU можно использовать после удаления всех индексов перед операцией массового импорта данных. Затем можно выполнить параллельный массовый импорт с применением параметра TABLOCK и заново создать индексы или индекс. Следует также заметить, что если в непустой таблице есть индексы, массовый импорт полностью протоколируется даже при использовании модели восстановления с неполным протоколированием. Чтобы решить, удалять ли индексы, нужно оценить, оправдывает ли выгода от массового импорта в таблицу без индексов затраты на удаление и создание индексов заново.

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

  Чтобы не удалять и не создавать заново индексы, можно выполнить параллельный импорт без указания подсказки TABLOCK. Однако в этом случае возможна взаимная блокировка нескольких потоков массового импорта и, кроме того, недоступна оптимизация неполного протоколирования. Чтобы минимизировать блокирование, можно указать меньший размер пакета и применить подсказку ORDER, чтобы устранить шаг сортировки из операции массового импорта.
• Данные должны быть разбиты по нескольким входным файлам, по одному файлу для каждого клиента. Для более эффективного использования ЦП файлы данных должны быть похожего размера.

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

Блокировка таблиц и запись в журнал во время массового импорта

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

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

Целевая таблица массового импорта	Таблица пуста?	Подсказка TABLOCK установлена?	Блокировки	Ведение журнала при модели с неполным протоколированием и при простой модели восстановления
Куча	Да	Да	Вкладка BU	С неполным протоколированием
Куча	Да	Нет	Вкладка IX	Полное протоколирование
Куча	Нет	Да	Вкладка BU	С неполным протоколированием
Куча	Нет	Нет	Вкладка IX	Полное протоколирование
Куча с одним некластеризованным индексом	Да	Да	Блокировка SCH-M	С неполным протоколированием
Куча с одним некластеризованным индексом	Да	Нет	Вкладка IX	Полное протоколирование
Куча с одним некластеризованным индексом	Нет	Да	Блокировка SCH-M	Данные — неполное протоколирование. Индекс — полное протоколирование.
Куча с одним некластеризованным индексом	Нет	Нет	Вкладка IX	Полное протоколирование
Кластеризованный индекс	Да	Да	Блокировка SCH-M	С неполным протоколированием
Кластеризованный индекс	Да	Нет	Вкладка IX	Полное протоколирование
Кластеризованный индекс	Нет	Да	Вкладка X	Полное протоколирование
Кластеризованный индекс	Нет	Нет	Вкладка IX	Полное протоколирование

Копирование данных между экземплярами сервера SQL Server

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

Примечание.
Дополнительные сведения об этих форматах см. в разделах Использование собственного формата для импорта и экспорта данных и Использование собственного формата Юникода для импорта или экспорта данных.

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

1. Производите массовый экспорт данных из исходной таблицы с использованием программы bcp с параметром query в инструкцию SELECT и с соответствующим предложением ORDER BY, чтобы создать упорядоченный файл данных. Дополнительные сведения см. в разделе Программа bcp.
2. При массовом импорте данных в SQL Server используйте квалификатор ORDER, который поддерживается только в программе bcp и BULK INSERT. Дополнительные сведения см. в разделе Управление порядком сортировки во время массового импортирования данных.

Дополнительные сведения см. в разделе Копирование данных между серверами.

См. также

Справочник

SQL Server, объект Databases

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

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

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

Программа bcp
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)

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

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

Журнал изменений

Версия	Журнал
14 апреля 2006 г.	Новое содержимое: Добавлен раздел «Блокировка таблиц и запись в журнал во время массового импорта».