Сегодня я хочу предложить читателям 10 рекомендаций по настройке, которые помогут увеличить производительность SQL Server на 90%.
Оптимизировать производительность Microsoft SQL Server 2000 и SQL Server 7.0 не так-то просто, но в большинстве случаев администратор может добиться неплохих результатов, приложив совсем немного усилий. Здесь действует старое правило 90:10, т. е. 90% успеха достигается после затраты всего 10% усилий. Но нельзя забывать, что повысить производительность еще на 10% удастся, лишь затратив остальные 90% усилий при настройке.
Правило 90:10 неприменимо в отношении любых баз данных и даже ранних версий SQL Server. Для того чтобы добиться приемлемой производительности некоторых продуктов, необходимо настроить десятки – а то и сотни – параметров сервера и другие многочисленные функции SQL. В отличие от названных продуктов, SQL Server 2000 и SQL Server 7.0 – самонастраивающиеся системы, которые имеют неплохую производительность при работе с параметрами, принятыми по умолчанию.
Чтобы поднять производительность выше среднего стандартного уровня, потребуется уделить SQL Server совсем немного внимания.
1. О важности аппаратных средств
Обычно я не ставлю совершенствование аппаратных средств на первое место в списке рекомендаций по настройке, поскольку решить проблемы производительности исключительно за счет наращивания аппаратуры невозможно. Однако для увеличения быстродействия SQL Server следует в первую очередь обратиться именно к аппаратным средствам.
Если продукт установлен на хорошей машине, то модернизация аппаратных средств редко позволяет повысить производительность более чем на 10%. Но если приложение на базе SQL Server работает на сервере, к которому одновременно обращаются несколько сот пользователей, а сервер оснащен лишь одним жестким диском и минимальным объемом оперативной памяти, 64 Мбайт, то простое расширение оперативной памяти до 128 Мбайт приведет к резкому повышению быстродействия.
В идеальном случае, для каждых 10 одновременных соединений следует увеличивать оперативную память на 10 Мбайт; кроме того, необходима дополнительная память для хранения всех пользовательских данных, системных данных и индексов. Рекомендуется выбрать дисковую конфигурацию, которая позволит сохранить пользовательские данные (файлы .mdf и .ndf) и журналы (файлы .ldf) на других физических дисках, управляемых отдельными контроллерами. Файлы пользователей нужно хранить на RAID-массиве. Следует также потратиться на два самых быстрых процессора, какие только доступны для компании. Это минимальные требования к аппаратным средствам.
2. Не увлекайтесь настройками
Разработчики Microsoft заложили в SQL Server 2000 и SQL Server 7.0 способность к самонастройке. Например, механизм SQL Server может определить оптимальный режим использования памяти, допустимое число блокировок и частоту контрольных точек.
Изменять следует лишь те принимаемые по умолчанию параметры, которые не влияют на производительность. К таким параметрам относятся функции пользователя и функция преобразования года в двухсимвольный формат (битовая карта функций пользователя указывает, какие функции активизированы для каждого пользовательского соединения; функция преобразования года в двухсимвольный формат задает метод интерпретации двузначного обозначения года).
Возможности настройки параметров, влияющих на производительность, предусмотрены для тех редких случаев, когда изменения необходимы. Однако лучше воспользоваться автоматической настройкой конфигурации SQL Server.
Исключение составляет параметр max async I/O. При работе с SQL Server 7.0 он настраивается в зависимости от уровня сложности и числа контроллеров в подсистеме ввода/вывода. Значение max async I/O определяет максимальное число ожидающих обработки асинхронных запросов ввода/вывода, которые могут быть направлены сервером к любому файлу. Если база данных охватывает несколько файлов, то параметр применяется к каждому из них.
По умолчанию значение max async I/O составляет 32 (всего 32 операции записи и 32 операции чтения могут ожидать обработки для каждого файла), оно оптимально для многих систем. Чтобы выяснить, нужно ли изменить стандартное значение для данной системы, следует заглянуть в SQL Server Books Online (BOL). SQL Server 2000 не имеет параметра max async I/O и определяет оптимальное значение автоматически.
3. Уделите время проектированию
Всеми силами стараясь сократить время начальной реализации проекта, легко принести в жертву качество реляционных баз данных. Если поддаться соблазну, пострадает производительность. Исправлять недостатки, заложенные на стадии проектирования, очень трудно, так как для этого требуется вносить серьезные изменения в уже протестированный исходный текст.
Чтобы хорошо спроектировать базу данных, следует начать с создания нормализованной модели, по крайней мере, в третьей нормальной форме. В результате до минимума снижается избыточность и сокращается общий объем данных. Затем можно систематически денормализировать проект, документируя каждое отступление от нормализованной формы.
Денормализованная структура отличается от ненормализованной. В денормализованную структуру избыточность вносится ради улучшения конкретных рабочих характеристик. Например, если необходимо регулярно отыскивать невыполненные заказы по имени потребителя, и в одной таблице хранятся ID потребителя и его имя, а в другой таблице – ID потребителя и информация о состоянии заказа, то SQL Server должен объединить эти таблицы, чтобы извлечь имена потребителей с невыполненными заказами. В некоторых случаях операции объединения могут отнимать много времени. В денормализованной модели имя потребителя можно добавить в таблицу, содержащую информацию о состоянии заказа и ID потребителя, тогда объединять таблицы не потребуется.
4. Формируйте индексы
Чтобы отыскать нужные данные при отсутствии индексов, необходимо вести поиск в каждой строке таблицы. Если группируются и объединяются данные из нескольких таблиц, то, вероятно, в поисках результатов понадобится даже несколько раз пройтись по таблицам. Чтение с диска является одной из самых длительных операций в SQL Server, поэтому снижение числа обращений к диску позволит существенно поднять производительность.
Создав полезные индексы, можно повысить производительность на несколько порядков вместо нескольких процентов. Например, в отсутствие индекса SQL Server придется прочитать все 10 000 страниц таблицы. Если благодаря индексации число прочитанных страниц уменьшится до 10, то это будет равносильно увеличению скорости обработки страниц на 100 000%.
Знание архитектуры индексов и методов оптимизации запросов SQL Server поможет сформировать оптимальные индексы, а начинающие администраторы могут воспользоваться для этого мастером Index Tuning Wizard. Чтобы открыть его из SQL Server Enterprise Manager, следует щелкнуть на кнопке Wizard панели инструментов и заглянуть в раздел Management Wizards.
Прежде чем будет создан набор рекомендаций по индексации, необходимо определить, каким образом будет осуществляться доступ к данным. Удобнее всего получать эту информацию с помощью SQL Server Profiler. В течение нескольких часов пиковой нагрузки следует перехватывать пакеты команд SQL, посылаемые SQL-серверу из клиентских приложений. На основании этой информации можно выяснить, каким образом клиентские программы обращаются к таблицам.
5. Эффективно используйте SQL
SQL – язык, ориентированный на обработку наборов, а не отдельных строк. T-SQL, предложенный Micro-soft диалект языка SQL, использует серверные курсоры для обращения к одной строке за один раз; однако большинство решений, в которых применяются серверные курсоры, будут на несколько порядков медленнее, чем решения, в которых для выполнения тех же задач используются предложения SELECT и UPDATE. Применение таких функций языка, как подчиненные запросы, производные таблицы и выражения CASE для манипулирования наборами строк, ускорит подготовку решений и поможет добиться максимальной производительности SQL Server.
Предположим, что каждому продукту на складе соответствует строка в таблице, а в строки другой таблицы записываются данные о продажах этого продукта. Требуется денормализировать базу данных и сохранить сумму продаж каждого продукта в складской таблице. Чтобы получить суммы, можно, используя курсор, пройтись по таблице продуктов строка за строкой. Затем отыскать для каждой строки все соответствующие ей строки в таблице продаж, сложить величины и записать сумму в складскую таблицу. В данном примере использование серверного курсора для вычисления общих продаж возможно, но крайне неэффективно.
Эту задачу можно решить и с помощью предложения UPDATE и коррелированного подчиненного запроса. В данном предложении таблица titles базы данных pubs используется в качестве таблицы продуктов; для каждого наименования суммируются величины в поле qty таблицы sales.
UPDATE titles
SET ytd_sales =
(Select sum(qty) FROM sales
WHERE title_id = titles
.title_id)
6. Изучайте тонкости T-SQL
Microsoft T-SQL – усовершенствованная версия стандартного языка ANSI-SQL. Использование его возможностей позволяет существенно повысить производительность системы.
Например, предположим, что необходимо выставить все продукты на продажу, установив цену на них в зависимости от прошлогоднего объема продаж. Цена должна быть на 25% ниже текущей, если число проданных единиц меньше 3000; если объем продаж был от 3000 до 1000 единиц, то цена продукта должна быть снижена на 20%; скидка 10% предоставляется на продукты, объем продаж которых превысил 10 000 единиц. Очевидное решение – использовать предложение UPDATE с соответствующими значениями скидок после индивидуального просмотра строк продуктов с помощью курсора. Однако выражение T-SQL CASE позволяет вычислить соответствующие скидки с помощью одного оператора.
В приведенном ниже примере предложение UPDATE использует таблицу titles базы данных pubs, в которой есть поле цены, обновляемое предложением, и поле ytd_sales, где хранится информация о продажах за прошлый год. Этот запрос не будет работать, если предварительно была выполнена операция из рекомендации 5; в поле ytd_sales будет находиться набор других величин.
UPDATE titles
SET price = CASE
WHEN ytd_sales < 3000 THEN
price * 0.75
WHEN ytd_sales between 3000
and 10000 THEN price * 0.80
WHEN ytd_sales > 10000 THEN
price * 0.90
END
WHERE price IS NOT NULL
Другие элементы T-SQL, повышающие эффективность запросов, – оператор TOP, используемый вместе с ORDER BY; индексированные представления (только SQL Server 2000); разделенные (partitioned) представления.
7. Правильно применяйте блокировки
Проблемы с блокировками часто приводят к снижению производительности. Не рекомендуется навязывать SQL Server свои способы блокировки данных. Лучше изучить механизмы блокировки данных, обычно используемые SQL Server. Это позволит создавать приложения, не вступающие в конфликт с SQL Server.
Например, после того как приложение изменило данные в ходе транзакции, SQL Server блокирует эти данные и запрещает доступ к ним со стороны других процессов до тех пор, пока транзакция не будет завершена или отменена. Если команда завершения или отмены долго не поступает, данные будут блокированы в течение длительного времени. Поэтому транзакции рекомендуется делать покороче. Во время обработки транзакции желательно также запретить любое взаимодействие с пользователями.
По умолчанию SQL Server сохраняет монопольные блокировки – устанавливаемые при вставке, обновлении и удалении данных – до конца транзакции. Блокировки, допускающие чтение, – устанавливаемые при выборе данных, – действуют только до тех пор, пока не завершено чтение выбранных данных.
Уровень изоляции транзакций может быть изменен, и действие блокировок, допускающих чтение, может быть продлено до конца транзакции – это значит, что никто не может изменить данные после считывания. Таким образом, метод изменения уровней изоляции хорош в том случае, если нужно зарезервировать данные только для личного пользования. Однако его не стоит применять в многопользовательских системах. Я рекомендую установить уровень изоляции транзакций Committed Read (принимаемый по умолчанию) и изменять его только в случае крайней необходимости.
8. Сократите число перекомпиляций
В первой версии SQL Server можно было сохранить и повторно использовать план исполнения хранимой процедуры, многократно вызываемой приложением. Однако до появления SQL Server 7.0 данная возможность не давала очевидных преимуществ. Во многих случаях затраты на составление плана методом компиляции (компиляция предусматривает также оптимизацию запроса) были настолько малы, что экономить на этом не стоило.
Разработчики Microsoft дополнили оптимизатор запросов SQL Server 7.0 десятками новых методов обработки запросов. Благодаря новым возможностям, оптимизатор запросов уделяет больше времени составлению плана исполнения, чем в предыдущих версиях базы данных. Поэтому увеличивается ценность функций повторного использования плана как средства экономии времени.
В SQL Server 2000 и SQL Server 7.0 имеется механизм сохранения планов выполнения специализированных запросов, который может пригодиться при отсутствии хранимой процедуры. Он вводится в действие автоматически, но помогает не всегда. Дело в том, что данный механизм работает в соответствии со своим набором правил, и применять его сложнее, чем повторно используемые планы хранимых процедур. Поэтому рекомендуется составить хранимые процедуры для всех запросов SQL, где это возможно.
Заранее подготовленные планы позволяют сэкономить время, но иногда стоит провести перекомпиляцию. В некоторых случаях SQL Server перекомпилирует план для процедуры самостоятельно. Profiler сообщит, когда происходили перекомпиляции, а с помощью System Monitor можно определить частоту перекомпиляций.
9. Грамотно программируйте приложения
Чем более глубокими знаниями об устройстве SQL Server обладает автор клиентских программ, тем выше качество составленных им исходных текстов. Например, он не допустит взаимодействия с пользователем посреди транзакции (см. п. 7).
Еще один неудачный программный прием заключается в том, что клиентское приложение инициирует транзакцию, посылает команду обновления в SQL Server, а затем выводит на экран сообщение с вопросом о том, следует ли продолжить транзакцию. В этом случае SQL Server сохраняет все установленные блокировки до тех пор, пока пользователь – который мог уйти на обеденный перерыв или вообще покинуть офис на целый день – не вернется и не щелкнет OK.
В п. 5 я предостерегаю читателей от использования серверных курсоров. Но клиентские курсоры – совсем другое дело. Построчная обработка клиентской программой набора результатов, которые были сгенерированы SQL Server с использованием операции, ориентированной на наборы данных – приемлемое решение. Однако необходимо изучить документацию по API, чтобы применять разнообразные клиентские курсоры максимально эффективно.
Один из видов клиентского курсора, Fast Forward-Only, предназначен для последовательного извлечения данных при одноразовом считывании. С помощью этого курсора можно исключить два обращения к серверу; SQL Server выдает первую строку, когда курсор открывается, а когда SQL Server выдает последнюю строку, курсор закрывается. Даже если считывается всего несколько строк, при частом использовании программного фрагмента, содержащего курсор Fast Forward-Only, исключение двух обращений уже позволит повысить производительность.
10. Повышайте свою квалификацию
Если приведенные выше советы не помогли в решении конкретных проблем, рекомендую обратиться к многочисленным общедоступным источникам, которыми пользуются опытные специалисты по SQL Server. В частности, посмотрите конференции Micro-soft. Отыскать на сервере msnews.microsoft.com конференции, в названиях которых содержится обозначение sqlserver, можно с помощью любой программы чтения новостей (например, Microsoft Outlook Express).
Эти рекомендации – всего лишь вершина айсберга, которая открывает не более 10% всех возможностей. Теперь, когда известно, какие параметры SQL Server можно настроить, предстоит разобраться в том, как это сделать. Пройдет немного времени, и, вероятно, у администратора возникнет желание приложить те 90% усилий, которые необходимы, чтобы выжать из продукта последние 10% производительности.
Кэлен Дилани – независимый консультант и инструктор по SQL Server. Имеет сертификаты MCT и MCSE. Автор книги «Inside SQL Server 2000» (Microsoft Press). С ней можно связаться по адресу: kalen@sqlmag.com.