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

Откуда:
Сообщений: 241
Добрый день, подскажите, пожалуйста, как тут работает сервер (2008 R2).

Есть две таблицы [a] и [b], в первой порядка 30 млн строк, во второй порядка 800 млн.
Каждая из табиц описывает неск-ко аспектов одной и той же группы физических процессов, при этом [a] относится к [b] как master к detail (т.е. как общее описание процесса к списку подробностей), и однозначная идентификация процесса в каждой таблице производится по полю uniqueid

Далее, для [b] созданы два условных индекса:

A.
create nonclustered index if_b1 on b
  (uniqueid)
include
  (eventname, eventtype, propertyA)
where 
  (eventtype in ('1', '2', '3', '4', '5', '6', '9') and propertyB='smth')

B.
create nonclustered index if_b2 on b 
  (uniqueid,eventname,eventtype,propertyA)
where 
  (eventtype in ('1', '2', '3', '4', '5', '6', '9') and propertyB='smth')

и выполняются два эквивалентных, в смысле возвращаемого результата, запроса:

A.
dbcc dropcleanbuffers

  select
    count(1)
  from
    a
  join
    b with (index = if_b1)
  on
    b.uniqueid = a.uniqueid
    and b.eventtype in ('1', '2', '3', '4', '5', '6', '9')
    and b.propertyB = 'smth'
  where
    [условие для кластерного индекса по a]
    and b.eventname = '50004'

B.
dbcc dropcleanbuffers

  select
    count(1)
  from
    a
  join
    b
  on
    b.uniqueid = a.uniqueid
    and b.eventtype in ('1', '2', '3', '4', '5', '6', '9')
    and b.propertyB = 'smth'
  where
    [условие для кластерного индекса по a]
    and b.eventname = '50004'

(разница в клаузе with; т.е. мы как бы хотим узнать количество событий конкретного вида '50004', случившихся во время протекания некоторой группы процессов, и ищем оптимальный способ это сделать)

Актуальные планы представленных запросов однотипны (см. картинку), и вот, что не очень понятно: по умолчанию для поиска в таблице [b] оптимизатор использует Index Seek по индексу if_b2, но время поиска, как CPU, так и Elapsed, при этом получается БОЛЬШЕ, чем при использовании, после явного на то указания, индекса if_b1. А именно, поставив
set statistics time on
set statistics io on
, для первого запроса получаем

Table 'b'. Scan count 276243, logical reads 1409194, physical reads 24377, read-ahead reads 22645, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'a'. Scan count 1, logical reads 12571, physical reads 93, read-ahead reads 12565, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 9187 ms, elapsed time = 27689 ms.

, для второго

Table 'b'. Scan count 1933701, logical reads 7748858, physical reads 24170, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'a'. Scan count 1, logical reads 12571, physical reads 46, read-ahead reads 12565, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 22796 ms, elapsed time = 87508 ms.

Вопросов, собственно, три :

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

Это важный вопрос, т.к. все время подсказывать оптимизатору, какими индексами ему следует пользоваться, мучительно с точки зрения поддержки существующего кода - не говоря уже о том, что подобная практика противоречит концепции SQL, как декларативного языка программирования.

2. Почему, если второй из запросов выполняется дольше, относительная стоимость общей части двух запросов - поиска по кластерному индексу в таблице [a] - для второго запроса не объявлена меньшей?

Логика следующая: если в двух разных запросах есть общий этап или общая часть (у нас это Index Seek по кластерному индексу ic_a), то ее "абсолютную" стоимость, с долей условности, можно принять одинаковой, и тогда более ресурсоемким и длительным должен быть тот из запросов, для которого вклад общей части в совокупную стоимость запроса будет меньшим - а всего остального, таким образом, большим. По картинке получается наоборот.

3. Почему вообще отбор строк по условию eventname = '50004' с использованием индекса if_b2, для которого поле eventname входит в список индексируемых, оказывается более ресурсоемким и длительным, чем отбор по тому же условию с использованием индекса if_b1, для которого поле eventname входит в список "включенных"? Селективность условия eventname = '50004', имхо, приличная: доля строк, отвечающих условию eventname = '50004' в общем количенстве строк, отвечающих прочим условиям, не превосходит 0.01

Указав
set statistics profile on
, по выполнении запросов я получаю следующие отчеты:

