Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вопрос по плану  [new]
хмхмхм
Guest
коллеги, есть вопрос.

Почему для запроса

select top 10 start_datetime from dbo.b
where start_datetime < getdate()
and finish_datetime is null


При индексе:
CREATE NONCLUSTERED INDEX [idx1] ON dbo.b
(
	[start_datetime] asc,
	[finish_datetime] ASC
)
INCLUDE ( 	[id2],
	[val],
	[type])


Получается один план запроса:
+

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="11.0.3000.0">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="select top 10 start_datetime from dbo.b
where start_datetime < getdate()
and finish_datetime is null" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.184645" StatementEstRows="1" StatementOptmLevel="TRIVIAL" QueryHash="0x7E1213AAF8D66B7" QueryPlanHash="0x5E586E555CA24208">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="2" CompileCPU="2" CompileMemory="128">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209677" EstimatedPagesCached="104838" EstimatedAvailableDegreeOfParallelism="4" />
<RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="15" EstimatedTotalSubtreeCost="0.184645" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[b]" Column="start_datetime" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="0" IsPercent="0" WithTies="0">
<TopExpression>
<ScalarOperator ScalarString="(10)">
<Const ConstValue="(10)" />
</ScalarOperator>
</TopExpression>
<RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="0.146088" EstimateCPU="0.033157" AvgRowSize="23" EstimatedTotalSubtreeCost="0.179245" TableCardinality="30000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[b]" Column="start_datetime" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[b]" Column="start_datetime" />
</DefinedValue>
</DefinedValues>
<Object Database="[DB]" Schema="[dbo]" Table="[b]" Index="[idx1]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<EndRange ScanType="LT">
<RangeColumns>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[b]" Column="start_datetime" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="getdate()">
<Identifier>
<ColumnReference Column="ConstExpr1003">
<ScalarOperator>
<Intrinsic FunctionName="getdate" />
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[DB].[dbo].[b].[finish_datetime] IS NULL">
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[b]" Column="finish_datetime" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Top>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>



А при индексе:
CREATE NONCLUSTERED INDEX [idx2] ON dbo.b
(
	[finish_datetime] asc,
	[start_datetime] asc
)
INCLUDE ( 	[id2],
	[val],
	[type])


Получается немного другой план:
+

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="11.0.3000.0">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="select top 10 start_datetime from dbo.b
where start_datetime < getdate()
and finish_datetime is null" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0032832" StatementEstRows="1" StatementOptmLevel="TRIVIAL" QueryHash="0x7E1213AAF8D66B7" QueryPlanHash="0x17C095C96125B905">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="120">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209677" EstimatedPagesCached="104838" EstimatedAvailableDegreeOfParallelism="4" />
<RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[b]" Column="start_datetime" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="0" IsPercent="0" WithTies="0">
<TopExpression>
<ScalarOperator ScalarString="(10)">
<Const ConstValue="(10)" />
</ScalarOperator>
</TopExpression>
<RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="30000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[b]" Column="start_datetime" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[b]" Column="start_datetime" />
</DefinedValue>
</DefinedValues>
<Object Database="[DB]" Schema="[dbo]" Table="[b]" Index="[idx2]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[b]" Column="finish_datetime" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="NULL">
<Const ConstValue="NULL" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
<EndRange ScanType="LT">
<RangeColumns>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[b]" Column="start_datetime" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="getdate()">
<Identifier>
<ColumnReference Column="ConstExpr1003">
<ScalarOperator>
<Intrinsic FunctionName="getdate" />
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</Top>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>



Почему при наличии двух индексов оптимизатор выбирает второй?

Версия сервера Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Скрипт для воспроизведения:
+ Скрипт создания таблицы, тестовые данные

create table dbo.b(
id int identity(1,1) not null primary key,
start_datetime datetime not null,
finish_datetime datetime null,
id2 uniqueidentifier null,
[val] decimal(19,10) null,
[type] char(1) null
)


insert into dbo.b(start_datetime, finish_datetime, id2, val, type)
select top 30000 getdate(), getdate(), newid(), o.object_id, 'A' from sys.objects as o
cross join sys.objects as o2
cross join sys.objects as o3

CREATE NONCLUSTERED INDEX [idx1] ON dbo.b
(
[start_datetime] asc,
[finish_datetime] ASC
)
INCLUDE ( [id2],
[val],
[type])

select top 10 start_datetime from dbo.b
where start_datetime < getdate()
and finish_datetime is null

drop INDEX [idx1] ON dbo.b


CREATE NONCLUSTERED INDEX [idx2] ON dbo.b
(
[finish_datetime] asc,
[start_datetime] asc
)
INCLUDE ( [id2],
[val],
[type])

select top 10 start_datetime from dbo.b
where start_datetime < getdate()
and finish_datetime is null

drop INDEX [idx2] ON dbo.b

truncate table dbo.b
drop table dbo.b



К сообщению приложен файл. Размер - 65Kb
10 фев 15, 13:37    [17244258]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
хмхмхм
Почему при наличии двух индексов оптимизатор выбирает второй?
Потому что оптимизатор умный, и выбирает правильный индекс, используя который он сможет запихать оба условия в seek-предикат.
10 фев 15, 13:47    [17244348]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану  [new]
хмхмхм
Guest
Гавриленко Сергей Алексеевич,

а почему в первом индексе seek predicate только по условию start_datetime?
10 фев 15, 13:51    [17244386]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану  [new]
invm
Member

Откуда: Москва
Сообщений: 9826
хмхмхм
а почему в первом индексе seek predicate только по условию start_datetime?
Потому что start_datetime первый столбец индекса, а условие у вас в предикате не =.
10 фев 15, 14:32    [17244630]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
хмхмхм,

Добрый день,
В случае, когда предикат имеет условие равенства и неравенства по двум полям и есть составной индекс по двум полям, выгоднее, чтобы индекс был построен с лидирующем полем которое проверяется в предикате на равенство.

+

Смотрите сами, допустим, есть список имен и фамилий
Индекс
LastName asc, FirstName asc:
Иванов Антон
Иванов Борис
Иванов Владимир
Петров Антон
Петров Борис
Петров Владимир
Сидоров Антон
Сидоров Борис
Сидоров Владимир

Индекс
FirstName asc, LastName asc:
Антон Иванов
Антон Петров
Антон Сидоров
Борис Иванов
Борис Петров
Борис Сидоров
Владимир Иванов
Владимир Петров
Владимир Сидоров

Ищем по условию: where LastName = ‘Петров’ and FirstName > ‘Б’

Первый индeкс – позиционируемся на фамилия ‘Петров’ и где имя больше Б, начинаем читать – одна строчка, вторая тоже подходит – третья – уже все, т.к. фамилия уже будет Сидоров. Итого – всего две строки прочитали – поиск по диапазону в чистом виде.

Второй индекс – лидирующее поле Имя, позиционируемся на первую запись, где Имя > Б, начинаем читать до тех пор, пока имя больше Б, в нашем случае все оставшиеся записи. Для каждой прочитанной – проверяем условие, что фамилия равна ‘Петров’ (это тот самый residual). Итого – 6 записей прочитали.

Выгоднее первый индекс.


Оптимизатор знает про это, и проставляет операции Index Seek разную стоимость, т.к. каждому из этих операторов придется прочитать разное число записей из таблицы, чтобы вернуть результат. При этом, итоговый результат по оценке совпадает (это логично) – 1 строка, но чтобы его достигнуть – будет прочитано разное число записей.

Посмотрите, сколько реально будет прочитано записей и по скольким будет сделана проверка residual predicate:
select top 10 start_datetime from dbo.b
where start_datetime < getdate()
and finish_datetime is null
option(querytraceon 9130)

(только не используйте этот флаг в продакшене)
В итоге, из-за разной стоимости, оптимизатор выбирает тот вариант, что дешевле, и тут он абсолютно прав.
10 фев 15, 14:33    [17244634]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 901
еще все таки наверно зависит от статистики, т.е от селективности данных.
если finish_datetime будет много нужных значений, а start_datetime наоборот не много нужных значений, соответственно и стоимость будет другая.
10 фев 15, 15:03    [17244808]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Slava_Nik
еще все таки наверно зависит от статистики, т.е от селективности данных.
если finish_datetime будет много нужных значений, а start_datetime наоборот не много нужных значений, соответственно и стоимость будет другая.
В данном случае - не зависит. Второй индекс при любом кол-ве данных будет не хуже, чем первый.
10 фев 15, 15:08    [17244863]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 901
Гавриленко Сергей Алексеевич
Slava_Nik
еще все таки наверно зависит от статистики, т.е от селективности данных.
если finish_datetime будет много нужных значений, а start_datetime наоборот не много нужных значений, соответственно и стоимость будет другая.
В данном случае - не зависит. Второй индекс при любом кол-ве данных будет не хуже, чем первый.

в данном случае да,
если было бы не start_datetime < getdate(), а start_datetime= дата, то уже от селективности данных использовались нужные индексы.
10 фев 15, 15:11    [17244878]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Slava_Nik
Гавриленко Сергей Алексеевич
пропущено...
В данном случае - не зависит. Второй индекс при любом кол-ве данных будет не хуже, чем первый.

в данном случае да,
если было бы не start_datetime < getdate(), а start_datetime= дата, то уже от селективности данных использовались нужные индексы.
Если изменить условие на "start_datetime= дата", то оба индекса эквивалентны полчуаются. Что опять никак не зависит от статистики.
10 фев 15, 15:19    [17244930]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану  [new]
хмхмхм
Guest
SomewhereSomehow,

спасибо за развернутый ответ.
10 фев 15, 15:27    [17244991]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить