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

Откуда:
Сообщений: 22
Добрый день.
Подскажите, пожалуйста, почему в post_execution_plan показывает такую разницу между ожидаемым и актуальным количеством строк?
Например, на прикрепленном плане, самый первый поиск по CON_OPERATIONLOG по индексу KF05_CON_OPERLOG предполагает за 1 запуск вернуть всего 1 строку, при том что возвращает 2090583.

К сообщению приложен файл (plan.rar - 22Kb) cкачать
22 ноя 16, 11:23    [19919597]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
Kos_Da
Подскажите, пожалуйста, почему в post_execution_plan показывает такую разницу между ожидаемым и актуальным количеством строк?
Например, на прикрепленном плане, самый первый поиск по CON_OPERATIONLOG по индексу KF05_CON_OPERLOG предполагает за 1 запуск вернуть всего 1 строку, при том что возвращает 2090583.
Ну, очевидно же. Ожидаемое - это то, что сервер ожидает. Ожидает по статистике, или ещё по каким то причинам. А актуальное - сколько реально получилось.

Разница обычно из за неактуальной статистики. Её надо бы обновить.
22 ноя 16, 11:30    [19919662]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
Kos_Da
Member

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

статистика обновляется прямо перед запуском этой процедуры. делал даже фильтрованную статистику под этот запрос.
запускаю ex_event "inaccurate_cardinality_estimate". но ничего не ловится, то есть оптимизатор не считает, что ошибся.
22 ноя 16, 11:49    [19919798]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
o-o
Guest
...или план построен для одного прослушанного значения,
а передали совсем другое.
где текст запроса?
как выглядит фильтр на [LOAN].[dbo].[CON_OPERATIONLOG].ELEMENTTYPE?
where [LOAN].[dbo].[CON_OPERATIONLOG].ACTIONDATE >= <вот тут что? параметр/переменная?>
22 ноя 16, 11:53    [19919818]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
o-o
Guest
ага, уже признался, это процедура. с параметром, ага?
option(recompile)
22 ноя 16, 11:54    [19919831]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
TaPaK
Member

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

https://blogs.msdn.microsoft.com/blogdoezequiel/2014/11/18/when-do-conversions-generate-conversion-warnings-and-why-are-these-bad/
22 ноя 16, 11:57    [19919851]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
а вообще конечно содомия, жирность линий впечатляет :) сплошное несовпадение типов...
22 ноя 16, 12:03    [19919894]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
o-o
Guest
TaPaK
Kos_Da,

https://blogs.msdn.microsoft.com/blogdoezequiel/2014/11/18/when-do-conversions-generate-conversion-warnings-and-why-are-these-bad/

у него там дата.
а дата обычно передается строкой, так что конверт там в тему
22 ноя 16, 12:06    [19919931]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
o-o
TaPaK
Kos_Da,

https://blogs.msdn.microsoft.com/blogdoezequiel/2014/11/18/when-do-conversions-generate-conversion-warnings-and-why-are-these-bad/

у него там дата.
а дата обычно передается строкой, так что конверт там в тему

угу
dateadd(day,CONVERT_IMPLICIT(int,[LOAN].[dbo].[CON_OPERATIONLOG].[OPERDATE] as [oplog].[OPERDATE]-(2.000000000000000e+000),0),'1900-01-01 00:00:00.000'),112)
22 ноя 16, 12:10    [19919956]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
Kos_Da
Member

Откуда:
Сообщений: 22
-жирность строк, потому что отчет.
-ACTIONDATE хранится во float и параметр в предикат приходит во float.
-recompile стоит для того, потому что фильтрованная статистика не используется.

- сам запрос выглядит так
exec sp_executesql N'select [ContractCreditId],[OperationLogID],[Status],[ActionID],[Amount],[Direction],[OperDate],[ActionDate],[UserLogin],[RollbackDate],[RollbackLogin],[StateID],[PaymentID],[MassProtocolID],[ReverseOperationFlag],[ProlongationFlag],[ActionType] from LOAN.dbo.vOperationLog_actiondate2 WHERE ActionDate2 >= @P1
option (maxdop 8, force order, loop join)', N'@P1 float',@P1='42692.052198881174000'