+

А.
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
11select count(1) from a join b with (index = if_b1) on b.uniqueid = a.uniqueid and b.eventtype in ('1', '2', '3', '4', '5', '6', '9') and b.propertyB = 'smth' where stop between '2013-08-01' and '2013-08-16' and eventname = '50004'110NULLNULLNULLNULL1NULLNULLNULL0,01248176NULLNULLSELECT0NULL
00 |--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1011],0)))121Compute ScalarCompute ScalarDEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1011],0))[Expr1008]=CONVERT_IMPLICIT(int,[Expr1011],0)101,10E-06110,01248176[Expr1008]NULLPLAN_ROW01
11 |--Stream Aggregate(DEFINE:([Expr1011]=Count(*)))132Stream AggregateAggregateNULL[Expr1011]=Count(*)101,10E-06110,01248176[Expr1011]NULLPLAN_ROW01
264401 |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[uniqueid]) OPTIMIZED)143Nested LoopsInner JoinOUTER REFERENCES:([a].[uniqueid]) OPTIMIZEDNULL104,18E-0690,01248066NULLNULLPLAN_ROW01
2762431 |--Clustered Index Seek(OBJECT:([a].[ic_a]), SEEK:([a].[stop] >= '2013-08-01 00:00:00.000' AND [a].[stop] <= '2013-08-16 00:00:00.000') ORDERED FORWARD)164Clustered Index SeekClustered Index SeekOBJECT:([a].[ic_a]), SEEK:([a].[stop] >= '2013-08-01 00:00:00.000' AND [a].[stop] <= '2013-08-16 00:00:00.000') ORDERED FORWARD[a].[uniqueid]10,0031250,0001581270,0032831[a].[uniqueid]NULLPLAN_ROW01
26440276243 |--Index Seek(OBJECT:([b].[if_b1]), SEEK:([b].[uniqueid]=[a].[uniqueid]), WHERE:([b].[eventname]='50004') ORDERED FORWARD)174Index SeekIndex SeekOBJECT:([b].[if_b1]), SEEK:([b].[uniqueid]=[a].[uniqueid]), WHERE:([b].[eventname]='50004') ORDERED FORWARD, FORCEDINDEXNULL10,0038657410,0037568121380,007622553NULLNULLPLAN_ROW01


В.
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
11select count(1) from a join b on b.uniqueid = a.uniqueid and b.eventtype in ('1', '2', '3', '4', '5', '6', '9') and b.propertyB = 'smth' where stop between '2013-08-01' and '2013-08-16' and eventname = '50004'310NULLNULLNULLNULL1NULLNULLNULL0,00657148NULLNULLSELECT0NULL
00 |--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1011],0)))321Compute ScalarCompute ScalarDEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1011],0))[Expr1008]=CONVERT_IMPLICIT(int,[Expr1011],0)101,10E-06110,00657148[Expr1008]NULLPLAN_ROW01
11 |--Stream Aggregate(DEFINE:([Expr1011]=Count(*)))332Stream AggregateAggregateNULL[Expr1011]=Count(*)101,10E-06110,00657148[Expr1011]NULLPLAN_ROW01
264401 |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[uniqueid]))343Nested LoopsInner JoinOUTER REFERENCES:([a].[uniqueid])NULL104,18E-0690,00657038NULLNULLPLAN_ROW01
2762431 |--Clustered Index Seek(OBJECT:([a].[ic_a]), SEEK:([a].[stop] >= '2013-08-01 00:00:00.000' AND [a].[stop] <= '2013-08-16 00:00:00.000') ORDERED FORWARD)354Clustered Index SeekClustered Index SeekOBJECT:([a].[ic_a]), SEEK:([a].[stop] >= '2013-08-01 00:00:00.000' AND [a].[stop] <= '2013-08-16 00:00:00.000') ORDERED FORWARD[a].[uniqueid]10,0031250,0001581270,0032831[a].[uniqueid]NULLPLAN_ROW01
26440276243 |--Index Seek(OBJECT:([b].[if_b2]), SEEK:([b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='1' OR [b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='2' OR [b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='3' OR [b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='4' OR [b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='5' OR [b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='6' OR [b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='9') ORDERED FORWARD)364Index SeekIndex SeekOBJECT:([b].[if_b2]), SEEK:([b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='1' OR [b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='2' OR [b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='3' OR [b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='4' OR [b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='5' OR [b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='6' OR [b].[uniqueid]=[a].[uniqueid] AND [b].[eventname]='50004' AND [b].[eventtype]='9') ORDERED FORWARDNULL10,0031250,000158190,0032831NULLNULLPLAN_ROW01


Из них как бы следует, что поиск по if_b2 отличается от поиска по if_b1 осуществлением проверки eventtype in ('1', '2', '3', '4', '5', '6', '9'), которая здесь как бы лишняя. Возникает вопрос: это реальная проверка, или видимость? Т.е. может быть это формальная визуализация физических особенностей поиска по составному индексу if_b2, например, того факта, что реальные ссылки на строки таблицы хранятся на нижнем (последнем) уровне дерева индексов, так что даже заранее зная, что для какой-нибудь ветки условие b.uniqueid = a.uniqueid and b.eventname = '50004' гарантированно выполняется, движок обязан пройти по ней до конца?

Или что-то еще?

Подскажите, пожалуйста, как можно интерпретировать данные.

К сообщению приложен файл. Размер - 71Kb
29 авг 13, 17:50    [14772457]     Ответить | Цитировать Сообщить модератору
 Re: Как работает индекс с условием  [new]
SomewhereSomehow
Member

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

Добрый день!
Буду говорить за себя.

Во-первых, спасибо за хорошо оформленный вопрос. Это редкость.

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

По поводу анализа плана.
Тут, скажу честно, "чукча не читатель, чукча писатель" или "не читал, но осуждаю" =)
В том смысле, что детальный анализ плана не проводил, пока посмотрел бегло. Будет время завтра или на выходных, обязательно постараюсь сделать репро, чтобы объяснить поведение оптимизатора наглядно. Но наверняка у кого-то есть больше времени в данный момент, чтобы заняться этим (а я сейчас иду готовить блины=)).

Поверхностный анализ.
- Сам индекс, когда вы включаете в ключи доп. поля - шире, больше IO.
- Фильтрованные индексы имеют ограничения, не всегда очевидные.
- Обратите внимание на свойство Nested Loops (OPTIMIZED) в первом случае и отсутсвие такового во втором. Что за свойство - OPTIMIZED Nested Loops Joins - Craig Freedman. Может быть имеет смысл коррелировать это с "read-ahead reads 22645" в первом случае против "read-ahead reads 0" во втором. Впрочем, не утверждаю, но обращаю внимание.
- Есть сильное подозрение, что оба плана дико не оптимальны. Зачем там вообще Nested Loops.
Что значит: Clustered Index Seek (a) - Rows:276243 - EstimateRows: 1
Я уже подзабыл текстовый вывод "set statistics profile on", но не значит ли это что в предполагаемом плане там вообще одна строка, а реально - 276243 строк? Если так - то оба плана сильно ишибаются. И виной тому может быть вот это: [условие для кластерного индекса по a]

В общем, я думаю пища для размышлений есть, думайте. Если ничего не придумаете - публикуйте реальные планы в нормальном виде - будем думать вместе!
29 авг 13, 20:20    [14772977]     Ответить | Цитировать Сообщить модератору
 Re: Как работает индекс с условием  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Shlippenbaranus,

UPDATE STATISTICS A WITH FULLSCAN
30 авг 13, 04:10    [14773783]     Ответить | Цитировать Сообщить модератору
 Re: Как работает индекс с условием  [new]
топикстартер
Guest
Спасибо откликнувшимся, пошел осваивать подсказки.
30 авг 13, 07:18    [14773860]     Ответить | Цитировать Сообщить модератору
 Re: Как работает индекс с условием  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
интересно, что для таких объемов данных вернет sys.dm_db_index_physical_stats
select *
from sys.dm_db_index_physical_stats
(db_id ('имя бд таблицы A или B')
, object_id ('[имя бд таблицы A или B].[схема таблицы A или B].a')
, <идентификатор индекса if_b1 или if_b2>
, null, 'detailed');
30 авг 13, 08:17    [14773927]     Ответить | Цитировать Сообщить модератору
 Re: Как работает индекс с условием  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Ещё неплохо было бы увидеть результат
DBCC SHOW_STATISTICS (a, ic_a)
30 авг 13, 09:32    [14774197]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить