Рекомендации по проектированию SQL Server

System Center Operations Manager требует доступа к экземпляру сервера Microsoft SQL Server для поддержки рабочей базы данных, базы данных хранилища данных и базы данных аудита ACS. Базы операционных данных и базы хранилищ данных являются обязательными и создаются при развертывании первого сервера управления в группе управления, в то время как база данных ACS создается при развертывании коллектора ACS в группе управления.

В лабораторной среде или небольших развертываниях Operations Manager сервер SQL Server можно размещать вместе с другими компонентами на первом сервере управления в группе управления.

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

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

Важно!

Operations Manager не поддерживает экземпляры SQL "платформа как услуга" (PaaS), включая такие продукты, как Управляемый экземпляр SQL Azure или Amazon Relational Database Service (AWS RDS). Используйте экземпляр SQL Server, установленный на компьютере с Windows. Единственным исключением является Управляемый экземпляр SCOM Azure Monitor, которая использует Azure SQL MI и не настраивается повторно.

Требования к SQL Server

В установленной версии System Center Operations Manager для размещения сервера отчетов, рабочей базы данных, хранилища данных и базы данных ACS поддерживаются следующие версии выпусков SQL Server Enterprise и Standard:

• SQL Server 2022

• SQL Server 2019 с накопительным пакетом обновления 8 (CU8) или более поздней версии, как описано здесь

  Примечание

  • Operations Manager 2022 поддерживает SQL 2019 с накопительным пакетом обновления 8 (CU8) или более поздней версии; однако он не поддерживает SQL 2019 RTM.
  • Используйте ODBC 17.3 или более поздней версии и MSOLEDBSQL 18.2 или более поздней версии.

• SQL Server 2017 и накопительные пакеты обновления, как описано здесь.

Перед обновлением SQL Server см. сведения об обновлении за 2017 год и сведения об обновлении SQL 2019.

Примечание

• Все следующие компоненты SQL Server, поддерживающие инфраструктуру SCOM, должны находиться в одной и той же основной версии SQL Server:
  • SQL Server экземпляры ядра СУБД, в котором размещаются базы данных SCOM (то есть базы данных OperationManager, OperationManagerDW и SSRS ReportServer & ReportServerTempDB).
  • экземпляр SQL Server Reporting Services (SSRS).
• Параметр сортировки SQL Server должен быть поддерживаемого типа, как описано в разделе Параметр сортировки SQL Server ниже.
• Функция полнотекстового поиска SQL Server требуется для всех экземпляров ядра СУБД SQL Server, в которых размещены базы данных SCOM.
• Параметры установки Windows Server 2016 (Server Core, сервер с возможностями рабочего стола и Nano Server), поддерживаемые компонентами базы данных Operations Manager, основаны на параметрах установки Windows Server, поддерживаемых службой SQL Server.

Примечание

Отчеты System Center Operations Manager невозможно установить параллельно с предыдущей версией роли отчетов и должны устанавливаться только в собственном режиме (режим интеграции с SharePoint не поддерживается).

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

• Рекомендуется запускать SQL Server на компьютерах с форматом файлов NTFS.
• Для рабочей базы данных и базы данных хранилища данных требуется не менее 1024 МБ свободного дискового пространства. Он применяется во время создания базы данных и, скорее всего, значительно возрастет после установки.
• Требуется платформа .NET Framework 4.
• Платформа .NET Framework 4.8 поддерживается в Operations Manager 2022.
• Сервер отчетов не поддерживается в Windows Server Core.

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

Примечание

Хотя Operations Manager использует только проверка подлинности Windows во время установки, параметр смешанной проверки подлинности SQL по-прежнему будет работать, если ни у локальной учетной записи нет роли db_owner. Локальные учетные записи с ролью db_owner, как известно, вызывают проблемы с System Center Operations Manager. Удалите роль db_owner из всех локальных учетных записей перед установкой продукта и не добавляйте роль db_owner ни в одну из локальных учетных записей после установки.

Настройка параметров сортировки SQL Server

System Center Operations Manager поддерживает следующие типы параметров сортировки SQL Server и Windows.

Примечание

Чтобы избежать проблем с совместимостью при сравнении или копировании, рекомендуется использовать одинаковые параметры сортировки для баз данных SQL и Operations Manager.

Параметры сортировки SQL Server

• SQL_Latin1_General_CP1_CI_AS

Параметры сортировки Windows

• Latin1_General_100_CI_AS
• French_CI_AS
• French_100_CI_AS
• Cyrillic_General_CI_AS
• Chinese_PRC_CI_AS
• Chinese_Simplified_Pinyin_100_CI_AS
• Chinese_Traditional_Stroke_Count_100_CI_AS
• Japanese_CI_AS
• Japanese_XJIS_100_CI_AS
• Traditional_Spanish_CI_AS
• Modern_Spanish_100_CI_AS
• Latin1_General_CI_AS
• Cyrillic_General_100_CI_AS
• Korean_100_CI_AS
• Czech_100_CI_AS
• Hungarian_100_CI_AS
• Polish_100_CI_AS
• Finnish_Swedish_100_CI_AS

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

Настройка брандмауэра

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

Если вы разрабатываете распределенное развертывание, которое требует, чтобы группы доступности SQL Always On предоставляли возможности отработки отказа для баз данных Operations Manager, в стратегию безопасности брандмауэра необходимо включить дополнительные параметры конфигурации брандмауэра.

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

Сценарий	Port	Direction (направление)	Роль Operations Manager
Сервер SQL Server, на котором размещены базы данных Operations Manager	TCP 1433 *	Входящие	сервер управления и веб-консоль (для советника по приложениям и диагностики приложений)
Служба обозревателя SQL Server	UDP 1434	Входящие	сервер управления
Выделенное соединение администратора SQL Server	TCP 1434	Входящие	сервер управления
Дополнительные порты, используемые SQL Server — Удаленные вызовы процедур (Майкрософт) (MS RPC) — Инструментарий управления Windows (WMI) — Координатор распределенных транзакций (Майкрософт) (MS DTC)	TCP 135	Входящие	сервер управления
Прослушиватель группы доступности SQL Server Always On	Порт, настраиваемый администратором	Входящие	сервер управления
Службы SQL Server Reporting Services, в которых размещен сервер отчетов Operations Manager	TCP 80 (по умолчанию)/443 (SSL)	Входящие	сервер управления и консоль управления

* TCP 1433 — стандартный порт для экземпляра ядра СУБД по умолчанию. Но если создать именованный экземпляр на автономном сервере SQL Server или развернуть группу доступности SQL AlwaysOn, будет определен пользовательский порт, который нужно зафиксировать документально, чтобы правильно настроить брандмауэры и ввести соответствующую информацию во время настройки.

Более подробные сведения о требованиях брандмауэра к SQL Server см. в статье Настройка брандмауэра Windows для разрешения доступа к SQL Server.

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

база данных Operations Manager

База данных Operations Manager представляет собой базу данных SQL Server, которая содержит все данные, необходимые Operations Manager для ежедневного мониторинга. Изменение размера и конфигурация сервера базы данных имеют критическое значение для общей производительности группы управления. Наиболее важный ресурс, используемый базой данных Operations Manager, — это подсистема хранения, однако ЦП и ОЗУ также играют важную роль.

Факторы, влияющие на загрузку базы данных Operations Manager, включают следующее:

• Скорость сбора рабочих данных. Рабочие данные состоит из всех событий, предупреждений, изменений состояния и данных производительности, собранных агентами. Большая часть ресурсов, используемых базой данных Operations Manager, применяется для записи этих данных на диск, когда они поступают в систему. Скорость сбора рабочих данных увеличивается при импорте дополнительных пакетов управления и добавлении дополнительных агентов. Тип компьютера, отслеживаемого агентом, — также важный фактор при определении совокупной скорости сбора рабочих данных. Например, ожидается, что агент, который наблюдает за критически важным для бизнеса настольным компьютером, будет собирать меньше данных, чем агент, отслеживающий сервер, на котором выполняется экземпляр SQL Server с большим количеством баз данных.
• Скорость изменения пространства экземпляров. Обновление этих данных в базе данных Operations Manager достаточно затратно по сравнению с записью новых рабочих данных. Кроме того, при изменении данных пространства экземпляров серверы управления отправляют дополнительные запросы в базу данных Operations Manager, чтобы вычислить изменения конфигурации и групп. Скорость изменения пространства экземпляров увеличивается при импорте дополнительных пакетов управления в группу управления. Добавление новых агентов в группу управления также временно увеличивает скорость изменения пространства экземпляров.
• Количество консолей управления и других подключений SDK, работающих одновременно. Каждая консоль управления считывает данные из базы данных Operations Manager. Запрос этих данных потребляет потенциально большие объемы ресурсов ввода-вывода хранилища, время ЦП и ОЗУ. Консоли управления, на которых отображаются большие объемы рабочих данных в представлениях "События", "Состояние", "Предупреждения" и "Данные производительности", обеспечивают наибольшую нагрузку на базу данных.

База данных Operations Manager — это единственный источник сбоя для группы управления, поэтому с помощью поддерживаемых конфигураций отработки отказа (групп доступности SQL Server AlwaysOn и экземпляров отказоустойчивых кластеров) можно обеспечить высокую доступность этой базы данных.

Вы можете настроить и обновить базы данных Operations Manager с помощью существующей настройки Always-On SQL без необходимости вносить изменения после конфигурации.

Включение SQL Broker в базе данных Operations Manager

Реализация всех операций задач в System Center Operations Manager зависит от SQL Server Service Broker. Если SQL Server Service Broker отключен, это повлияет на все операции задач. Итоговое поведение может варьироваться в зависимости от инициируемой задачи. Поэтому важно проверка состояние компонента Service Broker SQL Server всякий раз, когда в System Center Operations Manager наблюдается непредвиденное поведение.

Чтобы включить SQL Server Service Broker, выполните следующие действия:

1. Выполните следующий запрос SQL:

   SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'
   

2. Пропустите этот шаг, если значение, отображаемое в is_broker_enabled поле, равно is_broker_enabled (один). Если значение другое, выполните следующий запрос SQL:

   ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   ALTER DATABASE OperationsManager SET ENABLE_BROKER
   ALTER DATABASE OperationsManager SET MULTI_USER
   

База данных хранилища данных Operations Manager

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

Факторы, влияющие на нагрузку на хранилище данных отчетов, включают следующее:

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

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

• Выберите соответствующую подсистему хранения. Так как хранилище данных отчетов — это неотъемлемая часть общего потока данных в группе управления, важно выбрать подходящую подсистему хранения для хранилища данных отчетов. При работе с базой данных Operations Manager лучше всего выбрать схему RAID 0 + 1. В общем случае подсистема хранения хранилища данных отчетов должна быть схожа с подсистемой хранения для базы данных Operations Manager, и инструкции, применимые к базе данных Operations Manager, также применяются к хранилищу данных отчетов.
• Рассмотрите возможность правильного размещения журналов данных и журналов транзакций. Что касается базы данных Operations Manager, отделение данных SQL от журналов транзакций часто целесообразно при горизонтальном увеличении масштаба для числа агентов. Если база данных Operations Manager и хранилище данных отчетов находятся на одном сервере и требуется разделить данные и журналы транзакций, необходимо поместить журналы транзакций для базы данных Operations Manager в отдельный физический том и на разные шпиндели дисков, чем хранилище данных отчетов, чтобы извлечь какие-либо преимущества. Файлы данных для базы данных Operations Manager и хранилища данных отчетов могут совместно использовать один и тот же физический том, если он обеспечивает достаточную емкость, а производительность операций ввода-вывода на диске не влияет на функции мониторинга и создания отчетов.
• Рекомендуется поместить хранилище данных отчетов на отдельный сервер от базы данных Operations Manager. Хотя в небольших развертываниях часто можно консолидировать базу данных Operations Manager и хранилище данных отчетов на одном сервере, их целесообразно разделять по мере увеличения числа агентов и объема входящих операционных данных. Если хранилище данных отчетов и сервер отчетов находятся на одном сервере, а база данных Operations Manager — на другом, производительность отчетности повышается.

База данных хранилища данных Operations Manager — это единственный источник сбоя для группы управления, поэтому с помощью поддерживаемых конфигураций отработки отказа (групп доступности SQL Server AlwaysOn и экземпляров отказоустойчивых кластеров) можно обеспечить высокую доступность этой базы данных.

SQL Server AlwaysOn

Группы доступности SQL Server AlwaysOn поддерживают среды отработки отказа для дискретного набора пользовательских баз данных (баз данных доступности). Каждый набор баз данных доступности размещается в реплике доступности.

В System Center 2016 Operations Manager и белее поздних версиях для обеспечения высокой доступности баз данных предпочтительнее использовать SQL AlwaysOn, нежели отказоустойчивую кластеризацию. Все базы данных, кроме установки служб отчетности в основном режиме, где для разделения постоянного и временного хранилища данных используются две базы данных, можно разместить в группе доступности AlwaysOn.

Operations Manager 2022 позволяет вам настраивать и обновлять имеющиеся базы данных Operations Manager, используя текущую конфигурацию SQL Always-On без необходимости ее дополнительного изменения.

Чтобы настроить группу доступности, необходимо развернуть кластер отказоустойчивой кластеризации Windows Server (WSFC) для размещения реплика доступности и включить Always On на узлах кластера. После этого можно добавить базу данных SQL Server Operations Manager как базу данных доступности.

• Дополнительные сведения о предварительных требованиях Always On.
• Узнайте больше о настройке WSFC для Always On групп доступности.
• Узнайте больше о настройке группы доступности.

Примечание

Развернув Operations Manager на узлах SQL Server, входящих в SQL Always On, для включения режима строгой безопасности в среде CLR запустите сценарий SQL для каждой базы данных Operations Manager.

Строка с несколькими подсетями

Operations Manager не поддерживает ключевые слова строка подключения (MultiSubnetFailover=True). Так как в группе доступности есть имя прослушивателя (также известно как имя сети или точка доступа клиента в диспетчере кластеров WSFC), зависимое от множества IP-адресов из разных подсетей, например, при развертывании в межсайтовой отказоустойчивой конфигурации, время ожидания у запросов клиентских подключений от серверов управления к прослушивателю группы доступности будет истекать.

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

1. Задайте сетевое имя прослушивателя группы доступности, чтобы зарегистрировать только один активный IP-адрес в DNS.
2. Настройте кластер так, чтобы для зарегистрированной записи DNS использовался низкий срок жизни.

Эти параметры обеспечивают более быстрое восстановление и разрешение имени кластера с новым IP-адресом при отработке отказа на узле в другой подсети.

Выполните следующие команды PowerShell на любом из узлов SQL, чтобы изменить его параметры:

Import-Module FailoverClusters
Get-ClusterResource "Cluster Name"|Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource "Cluster Name"|Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource "Cluster Name"
Start-ClusterResource "Cluster Name"
Start-ClusterGroup "Cluster Name"

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

Выполните следующие команды PowerShell на узле SQL, где сейчас размещен прослушиватель, чтобы изменить его параметры:

Import-Module FailoverClusters
Get-ClusterResource <Listener Cluster Resource name> | Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource <Listener Cluster Resource name> | Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource <Listener Cluster Resource name>
Start-ClusterResource <Listener Cluster Resource name>
Start-ClusterGroup <Listener Cluster Group name>

Если для обеспечения высокой доступности используется кластеризованный экземпляр SQL или экземпляр SQL AlwaysOn, на серверах управления следует включить функцию автоматического восстановления, чтобы избежать перезапуска службы доступа к данным Operations Manager при каждой отработке отказа между узлами. Сведения о том, как выполнить эту настройку, см. в статье базы знаний The System Center Management service stops responding after an instance of SQL Server goes offline (Служба управления System Center Management перестает отвечать на запросы после перехода экземпляра SQL Server в автономный режим).

Оптимизация SQL Server

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

• Недостаточное выделение шпинделей для LUN, чтобы выполнить требования Operations Manager в отношении операций ввода-вывода.
• Размещение журналов транзакций и файлов базы данных в одном томе. Эти две рабочие нагрузки имеют разные характеристики ввода-вывода и задержки.
• Конфигурация TempDB неверна в отношении размещения, изменения размера и т. д.
• Неправильное соответствие разделов диска томам, на котором размещаются журналы транзакций базы данных, файлы базы данных и TempDB.
• Игнорируя базовую конфигурацию SQL Server, например использование AUTOGROW для файлов базы данных и журналов транзакций, параметр MAXDOP для параллелизма запросов, создание нескольких файлов данных TempDB на ядро ЦП и т. д.

Конфигурация хранилища является одним из важных компонентов для развертывания сервера SQL Server для Operations Manager. Серверы баз данных обычно больше зависят от операций ввода-вывода из-за активного выполнения в базе данных операций чтения и записи и обработки журналов транзакций. Типичное для Operations Manager распределение операций ввода-вывода: 80 % операций записи и 20 % операций чтения. В результате неправильная настройка подсистем ввода-вывода может привести к низкой производительности и неправильной работе систем SQL Server, что проявляется в Operations Manager.

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

Размер единицы размещения NTFS

Выравнивание томов, которое часто также называют выравниванием секторов, следует выполнять в файловой системе (NTFS) всякий раз при создании тома на устройстве RAID. Если это не сделать, это может привести к значительному снижению производительности и, как правило, является результатом несоответствия секций с чередующиеся границы единиц. Это также может привести к неправильному согласованию аппаратного кэша и снижению эффективности кэша массива. При форматировании секции, которая будет использоваться для файлов SQL Server данных, рекомендуется использовать размер единицы выделения 64 КБ (то есть 65 536 байт) для данных, журналов и базы данных tempdb. Однако имейте в виду, что использование размеров единиц распределения больше 4 КБ приводит к невозможности использовать сжатие NTFS на томе. Хотя SQL Server поддерживает данные только для чтения на сжатых томах, это не рекомендуется.

Резервирование памяти

Примечание

большая часть информации в этом разделе взята из публикации Джонатана Кехайаса (Jonathan Kehayias) в своем блоге Сколько памяти на самом деле нужно моему SQL Server? (sqlskills.com).

Не всегда можно легко определить правильное количество физической памяти и процессоров, выделяемых для SQL Server в поддержке System Center Operations Manager (или для других рабочих нагрузок за пределами этого продукта). Калькулятор, предоставляемый группой продуктов, дает рекомендации с учетом масштабирования рабочей нагрузки, но эти рекомендации основаны на тестировании, выполняемом в лабораторной среде, которая может не соответствовать реальной рабочей нагрузке и конфигурации.

SQL Server позволяет настроить минимальный и максимальный объем памяти, который следует зарезервировать и использовать соответствующим процессом. По умолчанию SQL Server может менять свои требования к памяти динамически, с учетом доступных системных ресурсов. По умолчанию параметр min server memory имеет значение 0, а параметр max server memory — 2 147 483 647 МБ.

Если не задать соответствующее значение для параметра max server memory, могут возникнуть проблемы с производительностью и памятью. Многие факторы влияют на то, какой объем памяти необходимо выделить для SQL Server, чтобы операционная система могла поддерживать другие процессы, работающие в этой системе, такие как карта HBA, агенты управления и сканирование в реальном времени для антивирусной программы. Если не задан достаточный объем памяти, операционная система и SQL сохраняет страницы памяти на диске. Это может привести к росту количества дисковых операций ввода-вывода, что еще больше понизит производительность и создаст эффект пульсации, когда она станет заметной в Operations Manager.

Для параметра min server memory рекомендуется указать не менее 4 ГБ ОЗУ. Это необходимо сделать для каждого узла SQL, на котором размещается одна из баз данных Operations Manager (рабочая, хранилище данных, ACS).

Для параметра max server memory рекомендуется сначала зарезервировать:

• 1 ГБ ОЗУ для ОС;
• 1 ГБ ОЗУ на каждые 4 ГБ установленного ОЗУ (до 16 ГБ ОЗУ)
• 1 ГБ ОЗУ на каждые 8 ГБ установленного ОЗУ (свыше 16 ГБ ОЗУ)

Завершив настройку этих значений, проследите за счетчиком Память\Доступно МБ в Windows, чтобы определить, можно ли увеличить объем памяти, доступный для SQL Server. Windows сообщает, что объем доступной физической памяти не превышает 96 МБ, поэтому в идеале значение счетчика не должно превышать 200–300 МБ, чтобы обеспечить наличие буфера. Для серверов с 256 ГБ ОЗУ или выше вы, вероятно, захотите убедиться, что он не будет работать меньше 1 ГБ.

Помните, что в этих вычислениях предполагается, что вы хотите, чтобы SQL Server могли использовать всю доступную память, если только вы не измените подход для учета других приложений. Учитывайте определенные требования к памяти для вашей ОС, других приложений, стека потоков SQL Server и других многостраничных распределителей. Типичная формула — это ((total system memory) – (memory for thread stack) – (OS memory requirements) – (memory for other applications) – (memory for multipage allocators)), где память для стека потока = ((max worker threads) (stack size)). Размер стека составляет 512 КБ для систем x86, 2 МБ для 64-разрядных систем и 4 МБ для систем IA64. Кроме того, можно найти значение для параметра max worker threads в столбце max_worker_count в sys.dm_os_sys_info.

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

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

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

Дополнительные сведения см. в статье Параметры конфигурации памяти сервера.

Оптимизация базы данных TempDB

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

• Установите для базы данных tempdb модель восстановления SIMPLE. Эта модель автоматически освобождает место, занимаемое журналом, чтобы уменьшить требования к пространству.
• Заранее выделите место для всех файлов базы данных tempdb, установив в качестве размера файла достаточно большое значение для обработки стандартной рабочей нагрузки в среде. Это не позволит базе данных tempdb расширяться слишком часто, чтобы не ухудшать производительность. Для базы данных tempdb можно настроить автоувеличение, однако эту функцию следует использовать для увеличения места на диске в непредвиденных ситуациях.
• Создайте столько файлов, сколько нужно, чтобы максимально увеличить пропускную способность диска. Использование нескольких файлов уменьшает состязание за ресурсы хранения tempdb и обеспечивает лучшую масштабируемость. Однако не создавайте слишком много файлов, так как это может снизить производительность и увеличить затраты на управление. Как правило, рекомендуется создавать по одному файлу данных для каждого логического процессора на сервере (с учетом параметров маски сходства), а затем при необходимости регулировать количество файлов. Как правило, если число логических процессоров не превышает 8, следует использовать столько же файлов данных, сколько логических процессоров. Если число логических процессоров превышает 8, используйте восемь файлов данных. Если состязание за ресурсы сохранится, увеличивайте число файлов данных с кратностью 4 (вплоть до числа логических процессоров), пока состязание не снизится до приемлемого уровня. Либо же измените параметры рабочей нагрузки и (или) код. Если состязание не уменьшается, может потребоваться увеличить количество файлов данных.
• Сделайте каждый файл данных одинаковым размером, обеспечивая оптимальную производительность пропорционального заполнения. Использование файлов данных равного размера очень важно, потому что в основе алгоритма пропорционального заполнения лежит размер файлов. Если вы создаете файлы данных разного размера, алгоритм пропорционального заполнения пытается активно использовать самый крупный файл для распределения GAM, вместо того чтобы распределить нагрузку между всеми файлами, делая создание нескольких файлов данных бесполезным.
• Чтобы обеспечить максимальную производительность, поместите базу данных tempdb в подсистему ускоренного ввода-вывода, используя твердотельные диски. Используйте чередование дисков, если имеется много непосредственно присоединенных дисков.
• Поместите базу данных tempdb на диски, отличные от тех, которые используются пользовательскими базами данных.

Чтобы настроить базу данных tempdb, можно выполнить следующий запрос или изменить его свойства в Management Studio.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 8)
GO
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', NEWNAME = N'tempdb', SIZE = 2097152KB , FILEGROWTH = 512MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb2.mdf' , SIZE = 2097152KB , FILEGROWTH = 512MB )
GO

Выполните запрос SELECT * from sys.sysprocesses T-SQL, чтобы обнаружить состязание за выделение страниц для базы данных tempdb. В таблице выходных данных системы ресурс ожидания может иметь значение 2:1:1 (страница PFS) или 2:1:3 (общая страница глобальной карты распределения). В зависимости от уровня конкуренции это может вызвать небольшую задержку ответа SQL Server. Кроме того, можно проверить динамические представления управления [sys.dm_exec_request или sys.dm_os_waiting_tasks]. Результаты показывают, что эти запросы или задачи ожидают ресурсов tempdb и имеют те же значения, которые были выделены ранее при выполнении запроса sys.sysprocesses .

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

Примечание

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

Максимальная степень параллелизма

Конфигурация SQL Server по умолчанию для небольших и средних развертываний Operations Manager вполне подходит для большинства задач. Однако, когда рабочая нагрузка группы управления масштабируется в сторону сценария корпоративного класса (как правило, более 2000 систем, управляемых агентом, и расширенной конфигурации мониторинга, которая включает в себя мониторинг на уровне службы с помощью расширенных искусственных транзакций, мониторинг сетевых устройств, кроссплатформенный и т. д.), необходимо оптимизировать конфигурацию SQL Server, описанную в этом разделе документа. Одним из вариантов конфигурации, которые не обсуждались в предыдущих руководствах, является MAXDOP.

Конфигурация Microsoft SQL Server с максимальной степенью параллелизма (MAXDOP) контролирует число процессоров, используемых для выполнения запроса в параллельном плане. Эта конфигурация определяет, какие вычислительные ресурсы и ресурсы потока используются для функционирующих параллельно операторов плана запросов. В зависимости от того, настроена ли SQL Server на симметричном многопроцессорном компьютере (SMP), компьютере с неоднородным доступом к памяти (NUMA) или процессорах с поддержкой гиперпоточности, необходимо соответствующим образом настроить параметр max degree of parallelism.

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

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

Примечание

Параметр конфигурации max degree of parallelism не ограничивает количество процессоров, которые использует SQL Server. Чтобы настроить число процессоров, используемых сервером SQL Server, примените конфигурацию с маской сходства.

• Для серверов с числом процессоров более восьми используйте следующую конфигурацию: MAXDOP=8
• Для серверов, использующих восемь или меньше процессоров, используйте следующую конфигурацию: MAXDOP=0–N

  Примечание

  В этой конфигурации N представляет количество процессоров.