Как ограничить память sql server 2012

Тюнинг SQL Server 2012 под SharePoint 2013/2016. Часть 2

Здравствуйте! Сегодня с вами снова я — Любовь Волкова, системный архитектор департамента разработки бизнес-решений. В предыдущей статье мы начали обсуждение темы тюнинга SQL-серверов для работы с базами данных SharePoint 2013/2016. В материале были подробно рассмотрены вопросы выбора между физическим и виртуальным сервером, планирование размера, размещения баз данных, подготовка дисковой подсистемы и оптимизация передачи данных по сети.

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

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

Установка SQL Server 2012

Экран выбора функциональных компонентов

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

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

Конфигурация экземпляра SQL-сервера

На странице конфигурации экземпляра SQL-сервера предлагается ввести данные о имени экземпляра и расположении файлов в файловой системе, связанных с ним.

Использование имени экземпляра по умолчанию не является принципиальным с точки зрения оптимизации производительности SQL-сервера. Настоятельно рекомендуется установить значение путей в файловой системе для размещения файлов в соответствие с настройками размещения файлов, связанных с компонентами SQL.

Сервисные учетные записи

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

Рекомендуется установить отдельные учетные записи для служб SQL Server Agent и SQL Database Engine.

Sql_installation – учетная запись Active Directory (AD), используем для инсталляции SQL, которая на время инсталляции необходимо включить в группу локальных администраторов. После инсталляции учетную запись Sql_installation заблокировать. Она нужна будет только на время инсталляции обновлений.
Sql_engine – учетная запись Active Directory (AD), администратор отдельного экземпляра SQL Server 2012 (для каждого экземпляра свой), которая не входит в группу локальных администраторов. Под этой учетной записью будет работать сервисы SQL Server для устанавливаемого экземпляра (служба SQL Server Database Engine). Эта учетная запись не должна входить в группы локальных, доменных администраторов или администраторов предприятий.
SQL_agent – учетная запись службы агента SQL Server, позволяющей автоматизировать некоторые административные задачи. Агент SQL-сервера выполняет задания, контролирует SQL Server и обрабатывает предупреждения. Служба агента SQL Server должна быть запущена для автоматического запуска локальных или много серверных административных заданий. Рекомендуется выбирать учетную запись пользователя домена Active Directory, не входящего в группу локальных администраторов.
SQL_browser – учетная запись браузера SQL-сервера, которая прослушивает входящие запросы на ресурсы SQL-сервера и предоставляет сведения об экземплярах, установленных на этом компьютере. Рекомендуется выбирать учетную запись пользователя домена Active Directory, не входящего в группу локальных администраторов. В случае, если на сервере инсталлируется экземпляр, имя которого отличается от имени по умолчанию или количество экземпляров более одного, включение службы является обязательным.
SQL_admin – учетная запись администратора SQL-сервера.

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

Англоязычная версия SharePoint 2013 использует Latin1_General_CI_AS_KS_WS;
Русскоязычная версия SharePoint 2013 использует Cyrillic_General_CI_AS.

CI, Case Insensitive (нечувствительность к регистру). «A» и «а» рассматриваются как один и тот же символ.
AS, Accent Sensitive (чувствительность диакритическим знакам, — знакам акцента). Символы «a» и «á» рассматриваются как разные символы.
KS, Kana Sensitive (чувствительность к символам японского алфавита). Символы Japanese Hirakana и Katakana, которые выглядят одинаково, рассматриваются как разные символы.
WS, Width Sensitive (чувствительность к объему данных). Символ, требующий для хранения в таблице SQL один байт и тот же символ, требующий для хранения два байта рассматриваются системой как два разных символа.

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

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

При создании любых баз данных SharePoint средствами графического интерфейса или командной строки, для них автоматически конфигурируются параметры сортировки Latin1_General_CI_AS_KS_WS (для англоязычной версии) или Cyrillic_General_CI_AS (для русскоязычной версии).

Конфигурационные настройки ядра SQL-сервера

Настройки сервера

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

Настоятельно рекомендуется выбрать смешенный режим аутентификации, который позволяет пользователям подключаться с помощью проверки подлинности Windows или SQL Server. На странице ввода конфигурационных параметров сервера необходимо ввести данные о пароле системной учетной записи sa, а также выбрать администратора SQL из числа учетных записей Active Directory. Учетная запись sa всегда присутствует в качестве имени входа в компонент ядра SQL-сервер и является членом предопределенной роли сервера sysadmin.

Если компонент ядра SQL-сервера установлен с использованием только проверки подлинности Windows (то есть проверка подлинности SQL Server не включена), имя входа sa все равно будет присутствовать, но будет отключена.

Расположение файлов баз данных по умолчанию

Файлов данных системной базы tempdb;
Журнала транзакций системной базы tempdb;
Файлов данных баз контента SharePoint;
Журналов транзакций баз контента SharePoint;
Резервный копий баз данных SharePoint.

Конфигурирование после установки SQL-сервера

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

Установить максимальный и минимальный объем памяти, коэффициент заполнения для индекса, максимальный уровень параллелизма для SQL-сервера;
Задать пути для сохранения по умолчанию файлов данных, журналов транзакций и резервных копий по умолчанию;
Настроить сжатие при резервном копировании и проверку страниц при восстановлении;
Для каждой из баз данных SharePoint и базы данных tempdb установить исходный размер файлов данных и журналов транзакций, параметры автоматического увеличения размера файлов данных и журналов
Настроить параметры автоматического создания и обновления статистики
Сконфигурировать мгновенную инициализацию файлов
Выполнить настройку параметров блокировки страниц в памяти

Экспресс-аудит использования ОЗУ

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

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

Наилучшим результатом, свидетельствующим о том, что система не испытывает проблем с ОЗУ является получение значения «Available physical memory is high» в поле «system_memory_state_desc». Получение значения «Available physical memory is low» будет свидетельствовать об обратном.

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

Максимальный объем памяти

Для расчета максимального объема памяти используется следующая формула:

TotalPhyMem – общий физический размер ОЗУ на сервере.
NumOfCores – кол-во ядер процессоров.
NumOfSQLThreads – кол-во потоков, использующихся на сервере для обработки запросов к базам данных. При кол-ве ядер до 4 значение NumOfSQLThreads всегда постоянно и равно 256. При кол-ве ядер свыше 4 расчет выполняется по формуле: NumOfSQLThreads = 256 + (NumOfCores- 4) * 8.
ThreadStackSize = 2Мб для серверов x64. Для серверов IA64 ThreadStackSize=4Мб.
RAMOSReserved – ОЗУ для операционной системы. 20% для серверов с TotalPhyMem не более 15 Гб и 12,5% для большего объема.
RAMForOtherApps – ОЗУ для других экземпляров SQL-сервера и приложений;

Пример полученных результатов:

TotalPhyMem = 8191 Мб;
NumOfCores = 4;
Сервер x64;
RAMForOtherApps = 2000 Мб.

Для автоматизации выполнения расчетов можно использовать SQL MAX MEMORY CALCULATOR.
Начиная с версии SQL 2008 R2, в случае наличия только одного экземпляра SQL на сервере, нет необходимости устанавливать вручную значение максимального объема выделяемой памяти. Это значение рассчитывается автоматически компонентом управления памятью Microsoft SQL Server на основе данных о текущем использовании ОЗУ операционной системой и другими приложениями и динамически изменяется в случае уменьшения/увеличения нагрузки на эту подсистему.

Минимальный объем памяти

Установка значения минимально выделяемого кол-ва оперативной памяти для SQL-сервера позволяет гарантировать, что при расчете системой выделяемой памяти под нужды операционной системы и других приложений для SQL-сервера будут зарезервировано не менее указанного объема ОЗУ. Значение «0» по умолчанию допускает ситуации, при которых на нужды сервера баз данных будет выделено минимально возможное кол-во ресурсов, что может очень негативно сказаться на производительности SharePoint.

SharePoint используется главным образом для совместной работы, активно выполняется редактирование контента.
60% от значения параметра «Max Server Memory» в случае, если корпоративный портал SharePoint в основном используется для поиска и просмотра контента.
В остальных случаях устанавливается значение между 25% и 60% пропорционально процентному соотношению между чтением/изменением данных на корпоративном портале.

Коэффициент заполнения для индекса

Коэффициент заполнения (параметр «fill factor») служит для точной настройки хранения и производительности индекса. При создании или перестроении индекса коэффициент заполнения отображает процент заполнения пространства каждой страницы конечного уровня, что позволяет зарезервировать для будущего расширения оставшееся на каждой странице пространство как свободное. Например, при указании для коэффициента заполнения значения 80 на каждой странице конечного уровня будет зарезервировано 20 процентов занимаемого ею дискового пространства. Данное дисковое пространство будет использовано для расширения индекса при добавлении в базовую таблицу новых данных. Пустое место резервируется не в конце индекса, а между строками индекса.

Коэффициент заполнения — это значение в процентах от 1 до 100; значение по умолчанию на сервере — 0, что означает полное заполнение страниц конечного уровня.

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

Следующий скрипт позволят получить информацию от текущих настройках коэффициента заполнения индекса по умолчанию на уровне SQL-сервера:

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

В SharePoint 2010 установка значения «1» была опциональна, но для SharePoint 2013 значение, отличное от 1 будет препятствовать запуску мастера конфигурации фермы SharePoint. Установка указанного значения для степени параллелизма гарантирует то, что SQL-сервер, на котором размещены базы данных SharePoint, каждый из запросов обрабатывается только одним единственным процессом. Любое другое значение может стать причиной выбора менее оптимального плана выполнения для запроса и может послужить снижению общей производительности SharePoint Server 2013.

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

Расположение файлов по умолчанию и сжатие в ходе резервного копирования

Расположения файлов

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

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

Сжатие в ходе резервного копирования

Сжатие резервных копий позволяет ускорить резервное копирование в SharePoint. Оно доступно в выпусках SQL Server Standard Edition и Enterprise Edition. Установив параметр сжатия в скрипте резервного копирования или настроив сервер SQL Server для сжатия по умолчанию, можно значительно сократить размер резервных копий базы данных и доставляемых журналов.
Ниже приведен скрипт для получения данных о текущих параметрах сжатия в ходе выполнения резервного копирования:

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

Настройка параметров базы данных model

Шаблон базы данных model используется для создания всех баз данных в экземпляре SQL-сервер. Настройки и контент этой базы данных копируются для всех новых пользовательских баз данных при их создании.

Проверка страниц при восстановлении (Page Verify), значение «CHECKSUM»;
Автоматическое создание статистики (Auto Create Statistics), значение «False»;
Автоматическое обновление статистики (Auto Update Statistics), значение «False»;
Автоматическое асинхронное обновление статистики (Auto Update Statistics Asynchronously), значение «False»;
Автоматическое сжатие базы данных (Auto Shrink), значение «False».

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

Cоздание/обновление статистики

Автоматическое создание/обновление статистики для SharePoint Server не поддерживается, поэтому оно должно быть отключено для всех баз. Необходимо установить значение «False» для параметров «Auto Create Statistics», «Auto Update Statistics» и «Auto Update Statistics Asynchronously».

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

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

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

Настоятельно рекомендуется установить значение «CHECKSUM» для параметра проверки страниц при восстановлении (Page Verify), как минимум для системных баз данных SharePoint и баз контента. В этом случае SQL-сервер вычисляет контрольную сумму для контента всей страницы и сохраняет это значение в ее заголовке при записи на диск. При считывании страницы данных с диска выполняется пересчет контрольной суммы данных и сравнение полученного значения с тем, которое записано в заголовке. Это обеспечивает постоянную поддержку высокого уровня целостности данных.

Автоматическое сжатие базы данных

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

Автоматическое сжатие особенно вредно, поскольку оно запускается каждые 30 минут в фоновом режиме и пытается сжимать базы данных, для которых выставлен параметр автоматического сжатия. Этот процесс не вполне предсказуем в том, что он сжимает лишь базы данных с более чем 25% свободного места. Автоматическое сжатие использует массу ресурсов и вызывает понижающую производительность фрагментацию, так что оно нежелательно при любых обстоятельствах. Его всегда следует отключать.

Настройка параметров базы данных tempdb

временные объекты, созданные явно, такие как глобальные или локальные временные таблицы, временные хранимые процедуры, табличные переменные и курсоры;
внутренние объекты, создаваемые компонентом SQL Server Database Engine, например, рабочие таблицы, хранящие промежуточные результаты буферов или сортировки;
версии строк, сформированные транзакциями изменения данных в базе данных, в которой используются транзакции изоляции моментальных снимков с зафиксированным чтением и транзакции изоляции моментальных снимков;
версии строк, создаваемые транзакциями изменения данных для таких функций, как операции с индексами в сети, функции режима MARS и триггеры AFTER.

По умолчанию база данных tempdb настроена на работу в простом режиме восстановления.

Количество, первоначальный размер и автоматический рост файлов

База данных tempdb является одной из наиболее интенсивно использующихся баз данных и должна всегда располагаться на наиболее скоростных дисках. В случае, если создается несколько файлов данных для этой базы, SQL-сервер обеспечивает одновременную запись данных в каждый из них, тем самым увеличивая производительность выполнения указанной операции.
Общей рекомендацией является создание по одному файлу на каждое ядро процессора. Майкрософт также говорит о допустимости создания одного дополнительного файла данных из расчета на 2/4 ядра.

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

Пример записи в журнал о частых событиях автоматического приращения файла базы данных контента (для базы данных tempdb возможна аналогичная ситуация):

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

Размер каждого из файлов должен совпадать, при этом суммарный размер всех исходный размеров фалов данных должен быть настроен на 10-25% от размера самой большой базы данных контента. Рекомендации специалистов Майкрософт для размер автоматического роста файлов – 10% от размера файла данных.

Размер файла журнала транзакций – 50% от исходного размера файла данных. Автоматический рост файла журнала транзакций – 50% от его исходного размера.

память — 8Гб;
процессоров 4-ядра;
база контента 95 Гб.

количество файлов – 4шт, суммарный размер 24 Гб, то есть каждый файл по 6Гб;
автоматический прирост – 614 Мб;
исходный размер файла журналов транзакций – 3 Гб;
автоматически прирост файлов журналов – 1,5 Гб.

Проверка страниц

Необходимо убедиться в том, что установлено значение CHECKSUM для параметра Проверка страниц (Page Veriety).

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

избегайте сжатия базы данных tempdb, выполняйте эту операцию только если вы абсолютно уверены в ее необходимости, а размер свободного места составляет около 50%;
параметры сортировки должны совпадать с настройками этого параметра для SQL-сервера;
не изменяйте владельца базы данных со значения sa на другое;
не удаляйте базу данных tempdb;
не удаляйте гостевую учетную запись из списка пользователей базы данных;

Индивидуальные настройки для баз данных SharePoint

Важно знать, что SharePoint при создании баз данных контента или баз данных приложений-служб копирует не все настройки базы данных model.

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

Первоначальный размер файлов данных и журналов транзакций

Оцените ожидаемый размер базы данных с учетом роста в течение ближайшего года.
В качестве исходного суммарного размера файлов данных установите 25% от значения, полученного в п.1.
в качестве исходного размера файла журналов транзакций установите 25% от значения в п.2.

Автоматический прирост файлов баз данных SharePoint

Рекомендуется установить точные значения прироста файлов баз данных – 50% от их исходного значения.

Автоматическое создание статистики

Автоматическое создание статистики (Auto Create Statistics), значение «False»;
Автоматическое обновление статистики (Auto Update Statistics), значение «False»;
Автоматическое асинхронное обновление статистики (Auto Update Statistics Asynchronously), значение «False»;
Автоматическое сжатие базы данных (Auto Shrink), значение «False».

Мгновенная инициализация файлов

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

Выясните данные об учетной записи, от имени которой запускаются службы SQL-сервера:
- В данных о службах
- Или в окне настройки конфигурации SQL-сервера.
Откройте приложение Локальные политики (secpol.msc).
Разверните на левой панели узел Локальные политики, а затем щелкните пункт Назначение прав пользователей.
На правой панели дважды щелкните Выполнение задач по обслуживанию томов.
Щелкните кнопку Добавить пользователя или группу и добавьте учетную запись, данные о которой получены в п.1.
Нажмите кнопку Применить и закройте все диалоговые окна Локальная политика безопасности.

Настройка параметров блокировки страниц в памяти

В 64-разрядной версии SQL режим AWE можно включить через групповую политику – включить привилегию «блокировки страниц в памяти» для SQL-сервера. Результат – по мере необходимости автоматическое включение работы с режимом AWE для осуществления доступа к памяти буферного пула.

Конфигурирование брандмауэра

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

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

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

Включить брандмауэр можно с помощью следующей команды:

Set-NetFirewallProfile -DefaultInboundAction Block -DefaultOutboundAction Allow -NotifyOnListen True -AllowUnicastResponseToMulticast True

Настройка брандмауэра Windows для доступа к компоненту Database Engine

New-NetFirewallRule -DisplayName «SQL Server» -Direction Inbound –Protocol TCP –LocalPort 1433 -Action allow

New-NetFirewallRule -DisplayName «SQL Admin Connection» -Direction Inbound –Protocol TCP –LocalPort 1434 -Action allow

New-NetFirewallRule -DisplayName «SQL Database Management» -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow

New-NetFirewallRule -DisplayName «SQL Service Broker» -Direction Inbound –Protocol TCP –LocalPort 4022 -Action allow

New-NetFirewallRule -DisplayName «SQL Debugger/RPC» -Direction Inbound –Protocol TCP –LocalPort 135 -Action allow

Если для БД SharePoint используется именованный экземпляр SQL, то также необходимо подключение по именованному порту. Номер порта назначается динамически, отображается в менеджере конфигурации SQL Server (Сетевая конфигурация | Протоколы | TCP/IP| Свойства).

New-NetFirewallRule -DisplayName «SQL NamePort» -Direction Inbound –Protocol TCP –LocalPort -Action allow

New-NetFirewallRule -DisplayName «SQLSERVR» -program " C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe" -direction Inbound -Action Allow

Настройка брандмауэра Windows на разрешение доступа к службам Analysis Services

New-NetFirewallRule -DisplayName «SQL Analysis Services» -Direction Inbound –Protocol TCP –LocalPort 2383 -Action allow

New-NetFirewallRule -DisplayName «SQL Browser» -Direction Inbound –Protocol TCP –LocalPort 2382 -Action allow

New-NetFirewallRule -DisplayName «HTTP» -Direction Inbound –Protocol TCP –LocalPort 80 -Action allow

New-NetFirewallRule -DisplayName «SSL» -Direction Inbound –Protocol TCP –LocalPort 443 -Action allow

Настройка брандмауэра для доступа к серверу отчетов

New-NetFirewallRule -DisplayName «HTTP» -Direction Inbound –Protocol TCP –LocalPort 80 -Action allow

New-NetFirewallRule -DisplayName «SSL» -Direction Inbound –Protocol TCP –LocalPort 443 -Action allow

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

Псевдонимы для использования клиентами SQL

В диспетчере конфигурации SQL Server развернуть пункт Конфигурация клиента Native Client SQL, выбрать пункт Псевдонимы, нажать правой кнопкой мыши, в появившемся меню выбрать Создать псевдоним.
В поле Имя псевдонима указать имя. Клиентское приложение сможет использовать это имя вместо имени сервера для подключения.
В поле Сервер указать имя сервера или IP-адрес. Для именованного экземпляра добавить имя этого экземпляра.
В поле Протокол выбрать протокол, который будет использоваться для этого псевдонима.
Создать DNS-запись типа АA, которая разрешает имя псевдонима в IP-адрес SQL-сервера.

Настройка прав доступа перед установкой SharePoint

Необходимо настроить права учетной записи, от имени которой будет выполнена установка SharePoint Server.

Параметры быстродействия

По умолчанию визуальные эффекты выбирается на основе производительности сервера. Для повышения производительности сервера рекомендуем выбрать режим Обеспечить наилучшее быстродействие (Свойства системы > вкладка Дополнительно > раздел Быстродействие > Параметры > вкладка Визуальные эффекты).

Управление виртуальной памятью

Рекомендуем определить размер и расположения файла подкачки. Размер файла подкачки должен быть равен выделенной оперативной памяти сервера с коэффициентом 1,5. Пример: 8192 MB RAM х 1,5 = 12288 MB. Рекомендуем размещать файл подкачки на отдельном диске — не системном.

(Свойства системы -> вкладка Дополнительно > раздел Быстродействие > Параметры > вкладка Дополнительно > раздел Виртуальная память).

Настройка схемы управления электропитанием

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

Откройте Панель управления> Система и безопасность > Электропитание и выберите схему питания Высокая производительность.

Флаг трассировки 1117

Установка TraceFlags 1117 обеспечивает равномерный рост всех файлов данных. Когда файл в файловой группе достигает порогового значения автоприращения, все файлы в файловой группе автоматически увеличиваются в размере на коэффициент приращения.

Для настройки автоматического приращения файлов в группе перейдите в Диспетчер конфигурации SQL Server > Службы SQL Server > SQL Server <имя экземпляра> > Свойства > Дополнительно > Параметры запуска, после чего добавьте в конце строки «;-T1117».

Начиная с SQL Server 2016 это поведение контролируется с помощью опции AUTOGROW_SINGLE_FILE и AUTOGROW_ALL_FILES в параметрах базы данных, а флаг трассировки 1117 не оказывает никакого влияния.

SQL Server Memory Shortage for all 32 Bit SQL Server 2012 Instances

As you all know AWE has been deprecated and not available from SQL Server 2012 (Find details here), as a result when you upgrade from SQL Server 2008 R2 to the next version of SQL Server, you cannot use more memory than what the virtual address space limits in 32-bit instances of SQL Server. If you must have more memory for this instance of SQL Server, then you have to migrate to a 64-bit instance of SQL Server.

After you upgrade to SQL Server 2012, the maximum amount of memory that can be used by the 32-bit instance of SQL Server is determined as follows.

SQL Server and operating system settings	Maximum amount of memory used by SQL Server
32-bit SQL Server on 32-bit OS	2 GB
32-bit SQL Server on 32-bit OS with /3G boot option	3 GB
32-bit SQL Server on 64-bit OS	4 GB

Those who don’t know much about AWE;

AWE was being used in earlier versions of 32 Bit SQL Servers so that they can support more than 4GB of Physical Memory. SQL Server can access up to 64 GB of memory on Microsoft Windows Server 2000 & 2003.

By Default, Standard 32-bit addresses can map a maximum of 4 GB of memory. By default, on 32-bit Microsoft Windows operating systems, 2 GB is reserved for the operating system, and 2 GB is made available to the application. If you specify a /3gb parameter in the Boot.ini file of Windows Server, the operating system reserves only 1 GB of the address space, and the application can access up to 3 GB.

AWE is a set of extensions to the memory management functions of Windows that allow applications to address more memory than the 2-3 GB that is available through standard 32-bit addressing. AWE lets applications acquire physical memory, and then dynamically map views of the non-paged memory to the 32-bit address space.

Although the 32-bit address space is limited to 4 GB, the non-paged memory can be much larger. This enables memory-intensive applications, such as large database systems, to address more memory than can be supported in a 32-bit address space.

Before you configure the operating system for AWE on 32-bit operating systems, you must add the /pae parameter to the Boot.ini file and reboot the computer. Windows Server 2003, PAE is automatically enabled only if the server is using hot-add memory devices in Windows Server 2003.

The SQL Server buffer pool can fully utilize AWE mapped memory; however, only database pages can be dynamically mapped to and unmapped from SQL Server’s virtual address space and take full advantage of memory allocated through AWE. AWE does not directly help supporting additional users, databases, queries, and other objects that permanently reside in the virtual address space.

Lock pages in Memory

This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. The Lock Pages in Memory option is set to OFF by default in SQL Server. If you have system administrator permissions, you can enable the option manually by using the Windows Group Policy tool (gpedit.msc) and assign this permission to the account that SQL Server is running.

Although it is not required, we recommend locking pages in memory when using 64-bit operating systems. For 32-bit operating systems, Lock pages in memory permission must be granted before AWE is configured for SQL Server.

Microsoft SQL Server 2012 64-bit, забирает всю оперативу

Это точно, что тут думать — добавить памяти ну хотя бы до 64 гиг, а потом уже можно и о чем-то думать.

(29) При таком раскладе как у ТС — самое лучшее: Никаких ограничений, пусть система сама рулит сколько и кому. Если поставить ограничение скулю, не важно сколько, результат на 146% будет, что все стане еще хуже чем было до каких либо ограничений.

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

(39) и пусть крутятся, система сама решит сколько скулю она может дать в этот момент и не плохо скажу я вам она решает.

проверял, тока дал ограничению скулю и все стало тока печальней.

(57) Тест Гилева делается на его базе, весит она копейки.

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

у меня, блин, тормоза, жесть на жестяке, сервачок древнячок, пыжится шуршит сколько может из себя выжать. оперативы всего 8-мь гиг.
скуль жрет 800-т кило, как заставить жрать больше, знаешь?

+ (58) ну я возьми и поставь ему ограничение в 7-мь гиг. ну логично-же, если жрет меньше гига, че ему ограничение в 7-мь?

И все просто встало, точнее будет сказать упало 🙂

(67) один можно оставить
(68) то, что смотриш в мониторе — вранье 🙂

Загони тест гилева, сколько у тебя покажет, просто интересно.

я максимум у себя в руках 15-ть держал +/-

>>один можно оставить
ну иго нафиг, 50-100 зверьков плохо живут в конуре на 30 мест
>>то, что смотриш в мониторе — вранье 🙂
да ну, в win сервер разжёвано что и сколько, есть столбцы Выделено (харезервировано системой под процесс), реальное потребление процессом и т.д. Понятно что скуль может и выплюнуть назад сколько нибудь, но это уж когда он сам решит, по доброй воле не отдаст другим процессам

(69) завтра, если не забуду, щас домой, раб день закончен

(70) я не вращаюсь в таких высоких кругах, к сожалению, аль к счастью. просто тестил им, может он и врет, но в общих чертах чёто кажет.

Я просто пытаюсь донести ТС и остальным свой опыт — ограничения скуля только сделает хуже. Я вру?

(74) значит я вносил смуту 🙂 бить меня палкаме 🙂

Да и ситуация у меня малясь другая была 8-мь гиг оперативы и нарастить нет возможности технически.
А на этом серваке две базы одна 40-к гиг, вторая 8-мь.

Поставил ограничение, сервер вообще встал 🙂

