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

Под редакцией Нэнси Мичел (Nancy Michell)

Настройка памяти

Совет. Упрощенное профилирование

Знаете ли вы, что теперь можно использовать системный монитор совместно с профайлером SQL Server 2005?

Возможно, вам приходилось замечать в системном мониторе пиковую загрузку ЦП, избыточное потребление памяти или общее падение производительности на компьютере с SQL Server, и вы не понимали причин подобных аномалий производительности. В версиях SQL Server до SQL Server 2005 необходимо было с помощью профайлера выполнить трассировку, затем изучить системные процессы в программе Enterprise Manager, и, наконец, записать данные в журналы системного монитора, для чего, естественно, требовалось запустить системный монитор. И даже после всей проделанной с помощью указанных выше средств работы необходимо было вручную сопоставить события, записанные разными программами, чтобы выяснить причины падения производительности. А это, в свою очередь, требовало кропотливой работы по просмотру всех строк в каждом журнале. Вряд ли этом можно назвать увлекательным занятием, но без него было невозможно выяснить суть проблем с производительностью.

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

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

1. Запустите системный монитор и начните сбор данных с сервера баз данных.
2. Создайте в группе «Журналы и оповещения производительности» новый журнал счетчиков и введите имя журнала.
3. Добавьте новые счетчики, например «% загруженности процессора». На вкладке «Расписание» можно настроить ведение журнала для запуска вручную или по расписанию.
4. Нажмите кнопку «ОК» и, если был выбран запуск вручную, убедитесь в том, что ведение журнала включено.
5. Настройте трассировку в профайлере SQL Server. Это можно сделать с помощью команды «Создать трассировку» меню «Файл». Включите в трассировку время начала и время окончания, присвойте ей имя и настройте трассировку таким образом, чтобы результаты сохранялись в файл. Наконец, смоделируйте на сервере какие-либо транзакции, после чего остановите сбор данных в системном мониторе и профайлере.
6. Выберите в меню «Файл» профайлера команду «Импорт данных производительности». Затем выберите папку, в которую был сохранен файл журнала системного монитора, с помощью пунктов «Открыть» и «Трассировка» меню «Файл». Наконец, выберите папку, в которую был сохранен файл трассировки профайлера.

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

В. Я пытаюсь определить наилучшую конфигурацию памяти для серверов SQL Server™. Администратор, работавший до меня, настроил файл Boot.ini для каждого компьютера с 12 ГБ ОЗУ в кластере SQL Server 2000 с двумя узлами следующим образом: Yes /PAE NO /3GB (для SQL Server не включены расширения AWE). Так как доступно 12 ГБ ОЗУ, следует ли мне удалить из файла Boot.ini параметр /3GB, включить расширения AWE и предоставить серверу SQL Server примерно 10 ГБ ОЗУ из 12? На компьютерах запущен только SQL Server, поэтому не нужно выделять память для других приложений.

О. Да, расширения AWE следует включить и установить верхнюю границу ОЗУ для SQL Server на уровне 10 ГБ — это нормально при наличии выделенного под SQL Server компьютера с 12 ГБ ОЗУ. (Имейте в виду, что предварительное выделение памяти возможно только для SQL Server 2000. В версиях начиная с SQL Server 2005 расширения AWE перестали быть статическими и могут изменяться динамически). Вопрос о том, следует ли использовать оба параметра /3GB и /PAE или только параметр /PAE, всегда был предметом ожесточенных споров. Хотя в действительности необходимо использовать только параметр /PAE и включить расширения AWE, я рекомендую использовать оба параметра; однако необходимо учитывать ряд факторов.

