О настройке конфигурации SQL Server 7.0

Опыт поддержки SQL Server 6.5 приучил администраторов к тому, что СУБД можно очень эффективно настраивать вручную, изменяя параметры ее конфигурации. И при работе с версией 7.0 администраторам и консультантам не хочется менять свои привычки, и конечно же их интересует, есть ли подобные возможности в SQL Server 7.0. Именно таким администраторам адресован этот материал.

Хорошо известны общие положения оптимизации работы СУБД. Настройка выполняется с целью увеличения производительности. Производительность сервера измеряется транзакциями в секунду, а клиента – определяется временем отклика. Исходной картиной для настройки является базовая линия (Base Line). Настройка и оптимизация выполняются сначала для приложения и физической реализации базы данных, и лишь затем изменяются параметры конфигурации сервера. Но именно о конфигурации сервера и пойдет речь в этой статье.

Есть еще одно общее правило: не изменять несколько параметров одновременно. Изменили, увидели положительный эффект, оставили. Не увидели положительного эффекта – вернули в исходное состояние. И, конечно, никто не проводит эксперименты с рабочим, «боевым», сервером.

В Microsoft автоматическую настройку SQL Server 7.0 называют одной из его замечательных положительных черт. И это подтверждает практика. Почти все опции, влияющие на производительность сервера, в документации SQL Server 7.0 отмечены как SC. Это означает, что сервер устанавливает для них оптимальные значения самостоятельно (Self Configuring). С другой стороны, если оптимальные значения параметров устанавливаются автоматически и их не рекомендуется изменять, зачем тогда открыт доступ к ним? Зачем разработчики все-таки оставляют администраторам искушение настраивать сервер? Если параметры доступны, очень хочется этим воспользоваться. Некоторые специалисты называют параметры Memory, Locks, User connections первыми очевидными претендентами на настройку. Рассматривая возможность конфигурирования сервера, стоит начать с параметров, для изменения которых нет ограничений и запретов. Есть ли такие параметры и как с ними работать? Постараемся ответить на этот вопрос.

Итак, если для настройки приложения уже все готово, а уровень производительности не устраивает, можно попробовать заняться сервером. Но при этом необходима самая тщательная проверка и тестирование.

Из параметров конфигурации SQL Server 7.0 нужно выбрать те, которые связаны с производительностью и не отмечены как настраиваемые автоматически. Среди них существуют еще и такие, которые имеет смысл изменять только в многопроцессорных конфигурациях: affinity mask и lightweight pooling. Сюда же можно отнести параметр priority boost. В базе знаний MSDN отмечается, что установка значения этого параметра в 1 (что означает изменение приоритета с 7 на 15 в однопроцессорном варианте сервера) не дает положительного эффекта. Настройку многопроцессорной конфигурации стоить рассмотреть в отдельной статье.

Параметры памяти SQL Server 7.0 по умолчанию регулируются автоматически. Max server memory и Min server memory на выделенном сервере настройки не требуют. Ручная настройка параметра Max server memory на практике применяется для ограничения объема памяти, которую может использовать SQL Server, и обеспечения гарантии запуска и работы других приложений. А это не связано с оптимизацией СУБД.

В результате отсева остается два параметра: Network packet size и Min memory per query, которые не запрещено настраивать и которые не настраиваются автоматически. Рассмотрим их подробнее.

Network packet size

Сетевая библиотека SQL Server передает и получает данные из сети фиксированными порциями, размер которых соответствует размеру сетевого пакета (Network packet size). Маленький размер пакета (512 байт) будет эффективен, если определяющим фактором является время реакции сервера. Увеличение размера сетевого пакета СУБД при условии, что сетевые протоколы тоже поддерживают большие пакеты, приведет к увеличению пропускной способности, так как передача больших пакетов обходится дешевле. Это имеет смысл только в том случае, когда работа приложений действительно требует в ответ на один запрос или в рамках одной транзакции пересылать большой объем данных. Настраивать значение параметра network packet size следует только на выделенном под СУБД сервере.

Если в таблицах базы данных имеются поля типа Text и Image, можно записать идентификаторы этих объектов и с помощью SQL Server Profiler получить трассировку событий Object: Opened и Object:Closed c соответствующей фильтрацией по Object ID. Теперь нужно посмотреть, насколько активно идет работа с полями этих типов. Если выяснилось, что с типами Text и Image постоянно идет интенсивная работа, можно продолжить анализ.

