Windows Server Power Management Effect on SQL Server
I have a SQL Server 2012 installed on Windows Server 2008 R2 Enterprise Edition. The power plan of the Windows Server Operating System (OS) is left at its default power plan of Balanced. Are there any benefits or performance gain in setting the power plan to High Performance even when the CPU is not under any pressure?
Solution
Online transaction processing applications are typically high throughput and insert or update-intensive. These applications are used concurrently by hundreds of users. By having the power plan left at Balanced, a server with low utilization can have the server component such as its CPU throttled back to save on power consumption.
The benefit to set the server power management to high performance is for SQL Server to have consistent, predictable and high performance. This additional processing capacity comes with higher power utilization. Hence, you will need to evaluate if your server requires very low response time and high throughput at any given time versus the increase of cost due to server power consumption when you select the High Performance power plan.
Windows Server Power Options Background
The Windows Server OS Power Options form is accessible from the Control Panel on Windows 2008 R2 and higher. The default power plan is Balanced by default.
The server hardware used in my testing is a Dell PowerEdge R610 with dual socket Intel(R) Xeon X5650 CPUs @ 2367GHz.
I have set the server power management in the server BIOS to «OS Control». Basically this option will allow the server power management to be set from the Windows Server OS. Note that server BIOS Power Management setting overrides the Windows Server power plan. Should the server BIOS power management be set to Maximum Performance, then the Windows Server power plan of Balanced or High Performance would be irrelevant as it will always run in High Performance mode.
Demonstration of Effects
I am running Windows Server 2008 R2 Enterprise Edition with the default power plan of Balanced.
To illustrate the impact of the power plan, I will be comparing the performance of a light-weight query on the server under both power plans against the AdventureWorks2012 database. In this demonstration, all hardware and software resources will remain constant with the only change made in each test being the Windows Server power plan setting.
The sample query used for this test is as below. The query will be executed a number of times under Windows Server OS power plan of Balanced and High Performance.
SET STATISTICS TIME will be used to display the number of milliseconds (ms) required to parse, compile, and execute the query.
A brief explanation of SET STATISTICS TIME is as below:
- SQL Server parse and compile time
- Parse time is the time spent during checking SQL statement for syntax errors, breaking the command up into component parts, and producing an internal execution tree.
- Compile time is time spent during compiling an execution plan in cache memory from the execution tree that has just been produced.
- Execution time is the total time spent during execution of compiled plan.
Test 1- Windows Server power plan with the Balanced Power Option
For additional information, I have downloaded CPUID to check the CPU specification on the server. Under the Balanced power plan, the CPU core speed is 1.2GHz. This is where the CPU power saving kicks in by reducing the frequency multiplier (Core Multiplier) and the voltage (Core VID) when the server CPU is not under load. The combination of both multiplier and voltage is known as performance states (p-states) and this is what makes up a server power management profile.
The result for the first query execution (Run 0) is discarded as it is meant to warm up the cache. The query is then subsequently executed 7 more times with results captured as below
SQL Server parse and compile time SQL Server Execution Time CPU time elapsed time CPU time elapsed time Run 1 172 175 16 10 Run 2 171 184 16 10 Run 3 188 193 15 10 Run 4 218 230 16 16 Run 5 181 181 0 9 Run 6 187 187 0 9 Run 7 194 194 0 10 Test 2- Windows Server power plan under High Performance Power Option
When the Windows Server power plan is changed to High Performance, the CPU core speed now ramp up to 3.3GHz. You can see that CPU Clocks Multiplier is now at its maximum and the Core VID has also increased.
The same query is run again and query parse and compile time is recorded as below
SQL Server parse and compile time SQL Server Execution Time CPU time elapsed time CPU time elapsed time Run 1 109 110 0 6 Run 2 115 115 0 6 Run 3 109 110 0 6 Run 4 120 120 0 6 Run 5 109 113 16 6 Run 6 108 108 0 6 Run 7 109 114 0 6 Conclusion
Under Windows OS High Performance power plan, the results are more consistent with the SQL Server parse and compile time closer to around 110ms compared to between 180ms and 200ms under the Balanced power plan. The overall SQL Server Execution Time has also improved from around 10ms to a more consistent 6ms. You may say there is 40% improvement, but mileage varies and this percentage is not linear where you expect a process that runs for 60 minutes would complete in 36 minutes. Assuming all other factors are constant, the power plan might reduce an hour process to 50 minutes for instance.
If power saving is a higher priority than fast and predictable performance, you might leave the power plan as Balanced. There could be an applicable scenario where a server is not used frequently and is left under the default Balanced power plan to conserve energy.
Настройка MSSQL для работы с 1С — мифы и реальность
Оптимизируем Microsoft SQL Server 2014 для работы с 1С. ОС — Windows Server 2012 R2. Напишу что нужно сделать, а что не нужно.
Определяю мифичность очень просто, если мне пришлось применить настройку — реальность, не пришлось — миф.
Устанавливаем последний Service Pack и Cumulative Update — реальность
На текущий момент нужно обновиться до SP3.
Обязательно нужно сделать.
Выравнивание разделов — миф
Рекомендуют выравнивать сектора дисков по границе 1024Кб. Не заморачивайтесь, эта рекомендация устарела. Операционная система теперь сама выравнивает раздел как нужно для дисков более 4 ГБ.
Эту настройку не применяем.
Форматирование с размером блока 64Кб
А вот здесь всё очень неявно. Есть статья Best Practice от Microsoft:
В ней явно говорится:
Partition the Disk volumes hosting SQL databases (Data and log) with 64 KB allocation unit size:
A drive can be formatted with different sizes ranging from 512 bytes to 64K sizes with the default being 4KB (4096 bytes). This setting is also called as “Bytes Per Cluster”. The atomic unit of storage in SQL Server is a page which is 8KB in size. Extents are groups of eight 8 KB pages that are physically contiguous to each other for a total of 64 KB. SQL Server uses extents to store data. Hence, on a SQL Server machine the NTFS Allocation unit size hosting SQL database files (Including tempdb files) should be 64K.Кстати, обратите внимание на другие разделы статьи, возможно, там тоже найдёте для себя что-то полезное.
Казалось бы всё ясно, 64 килобайта — наше всё. Но. Откуда ноги растут? Есть рекомендация от Microsoft с 2008 года:
Здесь аргументированно с указанием конкретного железа, на котором производились испытания, пишут:
Note that file allocation unit (cluster) size commonly correlates with common stripe unit sizes. The performance question here is usually not one of correlation per the formula, but whether the cluster size is the NTFS default of 4,096 bytes or has been explicitly defined at 64 KB, which is a best practice for SQL Server.
Снова 64 килобайта — наше всё. Читаем дальше:
An appropriate value for most installations should be 65,536 bytes (that is, 64 KB) for partitions on which SQL Server data or log files reside. In many cases, this is the same size for Analysis Services data or log files, but there are times where 32 KB provides better performance. To determine the right size, you will need to do performance testing with your workload comparing the two different block sizes.
Получается, что SQL сервер ведёт себя лучше при размере кластера 64 килобайта, однако, в некоторых случаях, максимальной производительности удавалось достигать при размере кластера в 32 килобайта.
А теперь добавляем переменных:
- Появились новые типы массивов типа SSD и NVMe.
- Всё чаще SQL сервер устанавливается виртуально или в облаке, где файловая система виртуальной машины живёт внутри файловой системы гипервизора.
- Производители жёстких дисков и RAID контроллеров используют кеширование и дополнительные технологии ускорения работы дисков.
Ещё следует учитывать, что NTFS для разных размеров диска создаёт разные размеры блока. Стандартный размер кластера (лучше использовать это название) в файловой системе NTFS:
- ёмкость до 16 Тб — 4 килобайта
- ёмкость от 16 до 32 Тб — 8 килобайт
- ёмкость от 32 до 64 Тб — 16 килобайт
- ёмкость от 64 до 128 Тб — 32 килобайта
- ёмкость от 128 до 256 Тб — 64 килобайта
Так что если вы не хотите заморачиваться, то ставьте размер блока (кластера) при форматировании 64 килобайта. Однако не факт, что при этом ваш SQL сервер будет работать быстрее. Правильный ответ можно получить только при тестировании производительности.
Присылайте ссылки на статьи, где проводились исследования производительности для разных размеров кластера. Истина где-то там.
Database instant file initialization — частично миф, частично реальность
Рекомендуют включить возможность Database instant file initialization для пользователя, от которого запущена служба Microsoft SQL Server.
Что это за штука?
Во-первых, эта настройка влияет только на файл данных. Когда файл автоматически вырастает, то новый кусок заполняется нулями, в этот момент 1С может тормозить. Instant File Initialization (IFI) позволяет отключить это зануление.
- Запускаем Local Group Policy Editor:
- Слева выбираем Local Computer Policy, Computer Configuration, Windows Settings, Security Settings, Local Policies, User Rights Assigment.
- Тыкаем в Perform volume maintenance tasks.
- Сюда добавляем юзера, от имени которого запускается SQL Server.
Однако, у меня там уже прописана группа Administrators, а пользователь, под которым работает служба SQL Server уже в этой группе.
Так что у меня ничего не пришлось настраивать. Но вы у себя проверьте.
Давайте проверим, что всё работает. Рекомендуют создать новую БД размером 5 Гб и логом 1 Мб. Вот тут тоже нужно быть внимательным, нужно создавать под тем же пользователем, от которого запускается сервис SQL. Я создам от имени другого пользователя, который тоже в группе Administrators.
База создалась мгновенно. На всякий случай попробую создать базу размером 50 Гб, место есть. Да, моментально на диске пропало 50 Гб и БД создалась быстро.
Instant File Initialization (IFI) работает из коробки, если пользователь, под которым работает сервис SQL входит в группу Administrators сервера.
Эту настройку применяем только при необходимости.
Lock pages in memory — реальность
Рекомендуют установить разрешение на Lock pages in memory (блокировку страниц в памяти) для пользователя, от которого запущена служба Microsoft SQL Server. Эта политика Windows определяет, какие учетные записи могут сохранять данные в физической памяти, чтобы система не отправляла страницы данных в виртуальную память на диске.
- Запускаем Local Group Policy Editor:
- Слева выбираем Local Computer Policy, Computer Configuration, Windows Settings, Security Settings, Local Policies, User Rights Assignment.
- Тыкаем в Lock pages in memory.
Эту настройку применяем. Нужно учитывать, что потребление оперативки возрастёт.
Power Option: High performance — реальность
Да, это реальность. По умолчанию план электропитания в Windows Server 2012 R2 — Balanced.
В некоторых случаях, Microsoft не уточняет в каких, на сервере может из-за этого снижаться производительность. Переключаем план на High performance.
Эту настройку применяем.
Сжатие содержимого — миф
Рекомендуют проверить, что галка, отмеченная на картинке выше, снята. Имеется в виду папка, где лежат файлы БД. По умолчанию она снята — рекомендация миф.
Индексация файлов — миф
По умолчанию галка Allow files in this folder to have contents indexed in addition to the file properties стоит:
Казалось бы, надо снимать. Подумаем, эта галка указывает службе индексации, что содержимое нужно обработать. Зачем на сервере индексировать содержимое? А стоит ли вообще служба индексации по умолчанию? Проверяем:
А служба поиска не установлена! Ну и забиваем на все эти галки. Эту настройку не применяем.
Исключить файлы БД из систем резервного копирования — миф
Это классика, не нужно ничего исключать, нужно просто нормально резервирование делать.
Во-первых, не отделяйте мух от котлет. Не держите приложение 1С и SQL Server на одном сервере. MSSQL должен жить отдельно. И резервироваться он должен средствами MSSQL. И зеркалироваться.
Эту настройку не применяем.
Резервирование и обслуживание — реальность
Эту настройку применяем.
Настройка памяти — реальность
Да, память для MSSQL нужно настраивать. По умолчанию сиквел жрёт всё что может, системе начинает не хватать. Как итог — тормоза. Нужно выделить SQL серверу строго определённое количество памяти и пусть не рыпается.
Не держите приложение 1С и SQL Server на одном сервере. MSSQL должен жить отдельно.
Это позволить избежать конкуренции за ресурсы.
Эту настройку применяем.
Настройка процессора — реальность
Эту настройку применяем.
Расположение файлов данных — реальность
Файлы данных и файлы журналов транзакций желательно размещать на разных дисковых массивах. Если один из массивов быстрее, то лог нужно разместить там.
Эту настройку по возможности применяем.
Разбить tempdb на несколько файлов по количеству процессоров — миф
Эту настройку не применяем.
Перенести tempdb в RAM — реальность
Сам протестировал, добился 10-кратного прироста скорости чтения-запись для tempdb.
Эту настройку по возможности применяем.
Установить параметр Max degree of parallelism = 1 — частично миф, частично реальность
В закладке Advanced есть возможность установить параметр Max degree of parallelism. Кто-то рекомендует ставить 1, чтобы запретить параллельные запросы. Смысл в том, что один толстый запрос может сожрать все процессоры и помешать другим пользователям. С другой стороны, запущенная на ночь обработка могла бы выполняться быстрее при использовании нескольких процессоров. Давайте поступим иначе — ограничим половиной процессоров. У меня их 8, ставлю 4.
Эту настройку применяем только при необходимости и в зависимости от количества процессоров.
Прирост файлов БД — реальность
Меняем дефолтные настройки прироста размера файлов данных и лога.
По умолчанию прирост 10%, это никуда не годится. Если база 500 гигабайт, то одномоментно отобранные 50 Гб места могут негативно сказаться на работе.
Ставим прирост 200 Мб.
Эту настройку применяем.
Флаги трассировки — реальность
4199 — включает исправления оптимизатора из фиксов.
1118 — использовать разные страницы памяти.
Запускаем SQL Server Configuration Manager. SQL Server Services. SQL Server — правой кнопкой свойства. Вкладка Startup Parameters. Добавляем -T1118 и -T4199.
Для применения потребуется перезапустить SQL сервер.
Эту настройку применяем.
Сетевые протоколы — реальность
Протокол «Named pipes» необходимо отключить.
Запускаем SQL Server Configuration Manager. SQL Server Network Connfiguration. Protocols for MSSQLSERVER (наш инстанс). Делаем Named Pipes — Disabled.
Эту настройку применяем.
Отключение DFSS — миф
DFSS по умолчанию устанавливается на терминальный сервер. При чём тут SQL? Отключать нечего.
Влияние настроек энергопотребления на производительность SQL Server
Сегодняшняя моя заметка будет посвящена тому, как настройки энергопотребления в операционной системе и в BIOS могут влиять на производительность SQL Server. Сразу хочу предупредить, что данная статья не является руководством к действию. Все результаты, опубликованные в этой заметке, основаны на тестировании только одной определенной нагрузки и приводятся только в качестве примера. Ни в коем случае не стоит применять указанные в статье настройки преварительно не проведя тестирование на вашей нагрузке.
Для тестирования использовалась платформа Supermicro SuperServer 6017R-N3RF4+ с парой процессоров Intel® Xeon® Processor E5-2650 и включенным Hyper Threading, 64 Гб оперативной памяти и установленным Microsoft SQL Server 2008 R2.
Конфигурацию дисковой подсистемы я не привожу, т.к. для тестирования я выбрал запрос, который дает нагрузку только на процессор. Данные из таблицы объемом приблизительно 500 Мб и содержащие поле типа XML предварительно были закэшированы в Buffer Pool. Запрос проводит выборку из вышеуказанных данных, проводя над XML данными несколько операций с использованием методов nodes, query и value. Запрос я запускал несколько раз для каждой комбинации настроек как параллельно на всех логических процессорах, так и с опцией MAXDOP = 1.
За настройки энергопотребления CPU в BIOS отвечают следующие настройки в разделе CPU Power Management Configuration: Power Technology и Energy Performance Bias. По умолчанию они имеют значения Energy Efficiency и Balanced Performance. Соответственно, чтобы отключить экономию энергии для CPU я переключал эти параметры в значения Disabled и Performance соответственно.
В операционной системе в панели управления в разделе Power Options также можно выбрать один из 3х режимов энергопотребления. Значение по умолчанию Balanced. Причем перезапуск SQL Server или операционной системы после изменения не требуется – все начинает работать сразу.
Теперь я привожу результаты тестирования. Для начала посмотрим на время выполнения запроса с опцией MAXDOP = 1.
BIOS – настройки по умолчанию BIOS, отключена экономия энергии для CPU Power Options: Balanced 3 мин. 15 сек. 4 мин. 18 сек. Power Options: High performance 3 мин. 10 сек. 4 мин. 18 сек. Power Options: Power saver 5 мин. 48 сек. 4 мин. 18 сек. Как мы видим, если оставить настройки BIOS по умолчанию и менять параметры энергопотребления в операционной системе, то можно приблизительно на 2.5% ускорить запрос только выбрав значение High performance в Power Option. И напротив, что стало для меня удивлением, если изменить настройки в BIOS – мы наоборот, теряем в производительности.
А теперь давайте посмотрим на результаты, когда запрос выполнялся параллельно на 32 логических процессорах.
BIOS – настройки по умолчанию BIOS, отключена экономия энергии для CPU Power Options: Balanced 25-26 сек. 21 сек. Power Options: High performance 18-19 сек. 21 сек. Power Options: Power saver 29 сек. 21 сек. И опять получились довольно интересные результаты. Если изменить настройки BIOS по умолчанию, то мы получаем некоторый выигрыш в производительности. Но наилучший результат показывает ситуация, когда настройки BIOS не изменяются, а в настройках энергопотребления операционной системы мы выбираем High performance.
Итак, согласно результатам получается, что не стоит изменять настройки BIOS, но изменение параметров операционной системы может ускорить время выполнения от 2.5 до 30 процентов, что, согласитесь, очень и очень неплохо. Но опять же напомню, что я проверял только одну определенную нагрузку, хотя она и была в основном на CPU. Если у вас есть возможность провести подобное тестирование нагрузки на процессор, то призываю вас делиться результатами в комментариях – обсудим.
Настройка сервера 1С и MS SQL Server
На производительность сервера существенно могут настройки, управляющие поведением мощностей процессора, дисков, доступа к памяти, размещения памяти и т.п.
Например, в целях окономии электропитания процессоры могут «занижать» частоту процессора, что приемлемо для личных компьютеров и совершенно неприемлемо для серверов с 1С.
В BIOS сервера отключаем все настройки по экономии электропитания процессора.
Если есть «C1E» — обязательно ОТКЛЮЧАЕМ!!
Для некоторых не очень параллельных задач также рекомендуется выключить гипертрейдинг в биосе.
В некоторых случаях (особенно для HP!) надо зайти в BIOS сервера, и ВЫКЛЮЧИТЬ там пункты, в названии которых есть EIST и C1E.
Взамен надо там же найти пункты, связанные с процессором, в названии которых есть Turbo Boost, включить Intel SpeedStep и ВКЛЮЧИТЬ их.
Если в биосе есть общее указание режима энергосбережения — включить его в режим максимальной производительности (он ещё может называться «агрессивный»)Обратите внимание, что такие настройки популярны, но бывают исключения, когда вендоры реализуют обозначенные выше настройки и механизмы работы иначе, и тогда может потребоваться не выключать, а включать какие-то пункты, связанные с EIST, SpeedStep и Turbo Boost.
Не забываем и также и про настройки схемы в операционной системе.
В конечном итоге надо не ориентироваться на названия этих пунктов, а на итоговые максимальные частоты процессоров. Можно контролировать их утилитой CPU-Z. Приведём пример:
вот снимок системы на базе процессора i7-4770, тактовой частотой 3.4 ГГц (о чём в явном виде написано в поле Specification: @3.40Ghz). В группе Clocks (Core #0) в пункте Multiplier (множитель) указан весь допустимый для данного процессора диапазон множителей: от 8 до 39. 8 – это состояние покоя, а 39 – это максимально возможный множитель при загрузке одного ядра. Если умножить значение множителя на написанную ниже частоту шины (Bus Speed), в данном случае 99.76 МГц, то получится текущая тактовая частота (Core Speed). В данном случае, 99.76*27 примерно равно 2693.57 МГц. Как видим, это ниже даже паспортной тактовой частоты.
Допустим, мы проделали некоторый набор изменений, и хотим увидеть разницу. Заходим сюда же, и видим искомый максимальный множитель:
Но не спешим радоваться, на снимке всего лишь моментально зафиксированная частота одного из ядер. А как обстоит ситуация на остальных ядрах? В новых версиях CPU-Z появилась возможность наблюдать множитель и частоту по всем имеющимся ядрам (меню Tools – Clocks)
Заходим туда, и видим, что не на всех ядрах множитель максимальный, некоторые ядра «сачкуют»!
Продолжаем изыскания с настройками до тех пор, пока не увидим, что при отсутствии максимальной загрузки процессоров, частота всех ядер максимальна для данного процессора:
Вот теперь уже можно со спокойной совестью запускать тест TPC и смотреть там улучшение результата.
Внимание! Если выполнять тест CPU-Z внутри виртуальной машины — частоты и множители будут всегда показываться «номинально паспортные», независимо от того, какие они по факту, хост-машина не передаёт виртуалке фактические частоты. Данный тест полезен только в случае, когда выполняется ВНЕ виртуалки.
Сервера с архитектурой Intel Sandy Bridge и более новые умеют динамически менять частоты процессора.
Для управления под линуксом отправляем к документации редхат .
Убедитесь что после настройки схемы энергоснабжения процессор работает на нужной максимальной частоте, заявленной производителем. Для этого посмотрите с помощью утилиты cpu-z на core speed.
Использование виртуальной среды означает что может быть 4 места где надо проверить влияние настроек на частоты процессора (биос физического сервер, схему электроснабжения хостовой ОС, биос виртуального сервера, схему электроснабжения виртуальной ОС ).
На серверах 1С и MS SQL Server использование антивирусов (даже сам факт инсталяции без включения) будет приводить к снижению производительности в виде периодических массовых замедлений и подвисаний интерфейса.
Совмещение ролей сервера 1С и сервера MS SQL Server дает большую производительность, особенно если использовать протокол обмена данных напрямую через память «Shared Memory».