СОПРОВОЖДЕНИЕ, ОПТИМИЗАЦИЯ И НАСТРОЙКА MICROSOFT SQL SERVER

КАЧЕСТВО РАБОТЫ: 77%

СОПРОВОЖДЕНИЕ, ОПТИМИЗАЦИЯ И НАСТРОЙКА MicroSOFT SQL SERVER

На основе этой информации вы сможете сами справляться с большинством задач и решать большинство возможных проблем.

1. Заранее планируйте инсталляцию SQL Server;

2. Уделяйте большое внимание проектированию баз данных, запросов и индексов;

3. Старайтесь получить как можно больше информации о загрузке ресурсов сервера;

4. Планируйте и автоматизируйте регламентные работы;

5. Управляйте риском - готовьтесь к возможным сбойным ситуациям.

ИНСТАЛЛЯЦИЯ MicroSOFT SQL SERVER

Инсталляция Microsoft SQL Server в общем случае очень проста. Но к ней надо подойти ответственно, так как во время инсталляции устанавливаются несколько параметров, изменить которые в дальнейшем, при уже работающем сервере и внесенной в базы данных информации, бывает непросто. Так что если вы хотите выполнить инсталляцию только один раз, не спешите.

SQL Server инсталлируется при помощи программы setup, которая также используется для замены предыдущей версии SQL Server (например, 4.21) на новую, а также для последующей до-установки некоторых компонент сервера и изменения некоторых параметров.

Требования к аппаратуре

Вам необходим для работы с Microsoft SQL Server компьютер архитектуры Intel с процессором 486 или Pentium (SQL Server также поставляется в версиях для RISC-процессоров Alpha и MIPS). Минимально необходимый объем памяти - 16 Мб, рекомендуется начинать с 32 Мб. Если вы соберетесь использовать ваш SQL Server в качестве сервера-распространителя при тиражировании данных, вам понадобится минимум 16 Мб для собственно SQL Server, а ведь еще нужна память собственно для Windows NT Server.

Также, естественно, необходим жесткий диск с 75 Мб свободного места. Это в случае, если вы установите электронную документацию по SQL Server на жесткий диск. Вы можете не устанавливать ее на жесткий диск и читать ее с CD-ROM, что сэкономит вам 15 Мб дисковой памяти. Я рекомендую использовать электронную документацию, т.к. очень удобно осуществлять в ней быстрый поиск интересующей вас информации.

Ну и, конечно, необходима операционная система Windows NT Server версии 3.5 или выше (рекомендуется 3.51).

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

Кодовая страница

Выбранная кодовая страница определяет, какие символы будут рассматриваться сервером как пригодные для печати и наименования, например, дней недели и месяцев. Также кодовая страница, совместно с выбранным порядком сортировки, определяет, как будут сортироваться и сравниваться между собой символьные строки. Очень рекомендуется устанавливать на всех клиентах и сервере одну и ту же кодовую страницу. SQL Server 6.0 позволяет установить страницу № 1251, используемую для работы с русским языком в Windows, так что тут никаких проблем нет. Если вы не предполагаете работать с русским языком, то можно установить страницу № 850 (многоязычная) или № 437 (U.S. English).

Порядок сортировки

Порядок сортировки определяет:

1.  Как будут сортироваться записи при использовании в запросе ORDER BY

2. Как будут сравниваться между собой символьные строки 3.Скорость выполнения операций сортировки.

Существует два основных типа порядков сортировки: двоичный и по словарю.

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

Большие буквы будут в отсортированном порядке идти раньше маленьких, то есть большая буква "Я" - раньше маленькой "а". Это может породить некоторые проблемы в вашем конкретном приложении, хотя в некоторых случаях двоичный порядок оказывается вполне приемлемым. Но если вы хотите, чтобы символы сортировались в более удобном для вас порядке, вам надо использовать один из порядков сортировки по словарю.

Их существует несколько, имеет смысл рассмотреть т.н. регистро-независимый порядок (Case-Insesitivity), при котором буквы сортируются независимо от того, большие они или маленькие. Именно он предлагается при инсталляции по умолчанию. При использовании этого порядка операции сортировки работают примерно на 20% медленнее, чем при двоичном.

Сетевые установки

Microsoft SQL Server 6.0 может взаимодействовать с клиентами по многим протоколам сеансового уровня. Это:

• Named Pipes

• NWLink IPX/SPX

• TCP/IP Sockets

• Banyan VINES

• AppleTalk ADSP

• DECnet

