русский

10 главных причин использовать SQL Server 2008 для обеспечения высокой производительности решений бизнес-аналитики

AlexeiK — Mon, 20 Dec 2010 18:37:00 GMT

Автор: Карл Рэбелер (Carl
Rabeler)
Редакторы: Марк Соуза (Mark Souza), Прем Мехра (Prem Mehra),
Линдсей Аллен (Lindsey Allen), Майк Уинер (Mike Weiner), Джеймс Подгорски
(James Podgorski), Дональд Фармер (Donald Farmer), Роберт Брюкнер (Robert
Bruckner), Санджай Мишра (Sanjay Mishra), Джеймс Подгорски (James Podgorski),
Лукаш Павловски (Lukasz Pawlowski), Джефф Бернхардт (Jeff Bernhardt), Николас
Дрицас (Nicholas Dritsas)

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

Важно. Сведения
об обновлении существующих решений бизнес-аналитики до SQL Server 2008 см. в
документе Технический
справочник. Обновление SQL Server 2008.

Усовершенствования масштабируемости и управления
ресурсами в службах Reporting Services

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

В службах SQL Server 2008
Reporting Services компонент Report Engine был переработан с тем, чтобы службы
Reporting Services могли управлять ресурсами (потоки, память и состояние),
чтобы обеспечить наличие достаточного объема памяти для выполнения очень больших
отчетов и обработки значительной нагрузки от большого числа одновременно
работающих пользователей (за счет разбиения на страницы и высвобождения памяти
в случае ее нехватки). Для достижения этого используются следующие средства:

· Встраивание в компонент Report Engine
возможностей HTTP и удаление зависимости служб Reporting Services от служб IIS.
В результате этого настраивать службы Reporting Services стало намного проще,
поскольку они изолированы от других веб-приложений.

· Вычисление элементов отчета по запросу.

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

При работе со службами SQL Server
2008 Reporting Services в нашей лаборатории было отмечено, что в сравнении со
службами SQL Server 2005 Reporting Services службы SQL Server 2008 Reporting
Services обслуживали в 3–4 раза больше пользователей и их запросов на таком же
аппаратном обеспечении без ошибок HTTP 503 «Служба недоступна» независимо от
типа модуля подготовки отчетов. В отличие от служб SQL Server 2008 Reporting
Services, по мере роста числа пользователей и их запросов независимо от типа
модуля подготовки отчетов службы SQL Server 2005 Reporting Services выдавали
огромное количество ошибок HTTP 503 «Служба недоступна».

Наши тесты ясно показали, что
новая архитектура управления ресурсами сервера отчетов позволяет службам SQL
Server 2008 Reporting Services выполнять масштабирование, особенно на новых
компьютерах с четырьмя четырехъядерными процессорами. При тестировании рабочей
нагрузки службы SQL Server 2008 Reporting Services постоянно оказывались лучше
SQL Server 2005 при использовании модулей подготовки PDF и XLS на платформе с
четырьмя четырехъядерными процессорами (16 ядер) как по времени ответа, так и
по общей пропускной способности. По результатам этих испытаний для служб SQL
Server 2008 Reporting Services мы рекомендуем увеличить объем аппаратных
ресурсов до четырех четырехъядерных процессоров для обеспечения производительности
и распределить нагрузку на два узла для обеспечения высокого уровня
доступности. В дальнейшем по мере возникновения потребности в большей
вычислительной мощности подключайте дополнительные серверы с четырьмя
четырехъядерными процессорами. При использовании служб SQL Server 2005
Reporting Services из-за нехватки ресурсов рекомендуется увеличить число
процессоров больше четырёх. Тесты четко свидетельствуют, что службы SQL Server
2008 Reporting Services используют аппаратные ресурсы более эффективно, чем
службы SQL Server 2005 Reporting Services, особенно при использовании новых
серверов с четырьмя четырехъядерными процессорами. Дополнительные сведения см.
в статье Сравнение
масштабируемости служб Reporting Services 2008 и Reporting Services 2005:
полученные уроки.


    Вычисление в подпространстве в службах Analysis
Services

Обработчик формул служб SQL Server
Analysis Services для получения запрошенных данных разрабатывает план
выполнения запроса. Каждый узел плана выполнения запроса использует один из
двух типов режимов оценки: режим вычисления в подпространстве или режим оценки
«ячейка за ячейкой». При использовании режима вычисления в подпространстве
обработчик формул служб Analysis Services использует только ячейки с данными,
которые оказывают влияние на результат. При использовании же режима «ячейка за
ячейкой» обработчик формул служб Analysis Services использует все ячейки,
которые теоретически могли бы оказать влияние на результат, без учета того,
содержат ли они данные или нет. Получение данных в разреженном кубе, который
является стандартной структурой, с помощью режима оценки «ячейка за ячейкой»
является неэффективным для запросов, требующих значительных вычислений,
поскольку обрабатывается много пустых ячеек, не влияющих на результат.

До выхода пакета обновления 2
(SP2) в SQL Server 2005 планы выполнения запросов, выработанные обработчиком
формул служб Analysis Services, главным образом состояли из узлов, которые
использовали режим оценки «ячейка за ячейкой». В пакете обновления 2 (SP2) SQL
Server 2005 режим вычисления в подпространстве поддерживается небольшим
количеством функций многомерного выражения. В службах SQL Server 2008 Analysis
Services подавляющее большинство функций многомерного выражения используют
режим вычисления в подпространстве. При работе с нашими клиентами в отношении
служб SQL Server 2008 Analysis Services мы наблюдаем значительный рост
производительности при работе со следующими объектами:

· Разреженные кубы

· Кубы со сложными вычислениями

Этот рост производительности при
использовании служб SQL Server 2008 Analysis Services достигается без внесения
изменений в решение бизнес-аналитики. Кроме того, также можно добиться
дальнейшего роста производительности кубов со сложными вычислениями путем
дополнительной настройки вычислений многомерных выражений в кубах.
Дополнительные сведения см. в статьях Повышение
производительности многомерных выражений в службах SQL Server 2008 Analysis
Services и Руководство
по повышению производительности служб SQL Server 2005 Analysis Services.


Параллелизм конвейеров в службах Integration
Services

В службах SQL Server 2005
Integration Services каждому дереву выполнения в задаче DataFlow в пакете
обычно назначается один рабочий поток. Кроме того, при определенных условиях
каждое дерево выполнения фактически использует один и тот же поток одновременно
с другими деревьями выполнения. Такой подход имеет следующие преимущества:

· Планирование потоков производится до выполнения
пакета, во время выполнения на планирование время не тратится.

· Данные буфера остаются в кэше, поскольку поток
использует полный буфер на всем дереве выполнения.

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

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

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

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

В службах SQL Server 2008
Integration Services параллелизм конвейеров усовершенствован путем внедрения
улучшений в архитектуру подсистемы обработки потока данных. Теперь рабочие
потоки динамически назначаются отдельным компонентам из общего пула потоков. В
результате этого в многоядерных компьютерах подсистема обработки потока данных
автоматически задействует все ядра, при этом прибегать к каким-либо трюкам не
требуется. За счет улучшения параллелизма конвейеров в службах SQL Server 2008
Integration Services с помощью пула потоков повышается скорость выполнения
пакета, при этом имеющиеся ресурсы лучше используются при условии достаточности
других ресурсов (например, ввода/вывода или памяти). В сотрудничестве с
клиентами, использующими службы SQL Server 2008 Integration Services, мы
выяснили, что при обработке пакетов с длинными цепями синхронных
преобразований, а также большинства пакетов, выполняющихся на многопроцессорных
компьютерах, производительность значительно возросла.

Примечание. Дополнительные сведения о службах SQL Server 2008
Integration Services от группы разработчиков SQL Server Integration Services
см. в коллективном блоге SSIS Team Blog.
Дополнительные сведения о производительности см. в статьях Мировой
рекорд ETL и 10
основных рекомендаций по службам SQL Server Integration Services.


Усовершенствования масштабируемости и готовности
в службах Analysis Services

В SQL Server 2005 резервные копии больших баз данных Analysis Services
обладают ограниченной масштабируемостью, а службы Analysis Services
поддерживают только одно расположение для всех баз данных Analysis Services в
экземпляре служб Analysis Services, хотя секции для групп мер в базе данных
можно распределять по нескольким дискам для распределения ввода/вывода. Более
того, для повышения масштабируемости и готовности, после обработки одной базы
данных для экземпляра служб Analysis Services на сервере обработки эту базу
данных нельзя отсоединить, а затем присоединить ее к серверу запросов. Кроме
того, в службах SQL Server 2005 Analysis Services изначально отсутствует
поддержка присоединения доступной только для чтения копии базы данных Analysis
Services к нескольким серверам для повышения масштабируемости и готовности.
Такая возможность поддерживается только за счет использования технологий
моментальных снимков SAN (см. статью Масштабное
развертывание запросов в службах Analysis Services с помощью моментальных
снимков SAN).

В службах SQL Server 2008 Analysis Services эти проблемы решены за счет
следующих усовершенствований масштабируемости и готовности.

· Можно создавать резервную копию базы данных
служб Analysis Services любого размера. Производительность резервных копий баз
данных служб Analysis Services масштабируется линейно, она сравнима с
резервными копиями на уровне файлов.

· В экземпляре служб Analysis Service каждую базу
данных можно развернуть в собственном месте хранения, что позволяет размещать
каждую базу данных Analysis Service из экземпляра на отдельном диске.

· Можно отсоединять всю базу данных из экземпляра
служб Analysis Service, копировать или перемещать ее в новое расположение, а
затем присоединять ее к другому экземпляру Analysis Service. Эта функция
повышает масштабируемость и производительность, позволяя обрабатывать новые
данные для базы данных служб Analysis Service на сервере обработки, при этом
пользователи будут запрашивать текущую версию базы данных служб Analysis
Service, расположенную на сервере запросов. После завершения обработки
обработанную базу данных можно отсоединить от сервера обработки и присоединить
ее к серверу запросов. Более того, при использовании двух экземпляров служб
Analysis Services на сервере запросов и подсистемы балансировки загрузки для
распределения запросов соответствующему экземпляру можно направлять новые
соединения пользователей к вновь присоединенной базе данных, не влияя на
готовность, поскольку подключенные в данный момент пользователи могут завершить
свои выполняющиеся в данный момент запросы к ранее присоединенной базе данных.
Эта функция также позволяет отсоединять базу данных от экземпляра служб Analysis
Services в целях архивирования или резервного копирования.

· Для повышения масштабируемости и готовности базу
данных служб Analysis Services можно присоединить в режиме доступа только для
чтения к нескольким экземплярам служб Analysis Services. Доступная только для
чтения база данных может храниться в сети хранения данных (SAN) и монтироваться
на нескольких экземплярах, либо она может совместно использоваться посредством
общей папки файловой системы и монтироваться на нескольких экземплярах.

Примечание. При работе с большими базами данных, в которых секции
истории не изменяются, для клонирования отсоединенной базы данных потребуется
использовать функции высокоскоростного копирования SAN либо Robocopy.
Дополнительные сведения об использовании Robocopy см. в статье Образец
сценария Robocopy для пользовательской синхронизации баз данных служб Analysis
Services.

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




    Оптимизированные возможности конструирования в
службах Analysis Services

Решения служб SQL Server Analysis
Services конструируются в среде Business Intelligence Development Studio. В SQL
Server 2005 при разработке решения служб Analysis Services в среде Business
Intelligence Development Studio очень легко создать решение, которое не
соответствует рекомендациям по достижению наилучшей производительности, в
результате чего могут возникнуть проблемы с производительностью. Например,
отсутствие нужных связей атрибутов будет значительной недоработкой и может
ухудшить производительность. Однако среда SQL Server 2005 Business Intelligence
Development Studio не уведомляет об этом разработчика, который не создал такие
связи. Кроме того, во время работы со службами SQL Server 2005 Analysis
Services для достижения оптимальной производительности статистические выражения
часто приходится конструировать вручную.

В службах SQL Server 2008
Analysis Services в среду разработки Business Intelligence Development Studio
для обеспечения соблюдения рекомендаций и помощи разработчикам в создании
высокопроизводительных решений были внесены следующие изменения.

· В объектную модель AMO добавлены уведомления о
рекомендациях, называемые предупреждениями AMO. Эти сообщения с
предупреждениями формируются в среде Business Intelligence Development Studio,
чтобы уведомить разработчиков, когда их конструкции не соответствуют какой-либо
из более чем 40 рекомендаций. Эти предупреждения интегрированы в проверки
конструктора, проводимые в реальном времени, они используют синие волнистые
линии. Ненавязчиво (на них можно не обращать внимания) они позволяют
разработчикам видеть потенциальные проблемы конструкций задолго до их
развертывания. С помощью этой новой функции можно также анализировать
соответствие существующих кубов служб SQL Server 2005 Analysis Services
рекомендациям по разработке. Для этого нужно либо открыть базу данных Analysis
Services в среде Business Intelligence Development Studio версии SQL Server
2008, либо запустить последнюю версию анализатора соответствия рекомендациям
для SQL Server 2005 (в котором также имеются эти предупреждения AMO). Доступ к
этим предупреждениям AMO можно получить из PowerShell или пользовательского
клиентского приложения.

· Был добавлен новый конструктор связей атрибутов
для просмотра и редактирования связей атрибутов. Конструктор имеет встроенные
проверки, помогающие создавать оптимальные конструкции измерений.

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

· В среду Business Intelligence Development Studio
был добавлен новый конструктор агрегатов с новым алгоритмом создания новых
агрегатов. Конструктор агрегатов оптимизирован для работы со статистическими
выражениями. Теперь легко анализировать существующие статистические выражения,
дополнять эти выражения, удалять или заменять их. Для помощи в объединении
существующих и новых статистических схем предоставляется интеллектуальная
поддержка. Дополнительные сведения о новом конструкторе агрегатов и его
использовании со статистическими выражениями, основанными на использовании, см.
в статье Новые
возможности оптимизации на основе использования в службах SQL Server 2008
Analysis Services

Новые и улучшенные возможности разработки и
визуализации в службах Reporting Services

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

Службы SQL Server 2008 Reporting
Services предлагают множество улучшений для разработки, которые повышают
продуктивность работы разработчика. Кроме того, теперь в службы включены
полнофункциональные версии диаграммы Dundas и элементов управления в виде
датчиков, что позволяет создавать красивые диаграммы и датчики.

Улучшения для разработки

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

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

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

· Улучшенные возможности конструирования.
Конструктор отчетов в Microsoft Visual Studio переработан для упрощения работы
конструкторов.

· Среда разработки «Построитель отчетов 2.0». Эта
изолированная среда разработки имеет наглядный и знакомый вид Microsoft Office.

Улучшения диаграмм

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

· Ступенчатый график

· Область сплайн

· Круговая диаграмма

· Полярная диаграмма

· Лепестковая диаграмма

· Диаграмма Ганта

· Линия/столбец диапазона

· Воронкообразная диаграмма

· Пирамидальная диаграмма

· Гистограмма

· Блок

Датчики

Ниже перечислены новые типы датчиков:

· Круглый

· Линейный

· Угловой

· Термометр

· Числовые индикаторы

· Индикаторы состояния

Дополнительные сведения см. в
статьях Службы
Reporting Services и Новые возможности
(службы Reporting Services).

Сжатие данных в реляционном модуле

Подсистема дискового ввода/вывода является одним из наиболее часто
встречающихся узких мест многих реляционных хранилищ данных. Для сокращения
времени чтения/записи часто добавляются новые диски. Это может быть затратно,
особенно для высокопроизводительных систем хранения. В то же время потребность
в пространстве для хранения продолжает расти в силу быстрого увеличения объема
данных. Одновременно растут и затраты на обслуживание баз данных (резервное
копирование, восстановление, перенос и так далее).

В SQL Server 2008 для решения некоторых из этих проблем
используется сжатие данных. С помощью механизма сжатия данных можно выборочно
сжимать любую таблицу, секцию таблицы или индекс, при этом будет высвобождаться
место на диске, требоваться меньше памяти и ресурсов ввода/вывода. Работая с
клиентами, мы отмечаем, что во многих средах возможность сжатия данных
позволила сэкономить 50–80 % места на диске. Эта экономия места на диске
сразу же преобразуется в меньший объем памяти, который требуется для
высокоскоростного дискового хранилища. В реляционных хранилищах данных, где
узким местом является подсистема ввода/вывода и мощности процессора
задействуются не полностью, можно также ожидать роста общей производительности.

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

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

Чтобы определить, какой размер будет у объекта после его сжатия,
используйте системную хранимую процедуруsp_estimate_data_compression_savings . Сжатие баз
данных поддерживается только в выпусках SQL Server 2008 Enterprise и Developer.
Оно полностью управляется на уровне базы данных и не требует каких-либо
изменений в приложениях.

