Какую огромную разницу в скорость исполнения запроса может внести индекс! Недавно автор статьи получил еще одно наглядное подтверждение этой истины. Он ввел дополнительный индекс, и время обработки запроса уменьшилось с 40 минут до 12 секунд. Но индексы - всего лишь один из инструментов, применяемых администраторами баз данных для повышения производительности систем. Улучшить производительность можно настройкой самых разных параметров - от конфигурации технических средств до использования утилит баз данных. Ниже приведены 9 самых эффективных средств увеличения производительности SQL Server 6.5.

Чем большая часть базы данных сможет уместиться в кэше, тем быстрее будут обрабатываться запросы. Поэтому целесообразно увеличивать размер оперативной памяти пропорционально размеру базы данных. К примеру, если ваша база данных занимает 1 Гб, то оперативная память размером 1 Гб позволит разместить практически всю базу данных в памяти. Некоторую часть оперативной памяти следует оставить для Windows NT. Автор предпочитает оставлять для операционной системы от 64Мб до 128 Мб, а всю оставшуюся часть памяти отводить под SQL Server. И непременно надо сохранять объем доступной физической памяти NT не менее 4 Мб. Если он окажется ниже указанного предела, то NT немедленно начнет создавать страницы виртуальной памяти на диске.

Массивы RAID уровней 0 и 5 распределяют запросы на чтение по нескольким физическим дискам. Вы, наверняка, знаете, что творится на подступах к мостам в час пик, когда тысячи машин одновременно стремятся проехать через пространство ограниченной ширины. Такое же узкое место возникает и для запросов на чтение файлов с устройств вашей базы данных. Если вам удастся направить данные по нескольким каналам, то сервер сможет параллельно считывать блоки данных с каждого жесткого диска. При этом наблюдается почти линейное улучшение производительности. Такое увеличение пропускной способности для операций чтения обязано своим возникновением массивам RAID уровней 0 и 5. В качестве примера приведем цифры из книги Рона Саукапа «Внутри SQL Server 6.5», вышедшей в издательстве Microsoft Press в 1997 году. Он пишет, что один жесткий диск емкостью 4 Гб в состоянии обработать 80 - 90 операций ввода/вывода в секунду. В то же время массив RAID уровня 0 из 8 жестких дисков по 500 Мб каждый (то есть, обладающий такой же суммарной емкостью) пропускает 400 операций ввода/вывода в секунду. Конечно, при этом вопрос увеличения затрат остается открытым. Но в общем случае, чем больше жестких дисков в массиве, тем больше пропускная способность базы данных для операций чтения.

Возможно, ваша дисковая подсистема в состоянии обрабатывать свыше восьми асинхронных операций ввода/вывода в секунду, то есть больше величины, принятой в качестве значения по умолчанию более трех лет назад при выходе в свет версии SQL Server 6.5. Для оптимизации этого параметра следует увеличивать его небольшими шагами, наблюдая при этом за значением счетчика средней длины очереди к дискам, AvgDiskQueueLength, в мониторе производительности NT (NT Performance Monitor). До тех пор, пока средняя очередь к дисковой подсистеме не превышает удвоенного количества дисков в ней, можно считать, что вы ее не перегружаете.

Три параметра расширения блокировок на всю таблицу (LE - Lock Escalation): Максимальный порог (LE Threshold Maximum), Минимальный порог (LE Threshold Minimum) и Пороговый процент (LE Threshold Percent), определяют, сколько страниц должен заблокировать SQL Server, прежде чем будет заблокирована вся таблица целиком. По умолчанию для этих параметров приняты значения соответственно 200, 20 и 0. Для очень больших таблиц блокировка всей таблицы позволяет избежать накладных расходов, связанных с тысячами блокировок. Если в базе данных содержатся сотни таблиц, то устранение таких накладных расходов может оказать существенное влияние на производительность.

В силу того, что кластеризованные индексы упорядочивают данные физически, располагая их в порядке следования значений в индексе, такие индексы представляют собой прекрасную основу для запросов, которые ищут диапазоны значений. Например, если у таблицы, содержащей отдельные записи (строчки) счетов, имеется кластеризованный индекс по столбцу с идентификатором счета, то все строчки, относящиеся к счету 0001, будут находиться в самом начале таблицы, а строчки счета 9999 расположатся в самом ее конце. Такой порядок означает, что если эта таблица будет соответствовать части «многие» отношения «один - ко - многим» при соединении с другой таблицей, то SQL Server сможет легко найти первую строку с нужным значением идентификатора счета, а затем последовательно двигаться по таблице до тех пор, пока значение идентификатора не изменится.

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

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

При соединении двух таблиц SQL Server ищет во внутренней таблице все строки, значения которых удовлетворяют условию, вычисляемому на основании текущего значения из внешней таблицы. И такой поиск SQL Server повторяет для каждой строки из внешней таблицы. Если имеется индекс, то SQL Server сможет сначала отобрать только те строки, которые отвечают условию соединения. Когда размер внутренней таблицы в несколько раз больше размера внешней, выигрыш во времени выполнения соединения может составить несколько порядков. (Более подробно о соединении таблиц написано в статье Ицыка Бен-Гана и Кэйлен Дилани «Усовершенствованная техника соединения таблиц» («Advanced JOIN Techniques»), опубликованной в декабрьском номере журнала за 1999 год.) Какой индекс выбрать - кластеризованный или не кластеризованный - в основном, зависит от того, присутствуют ли в списке SELECT другие столбцы. Если в список входят только те столбцы, по которым производится соединение, лучше всего применять не кластеризованный индекс.

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

Морис Льюис (morris@holitech.com) является президентом компании Holitech, специализирующейся на консалтинге и обучении технологиям Internet и разработкам корпорации Microsoft в области баз данных.