Надо отметить, что работа по одному протоколу не исключает работы по другому и сервер может взаимодействовать с клиентами по нескольким протоколам одновременно. Для этого загружаются одна или несколько сетевых библиотек (Net-Libraries).

Кроме того, протокол Named Pipes работает над тремя протоколами транспортного уровня - NetBEUI, IPX/SPX и TCP/IP. Так что он устраивал в большинстве случаев использования SQL Server 4.2 и устанавливается по умолчанию именно он.

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

В версии SQL Server 6.0 появилась новая сетевая библиотека "Multi-Protocol", работающая сразу с тремя протоколами сеансового уровня - Named Pipes,

NWLink IPX/SPX, TCP/IP Sockets. Эта сетевая библиотека позволяет кодировать информацию, передаваемую между клиентом и сервером.

Режим секретности

Существует три режима секретности SQL Server:

1. Интегрированный с Windows NT;

2. Стандартный;

3. Смешанный.

Интегрированный режим позволяет пользователю, зарегестрировавшемуся в домене Windows NT, подключаться к серверу, не указывая имени и пароля - для определения его прав на SQL Server будет использовано его регистрационное имя в Windows NT. То есть существует единая регистрация - в домен и на SQL Server. Этот режим возможен при подсоединении пользователя по т.н. "доверительным соединениям", которые осуществляются при использовании сетевых библиотек "Named Pipes" и "Multi-Protocol". По другим соединениям клиенты работать в этом режиме не могут.

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

Основное преимущество интегрированного режима состоит в следующем. Секретность Windows NT имеет такие мощные средства, как устаревание пароля и ограничение на минимальную длину пароля. Этих средств нет в SQL Server, но они могут быть использованы для контроля доступа к SQL Server при использовании интегрированного режима секретности.

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

Имена пользователя для SQLExecutive и SQL Server

Сервис, называемый SQLExecutive, выполняет очень большую работу, связанную с выполнением плановых заданий, реакцией на происходящие события и тиражированием данных. Каждый сервис в Windows NT функционирует в т.н. контексте секретности, определяемом именем, под которым он регистрируется в Windows NT. По умолчанию SQLExecutive регистрируется под именем LocalSystem, т.е. как локальный системный сервис. Но для ряда процессов, связанных с соединением вашего SQL Server с другими серверами, в первую очередь для тиражирования, необходимо регистрировать SQLExecutive под именем, обеспечивающим ему доступ к другим серверам. Это имя должно:

• относиться к группе администраторов;

• иметь не устаревающий пароль;

• иметь право регистрироваться как сервис.

Вполне возможно (и даже более удобно) присвоение сервису SQLExecutive одного и того же имени на разных серверах.

Хотя ипрограмма setup и не требует задания имени, под которым буде регистрироваться сам SQL Server, лучше после установки сменить это имя с LocalSystem на "нормальное" имя. Это пригодится при создании резервных копий на жестких дисках других компьютеров, а также при работе с Microsoft Exchange.

Удаленная и автоматическая инсталляция

Есть два способа облегчить себе работу по установке SQL Server.

Первый — это удаленная инсталляция, используя которую вы можете не переходить от компьютера к компьютеру, и при этом установить SQL Server на несколько серверов.

Второй способ — автоматическая инсталляция, при которой вы заранее создаете текстовый файл, содержащий параметры инсталляции и освобождающий вас от необходимости отвечать на вопросы программы setup.

ПРОБЛЕМЫ ПРИ УСТАНОВКЕ?

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

Где можно найти информацию о том, что случилось во время инсталляции:

1. Журнал регистрации событий Windows NT.