Вопрос об использовании параметра /3GB сводится к тому, необходим ли он в конкретной ситуации. Выходите ли вы за пределы областей памяти MemToLeave, которые должны быть расположены в первых двух или трех гигабайтах виртуального адресного пространства? Хватает ли операционной системе памяти после включения данного параметра? (Дополнительные сведения см. в статье по адресу support.microsoft.com/kb/316739 (на английском языке)). При использовании кластера можно включить параметр /3GB на одном узле и отключить на другом. Таким образом, в случае возникновения проблем при тестировании параметра /3GB можно достаточно быстро переключиться на другой узел. Следует иметь в виду, что при наличии более 16 ГБ ОЗУ параметр /3GB не поддерживается.

При использовании параметра /3GB виртуальное адресное пространство увеличивается на 50%, что позволяет повысить производительность приложений, интенсивно использующих виртуальное адресное пространство, а не только кэш данных. К счастью, в 64-разрядных серверах, построенных как на основе архитектуры IA64, так и x64, этот фактор устранен. Что касается нехватки памяти для ОС, этот вопрос не имеет отношения к компьютерам, специально отведенным для работы SQL Server. 2 ГБ ОЗУ для ОС более чем достаточно; если сервер предназначен только для работы SQL Server и на нем запущен минимальный набор стандартных служб ОС, на компьютере будет свободно примерно 1,3 ГБ памяти, поэтому можно позволить серверу SQL Server использовать дополнительный гигабайт ОЗУ. Начните с 10 ГБ, отслеживайте с помощью системного монитора объем доступной памяти в течение длительного промежутка времени, после чего внесите соответствующие изменения. Помните, что недостаточное выделение памяти для SQL Server 2000 приведет к использованию файла подкачки, поскольку расширения AWE нельзя изменять динамически, как это делается в SQL Server 2005. Вопрос о необходимости использования параметра /3GB решается в результате тестирования в конкретной среде.

Имена экземпляров для репликации

В. Можно ли теперь использовать IP-адрес сервера в процессе репликации SQL Server 2005, чтобы указать экземпляр для репликации? В SQL Server 2000, как показано в статье «Репликация между компьютерами с SQL Server, расположенными в доменах, не являющихся доверенными, или через Интернет» (support.microsoft.com/kb/321822, на английском языке), такие действия привели бы к ошибкам, но мне неизвестно, исправлено ли это в новой версии.

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

Если сетевое имя экземпляра отличается от имени экземпляра SQL, добавьте имя экземпляра SQL Server в качестве допустимого сетевого имени. Один из способов задать альтернативное сетевое имя — добавить его в локальный файл hosts. Файл hosts по умолчанию находится в папке WINDOWS\system32\drivers\etc или WINNT\system32\drivers\etc. Например, если имя компьютера — comp1, IP-адрес компьютера — 10.193.17.129, а имя экземпляра — inst1/instname, добавьте в файл hosts следующую запись:

10.193.17.129 inst1

Службы интеграции SQL Server

В. Я устанавливаю кластер в режиме «активный/активный» для SQL Server 2005 (64-разрядная версия Enterprise Edition с двумя серверами), а всего имеется четыре экземпляра сервера SQL Server 2005. Службы интеграции SQL Server (SSIS) потребуются для всех экземпляров. Возможна ли кластеризация служб интеграции SQL Server и каково влияние кластеризации на планы обслуживания?

О. Кластеризация служб интеграции SQL Server возможна, однако это не является необходимым и может привести к различным проблемам, включая ограничение поддержки делегирования (см. статью по адресу msdn2.microsoft.com/aa337083) и отсутствие возможности одновременного запуска на одном узле нескольких экземпляров службы.

Ранее для работы мастера планов обслуживания было необходимо установить службы интеграции SQL Server (хотя их запуск был необязательным). Однако это не относится к SQL Server 2005 с пакетом обновления 1 (SP1). Если службы интеграции SQL Server не запущены, планы обслуживания могут выполняться агентом SQL Server.

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

Дополнительные сведения о сбоях при создании плана обслуживания см. в статье базы знаний Майкрософт по адресу support.microsoft.com/kb/909036 (на английском языке).

Странное время выполнения

В. В процессе тестирования под нагрузкой компьютера, отведенного для SQL Server 2005 с пакетом обновления 1 (SP1), в профайлере SQL Server было записано множество отрицательных значений для времени выполнения хранимой процедуры, а в некоторых случаях время выполнения хранимой процедуры не соответствовало разнице между временем окончания и временем начала выполнения процедуры.

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

Например, при использовании других схем управления питанием, внутренних версий процессора или технологии «Cool 'n Quiet» компании AMD тактовая частота ЦП изменяется и перестает соответствовать значению, используемому профайлером SQL Server при расчете времени выполнения.

В статье базы знаний Майкрософт по адресу support.microsoft.com/kb/931279 (на английском языке) приведены симптомы, возможные причины и способы устранения проблемы.

Совет. Проверьте коэффициент заполнения

Предположим, что у вас есть стакан, полностью заполненный водой, и вы пытаетесь долить в стакан еще воды. Что произойдет? Вода выплеснется.

То же самое может произойти и с SQL Server. Каждый раз при добавлении новой строки на страницу с полностью заполненным индексом SQL Server перемещает приблизительно половину строк на новую страницу, чтобы освободить место для новой строки. Этот процесс называется разбиением страницы. При разбиении страниц высвобождается пространство для новых записей, но этот процесс занимает много времени и потребляет много ресурсов. Разбиения страниц могут привести к фрагментации, что отрицательно скажется на производительности операций ввода-вывода. Так как же избежать разбиений?

Чтобы предотвратить появление подобных ситуаций, необходимо заблаговременно определить значение коэффициента заполнения. При создании или перестроении индекса значение коэффициента заполнения определяет процент заполнения данными каждой страницы конечного уровня; остальное место резервируется для последующего роста базы данных. Например, коэффициент заполнения, равный 60, означает, что 40% каждой страницы конечного уровня останутся пустыми. Пустое пространство будет использоваться для расширения индекса по мере добавления данных в соответствующую таблицу.

Коэффициент заполнения по умолчанию всегда равен 0, что нормально для большинства ситуаций. По существу, коэффициент заполнения 0, означает, что страницы конечного уровня заполнены почти полностью, но при этом имеется место по крайней мере для одной дополнительной строки индекса. (Обратите внимание на то, что значения коэффициента заполнения 0 и 100 эквивалентны).

Значение коэффициента заполнения для отдельных индексов можно задать с помощью инструкций CREATE INDEX и ALTER INDEX, либо настроить это значение непосредственно на уровне сервера таким образом, чтобы оно устанавливалось по умолчанию для всех вновь создаваемых индексов.

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

USE Master; GO SP_Configure 'show advanced options',1; GO SP_Configure 'Fill Factor', 70; GO — чтобы изменения вступили в силу, необходимо перезапустить SQL Server.

Что делать, если необходимо настроить коэффициент заполнения на уровне отдельных индексов? Предположим, что создается таблица, для которой необходимо создать уникальный индекс для столбца Col_A с коэффициентом заполнения 70. Команда будет выглядеть следующим образом:

USE Item_DB; GO CREATE TABLE ITEM (Col_A Varchar(100),Col_b Varchar(200)); GO; — создание таблицы Item

CREATE UNIQUE INDEX AK_Index ON Item (Col_A) WITH (FillFactor = 70); GO — создание уникального индекса для столбца Col_A таблицы Item с коэффициентом заполнения 70

Как определить значение коэффициента заполнения для каждого индекса? Чтобы получить значения коэффициентов заполнения для всех индексов в базе данных, необходимо выполнить запрос к таблице sys.Indexes, например, таким образом:

USE Item_DB; GO SELECT Fill_Factor FROM Sys.Indexes WHERE Object_id=object_id('item') AND name IS NOT NULL; GO

© 2008 Корпорация Майкрософт и компания CMP Media, LLC. Все права защищены; полное или частичное воспроизведение без разрешения запрещено.