August 2009

You are currently browsing the monthly archive for August 2009.

По материалам стать Иан Джоз (Ian Jose): Query Processor Modelling Extensions in SQL Server 2005 SP1
Перевод Александра Гладченко
Редактура Алексея Халако

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

Появившееся в SQL Server 2005 SP1 расширение оптимизатора Query Processor Modelling Extensions можно включить с помощью флага трассировки 2301. Это расширение обеспечивает возможность системы моделирования оптимизатора запросов выбирать более производительные планы исполнения сложных запросов к базе данных. Улучшенное моделирование планов запроса в некоторых случаях может привести к существенному повышению производительности исполнения запросов. Однако, эти расширения моделирования процессора запросов, могут привести к заметному увеличенному времени компиляции, и поэтому рекомендуются для использования только в тех приложениях, в которых компиляций бывает немного, и они происходят нечасто. Были добавлены следующие расширения моделирования:

  • Целочисленное моделирование (Integer Modelling)

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

  • Углублённое использование гистограммы (Comprehensive Histogram Usage)

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

  • Предположение основного ограничения (Base Containment Assumption)

    Обычно, при соединении двух отношений мы предполагаем, что для Х определенных кодовых точек (которые относятся к тому же самому ключевому диапазону, что и отношение R) при соединении с Y уникальных точек того же самого ключевого диапазона, что и в отношении S, всегда найдётся пара MIN(X,Y). Такое допущение называют “Простое ограничение”. Мы предполагаем, что наименьшее число из уникальных точек кода соотносится с точками кода на другой стороне. Это моделирование игнорирует относительную совокупность уникальных точек кода в основных формах R и S, и также игнорирует любую фильтрацию, которая была применена к основными формами R и S перед соединением. Основное ограничение основывается на предположение, что ограничение применяется только к основным отношениям и использует вероятностные методы вычисления степени соединения. Кроме того, подразумевается, что модели фильтров применены правильно, так как их поведение очень отличается от ортогональных фильтров.

  • Углублённое изменение отображения плотности (Comprehensive Density Remapping)

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

  • Углублённое согласование плотности (Comprehensive Density Matching)

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



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


Есть ещё один интересный флаг трассировки T2335, который заставит SQL Server генерировать более консервативный с точки зрения потребления памяти план исполняя запроса, не ограничивая при этом объём используемой SQL Server памяти для  кэша данных, запросов и т.п. Необходимость в этом флаге может возникнуть, если после увеличения значения «max server memory» наблюдается замедление исполнения запросов. Подробности в статье KB2413549.

По материалам статьи: Microsoft SQL Server 2008 TPC-E Trace Flags
Вольный перевод Александра Гладченко

Наиболее часто используемым способом изменения поведения SQL Server является выставление флагов трассировки. Следующие флаги трассировки поддерживаются в настоящее время Майкрософт для публикации результатов тесов производительности TPC-E. Если сотрудники Майкрософт рекомендуют Вам использовать другие флаги трассировки, которые не представлены в списке ниже, пожалуйста, сообщите об этом Джейми Редингу (Jamie.Reding@Microsoft.com) или Чарльзу Левину (Charles.Levine@Microsoft.com) до того, как вы опубликуете использование этих флагов.
Единственными поддерживаемыми для SQL Server 2008 флагами трассировки для TPC-E являются флаги: -T661 -T834 -T3502 -T8744.
Единственным поддерживаемыми для SQL Server 2008 параметрами запуска сервера для теста TPC-E являются параметры: -c -E -x, которые хорошо описаны в BOL.

661: Disable the ghost record removal process

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

834: Use Microsoft Windows large-page allocations for the buffer pool

Флаг трассировки 834 применяется в SQL Server 2008 для включения механизма распределения буферному пулу больших страницы памяти, которыми умеют оперировать последние версии Microsoft Windows. У разных аппаратных платформ может быть разный размер страниц, он может изменяться от 2 до 16 Мбайт. Большие страницы распределяются при запуске и сохраняются на протяжении всей жизни процесса. Флаг трассировки 834 повышает производительность, увеличивая эффективность TLB буфера процессоров.
Флаг трассировки 834 применим только к 64-битным версиям SQL Server 2008. Включить флаг трассировки 834 может только та учётная запись, для которой разрешена локальная политика “Lock pages in memory”. Включать флаг трассировки 834 можно только при запуске SQL Server.
Флаг трассировки 834 может препятствовать запуску сервера, если память сильно фрагментирована и это мешает распределению больших страниц. Поэтому, флаг трассировки 834 безопаснее использовать на серверах, которые обслуживают только SQL Server 2008.
Для получения более подробной информации о поддержке больших страниц Windows, перейдите на следующую страницу сайта Microsoft Developer Network (MSDN): Large-Page Support.

3502: Log Database Checkpoint Start and End times in the SQL Server ErrorLog

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

8744: Disable pre-fetching for ranges

Флаг трассировки 8744 отключает предварительную выборку для таких операторов, как “Nested Loops”. Неуместное использование этого флага может спровоцировать дополнительные физические чтения, при реализации плана с оператором “Nested Loops“.
Когда флаг трассировки 8744 включён при запуске сервер, он получает глобальный контекст. Когда он включен в сеансе пользователя, контекст ограничивается сеансом.

По материалам статьи: Microsoft SQL Server 2005 TPC-C Trace Flags
Вольный перевод Александра Гладченко

Наиболее часто используемым способом изменения поведения SQL Server является выставление флагов трассировки. Следующие флаги трассировки поддерживаются в настоящее время Майкрософт для публикации результатов тесов производительности, таких как TPC-C. Если сотрудники Майкрософт рекомендуют Вам использовать другие флаги трассировки, которые не представлены в списке ниже, пожалуйста, сообщите об этом Джейми Редингу (Jamie.Reding@Microsoft.com) или Чарльзу Левину (Charles.Levine@Microsoft.com) до того, как вы опубликуете использование этих флагов.

652: Disable page pre-fetching
Флаг трассировки 652 отключает предварительную выборку страниц во время полных просмотров. Если флаг трассировки 652 включается при запуске SQL Server, он получает глобальный контекст. Если он взводится в пользовательском сеансе, то контекст ограничивается сеансом.

661: Disable the ghost record removal process
Флаг трассировки 661 отключает системный процесс удаления фантомных записей. Фантомные записи появляются в результате исполнения операций удаления, после которых удалённые записи могут оставаться в файле как фантомные записи. Через некоторое время, удаленные записи вычищаются процессом удаления фантомных записей. Когда этот процесс отключается, удаленные записи не вычищаются. Поэтому, место, которое занимают удаленные записи, не высвобождается. Это влияет на занимаемое данными место и на производительность операций просмотра.
Флаг трассировки 661 всегда действует в контексте всего сервера, т.е. имеет глобальный контекст. Вы можете включать флаг трассировки 661 при запуске сервера или в пользовательском сеансе.

834: Use Microsoft Windows large-page allocations for the buffer pool
Флаг трассировки 834 применяется в SQL Server 2005 для включения механизма распределения буферному пулу больших страницы памяти, которыми умеют оперировать последние версии Microsoft Windows. У разных аппаратных платформ может быть разный размер страниц, он может изменяться от 2 до 16 Мбайт. Большие страницы распределяются при запуске и сохраняются на протяжении вей жизни процесса. Флаг трассировки 834 повышает производительность, увеличивая эффективность TLB буфера процессоров.
Флаг трассировки 834 применим только к 64-битным версиям SQL Server 2005. Включить флаг трассировки 834 может только та учётная запись, для которой разрешена локальная политика “Lock pages in memory“. Включать флаг трассировки 834 можно только при запуске SQL Server.
Флаг трассировки 834 может препятствовать запуску сервера, если память сильно фрагментирована и это мешает распределению больших страниц. Поэтому, флаг трассировки 834 безопаснее использовать на серверах, которые обслуживают только SQL Server 2005.
Для получения более подробной информации о поддержке больших страниц Windows, перейдите на следующую страницу сайта Microsoft Developer Network (MSDN): Large-Page Support.

836: Use the max server memory option for the buffer pool
Флаг трассировки 836 нужен для того, чтобы размер буферного пула при запуске SQL Server 2005 устанавливался на основе значения параметра глобальной конфигурации “max server memory”, вместо того, чтобы использовать в качестве ориентира размер физической памяти. Вы можете использовать флаг трассировки 836, чтобы уменьшить число описателей буфера, которые распределяются при запуске службы в 32-разрядном режиме Address Windowing Extensions (AWE). Это позволяет предоставить в памяти больше места для заимствованных из буферного пула страниц.
Флаг трассировки 836 применяется только с 32-разрядным версиям SQL Server 2005, которым разрешено распределение страниц через окно AWE. Включить флаг трассировки 836 можно только при запуске.

1228 и 1229 – Enable lock partitioning and disable lock partitioning
По умолчанию, секционирование блокировок становится возможным, когда сервер имеет 16 или более процессоров. Иначе, секционирование блокировок заблокировано. Флаг трассировки 1228 включает секционирование блокировок для двухпроцессорных и более систем. Для отключения секционирования блокировок используется флаг трассировки 1229.
Секционирование блокировок полезно на мультипроцессорных серверах, где для некоторых таблиц очень высоки нормы блокировок. Включить флаги трассировки 1228 и 1229 можно только при запуске.

2301: Enable advanced decision support optimizations
Флаг трассировки 2301 включает дополнительную оптимизацию, которая улучшает работу запросов систем поддержки принятия решений, характерных большим объёмом затрагиваемых запросом данных.
Когда флаг трассировки 2301 включается при запуске сервера, он получает глобальный контекст, иначе, он будет иметь контекст сеанса.

Флаги трассировки, которые отключают некоторые кольцевые буферы

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

8011: Disable the ring buffer for Resource Monitor
Флаг трассировки 8011 отключает сбор дополнительной диагностической информации, относящейся к Resource Monitor. Информация этого кольцевого буфера можете использовать для диагностики состояния исчерпания памяти. Флаг трассировки 8011 всегда применяется ко всему серверу и имеет глобальный контекст. Вы можете включить флаг 8011 при запуске или в сеансе пользователя.

8012: Disable the ring buffer for schedulers
Флаг трассировки 8012 отключает кольцевой буфер планировщиков. Информация этого кольцевого буфера можете использовать для диагностики проблем планирования потоков процессорам. Например, информацию этого кольцевого буфера можно использовать для выявления проблем, проявляющихся как потеря отклика от SQL Server 2005. Включать флаг трассировки 8012 можно только при запуске сервера.

8018 and 8019: Disable the exception ring buffer and disable stack collection for the exception ring buffer Этот кольцевой буфер хранит данные о последних 256 исключениях, которые были зафиксированы для узла. Каждая запись содержит немного информации об ошибке и трассировку стека. Запись добавляется кольцевым буфером в момент срабатывания исключения.
Флаг трассировки 8018 отключает создание кольцевого буфера, и после этого информация об исключения не сохраняется. Флаг трассировки 8019 отключает сбор трассировок стека, когда фиксируется информация об исключении. Флаг 8019 нельзя использовать без предварительного включения флага трассировки 8018. Отключение этого кольцевого буфера затрудняет диагностику связанных с проблемами исключений, т.е. с внутренними ошибками сервера. Включить флаги трассировки 8018 и 8019 можно только при запуске сервера.

8020: Disable working set monitoring
SQL Server 2005 использует информацию о размере рабочего множества, когда возникает необходимость интерпретировать глобальные сигналы операционной системы о состояния оперативной памяти. Флаг трассировки 8020 исключает из этой интерпретации информацию о размере рабочего множества. Этот флаг трассировки применяется только к обычным страницам памяти. Например, этот флаг трассировки не относится к заблокированным страницам или большим страницам.
Флаг трассировки 8020 может быть полезен, когда операционная система постоянно урезает рабочее множество обслуживающего SQL Server 2005 сервера, и когда сервер неспособен высвобождать память из-за большой рабочей нагрузки. В таком случае, попытки мониторинга приведут к напрасному расходованию циклов процессора. Флаг трассировки 8020 нужно использовать с осторожностью и только после глубокого тестирования на реальных рабочих нагрузках. Кроме того, использовать флаг трассировки 8020 нужно после тщательного выбора значения глобального параметра “max server memory”. Неуместное использование этого флага может привести к сильным листаниям.
Включить флаг трассировки 8020 можно только при запуске сервера.

8744: Disable pre-fetching for ranges
Флаг трассировки 8744 отключает предварительную выборку для таких операторов, как “Nested Loops“. Неуместное использование этого флага может спровоцировать дополнительные физические чтения, при реализации плана с оператором “Nested Loops”.
Когда флаг трассировки 8744 включён при запуске сервер, он получает глобальный контекст. Когда он включен в сеансе пользователя, контекст ограничивается сеансом.

По состоянию на 2009 год

Эта статья – вольная интерпретация рекомендаций: Microsoft, IBM, HP, Dell, QLogic, LSI, EMC, ACER, Bull, Fujitsu, Hitachi, NEC и Unisys. Некоторые рекомендуемые настройки требуют отдельного, обстоятельного разговора, и потому не включены в эту статью, а найти эти рекомендации можно в моём блоге.

Материал подготовил Александр Гладченко.

Обновление установки Windows

После установки Windows Server 2008 x64, установите последний сервисный пакет обновлений (Service Pack), обязательно установите выходившие после сервисного пакета исправления безопасности и последние версии драйверов и аппаратных прошивок.

Настройка файла подкачки Windows

Размер файла подкачки Windows в случае размещения на сервере только SQL Server не играет такой важной роли, как в типовых сценариях. SQL Server старается избегать листания. Размер файла подкачки можно выбрать небольшим, чтобы его было достаточно для формирования мини-дампов. Если сервер также обслуживает приложения, которые нуждаются в файле подкачки, размер его стоит выбирать в полтора раза больше, чем размер физической памяти сервера, но не более 50Гб. В случае монопольного владения ресурсами SQL Server, размещать файл подкачки можно на том же диске, где базируется операционная система. Минимальная активность работы с файлом подкачки не будет создавать конкуренцию другим задачам. В Windows Server 2003 имеется несколько предлагаемых вариантов настройки файла подкачки, он может настраиваться жёстко, может отсутствовать вообще или его размер будет управляться операционной системой. В последнем случае, Windows создаст файл подкачки, размер которого на один Мегабайт будет превышать размер физической памяти сервера. Такой выбор обусловлен тем, что ровно столько места необходимо для создания полного дампа памяти после аварии с выдачей “синего экрана”. Если вас такой дамп не интересует, можно взять управление размером файла подкачки в свои руки.

Настройка размера системных журналов

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

Настройка локальных политик Windows

Ограничьте размер системных дампов физической памяти размером 64 Гб, большой размер дампа непрактичен с точки зрения отладки. Можно оптимизировать управление памятью и существенно сократить листания с помощью установки локальной политики для пользователя, от имени которого запускается служба SQL Server. Политика называется “Lock pages in memory“, располагается она в узле “User rights assignment” локальных политик. Оболочку менеджера политик можно вызвать из командной строки, набрав там gpedit.msc и нажав ввод.

Ещё одной полезной локальной пользовательской политикой является мгновенная инициализация файла данных: “Выполнение задач обслуживания тома” (Perform Volume Maintenance). Для файлов журналов эта политика неприменима. Однако, включение этой политики хоть и позволяет сделать процесс создания файла данных, его приращение или восстановление практически мгновенным, оно чревато повышением рисков безопасности, т.к. инициализация не будет сопровождаться перезаписью новых файлов или приращиваемых к ним областей нулями. Т.е. существует риск, что расположенная в этих областях информация будет доступна для чтения и к удаленной ранее информации смогут получить доступ неавторизированные участники. Дополнительную информацию по безопасности этой операции можно найти в главе SQL Server Books Online: “Инициализация файлов базы данных“. Одной из альтернатив этому методу, также очень быстро позволяющей выполнять задачи создания и приращения файлов, является размещение файлов баз данных на “сырых” разделах (RAW).

Настройка Панели Управления Windows

Чтобы исключить снижение производительности в режиме энергосбережения, в оснастке “Электропитание” (Power Options) нужно выбрать режим “Высокая производительность” (High Performance). Стоит также убедиться, что сервер никогда не будет переключаться в спящий режим.

На закладке “Визуальные эффекты” можно отключить расходование процессорного времени на некритичные для работы сервера эффекты, выбрав режим “Обеспечить наилучшее быстродействие” (Adjust for best performance).

Настройка конфигурации системы

Отключите контроль учётных записей UAC. Для этого нажмите кнопку “Пуск” (Start), в списке “Программы” (All Programs) перейдите к папке “Администрирование” (Administrative Tools) и запустите утилиту “Конфигурация системы” (System Configuration). Отключить UAC можно на закладке “Сервис” (Tools), выбрав из списка средств задачу: “Отключите контроль учётных записей UAC”. Для применения изменений необходима перезагрузка системы.

Настройка кэширования ввода-вывода

Для включения кэширования операционной системой операций ввода-вывода логических дисков воспользуйтесь оснасткой Disk Management или Device Manager, перейдя в ней в узел Disk Drives. Для каждого настраиваемого устройства логического диска нужно выбрать Свойства (Properties) и перейти на закладку Policies. Для индивидуальной настройки дисков лучше подходит оснастка Disk Management, там настройки выполняются из свойств дисков, которые вызываются в графической, нижней части окна оснастки. Включение чекбокса “Enable write caching on the disk” разрешает кэширование записи на диск. После пометки этого чекбокса становится доступен для пометки второй чекбокс: “Enable advanced performance“. Включение обеих чекбоксов не только разрешает кэширование, но и заставляет операционную систему изымать из запросов ввода-вывода команды прямой записи на диск и сброса дискового кэша. Не рекомендуется включать эти чекбоксы если аппаратные кэши не имеют защиты от потери электропитания.

Если логический диск представлен внешним дисковым массивом, который оснащён аппаратным кэшем с батарейкой, можно не помечать второй чекбокс: “Enable advanced performance”. Однако, пометка этого чекбокса может повысить производительность записи в журнал транзакций.

Для вступления в силу изменений настроек кэша дисков перезагрузки сервера не требуется.

Настройка параметров сетевых плат

В окне Local Area Connection Properties мастера Network Connections панели управления Windows можно вызвать окно параметров сетевой платы, если нажать кнопку Configure. В этом окне на закладке Advanced перечислены несколько параметров, некоторые из них могут существенно влиять на производительность обмена данными по сети.

Link Speed and Duplex

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

Существует ещё ряд распространённых параметров, настройка которых может помочь поднять производительность сетевого интерфейса (например, Receive Buffers, Coalesce Buffers, Offload features и т.п.). Чтобы выдрать верные значения для подобных параметров, обратитесь к документации производителя сетевой платы.

Для того чтобы исключить возможность отключения сетевого интерфейса в целях оптимизации энергопотребления сервера, стоит на закладке Power Management убрать пометку чекбокса “Allow the computer to turn off this device to save power“.

Отключение неиспользуемых протоколов

Для того чтобы убрать незначительную дополнительную нагрузку, создаваемую для обслуживания неиспользуемых сетевых протоколов, предлагается отключить или деинсталлировать такие протоколы. Например, первым кандидатом на отключение является TCP/IPv6, в силу пока ещё своей малой распространённости. Сделать это можно в окне Local Area Connection Properties мастера Network Connections панели управления Windows.

SQL Server также использует по умолчанию несколько протоколов, отключить лишние из них позволяет оснастка SQL Server Configuration Manager, в которой протоколы перечислены в узле “Сетевая конфигурация SQL Server”.

В большинстве случаев и в свойствах сетевого интерфейса и в протоколах SQL Server достаточно ограничится использованием протокола TCP/IPv4.

Если же требуется поддерживать несколько протоколов, стоит задать приоритеты использования протоколов и их провайдеров в окне Advanced Settings мастера Network Connections.

Настройка антивирусного программного обеспечения

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

Необходимо настроить исключение сканирования файлов баз данных, журналов транзакций и резервных копий, которые типично имеют разрешения: mdf, ldf, ndf, bak и trn. Это позволит предотвратить повреждение этих файлов при попытке со стороны SQL Server их открытия, когда они уже открыты для проверки антивирусным ПО. Кроме того, необходимо принять меры для защиты каталогов полнотекстового поиска и содержащих данные Analysis Services от повреждений, связанных с активностью антивирусного программного обеспечения. Исключите также папку журналов SQL Server (MSSQL\Log), журнал ошибок открыт постоянно и в него может выводиться много событий. Если антивирусное ПО планируется использовать совместно с SQL Server работающем в кластере, нужно исключить сканирование кворум – диска и каталога: “c:\Windows\Cluster”. Для получения более подробной информации о требованиях к настройкам антивирусного ПО обратитесь к статье базы знаний Майкрософт: Guidelines for choosing antivirus software to run on the computers that are running SQL Server.

Во избежание вмешательства в работу служб SQL Server и для предотвращения увеличения времени их запуска из-за сканирования антивирусным ПО, рекомендуется исключить из проверки службы SQL Server. Наиболее распространенными в использовании являются службы: sqlservr.exe, sqlagent.exe, sqlbrowser.exe и sqlwriter.exe

Дополнительные рекомендации: Рекомендации по использованию антивирусных программ для компьютеров под управлением Windows Server 2003, Windows 2000 и Windows XP.

Управление памятью в SQL Server

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

Настройка параметров глобальной конфигурации SQL Server

affinity I/O mask

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

    exec sp_configure ‘affinity mask’,0xFFFE
    exec sp_configure ‘affinity I/O mask’,0x0001

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

network packet size

Следующим параметром глобальной конфигурации, изменение которого может в некоторых случаях способствовать повышению производительности приложений баз данных, является “network packet size (B)”. Увеличение размера сетевого пакета до 8192 Байт может позволить добиться выигрыша за счёт лучшего выравнивания размера пакета с размером страницы SQL Server, которая равна 8 КБ. Однако, следует учитывать, что значение этого параметра по умолчанию (4096Б), является лучшим для большинства приложений. Только тестирование позволит выбрать для этого параметра оптимальную установку.

Настройка ключей системного реестра Windows

    Важно! Эта глава содержит сведения об изменении реестра. Перед изменением реестра необходимо создать его резервную копию. Убедитесь в том, что знаете, как восстановить реестр в случае возникновения проблемы. Дополнительные сведения о создании резервной копии, восстановлении и изменении реестра см. в следующей статье базы знаний Майкрософт: Сведения о реестре Windows для опытных пользователей.

UseLargePages

Включение поддержки больших страниц может оказаться полезным для тех систем с SQL Server x64, которые оснащение большим объёмом оперативной памяти. Большие страницы способствуют повышению производительности за счёт увеличения TLB буфера процессора. Большие страницы могут использоваться для буферного пула и для кодовых страниц SQL Server. Для включения больших страниц на уровне SQL Server нужно задать флаг трассировки -T834 (это можно сделать через стартовые параметры). Кроме того, следует добавить ключ системного реестра. Содержимое reg-файла для добавления показано ниже:

 

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\sqlservr.exe]
    “UseLargePages”=dword:00000001

Для вступления изменений в силу потребуется перезагрузка системы.

Если поддержка больших страниц включается для Windows 2008 R2, нужно учесть информацию из следующей статьи: After you enable large pages for a process in Windows 7 or in Windows Server 2008 R2, the process stops responding intermittently

Поддержка больших страниц включается автоматически, если выполняются перечисленные ниже условия, подробности в статье: SQL Server and Large Pages Explained….

·    SQL Server Enterprise Edition

·    У сервера должно быть больше 8Гб оперативной памяти

·    Привилегия “Lock Pages in Memory” должна быть дозволена локальной политикой для пользователя, в контексте которого запускается служба MSSQLServer.

LargeSystemCache/Size и IdleFrom0Delay

Установив значение LargeSystemCache в 0, тем самым устанавливается стандартный размер кэша файловой системы, который равен приблизительно 8 Мб, максимальный размер кэша файловой системы не будет превышать 512 Мб. Эта установка рекомендуется для таких программ, которые осуществляют кэширование памяти самостоятельно, и к таким программам относится SQL Server. Ниже показан reg-файла для задания такой установки:

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
    “LargeSystemCache”=dword:00000000

То, насколько агрессивно будет заниматься физическая память под задачи файлового кэша, зависит от установки следующего ключа реестра:

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters]
    “Size”=dword:00000001

Возможные значения: 1-вяло, 2-сбалансировано, 3-агресивно. Для установок с малой нагрузкой на файловый кэш, вполне достаточно 1.. Другие установки свойственны файловым серверам разного масштаба, впрочем, для тестов TPC-E часто выбирают 3.

Отключить режим экономии энергии, который тоже может замедлять некоторые операции, можно с помощью ключа IdleFrom0Delay. Сделать это можно так:

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
    “IdleFrom0Delay”=dword:00000000

IoPageLockLimit, DisablePagingExecutive и DontVerifyRandomDrivers

В одном из документов по оптимизации мне попадалась следующая формула: “IoPageLockLimit = (RAMMb – 65) * 1024″. С помощью ключа IoPageLockLimit можно повлиять на то, сколько байт система будет читать или писать на логичекский диск за один раз.

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

Вот как могут выглядеть значения этих ключей на практике:

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management]
    “DisablePagingExecutive”=dword:00000001
    “DontVerifyRandomDrivers”=dword:00000001
    “IoPageLockLimit”=dword:00d9bc00

CountOperations

Параметр CountOperations позволяет отключить сбор данных по некоторым счётчикам производительности, которые относятся к запросам ввода-вывода дисковой подсистемы и сетевых интерфейсов. Чтобы это сделать, нужно в ключе системного реестра “I/O System” установить значение 0 для следующего параметра:

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\I/O System]
    “CountOperations”=dword:00000000

Требуется перезагрузка.

NumberOfRequests и MaximumSGList

Эта пара ключей системного реестра предназначена для управления драйвером минипорта в момент инициализации последнего.

Увеличивая значение NumberOfRequests может способствовать повышению производительности обслуживания в Windows запросов дискового ввода-вывода, адресованных логическим дискам, и бывает эффективно только если эти логические диски являются аппаратными RAID-массивами, которые обладают возможностью распараллеливания запросов ввода-вывода. Рекомендованное значение можно найти в документации производителя FC-адаптера или RAID-контроллера. Увеличивать значение нужно осторожно, т.к. большое значение может привести даже к отказу системы. Например, для HBA адаптера QLogic, управляемого драйвером “QLogic Fibre Channel Miniport Driver”, в документации не рекомендуется превышать значение 150. Новое значение вступает в силу после перезагрузки системы или, в некоторых случаях, достаточно перезапустить адаптер (заблокировать/разблокировать).

Ключ MaximumSGList позволяет изменять используемый по умолчанию размер пакета передачи данных по шине (64Кб), который актуален для команд интерфейса SCSI. Если установить значение 255, то размер передаваемого одной командой объёма данных будет равняться мегабайту. Современные адаптеры умеют объединять до 265 сегментов данных, каждый по 4096 байт, что в сумме может дать размер одной передачи до 1048576 байт. Этот параметр широко используется для повышения эффективности использования ленточных накопителей, а также для оптимизаций таких задач SQL Server, которые оперируют большими запросами ввода-вывода, например, резервное копирование и восстановление.

В описаниях тесов TPC-C встречается установка обоих ключей в значение 255, как это показано в примере ниже:

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ql2300\Parameters\Device]
    “DriverParameter”=””
    “BusType”=dword:00000006
    “NumberOfRequests”=dword:000000ff
    “MaximumSGList”=dword:000000ff
    “CreateInitiatorLU”=dword:00000001
    “DriverParameters”=”UseSameNN=1;buschange=0″

IdlePrioritySupported

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

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\SCSI\Disk&Ven_IBM&Prod_1726-4xx__FAStT\5&22c73432&0&000000\Device Parameters\Classpnp]
    “IdlePrioritySupported”=dword:00000000

Ключ Classpnp скорее всего придётся добавить. Подобные тонкие настройки были мной замечены у IBM в тестах TPC-E.

TCPWindowSize

В Windows 2008 этот ключ больше не используется. В более ранних версиях увеличение размера окна может повысить эффективность сетевого трафика. Рекомендуемое значение (64240) для ключа TCPWindowSize представлено ниже:

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
    “TCPWindowSize”=dword:0000faf0

Подробности можно узнать в статье Базы Знаний Майкрософт: Description of Windows 2000 and Windows Server 2003 TCP Features.

Если размер окна в 64240 Байт тоже ограничивает производительность сети, существует возможность увеличения размера окна до одного Гигабайта. Ключ реестра TCP1323Opts разрешает динамическое мастабирование окна. Рекомендуемым значением для этого ключа является: 0x3.

Для дальнейшей оптимизации производительности сетевых интерфейсов попробуйте использовать следующие ключи системного реестра Windows, описание которых легко найти в Базе Знаний Майкрософт: TCPMaxConnectRetransmissions, TCPMaxDataRetransmissions, TCPTimedWaitDelay, MaxUserPort, MaxHashTableSize, NumTcbTablePartitions, TcpAckFrequency, MTU и EnablePMTUDiscovery.

Рекомендуемые к отключению службы

Application Management Alerter, Clipbook, Computer Browser, Distributed file system, Distributed link tracking client, Error Reporting Service, Fax Service, File Replication, Help and Support HTTP SSL, License Logging, Messenger, Portable Media Serial Number Service, Shell Hardware Detection, Windows Audio, Wireless Configuration.

Отключение защиты от перезаписи обработчика структурных исключений (SEHOP) в ОС Windows

Сохраните это в текстовом файле с расширением “reg” на сервере. Щёлкните по файлу два раза мышкой, и убедитесь, что изменения в реестре были применены успешно. Перезагрузите систему. 

Windows Registry Editor Version 5.00

 

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\kernel]

“DisableExceptionChainValidation”=dword:00000001

Пояснения тут: http://support.microsoft.com/kb/956607

В тему

Параметры настройки для SQL Server 2005 и SQL Server 2008 при работе в высокопроизводительных рабочих нагрузок