ТС, не слушай меня, ограничивай, у тебя ситуация другая, может и поможет.

Настройка 1С и MS SQL. Оптимизация работы 1C. Настройка сервера MS SQL

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

Интервал перезапуска — отвечает за частоту перезапуска рабочих процессов кластера. Этот параметр необходимо выставлять при круглосуточной работы сервера. Рекомендуется частоту перезапуска связывать с технологическим циклом информационных баз кластера. Обычно это каждые 24 часа (86400 сек). Как известно, рабочие процессы серверов 1С обрабатывают и хранят рабочие данные.
Автоматический перезапуск был разработан в платформе «для минимизации отрицательных последствий фрагментации и утечки памяти в рабочих процессах». На ИТС есть даже информация о том, как организовать перезапуск рабочих процессов по другим параметрам (объем памяти, занимаемые ресурсы и т.п.).
Допустимый объем памяти — защищает сервера 1С от перерасхода памяти. При превышении процессом этого объема в интервале превышения допустимого объема, процесс перезапускается. Можно рассчитать, как максимальный размер памяти, занимаемый процессами «rphost» в периоды пиковой нагрузки серверов. Также стоит установить небольшой интервал превышения допустимого объема.
Допустимое отклонение количества ошибок сервера. Платформа рассчитывает среднее количество ошибок сервера по отношению к числу обращений к серверу в течение 5 минут. Если это отношение превысит допустимое, то рабочий процесс считается «проблемным», и может быть завершен системой, если установлен флаг «Принудительно завершать проблемные процессы».
Выключенные процессы останавливать через. При превышении допустимого объема памяти, рабочий процесс не завершается сразу, а становится «выключенным», чтобы было время «перенести» рабочие данные без потери на новый запущенный рабочий процесс. Если указан этот параметр, то «выключенный» процесс в любом случае завершится по истечении этого времени. Если наблюдаются «зависшие» рабочие процессы в работе сервера 1С, то можно стоит этот параметр на 2-5 минут.

Эти настройки устанавливаются для каждого сервера 1С индивидуально.

Максимальный объем памяти рабочих процессов — это объем совокупной памяти, которую могут занимать рабочие процессы (rphost) на текущем кластере. Если параметр установлен в «0», то занимает 80% оперативной памяти сервера. «-1» — без ограничений. Когда на одном сервере работают СУБД и сервер 1С, им нужно делить между собой оперативную память. Если в процессе эксплуатации обнаружится, что серверу СУБД не хватает памяти, то можно ограничить память, выделяемую серверу 1С с помощью этого параметра. Если СУБД и 1С разделены по серверам, то имеет смысл рассчитать этого параметр по формуле:
«Max объем» = «Общая оперативная память» — «Оперативная память ОС»;
«Оперативная память ОС» рассчитывается по принципу 1 Гб на каждые 16 Гб оперативной памяти сервера
Безопасный расход памяти за один вызов. В общем случае, отдельные вызовы не должны занимать всю оперативную память, выделенную рабочему процессу. Если параметр установлен в «0», то объем безопасного расхода будет равен 5 % от «Максимального объема памяти рабочих процессов». «-1» — без ограничения, что крайне не рекомендуется. В большинстве случаев этот параметр лучше оставлять «0».
С помощью параметров «Количество ИБ на процесс» и «Количество соединений на процесс» можно управлять распределением работы сервера 1С по рабочим процессам. Например, запускать под каждую информационную базу отдельный «rphost», чтобы в случае «падений» процесса, отключались только пользователи одной базы. Эти параметры стоит подбирать индивидуально под каждую конфигурацию сервера.

Включить возможность мгновенной инициализации файлов (Database instant file initialization)

Это позволяет ускорить работу таких операций как:

Создание базы данных.

Добавление файлов, журналов или данных в существующую базу данных.

Увеличение размера существующего файла (включая операции автоувеличения).

Восстановление базы данных или файловой группы.

Для включения настройки:

Откроть Local Security Policy (secpol.msc).

Локальные политики — Назначение прав пользователей

Выполнение задач по обслуживанию томов

«Добавить» пользователя или группу и добавить сюда пользователя, под которым запущен сервер MS SQL Server

Включить «Блокировка страниц в памяти» (Lock pages in memory)

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

Пуск — Выполнить — gpedit.msc.

Конфигурация компьютера — Конфигурация Windows

Настройки безопасности и Локальные политики

Назначение прав пользователя

«Блокировка страниц в памяти»

В диалоговом окне Параметр локальной безопасности — блокировка страниц в памяти выбрать «Добавить» пользователя или группу

В диалоговом окне Выбор: пользователи, учетные записи служб или группы добавьте ту учетную запись, под которой запускается служба MS SQL Server

Чтобы изменения вступили в силу, перезагрузите сервер.

Отключить механизм DFSS для дисков

Механизм Dynamic Fair Share Scheduling отвечает за балансировку и распределение аппаратных ресурсов между пользователями. Иногда его работа может негативно сказываться на производительности 1С. Чтобы отключить его только для дисков, нужно

Найти в реестре ветку HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\TSFairShare\Disk
Установить значение параметра EnableFairShare в 0

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

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

Чтобы отключить сжатие файлов в каталоге, необходимо

Открыть свойства каталога
На закладке Общие нажать — Другие
Снять флаг «Сжимать» содержимое для экономии места на диске

Установить параметр «Максимальная степень параллелизма» (Max degree of parallelism) в значение 1

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

Запустить Management Studio и подключиться к нужному серверу
Свойства сервера — Дополнительно
Установить значение параметра равное единице

Ограничить максимальный объем памяти сервера MS SQL Server

Необходимо ограничить максимальный объем памяти, потребляемый MS SQL Server, особенно это критично, если роли сервера 1С и сервера СУБД совмещены. Максимальный объем памяти, рекомендуемый для MS SQL Server, можно рассчитать по следующей формуле:

Память для MS SQL Server = Память всего — Память для ОС — Память для сервера 1С

Например, на сервере установлено 64 ГБ оперативной памяти, необходимо понять, сколько памяти выделить серверу СУБД, чтобы хватило серверу 1С.

Для нормальной работы ОС в большинстве случаев более чем достаточно 4 ГБ, обычно — 2-3 ГБ.

Чтобы определить, сколько памяти требуется серверу 1С, необходимо посмотреть, сколько памяти занимают процессы кластера серверов в разгар рабочего дня. Этими процессами являются ragent, rmngr и rphost, подробно данные процессы рассматриваются в разделе, который посвящен кластеру серверов. Снимать данные нужно именно в период пиковой рабочей активности, когда в базе работает максимальное количество пользователей. Получив эти данные, необходимо прибавить к ним 1 ГБ — на случай запуска в 1С «тяжелых» операций.

Чтобы установить максимальный объем памяти, используемый MS SQL Server, необходимо

Запустить Management Studio и подключиться к нужному серверу
Свойства сервера, Память
Указать значение параметра Максимальный размер памяти сервера

Включить флаг «Поддерживать» приоритет SQL Server (Boost SQL Server priority)

Данный флаг позволяет повысить приоритет процесса MS SQL Server над другими процессами.

Имеет смысл включать флаг только в том случае, если на компьютере с сервером СУБД не установлен сервер 1С

Для установки флага:

Запустить Management Studio и подключиться к нужному серверу
Свойства сервера — Процессоры
Включить флаг «Поддерживать приоритет SQL Server (Boost SQL Server priority)» и Ок.

Установить размер авто увеличения файлов базы данных

Автоувеличение позволяет указать величину, на которую будет увеличен размер файла базы данных, когда он будет заполнен. Если поставить слишком маленький размер авторасширения, тогда файл будет слишком часто расширяться, на что будет уходить время. Рекомендуется установить значение от 512 МБ до 5 ГБ.

Для установки размера авторасширения необходимо:

Запустить Management Studio и подключиться к нужному серверу
Свойства нужной базы — Файлы
Напротив каждого файла в колонке Автоувеличение поставить необходимое значение

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

Разнести файлы данных mdf и файлы логов ldf на разные физические диски

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

Для переноса файлов:

Запустить Management Studio и подключиться к нужному серверу
Открыть свойства нужной базы — Файлы
Запомнить (записать) имена и расположение файлов
Отсоединить базу.
Поставить флаг Удалить соединения и нажать Ок
Открыть Проводник и переместить файл данных и файл журнала на нужные носители
В Management Studio открыть контекстное меню сервера и «Присоединить базу».
Нажать кнопку Добавить и указать файл mdf с нового диска
В нижнем окне сведения о базе данных в строке с файлом лога нужно указать новый путь к файлу журнала транзакций и нажать Ок

Вынести файлы базы TempDB на отдельный диск

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

Рекомендуется хранить базу TempDB на отдельном диске для повышения производительности работы системы

Для переноса базы TempDB на отдельный диск:

Запустить Management Studio и подключиться к нужному серверу
выполнить скрипт

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = ‘Новый_Диск:\Новый_Каталог\tempdb.mdf’)