- где несовпадение типов сплошное? там только у двух таблиц маленьких несовпадение. всё остальные в норме.
22 ноя 16, 12:13    [19919975]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
TaPaK
Member

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

а loop join это такой вид садизма?
22 ноя 16, 12:14    [19919980]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
SomewhereSomehow
Member

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

Добрый день.

Очень похоже, на проблемы со статистикой.
- Проверьте, что точно идет обновление статистики в нужной таблице [CON_OPERATIONLOG] и нужной статистики (если вы обновляете конкретную статистику).
- Проверьте, что оно точно обновляется своевременно, т. е. никто не успевает туда налить новую порцию данных (судя по количеству строк в таблице 1 499 010 000 порция 2 090 583 строк похожа на новые добавленные данные, которых нет в гистограмме, и в целом на проблему возрастающих ключей)
- Проверьте, что значения отражены в гистограмме, т.е. может быть сделать обновление с fullscan.

Кроме того, даже если статистика у вас обновленная, судя по атрибутам в плане PlanGuideDB и PlanGuideName, используется руководство планов, если хотите чтобы план был перестроен по результатам обновленной статистки отключите руководство.
22 ноя 16, 12:44    [19920132]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
Kos_Da
Member

Откуда:
Сообщений: 22
SomewhereSomehow,
Спасибо!

Сейчас еще раз попробую на тестовом стенде пересчитать всю статистику с fullscan и запустить запрос.

План гайд добавляет хинты к запросу (maxdop 8), сам план не фиксированный. Это не должно мешать использовать обновленную статистику. Или всё таки мешает?
22 ноя 16, 13:34    [19920487]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
o-o
Guest
Kos_Da
сам план не фиксированный

думаете?
скажите, а что изменится в вашем случае,
когда сервер правильно оценит число строк?
вы же все равно ему прописали и именно этот порядок, и Nested Loops.
что в принципе может поменяться?
---
у вас не делают кластерные индексы и можно только лукапить,
т.к. альтернатива лукапу только скан огромных куч.
это так специально задумано?
22 ноя 16, 14:03    [19920694]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
SomewhereSomehow
Member

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

Если вы каким-то образом фиксируете план, то обновленную статистику он тоже сможет видеть, но план будет строить не на ее основе и оценках, а на основе заданной формы. Если же у вас просто добавляется хинт MAXDOP, то на выбор формы плана это должно влиять не больше, чем обычное ограничение степени параллелизма.
22 ноя 16, 14:16    [19920750]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 901
Kos_Da,

Вы точно дату передаете в условие?Она в статистике есть? Попробуйте для других данных , которые точно есть в статистике
Попробуйте выполнить код не как процедуру, а как запрос отдельно, посмотрите там план.
а так же выполнить не используя вьюху, а код вместо него.
22 ноя 16, 15:18    [19921105]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
Kos_Da
Member

Откуда:
Сообщений: 22
SomewhereSomehow
alexeyvg

большое спасибо! действительно дело оказалось все таки в статистике и помог принудительном пересчете fullscan по полю [ACTIONDATE].

возник вопрос:
фильтрованная статистика создается используя существующую статистику на столбце или заново пересчитывая данные?

просто я создавал фильтрованную статистику за необходимый период и это заняло 1 минуту
CREATE STATISTICS [s_f_CON_OPERATIONLOG_1] ON [dbo].[CON_OPERATIONLOG](ACTIONDATE) WHERE ACTIONDATE >= 42692 WITH FULLSCAN

а просто пересчет статистики на этом столбце update statistics [dbo].[CON_OPERATIONLOG] [ACTIONDATE] WITH FULLSCAN
занял полчаса.
22 ноя 16, 15:53    [19921361]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
Kos_Da
Member

Откуда:
Сообщений: 22
o-o,

периодически проскакивает TempDB Spills. подозреваю из-за неправильной статистики.
22 ноя 16, 16:26    [19921592]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
SomewhereSomehow
Member

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

