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

Надлежащим образом построенные кубы, эффективные многомерные выражения и подходящее оборудование — это наиважнейшие аспекты, влияющие на оптимальность производительности многомерных запросов, выполняемых SQL Server 2005 Analysis Services. В этой статье приводятся 10 самых распространенных рекомендаций, предлагаемых группой разработки Microsoft SQL Server для оптимизации производительности запросов служб Analysis Services. Обсуждение рекомендаций, касающихся оптимизации запросов в службах Analysis Services, см. в статьях Руководство по управлению производительностью служб Analysis Services и Рекомендации по проектированию OLAP для служб Analysis Services 2005.

Оптимизируйте структуру кубов и групп мер

• Определите в каждом измерении каскадные связи атрибутов (например, День > Месяц > Квартал > Год) и пользовательские иерархии связанных атрибутов (такие иерархии называются естественными) в соответствии со структурой данных. Атрибуты, участвующие в естественных иерархиях, материализуются на диске в хранилищах иерархий и автоматически считаются кандидатами для создания агрегатов. Пользовательские иерархии не считают естественными, если атрибуты, заполняющие уровни, не связаны с помощью каскадных связей. В SQL Server 2005 с пакетом обновления 2 (SP2) в среде Business Intelligence Development Studio выводится предупреждение для каждой пользовательской иерархии, которая не определена как естественная.
• Чтобы обработчику запросов было проще создать правильный план запроса, удалите избыточные связи между атрибутами. Атрибуты должны иметь либо прямую, либо косвенную связь с ключевым атрибутом, но не связи обоих типов одновременно.
• Поддерживайте по возможности пространство куба небольшим, добавляя в него только необходимые группы мер.
• Помещайте меры, запросы к которым выполняются одновременно, в одну группу мер. Для запроса, получающего меры из нескольких групп, необходимо несколько операций подсистемы хранилища. Крупные наборы мер, которые не запрашиваются одновременно, разумно размещать в разных группах, однако не следует чрезмерно увеличивать число групп мер.
• Сведите к минимуму число используемых крупных иерархий типа «родители-потомки». В иерархиях типа «родители-потомки» агрегаты создаются только для ключевого атрибута и атрибута верхнего уровня (например, атрибута «Все»), если он не отключен. В результате, если возвращаются ячейки, расположенные на промежуточных уровнях, данные вычисляются в ходе запроса, и работа таких запросов для крупных измерений типа «родители-потомки» может замедлиться. В проекте, где участвует крупная иерархия типа «родители-потомки» (более 250 тыс. элементов), может быть разумным изменить исходную схему, чтобы частично или полностью реорганизовать иерархию в пользовательскую, с фиксированным числом уровней.
• Оптимизируйте производительность измерений «многие ко многим», если они используются. Если выполняется запрос к группе мер данных по измерению «многие ко многим», во время выполнения запроса создается объединение (join), и временно созданная промежуточная группа мер использует гранулярность атрибутов измерений, общих для групп мер. По возможности следует сократить размер промежуточной таблицы фактов, на которой строится промежуточная группа мер. Чтобы оптимизировать объединение, проверьте схему агрегирования для промежуточной группы мер и убедитесь, что в агрегаты входят атрибуты из измерения «многие ко многим».

Сведения об оптимизации измерений для повышения производительности запросов см. в статьях Руководство по управлению производительностью служб SQL Server 2005 Analysis Services и Рекомендации по проектированию OLAP для служб Analysis Services 2005. Для помощи в анализе структуры данных с точки зрения соответствия рекомендациям предназначена CTP-версия анализатора соответствия рекомендациям для SQL Server 2005 за февраль 2007 г. (скоро должна быть выпущена конечная версия).

Определите эффективные агрегаты

• Чтобы сократить число записей, которые подсистема хранилища должна считать с диска для выполнения запроса, определите агрегаты. Если трассировки приложения SQL Server Profiler показывают, что большинство пользовательских запросов, которые не выполняются из кэша, считывают данные из секций, а не из агрегатов, то рекомендуется создавать нестандартные статистические выражения с помощью приложения Aggregation Manager. Это приложение доступно на узле CodePlex по адресу http://www.codeplex.com/MSFTASProdSamples. Версия приложения, доработанная участниками сообщества, находится по адресу http://www.codeplex.com/bidshelper.
• Избегайте создания чрезмерно большого числа агрегатов. Лишние агрегаты снижают скорость обработки и могут негативно отразиться на производительности запросов. Оптимальное число агрегатов может различаться, но по опыту группы SQL Server Best Practices в большинстве случаев оптимальное количество агрегатов будет исчисляться десятками, а не сотнями и тысячами.
• Чтобы собрать сведения о закономерностях, проявляющихся в пользовательских запросах, включите журнал запросов Analysis Services и используйте его в создании статистических схем. Дополнительные сведения см. в разделе Настройка журнала служб Analysis Services.

Сведения о создании агрегатов для повышения производительности запросов см. в статьях Руководство по управлению производительностью служб SQL Server 2005 Analysis Services и Рекомендации по проектированию OLAP для служб Analysis Services 2005.

Используйте секции

• Определите секции, чтобы службы Analysis Services могли сканировать меньший объем данных для выполнения запроса в случаях, когда запрос не может быть выполнен, используя данные из кэша или агрегатов. Кроме того, определять секции нужно, чтобы повысить параллелизм при выполнении запросов.
• Для достижения оптимальной производительности данные следует разбивать на секции таким образом, чтобы удовлетворить наиболее частым запросам. Очень часто в создании секций применяется следующий подход: выбирается элемент времени (например, день, месяц, квартал, год) или сочетание элементов времени. Следует избегать вариантов секционирования, в которых большинство запросов будут использовать данные из нескольких секций.
• В большинстве случаев размер секции должен быть меньше 20 млн записей, а каждая группа мер должна содержать менее 2 тыс. секций. Кроме того, избегайте определения секций, содержащих менее 2 млн записей. Слишком большое число секций вызывает замедление операций с метаданными, но слишком малое число секций оборачивается упущенными возможностями организации параллелизма.
• Определите отдельную секцию ROLAP для данных, изменяемых в реальном времени, и поместите секцию ROLAP в собственную группу мер.

Рекомендации о создании секций для повышения производительности запросов см. в статьях Руководство по управлению производительностью служб SQL Server 2005 Analysis Services и Рекомендации по проектированию OLAP для служб Analysis Services 2005, а также в блоге группы поддержки пользователей Microsoft SQL Server.

Пишите эффективные многомерные выражения

• Удалите пустые кортежи из результирующего набора, чтобы сократить время, затрачиваемое обработчиком запросов на упорядочивание результирующего набора.
• В вычислении многомерных выражений следует избегать динамических проверок, так как они замедляют выполнение запроса. Если для проверки условий используются функции Case и IF, которые должны многократно выполняться в ходе выполнения запроса, заброс будет выполнен по самому медленному пути. Перепишите такие запросы, используя функцию SCOPE, чтобы сократить объем вычислений, необходимых для вычисления многомерного выражения. Дополнительные сведения см. в разделах Бюджетное отклонение — анализ оптимизации многомерных выражений: режимы вычисления и NON_EMPTY_BEHAVIOR, Сравнение уровней в многомерных выражениях, ключевые слова CONDITION и SCOPE в вычислении ячеек и Вычисление многомерных выражений с поддержкой множественного выбора.
• Чтобы включить для обработчика запросов режим массовой оценки, используйте свойство Non_Empty_Behavior везде, где это возможно. Однако, если использовать свойство Non_Empty_Behavior неправильно, будут возвращаться неверные результаты. Дополнительные сведения см. в разделах Бюджетное отклонение — анализ оптимизации многомерных выражений: режимы вычисления и NON_EMPTY_BEHAVIOR и Средние значения, коэффициенты, деление на ноль и NON_EMPTY_BEHAVIOR.

· Чтобы избежать медленного пути выполнения, вместо фильтрации по свойствам элементов используйте функцию EXISTS. Чтобы обработчик запросов работал в режиме массовой оценки, используйте функции NonEmpty и Exists везде, где это возможно.

• Работайте со строками внутри хранимых процедур служб Analysis Services, используя функции ADOMD.NET на сервере, а не функции для работы со строками, такие как StrToMember и StrToSet.
• Вместо функции LookupCube везде, где возможно, используйте несколько групп мер в одном кубе.
• Везде, где возможно, перепишите запросы многомерных выражений, содержащие произвольные формы, чтобы устранить излишние вложенные запросы. Набор произвольной формы — это набор элементов, который нельзя разрешить как перекрестное соединение наборов в пределах одной иерархической структуры. Например, набор {(Gender.Male, Customer.USA), (Gender.Female, Customer.Canada)} имеет произвольную форму. Часто с помощью функции Descendants можно разрешить произвольные формы, используя меньшее число вложенных запросов по сравнению с запросами, возвращающими тот же результат и написанными с использованием других функций.
• Там, где это возможно, перепишите запросы многомерных выражений, которые приводят к большому объему упреждающей выборки. Термин упреждающая выборка применяется для описания ситуаций, когда обработчик запросов запрашивает из подсистемы хранилища больше данных, чем необходимо для выполнения текущего запроса, с целью повышения эффективности принятия данных. Обычно упреждающая выборка является самым эффективным методом получения данных, однако иногда это не так. В некоторых случаях исключить излишнюю упреждающую выборку можно, если переписать запросы, указав подзапрос выборки в предложении FROM вместо набора в предложении WHERE. Если нельзя исключить излишние операции упреждающей выборки, может понадобиться отключить упреждающую выборку и подготовить кэш с помощью инструкции CREATE CACHE. Дополнительные сведения см. в разделе Как подготовить кэш данных служб Analysis Services с помощью инструкции CREATE CACHE.
• Перед выполнением перекрестного соединения отфильтруйте участвующий в нем набор, чтобы сократить размер куба.

Эффективно используйте кэш обработчика запросов

• Убедитесь, что компьютер со службами Analysis Services оснащен достаточным объемом памяти, чтобы хранить в ней результаты запросов и использовать их для выполнения последующих запросов. Для мониторинга используйте счетчики системного монитора MSAS 2005: Memory/Cleaner Memory Shrinkable DB и MSAS 2005: Cache/Evictions/sec.
• Определите вычисления в сценарии многомерных выражений. Вычисления в сценарии многомерных выражений имеют глобальную область действия, что позволяет сеансам с одинаковым набором прав доступа совместно использовать кэш, относящийся к этим запросам. Однако вычисляемые элементы, определенные с помощью выражений Create Member и With Member в составе пользовательских запросов, не имеют глобальной области действия, и сеансы не могут совместно использовать кэш, относящийся к подобным запросам.
• Подготовьте кэш, выполнив набор стандартных запросов в любом из программных средств. Для этого также можно использовать инструкцию CREATE CACHE. Дополнительные сведения об использовании инструкции CREATE CACHE см. в разделе Как подготовить кэш данных служб Analysis Services с помощью инструкции CREATE CACHE. Сведения об использовании служб SQL Server 2005 Integration Services для подготовки кэша см. в разделе Построение модуля для подготовки кэша служб Analysis Services в службах Integration Services.
• Для оптимизации кэширования перепишите запросы многомерных выражений, содержащие произвольные формы. Например, в некоторых случаях можно переписать запросы, которым необходим некэшируемый доступ к диску, чтобы их стало возможным целиком выполнить из кэша. Для этого вместо предложения WHERE используйте подзапрос выборки в приложении FROM. В других случаях предложение WHERE может оказаться лучшим выбором.

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

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

Сведения о том, как избежать удаления гибких агрегатов, см. в разделе Руководство по управлению производительностью служб SQL Server 2005 Analysis Services.

Настройте использование памяти

• Увеличьте размер файлов подкачки на сервере служб Analysis Services или оснастите его дополнительной памятью, чтобы предотвратить ошибки, вызванные нехваткой памяти, когда выделяемый объем виртуальной памяти превышает объем физической памяти сервера служб Analysis Services.
• Используйте ОС Microsoft Windows Advanced Server® или Datacenter Server с выпуском SQL Server 2005 Enterprise Edition (или SQL Server 2005 Developer Edition), чтобы службы Analysis Services могли адресовать до 3 ГБ памяти, если используется 32-разрядная версия SQL Server 2005. Чтобы службы Analysis Services поддерживали адресацию более 2 ГБ физической памяти в одном из этих выпусков сервера, используйте параметр /3GB в файле boot.ini. Если в файле boot.ini указан параметр /3GB, сервер должен иметь не менее 4 ГБ памяти, чтобы гарантировать достаточный объем памяти для системных служб операционной системы Windows.
• Если на одном компьютере запущено несколько экземпляров служб Analysis Services или вместе со службами работают другие приложения, уменьшите значение свойства Memory/LowMemoryLimit, чтобы оно составляло менее 75%.
• Если на одном компьютере запущено несколько экземпляров служб Analysis Services или вместе со службами работают другие приложения, уменьшите значение свойства Memory/TotalMemoryLimit, чтобы оно составляло менее 80%.
• Следует сохранять разницу между значениями свойств Memory/LowMemoryLimit и Memory/TotalMemoryLimit. Часто используется разница в 20%.
• Если в многопользовательской среде обнаруживается пробуксовка в работе с памятью, обратитесь в службу технической поддержки Майкрософт за содействием в изменении свойства MemoryHeapType.

