Настройка SQL Server 6.5 на обработку запросов c высокой производительностью

Журнал "SQL Server Magazine ONLINE", #01/2000
Морис Льюис

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

Что оптимизировать?

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

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

В качестве примера рассмотрим простое объединение двух таблиц:

SELECT .....
......

Один из клиентов спросил автора статьи, почему этот запрос выполняется так долго. После того, как автор статьи запустил утилиту SHOWPLAN и взглянул на план запроса, ответ стал очевиден: таблица Payroll_Checks не имела индекса по столбцу empId. В таблице Employees содержалось около 10000 записей, а таблицу Payroll_Checks составляли 750 000 строк. Поскольку индекс отсутствовал, SQL Server сканировал таблицу Payroll_Checks 10 000 раз. Когда автор прервал выполнение запроса, сервер уже выполнил 15 миллионов логических операций ввода/вывода. Создание индекса по столбцу сократило время обработки до секунд, а число логических операций ввода/вывода до приблизительно 750000.

Магический размер строки

Первый шаг в минимизации операций ввода/вывода - убедиться в том, что строка сделана настолько компактной, насколько это возможно. В SQL Server строки не могут простираться на несколько страниц. В SQL Server 6.5 заголовок страницы не может превышать 32 байтов, а данные - занимать более 2016 байтов. Каждая строка данных содержит также область переполнения строки. Максимально допустимая длина отдельной строки составляет 1962 байта, включая область переполнения. Это ограничение выбрано с таким расчетом, чтобы вставляемая или удаляемая строка базы данных смогла бы также поместиться в строку журнала транзакций. Поэтому, хотя длина одной строки и не превысит 1962 байтов, две строки могут полностью занять все 2016 байтов, отведенных под данные на странице. Следствием этого является тот факт, что определенные значения длины строки могут существенно понизить процент операций ввода/вывода. Например, если длина строки составляет 1009 байтов, то на странице уместится только одна строка. Если же уменьшить длину строки всего только на один байт, то на страницу поместятся две строки. То есть, можно наполовину снизить обращения к вводу/выводу для таблицы, убрав всего один байт! Аналогичные ситуации имеют место для следующих размеров строк: 673, 505, 404 байтов и т.д. Если вам удастся сохранить размер строки ниже указанных пределов, то тем самым вы уменьшите долю операций ввода/вывода соответственно на 33, 25 и 20 процентов.

Все строки могут иметь несколько байтов переполнения, которые следует учитывать в расчетах. Отметим, что переполнение строк переменной длины больше, чем переполнение строк фиксированной длины. Чтобы выяснить, имеется ли у вас на страницах неиспользуемое место, запустите DBCC SHOWCONTIG. Это позволит вам определить среднюю плотность страницы и среднее число свободных байтов на странице. Наиболее вероятными кандидатами на звание "чемпиона по расточительству пространства" будут те таблицы, у которых среднее число свободных байтов близко к размеру строки.

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

Непредвиденный ввод/вывод при обновлениях

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

UPDATE ....
     9102 .....

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

UPDATE .....

Если au_id является первичным (или уникальным) ключом, то обновление первой строки может привести к нарушению ограничения уникальности, особенно если au_id - монотонно возрастающая величина. Но ведь оператор UPDATE корректен, так каким же образом SQL Server сможет выполнить его без нарушения ограничений? Здесь SQL Server прибегает к использованию режима отсроченного обновления, при котором операция обновления разбивается на две части: сначала удаляется старая строка, а затем вводится новая, содержащая требуемое значение.

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

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

SQL Server 6.5 способен выполнять операции обновления четырьмя различными способами. Самым быстрым является прямое обновление замещением. При этом не происходит никаких перемещений, а в журнал транзакций помещается единственная запись, содержащая информацию о том, какие байты получили новые значения. Самым медленным способом является отсроченное обновление, которое было описано выше. Оба других способа представляют собой прямые обновления (то есть никаких лишних записей в журнал транзакций не производится), но запись новых значений происходит не на то же самое место, на котором помещались обновляемые данные. Поэтому некоторые перемещения данных все-таки имеют место. Чтобы обновление, которое вы собираетесь сделать, проводилось в режиме прямого обновления замещением, должен быть исполнен такой длинный список условий, что в данной статье просто не представляется возможным все это изложить. В "SQL Server 6.5 Books Online" (BOL) есть раздел, называемый "Прямой режим обновления" (The update mode is direct). В нем перечислены некоторые условия, которые непременно должны выполнены, чтобы SQL Server произвел обновление прямым замещением. Однако в BOL иногда путаются прямое обновление и замещение, что приводит к некорректности некоторых рассуждений. Самым полным опубликованным описанием различных типов обновлений признана книга "Внутри SQL Server 6.5" (Inside SQL Server 6.5) Рона Саукапа, выпущенная в 1997 году издательством Microsoft Press.

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

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

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

Аргументы поиска

Простейший способ снизить объем ввода/вывода, необходимого для обработки запроса - это уменьшить количество строк, которые должен проанализировать SQL Server. Это делается с помощью задания выборочных критериев поиска в обороте WHERE, входящем в структуру запроса. Эти критерии обычно называются аргументами поиска. Они помогают оптимизатору запросов, давая подсказки относительно того, какой метод доступа к данным окажется самым быстрым. Аргументы поиска представляются в виде следующей записи:

Имя Столбца      оператор      [Имя Столбца или константа]

где оператором может быть один из следующих знаков сравнения =, <, >, <=, <=. Аргументы поиска могут быть соединены булевским оператором AND. Фраза BETWEEN ..... AND также допустима, поскольку задаваемое ею условие может быть по-другому сформулировано с помощью операторов >= и <=. Ниже приведено несколько примеров аргументов поиска:

LastName = .....
LastName  >= .....
OrderDate .....

Обратите внимание на то, что не был упомянут ни один отрицательный оператор. Для обработки запроса, содержащего оборот WHERE (ProductId <>2) SQL Server просматривает каждую строку, проверяя, не равно ли ее значение двум. Даже индекс по ProductId не так уж сильно облегчает ситуацию, если только строки, содержащие значение 2 не составляют весьма незначительную часть таблицы. Почти во всех случаях SQL Server выполнит этот запрос просмотром таблицы, а не индекса.

С точки зрения оптимизации запросов оператор LIKE почти столь же неэффективен, как и оператор NOT. Если в вашем запросе присутствует, например, такой оборот

WHERE  LastName LIKE '%Мс%',

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

WHERE  LastName LIKE 'Le%'.

Разница заключается в том, что этот критерий поиска логически эквивалентен выражению

WHERE LastName >= 'Le' AND LastName < 'LF',

которое по определению представляет собой аргумент поиска.

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

Селективность оборота отражает, насколько эффективно аргумент поиска сужает область просмотра. Этот показатель может быть измерен отношением числа возвращаемых строк к суммарному количеству строк в таблице. (Приведенное определение нарочно немного упрощено, для того чтобы сделать обсуждение более наглядным.) Низкий процент означает, что оборот обладает высокой селективностью; напротив, высокий процент соответствует слабой селективности. Поскольку оператор AND коммутативен (то есть, a AND b означает то же самое, что и b AND a), оптимизатор запросов может выбирать для обработки запроса наиболее селективный оборот из числа оборотов, объединенных оператором AND. Это оправдано, ведь выбор наиболее селективного оборота способен заметно снизить объем выполняемых операций ввода/вывода.

В качестве примера рассмотрим запрос

SELECT ....
     "213-46-8915"....

Оба оборота, составляющие оборот WHERE, являются аргументами поиска. Но столбец state (штат), скорее всего, не обеспечит получение единственного значения, а столбец au_id непременно гарантирует это, так как он является первичным ключом таблицы. Чтобы понять, что оборот au_id = "213-46- 8915" обладает очень высокой селективностью, а оборот state = "СА", наоборот, средней или даже низкой, вряд ли требуется знать что-либо еще. Конечно, если бы нашлась только одна строка, в столбце state которой было бы значение, соответствующее штату СА, то оба оборота были бы одинаково селективны.

Оптимизатор запросов решает, насколько селективен аргумент поиска, исходя из статистики соответствующего индекса. Статистика дает приблизительное представление о том, сколько записей будет удовлетворять заданному критерию. В таком случае, если оптимизатор запросов знает, сколько строк содержится в таблице, и сколько строк будет возвращено при использовании условий обеих частей оборота WHERE, то не составит труда решить, какой индекс целесообразно использовать. (Применение статистики в SQL Server 7.0 более подробно описано в статье Кэйлен Дилани "Статистика SQL Server : полезный инструмент оптимизатора запросов".) В рассматриваемом запросе, если имеются индексы и по столбцу state, и по столбцу au_id, то оптимизатор запросов выберет индекс по au_id. Если же индекс по au_id отсутствует, а по state создан, то оптимизатор запросов выберет его. Это вполне логично, поскольку в любом случае применение индекса более селективно, чем сканирование всей таблицы. При отсутствии обоих индексов единственным остающимся решением является сканирование таблицы для выявления всех строк, которые удовлетворяют условиям.

(Более подробно работа оптимизатора запросов изложена в книге "SQL Server 6.5 корпорации Microsoft " ("Microsoft SQL Server 6.5 unleashed"), выпущенной издательством в 1998 году. В ней рассмотрено несколько наиболее распространенных сценариев. Конечно, проработка каждого примера займет время, но зато вы станете гораздо лучше писать запросы, если разберетесь в том, каким образом действует оптимизатор. В книге "Внутри SQL Server 6.5" ("Inside SQL Server 6.5" ) также хорошо рассказано о работе оптимизатора запросов.)

Индексирование: скорее искусство, чем наука

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

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

А теперь очертим основы концепции правильного выбора типа индексов и столбцов, по которым они должны строиться. Прежде всего, поскольку для каждой таблицы можно создать только один кластеризованный индекс, его надо строить так, чтобы удовлетворить максимально возможное число запросов. Кластеризованные индексы более всего полезны для запросов, использующих условия на диапазон значений. Это обусловлено тем, что уровень листьев такого индекса содержит данные, отсортированные в порядке значений индекса. Наибольший выигрыш от применения кластеризованного индекса получается в тех случаях, когда оборот WHERE запроса содержит операторы >, < или BETWEEN .... AND, а также оборот GROUP BY, в которых столбцы перечислены в том же порядке, что и в индексе. Хотя это может и не помочь в поиске строк, но кластеризованный индекс способен улучшить производительность системы при обработке оборотов ORDER BY, если и в индексе и в обороте ORDER BY использованы одни и те же столбцы, причем в совпадающем порядке.

Поскольку промежуточный уровень кластеризованного индекса крайне мал, он прекрасно работает при поиске уникальных значений. Однако некластеризованные индексы лучше работают для "точечных" запросов, которые должны найти небольшое число строк. Обороты WHERE с оператором = являются первыми кандидатами на построение некластеризованных индексов по соответствующим столбцам. Этот тип индекса также очень хорош для функций агрегирования MIN и MAX, потому что легко найти первую и последнюю записи для диапазона значений, если воспользоваться уровнем листьев индекса. Наконец, некластеризованные индексы очень существенно ускоряют выполнение функции COUNT, так как сканирование уровня листьев индекса происходит намного быстрее сканирования таблицы.

Куда двигаться дальше?

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

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