Заново считает. Фильтрованная статистика, вещь интересная, можете поискать на Microsoft Connect или в Гугле разные забавные случаи с ней. Так что прежде всего, вам нужно убедиться, что она действительно используется оптимизатором. И потом, если вы создали только по одному полю, а есть еще какие-то предикаты в которых также не отражены последние значения, можете посмотреть, что будет если использовать их совместно, запустив простой пример.
+
create table t(a int, b int);
with cte(n) as (select top(1000) row_number() over(order by (select null)) from sys.all_columns) 
insert t select n, n from cte;
create index ix_a on t(a);
create statistics s_b on t(b);
update statistics t with fullscan;
go
set statistics profile on;
select * from t where t.b > 1000 and t.a > 1000 option(recompile);
set statistics profile off;
go
with cte(n) as (select top(10) row_number() over(order by (select null)) from sys.all_columns) 
insert t select n+1000, n+1000 from cte;
go
set statistics profile on;
select * from t where t.b > 1000 and t.a > 1000 option(recompile);
set statistics profile off;
go
create statistics sb on t(b) where b > 1000 with fullscan; -- one col
go
set statistics profile on;
select * from t where t.b > 1000 and t.a > 1000 option(recompile);
set statistics profile off;
go
create statistics sa on t(a) where a > 1000 with fullscan; -- another col
go
set statistics profile on;
select * from t where t.b > 1000 and t.a > 1000 option(recompile);
set statistics profile off;
go
drop table t;


-- Изначальный план, строк более 1000 нет, план правильный
StmtText
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|--Index Seek(OBJECT:([opt].[dbo].[t].[ix_a]), SEEK:([opt].[dbo].[t].[a] > (1000)) ORDERED FORWARD)
|--RID Lookup(OBJECT:([opt].[dbo].[t]), SEEK:([Bmk1000]=[Bmk1000]), WHERE:([opt].[dbo].[t].[b]>(1000)) LOOKUP ORDERED FORWARD)

-- Добавляем данные, автообновление не срабатывает т.к. не преодолен порог для него, план такой же
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|--Index Seek(OBJECT:([opt].[dbo].[t].[ix_a]), SEEK:([opt].[dbo].[t].[a] > (1000)) ORDERED FORWARD)
|--RID Lookup(OBJECT:([opt].[dbo].[t]), SEEK:([Bmk1000]=[Bmk1000]), WHERE:([opt].[dbo].[t].[b]>(1000)) LOOKUP ORDERED FORWARD)

-- Создаем фильтрованную статистику, но только по одному предикату - план все еще такой же
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|--Index Seek(OBJECT:([opt].[dbo].[t].[ix_a]), SEEK:([opt].[dbo].[t].[a] > (1000)) ORDERED FORWARD)
|--RID Lookup(OBJECT:([opt].[dbo].[t]), SEEK:([Bmk1000]=[Bmk1000]), WHERE:([opt].[dbo].[t].[b]>(1000)) LOOKUP ORDERED FORWARD)

-- Наконец добавляем по второму, план меняется
|--Table Scan(OBJECT:([opt].[dbo].[t]), WHERE:([opt].[dbo].[t].[b]>(1000) AND [opt].[dbo].[t].[a]>(1000)))

Короче, поэкспериментируйте. Может не обязательно делать фулскан, а задать какой-то процент, может вообще дефолтный подойдет, т.к. обычно граничные значения отражаются в гистограмме, возможно вам также поможет ссылка, которую я скинул в первом посте. Удачи.
22 ноя 16, 16:36    [19921632]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
o-o
Guest
Kos_Da
o-o,
периодически проскакивает TempDB Spills. подозреваю из-за неправильной статистики.

вы бы лучше кластерные на таблицы сделали,
да процедуру переписали.
у вас же повторяющиеся куски кода там,
почему не сохранить во временной таблице,
там ведь хорошие такие перелопачивания данных, не 2 строки.
любители CTE писали, наверное.

К сообщению приложен файл. Размер - 112Kb
22 ноя 16, 16:47    [19921697]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
TaPaK
Member

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

кстати, не пишете версию сервера, но можете попробовать Trace Flag 4199
22 ноя 16, 19:21    [19922352]     Ответить | Цитировать Сообщить модератору
 Re: estimated and actual rows разницы  [new]
Kos_Da
Member

Откуда:
Сообщений: 22
TaPaK,
Trace Flag 4199 включен на уровне экземпляра.

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

всем еще раз спасибо за помощь.
23 ноя 16, 11:06    [19923970]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить