10 рекомендаций по использованию служб интеграции SQL Server

Авторы: Томас Кайзер (Thomas Kejser), Дэнни Ли (Denny Lee)

Соавторы: Раньюин Мао (Runying Mao)

Технические редакторы: Бурцин Пэйтел (Burzin Patel), Кевин Кокс (Kevin Cox), Питер Карлин (Peter Carlin), Марк Суза (Mark Souza), Ричард Ткачук (Richard Tkachuk), Лен Уайатт (Len Wyatt), Линдcи Аллен (Lindsey Allen), Прем Мехра (Prem Mehra), Николас Дритсас (Nicholas Dritsas), Карл Рабилер (Carl Rabeler), Кан Ченг (Kun Cheng), Стюарт Озер (Stuart Ozer), Дональд Фармер (Donald Farmer) 

Дата публикации: 10.01.2008 

Дата обновления: 12.12.2008

Вероятно, вам приходилось слышать миф о том, будто службы интеграции Microsoft® SQL Server® Integration Services (SSIS) не масштабируются? В ответ мы для начала спросим: «А нужно ли масштабировать вашу систему для объемов, превышающих 4,5 миллионов строк транзакций продаж в секунду?» Службы интеграции SQL Server — это высокопроизводительная платформа для извлечения, преобразования и загрузки данных (ETL), способная масштабироваться для самых требовательных сред. И, как описано в документе Мировой рекорд производительности SSIS ETL, службы интеграции SQL Server могут обрабатывать 4,5 миллионов строк транзакций продаж в секунду.   Чтобы оптимизировать вашу рабочую среду, мы подготовили 10 лучших рекомендаций по использованию служб интеграции SQL Server 2008. Большинство из них применимы и к SQL Server 2005.

1. SSIS — это конвейер обработки в памяти, поэтому убедитесь, что все транзакции выполняются в оперативной памяти

Службы интеграции используются в наборе функциональности SQL Server в качестве гибкого, надежного конвейера, способного эффективно проводить построчные вычисления и разбор данных непосредственно в оперативной памяти. 
Хотя стадии извлечения и загрузки конвейера задействуют диск (при считывании и записи соответственно), само преобразование должно проходить исключительно в памяти. Если по каким-то причинам при преобразовании будет задействовано дисковое пространство (например, при больших операциях сортировки), производительность значительно ухудшится. Поэтому пакеты следует секционировать и фильтровать в них данные таким образом, чтобы все преобразования умещались в оперативной памяти.
Чтобы проверить, помещаются ли пакеты в оперативной памяти, можно просмотреть счетчик производительности служб интеграции Выгружено буферов, исходное значение которого равно 0. Большее нуля значение указывает, что модуль начал выгружать данные на диск. Дополнительные сведения см. в разделе Некоторые сведения о датчиках производительности служб интеграции SQL Server.

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

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

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

Самым важным будет счетчик монитора производительности ( perfmon) Процесс / % процессорного времени (Общий). Значение этого счетчика следует определить для процессов sqlservr.exe и dtexec.exe.

Если службам интеграции не удается задействовать около 100% ресурсов ЦП, это может указывать на следующее:

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

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

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

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

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

• Ограничение по дисковому вводу-выводу: Если обеспечено минимальное взаимодействие служб интеграции с диском, обращение к диску будет осуществляться только при чтении исходных и записи целевых данных. В случае медленной подсистемы ввода-вывода операции чтения и особенно записи могут быть «узким местом» и ограничивать общую производительность. 

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

 Помните, что система ввода-вывода характеризуется не только объемом (например, «требуется 10 ТБ»), но и обеспечиваемой скоростью непрерывного обмена данными (например, «требуется выполнение 20 000 операций ввода-вывода в секунду»).

• Ограничение по памяти: При использовании служб интеграции очень важен ответ на вопрос «Сколько памяти использует мой пакет?»  

Основными счетчиками для служб интеграции и для SQL Server являются:

• Процесс / Байты исключительного пользования (DTEXEC.exe) — Объем памяти, используемой в данный момент службами интеграции. Эта память не может использоваться совместно с другими процессами.
• Процесс / Рабочее множество (DTEXEC.exe) —  Общий объем памяти, выделенной службам интеграции.
• SQL Server: Диспетчер памяти / Общая память сервера: Общий объем памяти, выделенный для SQL Server. Поскольку SQL Server может резервировать память другим способом, с помощью AWE API, этот счетчик точнее всего указывает общий объем используемой SQL Server памяти. Чтобы лучше понять принципы выделения памяти для SQL Server, обратитесь к блогу Славы Окса (Slava Oks).
• Память / Чтений страниц/сек — представляет общую нагрузку на память в системе. Если это значение постоянно превышает 500, то система испытывает нехватку памяти.

3. Базовая скорость извлечения исходной системы

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

Измерьте скорость работы исходной системы, создав очень простой пакет, считывающий данные из источника с назначением в «Счетчике строк»:

Запустите пакет из командной строки (DTEXEC) и замерьте время, которое потребуется на выполнение задания. Для точного измерения времени воспользуйтесь выводом журнала служб интеграции. Требуется вычислить число строк в секунду:

             Строк/сек = Счетчик строк/времяПоток данных

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

• Обновить драйверы и их конфигурации: Убедитесь, что используются самые последние драйверы (и их конфигурации) для сети, источника данных и дисковой подсистемы ввода-вывода. Нередко сетевые драйверы, устанавливаемые на сервере по умолчанию, не настроены оптимальным образом для конкретного сетевого стека, что понижает производительность при большом числе одновременных сетевых запросов. Стоит отметить, что на 64-разрядных системах во время разработки могут загружаться 32-разрядные драйверы; убедитесь, что во время эксплуатации используются 64-разрядные драйверы.
• Создать несколько подключений: Чтобы преодолеть ограничения драйверов, можно попробовать создать несколько подключений к источнику данных. Пока источник сможет обслуживать такое количество одновременных соединений, пропускная способность при одновременном запуске нескольких процессов загрузки будет расти. Если одновременные обращения будут вызывать блокировки, рассмотрите возможность секционирования источника и настройки пакетов на считывание из различных секций; при этом нагрузка распределится более равномерно.  
• Используйте несколько сетевых адаптеров: Если «узким местом» системы является сеть, причем вы уже перешли на гигабитные сетевые карты и маршрутизаторы, возможно, проблему можно решить, применяя на серверах по несколько сетевых адаптеров. Помните, что настраивать среды с несколькими сетевыми интерфейсами следует внимательно, иначе могут возникнуть сетевые конфликты.

4. Оптимизируйте источник данных SQL, преобразования типа «Уточняющий запрос» и назначение данных

При выполнении инструкций SQL в службах интеграции (как показано выше в диалоговом окне Режим доступа к данным), будь то инструкции чтения данных из источника, выполнения преобразования типа «Уточняющий запрос» или изменения таблиц, некоторые стандартные методы оптимизации могут существенно улучшить производительность:

• Используйте подсказки NOLOCK или TABLOCK, чтобы устранить накладные расходы, связанные с блокировкой.
• Чтобы оптимизировать использование памяти, выбирайте с помощью инструкции SELECT лишь действительно необходимые столбцы. При выборе с помощью SELECT всех столбцов таблицы (например, инструкцией SELECT * FROM) на получение и хранение столбцов, которые так и не будут использованы, будут зря израсходованы ресурсы памяти и пропускной способности.
• По возможности выполняйте преобразования данных типа datetime в пределах исходной или целевой баз данных, так как внутри служб интеграции это преобразование расходует больше ресурсов.
• В службах интеграции SQL Server 2008 введен новый компонент — общий кэш уточняющих запросов. Он обеспечивает высокоскоростной кэш с общим доступом для параллельных конвейеров (см. пункты №8 и №10 ниже). 
• Если службы интеграции и SQL Server выполняются на одном сервере, используйте в качестве назначения SQL Server вместо OLE DB, чтобы повысить производительность.
• Размер фиксации 0 работает быстрее всего для массовых неиндексированных назначений, так как фиксируется лишь одна транзакция. Если использовать значение 0 невозможно, выберите максимальное возможное значение размера фиксации, чтобы сократить накладные издержки при записи многих пакетов.
• Используйте размер фиксации, меньший 5000, чтобы избежать эскалации блокировки при вставке; не забывайте, что в SQL Server 2008 теперь можно включать и отключать эскалацию блокировок на уровне объектов, но этой функцией следует пользоваться осторожно.
• * Неиндексированная вставка обычно быстрее, чем с применением кластеризованного индекса. Это означает, что при изменении значительной части таблицы назначения может быть выгодно удалить индексы, а затем пересоздать их; чтобы выяснить, какой из способов быстрее, необходимо проверить длительность операции вставки без удаления индексов и с удалением и последующим перестроением всех индексов.
• Используйте секционирование и команду секционирования SWITCH; то есть загрузите рабочую таблицу, содержащую одну секцию, и переключите ее командой SWITCH в главную таблицу после построения индексов и наложения ограничений.
• Еще одна очень полезная ссылка от рабочей группы по производительности SQL — Как добиться оптимальной производительности при работе уточняющих запросов служб интеграции.

5. Оптимизируйте производительность сети

Одним из ключевых свойств сети является размер передаваемых в соединении пакетов. По умолчанию он равен 4096 байт. Это значит, что для каждых 4 КБ данных собирается отдельный сетевой пакет. Как указано в описании свойства SqlConnection.PacketSize библиотеки классов .NET Framework, при увеличении размера пакета производительность возрастет, так как для передачи больших наборов данных потребуется меньше операций чтения и записи. 

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

Так как при работе служб интеграции передаются большие объемы данных, желательно минимизировать сетевые накладные расходы. Максимальную скорость обеспечивает значение размера пакета, равное 32К (32767 байт). Хотя размер сетевых пакетов можно настроить на уровне сервера с помощью хранимой процедуры sp_configure, делать этого не следует. У администратора базы данных могут быть причины для использования на сервере параметра размера, отличного от 32K. Вместо этого измените настройки сервера в диспетчере соединений, как показано ниже.

Кроме того, чтобы настроить сеть, можно использовать настройки сетевого сходства (network affinity) на уровне операционной системы. При использовании значительной пропускной способности это иногда помогает повысить производительность.

Что касается собственно сети, можно, совместно с сетевыми администраторами , включить поддержку в сети кадров крупного размера (jumbo frames), при этом изменив объем фрейма от размера по умолчанию, составляющего 1500 байт, до 9000 байт. Применяя фреймы крупного размера, можно дополнительно уменьшить количество сетевых операций, необходимых для перемещения больших наборов данных. 

6. Используйте типы данных продуманно

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

• Используйте как можно более малые типы данных, тогда для преобразования потребуется выделять меньше памяти.
• Не злоупотребляйте приведением типов данных — это лишь ухудшит производительность. Используйте типы данных, соответствующие типам в источнике или назначении, а необходимые приведения типов задавайте явным образом.
• Следите за точностью при использовании типов money, float и decimal. Кроме того, не забывайте, что тип money работает быстрее, чем тип decimal, при этом тип money позволяет меньше заботиться о точности, чем тип float.

7. Измените структуру решения

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

• Не выполняйте сортировку внутри служб интеграции без крайней необходимости. Чтобы выполнить сортировку, службы интеграции выделяют в памяти пространство для всего преобразуемого набора данных. По возможности выполните предварительную сортировку данных перед их подачей на конвейер. Если сортировка данных необходима, постарайтесь сортировать на конвейере только маленькие наборы данных. Вместо сортировки данных средствами служб интеграции используйте инструкции SQL с предложением ORDER BY для сортировки больших наборов данных в базе данных; отмечайте выходные данные как отсортированные, изменив метаданные конвейера служб интеграции для источника данных.
• Иногда использование Transact-SQL оказывается более быстрым методом, чем обработка данных в службах интеграции. Как правило, все операции на основе наборов в Transact-SQL выполняются быстрее, так как задачу можно преобразовать в формулировку реляционной алгебры (домены и кортежи), для работы с которой оптимизирован SQL Server. Кроме того, оптимизатор SQL Server автоматически применяет высокую степень параллелизма и управление памятью к операциям на основе наборов — в службах интеграции эти операции, возможно, придется выполнять вручную. Типичными операциями на основе наборов являются следующие:
• Инструкции UPDATE на основе наборов, которые значительно эффективнее построчных вызовов OLE DB. 
• Статистические вычисления, например, GROUP BY или SUM. Обычно с помощью Transact-SQL они выполняются быстрее, чем аналогичные вычисления, выполняемые в оперативной памяти на конвейере.
• Обнаружение разности — это механизм, изменяющий существующие строки в целевой таблице вместо перезагрузки всей таблицы. Для выполнения обнаружения разности можно воспользоваться механизмом обнаружения изменений, например, новой функцией SQL Server 2008 — системой отслеживания измененных данных (CDC). Если эта функция недоступна, обнаружение разности придется выполнять путем сравнения исходных входных данных с целевой таблицей. Такая операция может потребовать значительного объема ресурсов, включая поддержание специальных индексов и контрольных сумм, предназначенных исключительно для этой цели. Часто более быстрым методом будет простая перезагрузка целевой таблицы. Как правило, если целевая таблица изменилась больше, чем на 10%, ее перегрузка может оказаться более быстрой, чем обнаружение разности.

8. Разделите задачу на составляющие

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

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

Чтобы создать диапазоны секций равного размера, используйте в качестве механизма секционирования временной период или измерения (например, географическое расположение). Если первичный ключ является инкрементальным значением, например, IDENTITY или другим значением с приращением, можно воспользоваться функцией остатка от деления. Если столбцы, подходящие для секционирования по ним, отсутствуют, создайте хэш на основе значения строк и выполните секционирование по значению хэша. Дополнительные сведения о хэшировании и секционировании см. в техническом документе Оптимизация числа различных объектов служб Analysis Services; хотя документ посвящен числу различных объектов в службах Analysis Services, в нем также подробно рассматривается техника секционирования по хэшу.

Несколько дополнительных советов по секционированию:

• Применяйте секционирование в целевой таблице. Это позволит параллельно запускать несколько версий одного и того же пакета, вставляющих данные в различные секции целевой таблицы. При использовании секционирования очень полезна инструкция SWITCH. Она не только повышает скорость параллельной загрузки, но и позволяет эффективно перемещать данные. Дополнительные сведения см. в статье электронной документации по SQL Server Эффективная передача данных с помощью переключения секций.
• В соответствии с вышесказанным, пакеты следует разрабатывать таким образом, чтобы они принимали параметр, указывающий обрабатываемую секцию. Это позволит запустить одновременно несколько экземпляров пакета с различными значениями параметра и секции; при этом выполнение задачи ускорится за счет параллелизма
• Запустить несколько выполнений можно из командной строки с помощью команды START. Краткий пример кода, запускающего параллельно несколько инструкций robocopy, можно найти в технической статье Образец скрипта Robocopy для пользовательской синхронизации баз данных служб Analysis Services.
• Большинство современных серверов — это многоядерные SMP-системы. Для полной загрузки процессоров на таком сервере необходимо применение высокого уровня параллелизма. Оно обеспечивается секционированием задачи и одновременным выполнением многих процессов. Дополнительные сведения о том, как это реализуется, см. в статье Мировой рекорд производительности SSIS ETL.

9. Минимизируйте количество журналируемых операций

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

Поэтому при разработке пакетов служб интеграции учитывайте следующее:

• Постарайтесь обрабатывать все потоки данных в массовом режиме, а не построчно. В массовом режиме минимизируется число записей, добавляемых в файл журнала. Это сокращение освободит ресурсы ввода-вывода для прочих операций вставки и минимизирует ограничения по производительности, вызываемые записью в журнал.
• Если необходимо выполнять операции удаления, организуйте данные таким образом, чтобы можно было обрезать (TRUNCATE) таблицу вместо выполнения операции DELETE. В последнем случае для каждой удаляемой строки в журнал будет вноситься запись, в то время как при обрезании данные из таблицы просто удаляются и в журнал вносится единственная запись о факте выполнения TRUNCATE. Вопреки устоявшемуся мнению, инструкция TRUNCATE может участвовать в транзакции.
• Используйте инструкцию SWITCH и секционирование. Если необходимо переместить секции, можно использовать инструкцию SWITCH (чтобы включить новую секцию или исключить самую старую), при этом в журнал вносится минимальное количество записей.  
• Будьте осторожны при использовании инструкций DML; при совмещении инструкций DML с инструкциями INSERT минимальное журналирование прекращается.

10. Правильно планируйте и распределяйте выполнение задачи

Разбив задачу на удобные в обработке фрагменты, следует решить, где и когда эти фрагменты будут выполняться. Самое важное при этом — не допустить, чтобы одна долго выполняющаяся задача задерживала выполнение всего процесса ETL.

Хороший вариант управления выполнением — создание очереди приоритетов для пакета с последующим выполнением многих экземпляров этого пакета (с различными значениями параметра секции). Очередь может быть просто таблицей SQL Server. Поток управления каждого из пакетов должен содержать следующий простой цикл:

1. Выбрать актуальный фрагмент данных из очереди:

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

2. Выполнить необходимую операцию над фрагментом данных.3. Пометить фрагмент в очереди как «готовый».4. Вернуться к началу цикла.

Выбор части данных из очереди и пометку части данных как «готовой» (шаги 1 и 3 выше) можно реализовать в виде хранимой процедуры.

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