2. Журнал ошибок SQL Server (находится в каталоге '\SQL60\LOG\').

3.Выходные файлы инсталляционных скриптов. В директории '\SQL60\INSTALL\' вы найдете около 20 файлов с расширением '.SQL' (скрипты) и соответствующих им файлов с теми же именами и расширением '.OUT' (выходные файлы). В процессе инсталляции SQL Server выполняет скрипты (они же сценарии) и результаты выполнения записываются в выходные файлы. Просматривая выходные файлы, вы можете обнаружить сообщения об ошибках.

ОПТИМИЗАЦИЯ И НАСТРОЙКА MICROSOFT SQL SERVER

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

Ключи к производительности

• Структура базы данных;

• Пути доступа к данным;

• Аппаратура;

• Физическое распределение данных;

• Настройка параметров среды и SQL Server

Проектирование базы данных - фундамент производительности

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

Логическое проектирование базы данных

Оптимальное логическое проектирование баз данных базируется на применении трех основных методологий:

1. Моделирование данных;

2. Нормализация;

3. Разумная денормализация

Моделирование данных

Для моделирования данных традиционно применяется методология диаграмм "Сущность-Связь", которая позволяет построить законченную логическую модель данных, то есть представление в виде связанных таблиц. Существуют различные модификации этой методологии, как правило реализуемые фирмами-производителями CASE-инструментов в своих продуктах. Базовая методология построения диаграмм "Сущность-Связь" зафиксирована в стандарте IDEF1X. Некоторые CASE-инструменты основаны на методологиях, расширяющих возможности этого стандарта. К таким инструментам относится, в частности, S-Designor фирмы Powersoft.

Есть и другие методологии, в частности Объектно-Ролевое моделирование, которое позволяет описывать предметную область на более абстрактном уровне, чем моделирование "Сущность-Связь", по крайней мере базовый вариант последней. Объектно-Ролевое моделирование реализовано в CASE-инструменте InfoModeler фирмы Asymetrix. Применение S-Designor и InfoModeler рассмотрено в докладе "Проектирования структур баз данных с использованием CASE-инструментов S-Designor и InfoModeler".

Нормализация

В процессе построения логической модели осуществляется также нормализация, т.е. построенная модель удовлетворяет требованиям трех уровней нормализации:

1. Первая нормальная форма - отсутствие многозначных полей.

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

3. Третья нормальная форма - неключевое поле не должно зависеть от другого неключевого поля.

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

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

Денормализация

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

• Если спроектированная база данных требует связывания в одном запросе 4-х и более таблиц, стоит ввести избыточность, добавляя поля в таблицы или целые таблицы.

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

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

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

Противоречия логического проектирования

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

Приложения по обработке данных можно условно разделить на два класса:

• Системы оперативной обработки транзакций, характеризующиеся большой интенсивностью вставки и обновления записей.

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

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

Проектирование путей доступа

Когда создана структура базы данных, можно проектировать запросы, при помощи которых клиентские приложения будут манипулировать данными на сервере, осуществляя операции выборки, вставки, изменения и удаления данных. Каждый запрос характеризуется определенным путем доступа к данным. В понятие пути доступа входит:

• структура таблицы, к которой обращается запрос;

• поля, по которым происходит поиск;

• индексы, которые можно использовать для ускорения поиска;

• состав полей, которые обновляются в процессе выполнения запроса.

Цель проектирования оптимальных путей доступа - минимизация количества операций чтения/записи при выполнении клиентских запросов. Основа для этого должна быть заложена на этапе проектирования структуры базы данных.

Оптимизация путей доступа

Главный вопрос в оптимизации путей доступа - использование индексов. Если некий запрос выбирает строки в таблице по полю "field1", то при отсутствии индекса по этому полю сервер будет сканировать всю таблицу, что может быть очень "дорого" в терминах операций чтения. Если по полю "field1" построен индекс, то количество операций чтения может сократиться в несколько тысяч раз. Индексы существенны также при операциях соединения таблиц (JOIN) и операциях сортировки.

Какие еще моменты необходимо учитывать при проектировании путей доступа? Это

• обращение клиента к серверу через SQL-запрос или через вызов хранимой процедуры. Второй вариант работает немного быстрее, но необходимо учитывать один важный нюанс. План выполнения хранимой процедуры составляется при ее первом (после создания) вызове и затем хранится в кэше. Этот план оптимизируется для набора параметров и индексной статистики, имевших место именно при первом вызове. При дальнейших вызовах этот план может оказаться неоптимальным, то есть может потребоваться перекомпиляция процедуры, например, путем вызова с опцией "WITH RECOMPILE".

• проведение операций обновления "на месте" или путем удаления с последующей вставкой - обновление "на месте" проходит гораздо быстрее.

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

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

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

Аппаратура и производительность

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

Процессор

Процессор, как правило, достаточно интенсивно используется SQL Server'ом.

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

• Будет ли компьютер выделен для SQL Server?

• Сколько клиентов будут работать с сервером?

• Каково ожидаемое число транзакций в единицу времени?

• Велика ли доля агрегативных операций?

Количественно оценить загрузку процессора можно, проводя тестовые испытания и отслеживая параметры производительности при помощи Windows NT Performance Monitor.

Лучше, конечно, не скупиться на процессоре и ограничить свой выбор снизу хотя бы 486/50.

Память

Память используется SQL Server'ом очень интенсивно и многообразно. Память расходуется на кэширование данных и процедур, на поддержку подсоединений клиентов, открытых баз данных, открытых таблиц, блокировок таблиц и т.д. Из всех этих пунктов подробно остановиться имеет смысл на кэшировании. Все остальные расходы памяти при 50 одновременно работающих клиентах и достаточно большом количестве открытых объектов не превышают 3.5 Мб. Вся остальная память, доступная SQL Server, используется под кэш. Настраиваемый параметр "procedure cache" регулирует соотношение между кэшем данных и кэшем процедур. По умолчанию данные занимают 80% кэша. Приведенная ниже таблица содержит рекомендации по распределению памяти между SQL Server и остальной системой на выделенном компьютере. SQL Server использует память в количестве, отведенном ему настраиваемым параметром "memory".

 Не следует выделять SQL Server слишком много памяти (относительно общего объема памяти компьютера), т.к. это может привести к интенсивному вытеснению страниц оперативной памяти на диск ("paging"), что резко понижает производительность.

Диски

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

Быстрый интеллектуальный SCSI-2 контроллер

Кэш-память на контроллере

Bus Master card - процессор на плате снижает нагрузку на CPU

Поддержка асинхронного чтения и записи

32-битные EISA или MCA

Аппаратная поддержка RAID

Быстрые SCSI-2 диски

Кэширование с опережающим чтением

Минимальный рекомендуемый вариант дисковой подсистемы - SCSI-контроллер и два SCSI-диска - один для баз данных и другой для журнала транзакций.

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

Сеть

Так же как и с дисками, лучше иметь интеллектуальную сетевую карту, которая сэкономит процессорное время и расходы памяти. Вот некоторые рекомендации:

• 32-битные EISA или MCA •Bus Master card - процессор на плате снижает нагрузку на CPU;

• Кэш-память на адаптере

Физическое распределение данных

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

При планировании физического распределения данных следует учитывать следующие рекомендации:

• Распределение баз данных и журналов транзакций на разные физические устройства повышает производительность.

• Размещение большой таблицы и ее некластеризованного индекса на разных устройствах может повысить производительность.

• Распределение большой, активно используемой таблицы по нескольким устройствам может повысить производительность.

• Использование чередования данных в виде RAID 0 или RAID 5 повышает производительность.

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

Параметры среды и SQL Server

Настройка параметров среды и самого SQL Server - еще один ключ к производительности. Однако, он действительно может помочь лишь в том случае, если правильно подобраны ключи, описанные выше - структура базы данных, пути доступа, аппаратура и физическое распределение данных. Можно ожидать, что оптимальный подбор параметров среды и SQL Server даст прирост производительности на 5-10%.

Операционная система и производительность

Вот несколько советов по настройке Windows NT Server для повышения производительности SQL Server:

• Установка режима "Foreground and Background Applications Equally Response" (Control Panel - System - Tasking).

• Установка режима "Maximize Throughput for Network Applications" (Control Panel - Network - Server - Configure).

• Размещение файла подкачки "pagefile.sys" на физическом диске, не занятом данными SQL Server. Еще лучше, если эти диски обслуживаются разными контроллерами.

• Файловая система может быть любой (FAT или NTFS). Небольшой выгоды можно ожидать, если разместить слабо обновляемые базы данных на NTFS, а журнал транзакций - на FAT.

• Желательно отключить все ненужные сервисы Windows NT.

Параметры инсталляции и настройки SQL Server

Из всех параметров инсталляции SQL Server, которые трудно изменить в дальнейшем, на производительность влияет только порядок сортировки, о чем мы уже говорили при обсуждении инсталляции SQL Server. Выбор двоичного порядка сортировки может на 20-30% повысить производительность некоторых операций по сравнению с другими порядками, использующими словарный порядок символов.

Следующие параметры SQL Server могут повысить производительность:

• 'priority boost' (может понизить скорость выполнения других процессов на сервере). Чтобы иметь возможность задать этот параметр, следует сначала установить в 1 параметр 'show advanced option'

• 'memory' - задает размер памяти, доступной SQL Server. Чем больше, тем лучше, но в рекомендованных пределах (см. выше)

• 'user connections' - следует избегать неоправданного завышения этого параметра , т.к. это уменьшает объем кэша. Увеличение этого параметра на 1 "стоит" примерно •24 Кбайт памяти для кэша

• 'procedure cache' - процент кэша, отведенный по хранимые процедуры. При большом числе используемых хранимых процедур можно повысить.

• 'tempdb in RAM' - может существенно ( иногда в несколько раз) повысить скорость выполнения выборок, требующих сортировки или группирования строк.

Кроме того, определенное влияние на производительность могут иметь сетевые установки - выбор транспортных и сеансовых протоколов. Замечено, например, что работа по Named Pipes более всего эффективна над TCP/IP. Это связано с тем, что TCP/IP более эффективно наполняет передаваемые по сети кадры, что снижает общее число передаваемых кадров и, соответственно, повышает производительность.

Стратегия настройки

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

1. Мониторинг параметров производительности;

2. Предположение о причине низкой производительности;

3. Выбор параметра для изменения;

4. Изменение выбранного параметра;

5. Переход к шагу 1.

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

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

Мониторинг

Собирать информацию о параметрах, характеризующих производительность SQL Server удобнее всего с помощью Windows NT Performance Monitor, который позволяет отследить более 30 параметров работы SQL Server. Также важно наблюдать некоторые параметры Windows NT. Основными являются следующие:

• Processor: %Processor Time - преобладающее значение выше 80% говорит о том, что процессор является узким местом. Это может быть вызвано, в частности, неоптимальной структурой базы данных.

• Memory: Pages/sec - должен быть не выше 5-10. Этот параметр характеризует интенсивность вытеснения страниц оперативной памяти на диск (paging).

• SQLServer: Cache Hit Ratio - (процент нахождения требуемой страницы памяти в кэше, а не на диске); должен быть не ниже 80%

• SQLServer: I/O - Page Reads/sec - высокое значение этого параметра говорит о недостаточном размере кэша.

• Physical Disk: Disk Queue Length или Logical Disk: Disk Queue Length - значение выше 2 говорит о том, что узким местом является диск.

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

Кроме этого, могут помочь такие предложения языка Transact-SQL:

• DBCC MEMUSAGE - информация об использовании кэша данных и процедур

• SET SHOWPLAN ON - просмотр плана выполнения запроса, информация об использовании индексов

• SET STATISTICS TIME ON - показывает, сколько времени было затрачено на выполнение каждой стадии запроса

• SET STATISTICS IO ON - показывает, сколько операций логического и физического чтения было произведено над каждой таблицей при выполнении запроса.

Настройка. Память

Если параметр SQL Server: Cache Hit Ratio (процент попаданий в кэш), доступный для наблюдения через Windows NT Performance Monitor, по величине меньше 80%, то увеличение размера кэша должно повысить производительность. Оценить необходимый размер кэша для процедур и данных можно, выполняя самые часто встречающиеся запросы и хранимые процедуры и анализируя содержимое кэша при помощи предложения DBCC MEMUSAGE. Следует помнить, что хранимые процедуры в SQL Server не являются реентерабельными, т.е. если несколько клиентских процессов одновременно вызывают одну и ту же хранимую процедуру, то в кэше окажется несколько ее копий.

Если же параметр Memory: Pages/sec постоянно выше 5-10, то памяти не хватает системе в целом и нужно либо остановить какие-либо приложения или сервисы, или добавить памяти в компьютер.

Настройка. Запросы и индексы

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

Задав перед выполнением запроса опцию "SET SHOWPLAN ON", мы получим информацию об использовании индексов. В первоначальном варианте запроса индексы не используются. Построив индекс по полю, являющемуся аргументом поиска в запросе, мы существенно сокращаем количество операций чтения и, соответственно, время выполнения запроса.

Следует учесть, что оптимизатор запросов SQL Server делает вывод об использовании того или иного индекса при выполнении запроса на основании статистических данных о распределении значений ключей индекса. Эти статистические данные не обновляются при обновлении данных в таблице. Для обновления статистики можно или перестроить индекс, или использовать предложение "UPDATE STATISTICS".

СОПРОВОЖДЕНИЕ MICROSOFT SQL SERVER

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

1) работы, которые можно и должно планировать заранее - назовем их регламентными работами

2) обработка различных сбойных ситуаций, которые планировать невозможно, но можно все-таки быть к ним готовым.

Регламентные работы (Планировщик)

В Microsoft SQL Server 6.0 есть все возможности довести выполнение регламентных работ до такой степени автоматизации, что администратор может на них практически не тратить время - все будет выполнять специальный сервис, который называется Планировщик (Scheduling Engine). Вы можете давать ему задания, которые будут выполняться по расписанию, периодически или однократно в назначенное время и о выполнении которых вы можете узнавать, просматривая историю заданий или получая от Планировщика сообщения по электронной почте.

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

Резервное копирование

Базы данных необходимо периодически копировать - это объяснять не нужно. Копировать нужно как пользовательские базы данных, так и системные - в SQL Server 6.0 к ним относятся, помимо базы "master", еще и появившиеся в SQL Server 6.0, "msdb" и, для серверов-распространителей в процессе тиражирования, - "distribution".

Базу данных "master" следует копировать после таких действий, как создание новой базы данных, нового устройства или сегмента, заведения нового пользователя. Вполне разумно выполнять ее резервное копирование периодически (например, ежедневно), также как и копирование пользовательских баз данных или их журналов транзакций.

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

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

Обновление индексной статистики, как мы уже говорили, может существенно повлиять на производительность сервера. Поэтому его тоже имеет смысл делать регулярно и поручить Планировщику. Что мы сейчас и сделаем. Создадим задание типа 'TSQL' (выполнение предложения языка Transact-SQL), выполняющее в базе данных 'pubs' вызов хранимой процедуры 'update_all_stats', которая обновляет статистику по всем таблицам. Пусть статистика обновляется еженедельно, по воскресеньям в 3:00. Сообщения о неудачном завершении задания будут записываться в системный журнал, а также отправляться по электронной почте оператору.

Предвидение сбойных ситуаций

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

Так, например, если вы используете SQL Server в приложении, "жизненно важном" для деятельности вашей фирмы, то вам, скорее всего, необходимо будет делать резервное копирование баз данных минимум раз в день. Копировать данные можно на стриммер или на жесткий диск. Кроме того, следует, очевидно, иметь резервный компьютер с установленным на нем SQL Server и конфигурацией баз данных, полностью соответствующей основному серверу. Тогда в случае выхода из строя основного сервера можно будет с минимальными потерями времени перевести систему на обслуживание резервным сервером. Можно делать резервное копирование баз данных основного сервера на жесткий диск резервного сервера. Для этого нужно, чтобы сервис SQL Server регистрировался в Windows NT под именем пользователя, который имеет права на запись на этот диск.

Менеджер событий

Еще один компонент системы управления SQL Server - менеджер событий (Alert Manager), позволяет запланировать реакцию сервера на все возможные сбойные ситуации. События фиксируются в системном журнале, менеджер событий постоянно читает этот журнал и, при обнаружении заданного кода сообщения, выполняет запланированное администратором действие. Это действие оформляется в виде задания планировщику, аналогичного рассмотренному заданию на обновление статистики, только выполняется оно не по расписанию, а "по требованию". Конфигурируя реакцию на события, администратор указывает, какое задание выполнить в случае наступления этого события. Например, при переполнении журнала транзакций можно вызвать задание, которое осуществит резервное копирование журнала и тем самым очистит его.

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

Реакция на событие также предусматривает уведомление указанного списка операторов средствами электронной почты или пэйджинговой связи. Для того, чтобы SQL Server мог отправлять и принимать почту, работая с Microsoft Exchange, необходимо при конфигурировании SQL Mail указать в качестве имени пользователя имя профиля ("profile"), а в качестве пароля - сетевой пароль владельца профиля. При этом необходимо, чтобы SQL Server регистрировался в Windows NT не под именем "Local System", а под именем владельца вышеупомянутого профиля.

Анализ сбойной ситуации

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

• Клиентское приложение;

• Сеть;

• SQL Server

Если вы выяснили, какой запрос привел к сбойной ситуации, то изолировать клиентское приложение можно, послав этот же запрос к SQL Server через интерактивную утилиту ISQL/W. Если ошибка повторяется, значит клиентское приложение ни при чем. Послав этот же запрос, но не с рабочей станции, а непосредственно с компьютера, на котором работает SQL Server, можно выяснить, виновата ли сеть.

Анализ проблем с блокировками

Если вы предполагаете, что есть проблемы с блокировками, то удобно выяснить, так ли это, используя SQL Enterprise Manager и, конкретно, режим просмотра текущей активности ("current activity"). В этом режиме наглядно представлена информация о том, какой процесс блокирует другие процессы и из-за блокирования каких таблиц происходит конфликт. Вы имеете возможность перестроить запросы так, чтобы они не приводили к конфликтам и, в крайнем случае, "убить" нежелательный процесс.

РАБОТА ДОБАВЛЕНА В КОЛЛЕКЦИЮ: 23 СЕНТЯБРЯ 2001