Random Prefetching

Автор: Craig Freedman Random Prefetching

В предыдущей статье мы рассмотрели важность для повышения производительности асинхронного ввода-вывода и последовательного упреждающего чтения (sequential read ahead). В этой статье мы рассмотрим, как SQL Server использует случайную упреждающую (random prefetching) выборку. Давайте начнем с простого примера плана запроса, в котором много случайных операций ввода-вывода. Как и в предыдущей статье, все примеры используют базу данных TPC-H с коэффициентом масштабирования 1ГБ. Следующий запрос возвращает количество позиций, связанных с заказами, размещенным 15 марта 1998г.

SELECT O_ORDERKEY, COUNT(*)

FROM ORDERS

JOIN LINEITEM ON O_ORDERKEY = L_ORDERKEY

WHERE O_ORDERDATE = '1998-03-15'

GROUP BY O_ORDERKEY

  |--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1012],0)))
       |--Stream Aggregate(GROUP BY:([ORDERS].[O_ORDERKEY]) DEFINE:([Expr1012]=Count(*)))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([ORDERS].[O_ORDERKEY], [Expr1011]) OPTIMIZED WITH UNORDERED PREFETCH)
                 |--Clustered Index Seek(OBJECT:([ORDERS].[O_ORDERDATE_CLUIDX]), SEEK:([ORDERS].[O_ORDERDATE]='1998-03-15') ORDERED FORWARD)
                 |--Index Seek(OBJECT:([LINEITEM].[L_ORDERKEY_IDX]), SEEK:([LINEITEM].[L_ORDERKEY]=[ORDERS].[O_ORDERKEY]) ORDERED FORWARD)

Этот план запроса использует индексы и соединение вложенных циклов. Поиск по кластерному индексу таблицы ORDERS возвращает 661 заказ, они были размещены 15 марта 1998 года. Для каждого из этих заказов SQL Server выполняет поиск по индексу таблицы LINEITEM, находя записи, связанные с каждым затронутым заказом. Каждый из поисков по индексу потенциально представляет собой серию случайных операций ввода-вывода для перехода от корня индекса B-дерева к страницам на листовом уровне, где и хранятся записи для этого заказа. Чтобы минимизировать стоимость этих операций ввода-вывода, SQL Server улучшает работу соединения вложенные циклов с помощью упреждающей выборки (обратите внимание на пометку WITH UNORDERED PREFETCH, указанную в строке с Nested Loops). Механизм упреждающей выборки действует на опережение поиска по кластерному индексу таблицы ORDERS и выполняет асинхронные операции ввода-вывода для страниц, которые в конечном итоге понадобятся для поиска по индексу в таблице LINEITEM. Как и в сценарии последовательного упреждающего чтения, мы можем увидеть упреждающую выборку в действии, установив для запроса SET STATISTICS IO ON. Вот так может выглядеть сообщение, включающее регистрацию факта упреждающего чтения для таблицы LINEITEM:

Table 'LINEITEM'. Scan count 661, logical reads 5165, physical reads 2, read-ahead reads 5000, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ORDERS'. Scan count 1, logical reads 15, physical reads 2, read-ahead reads 19, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Вы могли заметить, что упреждающая выборка в этом примере была UNORDERED. Действительно, существует два типа предварительной выборки: UNORDERED и ORDERED. Хотя соединение вложенных циклов обычно сохраняет порядок строк из внешнего потока (в данном случае таблицы ORDERS), этот же тип соединения с WITH UNORDERED PREFETCH не сохраняет порядок строк. Вместо этого строки возвращаются в том порядке, в котором они оказываются после асинхронных операций ввода-вывода. Однако, если порядок строк важен, SQL Server может использовать соединение вложенных циклов WITH ORDERED PREFETCH. Например, посмотрите, что произойдет с планом, если мы добавим предложение ORDER BY к приведенному выше запросу:

SELECT O_ORDERKEY, COUNT(*)

FROM ORDERS JOIN LINEITEM ON O_ORDERKEY = L_ORDERKEY

WHERE O_ORDERDATE = '1998-03-15'

GROUP BY O_ORDERKEY

ORDER BY O_ORDERKEY

  |--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1012],0)))
       |--Stream Aggregate(GROUP BY:([ORDERS].[O_ORDERKEY]) DEFINE:([Expr1012]=Count(*)))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([ORDERS].[O_ORDERKEY], [Expr1011]) WITH ORDERED PREFETCH)
                 |--Sort(ORDER BY:([ORDERS].[O_ORDERKEY] ASC))
                 |    |--Clustered Index Seek(OBJECT:([ORDERS].[O_ORDERDATE_CLUIDX]), SEEK:([ORDERS].[O_ORDERDATE]='1998-03-15 00:00:00.000') ORDERED FORWARD)
                 |--Index Seek(OBJECT:([LINEITEM].[L_ORDERKEY_IDX]), SEEK:([LINEITEM].[L_ORDERKEY]=[ORDERS].[O_ORDERKEY]) ORDERED FORWARD)

В этом плане обратите внимание на то, что SQL Server выбирает для сортировки позицию ниже Nested Loops. Чтобы эта сортировка удовлетворяла условию ORDER BY, соединение вложенных циклов должно сохранять порядок возвращаемых строк. Таким образом, на этот раз SQL Server использует соединение вложенных циклов WITH ORDERED PREFETCH.

SQL Server также может использовать случайную упреждающую выборку для ускорения bookmark lookup и некоторых операторов UPDATE и DELETE. Например, рассмотрим следующие два запроса:

SELECT *

FROM LINEITEM

WHERE L_ORDERKEY BETWEEN 5000000 AND 5001000

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [LINEITEM].[L_SHIPDATE], [Expr1004]) OPTIMIZED WITH UNORDERED PREFETCH)
       |--Index Seek(OBJECT:([LINEITEM].[L_ORDERKEY_IDX]), SEEK:([LINEITEM].[L_ORDERKEY] >= (5000000) AND [LINEITEM].[L_ORDERKEY] <= (5001000)) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([LINEITEM].[L_SHIPDATE_CLUIDX]), SEEK:([LINEITEM].[L_SHIPDATE]=[LINEITEM].[L_SHIPDATE] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

UPDATE LINEITEM

SET L_DISCOUNT = 0.1

WHERE L_ORDERKEY BETWEEN 5000000 AND 5001000

  |--Clustered Index Update(OBJECT:([LINEITEM].[L_SHIPDATE_CLUIDX]), SET:([LINEITEM].[L_DISCOUNT] = RaiseIfNull([ConstExpr1010])) WITH UNORDERED PREFETCH)
       |--Compute Scalar(DEFINE:([ConstExpr1010]=CONVERT_IMPLICIT(money,[@1],0)))
            |--Top(ROWCOUNT est 0)
                 |--Index Seek(OBJECT:([LINEITEM].[L_ORDERKEY_IDX]), SEEK:([LINEITEM].[L_ORDERKEY] >= [@2] AND [LINEITEM].[L_ORDERKEY] <= [@3]) ORDERED FORWARD)

Оба плана используют для отбора строк, соответствующих предикату L_ORDERKEY, не кластерный индекс на таблице LINEITEM. В запросе с оператором SELECT выполняется поиск закладок, чтобы извлечь столбцы таблицы LINEITEM, которые не присутствуют в некластерном индексе. Напомним, что Bookmark Lookup — это всего лишь частный случай соединения вложенных циклов. В случае запроса с UPDATE, необходимо найти правильную страницу и строку в кластерном индексе и обновить столбец L_DISCOUNT. Результирующая последовательность ввода-вывода такая же, как и при поиске закладок. В обоих случаях, чтобы минимизировать стоимость операций ввода-вывода, SQL Server добавляет в план упреждающую выборку. Как и в первом примере, механизм упреждающей выборки, опираясь на поиск в некластерном индексе таблицы LINEITEM, выполняет асинхронные операции ввода-вывода для загрузки страниц кластерного индекса, которые потом потребуются.

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