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

Откуда: Москва
Сообщений: 361
Добрый день
Исходные данные:
Версия сервера:
Microsoft SQL Server 2014 (KB4019093) - 12.0.5207.0 (X64)

Есть две тестовые таблицы (для того чтобы показать поведение):
Основная :
CREATE TABLE [Main](
	[OrderID] [int] NOT NULL,
	[RegID] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/*некластерный индекс*/
Create INdex IX_RegID ON Main(RegID)


Содержит около 500 000 строк

и вспомогательная:
CREATE TABLE [Linked](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[OrderID] [int] NULL,
	[BoxID] [int] NULL,

PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/*Индексы*/
CREATE NONCLUSTERED INDEX [IX_Order_ID] ON [dbo].[Linked]([OrderID] ASC)
GO
CREATE  INDEX [IX_BOX_ID] ON [dbo].[Linked]([BoxID] ASC)


Содержит около 430 000 строк


При выполнении запроса
Select TOP 1000 T0.OrderID,T7.OrderID from
Main T0
LEFT JOIN Linked T7 ON T0.OrderID = T7.OrderId
where T0.RegID Is Not Null
AND T7.OrderID Is null
order by T0.RegID

План выполнения запроса в оценке строк показывает меньше записей чем их реальное количество.
План прикрепил.
Почему так?

К сообщению приложен файл. Размер - 107Kb
14 май 18, 18:02    [21409040]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
И второй Index Seek такая же картина

К сообщению приложен файл. Размер - 108Kb
14 май 18, 18:03    [21409043]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
[сарказм]еще пару селектов сделайте, чтобы он статистику на таблицах обновил.[/сарказм]
ну и OPTION ( RECOMPILE ) в конце, для красоты.
SELECT TOP 1000 
  T0.[OrderID]
FROM
  [Main] T0
WHERE
  T0.[RegID] IS NOT NULL
  AND NOT EXISTS( SELECT 1 FROM [Linked] T7 WHERE T0.[OrderID] = T7.[OrderId] )
ORDER BY
  T0.[RegID]
OPTION (
  RECOMPILE )
14 май 18, 18:15    [21409064]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
Руслан Дамирович,

Таблицы только созданы, но для каждой из таблиц делал Update Statistics

выполнил Ваш запрос,получилось все тоже самое.

И еще вопрос - откуда оценочное число 7000 или 8000, 1000 строк запрашиваю.

К сообщению приложен файл. Размер - 99Kb
14 май 18, 20:05    [21409252]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361


К сообщению приложен файл. Размер - 122Kb
14 май 18, 20:05    [21409253]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Алексей,

https://www.sqlshack.com/join-estimation-internals/
14 май 18, 20:32    [21409270]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1368
Алексей,

Это оценочное количество строк не для всего запроса а для оператора index seek, сам же на скриншоте курсором оценку смотрите.
Оптимизатор предполагает что у вас строк удовлетворяющих предикату поиска RegID is not null около 7000

update statistics как делаете? если она sampled то реальные данные могут в некоторой степени различаться от показателей распределения в гистограмме.

покажите результат
select * from sys.stats s cross apply sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
where s.[object_id] = object_id('Main', N'U');


и дополнительно для вашего запроса включите
option (querytraceon 3604, querytraceon 9292, querytraceon 9204)
14 май 18, 21:47    [21409348]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
felix_ff,

статистику делаю update statistics Main

запрос возвращает две строки
1620200822;PK__Main__C3905BAF2A8B2BBF;1;0;0;0;0;NULL;0;0;1620200822;1;2018-05-14 17:41:01.3700000;497166;497166;11;497166;0
1620200822;IX_RegID;2;0;0;0;0;NULL;0;0;1620200822;2;2018-05-14 17:39:36.7670000;497166;497166;200;497166;0
14 май 18, 23:18    [21409504]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
felix_ff,

при установленных флагах картина не поменялась
14 май 18, 23:21    [21409509]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
invm,

спасибо
14 май 18, 23:22    [21409513]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
felix_ff,

странно как то оценивает, если убрать Top 1000 (мне кажется, что причина кроется в нем) то оценка приблизительно правильная.... Warning появился.....

К сообщению приложен файл. Размер - 112Kb
15 май 18, 07:22    [21409669]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Алексей,

Вам invm не просто так ссылку скинул.
Скорее всего дело в top, скорее всего используется row goal. В 2017 CU3 в плане показывается используется он или нет.
Для того чтобы посчитать кол-во строк ему нужно знать селективность соеденеия и кол-во строк в таблице.
Если интересно покапайтесь с включенными (НЕ ДОКУМЕНТИРОВАННЫМИ) флагами: 3604, 2363
Также можно использовать флаги 8607 и 8712, на выходе дерево физических оператор.
Подробнее http://www.queryprocessor.ru/rowgoal-on-non-uniform-distribution/
15 май 18, 09:50    [21409907]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
aleksrov,

Спасибо огромное, буду разбираться
15 май 18, 09:58    [21409931]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Алексей,
в тему row goal покажите оценку

Select TOP 1000 T0.OrderID,T7.OrderID from 
#Main T0
LEFT OUTER LOOP JOIN #Linked T7  ON T0.OrderID = T7.OrderId
where T0.RegID Is Not Null
AND T7.OrderID Is null
order by T0.RegID 
OPTION (RECOMPILE, querytraceon 4138)
15 май 18, 10:58    [21410097]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Алексей,

Немного дополню свой ответ простым примером.
Предположим у нас есть таблица Table (id int, num int), статистика свежая, есть PK по id, по num ничего нет. В таблице 65552 строки, num равен id, т.е. по сути поле тоже уникально, на нем нет индекса, но есть статистика.
Теперь я выполню:
select top 7 * from table where number < 1000

Вернулось 7 строк. План обычный - Select --> Top --> Clust. scan
Смотрим на ожидание, если смотреть без флага 9130, то est. num of rows 7, чтобы увидеть нужной нам на плане, включает этот флаг, теперь фильтр у нас в плане "отдельно", теперь ожидание у нас 459.323 (Actual num of rows 7). Также можно посмотреть ожидание RowGoal с помощью 8607, 8612, как в ссылке выше.
Теперь разбираемя откуда он взял 459.323. Включаем флаг 2363 и смотрим. Сначала посмотрим какую статистику он загрузил, там должна быть строчка : Loaded histogram for column : Table.num from stats with id = 2 (у меня, у вас может быть другое). Смотрим что это за статистика, можно через sys.stats с нашим object_id.
Далее здесь смотри selectivity для нашего скана, она равна 0.0152398, в данном случае она считается 999\65552 (причем 6552 это данные с статистики)
Также ниже можно увидеть кардинальность нашего фильтра, это будет 999: 0.0152398 * 65552 (а вот сейчас 65552 это уже метаданные нашей таблицы, т.е. если бы в к примеру у нас в таблице было 64500 строк, но в статистике было бы 65552, то эта цифра была бы 982.968: 0.0152398 * 64500. Кстати, если в таком случе убрать top, то это будет наш estimated number of rows у скана), но в случае с RowGoal, эти данные как я понимаю не используются (конкретно в моем примере).
Я отвлекся, так откуда взялось 459.323, да все просто, зная селективность оптимизатор, учитывая равномерное распределение данные может оценить, сколько надо отсканить чтобы получить 7 строк : 7\0.0152398, если бы был top 6 то ожидаемое кол-во строк было бы 393.706

P.S. Не буду делать вида что полностью понимаю все кишки оптимизатора, на эту тему преступно мало статей, поэтому возможно где-то ошибся, скажу спасибо если меня поправят.
15 май 18, 11:20    [21410149]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
aleksrov,

так то всё это да, но ведь суть использования оценки кардинальности это в итоге выбор оптимального типа соединения, и как раз row goal корректирует и получаем NL вместо скорее всего HASH без корректировки
15 май 18, 11:25    [21410174]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
aleksrov
Member

Откуда:
Сообщений: 948
TaPaK,

Я это понимаю, я имел ввиду что в данном случе, без соедения, когда from просто из одной таблицы и используется rowgoal то кардинальность считается, но не влияет на кол-во ожидаемых строк.
А так да, кардинальность используется дальше, к прмиру я задавал Дмитрию вопрос, скопирую его сюда.

I have one more question, it doesn't relate to this article, but it's about estimator.

There is a query:

SELECT *
FROM [TSQL2012].[dbo].[Employees] as e
join sales.Orders as o on e.ID = o.empid
where (e.ID > 63000 and e.ID < 65000) or e.ID = 1

Employees has 63900 rows (in stat 65552), orders has 830 (in stat 830).
At the top of the plan there's seek on Employess Clustered index, at the bottom scan on Orders.
Selectivity of the seek is 0.0305101 and estimated number of rows 1949.6
Scan has selectivity of 1 and and estimated number of rows 830.
Then there is merge join, it has selectvity 7.40964e-005 and estimated number of rows 119.9
I understand how it calculated 119.9 ( 7.40964e-005 * 1949.6 * 830), but i don't understand where he got 7.40964e-005.
I have read your article http://www.queryprocessor.c...
And it uses CselCalcExpressionComparedToExpression calculator, but in your article you didn't explain how exactly it calculate selectivity for this calculator.
I hope you explain that.
You write very interesting articles.Thanks again.

Здесь видно что кардинальность использовалась в merge join для подсчета estimated number of rows.
15 май 18, 11:33    [21410197]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
TaPaK,

Как ее вывести?
15 май 18, 12:05    [21410299]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Алексей
TaPaK,

Как ее вывести?

да всё то же Actual/Estimate rows
15 май 18, 12:06    [21410302]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
TaPaK,

Для запроса
Select TOP 1000 T0.OrderID,T7.OrderID from 
Main T0
LEFT OUTER LOOP JOIN Linked T7  ON T0.OrderID = T7.OrderId
where T0.RegID Is Not Null
AND T7.OrderID Is null
order by T0.RegID 
OPTION (RECOMPILE, querytraceon 4138)


К сообщению приложен файл. Размер - 65Kb
15 май 18, 12:26    [21410371]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
С этим запросом количество ожидаемых строк увеличилось

К сообщению приложен файл. Размер - 83Kb
15 май 18, 12:28    [21410379]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Алексей,

ну как я понимаю, в оригинале вы и видите скорректированную row goal оценку и на выходе NL, если хинт LOOP убрать и 4138 то получите скорее всего HASH. В оригинале оценка/факт меньше скорее из-за того, что на самом деле определить точно сколько же прийдется прочитать для получения вашей 1000 записией трудно/не возможно :)
15 май 18, 12:35    [21410397]     Ответить | Цитировать Сообщить модератору
 Re: Странная оценка количества строк  [new]
Алексей
Member

Откуда: Москва
Сообщений: 361
TaPaK,

Спасибо за ответ :)
15 май 18, 13:47    [21410657]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить