Возможности для повышения производительности потока данных

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

Настройка потока данных

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

Настройка свойств задачи потока данных

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

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

• Указать местоположение временного хранилища буферных данных (свойство BufferTempStoragePath) и столбцов, содержащих данные объектов типа BLOB (свойство BLOBTempStoragePath). По умолчанию эти свойства содержат значения переменных среды TEMP и TMP. Для хранения временных файлов можно указать другие папки на другом или более быстром жестком диске либо распределить их по нескольким дискам. Можно указать несколько каталогов, разделяя имена каталогов точками с запятой.

• Определить размер буфера по умолчанию для задачи, установив свойство DefaultBufferSize, и задать максимальное количество строк для каждого буфера, установив свойство DefaultBufferMaxRows. Размер буфера по умолчанию равен 10 МБ, а максимальный размер буфера равен 100 МБ. Максимальное количество строк равно 10 000.

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

• Указать, следует ли выполнять задачу потока данных в режиме оптимизации (свойство RunInOptimizedMode). Режим оптимизации повышает производительность, удаляя из потока данных неиспользуемые столбцы, выходы и компоненты.

  Примечание
  Свойство с тем же именем, RunInOptimizedMode, может быть задано на уровне проекта в среде SQL Server Data Tools (SSDT), чтобы указать, что задача потока данных запускается в режиме оптимизации во время отладки. Это свойство переопределяет свойство RunInOptimizedMode задач потока данных во время проектирования.

Настройка размера буфера

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

• Если результат больше значения DefaultBufferSize, подсистема обработки потока данных уменьшает количество строк.

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

• Если результат находится между минимальным размером буфера и значением DefaultBufferSize, подсистема обработки потока данных устанавливает размер буфера как можно ближе к предполагаемому размеру строки, умноженному на значение DefaultBufferMaxRows.

В начале тестирования задач потока данных используйте значения по умолчанию для свойств DefaultBufferSize и DefaultBufferMaxRows. Включите ведение журналов в задаче потока данных и выберите событие BufferSizeTuning для определения количества строк, содержащихся в каждом буфере.

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

При наличии достаточного объема памяти следует использовать меньшее число больших буферов, а не наоборот. Иначе говоря, производительность можно повысить, уменьшив общее число буферов, необходимых для хранения данных, а также установив как можно большее количество строк данных для одного буфера. Для определения оптимального числа буферов и их размеров поэкспериментируйте со значениями свойств DefaultBufferSize и DefaultBufferMaxRows, наблюдая за уровнем производительности и сведениями, получаемыми событием BufferSizeTuning.

Не увеличивайте размер буфер до такого размера, при котором он начнет записываться на диск. При записи буфера на диск производительность ниже, чем при неоптимизированном буфере. Чтобы определить, происходит ли запись на диск, наблюдайте за счетчиком производительности "Выгружено буферов" в оснастке "Производительность" консоли управления Microsoft (MMC). 

Настройка пакета для параллельного выполнения

Параллельное выполнение повышает производительность на компьютерах с несколькими физическими или логическими процессорами. Для поддержки параллельного выполнения различных задач в пакете в службах Службы Integration Services используются два свойства: MaxConcurrentExecutables и EngineThreads.

Свойство MaxConcurrentExcecutables

Свойство MaxConcurrentExecutables является свойством самого пакета. Оно определяет число одновременно работающих задач. По умолчанию оно имеет значение –1, что означает число физических или логических процессоров +2.

Чтобы понять работу этого свойства, рассмотрим образец пакета, в котором есть три задачи потока данных. Если задать для свойства MaxConcurrentExecutables значение 3, то все три задачи потока данных смогут выполняться одновременно. Однако предположим, что каждая задача потока данных имеет 10 деревьев выполнения «источник-назначение». Если свойство MaxConcurrentExecutables имеет значение 3, то нельзя гарантировать, что деревья выполнения внутри каждой задачи потока данных будут работать параллельно.

Свойство EngineThreads

Свойство EngineThreads является свойство задачи потока данных. Это свойство определяет число потоков, которое подсистема обработки потока данных может создать и выполнять параллельно. Свойство EngineThreads применяется одинаково как к потокам источника, которые подсистема обработки потока данных создает для источников, так и к потокам исполнителя, которые подсистема создает для преобразований и назначений. Таким образом, если свойство EngineThreads имеет значение 10, подсистема может создавать до десяти потоков источника и до десяти потоков исполнителя.

Чтобы понять работу этого свойства, рассмотрим образец пакета с тремя задачами потока данных. Каждая задача потока данных содержит десять деревьев выполнения «источник-назначение». Если свойство EngineThreads для каждой задачи потока данных имеет значение 10, все 30 деревьев выполнения потенциально могут выполняться одновременно.

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

Настройка отдельных компонентов потока данных

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

Общие рекомендации

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

Оптимизация запросов

Многие компоненты потока данных используют запросы как для извлечения данных из источников, так и для операций поиска, чтобы создать ссылочные таблицы. По умолчанию запрос использует синтаксис SELECT * FROM <Имя таблицы>. Этот тип запроса возвращает все столбцы исходной таблицы. Так как все столбцы доступны во время разработки, можно выбрать любой столбец в качестве столбца поиска, сквозного или исходного столбца. Однако после выбора используемых столбцов следует изменить запрос таким образом, чтобы в него были включены только выбранные столбцы. Удаление неиспользуемых столбцов делает поток данных пакета более эффективным, так как чем меньше столбцов, тем меньше создаваемая строка. А чем короче строка, тем больше строк может быть загружено в один буфер и тем меньше усилий потребует обработка всех строк набора данных.

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

Примечание

При запуске пакета в среде SQL Server Data Tools (SSDT) на вкладке «Ход выполнения» конструктора служб Службы SSIS отображаются предупреждения. Они включают обнаружение любых столбцов данных, которые источник делает доступными для потока данных, но которые впоследствии не используются нисходящими компонентами потока данных. Для автоматического удаления таких столбцов можно использовать свойство RunInOptimizedMode.

Исключение ненужной сортировки

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

Иногда источник данных уже упорядочен до использования нисходящим компонентом. Такая предварительная сортировка может происходить при использовании запроса SELECT с предложением ORDER BY, а также при вставке данных в источник в порядке сортировки. Для подобных, предварительно отсортированных исходных данных можно предоставить указание о том, что эти данные уже отсортированы, чтобы избежать использования преобразования «Сортировка» в соответствии с требованиями определенных нисходящих преобразований. Например, для преобразования «Слияние» и «Соединение слиянием» требуются отсортированные входные данные. Чтобы предоставить указание о том, что данные отсортированы, необходимо выполнить следующие задачи.

• Присвойте значение True свойству IsSorted на выходе из вышестоящего компонента потока данных.

• Укажите ключевые столбцы, по которым отсортированы данные.

Дополнительные сведения см. в разделе Сортировка данных для преобразований «Слияние» и «Соединение слиянием».

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

Дополнительные сведения см. в разделах Преобразование «Сортировка», Преобразование «Слияние», Преобразование «Соединение слиянием» и Преобразование «Многоадресная доставка».

Источники

Источник «OLE DB»

При получении данных из представления с помощью источника OLE DB выберите режим доступа к данным «Команда SQL» и введите инструкцию SELECT. Обращение к данным с помощью инструкции SELECT дает более высокую производительность, чем при выборе режима доступа к данным «Таблица или представление».

Преобразования

Советы в этом разделе служат для повышения производительности при преобразованиях «Статистическая обработка», «Нечеткий уточняющий запрос», «Нечеткое группирование», «Уточняющий запрос», «Cоединение слиянием» и «Медленно изменяющееся измерение».

Преобразование «Статистическая обработка»

Преобразование «Статистическая обработка» содержит свойства Keys, KeysScale, CountDistinctKeys и CountDistinctScale. Эти свойства улучшают производительность, позволяя преобразованию предварительно выделять память в размере, необходимом для кэширования данных. Если известно точное или приблизительное число групп, которые будут являться результатом операции Группировать по, задайте свойства Keys и KeysScale соответственно. Если известно точное или приблизительное число различающихся значений, которые будут являться результатом операции Подсчет различных объектов, задайте свойства CountDistinctKeys и CountDistinctScale соответственно.

Если в потоке данных необходимо создать несколько статистических выражений, попробуйте использовать одно преобразование «Статистическая обработка» вместо создания нескольких преобразований. Такой подход, в частности, повысит производительность, если статистическая обработка является подмножеством другой статистической обработки, поскольку преобразование сможет оптимизировать внутреннее хранилище и просматривать входящие данные только один раз. Например, если статистическое выражение использует предложение GROUP BY и статистическое выражение AVG, объединение их в одно преобразование может повысить производительность. Однако выполнение нескольких статистических выражений в пределах одного преобразования «Статистическая обработка» сериализует операции статистической обработки, и таким образом производительность может не повышаться при независимом вычислении нескольких статистических выражений.

Преобразования «Нечеткий уточняющий запрос» и «Нечеткое группирование»

Сведения по оптимизации производительности преобразований «Нечеткий уточняющий запрос» и «Нечеткое группирование» см. в технической документации Преобразования «Нечеткий уточняющий запрос» и «Нечеткое группирование» в службах SQL Server Integration Services 2005.

Преобразование «Уточняющий запрос»

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

Преобразование «Соединение слиянием»

