SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Первый взгляд на затраты плана выполнения в Yukon Beta 1

ПУБЛИКАЦИИ  

По материалам статьи Joe Chang A First Look at Execution Plan Costs in Yukon Beta 1
Перевод Виталия Степаненко

Многих из нас интересуют изменения в Yukon и его особенности. С первого взгляда можно заметить некоторые отличия в формулах затрат плана выполнения в Yukon Beta 1. Скорее всего, еще больше изменений будет сделано между Beta 1 и финальной версией.

Большинство формул затрат не изменились. Это формулы для поиска по индексу, сканирования таблицы, loop, hash и merge join'ов. Изменился Bookmark lookup. Bookmark lookup часто следует за использованием некластерного индекса для нахождения набора строк, где требуется больше информации о таблице, чем находится в индексе. Например, TableA имеет некластерный индекс по столбцу Col1. Для запроса SELECT * FROM TableA WHERE Col1 = x план выполнения может состоять из поиска по индексу столбца Co1l, сопровождаемого bookmark lookup по таблице для получения оставшейся информации по столбцам таблицы для нужных строк. В SQL Server версий 7.0 и 2000 план выполнения выглядел бы так, как показано на рис.1. В Yukon Beta 1 план выполнения выглядит так, как показано на рис.2 и 3.

Рис.1. План выполнения с Bookmark Lookup в SQL Server 7.0 и 2000.

Рис.2. План выполнения в Yukon Beta 1 для "RID" Lookup.

Рис.3. План выполнения в Yukon Beta 1 для поиска по некластерному индексу.

Это изменение в графическом представлении bookmark lookup на самом деле не изменяет порядок выполнения самой операции, но показывает более целостную картину выполнения операции. Во-первых, существует разница между bookmark lookup по таблице с кластерным индексом и без кластерного индекса. SQL Server 7.0 и 2000 не показывают этого, а Yukon эту разницу показывает. В таблице без кластерного индекса некластерный индекс имеет строку, страницу и информацию о файле для прямого указания на страницу со строкой. В таблице с кластерным индексом некластерный индекс имеет значение ключа кластерного индекса. В этом случае bookmark lookup выполняется совместно с операцией поиска по индексу.

Во-вторых, есть некоторое сходство между операциями bookmark lookup и loop join, поэтому изменение графического представления позволяет легче распознать операцию. Однако минусом является то, что операция отображается в более сложном виде, чем выводимый раньше простой символ операции bookmark lookup. В графическом представлении плана выполнения иногда лучше упростить части этого плана, которые уже поняты или неинтересны (относительно других более интересных частей плана выполнения).

В дополнение к изменению в графическом представлении плана выполнения для операции bookmark lookup, формулы расчета затрат для этой операции также изменились. В SQL Server 7.0 и 2000 операция bookmark lookup имела один набор формул расчета затрат, а операция loop join - другой набор таких формул. В Yukon Beta 1 старые формулы расчета затрат операции bookmark lookup были удалены и теперь существует только один набор формул расчета затрат для операций bookmark lookup и loop join. Набор формул расчета затрат тот же, что и старый набор формул расчета затрат версий 7.0 и 2000. Хотя это и более целостное представление операции, при этом есть положительные и отрицательные последствия.

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

Затраты ввода-вывода =
0.006328500 + 0.000740741 на каждую дополнительную страницу (до 1 GB)
0.003203425 + 0.000740741 на каждую дополнительную страницу (более 1 GB)

Загрузка процессора = 0.000079600 + 0.000001100 на каждую дополнительную строку

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

Старые формулы расчета затрат в SQL Server версий 7.0 и 2000 для операций bookmark lookup были следующими:

Оценочные затраты ввода-вывода =
множество значений 0.006250000 (до 1 GB)
множество значений 0.003124925 (более 1 GB)

Оценочная загрузка процессора = 0.0000011 на каждую строку

Множество значений для оценочных затрат ввода-вывода не равно оценочному количеству строк, а является близким значением этого количества. Рис.4 показывает пример множества значений старой операции bookmark lookup.

Рис.4. Множество значений затрат ввода-вывода старой операции Bookmark Lookup как процент от количества строк.