· Если используется архитектура NUMA и функция VirtualAlloc долгое время не возвращает данные или не отвечает на запросы, выполните обновление до версии SQL Server 2005 с пакетом обновления 2 (SP2) и обратитесь в службу технической поддержки Майкрософт за содействием в настройке параметров предварительного выделения памяти NUMA.

Анализ причин, по которым следует изменять параметры использования памяти по умолчанию, см. в статье Руководство по управлению производительностью служб SQL Server 2005 Analysis Services и в блоге группы поддержки пользователей Microsoft SQL Server.

Настройте использование процессора

· Чтобы повысить параллелизм работы запросов для серверов с несколькими процессорами, рекомендуется изменить параметры Threadpool\Query\MaxThreads и Threadpool\Process\MaxThreads, указав для них число, связанное с количеством процессоров.

· В общем случае рекомендуется установить для параметра Threadpool\Query\MaxThreads значение, меньшее или равное удвоенному количеству процессоров на сервере. Например, если используется сервер с 8 процессорами, рекомендуется установить значение не более 16. С практической точки зрения увеличение значения параметра Threadpool\Query\MaxThreads незначительно увеличит производительность отдельно взятого запроса. Однако большее значение этого свойства позволяет увеличить число одновременно обслуживаемых запросов.

· Для параметра Threadpool\Process\MaxThreads в общем случае рекомендуется установить значение, меньшее или равное десятикратному количеству процессоров на сервере. Это свойство управляет числом потоков, используемых подсистемой хранилища в ходе операций запроса и обработки. Например, если используется сервер с 8 процессорами, рекомендуется установить значение не более 80. Примечание: хотя значение по умолчанию равно 64, если на заданном сервере работают менее 8 процессоров, значение по умолчанию не нужно снижать для регулирования параллельных операций.

· Изменение свойств Threadpool\Process\MaxThreads и Threadpool\Query\MaxThreads может повысить степень параллелизма в работе запросов, однако необходимо также учитывать дополнительный эффект параметра CoordinatorExecutionMode. Например, если используется сервер с 4 процессорами и используется значение параметра CoordinatorExecutionMode, по умолчанию равное 4, то для всех операций сервера одновременно может выполняться 16 заданий. Поэтому, если параллельно выполняются 10 запросов, которым суммарно необходимо 20 заданий, одновременно могут начаться только 16 заданий (предполагается, что в это время не выполняются операции обработки). Если достигается пороговое количество заданий, последующие задания ожидают в очереди до момента, когда станет возможным создать новое задание. Таким образом, если узким местом операции является количество заданий, увеличение числа потоков необязательно приведет к повышению общей производительности.

По возможности применяйте вертикальное масштабирование

· Для всех крупных систем используйте 64-разрядную архитектуру.

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

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

• Если узким местом производительности является использование процессора в отдельной системе и недостаток ресурсов вызывается нагрузкой со стороны запросов от нескольких пользователей, можно повысить производительность запросов с помощью кластера из серверов служб Analysis Services, который будет обслуживать запросы. Нагрузку по обработке запросов можно распределить по двум серверам служб Analysis Services или большему числу серверов, что позволит одновременно поддерживать большее число пользователей (такая структура называется фермой серверов). Кластеры с распределением нагрузки обычно масштабируются линейно.
• Если для повышения производительности запросов используется кластер из серверов служб Analysis Services, выполните обработку на одном сервере обработки, а затем проведите синхронизацию серверов обработки и запросов с помощью инструкции Synchronize XML для аналитики, скопируйте каталог базы данных с помощью Robocopy или другой программы для копирования файлов либо используйте средство высокоскоростного копирования из состава решений по хранению данных в сетях SAN.