Заметки Дмитрия Пилюгина о Microsoft SQL Server 

Twitter RSS
Home SQL Server (все заметки) Дополнительные чтения в nested loops
formats

Дополнительные чтения в nested loops

Недавно, просматривая статистику ввода вывода одного из запросов, я обнаружил дополнительные чтения там, где по-идее их быть не должно. Мне стало интересно, откуда они берутся и я попробовал разобраться.

 

 

 

Создадим тестовую таблицу и заполним ее значениями.

use tempdb;
go
create table t(id int identity primary key, a char(50), b char(50) default 'b');
with g as(select top (1000) rn = row_number() over (order by(select null)) from master..spt_values v1, master..spt_values v2)
insert t(a)
select top(20000) 'a'+ replace(str(g2.rn,5),' ','0')
from
	g g1
	join g g2 on g1.rn <= g2.rn
order by g2.rn;
create index ix_a on t(a);
go

В результате выполнения этого скрипта будет создана таблица, в которой значения в колонке [a] распределятся как: одна строка для значения «a00001», две для «a00002», три для «a00003» и т.д. Это не имеет принципиального значения, а просто удобно для демонстрации.
Взглянем на план запроса

select * from t where a = 'a00010'

Мы видим, что используется поиск по индексу ix_a (Index Seek), и для каждого найденного значения поиск соответствующей строки в кластерном индексе (Key Lookup), после чего результаты объединяются при помощи Nested Loops Join.
Теперь попробуем посчитать сколько чтений страниц, потребуется, например, чтобы получить все строки для значения ‘a00010’.
Для этого нам потребуется знать глубину индексов:

select
	name,
	depth = indexproperty ([object_id],name,'IndexDepth')
from
	sys.indexes
where [object_id] = object_id('dbo.t');

Мы увидим следующее

name depth
PK__t__3213E83F2F10007B 2
ix_a 3

Теперь мы можем посчитать количество чтений.
Итак, три чтения требуются чтобы выполнить поиск в некластерном индексе ix_a и найти на листовом уровне указатели на строки кластерного индекса, которых, как мы помним всего 10 для значения ‘a00010’. Далее, для каждой из найденных строк, требуется выполнить поиск в кластерном индексе, который состоит в нашем случе из двух уровней, т.е. каждый поиск это чтение 2-х страниц. Всего 10 операций поиска итого 20 страниц. В итоге, 3 + 10*2 = 23, мы должны ожидать чтения 23 страниц. Проверим.

/*
сделаем запрос, чтобы заполнить кэш данных и исключить влияние
дополнительных чтений информации из служебных таблиц
*/
select * from t
/*
теперь включим сбор статистики и выполним запрос
*/
set statistics io on
select * from t where a = 'a00010'
set statistics io off

Результат:

(10 row(s) affected)
Table ‘t’. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Мы не ошиблись. Та же математика для 20 строк: 3+20*2=43. Проверяем:

set statistics io on
select * from t where a = 'a00020'
set statistics io off
(20 row(s) affected)
Table ‘t’. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

То же верно.
Для 40 строк, 3+1+40*2 = 84, 1 чтение — это частичное сканирование (partial scan) листового уровня некластерного индекса, т.к. указатели для 40 строк, уже не помещяются на одной странице.

set statistics io on
select * from t where a = 'a00040'
set statistics io off
(40 row(s) affected)
Table ‘t’. Scan count 1, logical reads 95, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Вопрос, откуда взялось еще 11 чтений?
Если проделать эксперимент для 80, мы получим уже 14 чтений и т.д. Чем больше количество строк, тем больше чтений мы будем получать. Правда в условиях этого эксперимента уже со значения 85 оптимизатор у меня выбирает сканирование, но тенденция понятна.
Итак, откуда они берутся?
Оказывается, надо повнимательнее посмотреть на планы запросов. Точнее на свойства оператора Nested Loops.
Если сравнить план для 20 строк и для 40 строк, то вот что мы увидим.

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

set statistics io on
select * from t where a = 'a00025'
select * from t where a = 'a00026'
set statistics io off

Вместо ожидаемого увелечения чтений на 2 страницы (т.е. на еще одну операцию поиска по кластерному индексу), количество возрастает сразу на 11, а в плане появляется секция WithUnorderedPrefetch.

(25 row(s) affected)
Table ‘t’. Scan count 1, logical reads 53, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(26 row(s) affected)
Table ‘t’. Scan count 1, logical reads 64, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)

Существует специальный флаг трассировки 8744, который отключает предварительное чтение в nested loops.
Если выполнить такой запрос:

set statistics io on
select * from t where a = 'a00040' option (querytraceon 8744)
set statistics io off

то мы увидим

(40 row(s) affected)
Table ‘t’. Scan count 1, logical reads 84, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Т.е. то что и должно быть.
Так что, не удивляйтесь если вдруг столкнетесь с дополнительными чтениями в nested loops, а обратите внимание, есть ли в плане секция prefetching.

 

 

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Анти-спам: введите результат (цифрами) *