Регулятор ресурсов в реляционном модуле

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

Пример.

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

· Некоторые рабочие нагрузки выигрывают от уменьшения
параллелизма. Во время обработки секций службы SQL Server Analysis Services
могут выдавать большое количество параллельных запросов. Для обеспечения
наилучшей производительности рекомендуется так настроить задания на обработку в
службах Analysis Services, чтобы они выдавали по одному запросу на ядро
компьютера с реляционным хранилищем данных. Однако, если реляционный механизм
распараллеливает каждый из таких обрабатывающих запросов, потоки могут начать
пробуксовывать. Одним из решений является выполнение каждого обрабатывающего
запроса с использованием параметра MAXDOP=1. Но до выхода SQL Server 2008 это
был серверный параметр, который применялся ко всем запросам, поскольку добавить
подсказку MAXDOP в обрабатывающие запросы служб Analysis Services было нельзя.
Использование параметра MAXDOP=1 для всех запросов оказывало отрицательное
влияние на запросы, которые значительно выигрывали от распараллеливания, из-за
чего приходилось выбирать, запросы какого типа оптимизировать.

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

На
приведенной ниже диаграмме показан процесс распределения ресурсов. В этом
сценарии настроены три пула рабочих нагрузок (рабочая нагрузка Admin, рабочая
нагрузка OLTP и рабочая нагрузка Report), а пулу рабочей нагрузки OLTP присвоен
высокий приоритет. Параллельно настроены два пула ресурсов (пул Admin и пул
Application), имеющие определенные ограничения по памяти и процессору (ЦП), как
показано на диаграмме. Наконец, рабочая нагрузка Admin распределяется пулу
Admin, а рабочие нагрузки OLTP и Report распределяются пулу Application.

Ниже
приведены некоторые особенности, которые следует учитывать при использовании
регулятора ресурсов.

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

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

· Регулятор ресурсов позволяет управлять ресурсами только
в одном экземпляре SQL Server. Для управления несколькими экземплярами можно
использовать Системный диспетчер ресурсов Windows.

· Настраивать можно только использование ресурсов процессора и
памяти. Управление ресурсами ввода-вывода не реализовано.

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

· Регулятор ресурсов поддерживается только в выпусках SQL
Server 2008 Enterprise и Developer и может использоваться только для компонента
SQL Server Database Engine. Использованием ресурсов службами SQL Server
Analysis Services, службами SQL Server Integration Services и службами SQL
Server Reporting Services с помощью регулятора ресурсов управлять нельзя. Для
управления этими службами SQL Server можно использовать Системный диспетчер ресурсов Window

  Минимально
протоколируемые операции в реляционном модуле

Хотя
операции массовой загрузки имеют значительные преимущества по
производительности относительно построковых операций, протоколирование этих
операций массовой загрузки (обычно вставки) при загрузке больших объемов данных
часто является причиной возникновения узких мест при вводе/выводе на диск. В
отличие от полностью протоколируемых операций, которые используют журнал
транзакций для отслеживания всех измененных строк, операции с минимальным
протоколированием отслеживают только выделение экстентов и страниц, а также
изменение метаданных. Поскольку в журнал транзакций заносится намного меньше
данных, операции с минимальным протоколированием часто выполняются быстрее, чем
полностью протоколируемые операции, если протоколирование является узким
местом. Более того, так как в журнал транзакций заносится меньше записей,
формируется файл журнала намного меньшего размера, требующий меньше ресурсов
ввода/вывода. Операции с минимальным протоколированием возможны, только если
база данных находится в режиме восстановления с неполным протоколированием или
в режиме простого восстановления. Дополнительные сведения см. в разделе Операции, для которых возможно минимальное
протоколирование. Следует
отметить, что выполнение массовой операции в базе данных с неполным
протоколированием влияет на стратегию резервного копирования для этой базы
данных. Кроме того, откат операции с минимальным протоколированием
протоколируется полностью, в результате чего на откат операции с минимальным
протоколированием может уйти значительно больше времени, чем на саму операцию. Дополнительные
сведения о последствиях см. в документе Резервное копирование при модели
восстановления с неполным протоколированием.

В
SQL Server 2008 в качестве нового способа выполнения операций вставки с
минимальным протоколированием появилась инструкция INSERT…SELECT, позволяющая
инструкциям INSERT на основе Transact-SQL в определенных условиях иметь
минимальное протоколирование.

· Кучи — инструкция INSERT, которая берет строки
из операции SELECT и вставляет их в кучу, минимально протоколируется, если в
целевой таблице используется подсказка WITH (TABLOCK).

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

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

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

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

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

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

В
этом примере страницы со значениями ключей 8–15 (выделены светло-синим цветом)
будут минимально протоколироваться с флагом трассировки 610.

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

  Другие полезные функции

В
SQL Server 2008 появились следующие дополнительные функции, которые улучшают
производительность решений бизнес-аналитики.

Улучшения
запросов типа «звезда»

Большинство запросов к хранилищу данных
сконструированы по схеме «звезда» и способны обрабатывать сотни миллионов строк
за один запрос. По умолчанию оптимизатор запросов сравнивает запросы со схемами
«звезда» и создает для них эффективные планы запроса. Одним из методов создания
эффективного плана запроса является применение битовой фильтрации. Фильтр по
битовым картам использует компактное представление набора значений из таблицы,
находящейся в одной части дерева операторов, для фильтрации строк из другой
таблицы, находящейся в другой части дерева. В сущности, фильтр выполняет
сокращение полусоединением, то есть обрабатываются только те строки второй
таблицы, которые удовлетворяют условию объединения с первой таблицей. В SQL
Server 2008 битовые фильтрации можно ввести в план запроса после оптимизации,
как в SQL Server 2005, или динамически с помощью оптимизатора запросов во время
создания плана запроса. Если фильтр создается динамически, его называют
оптимизированным фильтром по битовым картам. Оптимизированная битовая
фильтрация может значительно повысить производительность запросов к хранилищу
данных, использующих схему «звезда», удаляя не соответствующие условию строки
на раннем этапе плана запроса. Без оптимизированной битовой фильтрации в
некоторой части дерева операторов обрабатываются все строки в таблице фактов
перед тем, как операция объединения с таблицей измерения удалит не
соответствующие условию строки. Если применяется оптимизированная битовая
фильтрация, не соответствующие условию строки в таблице фактов удаляются
немедленно. Оптимизированная фильтрация по битовым картам доступна только в
следующих выпусках SQL Server: Enterprise, Developer и Evaluation.
Дополнительные сведения см. в документе Оптимизация производительности запросов к
хранилищу данных с помощью битовой фильтрации.

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

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

Инструкция
MERGE

В SQL
Server 2008 несколько операций языка обработки данных DML можно выполнить,
используя одну инструкцию MERGE. Например, может потребоваться синхронизировать
две таблицы путем вставки, обновления или удаления строк в одной таблице на
основании отличий, найденных в другой таблице. Как правило, это производится
путем выполнения хранимой процедуры или пакета, содержащего отдельные
инструкции INSERT, UPDATE и DELETE. Однако это означает, что данные и в
исходных и в целевых таблицах вычисляются и обрабатываются несколько раз — по
крайней мере один раз для каждой инструкции. При помощи инструкции MERGE можно
заменять отдельные инструкции DML одной инструкцией. Это может улучшить
производительность запросов, так как операции выполняются внутри одной
инструкции. Соответственно, количество обработок данных в исходных и целевых
таблицах снижается. Однако увеличение производительности зависит от наличия
правильных индексов, соединений и других факторов. Дополнительные сведения см.
в разделе Оптимизация производительности инструкции
MERGE.

Отслеживание
измененных данных

В SQL Server 2008 эта новая функция
обеспечивает простой способ отслеживания изменений данных, которые находятся в
таблицах базы данных, чтобы эти изменения можно было перенести во вторую
систему, например хранилище данных. Система отслеживания измененных данных
регистрирует действия по вставке, обновлению и удалению, применяемые к таблицам
SQL Server, сохраняя подробности операций изменения в легко обрабатываемом
реляционном формате. Таблицы изменений, используемые системой отслеживания
измененных данных, содержат столбцы, отражающие структуру столбцов
отслеживаемой исходной таблицы, а также метаданные, необходимые для понимания
того, какие изменения произошли. Система отслеживания измененных данных
доступна только в следующих выпусках SQL Server: Enterprise, Developer и
Evaluation. Дополнительные сведения см. в статье Настройка производительности системы
отслеживания изменения данных в SQL Server 2008.

ExecutionLog2

В SQL Server 2008 база данных
служб Reporting Services хранит дополнительные сведения, которые позволяют
анализировать данные журнала выполнения отчетов для поиска ответов на следующие
вопросы о производительности.

· Какие отчеты следует кэшировать?

· Сколько отчетов было возвращено из кэша по
сравнению с выполнением в реальном времени и созданием моментального снимка
выполнения?

· Какой отчет использовался чаще всего в течение
указанного периода времени?

· Какие отчеты имеют самую низкую
производительность?

· Какие отчеты выполнялись службами SQL Server
2008 Reporting Services, а какие — службами SQL Server 2005 Reporting Services?

· Для каких отчетов отмечалась нехватка памяти?

Эти подробные сведения отображаются в
представлении ExecutionLog2. Дополнительные сведения см. в статье Представление ExecutionLog2 — анализ и
оптимизация отчетов.

Заключение

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

Уроки по сравнению масштабируемости служб Reporting Services 2008 и Reporting Services 2005

AlexeiK — Mon, 20 Dec 2010 14:13:00 GMT

Автор: Карл Рэбелер (Carl Rabeler)

Разработчики теста: Джим Кэррол (Jim Carroll), Дэвид Раскино (David Raskino),
Билл Эммерт (Bill Emmert), Дэн Марник (Dan Manrique), Джим Даган (Jim Dugan)

Технические редакторы: Роберт Бракнер (Robert Bruckner), Дэвид Раскино (David
Raskino), Лукаш Павловски (Lukasz Pawlowski), Барзин Пэтел (Burzin Patel), Кун
Ченг (Kun Cheng), Терри Д’Хёрз (Thierry D'Hers), Прем Мехра (Prem Mehra), Кевин
Кокс (Kevin Cox)

Введение

Службы Microsoft SQL Server 2008
Reporting Services включают множество улучшений, направленных на повышение
производительности и мощности. Преобразование архитектуры сервера служб
Reporting Services 2008 в одиночный сервер со внедренными возможностями HTTP
предоставляет ему больше возможностей по управлению ресурсами (потоками,
памятью и состоянием), повышает производительность, масштабируемость и
возможности настройки. Новая архитектура ядра подготовки отчетов включает новую
модель обработки по требованию, обеспечивающую достаточный объем памяти для
выполнения очень больших отчетов и обработки значительной нагрузки от большого
числа одновременно работающих пользователей (за счет разбиения на страницы и
высвобождения памяти в случае ее нехватки). На ранних неоптимизированных
сборках RC0 проводилось тестирование производительности служб SQL Server 2008
Reporting Services в сравнении с оптимизированной сборкой SQL Server 2005
Reporting Services с пакетом обновления 2 (SP2) на нескольких расширяющихся
аппаратных платформах. В этой технической статье обсуждаются эти тесты и
анализируются полученные уроки.

Резюме

На одном и том же оборудовании
службы Reporting Services 2008 могут в 3–4 раза больше обслуживать
пользователей и обрабатывать запросов до появления ошибки HTTP 503: «Служба
недоступна» по сравнению с Reporting Services 2005, независимо от типа модуля
подготовки отчетов. В отличие от служб SQL Server 2008 Reporting Services по
мере роста числа пользователей и их запросов службы SQL Server 2005 Reporting
Services выдавали огромное количество ошибок HTTP 503 «Служба недоступна»
независимо от типа модуля подготовки отчетов.

Наши тесты ясно показывают, что
новая архитектура управления памятью сервера отчетов позволяет эффективно
масштабировать службы SQL Server 2008 Reporting Services, особенно на новых
компьютерах с четырьмя четырехъядерными процессорами. При тестировании рабочей
нагрузки службы Reporting Services 2008 постоянно оказывались лучше SQL Server
2005 при использовании модулей подготовки PDF и XLS на платформе с четырьмя
четырехъядерными процессорами (16 ядер) как по времени ответа, так и по общей
пропускной способности. Более того, с этими модулями подготовки данных на этой
аппаратной платформе службы Reporting Services значительно опережали по
производительности другие аппаратные платформы, независимо от версии служб
Reporting Services, обрабатывая в 3–5 раз большее число запросов, чем при
работе на других аппаратных платформах. По результатам этих испытаний рекомендуется
увеличить ресурсы оборудования до четырех четырехъядерных процессоров, чтобы
обеспечить высокую производительность и распределить нагрузку на два сервера,
развертывая высокий уровень доступности. В дальнейшем, по мере возникновения
потребности в большей вычислительной мощности, подключайте дополнительные
серверы с четырьмя четырехъядерными процессорами.

Наконец, со всеми модулями
подготовки данных и на всех аппаратных платформах тестового окружения для служб
Reporting Services 2008 узкими местами оказались процессор на сервере
приложений и дисковая подсистема источника данных, тогда как для служб
Reporting Services 2005 узким место оказалась веб-служба Reporting Services,
обеспечивающая клиентский доступ.

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

· Планирование масштабируемости и производительности служб Reporting Services

· Архитектура масштабного развертывания служб Reporting Services

Среда
тестирования

Для всех тестов использовалась
стандартная среда с удаленным размещением каталога. На рисунке 1 показана среда
тестирования для проверки производительности масштабного развертывания. Для
тестирования служб Reporting Services 2008 на всех серверах были установлены
компоненты SQL Server 2008, а для тестирования служб Reporting Services 2005 —
компоненты SQL Server 2005. Все тесты проводились в операционной системе
Windows Server 2003.

Рис. 1. Архитектура масштабирования
служб Reporting Services с удаленным каталогом и удаленным источником данных

Серверы
веб-интерфейса служб Reporting Services

Для сервера веб-интерфейса служб
Reporting Services тестирование Reporting Services 2008 и Reporting Services
2005 производилось на следующих трех аппаратных платформах.

· Конфигурация оборудования №1
(2x2): один компьютер HP Proliant BL 460c G1 с двумя двухъядерными процессорами
x64 и ОЗУ 16 ГБ

· Конфигурация оборудования №2
(4x2): один компьютер Dell 2970 с четырьмя двухъядерными процессорами x64 и ОЗУ
16 ГБ

· Конфигурация оборудования №3
(4x4): один компьютер HP Proliant BL 460c G5 с четырьмя четырехъядерными
процессорами x64 и ОЗУ 16 ГБ

Серверы
каталога сервера отчетов

Во всех тестах в качестве сервера
каталога для сервера отчетов использовался компьютер HP Proliant BL 460c G1 с
двумя двухъядерными процессорами x64 и ОЗУ 8 ГБ. Сервер каталога для сервера
отчетов был подключен к сети хранения данных EMC Clarion CX3 Model 40.

Сервер
источника данных для отчетов

Во всех тестах в качестве сервера
источника данных для сервера отчетов использовался один компьютер HP Proliant
BL 460c G5 с четырьмя четырехъядерными процессорами x64 и ОЗУ 16 ГБ. Сервер
источника данных для сервера отчетов был подключен к сети хранения данных EMC
Clarion CX3 Model 40.

Внимание!. Во всех тестах использовалась одна и та же конфигурация
сервера каталогов и сервера источника данных для сервера отчетов. Тестирование
служб Reporting Services 2008 показало, что узким местом производительности
является диск, поэтому для получения оптимального уровня производительности для
служб Reporting Services 2008 нужно уделять больше внимания дисковой
конфигурации, чем в случае с Reporting Services 2005. Это относится к дисковой
конфигурации базы данных-источника, а также баз данных ReportServer и ReportServerTempDB.
Дополнительные сведения об оптимизации хранилища для повышения производительности
см. в разделах 10 лучших рекомендаций по хранению и Рекомендации по настройке
ввода-вывода до развертывания.

Тестовое
окружение

Для проверки балансировки нагрузки
на образце реляционной базы данных Adventure Works при выполнении всех тестов
производительности использовалась среда Visual Studio Team System 2008 Test
Edition (VSTS). Для тестирования было подготовлено 50 отдельных отчетов
различной сложности и размера, рассчитанных на различные категории
пользователей — руководитель, территориальный менеджер, менеджер по продуктам и
менеджер по продажам. Нагрузка отчетов состояла из:

· Различных режимов выполнения в
следующей пропорции: 95% интерактивных данных и 5% из моментальных снимков

· Различных типов областей данных в
следующей пропорции: 80% таблицы и 20% диаграммы

· Различных размеров запросов в
следующей пропорции: 60% малых, 20% средних и 20% больших

Все тесты выполнялись на одном и том
же наборе отчетов. Эти отчеты выполнялись через 20-секундные интервалы с
линейным увеличением числа пользователей. Хотя тесты проводились с разным
числом пользователей, в данной технической статье описаны тесты с 5 000
пользователей, которые лучше всего демонстрируют сходство и различия между
службами Reporting Server 2008 и Reporting Services 2005 (тест линейного
наращивания). В конце статьи мы обсудим результаты тестирования отправки
отчетов, в ходе которых была определена производительность отправки служб
Reporting Services 2008 по сравнению с SQL Server 2005, и были выявлены узкие
места в каждой из версий этих служб (тест принудительной отправки). Тесты
производились для следующих модулей подготовки отчетов:

· HTML

· PDF

· XLS

· Смешанный (примерно по 33% для
каждого модуля подготовки отчетов)

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

Результаты
тестирования

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

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

С различными модулями подготовки
отчетов службы Reporting Services 2008 превосходят Reporting Services 2005 на
платформах 4x4 и 4x2, а платформа 4x4 дает незначительное преимущество в
сравнении с платформами 4x2 и 2x2, независимо от версии служб Reporting Services.
На рисунках 2, 3 и 4 представлены такие показатели, как число ошибок в секунду,
среднее время ответа и общее число запросов для этих аппаратных платформ по
каждой из версий служб Reporting Services.

Число
ошибок в секунду

При анализе числа ошибок в секунду в
тесте линейного наращивания обнаружено, что рост количества ошибок HTTP 503
«Служба недоступна» начинается в службах Reporting Services 2005 на всех
аппаратных платформах примерно с 4 500 пользователей (см. рис. 2). При
тестировании служб Reporting Services 2008 на трех аппаратных платформах с теми
же модулями подготовки отчетов мы так и не смогли достичь граничного значения.
Выяснилось, что одно и то же ограничение достигается в службах Reporting
Services 2005 для любого модуля подготовки отчетов, но не достигается в любых
тестах служб Reporting Services 2008. Службы Reporting Services 2008
значительно лучше управляют памятью в условиях высокой нагрузки, чем Reporting
Services 2005, что позволяет устранить перегрузку сервера, обслуживающего
клиентские серверы служб Reporting Services. Рост количества ошибок HTTP 503 в
службах Reporting Services 2005 указывает на то, что высокая нагрузка во время
работы клиентов перегружает сервер, обслуживающего клиентские запросы служб Reporting
Services и приводит к тому, что с некоторого момента ему просто не хватает
ресурсов. Это свидетельствует о том, что управление памятью в условиях высокой
нагрузки является очевидно проблемным местом служб Reporting Services 2005.

Примечание. В число ошибок в секунду включены ошибки HTTP 500 и 503.
Ошибки HTTP 503 в службах Reporting Services свидетельствуют о том, что сама
веб-служба не может отвечать на запросы (часто из-за нехватки памяти). Ошибки
HTTP 500 в службах Reporting Services говорят о том, что веб-службе не удалось
выполнить запрос по неизвестной причине (обычно из-за наличия узкого места в
оборудовании на сервере, обслуживающие клиентские запросы, в сети или
внутреннем сервере). При обсуждении теста принудительной отправки (далее в этой
технической статье) мы более подробно проанализируем обнаруженные в службах
Reporting Services 2008 узкие места, которые приводили к возникновению ошибок
HTTP 500.

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

Среднее
время ответа

При анализе среднего времени отклика
в тесте линейного наращивания с различными модулями подготовки отчетов
обнаружено, что службы Reporting Services 2008 показали лучшее среднее время
ответа, чем службы Reporting Services 2005, на аппаратных платформах 4x4 и 4x2
(см. рис. 3). Выяснилось, что по среднему времени ответа на аппаратной
платформе 2x2 особых отличий нет. Кроме того, было обнаружено, что платформа
4x4 незначительно превосходит две другие аппаратные платформы как для служб
Reporting Services 2008, так и для служб Reporting Services 2005. Кроме того,
было замечено, что улучшение среднего времени ответа служб Reporting Services
2008 на платформе 4x2 по сравнению со службами Reporting Services 2005
становится более значительным по мере увеличения количества запросов от
пользователей.

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

Примечание. Графики для тестов служб Reporting Services 2005 на рисунке
3 отображают время ответа для запросов пользователей только до момента, пока
число ошибок в секунду не приводит к полному искажению общего времени отклика.

Общее
число запросов

Анализ общего числа запросов в тесте
линейного нарастания с различными модулями подготовки отчетов показал, что
службы Reporting Services 2008 без ошибок «Служба недоступна» и значительного
времени ожидания обрабатывают большее число запросов, поступивших от
пользователей. Кроме того, службы Reporting Services 2008 формируют отчеты с
лучшим средним временем ответа и поддерживают более высокую пропускную способность
по сравнению со службами Reporting Services 2005 на аппаратных платформах 4x4 и
4x2 (см. рис. 4). Это лучше всего заметно на аппаратной платформе 4x4, где
выполнение теста заняло примерно на 40% меньше времени, чем для служб Reporting
Services 2005. Новые возможности служб Reporting Services 2008 по управлению
памятью позволили обеспечить отличную производительность на платформе 4x4,
которая значительно превзошла показатели служб Reporting Services 2005 на той
же платформе.

Рис. 4. Различные модули подготовки
отчетов — общее число запросов

Примечание. Графики для тестов служб Reporting Services 2005 на рисунке
5 отображают общее число запросов пользователей только до момента, пока число
ошибок в секунду не приводит к полному искажению этого показателя. На всех
аппаратных платформах службы Reporting Services 2008 продолжали обрабатывать
запросы пользователей даже после того, как службы Reporting Services 2005
начинали постоянно выдавать ошибки HTTP 503 «Служба недоступна».

Тест
линейного нарастания для модуля подготовки отчетов PDF

С модулем подготовки отчетов PDF
службы Reporting Services 2008 превосходят службы Reporting Services 2005 на
платформах 4x4 и 4x2, а платформа 4x4 дает незначительное преимущество по
сравнению с платформами 4x2 и 2x2, независимо от версии служб Reporting
Services. На рисунках 5 и 6 представлены такие показатели, как число ошибок в
секунду, среднее время ответа и общее число запросов для каждой из этих
аппаратных платформ по каждой из версий служб Reporting Services. При использовании
модуля подготовки отчетов PDF до появления в службах Reporting Services 2005
ошибок HTTP 503, узким местом производительности был процессор.

Среднее
время ответа

При анализе среднего времени отклика
в тесте линейного наращивания с модулем подготовки отчетов PDF обнаружено, что
службы Reporting Services 2008 показали лучшее среднее время ответа, чем службы
Reporting Services 2005, на аппаратных платформах 4x4 и 4x2 (см. рис. 5). Кроме
того, было обнаружено, что платформа 4x4 незначительно превосходит две другие
аппаратные платформы как для служб Reporting Services 2008, так и для служб
Reporting Services 2005.

Рис. 5. Среднее время отклика для
модуля подготовки отчетов PDF.

Примечание. Графики для тестов служб Reporting Services 2005 на рисунке
5 отображают время ответа для запросов пользователей только до момента, пока
число ошибок в секунду не приводит к полному искажению общего времени отклика.
На всех аппаратных платформах службы Reporting Services 2008 продолжали
обрабатывать запросы пользователей даже после того, как службы Reporting
Services 2005 начинали постоянно выдавать ошибки HTTP 503 «Служба недоступна».

Общее
число запросов

При анализе общего числа запросов в
тесте линейного наращивания с модулем подготовки отчетов PDF обнаружено, что
службы Reporting Services 2008 показали значительно лучшее общее число
запросов, чем службы Reporting Services 2005, только на аппаратной платформе
4x4 (см. рис. 6).

Рис. 6. Общее число запросов для
модулей подготовки отчетов PDF.

Примечание. Графики для тестов служб Reporting Services 2005 на рисунке
6 отображают общее число запросов пользователей только до момента, пока число
ошибок в секунду не приводит к полному искажению этого показателя. На всех
аппаратных платформах службы Reporting Services 2008 продолжали обрабатывать
запросы пользователей даже после того, как службы Reporting Services 2005
начинали постоянно выдавать ошибки HTTP 503 «Служба недоступна».

Тест
линейного нарастания для модуля подготовки отчетов XLS

С модулем подготовки отчетов XLS
службы Reporting Services 2008 превосходят Reporting Services 2005 главным
образом на аппаратной платформе 4x4, а платформа 4x4 дает незначительное
преимущество по сравнению с платформами 4x2 и 2x2, независимо от версии служб
Reporting Services. На рисунках 7 и 8 представлены такие показатели, как число
ошибок в секунду, среднее время ответа и общее число запросов для каждой из
этих аппаратных платформ по каждой из версий служб Reporting Services. При
использовании модуля подготовки отчетов XLS до появления в службах Reporting
Services 2005 ошибок HTTP 503 узким местом производительности был процессор.

Среднее
время ответа

При анализе среднего времени отклика
в тесте линейного наращивания с модулем подготовки отчетов XLS обнаружено, что
службы Reporting Services 2008 показали лучшее среднее время ответа, чем службы
Reporting Services 2005, на аппаратных платформах 4x4 и 2x2 (см. рис. 7). Кроме
того, было обнаружено, что платформа 4x4 незначительно превосходит две другие
аппаратные платформы как для служб Reporting Services 2008, так и для служб
Reporting Services 2005. Кроме того, было также замечено, что (в отличие от
тестов с различными модулями подготовки отчетов и с модулем подготовки PDF)
среднее время ответа для служб Reporting Services 2008 и Reporting Services
2005 на аппаратной платформе 2x2 было немного лучше, чем на аппаратной
платформе 4x2, причем службы Reporting Services 2005 на этой платформе показали
результат лучше, чем службы Reporting Services 2008. Это различие в
производительности оказалось для нас полной неожиданностью, но нам не хватило
времени выяснить, чем вызван такой результат.

Рис. 7. Среднее время отклика для
модуля подготовки отчетов XLS

Примечание. Графики для тестов служб Reporting Services 2005 на рисунке
7 отображают время ответа для запросов пользователей только до момента, пока
число ошибок в секунду не приводит к полному искажению общего времени отклика.
На аппаратной платформе 4x4 службы Reporting Services 2008 продолжают
обслуживать пользователей даже после того, как службы Reporting Services 2005
перестают работать, постоянно выдавая ошибку HTTP 503 «Служба недоступна».
Обратите внимание, что порог появления ошибок HTTP 503 в службах Reporting
Services 2005 достигается только на платформе 4x4. На остальных аппаратных
платформах с использованием модуля подготовки отчетов XLS в службах Reporting
Services 2005 узким местом производительности является процессор.

Общее
число запросов

При анализе общего числа запросов в
тесте линейного наращивания с модулем подготовки отчетов XLS обнаружено, что
службы Reporting Services 2008 показали значительно лучшее общее число
запросов, чем службы Reporting Services 2005, только на аппаратной платформе
4x4 (см. рис. 8). Кроме того, было также замечено, что общее число запросов в
службах Reporting Services 2008 на платформе 2x2 немного лучше, чем на
платформе 4x2.

Рис. 8. Общее число запросов для
модуля подготовки отчетов XLS

Примечание. Графики для тестов служб Reporting Services 2005 на рисунке
8 отображают общее число запросов пользователей только до момента, пока число
ошибок в секунду не приводит к полному искажению этого показателя. На
аппаратной платформе 4x4 службы Reporting Services 2008 продолжают обслуживать
пользователей даже после того, как службы Reporting Services 2005 перестают
работать, постоянно выдавая ошибку HTTP 503 «Служба недоступна». Обратите
внимание, что порог появления ошибок HTTP 503 в службах Reporting Services 2005
достигается только на платформе 4x4. На остальных аппаратных платформах с
использованием модуля подготовки отчетов XLS в службах Reporting Services 2005
узким местом производительности является процессор.

Тест
линейного наращивания для модуля подготовки отчетов HTML

С модулем подготовки отчетов HTML
службы Reporting Services 2008 незначительно опережают службы Reporting
Services 2005 на любой аппаратной платформе. Кроме того, платформа 4x4 дает
незначительное преимущество по сравнению с платформами 4x2 и 2x2, независимо от
версии служб Reporting Services. На рисунках 9 и 10 представлены такие
показатели, как число ошибок в секунду, среднее время ответа и общее число
запросов для каждой из этих аппаратных платформ по каждой из версий служб
Reporting Services.

Примечание. Снова можно отметить, что анализ счетчиков
производительности на сервере каталога и на сервере источника данных для
сервера отчетов свидетельствует о том, что эти серверы являются узкими местами
производительности в службах Reporting Services 2008, и не являются таковыми в
службах Reporting Services 2005. Исходя из рабочей нагрузки и
программно-аппаратной конфигурации, выбранной для проведения тестов, можно
сделать вывод, что после более тщательной настройки или увеличения
производительности оборудования службы Reporting Services 2008 смогут работать
еще лучше, в отличие от служб Reporting Services 2005. Нам не удалось выяснить,
покажет ли модуль подготовки отчетов HTML в службах Reporting Services 2008
более высокую производительность, чем в службах Reporting Services 2005, после
повышения мощности сервера базы данных. Для этого нужно провести дополнительные
тесты.

Среднее
время ответа

При анализе среднего времени ответа
в тесте линейного наращивания с модулем подготовки отчетов HTML обнаружено, что
службы Reporting Services 2008 показали лучшее среднее время отклика, чем
службы Reporting Services 2005, на аппаратных платформах 4x4 и 2x2. Однако
службы Reporting Services 2008 на платформе 4x4 показывают снижение этого
показателя по мере увеличения числа запросов от пользователей И размеров
окружения (см. рис. 9). Нам не удалось выяснить, почему это происходит. Для
выяснения первопричины снижения производительности необходимо дополнительное
тестирование и анализ.

Рис. 9. Среднее время отклика для
модуля подготовки отчетов HTML.

Примечание. Графики для тестов служб Reporting Services 2005 на рисунке
9 отображают время ответа для запросов пользователей только до момента, пока
число ошибок в секунду не приводит к полному искажению общего времени отклика.
На всех аппаратных платформах службы Reporting Services 2008 продолжали
обрабатывать запросы пользователей даже после того, как службы Reporting
Services 2005 начинали постоянно выдавать ошибки HTTP 503 «Служба недоступна».

Общее
число запросов

При анализе общего числа запросов в
тесте линейного наращивания с модулем подготовки отчетов HTML обнаружено, что
службы Reporting Services 2008 показали незначительно большее общее число
запросов, чем службы Reporting Services 2005 (см. рис. 10), до того, как было достигнуто
пороговое значение возникновения ошибок HTTP 503.

Рис. 10. Общее число запросов для
модуля подготовки отчетов HTML

Примечание. Графики для тестов служб Reporting Services 2005 на рисунке
10 отображают общее число запросов пользователей только до момента, пока число
ошибок в секунду не приводит к полному искажению этого показателя. На всех
аппаратных платформах службы Reporting Services 2008 продолжали обрабатывать
запросы пользователей даже после того, как службы Reporting Services 2005
начинали постоянно выдавать ошибки HTTP 503 «Служба недоступна».

Тест
принудительной отправки

Мы также выполнили несколько
ограниченных тестов, чтобы проверить, с какой задержкой будут доставляться
отчеты на аппаратной платформе 4x4 без значительных ошибок. Для этого был
изменен тест VSTS (устранена 20-секундная задержка). Тестирование проводилось с
различными модулями подготовки отчетов на аппаратной платформе 4x4 в течение 25
минут. Число пользователей в течение первых 10 минут линейно повышалось до
10 000, а затем до конца теста оставалось неизменным.

Число
ошибок в секунду

При анализе числа ошибок в секунду в
тесте принудительной отправки обнаружено, что резкий рост количества ошибок
HTTP 503 «Служба недоступна» начинается в службах Reporting Services 2005 на
всех аппаратных платформах начиная примерно с девятой минуты теста, то есть
примерно с 9 000 пользователей (см. рис. 11). При проведении теста
принудительной отправки на аппаратной платформе 4x4 в службах Reporting
Services 2008 это ограничение достигнуто не было.

Рис. 11. Число ошибок в секунду для
теста принудительной отправки с различными модулями подготовки отчетов.

Ошибки
HTTP и ошибки истечения времени ожидания

Чтобы разобраться с обнаруженными
аппаратными ограничениями служб Reporting Services 2008 по сравнению со
службами Reporting Services 2005, мы сравнили ошибки HTTP 503, обнаруженные в
службах Reporting Services 2005, с ошибками истечения времени ожидания в
службах Reporting Services 2008. Выяснилось, что частота сбоев для отчетов в
службах Reporting Services 2005 имеет прямую связь с ошибками HTTP 503, а
частота сбоев для отчетов служб Reporting Services 2008 прямо связана с ошибками
истечения времени ожидания. В увязке с анализом работы дисковых очередей на
источнике данных эти ошибки указывают на то, что источник данных просто не
успевает обрабатывать запросы, поступающие от сервера, обслуживающего
клиентские запросы.

Рис. 12. Подробный анализ ошибок для
теста принудительной отправки

Примечание. Графики для тестов служб Reporting Services 2005 на рисунке
12 отображают общее число ошибок только до момента, пока число ошибок в секунду
не приводит к полному искажению этого показателя.

Среднее
время ответа

При анализе среднего времени отклика
и общего числа запросов в тесте принудительной отправки с различными модулями
подготовки отчетов (см. рис. 13 и 14) обнаружено, что хотя среднее время ответа
в службах Reporting Services 2005 было лучше, чем в службах Reporting Services
2008, они полностью переставали отвечать на запросы после увеличения числа
пользователей до 9 000. Службы Reporting Services 2008 в это время
продолжали подготовку отчетов к просмотру. Исходя из анализа дисковых очередей
на источнике данных для сервера отчетов, мы считаем, что значительное улучшение
времени ответа достигается в службах Reporting Services 2008 с ростом мощности
оборудования.

Рис. 13. Среднее время ответа для
теста принудительной отправки с различными модулями подготовки отчетов

Рис. 14. Общее число запросов для
теста принудительной отправки с различными модулями подготовки отчетов.

Прочее

Во время проведения теста были также
сделаны следующие наблюдения:

1. Выполнение рекомендаций служб Reporting
Services 2005 по использованию файловой системы в условиях высокой нагрузки
отрицательно сказывается на результатах и не должно выполняться в службах
Reporting Services 2008. Дополнительные сведения об этом параметре служб
Reporting Services 2005 см. в разделе Планирование
масштабируемости и производительности служб Reporting Services.

2. Во время первоначальной
оптимизации конфигурации по умолчанию служб Reporting Services 2008 и Reporting
Services 2005 было произведено только изменение, касающееся выделения дискового
пространства для базы данных ReportServerTempDB. Это весьма положительно
сказалось на производительности. Мы не пытались оптимизировать производительность
этой базы данных, помещая ее на быстрый диск или распределяя по нескольким
дисковым устройствам. С точки зрения анализа своих тестов, мы считаем, что
дисковая конфигурация баз данных ReportServer и ReportServerTempDB
в службах Reporting Services 2008 имеет даже большее значение, чем в Reporting
Services 2005. По результатам проведенных тестов мы считаем, что в каждой
установке или обновлении служб Reporting Services 2008 нужно уделить самое
серьезное внимание дисковой конфигурации этих баз данных. Дополнительные
сведения об оптимизации дисковой конфигурации служб Reporting Services 2005 см.
в разделе Планирование
масштабируемости и производительности служб Reporting Services. Поскольку советы в этом техническом документе рассчитаны
на службы Reporting Services 2005, большинство из них применимо и к Reporting
Services 2008. Возможно, здесь понадобится дополнительное тестирование.

3. В дополнение ко всем проведенным
тестам нами были проведены также некоторые предварительные тесты по выявлению
преимуществ Windows Server 2008 перед Windows Server 2003. Помимо прочих
улучшений, в Windows Server 2008 улучшено управление памятью и стеком TCP/IP.
Поскольку службы Reporting Services 2008 под управлением Windows Server 2008
работали несколько лучше, чем под управлением Windows Server 2003, полученные
данные могут рассматриваться лишь как предварительные. Ожидается, что
подготовка к просмотру множества больших отчетов на Windows Server 2008 будет
выполняться лучше. Помимо этого, в сборке, выпущенной уже после проведения
наших тестов, были добавлены улучшения, касающиеся подготовки службами
Reporting Services 2008 отчетов в графических форматах (PDF, EMF/Print, TIFF)
под управлением Windows Server 2008. По другим тестам, это улучшение показало
прирост производительности примерно на 5,7%. Чтобы определить, какой выигрыш
это даст при использовании служб Reporting Services 2008 совместно с Windows
Server 2008, потребуется дополнительное тестирование.

Оптимизация производительности служб Reporting Services

AlexeiK — Mon, 20 Dec 2010 13:13:00 GMT

Серия технических статей по построению и развертыванию крупномасштабной среды служб SQL Server Reporting Services

Авторы: Дэнни Ли (Denny Lee), Лукаш Павловски (Lukasz Pawlowski)
Соавторы: Роберт Брюкнер (Robert Bruckner), Джеймс Ву (James Wu), Джон Галлардо (John Gallardo), Дин Кэланквин (Dean Kalanquin)

ВведениеЭта техническая статья входит в серию технических статей по построению и развертыванию крупномасштабных сред служб SQL Server Reporting Services, содержащую общие рекомендации по настройке, реализации и оптимизации горизонтально масштабируемых архитектур служб Reporting Services корпоративного масштаба. В этой статье приводятся рекомендации по использованию служб Reporting Services как в Microsoft® SQL Server® 2005, так и в SQL Server 2008. Основное внимание в статье уделяется оптимизации архитектуры служб Reporting Services для повышения производительности, увеличения пропускной способности при формировании отчетов и ускорения обработки пользовательских нагрузок.

Архитектура

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

Рис. 1.. Архитектура горизонтального масштабирования служб Reporting Services

Следует ли использовать 64-разрядную архитектуру?

Да.

Но почему именно? Ответ состоит из двух частей: в первой описывается, как 64-разрядные вычисления повышают производительность каталога сервера отчетов, а во второй описывается повышение производительности самого сервера отчетов.

Как 64-разрядные вычисления повышают производительность каталога сервера отчетов

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

Как 64-разрядные вычисления улучшают работу службы сервера отчетов

Для серверов отчетов ответ будет несколько более сложным. В общем случае большинство отчетов служб Reporting Services интенсивно используют память, поэтому дополнительное адресное пространство, доступное при использовании 64-разрядных версий, расширяет возможности масштабирования. Обратите внимание, что некоторые рабочие нагрузки могут выполняться быстрее при использовании 32-разрядных вычислений, особенно в ситуации, когда имеется много небольших отчетов. Но благодаря доступу к большему объему памяти в 64-битной версии можно будет работать с большим количеством пользователей отчетов одновременно. Так как процессы в этом случае меньше конфликтуют за память, пропускная способность обработки возрастет, то есть можно будет позволить просматривать и экспортировать отчеты большего размера большему количеству пользователей. В службах SQL Server 2005 Reporting Services набор данных каждого отчета помещался в память, соответственно, чем больше отчетов выполнялось одновременно, тем больше использовалось памяти. При использовании 32-разрядных вычислений в этом случае можно легко достичь предела выделяемой памяти, равного 3 ГБ. В этом случае может произойти перезапуск процесса IIS, вызывающий сбой отчета.

В то же время, как описано в технической статье Советы и рекомендации по масштабному развертыванию служб Reporting Services, службы SQL Server 2008 Reporting Services не настолько зависят от доступной памяти. Они могут эффективно использовать файловую систему для выгрузки и загрузки структур данных в случае, если доступный серверу отчетов объем памяти недостаточен. Предельные значения объемов памяти в службах Reporting Services в SQL Server 2008 можно задать в файле RSReportServer.config, как описано ниже в разделе Настройка памяти для служб SQL Server 2008 Reporting Services. Когда службы Reporting Services задействуют файловую систему, отчеты выполняются медленнее, поскольку выборка данных из памяти намного более эффективна, чем доступ к диску. Файловая система начинает использоваться только тогда, когда используемый службами Reporting Services объем памяти приближается к установленным предельным значениям. Если сервер отчетов в службах SQL Server 2008 Reporting Services будет перегружен большим количеством одновременно обращающихся пользователей или очень больших отчетов, все отчеты все же будут выполнены, хотя это займет больше времени, поскольку службы Reporting Services смогут обработать данные, не превышая доступный объем пространства памяти. В корпоративной среде рано или поздно может возникнуть ситуация, когда серверам придется одновременно обслуживать многих пользователей и значительные нагрузки — и службы SQL Server 2008 Reporting Services (в отличие от SQL Server 2005 Reporting Services) оптимизированы таким образом, что, хотя отчеты иногда могут формироваться дольше, они гарантированно будут сформированы.

Исключения

Не забывайте, что не все поставщики данных доступны в 64-разрядном варианте (например, недоступен поставщик Microsoft JET и некоторые поставщики сторонних производителей). В таких случаях пользователю придется продолжить использование 32-разрядных вычислений в среде служб Reporting Services.

Обработка большой рабочей нагрузки

Как отмечалось в предыдущем разделе, два основных требования к корпоративной среде создания отчетов — это возможность обработки одновременной нагрузки от одновременно работающих пользователей и возможность обработки большой рабочей нагрузки (т. е. больших отчетов). Чтобы решить проблему параллельного обслуживания пользователей, можно масштабировать среду на несколько серверов отчетов и распределить между ними создаваемую запросами пользователей нагрузку, как описано в технической статье Советы и рекомендации по масштабному развертыванию служб Reporting Services .

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

Управляйте размером отчетов

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

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

Используйте выполнение в кэше

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

Настраивайте и планируйте выполнение отчетов

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

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

Доставляйте готовые отчеты для просмотра в отличных от обозревателя форматах

В службах SQL Server 2008 Reporting Services повышена производительность подготовки отчетов к просмотру в форматах отличных от обозревателя, например в форматах PDF и XLS, как описано в технической статье Масштабирование служб Reporting Services 2008 по сравнению с Reporting Services 2005: полученные уроки. В любом случае для уменьшения нагрузки на среду служб SQL Server Reporting Services можно помещать отчеты в других форматах в общую папку или размещать их в командных службах SharePoint®, чтобы пользователи могли работать непосредственно с файлом без постоянного повторного создания отчета.

Предварительно заполняйте для параметризованных отчетов кэш отчетов с помощью управляемых данными подписок

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

Возвращаясь к каталогам отчетов

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

Настройка веб-службы

С помощью настройки IIS и Http.sys можно достичь дополнительного повышения производительности компьютера, на котором размещен сервер отчетов. Настройки нижних уровней позволяют изменять длину очереди HTTP-запросов, длительность поддержания соединений в активном состоянии и т. п. В случае одновременного выполнения больших отчетов может потребоваться изменение этих параметров, чтобы компьютер с сервером мог одновременно принимать достаточное количество запросов для использования в полной мере ресурсов сервера. Возможность изменения этих параметров следует рассматривать только тогда, когда все серверы работают с максимальной нагрузкой, но ресурсы используются не полностью или возникают сбои при подключениях к процессу служб Reporting Services. Чтобы изменить эти параметры, выполните следующие действия.

Для служб SQL Server 2005 Reporting Services выполните настройку IIS.
Для служб SQL Server 2008 Reporting Services выполните настройку драйвера Http.sys в операционной системе: в Windows® 2003 или в Windows 2008.

Наблюдение с помощью представления ExecutionLog2

Начать анализ существующих рабочих нагрузок и исследование размера наборов данных, производительности и характеристик сложности этих нагрузок можно с представления ExecutionLog2 в службах Reporting Services. Дополнительные сведения см. в блоге Роберта Брюкнера, в котором содержатся исчерпывающие сведения о представлении ExecutionLog2 (http://blogs.msdn.com/robertbruckner/archive/2009/01/05/executionlog2-view.aspx). Дополнительные сведения о запросах и отчетах по данным журнала выполнения отчетов см. в электронной документации по SQL Server (http://msdn.microsoft.com/ru-ru/library/ms155836.aspx).

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

Ниже даны советы по просмотру представления ExecutionLog2 и быстрому обнаружению потенциальных «узких мест», ограничивающих производительность. Эта ссылка указывает на проект Просмотр журналов выполнения служб Reporting Services. Проект создает сводные и подробные отчеты Reporting Services по 1000 последних записей в представлении ExecutionLog2 с указанными ниже параметрами сортировки.

Рис. 2. Сводный отчет просмотра журналов выполнения (ExecutionLog2)

Рис. 3. Подробный отчет просмотра журналов выполнения (ExecutionLog2)

Длительное выполнение?

Сортировка по полям ElapsedSec или RowCount поможет выделить долго выполняющиеся отчеты. Если значение в поле TimeDataRetrieval велико, то «узким местом», ограничивающим производительность, является источник данных. Возможно, следует провести оптимизацию. Если велико значение RowCount, то службы Reporting Services извлекают и выполняют статистическую обработку большого объема данных — возможно, следует вынести эти операции в источник данных, чтобы сократить нагрузку на сервер отчетов.

Подписки или интерактивные отчеты?

Сортировка по полю RequestType позволяет определить, много ли выполняется подписок. Затем можно выделить «узкие места» и запланировать последовательное выполнение отчетов (то есть разнести плановое время выполнения подписок).

Данные, передаваемые в режиме реального времени, или моментальные снимки?

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

Сбалансирована ли нагрузка?

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

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

Сортировка по полям ReportPath и TimeStart может помочь при обнаружении интересных закономерностей выполнения отчетов. Например, может выясниться, что каждые 10 минут запускается ресурсоемкий отчет, выполнение которого занимает 5 минут.

Исправность отчетов

Можно выполнить сортировку отчетов по статусу, чтобы определить, не возникает ли большое количество ошибок перед обработкой отчета (например, в случае неверного языка определения отчетов) или после нее (например, в случае ошибки доставки подписки). Эта сортировка также поможет обнаружить отчеты с устаревшими сведениями или параметрами (например, в отчете могут содержаться устаревшие пароли для доступа к источникам данным или могут отсутствовать вложенные отчеты).

Кроме того, если значение ScalabilityTime больше нуля, то службы Reporting Services работают в режиме масштабирования, то есть при остром дефиците памяти, и выгружают долго выполняющиеся отчеты в файловую систему, чтобы освободить достаточно памяти для выполнения запросов меньшего объема. Если это происходит часто, рассмотрите возможность выполнения одного из следующих действий.

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

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

Используя все приведенные сведения, можно создавать собственные управляемые данными подписки, которые будут сортировать, фильтровать и отслеживать возникающие проблемы. Например, можно создать подписку, которая будет отправлять предупреждение, если доля ошибок (поле Errors) превысит 5 %.

Настройка памяти для служб SQL Server 2008 Reporting Services

Как упоминалось выше в разделе о 64-разрядных вычислениях, в службах SQL Server 2008 Reporting Services память на уровне структуры данных используется более эффективно. При высокой интенсивности рабочих нагрузок службы задействуют кэш в файловой системе, чтобы сократить используемый объем памяти. За счет повышения эффективности и возможности выгрузки на диск службы могут успешно выполнять большее количество отчетов (даже отчетов большого размера). Администратор задает параметры, определяющие, когда службы SQL Server 2008 Reporting Services начинают использовать кэш в файловой системе и насколько интенсивно используется этот кэш. Администратору рекомендуется рассмотреть возможность настройки параметров памяти в службах SQL Server 2008 Reporting Services для оптимального использования ресурсов компьютеров. Тонкая настройка этих параметров может обеспечить рост производительности при интенсивных рабочих нагрузках (по сравнению с конфигурацией по умолчанию).

Дополнительные сведения см. в электронной документации по SQL Server 2008 в разделе Настройка доступной памяти для приложений сервера отчетов . Ниже перечислены основные параметры управления памятью.

WorkingSetMinimum. Это минимальный объем памяти, выделяемый службами Reporting Services для функционирования (то есть, если используемый процессом служб SQL Server Reporting Services объем памяти ниже этого предела, кэш в файловой системе не используется). Он указывается в килобайтах в файле RSReportServer.config. По достижении этого предела службы Reporting Services начинают компенсировать нехватку памяти, перемещая данные долго выполняющихся запросов в кэш в файловой системе и освобождая память для выполнения запросов меньшего объема.

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

WorkingSetMaximum. Это максимальный объем физической памяти, используемой службами Reporting Services. Он также задается в килобайтах в файле RSReportServer.config. Если это предельное значение превышается в течение определенного периода времени, службы Reporting Services очищают домены приложений, чтобы сократить использование памяти. Таким образом обеспечивается наличие достаточного объема свободной памяти для нормальной работы операционной системы. Это значение можно увеличить, если необходимо одновременно обрабатывать большее количество отчетов.

MemorySafetyMargin и MemoryThreshold. Эти значения определяют интенсивность использования кэша в файловой системе. MemorySafetyMargin. Задает значение, разделяющее уровни низкой и средней нагрузки. По умолчанию этот параметр равен 80 %. MemoryThreshold. Задает границу между уровнями средней и высокой нагрузки. По умолчанию этот параметр равен 90 %. Оба параметра задаются в файле RSReportServer.config.

Обычно при постоянном достижении предельных уровней памяти рекомендуется рассмотреть возможность увеличения доступных ресурсов (добавления памяти, изменения этих значений конфигурации), а в дальнейшем — и масштабирования (добавления серверов). Сначала следует увеличить доступные ресурсы на существующем компьютере, так как в службах SQL Server 2008 Reporting Services ресурсы используются и управляются более эффективно (см. техническую статью Масштабирование служб Reporting Services 2008 по сравнению с Reporting Services 2005: полученные уроки.

Настройка памяти для служб SQL Server 2005 Reporting Services

Как отмечено в технической статье Архитектура масштабного развертывания служб Reporting Services, в этом случае также возможно масштабирование путем добавления дополнительной памяти. Но результат этого может быть не таким существенным: в некоторых ситуациях удваивание объема памяти и количество ЦП (до 16 ГБ ОЗУ и до 8 ядер ЦП) может увеличивать производительность на 1/3. Тем не менее, существуют способы, которыми можно увеличить производительность служб SQL Server 2005 Reporting Services до масштабирования путем добавления дополнительных серверов.

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

MemoryLimit. Этот параметр аналогичен параметру WorkingSetMinimum в SQL Server 2008. По умолчанию его значение соответствует 60 % от объема физической памяти. При увеличении данного параметра службы Reporting Services смогут обрабатывать большее количество запросов. По достижении задаваемого этим параметром предельного уровня новые запросы не принимаются.
MaximumMemoryLimit. Этот параметр аналогичен параметру WorkingSetMaximum в SQL Server 2008. По умолчанию его значение соответствует 80 % от объема физической памяти. Однако в отличие от версии для SQL Server 2008 при достижении этого параметра начинается прерывание выполнения процессов, а не отказ в приеме новых запросов.

Хотя в случае постоянного достижения пороговых значений объемов памяти рекомендуется изменить параметры конфигурации памяти, учтите, что при изменении этих параметров могут возникнуть или усилиться конфликты за другие виды ресурсов. ЗаключениеЭта статья завершает состоящую из четырех технических статей серию «Построение и развертывание крупномасштабных сред для служб SQL Server Reporting Services». Надеемся, что эта серия технических статей поможет вам при проектировании, управлении и поддержке корпоративной среды служб SQL Server Reporting Services.

Руководство по обеспечению соответствия требованиям в SQL Server 2008

AlexeiK — Mon, 20 Dec 2010 13:02:00 GMT

Техническая статья по SQL Server

Авторы: Джей-Си Кэннон (JC Cannon), Денни Ли (Denny Lee)

Соавторы: Энди Робертс (Andy Roberts), Айяд Шаммут (Ayad Shammout)

Технические редакторы: Дэн Джонс (Dan Jones), Крэйг Гик (Craig Gick), Джек Ричинс (Jack Richins), Рауль Гарсия (Raul Garcia), Девендра Тивари (Devendra Tiwari), Стивен Готт (Steven Gott), Эл Комо (Al Comeau), Лара Руббелке (Lara Rubbelke)

Публикация: ноябрь 2008 г.

Область применения: SQL Server 2008

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

Ссылка на статью: http://download.microsoft.com/download/E/7/9/E791638C-9084-4AEE-AD94-7D4B55FFC1D2/ReachingCompliance_RUS.docx

Руководство по стратегиям консолидаций SQL Server

AlexeiK — Mon, 20 Dec 2010 12:57:00 GMT

Техническая статья по SQL Server

Авторы: Санг Хсуэх (Sung Hsueh), Энтони Жонг (Antony Zhong), Мадхан Арумугам (Madhan Arumugam)

Технические редакторы: Клод Лоренсон (Claude Lorenson), Клиффорд Диббл (Clifford Dibble), Линдсей Эллен (Lindsey Allen), Самбит Самал (Sambit Samal), Сетху Калавакур (Sethu Kalavakur), Прем Мехра (Prem Mehra), Самир Теджани (Sameer Tejani), Иль-Сун Ли (Il-Sung Lee), Джек Ричинс (Jack Richins), Брайан Дьюи (Brian Dewey), Мэтью Джон (Mathew John), Джейми Рединг (Jamie Reding), Джонатан Моррисон (Jonathan Morrison), Омри Бахат (Omri Bahat), С. Муралидхар (S Muralidhar), Гайдн Ричардсон (Haydn Richardson)

Редактор: Бет Ингрэм (Beth Inghram)

Публикация: ноябрь 2009 г.

Применимо только к SQL Server 2008 и более поздним версиям

Сводка. Цель данного технического документа — обеспечить возможность выбора стратегий консолидации (виртуализация, использование нескольких баз данных или нескольких экземпляров) для приложений OLTP компонента SQL Server Database Engine, предоставив сведения о некоторых важных точках принятия решения, выявленных методом технического анализа. Некоторые основные определения и термины будут включены в документ для облегчения обсуждения, поскольку могут иметь различную трактовку в разных документах.

Ссылка на документ: http://download.microsoft.com/download/3/A/C/3AC5590B-A039-4AB3-A061-3775888986D7/ConsolidationPrescriptiveGuidance_RUS.docx

Руководство по повышению производительности служб Analysis Services

AlexeiK — Mon, 20 Dec 2010 12:50:00 GMT

Техническая статья по SQL Server <:o:p>Авторы: Ричард Ткачук и Томас Кейсер

Соавторы и технические редакторы: T.K. Ананд (Anand), Мариус Думитру (Marius Dumitru), Грег Галловей (Greg Galloway), Сива Харинат (Siva Harinath), Денни Ли (Denny Lee), Эдвард Меломед (Edward Melomed), Акшай Мирчандани (Akshai Mirchandani), Моша Пасумански (Mosha Pasumansky), Карл Рабелер (Carl Rabeler), Элизабет Витт (Elizabeth Vitt), Седат Йогурткуоглу (Sedat Yogurtcuoglu), Анн Зорнер (Anne Zorner)

Опубликовано: Октябрь 2008 г.

Область применения: SQL Server 2008 В этом техническом документе приводится описание того, как разработчики приложений могут применять методы повышения производительности обработки запросов в решениях, использующих службы SQL Server 2008 Analysis Services OLAP.

Ссылка на статью: http://download.microsoft.com/download/C/5/1/C5158403-F1FC-4127-8B6D-F316CAEBE2C0/SSASPerfGuide2008_RUS.docx

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

AlexeiK — Tue, 26 Jan 2010 13:58:00 GMT

Авторы: Томас Кайзер (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.

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

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

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

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

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

Службы интеграции 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, то система испытывает нехватку памяти.

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

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

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

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

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

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

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

Оптимизируйте источник данных 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 — Как добиться оптимальной производительности при работе уточняющих запросов служб интеграции.

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

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

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

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

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

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

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

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

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

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

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

Не выполняйте сортировку внутри служб интеграции без крайней необходимости. Чтобы выполнить сортировку, службы интеграции выделяют в памяти пространство для всего преобразуемого набора данных. По возможности выполните предварительную сортировку данных перед их подачей на конвейер. Если сортировка данных необходима, постарайтесь сортировать на конвейере только маленькие наборы данных. Вместо сортировки данных средствами служб интеграции используйте инструкции SQL с предложением ORDER BY для сортировки больших наборов данных в базе данных; отмечайте выходные данные как отсортированные, изменив метаданные конвейера служб интеграции для источника данных.
Иногда использование Transact-SQL оказывается более быстрым методом, чем обработка данных в службах интеграции. Как правило, все операции на основе наборов в Transact-SQL выполняются быстрее, так как задачу можно преобразовать в формулировку реляционной алгебры (домены и кортежи), для работы с которой оптимизирован SQL Server. Кроме того, оптимизатор SQL Server автоматически применяет высокую степень параллелизма и управление памятью к операциям на основе наборов — в службах интеграции эти операции, возможно, придется выполнять вручную. Типичными операциями на основе наборов являются следующие:

Инструкции UPDATE на основе наборов, которые значительно эффективнее построчных вызовов OLE DB.
Статистические вычисления, например, GROUP BY или SUM. Обычно с помощью Transact-SQL они выполняются быстрее, чем аналогичные вычисления, выполняемые в оперативной памяти на конвейере.

Обнаружение разности — это механизм, изменяющий существующие строки в целевой таблице вместо перезагрузки всей таблицы. Для выполнения обнаружения разности можно воспользоваться механизмом обнаружения изменений, например, новой функцией SQL Server 2008 — системой отслеживания измененных данных (CDC). Если эта функция недоступна, обнаружение разности придется выполнять путем сравнения исходных входных данных с целевой таблицей. Такая операция может потребовать значительного объема ресурсов, включая поддержание специальных индексов и контрольных сумм, предназначенных исключительно для этой цели. Часто более быстрым методом будет простая перезагрузка целевой таблицы. Как правило, если целевая таблица изменилась больше, чем на 10%, ее перегрузка может оказаться более быстрой, чем обнаружение разности.

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

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

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

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

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

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

В соответствии с вышесказанным, пакеты следует разрабатывать таким образом, чтобы они принимали параметр, указывающий обрабатываемую секцию. Это позволит запустить одновременно несколько экземпляров пакета с различными значениями параметра и секции; при этом выполнение задачи ускорится за счет параллелизма
Запустить несколько выполнений можно из командной строки с помощью команды START. Краткий пример кода, запускающего параллельно несколько инструкций robocopy, можно найти в технической статье Образец скрипта Robocopy для пользовательской синхронизации баз данных служб Analysis Services.

Большинство современных серверов — это многоядерные SMP-системы. Для полной загрузки процессоров на таком сервере необходимо применение высокого уровня параллелизма. Оно обеспечивается секционированием задачи и одновременным выполнением многих процессов. Дополнительные сведения о том, как это реализуется, см. в статье Мировой рекорд производительности SSIS ETL.

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

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

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

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

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

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

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

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

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

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

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

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

10 самых полезных функций SQL Server 2008 для администратора баз данных

AlexeiK — Tue, 26 Jan 2010 11:45:00 GMT

Автор: Майк Вайнер (Mike Weiner)Соавтор: Бурцин Пэйтел (Burzin Patel)Редакторы: Любор Коллар (Lubor Kollar), Кевин Кокс (Kevin Cox), Билл Эммерт (Bill Emmert), Грег Хузмайер (Greg Husemeier), Пол Бурпо (Paul Burpo), Джозеф Сак (Joseph Sack), Дэнни Ли (Denny Lee), Санджай Мишра (Sanjay Mishra), Линдси Аллен (Lindsey Allen), Марк Суза (Mark Souza)

Microsoft SQL Server 2008 содержит ряд улучшений и новых функциональных возможностей, расширяющих функциональность предыдущих версий. Администрирование и обслуживание баз данных, поддержание управляемости, доступности, безопасности и производительности — все это входит в обязанности администратора базы данных. В этой статье описаны десять самых полезных новых функций SQL Server 2008 (в алфавитном порядке), облегчающих работу администратора БД. Помимо краткого описания, для каждой из функций приведены возможные ситуации ее применения и важные рекомендации по использованию.

Монитор активности

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

Чтобы открыть монитор активности, щелкните правой кнопкой мыши на имени зарегистрированного сервера в обозревателе объектов, затем выберите Монитор активности или же воспользуйтесь стандартным значком на панели инструментов в среде SQL Server Management Studio. Монитор активности предлагает администратору раздел обзора, внешне похожий на Диспетчер задач Windows, а также компоненты детального просмотра отдельных процессов, ожидания ресурсов, ввода-вывода в файлы данных и последних ресурсоемких запросов, как показано на рис. 1.

Рис. 1: Вид представления Монитора активности SQL Server 2008 в среде Management Studio

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

С помощью монитора активности администратор также может выполнять следующие задачи:

· Приостанавливать и возобновлять работу монитора активности одним щелчком правой кнопки мыши. Это позволяет администратору «сохранить» сведения о состоянии на определенный момент времени, они не будут обновлены или перезаписаны. Но не забывайте, что при обновлении данных вручную, развертывании или сворачивании раздела старые данные будут обновлены и утеряны.

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

· Выполнять трассировку приложением Profiler или завершать процессы в представлении «Процессы». События приложения Profiler включают события RPC:Completed, SQL:BatchStarting и SQL:BatchCompleted, а также Audit Login и Audit Logout.

Монитор активности также позволяет отслеживать активность любого локального или удаленного экземпляра SQL Server 2005, зарегистрированного в среде SQL Server Management Studio.

Аудит [SQL Server]

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

В SQL Server 2008 (только выпуски Enterprise и Developer) SQL Server Audit реализует автоматизацию, позволяющую администратору и другим пользователям подготавливать, сохранять и просматривать аудиты различных компонентов серверов и баз данных. Функция предусматривает возможность аудита с детализацией уровня сервера или базы данных.

Существуют группы действий аудита на уровне сервера, например, следующие:

· FAILED_LOGIN_GROUP отслеживает неудачные попытки входа в систему.

· BACKUP_RESTORE_GROUP сообщает, когда создавалась резервная копия базы данных или выполнялось ее восстановление.

· DATABASE_CHANGE_GROUP проводит аудит времени создания, изменения или удаления базы данных.

Группы действий аудита на уровне базы данных включают следующие:

· DATABASE_OBJECT_ACCESS_GROUP вызывается при каждом выполнении инструкции CREATE, ALTER или DROP для объекта базы данных.

· DATABASE_OBJECT_PERMISSION_CHANGE_GROUP вызывается при использовании инструкций GRANT, REVOKE или DENY для объектов базы данных.

Существуют и другие действия аудита, например, SELECT, DELETE и EXECUTE. Дополнительные сведения, в том числе полный список всех групп и действий аудита, см. в разделе Группы действий и действия аудита SQL Server.

Результаты аудита можно направить для последующего просмотра в файл или журнал событий (системный журнал или журнал событий безопасности Windows). Данные аудита создаются с применением Расширенных событий — еще одной новой функции SQL Server 2008.

Аудиты SQL Server 2008 позволяют администратору ответить на вопросы, на которые раньше было очень сложно ответить постфактум, например, «Кто удалил этот индекс?», «Когда была изменена хранимая процедура?», «Какое внесенное изменение может мешать пользователю получить доступ к этой таблице?» и даже «Кто выполнил инструкцию SELECT или UPDATE для таблицы [dbo.Payroll]?».

Дополнительные сведения об использовании аудита SQL Server и примеры его реализации см. в разделе Руководство по обеспечению соответствия требованиям в SQL Server 2008.

Сжатие резервных копий

Администраторы БД долгое время предлагали включить эту функцию в SQL Server. Теперь это сделано, и как раз вовремя! В последнее время по ряду причин, например, в связи с возросшей длительностью хранения данных и необходимостью физического хранения большего объема данных, размеры баз данных стали расти экспоненциально. При резервном копировании большой базы данных необходимо выделение значительного дискового пространства для файлов резервной копии, а также выделение для операции существенного временного промежутка.

При использовании сжатия резервных копий SQL Server 2008 файл резервной копии сжимается по мере его записи, благодаря чему требуется не только меньше дискового пространства, но и меньше операций ввода-вывода, а резервное копирование занимает меньше времени. В ходе лабораторных испытаний с реальными пользовательскими данными во многих случаях наблюдалось уменьшение размера файла резервной копии на 70-85%. Кроме того, испытания показали, что длительность операций копирования и восстановления сократилась примерно на 45%. Следует отметить, что дополнительная обработка при сжатии увеличивает загрузку процессоров. Чтобы отделить во времени ресурсоемкий процесс копирования от других процессов и минимизировать его влияние на их работу, можно воспользоваться другой описанной в этом документе функцией — Resource Governor.

Сжатие включается путем добавления предложения WITH COMPRESSION в команду BACKUP (дополнительные сведения см. в разделе Электронная документация по SQL Server) или установкой этого параметра на странице Параметры диалогового окна Резервное копирование базы данных. Чтобы не требовалось вносить изменения во все существующие сценарии резервного копирования, реализован глобальный параметр, включающий сжатие всех создаваемых на экземпляре сервера резервных копий по умолчанию. (Этот параметр доступен на странице Настройки базы данных диалогового окна Свойства сервера; его также можно установить, выполнив хранимую процедуру sp_configure со значением параметра backup compression default, равным 1). Команда создания резервной копии требует явного задания параметра сжатия, а команда восстановления автоматически распознает сжатую резервную копию и распаковывает ее при восстановлении.

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

Серверы централизованного управления

Часто администратор БД управляет сразу многими экземплярами SQL Server. Возможность централизации управления и администрирования многими экземплярами SQL в единой точке позволяет экономить существенные усилия и время. Реализация серверов централизованного управления, доступная в среде SQL Server Management Studio посредством компонента «Зарегистрированные серверы», позволяет администратору выполнять различные административные операции над многими серверами SQL Servers из единой консоли управления.

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

· Многосерверное выполнение запросов: теперь из одного источника можно выполнить скрипт на многих серверах SQL Server, данные будут возвращены этому источнику, причем раздельно выполнять вход в каждый из серверов не требуется. Это может быть особенно полезным в случае, когда необходимо просмотреть или сравнить данные с нескольких серверов SQL Server, не выполняя распределенный запрос. Кроме того, при условии поддержки синтаксиса запроса предыдущими версиями SQL Server, запускаемый из редактора запросов SQL Server 2008 запрос может выполняться и на экземплярах SQL Server 2005 и SQL Server 2000. Дополнительные сведения см. в блоге рабочей группы по управляемости SQL Server в разделе Выполнение многосерверных запросов в среде SQL Server 2008.

· Импорт и определение политик на многих серверах: в рамках функциональности Управления на основе политик (еще одной новой функции SQL Server 2008, также описанной в этой статье), SQL Server 2008 обеспечивает возможность импорта файлов политик в отдельные группы серверов централизованного управления и позволяет определять политики на всех серверах, зарегистрированных в определенной группе.

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

· Импорт и экспорт зарегистрированных серверов: серверы, зарегистрированные в серверах централизованного управления (Central Management Servers), могут экспортироваться и импортироваться при их передаче между администраторами или различными установленными экземплярами SQL Server Management Studio. Эта возможность служит альтернативой импорту или экспорту администратором его собственных локальных групп в SQL Server Management Studio.

Не забывайте, что разрешения применяются с помощью проверки подлинности Windows, поэтому права и разрешения пользователей могут различаться на различных серверах, зарегистрированных в группе сервера централизованного управления. Дополнительные сведения см. в разделе Администрирование нескольких серверов с помощью серверов централизованного управления и в блоге Кимберли Трипп (Kimberly Tripp): Центральные серверы управления SQL Server 2008 — знакомы ли вы с ними?

Сборщик данных и хранилище данных управления

Настройка производительности и диагностика занимают много времени и могут требовать профессиональных навыков работы с SQL Server, а также понимания внутренней структуры баз данных. Системный монитор Windows (Perfmon), профилировщик SQL Server Profiler и динамические административные представления решали часть этих задач, но они нередко оказывали влияние на работу сервера, были трудоемки в применении или задействовали методы сбора разрозненных данных, затрудняющие их последующее объединение и интерпретацию.

Чтобы предоставить понятные сведения о производительности системы, позволяющие предпринять конкретные меры, в SQL Server 2008 реализовано полностью расширяемое средство сбора и хранения данных о производительности — сборщик данных. Оно содержит несколько непосредственно готовых к работе агентов сбора данных, централизованное хранилище данных о производительности, так называемое хранилище данных управления, и несколько подготовленных заранее отчетов для представления собранных данных. Сборщик данных — это масштабируемое средство, обеспечивающее сбор и объединение данных из различных источников, таких как динамические административные представления, монитор производительности Perfmon и запросы Transact-SQL, в соответствии с полностью настраиваемой частотой сбора данных. Сборщик данных можно расширить, реализовав сбор данных по любому измеряемому атрибуту приложения.

Еще одна полезная функция хранилища данных управления — это возможность его установки на любом сервере SQL Server с последующим сбором данных с одного или нескольких экземпляров SQL Server. При этом минимизируется влияние на производительность рабочих систем, а также улучшается масштабируемость в контексте отслеживания и сбора данных со многих серверов. При лабораторных испытаниях наблюдаемая потеря пропускной способности при выполнении агентов и работе хранилища данных управления на нагруженном сервере (с применением рабочей нагрузки OLTP) составила приблизительно 4%. Потеря производительности может изменяться в зависимости от периодичности сбора данных (упомянутое испытание велось при расширенной рабочей нагрузке, с передачей данных в хранилище каждые 15 минут), она также может резко увеличиваться во время периодов сбора данных. В любом случае следует ожидать некоторого уменьшения доступных ресурсов, так как процесс DCExec.exe использует определенный объем памяти и ресурсы ЦП, а запись в хранилище данных управления повысит нагрузку на подсистему ввода-вывода и потребует выделения пространства в месте расположения файлов данных и журнала.На диаграмме (рис. 2) показан типичный отчет сборщика данных.

Рис. 2: Вид отчета сборщика данных SQL Server 2008

В отчете показана деятельность SQL Server за время периода сбора данных. В нем собраны и отражены такие события, как ожидания, использование ЦП, ввода-вывода и памяти, а также статистика по ресурсоемким запросам. Администратор может также перейти к детальному рассмотрению элементов отчетов, сконцентрировавшись на отдельном запросе или операции, чтобы исследовать, определить и устранить проблемы, связанные с производительностью. Эти возможности сбора данных, их хранения и создания отчетов позволяют реализовать упреждающее отслеживание состояния серверов SQL Server в среде. При необходимости они позволяют возвращаться к историческим данным, чтобы понять и оценить изменения, повлиявшие на производительность за отслеживаемый период. Сборщик данных и хранилище данных управления поддерживаются во всех выпусках SQL Server 2008, кроме SQL Server Express.

Сжатие данных

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

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

В некоторых лабораторных испытаниях включение сжатия данных обеспечивало экономию 50-80% дискового пространства. Экономия пространства значительно различалась: если в данных содержалось мало повторяющихся значений, или значения использовали все выделяемые для указанного типа данных байты, экономия была минимальной. При этом производительность многих рабочих нагрузок не увеличивалась. Однако при работе с данными, содержащими много числовых данных и много повторяющихся значений, отмечались значительная экономия дискового пространства и рост производительности, составляющий от нескольких процентов до 40-60% для некоторых образцов рабочих нагрузок запросов.

SQL Server 2008 поддерживает два типа сжатия: сжатие строк, при котором сжимаются отдельные столбцы таблицы, и сжатие страниц, при котором страницы данных сжимаются с помощью сжатия строк, префиксов и словарного сжатия. Достигаемая степень сжатия сильно зависит от типов данных и содержимого базы данных. В общем, при использовании сжатия строк уменьшается дополнительная нагрузка на операции приложений, однако уменьшается и степень сжатия, то есть выигрывается меньше места. В то же время сжатие страниц приводит к большей дополнительной нагрузке на приложение и загрузке процессора, но и экономит значительно больше пространства. Сжатие страниц является надмножеством сжатия строк, то есть если объект или секция объекта сжимаются с помощью сжатия страниц, к ним также применяется и сжатие строк. Кроме того, SQL Server 2008 поддерживает формат хранения vardecimal из SQL Server 2005 с пакетом обновления 2 (SP2). Следует учитывать, что, поскольку этот формат является подмножеством сжатия строк, он считается устаревшим и будет исключен из будущих версий продукта.

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

Сжатие данных можно провести с помощью инструкций Transact-SQL или мастера сжатия данных. Чтобы определить возможное изменение размера объекта при его сжатии, можно воспользоваться системной хранимой процедурой sp_estimate_data_compression_savings или мастером сжатия данных. Сжатие базы данных поддерживается только в выпусках SQL Server 2008 Enterprise и Developer. Оно реализуется исключительно в самих базах данных и не требует внесения каких-либо изменений в приложения.

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

Управление на основе политик

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

Управление на основе политик (PBM) предоставляет администратору широкий набор возможностей по управлению средой. Политики можно создавать и выполнять проверку на соответствие им. Если цель проверки (например, ядро базы данных, база данных, таблица или индекс SQL Server) не соответствует требованиям, администратор может автоматически перенастроить ее в соответствии с этими требованиями. Также существует ряд режимов определения политик (многие из которых автоматизированы), упрощающих проверку соответствия требованиям политик, регистрацию в журнале нарушений политики и отправку уведомлений, и даже выполняющих откат изменений для обеспечения соответствия требованиям политики. Дополнительные сведения о режимах определения и об их сопоставлении с аспектами (понятием управления на основе политик (PBM), также обсуждаемым в этом блоге) см. в разделе Блог об управлении SQL Server на основе политик.

Политики можно экспортировать и импортировать в виде XML-файлов для их определения и применения на многих экземплярах серверов. Кроме того, в среде SQL Server Management Studio и в представлении зарегистрированных серверов политики можно определять на многих серверах, зарегистрированных в локальной группе серверов или в группе сервера централизованного управления.

В предыдущих версиях SQL Server может быть реализована не вся функциональность управления на основе политик. Тем не менее, функцию составление отчетов политики можно использовать на серверах SQL Server 2005 и SQL Server 2000. Дополнительные сведения об использовании управления на основе политик см. в разделе Администрирование серверов с помощью управления на основе политик в электронной документации по SQL Server. Дополнительные сведения о самой технологии политик с примерами см. в разделе Руководство по обеспечению соответствия в SQL Server 2008.

Прогнозируемая производительность и параллелизм

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

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

Во-первых, структуры планов (Plan Guide):

В SQL Server 2005 было реализовано улучшение стабильности и предсказуемости запросов с помощью новой на тот момент функции — «структур планов», содержавших указания для выполнения запросов, которые нельзя было изменить непосредственно в приложении. Дополнительные сведения см. в техническом документеПринудительное использование планов запросов . Хотя подсказка в запросе USE PLAN является очень мощной функцией, она поддерживала только операции SELECT DML и часто была неудобна в применении из-за чувствительности структур планов к форматированию.

В SQL Server 2008 механизм структур планов расширен в двух направлениях: во-первых, расширена поддержка подсказки в запросе USE PLAN, которая теперь совместима со всеми инструкциями DML (INSERT, UPDATE, DELETE, MERGE); во-вторых, введена новая функция закрепления планов, позволяющая непосредственно создавать структуру плана (закрепление) любого плана запроса, существующего в кэше планов SQL Server, как показано в следующем примере.

sp_create_plan_guide_from_handle

@name = N'MyQueryPlan',

@plan_handle = @plan_handle,

@statement_start_offset = @offset;

У структуры планов, созданной любым способом, имеется область базы данных; она хранится в таблице sys.plan_guides. Структуры планов лишь влияют на процесс выбора плана запроса оптимизатором, но не избавляют от необходимости компиляции запроса. Также добавлена функция sys.fn_validate_plan_guide, для проверки существующих структур планов SQL Server 2005 и обеспечения их совместимости с SQL Server 2008. Закрепление планов доступно в выпусках SQL Server 2008 Standard, Enterprise и Developer.

Во-вторых, эскалация блокировок:

Эскалация блокировки часто вызывало проблемы блокировки, а иногда даже взаимоблокировку. Устранять эти проблемы приходилось администратору. В предыдущих версиях SQL Server можно было управлять эскалацией блокировок (флаги трассировки 1211 и 1224), но это было возможно только для детализации на уровне экземпляра. Для одних приложений это устраняло проблему, а для других вызывало еще большие проблемы. Другим недостатком алгоритма эскалации блокировок в SQL Server 2005 было то, что блокировки секционированных таблиц укрупнялись напрямую до уровня таблиц, а не до уровня секций.

SQL Server 2008 предлагает решение обеих проблем. В нем реализован новый параметр, позволяющий управлять эскалацией блокировки на уровне таблиц. С помощью команды ALTER TABLE можно выбрать отключение эскалации или эскалацию до уровня секций для секционированных таблиц. Обе эти возможности улучшают масштабируемость и производительность без нежелательных побочных эффектов, затрагивающих другие объекты в экземпляре. Эскалация блокировки задается на уровне объекта базы данных и не требует внесения каких-либо изменений в приложения. Оно поддерживается во всех выпусках SQL Server 2008.

Resource Governor

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

Новая функция SQL Server 2008 — «Регулятор ресурсов»(Resource Governor) — помогает справиться с этой проблемой, давая возможность дифференцировать рабочие нагрузки и распределять ресурсы в соответствии с потребностями пользователей. Ограничения регулятора ресурсов легко перенастраиваются в реальном времени при минимальном воздействии на выполняющиеся рабочие нагрузки. Распределение рабочих нагрузок по пулу ресурсов настраивается на уровне соединения, причем этот процесс полностью прозрачен для приложений.

На диаграмме ниже показан процесс выделения ресурсов. В данном сценарии настраиваются три пула рабочих нагрузок (рабочие нагрузки Admin, OLTP и Report), после чего пулу рабочих нагрузок OLTP присваивается высший приоритет. В то же время настраиваются два пула ресурсов (пул Pool и пул Application) с заданными ограничениями по объему памяти и времени процессора (ЦП). На последнем этапе рабочая нагрузка Admin назначается пулу Admin, а рабочие нагрузки OLTP и Report назначаются пулу Application.

Ниже приведены особенности, которые следует учитывать при использовании регулятора ресурсов.

- Регулятор ресурсов использует учетные данные входа, имя узла или имя приложения в качестве «идентификатора пула ресурсов», поэтому использование для приложения одного имени входа при определенных количествах клиентов, приходящихся на один сервер, может усложнить создание пулов.

- Не поддерживается группировка объектов на уровне базы данных, при которой доступ к ресурсам регулируется на основе объектов базы данных, к которым осуществляется доступ.

- Регулятор ресурсов позволяет управлять ресурсами только в пределах одного экземпляра SQL Server. Для управления несколькими экземплярами SQL Server или процессами на сервере из единого источника следует рассмотреть возможность применения Диспетчера системных ресурсов Windows.

- Настраивать можно только использование ресурсов процессора и памяти. Управление ресурсами ввода-вывода не реализовано.

- Динамическое переключение рабочих нагрузок между пулами ресурсов после соединения невозможно.

- Регулятор ресурсов поддерживается только в выпусках SQL Server 2008 Enterprise и Developer и может использоваться только для ядра базы данных SQL Server; управление службами SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS) и SQL Server Reporting Services (SSRS) не поддерживается.

Прозрачное шифрование данных (TDE)

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

Server 2008 предлагает решение этой проблемы путем прозрачного шифрования данных (TDE). При шифровании TDE данные в операциях ввода-вывода шифруются и дешифруются в реальном времени; файлы данных и журналов шифруются с помощью ключа шифрования базы данных (DEK). DEK — это симметричный ключ, защищаемый сертификатом, который хранится в базе данных master сервера, или асимметричный ключ, защищаемый модулем расширенного управления ключами (EKM).

Функция TDE защищает «неактивные» данные, поэтому данные в файлах MDF, NDF, и LDF невозможно просматривать с помощью редактора шестнадцатеричных данных или каким-либо другим способом. Однако активные данные, например, результаты выполнения инструкции SELECT в среде SQL Server Management Studio, останутся видимыми для пользователей, у которых имеются права на просмотр таблицы. Кроме того, поскольку функция TDE реализована на уровне базы данных, база данных может задействовать индексы и ключи для оптимизации запросов. TDE не следует путать с шифрованием на уровне столбцов — это отдельная функция, позволяющая шифровать даже активные данные.

Шифрование базы данных — одноразовый процесс, который можно запустить командой Transact-SQL или из среды SQL Server Management Studio, после чего он выполняется в фоновом потоке. Состояние шифрования или дешифровки можно отслеживать с помощью динамического административного представления sys.dm_database_encryption_keys. Во время проведенных лабораторных испытаний шифрование базы данных размером 100 Гб с применением алгоритма шифрования AES_128 заняло около часа. Хотя накладные расходы при использовании TDE определяются в основном рабочей нагрузкой приложения, в некоторых из проведенных испытаний эти дополнительные расходы составили менее 5%. Следует учитывать одну особенность, которая может повлиять на производительность: если TDE используется в любой из баз данных на экземпляре, то также шифруется и системная база данных tempDB. Наконец, при одновременном использовании различных функций необходимо учитывать следующее:

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

· Шифрование базы данных не влияет на сжатие данных (строчное или страничное).

TDE позволяет организации обеспечить соответствие требованиям нормативных стандартов и общему уровню защиты данных. TDE поддерживается только в выпусках SQL Server 2008 Enterprise и Developer; его активация не требует внесения изменений в существующие приложения. Дополнительные сведения см. в разделе Шифрование данных в выпуске SQL Server 2008 Enterprise или в обсуждении в Руководстве по обеспечению соответствия требованиям в SQL Server 2008 на тему Прозрачное шифрование данных.

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

10 рекомендаций по созданию крупномасштабного реляционного хранилища данных

AlexeiK — Tue, 26 Jan 2010 11:34:00 GMT

Авторы:: Стюарт Озер (Stuart Ozer) при участии Према Мехры (Prem Mehra) и Кевина Кокса (Kevin Cox)

Технические редакторы: Любор Коллар (Lubor Kollar), Томас Кейзер (Thomas Kejser), Дэнни Ли (Denny Lee), Джимми Мэй (Jimmy May), Майкл Рэдман (Michael Redman), Санджэй Мишра (Sanjay Mishra)

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

Рекомендуется секционировать большие таблицы фактов

Рекомендуется секционировать таблицы фактов размером 50–100 ГБ и более.
Секционирование может упростить управление данными и часто приводит к повышению производительности.

Быстрое, более детальное сопровождение индексовБолее гибкие варианты резервного копирования и восстановления.
Ускоренная загрузка и удаление данных.

Ускорение работы запросов, ограниченных одной секцией.
Обычно таблицу фактов секционируют по ключу даты.

Позволяет применять метод скользящего окна.

Позволяет применять методику «устранения» секций.

Постройте кластеризованный индекс на ключе даты в таблице фактов

Это поддерживает использование эффективных запросов для заполнения кубов или получения среза исторических данных.
Если данные загружаются пакетами в отведенное для заливки данных время, то для кластеризованного индекса на таблице фактов нужно использовать параметры ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCKS = OFF. Это поможет ускорить операции просмотра таблиц во время запроса и избежать избыточной активности блокировок во время масштабных операций обновления.
Постройте некластеризованные индексы для каждого внешнего ключа. Это помогает точечным запросам извлекать строки на основе избирательного предиката измерения. Используйте файловые группы для административных задач, в том числе резервного копирования и восстановления, частичного обеспечения доступности базы данных и т. п.

Будьте внимательны с выбором фрагмента секционирования

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

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

Выбор степени секционирования влияет на параллелизм запросов.

Для SQL Server 2005:

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

Для SQL Server 2008:

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

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

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

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

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

Постройте кластеризованный индекс на суррогатном ключе для каждой таблицы измерения и некластеризованный индекс на бизнес-ключе (потенциально объединенный с эффективной датой строки) для поддержки поиска по суррогатному ключу во время загрузки.
Постройте некластеризованные индексы для других столбцов измерения, в которых часто выполняется поиск.
Избегайте секционирования таблиц измерений.
Избегайте принудительного установления связей по внешним ключам между таблицами фактов и измерений — это ускорит загрузку данных. Можно создать ограничения внешнего ключа с параметром NOCHECK для документирования связей, но не следует обеспечивать строгое выполнение таких ограничений. Проверять целостность данных можно с помощью преобразований Transform Lookup или же можно проверять целостность данных в источнике данных.

Пишите эффективные запросы для устранения секций

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

Используйте для сопровождения данных метод скользящего окна

Поддерживайте движущееся временное окно для оперативного доступа к таблицам фактов. Загружайте самые новые данные и выгружайте самые старые.
Всегда сохраняйте пустые секции с обоих концов диапазона секций, чтобы гарантировать, что разбиение секций (перед загрузкой новых данных) и слияние секций (после выгрузки старых данных) не повлекут перемещения данных.
Не следует выполнять разбиение или слияние заполненных секций. Разбиение или слияние заполненных секций может оказаться крайне неэффективным, поскольку в таком случае объем операций с журналом может возрасти в 4 раза и, кроме того, это приведет к интенсивной работе механизма блокировок.
Создавайте промежуточную таблицу загрузки в той же файловой группе, где располагается загружаемая секция.
Создавайте промежуточную таблицу выгрузки в той же файловой группе, где располагается удаляемая секция.
Быстрее всего загружать сразу полную новую секцию, но это возможно только в случае, когда размер секции совпадает с периодом загрузки данных (например, используется одна секция для каждого дня и данные загружаются один раз в день).
Если размер секции не совпадает с периодом загрузки данных, следует проводить добавочную загрузку только самой последней секции.
Различные варианты загрузки массовых данных в секционированную таблицу обсуждаются в техническом документе http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/loading_bulk_data_partitioned_table.mspx.
Всегда выгружайте секции по одной.

Загружайте исходные данные эффективным образом

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

Используйте несколько инструкций BULK INSERT, программу BCP или задачи служб SSIS.

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

Постройте кластеризованный индекс для каждой промежуточной таблицы, а затем создайте соответствующие ограничения CHECK.
Переключите все секции в секционированную таблицу с помощью инструкции SWITCH.
Постройте для секционированной таблицы некластеризованные индексы.
На 64-процессорном сервере 1 ТБ данных можно загрузить менее чем за с хранилищем SAN с пропускной способностью 14 ГБ/с (для неиндексированной таблицы). Дополнительные сведения см. в записи блога SQLCAT http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx.

Эффективно удаляйте старые данные

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

Не используйте конструкцию DELETE FROM ...WHERE ...

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

Как правило, более быстрым будет:

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

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

DELETE TOP (1000) ... ;

COMMIT

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

Управляйте статистикой вручную

Статистика по секционированным таблицам ведется для таблицы в целом.
После загрузки новых данных обновляйте вручную статистику по крупным таблицам фактов.
После повторного построения индекса для секции обновляйте статистику вручную.
Если статистика обновляется регулярно, после периодической загрузки данных, то автоматическое создание статистики (параметр AUTOSTATS) для этой таблицы можно отключить.
Это важно для оптимизации запросов, при которых может потребоваться чтение лишь самых новых данных.
Также повысить производительность можно, обновляя статистику по таблицам измерений небольшого размера после добавочной загрузки данных. Чтобы получить более точные планы запросов, используйте параметр FULLSCAN для обновления статистики по таблицам измерения.

Выбирайте эффективные стратегии резервного копирования

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

Например, резервное копирование базы данных размером 2 ТБ на массив RAID-5 из 10 дисков по сети SAN может занять 2 часа (при скорости 275 МБ/с).

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

Файловые группы для секций с историческими данными можно пометить как READ ONLY.
Создавайте резервную копию файловой группы лишь один раз при ее переводе в состояние «только для чтения».
Проводите регулярное резервное копирование только для файловых групп, доступных и для чтения, и для записи.

Учтите, что инструкции RESTORE для восстановления файловых групп, доступных только для чтения, не могут выполняться параллельно.

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

Отказоустойчивый кластер SQL Server 2008

AlexeiK — Tue, 26 Jan 2010 11:21:00 GMT

Техническая статья по SQL Server

Авторы: Майк Вайнер (Mike Weiner), Пол Бурпо (Paul Burpo), Макс Верун (Max Verun), Джозеф Сак (Joseph Sack), Джастин Эриксон (Justin Erickson)Соавторы: Санджай Мишра (Sanjay Mishra), Джейсон Ву (Jason Wu), Уттам Паруи (Uttam Parui)

Технические редакторы: Прем Мехра (Prem Mehra), Джеймс Подгорски (James Podgorski), Дэвид Уитни (David Whitney), Ричард Ткачук (Richard Tkachuk), Сетху Калавакур (Sethu Kalavakur), Синди Гросс (Cindy Gross), Нил Грейвз (Neal Graves), Фарзан Ратистари (Farzan Ratistari), Айяд Шаммут (Ayad Shammout, Caregroup Healthcare Systems), Дейвид П. Смит (David P. Smith, ServiceU Corporation)

Опубликовано: июнь 2009 г.

Область применения: SQL Server 2008

Краткий обзор: Данный технический документ служит дополнением к существующей документации по планированию, реализации и администрированию отказоустойчивых кластеров SQL Server 2008, входящей в состав электронной документации по Microsoft SQL Server 2008. В настоящем руководстве содержатся ссылки на существующую документацию, предназначенные в первую очередь для технических специалистов. В этом техническом документе описываются архитектура отказоустойчивых кластеров и основные связанные с ней понятия в Windows Server и SQL Server 2008; установка отказоустойчивых кластеров SQL Server 2008; обновление отказоустойчивых кластеров SQL Server 2008; обслуживание и администрирование SQL Server 2008.

Ссылка на статью:

http://download.microsoft.com/download/4/4/A/44A31751-86ED-4B74-ABC2-FF80A4EBD476/SQLServer2008FailoverCluster_RUS.docx

Как справиться с PAGELATCH при большом количестве одновременных операций INSERT

AlexeiK — Wed, 25 Nov 2009 14:45:00 GMT

Авторы: Thomas Kejser, Lindsey Allen, Arvind Rao и Michael Thomassy

При участии и с рецензиями: Mike Ruthruff, Lubor Kollar, Prem Mehra, Burzin Patel, Michael Thomassy, Mark Souza, Sanjay Mishra, Peter Scharlock, Stuart Ozer, Kun Cheng и Howard Yin

Введение

Недавно, мы проводили лабораторные испытания в Microsoft Enterprise Engineering Center, при которых использовалась большая рабочая нагрузка, характерная для OLTP систем. Целью этой лабораторной работы было взять Microsoft SQL Server c интенсивной рабочей нагрузкой и посмотреть, что случится при увеличении числа процессоров с 64 до 128 (примечание: эта конфигурация была ориентирована на релиз Microsoft SQL Server 2008 R2). Рабочая нагрузка представляла собой бльшое количесво одновеменных операций вставки, направляемых в несколько больших таблиц.

Как только мы начали масштабировать нагрузку до 128 процессорных ядер, сразу же в статистике ожиданий стали доминировать блокировоки PAGELATCH_UP и PAGELATCH_EX. Средняя продолжительность ожиданияй была десятки миллисекунд, и таких ожиданий было очень много. Такое их количество оказалось для нас неожиданностью, ожидалось, что их продолжительность не будет превышать несколько миллисекунд.

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

Диагностика проблемы

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

SELECT session_id, wait_type, resource_description
FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGELATCH%'

Пример результата:

session_id	wait_type	resource_description
42	PAGELATCH_EX	7:1:122
46	PAGELATCH_EX	7:1:122
48	PAGELATCH_EX	7:1:122
53	PAGELATCH_EX	7:1:122

В столбце resource_description указаны местоположения страниц, к которым ожидают доступ сессии, местоположение представлено в таком формате:

<database_id>:<file_id>:<page_id>

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

SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms

, s.name AS schema_name , o.name AS object_name ,
i.name AS index_name
FROM sys.dm_os_buffer_descriptors bd JOIN (
SELECT *

, CHARINDEX(':', resource_description) AS file_index
, CHARINDEX(':', resource_description

, CHARINDEX(':', resource_description)) AS page_index

, resource_description AS rd
FROM sys.dm_os_waiting_tasks wt
WHERE wait_type LIKE 'PAGELATCH%'
)AS wt
     ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
     AND bd.file_id = SUBSTRING(wt.rd, wt.file_index, wt.page_index)
   AND bd.page_id = SUBSTRING(wt.rd, wt.page_index, LEN(wt.rd))
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id

JOIN sys.objects o ON i.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id

Запрос показал, что ожидаемые страницы относятся к кластеризованному индексу, определённому первичным ключом таблицы с представленной ниже структурой:

CREATE TABLE HeavyInsert (

ID INT PRIMARY KEY CLUSTERED
, col1 VARCHAR(50)
) ON [PRIMARY]

Что происходит, почему возникает очередь ожиданий к страницам данных индекса - всё это будет рассмотрено в этой технической заметке.

Основная информация

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

1. В журнале транзакций создаётся запись о том, что строка изменилась.
2. Осуществляется поиск в В-дереве местонахождения той страницы, куда должна будет попасть новая запись.
3. Осуществляется наложение на эту страницу краткой блокировки PAGELATCH_EX, чтобы воспрепятствовать другим изменениям на этой странице.
4. Осуществляется добавление строки в страницу и, если это необходимо, осуществляется пометка этой страницы как «грязной».
5. Осуществляется снятие краткой блокировки со страницы.

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

Однако, что произойдет если все вставляемые строки попадают на одну и ту же страницу? В данном случае можно наблюдать рост очереди к этой странице. Даже учитывая, что краткая блокировка весьма непродолжительна, она может стать причиной конкуренции при высокой параллельной рабочей нагрузке. У нашего клиента, первый и единственный столбец в индексе являлся монотонно возрастающим ключом. Из-за этого, каждая новая вставка шла на ту же самую страницу в конце В-дерева, пока эта страница не была заполнена. Рабочие нагрузки, которые используют в качестве первичного ключа IDENTITY или другие столбцы с последовательно увеличивающимися значениями, также могут столкнуться с подобной проблемой, если одновременно выполняемая нагрузка достаточно высока.

Решение

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

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

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

Сначала нужно создать функцию и схему секционирования:

CREATE PARTITION FUNCTION pf_hash (INT) AS RANGE LEFT FOR VALUES (0,1,2)
CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY])

Представленный выше пример использует четыре секции. Число необходимых секций зависит от числа активных процессов, выполняющих операции INSERT в описанную выше таблицу. Есть некоторая сложность в секционировании таблицы с помощью хэш-столбца,например в том, что всякий раз, когда происходит выборка строк из таблицы, будут затронуты все секции. Это означает, что придётся обращаться более чем к одному В-дереву, т.е. не будет отброшенных оптимизатором за ненадобностью ненужных секций. Связанная сэтим дополнительная нагрузка на процессоры и некоторое увеличение времени ожиданий процессоров, побуждает минимизировать число планируемых секций (их должно быть минимальное количество, при котором не наблюдается PAGELATCH). В рассматриваемом нами случае, в системе нашего клиента имелось достаточно много резерва в утилизации процессоров, так что было вполне возможно допустить небольшую потерю времени для инструкций SELECT, и при этом увеличить до необходимых объёмов норму инструкций INSERT.

Далее нам требуется столбец, который поможет распределить вставки по четырем секциям. Такого столбца изначально в сценарии Microsoft Enterprise Engineering Center не было. Однако, его всегда достаточно просто создать. Используя тот факт, что столбец ID монотонно увеличивается с приращением равным единице, и здесь легко применима довольно простая хеш-функция:

CREATE TABLE HeavyInsert_Hash(

ID INT NOT NULL , col1 VARCHAR(50) , HashID AS ID % 4 PERSISTED NOT NULL)

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

CREATE UNIQUE CLUSTERED INDEX CIX_Hash
ON HeavyInsert_Hash (ID, HashID) ON ps_hash(HashID)

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

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

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

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

Пример: Чтобы отбросить ненужные секции, можно внести следующие изменения в сценарий:

SELECT * FROM HeavyInsert_Hash

WHERE ID = 42

Который после изменений будет выглядеть так:

SELECT * FROM HeavyInsert_Hash

WHERE ID = 42 AND HashID = 42 % 4

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

Вариант стратегии быстрого и надежного резервного копирования/восстановления VLDB по сети

AlexeiK — Tue, 24 Nov 2009 20:06:00 GMT

Автор: Томас Грохсер (Thomas H. Grohser)

При содействии: Линдсей Аллен (Lindsey Allen)

Техническая экспертиза статьи: Sanjay Mishra, Lubor Kollar, Stuart Ozer, Thomas Kejser, Juergen Thomas, James Podgorski, Burzin Patel

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

Создание 2-террабайтной резервной копии на локальном диске и быстрое восстановление из нее достаточно быстрый процесс, однако он не предоставляет адекватной защиты от форс-мажорной ситуации. С другой стороны, создание резервной копии базы данных по чети на сетевой ресурс предоставляет достаточную защиту от от форс-мажора, но копирование данных по обычной 1-гигабайтной сети займет очень много времени. Когда мы только изучали данную ситуацию, для базового сравнения мы провели создание резервной копии 2-террабайтной базы по 1-гигабайтной сети на сервер, который находился на удалении 10 миль. Процесс занял 24 часа, что было абсолютно недопустимо. Необходимо было создать некое решение, которое позволило бы избежать узких мест в производительности процесса создания резервной копии и дало бы возможность соблюсти требования сервисного соглашения ( SLA).

Собирая решение по кусочкам и проводя бесконечные тесты, нам удалось сократить время создания копии 2-террабайтной базы до 36 минут. Решение, которое мы назвали «многопоточным резервированием по сети» , использует восемь 1-гигабайтных сетевых соединений. Jumbo Frames были настроены на каждой сетевой карте, все сетевые карты были объеденены в один 10-гигабитный канал, который перекинулся ко второму серверу. После этих оптимизаций создание резервной копии заняло 2 часа 15 минут. Использование компресии резервной копии дало возможность сократить время до финальных 36 минут.

Детальное описание проекта можете прочитать во вложении.

Использование SQL Server 2008 в среде Hyper-V. Рекомендации и вопросы производительности

AlexeiK — Thu, 22 Oct 2009 12:40:00 GMT

Авторы: Линдсей Аллен (Lindsey Allen), Майк Рутраф (Mike Ruthruff), Прем Мехра (Prem Mehra)
Технические редакторы: Синди Гросс (Cindy Gross), Бурзин Пател (Burzin Patel), Денни Ли (Denny Lee), Майкл Томасси (Michael Thomassy), Санджай Мишра (Sanjay Mishra), Савитха Падманабхан (Savitha Padmanabhan), Тони Вельм (Tony Voellm), Боб Уорд (Bob Ward)

Основанная на технологии гипервизора функция виртуализации Hyper-V™ представляет собой тонкий слой ПО, связывающий оборудование и ОС Windows Server® 2008, который позволяет одновременно запускать на сервере несколько операционных систем без их модификации. Hyper-V — это мощная технология виртуализации, обеспечивающая консолидацию слабо загруженных корпоративных серверов, что позволяет снизить совокупную стоимость владения при хранении и повысить качество обслуживания.
Hyper-V дает возможность обойти ограничения, связанные с наличием оборудования, и использовать больше разнообразных типов сред разработки и тестирования.
Определение объема оборудования, необходимого для консолидации имеющихся рабочих нагрузок и обеспечения задела для роста, как правило, довольно проблематично. Добавление виртуализации усложняет проблемы планирования ресурсных затрат в неоднородной среде. Данный документ призван помочь в решении этих проблем. Основное внимание уделено следующим двум важным аспектам запуска Microsoft® SQL Server® в среде Hyper-V:

• Дополнительные затраты системных ресурсов, связанные с работой SQL Server в среде Hyper-V
• Масштабируемость Hyper-V при работе с SQL Server 2008
В этом документе описан ряд опробованных тестовых конфигураций, представляющих разнообразные возможные сценарии функционирования SQL Server в среде Hyper-V. В документе изложены полученные результаты и наблюдения, а также представлены рекомендации. Результаты тестов показали, что SQL Server 2008 обеспечивает при работе в среде Hyper-V стабильную производительность и масштабируемость. Мы уверены, что при соответствующих рабочих нагрузках Windows Server 2008 Hyper-V представляет собой надежную платформу для работы SQL Server 2008. Запуск производственных процессов в среде Hyper-V допустим при условии, что рабочая нагрузка не превышает возможностей гостевой виртуальной машины Hyper-V.

Ссылка на статью:

http://download.microsoft.com/download/6/8/8/6886ECEA-04C1-493B-9E2B-6276D93C5669/SQL2008inHyperV2008_RUS.docx

10 рекомендаций по настройке хранилищ данных

AlexeiK — Thu, 30 Oct 2008 18:58:00 GMT

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

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

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

Каково соотношение операций чтения и записи, производимых приложением?
Каковы типичные значения параметров дискового ввода-вывода (число операций ввода-вывода в секунду, объем передаваемых данных в МБ/с, размер операций ввода-вывода)? Проверьте следующие счетчики системного монитора:

Средняя скорость чтения, байт/c (average read bytes/sec), средняя скорость записи, байт/c (average write bytes/sec)
Чтений/с (reads/sec), записей/с (writes/sec)
Скорость чтения с диска (disk read bytes/sec), скорость записи на диск, байт/c (disk write bytes/sec)
Среднее время чтения с диска (average disk sec/read), среднее время записи на диск (average disk sec/write)
Средняя длина очереди диска (аverage disk queue length)

Какая часть операций дискового ввода-вывода использует последовательный доступ, а какая — произвольный? Работает ли приложение в основном в режиме OLTP или в режиме реляционного хранилища данных?

С основными возможностями дискового ввода-вывода в SQL Server можно ознакомиться в статье Основы ввода-вывода в SQL Server 2000 (на английском языке).

Использование большего числа дисков и/или дисков с более высокой скоростью работы положительно скажется на производительности системы

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

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

За исключением случаев, когда досконально известны особенности приложения, не следует дополнительно оптимизировать дисковый ввод- вывод путем выборочного размещения объектов на различных физических дисках.
Обязательно продумайте заранее стратегию роста инфраструктуры. Как будет решаться задача увеличения числа файлов данных, устройств LUN и групп RAID по мере роста объема данных? Лучше спланировать решение этой задачи заранее, чем впоследствии перераспределять нагрузку по файлам данных или LUN в уже работающей системе.

Проверяйте конфигурацию перед развертыванием

Перед развертыванием SQL Server выполните базовое тестирование пропускной способности дисковой подсистемы ввода-вывода. Убедитесь, что на этих тестах достигаются требуемые показатели дискового ввода-вывода с приемлемым временем задержки. Одним из средств, полезных для тестирования, является SQLIO. Вместе с этим средством поставляется документ, в котором описаны основы тестирования дисковой подсистемы ввода-вывода. Загрузите Средство SQLIO для тестирования дисковой подсистемы.
Учтите, что тесты SQLIO не предназначены для точного воспроизведения реальных характеристик дискового ввода-вывода SQL Server. Однако они помогут оценить максимальную пропускную способность, которую можно достичь в подсистеме дискового ввода-вывода для распространенных типов ввода-вывода SQL Server.
В качестве альтернативы средству SQLIO можно использовать средство IOMETER.

Всегда размещайте файлы журналов на дисковых массивах RAID 1+0 (или RAID 1). Это позволит:

обеспечить повышенную защиту от сбоев оборудования;
достичь большей производительности записи.
Примечание. Дисковый массив RAID 1+0 обычно обеспечивает более высокую пропускную способность для приложений, интенсивно нагружающих подсистему записи. Выигрыш в производительности может быть разным в зависимости от реализации RAID на конкретном оборудовании. Самой распространенной альтернативой дисковому массиву RAID 1+0 является массив RAID 5. Однако в общем случаем массив RAID 1+0 обладает самой высокой производительностью среди массивов RAID, предусматривающих защиту данных, включая также и RAID 5.

Изолируйте журнал от данных на уровне физического диска

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

Проверьте конфигурацию базы данных TEMPDB

После установки SQL Server базу данных TEMPDB необходимо переместить в подходящее хранилище и правильно задать для нее исходный размер.
В некоторых случаях (в зависимости от характера использования TEMPDB) можно добиться повышения производительности, если поместить базу данных TEMPDB на дисковый массив RAID 1+0.
Создайте для базы данных TEMPDB один файл данных на каждый ЦП, как описано ниже в пункте 8.

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

Рекомендуется иметь от 0,25 до 1 файла данных (в каждой файловой группе) на каждый процессор сервера.
Это особенно важно для базы данных TEMPDB, для которой рекомендуется создать 1 файл данных на каждый процессор.
Двухъядерные процессоры считаются за 2 процессора, но логические процессоры (технология Hyper-Threading) не считаются отдельными процессорами.

Соблюдайте основные требования SQL Server

Файлы данных должны иметь одинаковый размер. Алгоритм пропорционального заполнения, применяемый в SQL Server, отдает предпочтение файлам с большим объемом свободного места.
Задавайте исходный размер для файлов данных и файлов журнала.
Не полагайтесь на автоматическое расширение этих файлов, включаемое с помощью параметра AUTOGROW; вместо этого управляйте увеличением размера вручную. По соображениям безопасности можно оставить для параметра AUTOGROW значение ON, однако следует заблаговременно увеличивать размер файлов данных.

Соблюдайте основные требования к конфигурации хранилища

Используйте последние версии драйверов HBA, рекомендуемые поставщиком оборудования.
Применяйте специальные версии драйверов, полученные с веб-узла изготовителя HBA и предназначенные для определенного оборудования.
Настройте параметры HBA в соответствии с потребностями томов ввода-вывода. Обычно поставщик оборудования указывает необходимые значения параметров драйвера, однако обнаружилось, что, как правило, значение длины очереди по умолчанию оказывается недостаточным для поддержки томов ввода-вывода SQL Server.
Убедитесь, что прошивка массива хранения данных обновлена до последней рекомендованной версии.
Для равномерного распределения нагрузки по HBA и устройствам LUN применяйте ПО многопутевого ввода-вывода. Убедитесь, что оно работает правильно.
Такое ПО упрощает настройку и помогает в обеспечении доступности.

Технология Microsoft Multipath I/O (MPIO): разработчики оборудования создают модули для отдельных устройств (DSM) на базе пакета разработки драйверов, поставляемого корпорацией Майкрософт.

10 рекомендаций по созданию крупномасштабного реляционного хранилища данных

AlexeiK — Thu, 30 Oct 2008 18:56:00 GMT

Авторы: Стюарт Озер (Stuart Ozer), при участии Према Мехры (Prem Mehra) и Кевина Кокса (Kevin Cox)

Технические редакторы: Любор Коллар (Lubor Kollar), Томас Кейсер (Thomas Kejser), Денни Ли (Denny Lee), Джимми Мэй (Jimmy May), Майкл Редман (Michael Redman), Санджай Мишра (Sanjay Mishra)Построение крупномасштабного реляционного хранилища данных представляет собой сложную задачу. В этой статье описан ряд методов проектирования, которые могут помочь в создании эффективного реляционного хранилища данных большого объема с помощью SQL Server. Поскольку в большинстве крупномасштабных хранилищ данных используется секционирование таблиц и индексов, многие из приводимых рекомендаций также относятся к секционированию. Большинство даваемых советов основаны на опыте построения крупных хранилищ данных на базе SQL Server 2005.

Рассмотрите возможность секционирования крупных таблиц фактов

Рекомендуется разбивать на секции таблицы фактов, имеющие размер от 50 до 100 ГБ и более.
Секционирование может упростить управление данными и часто приводит к повышению производительности. Так, использование секционирования помогает:

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

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

Это позволяет применять метод скользящего окна.

Включается режим «исключения» секций из обработки.

Создайте кластеризованный индекс по ключу даты в таблице фактов

Это позволит эффективно выполнять запросы для заполнения кубов или получения срезов исторических данных.
Если данные загружаются в пакете в рамках определенного временного окна, то для кластеризованного индекса в таблице фактов нужно использовать параметры ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCKS = OFF. Это поможет ускорить операции сканирования таблиц во время запроса и избежать избыточных блокировок во время масштабных операций обновления.
Создайте некластеризованные индексы для каждого внешнего ключа. Это помогает «точечным» запросам извлекать строки на основе избирательного предиката измерения. Используйте файловые группы для администрирования, в том числе для резервного копирования и восстановления, частичного обеспечения доступности базы данных и т. п.

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

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

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

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

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

Если необходимо добиться максимального параллелизма (параметр MAXDOP = 4 или выше), не следует секционировать данные так, чтобы частые запросы затрагивали только 2 или 3 секции.

Правильно проектируйте таблицы измерений

Используйте суррогатные ключи типа integer для всех измерений, кроме измерения даты. Для суррогатных ключей измерения используйте минимальный по размеру тип семейства integer. Это поможет сохранить малый размер таблицы фактов.
Выберите для ключа даты осмысленное целочисленное значение, производное от типа данных DATETIME (например, 20060215).

o Не используйте суррогатный ключ для измерения даты.o Можно легко написать запросы с предложением WHERE, указывающим на этот столбец, что позволит исключать из запроса секции в таблице фактов.

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

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

Создайте некластеризованный индекс первичного ключа по ключу измерения (суррогатному) каждой таблицы измерения.
Создайте некластеризованные индексы по другим столбцам измерения, в которых часто выполняется поиск.
Избегайте секционирования таблиц измерения.
Избегайте создания связей «первичный ключ – внешний ключ» между таблицей фактов и таблицей измерения для ускорения загрузки. Ограничения по внешнему ключу можно создать с указателем NOCHECK — так вы сможете документировать связи; однако не включайте их принудительное применение. Обеспечьте целостность данных с помощью преобразований «Уточняющий запрос» или проводите проверку целостности в источнике данных.

Пишите эффективные запросы для использования механизма исключения секций

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

Используйте для сопровождения данных метод скользящего окна

Используйте метод скользящего временного окна для оперативного доступа к таблицам фактов. Загружайте самые новые данные и выгружайте самые старые.
Всегда сохраняйте пустые секции с обоих концов диапазона секций, чтобы гарантировать, что разбиение секций (перед загрузкой новых данных) и слияние секций (после выгрузки старых данных) не повлекут за собой перемещения данных.
Не следует выполнять разбиение или слияние заполненных секций. Разбиение или слияние заполненных секций может оказаться крайне неэффективным, поскольку в таком случае объем операций с журналом может возрасти до 4 раз, и, кроме того, влечет интенсивную работу механизма блокировок.
Создавайте промежуточную таблицу загрузки в той же файловой группе, где располагается загружаемая секция.
Создавайте промежуточную таблицу выгрузки в той же файловой группе, где располагается удаляемая секция.
Быстрее всего загружать сразу полную новую секцию, но это возможно только в случае, когда размер секции совпадает с периодом загрузки данных (например, используется одна секция для каждого дня и данные загружаются один раз в день).
Если размер секции не совпадает с периодом загрузки данных, следует проводить добавочную загрузку самой последней секции.
Различные варианты массовой загрузки данных в секционированную таблицу обсуждаются в статье с рекомендациями по адресу http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/loading_bulk_data_partitioned_table.mspx.
Всегда выгружайте секции по одной.

Загружайте исходные данные эффективным образом

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

Используйте несколько инструкций BULK INSERT, программу BCP или задачи служб SSIS.

Если в дисковой подсистеме ввода-вывода нет узких мест, для параллельной работы создайте столько сценариев загрузки, сколько процессоров имеется в системе. Если пропускная способность дискового ввода-вывода ограничена, запускайте меньшее число сценариев для параллельной работы.
Установите размер пакета для загрузки в 0.
Установите размер фиксирования данных для загрузки в 0.
Используйте ключевое слово TABLOCK.
Если источники представляют собой плоские файлы, расположенные на одном сервере, используйте инструкцию BULK INSERT. Если данные принудительно отправляются с удаленных компьютеров, используйте программу BCP или службы SSIS.

Создайте кластеризованный индекс для каждой промежуточной таблицы, а затем создайте соответствующие ограничения CHECK. Не используйте параметр SORT_IN_TEMPDB.
Переключите все секции в секционированную таблицу с помощью инструкции SWITCH.
Создайте некластеризованные индексы для секционированной таблицы.
На сервере с 64 процессорами можно загрузить 1 ТБ данных менее чем за час при условии, что сеть SAN обладает пропускной способность 14 ГБ/с (неиндексированная таблица). Дополнительные сведения см. в записи блога SQLCAT http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx.

Удаляйте старые данные эффективным образом

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

Не используйте конструкцию DELETE FROM ...WHERE ...

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

Обычно быстрее оказывается следующий путь.

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

В качестве альтернативы можно выполнять удаление небольшими порциями, организовав цикл следующего вида:
DELETE TOP (1000) ... ;

COMMIT

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

Управляйте статистикой вручную

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

Выбирайте эффективные стратегии резервного копирования

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

Например, резервное копирование базы данных размером 2 ТБ на массив RAID‑5 из 10 дисков по сети SAN может занять 2 ч (при скорости 275 МБ/с).

Хорошим вариантом является резервное копирование путем создания моментального снимка с применением технологии SAN.
Сократите объем данных, которые подвергаются регулярному резервному копированию.

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

Учтите, что инструкции RESTORE для восстановления файловых групп, доступных только для чтения, не могут выполняться параллельно.

русский

10 главных причин использовать SQL Server 2008 для обеспечения высокой производительности решений бизнес-аналитики

Улучшения для разработки

Улучшения диаграмм

Датчики

Улучшениязапросов типа «звезда»

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

ИнструкцияMERGE

Отслеживаниеизмененных данных

ExecutionLog2

Заключение

Уроки по сравнению масштабируемости служб Reporting Services 2008 и Reporting Services 2005

Оптимизация производительности служб Reporting Services

Руководство по обеспечению соответствия требованиям в SQL Server 2008

Руководство по стратегиям консолидаций SQL Server

Руководство по повышению производительности служб Analysis Services

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

10 самых полезных функций SQL Server 2008 для администратора баз данных

10 рекомендаций по созданию крупномасштабного реляционного хранилища данных

Отказоустойчивый кластер SQL Server 2008

Как справиться с PAGELATCH при большом количестве одновременных операций INSERT

Вариант стратегии быстрого и надежного резервного копирования/восстановления VLDB по сети

Использование SQL Server 2008 в среде Hyper-V. Рекомендации и вопросы производительности

10 рекомендаций по настройке хранилищ данных

10 рекомендаций по созданию крупномасштабного реляционного хранилища данных

Улучшения
запросов типа «звезда»

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

Инструкция
MERGE

Отслеживание
измененных данных