Как определить, что через сеть отправляются и принимаются большие объемы данных? Какой объем считать большим и насколько увеличивать размер пакета? Рассмотрим отдельно отправляемые и получаемые данные. Размер сетевого пакета стоит увеличивать, если средний объем данных, возвращаемых запросами, устойчиво превышает размер сетевого пакета по умолчанию. Определить объем данных, отправляемый сервером в сеть, позволяет административное приложение Network Monitor. В этом приложении нужно установить фильтрацию отправляемых сервером пакетов TCP и IP, узнать среднюю скорость передачи за конкретный период времени, а именно параметр Bytes Per Second. Объем принимаемых данных выводится аналогично. Но как по скорости передачи определить средний объем одного запроса? Для этого нужно знать среднее количество запросов в секунду. Такого счетчика в мониторе производительности SQL Server нет. Можно воспользоваться средним значением счетчика Batch Requests/sec объекта SQL Server: SQL Statistics. Он показывает, сколько пакетных запросов на выполнение SQL-операторов, получаемых в секунду. Эта статистика определяется операциями ввода/вывода, количеством пользователей, размером буферного кэша, сложностью запросов и т. д. Высокий показатель счетчика свидетельствует о большой пропускной способности сервера. Если значение параметра Bytes Per Second, полученное в Network Monitor, разделить на среднее значение счетчика Batch Requests/sec, определяемое в Performance Monitor, то получим очень грубую среднюю оценку количества байтов, соответствующих одному пакетному запросу. Очевидно, что показания в Network Monitor и Performance Monitor нужно снимать одновременно. Если полученное значение намного превышает размер сетевого пакета, то последний можно увеличить. Максимальный размер сетевого пакета в SQL Server 7.0 + SP2 составляет 64 Кбайт.

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

Рекомендованное в документации увеличение размера сетевого пакета при выполнении команды Bulk Insert имеет смысл только в том случае, когда исходные файлы находятся не на сервере. Такие ситуации можно выявить с помощью SQL Server Profiler, настроив фильтрацию по критерию Text: Include: Bulk Insert.

Сегодня основным транспортным протоколом в сетях стал TCP/IP. В SQL Server 7.0 сетевая библиотека TCP/IP устанавливается по умолчанию как одна из основных. Как соотносится параметр Network packet size и параметры протокола TCP/IP? Нужно ли настраивать размер сетевого пакета SQL Server в соответствии с размером TCP-окна?

Размер окна TCP определяет объем информации, который можно передать без подтверждения. В Windows NT максимальный размер окна равен 64 Кбайт, что соответствует максимальному размеру сетевого пакета SQL Server. При установке соединения между хостами размер окна задается равным четырем максимальным размерам TCP-пакета. Для сети Ethernet по умолчанию начальный размер окна равен 16 Кбайт. С помощью Network Monitor можно увидеть, что в таком окне как раз помещается четыре сетевых пакета SQL Server. В Windows 2000 предусмотрен автоматический переход к масштабированию размеров окна, если максимальный размер TCP-пакета превышает 64 Кбайт. В предельном случае размер окна может составлять 1 Гбайт. Таким образом, о соотношении размера окна TCP и размера сетевого пакета SQL Server 7.0 позаботились разработчики сервера, и администратору беспокоиться об этом не нужно. Размер сетевого пакета никогда не превысит размера окна. Другие транспортные протоколы и соотношение с ними размеров сетевого пакета мы в этой статье не рассматривать не будем.

Что касается физического уровня, то даже для 10-мегабитного Ethernet 32разрядная сетевая карта имеет скорость передачи порядка 1,2 Мбит/с. Следовательно, здесь ограничений тоже нет.

Min memory per query

Этот параметр определяет минимум памяти, гарантированно выделяемый каждому запросу. Выделяемая память имеет область сортировки. Если запрос не может получить минимум памяти, он ожидает своей очереди в соответствии со значением параметра query wait. Иногда система может позволить запросу задействовать памяти больше, чем указывает параметр min memory per query. Запросы, требующие много памяти, включают сортировку или хеширование. Прежде чем пытаться поднять производительность, увеличивая значение параметра min memory per query, нужно выяснить, есть ли запросы, требующие такого увеличения.

Хеширование используется при реализации операторов Join. Такие запросы можно отфильтровать по критерию Text: Include: Join в SQL Server Profiler. Запросы с сортировкой фильтруются по критерию Text: Include: Order by. Просто отфильтровать такие запросы недостаточно. Нужно не учитывать случаи применения специально созданных для ускорения сортировки индексов. Если соответствующие индексы используются, то большой объем памяти для сортировки не нужен. В этом случае запросы с сортировкой выполняются сравнительно быстро.

Значение параметра min memory per query увеличивается для того, чтобы запрос получал сразу много памяти и не обращался за ней дополнительно. Определить общий объем памяти, выделенной под хеширование, сортировку, создание индексов и выполнение bcp, можно с помощью счетчика Granted Workspace Memory объекта SQL Server: Memory Manager в Performance Monitor. Его значение увеличивается только тогда, когда для сортировки нет подходящих индексов. По счетчику Maximum Workspace Memory определяют, сколько всего имеется памяти для выполнения таких операций, и его показатель, как и всякий общий резерв, должен намного превышать значение Granted Workspace Memory. Счетчик Memory Grants Pending показывает количество процессов в секунду, ожидающих получения памяти. Если выполняется много параллельных запросов c хешированием и сортировкой, а минимальный размер памяти увеличен, то не все запросы смогут ее получить. В этом случае тестирование после увеличения параметра min memory per query показывает возросшее среднее значение Memory Grants Pending, следовательно, min memory per query нужно восстановить. Очень важно, чтобы оперативной памяти на компьютере было не меньше 1 Гбайт. В противном случае эффекта ожидать не стоит.

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

КРАМАРСКАЯ ТАТЬЯНА АЛЕКСЕЕВНА

к.т.н., MCSE, MCDBA, MCT. Специализируется на преподавании и консультировании по вопросам использования SQL Server и продуктов BackOffice. Старший консультант компании АЛЕСТА (MCSP,CTEC). Адрес: kram@alesta.ru