Настраивать значение свойства MaxBuffersPerInput больше не нужно, поскольку корпорация Майкрософт внесла изменения, которые уменьшили риск потребления чрезмерных ресурсов памяти при выполнении операции «Соединение слиянием». Эта проблема иногда возникает, если несколько входов операции «Соединение слиянием» производят данные с различной скоростью.

Преобразование «Медленно изменяющееся измерение»

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

Как правило, самые медленные компоненты преобразования «Медленно изменяющееся измерение» — преобразования «Команда OLE DB», выполняющие инструкции UPDATE одновременно только для одной строки. Таким образом, самым эффективным способом повысить производительность преобразования «Медленно изменяющееся измерение» — замена преобразований «Команда OLE DB». Эти преобразования можно заменить целевыми компонентами, сохраняющими все строки для обновления в промежуточной таблице. Затем можно добавить задачу «Выполнение SQL», которая выполняет одну инструкцию Transact-SQL UPDATE одновременно для всех строк.

Опытные пользователи могут сконструировать пользовательский поток данных для обработки медленно изменяющегося измерения, оптимизированного для больших измерений. Обсуждение и примеры по решению этой задачи см. в разделе «Сценарий уникального измерения» в техническом документе Проект REAL. Рекомендации по разработке ETL-решений для бизнес-аналитики (на английском языке).

Назначения

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

Назначение SQL Server

Когда пакет загружает данные в экземпляр SQL Server на этом же компьютере, используйте назначение SQL Server. Это назначение оптимизировано для высокоскоростной массовой загрузки.

Тестирование производительности назначений

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

Просмотр сведений на вкладке «Выполнение»

Конструктор служб Службы SSIS предоставляет сведения как о потоке управления, так и о потоке данных при запуске пакета в среде SQL Server Data Tools (SSDT). На вкладке Выполнение приводится список задач и контейнеров в порядке выполнения, в том числе время запуска и окончания работы, предупреждения и сообщения об ошибках каждой задачи или контейнера, включая сам пакет. На ней также представлены список компонентов потока данных в порядке выполнения и сведения о ходе выполнения в виде процентов завершения и количества обработанных строк.

Чтобы включить или отключить отображение сообщений на вкладке Выполнение, установите или снимите флажок Отчет о ходе отладки в меню Службы SSIS. Отключение отчетов о состоянии способствует повышению производительности выполнения сложных пакетов в среде SQL Server Data Tools.

Связанные задачи

• Сортировка данных для преобразований «Слияние» и «Соединение слиянием»

См. также

Статьи и сообщения в блогах

• Техническая статья SQL Server 2005 Integration Services: A Strategy for Performance (на английском языке) на сайте technet.microsoft.com

• Техническая статья Integration Services: Performance Tuning Techniques (на английском языке) на сайте technet.microsoft.com

• Техническая статья Increasing Throughput of Pipelines by Splitting Synchronous Transformations into Multiple Tasks (на английском языке) на сайте sqlcat.com

• Техническая статья Руководство по производительности загрузки данных на сайте msdn.microsoft.com.

• Техническая статья We Loaded 1TB in 30 Minutes with SSIS, and So Can You (на английском языке) на сайте msdn.microsoft.com.

• Техническая статья Top 10 SQL Server Integration Services Best Practices (на английском языке) на сайте sqlcat.com.

• Техническая статья и образец кода The «Balanced Data Distributor» for SSIS на сайте sqlcat.com.

• Сообщение в блоге Troubleshooting SSIS Package Performance Issues (на английском языке) на сайте blogs.msdn.com

Видеоматериалы

• Видеосерии Проектирование и настройка производительности пакетов SSIS на предприятии (SQL Video Series)

• Видеоматериал Tuning Your SSIS Package Data Flow in the Enterprise (видеоматериалы по SQL Server) на сайте technet.microsoft.com

• Видеоматериал Understanding SSIS Data Flow Buffers (видеоматериалы по SQL Server) на сайте technet.microsoft.com

• Видеоматериал Microsoft SQL Server Integration Services Performance Design Patterns на сайте channel9.msdn.com.

• Презентация How Microsoft IT Leverages SQL Server 2008 SSIS Dataflow Engine Enhancements (на английском языке) на сайте sqlcat.com.

• Видеоматериал Balanced Data Distributor на сайте technet.microsoft.com.

Оставайтесь в курсе последних возможностей служб Integration Services Чтобы загрузить новейшую документацию, статьи, образцы и видеоматериалы корпорации Майкрософт, а также лучшие решения участников сообщества, посетите страницу служб Службы Integration Services на сайте MSDN: Посетить страницу «Службы Integration Services» на сайте MSDN Чтобы получать автоматические уведомления об этих обновлениях, подпишитесь на RSS-каналы, предлагаемые на этой странице.

См. также

Задания

Инструменты устранения неполадок при разработке пакета

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

Устранение неполадок инструментов с помощью отчетов