Формулы расчета затрат на сканирование таблицы в SQL Server 7.0, 2000 и Yukon Beta следующие:

Затраты ввода-вывода = 0.0375785 + 0.000740741 на каждую дополнительную страницу

Загрузка процессора = 0.0000785 + 0.0000011 на каждую строку

Для формул расчета затрат в SQL Server 7.0 и 2000 подразумевается, что затраты плана выполнения с bookmark lookup в 7 раз больше для одной строки, чем затраты на сканирование каждой страницы при сканировании таблицы для систем с объемом памяти до 1 гигабайта включительно, и в 3.5 раза больше для систем с объемом памяти больше 1 гигабайта.

Сейчас можно спросить, почему старые затраты на операцию bookmark lookup для кажой дополнительной строки гораздо выше, чем затраты на страницу для сканирования таблицы. Вспомним, что в начале 1990-х годов память была очень дорогой по сравнению с настоящим временем. 16-мегабайтный модуль памяти стоил около 600 долларов. Обоснованным предположением в то время было то, что обычный сервер баз данных может иметь объем памяти, достаточный только для кэширования небольшой части общего объема данных. Т.е. скорее всего получение каждой строки в операции bookmark lookup будет сопровождаться дисковыми операциями ввода-вывода. Более того, для операции bookmark lookup каждая последующая строка в запросе, возможно, не является последующей строкой в таблице, поэтому выборка каждой строки может приводить к чтению данных из разных страниц. С другой стороны, сканирование таблицы потенциально может читать целые экстенты (8 страниц) за одну операцию ввода-вывода. Возможно, что затраты на физический дисковый ввод-вывод больше зависят от количества отдельных операций ввода-вывода, чем от количества данных. Если так, то это одно из возможных объяснений больших затрат на строку при выполнении операции bookmark lookup относительно затрат на страницу при сканировании таблицы.

Это объяснение является полностью умозрительным. Однако, если считать его верным, то также можно предположить, что формулы расчета затрат зависят от того, какой объем нужных данных находится в кэше буфера. В этом случае формула расчета затрат должна зависеть от индикатора, отражающего наличие данных в кэше, такого, как коэффициент заполнения кэша буфера (buffer cache hit ratio), чем от объема памяти в системе. Сервер с 512 мегабайтами памяти и базой данных в 256 мегабайт скорее всего имеет больший коэффициент заполнения кэша буфера, чем сервер с 4 гигабайтами памяти и базой данных в 100 гигабайт. Так что вполне понятно, что абсолютный объем памяти сам по себе - это плохой индикатор для использования того или иного варианта формулы расчета затрат.

Формулы расчета затрат на операцию bookmark lookup, использовавшиеся в SQL Server 7.0 и 2000, были заменены старыми формулами расчета затрат на операцию loop join, которые сейчас используются и для операции bookmark lookup, и для операции loop join. Но формулы расчета затрат для операции loop join сами могут вести себя необычно. Компонент loop join сам по себе - это относительно простая формула затрат процессора в 0.00000418 на каждую строку. Сложность содержится в операции поиска по индексу для внутреннего источника данных (inner source, IS). Во-первых, существуют как минимум 3 разных варианта формулы расчета затрат операции loop join для внутреннего источника данных, как показано на рис.5.

Рис.5. Известные варианты формулы расчета затрат операции loop join.

Первый вариант относится к обычным ситуациям, когда поисковый аргумент определен на обеих таблицах в объединении и где индекс на внутреннем источнике данных предваряется поисковым аргументом внутреннего источника данных и сопровождается условием объединения с результатом, при котором данные из внутреннего источника данных содержатся в небольшом количестве страниц. Второй вариант наблюдается, когда поисковый аргумент определен только на таблице внешнего источника данных (outer source, OS), а таблица внутреннего источника данных имеет индекс, используемый в условии объединения. Третий вариант наблюдается в тех случаях, когда поисковый аргумент определен на обеих таблицах, как в первом варианте, но внутренний код не ограничивается небольшим количеством страниц. Во всех трех вариантах затраты на строку для всей операции loop join составляют примерно 0.00015 для первых 132 строк. При количестве строк, большем 132, второй и третий варианты показывают очень резкий скачок затрат плана выполнения, более чем в 30 раз при переходе от 132 к 133 строкам. Нет никакой причины для превышения затрат операции loop join с 133 строками в 30 раз над затратами операции loop join с 132 строками на тех же таблицах. Первый вариант не показывает необычный скачок затрат при переходе от 132 к 133 строкам, а продолжает показывать затраты в 0.00015 на строку. Есть еще один известный вариант, когда таблица внутреннего источника данных маленькая и затраты в этом случае равны 0.0000796 на строку.

Для операций bookmark lookup используемая формула расчета затрат является вторым вариантом, т.к. нет явного поискового аргумента на таблице внутреннего источника данных и эта таблица не может быть маленькой, если обрабатывается множество строк. Итоговым результатом изменений при переходе от SQL Server 7.0 и 2000 к Yukon состоит в том, что затраты на операцию bookmark lookup изменились. Если раньше они были в несколько раз больше, чем затраты на страницу при сканировании таблицы, то теперь они в несколько раз меньше, кроме случаев, когда количество строк превышает 132 строки.

Таблица ниже суммирует примерные затраты плана выполнения и действительные затраты на каждую строку для операций bookmark lookup и loop join, и затраты на страницу для сканирования таблицы (не в режиме rowlock). Затраты плана выполнения для операций сканирования таблицы и loop join относятся к SQL Server 7.0, 2000 и Yukon Beta 1. Затраты плана выполнения для операции bookmark lookup относятся к SQL Server 7.0 и 2000. Затраты плана выполнения для операции bookmark lookup для Yukon Beta 1 - те же, что и показанные ниже затраты для операции loop join для 132 строк включительно. Для 133 или более строк затраты для операции loop join на строку намного выше. Показанные ниже затраты относятся к SQL Server 2000. В Yukon измерение этих затрат пока не производилось.

Операция

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

Действительные
затраты
Pentium III

Действительные
затраты
Xeon

Bookmark – таблица
без кластерного индекса
(старые формулы)

~0.00625 ~11K ~13K

Bookmark – таблица
с кластерным индексом
(старые формулы)

~0.00625 ~15K ~21K

Loop Join
(до 132 строк)

~0.00015 ~17K ~24K

Сканирование таблицы
(на страницу)

~0.0007407 ~24K ~24K

Затраты плана выполнения выводятся в неопределенных единицах измерения. Действительные затраты измеряются циклами процессора, вернее, единицами времени, относящимися к частоте процессора (CPU clock). Для компьютеров на базе Pentium III частота процессора составляет от 600 до 733 мегагерц. Для компьютеров на базе Xeon частота процессора составляет от 2.0 до 2.4 гигагерц.

Действительные затраты на строку для операций bookmark lookup и loop join и затраты на страницу для сканирования таблицы не сильно различаются. Это больше различия между процессорами Pentium III и Xeon. Затраты на операцию bookmark lookup относительно ниже для таблиц без кластерного индекса, чем для таблиц с кластерным индексом.

[В начало]

Заключение

Главным изменением при переходе от SQL Server 7.0 и 2000 к Yukon Beta 1 является то, что затраты плана выполнения с операциями поиска по индексу и bookmark lookup, которые были сильно недооценены, сейчас, возможно, переоценены, кроме случая, когда обрабатываются более 132 строк. Объединение операции bookmark lookup с операцией loop join - изменение в принципе правильное и позитивное. Однако операция loop join нуждается в новой калибровке. Для большей аккуратности несколько разные формулы должны использоваться для RID таблиц (без кластерного индекса) и таблиц с кластерным индексом. Хотя эта разница не очень большая, но если графическое представление плана выполнения пытается показывать разные операции, то, естественно, формулы расчета затрат могут также использовать наиболее подходящие значения. Также желательно, чтобы формулы расчета затрат плана выполнения учитывали разницу в архитектуре процессоров. Также было бы желательно, чтобы формулы расчета затрат основывались на коэффициенте заполнения кэша буфера (buffer cache hit ratio), а не на абсолютном объеме памяти. Есть надежда, что в релизе Yukon'а будут сделаны по крайней мере некоторые необходимые улучшения в формулах расчета затрат плана выполнения.

[В начало]

Перевод: Виталия Степаненко  2004г.

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013