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

PDF

На прошедшей конференции 24HOP Russia я рассказывал о Columnstore индексах и оптимизаторе запросов. К сожалению, мне не хватило времени поделиться всеми интересными примерами, и в этой заметке я расскажу об одном любопытном случае, который влияет на производительность запроса и Columnstore индекса.

Одним из революционных изменений представленных в рамках технологии Columnstore является режим выполнения запроса Batch. Рассказ об это режиме занимает много времени, поэтому, я не буду подробно останавливаться на объяснении механизмов работы этого режима.

Вкратце, режим выполнения Batch отличается от традиционного режима выполнения Row тем, что итераторы (то что мы видим, как операторы плана в среде SSMS) обрабатывают за раз не по одной строке, а по пакету из, примерно, 1000 строк.
За более подробным объяснением можно обратиться к следующим источникам:
SQL Server Columnstore Index FAQ — 7. Batch Mode Processing
MSDN-Columnstore Indexes
Также, вы можете скачать мою презентацию (~433 KB) с 24HOP, где тоже подробно рассматривается режим Batch.

Вернемся к тому случаю, о котором я не успел рассказать.
Для начала, нам потребуются SQL Server 2012 (я использую версию RTM, редакцию Developer Edition) и тестовые данные. В частности, таблица, симулирующая таблицу фактов. Для теста я сделал таблицу в 10 000 000 строк.

Подготовка данных

use master;
go
if db_id('CS') is not null drop database CS;
go
create database CS;
go
use CS;
go
with nums(n) as
(
	select top(10000000) row_number() over (order by(select null)) from master..spt_values v1, master..spt_values v2, master..spt_values v3
)
select
	OrderID = n,
	CountryID = n%10,
	RegionID = n%100,
	CityID = n%10000,
	StoreID = n%1000,
	OrderSum = convert(money,n%100000+convert(money,n)/100.00)
into SalesOrder
from
	nums
go
create nonclustered columnstore index csix_SalesOrder on 
	SalesOrder(OrderID,CountryID,RegionID,CityID,StoreID,OrderSum);
go

Пример
Теперь, выполним два запроса, включив планы и статистику (выполним два раза, чтобы разогреть кэш)
Запросы считают сумму заказов для 10 региона:

set statistics time, io on
select
	RegionID, 
	sum(OrderSum) 
from 
	SalesOrder
where
	convert(varchar(10),RegionID) = '10'
group by 
	RegionID
option(recompile)


select 
	RegionID, 
	sum(OrderSum) 
from 
	SalesOrder
where
	RegionID+0 = 10
group by 
	RegionID
option(recompile)

Статистика:

Первый запрос выполнился в 20 (!) раз медленнее!

Посмотрим на планы:

Планы данных запросов почти ничем не отличаются. Имеют почти одинаковую стоимость и одинаковый режим выполнения Batch! Первый запрос показывает предупреждение о том что преобразование может повлиять на оценки. Но если посмотреть на оцененное число строк — то мы увидим 100000 против 101090, разница очень небольшая. Также известно, что Columnstore не сбалансированное дерево и поиск по нему невозможен, только сканирование. По этому, в отличии от традиционных индексах мы можем не беспокоится о том, что аргумент не SARGable. Почему же тогда такая разница.

Более реалистичный пример.
Те, кто занимается оптимизацией запросов наверняка знают про то что функция coalesce раскрывается оптимизатором как case:

При этом, если в выражении присутствует, например, подзапрос, то он будет выполнен несколько раз.
На эту тему есть популярный запрос на Microsoft Connect — Unnecessarily bad performance for coalesce(subquery) — by Erland Sommarskog
По этому, функция isnull считается более дружелюбной к оптимизатору и рекомендуется использовать ее (помня, что она возвращает данные согласно типу данных первого аргумента, и если у вас первый аргумент varchar(10), к примеру, а второй varchar(20) — то второй аргумент будет обрезан до varchar(10)).
В данном случае, такая дружелюбность выходит боком.

Посмотрим запрос:

set nocount on
set statistics time, io on
select 
	RegionID, 
	sum(OrderSum) 
from 
	SalesOrder
where
	isnull(OrderSum,0) < 10000
group by 
	RegionID
option(recompile)

select 
	RegionID, 
	sum(OrderSum) 
from 
	SalesOrder
where
	coalesce(OrderSum,0) < 10000
group by 
	RegionID
option(recompile)
go

Статистика:

Планы:

И снова, планы одинаковые, при этом, оба имеют режим выполнения Batch, но первый запрос выполняется в 2 (!) раза медленнее!

Объяснение
К счастью, такому поведению есть объяснение и нам даже не придется лезть в недокументированные дебри =)
Однако, способ которым можно посмотреть что происходит, очень странный и если не знать где искать — будет затруднительно.

Итак, настроим сессию xEvents, выберем событие expression_compile_stop_batch_processing. В документации, как и вообще в интернете мне не удалось найти описание этого события, однако, вот что гласит описание самого события в sql server:

Occurs when an expression is not natively supported in batch processing mode and a wrapper of row-by-row evaluation is used.

Перевести можно как то, что для некоторых выражений, отсутствует поддержка режима Batch. Вместо этого используется некая оболочка, благодаря которой выражение можно обработать но в режиме Row.

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

Теперь, запустим сессию и выполним наши запросы один за другим:

И мы получаем… событие expression_compile_stop_batch_processing для первого запроса с isnull!
Voilà!

Резюме
Дело в том, что режим Batch использует векторную обработку. Добавить к колонке какое-то целое число — не составляет проблем, и может быть обработано в режиме Batch, проверить на NULL при помощи case — тоже. Но не все вычисления можно выполнить в этом режиме. Подробнее о векторной обработке я рассказывал в своем докладе на 24 HOP Columnstore Indexes, и еще буду рассказывать на конференции DevCon. Приходите, будет интересно! =)

Как только вы увидите в плане перед сканированием columnstore индекса оператор compute scalar — подумайте, не может ли он предотвратить реальный режим Batch и проверьте это при помощи xEvents, помня о том, что данное событие генерируется только во время компиляции плана. Для меня остается загадкой, зачем это было вынесено в отдельное событие, вместо того, чтобы отразить это в плане, особенно учитывая что этот момент определяется на этапе компиляции плана. Возможно, на то были свои причины, но это странно.

Пища для размышлений
В качестве дальнейших экспериментов, оставляю следующий запрос, в котором задействована in-line функция.
Тестировать на скалярных функциях смысла нет, т.к. скалярная функция отменяет параллельный план, а режим Batch в текущей версии возможен только для параллельных планов.

create function dbo.nop_i(@d decimal)
returns table
as
return select @d as d;
go
set nocount on
set statistics time, io on
select 
	RegionID, 
	sum(OrderSum) 
from 
	SalesOrder
	cross apply dbo.nop_i(OrderSum) i
where
	 i.d < 10000
group by 
	RegionID
option(recompile)

select 
	RegionID, 
	sum(OrderSum) 
from 
	SalesOrder
where
	(select OrderSum) < 10000
group by 
	RegionID

PDF