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

Откуда: большая деревня
Сообщений: 266
Интересует, почему оптимизатор неверно оценивает количество строк при чтении из индекса IX_Agent (оценка 3 строки, фактически 400 тыс.). Функция на основе данных в исторической таблице выводит агрегированные данные с разбивкой по дням.

Текст функции:
+
with cte as (
	select 
			StartTime
			,EndTime
			,StartDate
			,EndDate
			,operator
			,campaign

			,StateGroup
			,StateTotal


	FROM [dbo].[AgentActivity] A

	where EndTime >= @Date_Start and StartTime < @Date_End + 1
		and EndTime < @Date_End + 2
)


select	H.Interval
		,C.operator
		,sum(case when StateTotal = 'Total' then 
				datediff(ss
					,case when StartTime > Interval and StartTime < dateadd(day, 1, Interval) then StartTime
						else Interval
					end
					,case when EndTime < dateadd(day, 1, Interval) then EndTime
						else dateadd(day, 1, Interval)
					end
				) end) as Total

		,sum(case when StateGroup = 'Paused' then 
				datediff(ss
					,case when StartTime > Interval and StartTime < dateadd(day, 1, Interval) then StartTime
						else Interval
					end
					,case when EndTime < dateadd(day, 1, Interval) then EndTime
						else dateadd(day, 1, Interval)
					end
				) end) as Paused



from cte C
outer apply (
	select Interval
	from [dbo].[CalendarDays]
	where Interval >= StartDate
		and Interval <= EndDate
) H

where H.Interval >= @Date_Start and H.Interval < @Date_End + 1
group by operator, Interval

)



[dbo].[AgentActivity] - представление:
+
CREATE view [dbo].[AgentActivity] WITH SCHEMABINDING
AS

SELECT D.StartTime
		,D.EndTime

		,cast(D.StartTime as date) as StartDate
		,cast(D.EndTime as date) as EndDate
		,LA.operator
		,LC.campaign
		,LT.Call_Type
		,D.ContactID
		,S.State
		,S.DescCode_ForAgentView as OperatorState
		,case when S.State = 20 then 'Paused'
			when S.State = 30 then 'AdmWk'
			when S.State = 40 then 'Waiting'
			when S.State = 50 then 'WrapUp'
			when S.State in (70, 90) then 'Ringing'
			when S.State in (80, 100) then 'Talking'
		end as StateGroup

		,case when S.State >= 20 then 'Total' end as StateTotal

from [dbo].[TIMESTATS] D
inner join [dbo].[TIMESTATS_STATUSES] S
	on S.[State] = D.[State]
inner join (
	select	id
			,Names2 as operator
	from dbo.TIMESTATS_LOOKUP 
	where [Type] = 'A'
) LA
	on LA.id = D.agent
left join (
	select id
			,Names2 as campaign
	from dbo.TIMESTATS_LOOKUP 
	where [Type] = 'C'
) LC
	on LC.id = D.Campaign
left join (
	select id
			,Names2 as Call_Type
	from dbo.TIMESTATS_LOOKUP 
	where [Type] = 'T'
) LT
	on LT.id = D.ContactType


Индекс
+
CREATE NONCLUSTERED INDEX [IX_Agent] ON [dbo].[TIMESTATS]
(
	[Agent] ASC,
	[StartTime] ASC,
	[EndTime] ASC
)
INCLUDE ( 	[State])
1 фев 16, 14:50    [18756821]     Ответить | Цитировать Сообщить модератору
 Re: Неверная оценка оптимизатора  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
План запроса

К сообщению приложен файл (fnAgentActivity_test.zip - 9Kb) cкачать
1 фев 16, 14:52    [18756829]     Ответить | Цитировать Сообщить модератору
 Re: Неверная оценка оптимизатора  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Статистика когда последний раз обновлялась?
1 фев 16, 15:18    [18757032]     Ответить | Цитировать Сообщить модератору
 Re: Неверная оценка оптимизатора  [new]
смотрю_тут
Member

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

скорее всего из-за вашей функции.
попробуйте запрос сделать без функции, посмотрите план, думаю будет нормальным,
Да и все равно с функцией у вас будет nestedloops, при больших данных использований функций губительно на производительности.
1 фев 16, 15:23    [18757074]     Ответить | Цитировать Сообщить модератору
 Re: Неверная оценка оптимизатора  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
AlanDenton, статистика актуальная, полный просмотр.


смотрю_тут,
без функции ойенка 11 строк, план превращается в параллельный, но по времени особой разницы не заметил.
1 фев 16, 15:43    [18757198]     Ответить | Цитировать Сообщить модератору
 Re: Неверная оценка оптимизатора  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Параллельный план

К сообщению приложен файл (sqlAgentActivity_test.zip - 14Kb) cкачать
1 фев 16, 15:44    [18757203]     Ответить | Цитировать Сообщить модератору
 Re: Неверная оценка оптимизатора  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
смотрю_тут
Да и все равно с функцией у вас будет nestedloops, при больших данных использований функций губительно на производительности.

Так именно в этом случае какая разница функция или нет. nestedloops в данном случае из-за неравенства (мне так кажется).

Я не буду эту функцию соединять в запросах через apply. Результат функции будет записываться во временную таблицу, а таблица уже в других соединениях. В данном случае выбрал функцию вместо процедуры, т.к. количество столбцов (статусов) может меняться, и с функцией не надо искать потом по десяткам баз данных, где отвалилось.
1 фев 16, 15:50    [18757243]     Ответить | Цитировать Сообщить модератору
 Re: Неверная оценка оптимизатора  [new]
o-o
Guest
virtuOS
Интересует, почему оптимизатор неверно оценивает количество строк при чтении из индекса IX_Agent (оценка 3 строки, фактически 400 тыс.)

вы что-то не то читаете.
он оценил IX_Agent в 2553 строк, а не в 3,
т.е. по 3 строки на 1 итерацию, к-ых, он считал, будет 851
(3 х 851 = 2553)
но оказалось 840 строк в ведущей таблице и по 467 строк нижней таблицы в среднем на итерацию
1 фев 16, 16:10    [18757358]     Ответить | Цитировать Сообщить модератору
 Re: Неверная оценка оптимизатора  [new]
SomewhereSomehow
Member

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

Добрый день.

Скорее всего, дело в оценке соединения. Правильно оценить соединения, оперируя статистиками таблиц, довольно нетривиальная задача, особенно, если есть еще и независимые предикаты по другим колонкам. К сожалению, с такой задачей оптимизатор справляется не всегда хорошо до сих пор, даже в 2014 сервере и новой модели оценки, которая используется у вас.

Вы можете попробовать помочь ему создав фильтрованную статистику по колонке ID с фильтром по Type = A, типа такого:

create statistics sf_id on TIMESTATS_LOOKUP(ID) where Type = 'A';


После этого убедитесь, что статистики на TIMESTATS колонке agent действительно обновлены. Специально пишу статистики, т.к. иногда бывает, обновляют одну, а используется совсем другая. Если не хочется выяснять какая статистика используется точно, для верности, обновите все.

После этого попробуйте построить план заново.

Если это не поможет, посмотрите, как с этим справляется старая модель оценки, для этого, допишите в конце запроса option(querytraceon 9481). Иногда, она справляется лучше.

Если ничего из этого не помогло, то остается, вариант, разбивать запрос на несколько и класть во временные таблицы, либо прибивать жестко при помощи хинтов.
1 фев 16, 16:43    [18757613]     Ответить | Цитировать Сообщить модератору
 Re: Неверная оценка оптимизатора  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
SomewhereSomehow,

спасибо, интересное решение. Как вообще можно было подумать создать статистику на таблицу TIMESTATS_LOOKUP, если по ней как раз оценка была верной? Интересен ваш ход мыслей.

После создания статистики план круто поменялся, теперь используется поиск по кластерному индексу (IX_TIMESTATS_ENDTIME) вместо некластерного. Стало в несколько раз быстрее, и даже чтение с диска не зашкаливает, но всё равно думаю посмотреть некоторое время, лучше ли такое решение для сервера ))).

К сообщению приложен файл (fnAgentActivity_test параллельный.zip - 14Kb) cкачать
1 фев 16, 17:17    [18757876]     Ответить | Цитировать Сообщить модератору
 Re: Неверная оценка оптимизатора  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
SomewhereSomehow,

Позвольте ещё вопрос. Сделал процедуру, внутри которой поместил эту самую табличную функцию fnAgentActivity
ALTER PROCEDURE [dbo].[sp_AgentActivityOper] 
@Date_Start datetime, 
@Date_End datetime
AS
BEGIN
SET NOCOUNT ON;

declare @AgentActivity table (...)

insert into @AgentActivity (...)
select ...
from CRM_STATS.dbo.fnAgentActivity (@Date_Start, @Date_End)

/*
 Тут ещё простое соединение с пары таблиц с времянкой @AgentActivity
*/

END


Почему в данном случае оптимизатор упорно не желает строить параллельный план, даже если на выходе несколько миллионов строк? Это вряд-ли из-за прослушки параметров, т.к. указывал option(recompile).

К сообщению приложен файл (fnAgentActivity_test процедура.zip - 24Kb) cкачать
1 фев 16, 17:39    [18758042]     Ответить | Цитировать Сообщить модератору
 Re: Неверная оценка оптимизатора  [new]
SomewhereSomehow
Member

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

Ход мыслей примерно такой, если посмотреть на оценки в плане – вы правы, наибольшее расхождение там, где вы сказали. Далее из того итератора, где оценка расходится – видно, что есть несколько предикатов – один из которых column=column остальные сравнение с известной константой по независимой от соединения колонке. Далее возникает подозрение, что предикаты соединения могут сильно искажать базовую статистику.

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

Представим, что у нас есть коробки с номерами, и шары с номерами, каждая коробка имеет свой номер, каждый шар имеет свой номер, кроме того каждый шар имеет свой цвет.

(Аналогия: коробки и шары – таблицы, номера или цвета – столбцы)

Нам известны номера коробок.
Картинка с другого сайта.

Нам известны номера шаров:
Картинка с другого сайта.

Нам известны цвета шаров:
Картинка с другого сайта.
(Аналогия: нам известны гистограммы распределения по каждому столбцу – сколько штук каждого номера и цвета)

Задача 1:
Определить, сколько коробок будет заполнено, если каждый шар положить в коробку со своим номером.

(Аналогия: t1 join t2 on t1.id = t2.id)

Очевидно, что мы знаем номера шаров и номера коробок – в результате 2 коробки:
Картинка с другого сайта.

Задача 2:
Определить, сколько коробок будет заполнено, если каждый красный шар положить в коробку со своим номером.

(Аналогия: t1 join t2 on t1.id = t2.id where t1.color = ‘Red’)

И тут возникает проблема. Да мы знаем сколько каждого номеров, да мы знаем сколько каждого цветов, но мы не знаем, сколько каждого номера каждого цвета.

Раскрасим так:
Картинка с другого сайта.
Будет ответ такой же 2 коробки.

Раскрасим так:
Картинка с другого сайта.
Будет ответ 0 коробок.

Т.е. разброс оценки может быть от 0 (1) строк, до сколько угодно, т.к. у нас нет коррелированной гистограммы.

Если я правильно помню, в старом Cardinality Estimator независимые фильтры моделировались, как умножение средней вероятности для каждого шага гистограммы на вероятность независимого предиката, это было возможно из-за предположения о равномерности внутри гистограммы и всей таблицы. В новом – не проверял, но должно быть подобное, т.к. коррелированных гистограмм по-прежнему нет – данные взять все еще неоткуда.

Сделав фильтрованную статистику, вы сказали серверу примерно следующее: «не знаю, сколько каких шаров в коробке, но вот в этой коробке красных шаров столько-то и они имеют следующие номера…». Т.е. вы заранее выбрали все красные шары и построили статистику по их номерам (create statistics S on Box(ID) where Color = ‘Red’), чем помогли серверу оценить соединение для запроса в котором спрашивается соединение по красным шарам.

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

автор
Почему в данном случае оптимизатор упорно не желает строить параллельный план

Вы вставляете в табличную переменную, это один из запрещающих параллелизм факторов в MS SQL, замените ее на временную таблицу. У меня кстати есть доклад про оптимизатор и параллелизм, там есть этот пример на эту тему, можете глянуть, если интересно: https://channel9.msdn.com/Events/SQL-Saturday/SQL-Saturday-Moscow-2015/Hall-C-Inside-the-query-optimizer-concurrency-Part-1
1 фев 16, 19:54    [18758490]     Ответить | Цитировать Сообщить модератору
 Re: Неверная оценка оптимизатора  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
SomewhereSomehow,

Спасибо за подробные ответы.

SomewhereSomehow
Если я правильно помню, в старом Cardinality Estimator независимые фильтры моделировались, как умножение средней вероятности для каждого шага гистограммы на вероятность независимого предиката, это было возможно из-за предположения о равномерности внутри гистограммы и всей таблицы. В новом – не проверял, но должно быть подобное, т.к. коррелированных гистограмм по-прежнему нет – данные взять все еще неоткуда.

Если не ошибаюсь, в каком-то достаточно старом докладе на techdays проскакивала информация, что для нескольких параметров оптимизатор оценивает итоговую вероятность как корень из произведения вероятностей для каждого предиката. Объяснялось это тем, что обычно условия, по которым отбиратся данные, коррелированы между собой, и простое произведение дает заниженную оценку. А вот корень из произведения в большинстве случаев ближе к истинному распределению.
2 фев 16, 11:11    [18760361]     Ответить | Цитировать Сообщить модератору
 Re: Неверная оценка оптимизатора  [new]
SomewhereSomehow
Member

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

Может быть, даже в моем, хотя не знаю, есть ли тот мой доклад на techdays.

В случае нескольких предикатов в Cardinality Estimator 70 (CE до 2014) использовалось предположение о независимости предикатов. И для вычисления общей селективности сложного предиката, селективности каждого простого предиката по умолчанию перемножались, что на практике оказывалось не всегда верно, т.к. часто получается, что данные в колонках между собой связаны.

В CE 120 использовать этот алгоритм по умолчанию перестали, вместо него стали использовать алгоритм, который называется Exponential Backoff, суть в том, что каждый последующий простой предикат в комплексном вносит все меньший и меньший вклад в общую селективность.

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

Главное просто знать то, что базовая гистограмма при переходе от одного оператора плана к другому изменяется в зависимости от условий и эта измененная гистограмма используется для оценок. Но поскольку для изменений до сих пор используются предположения, догадки и теория вероятности – эти изменения и оценка на их основе может сильно отличаться от реальности, что может приводить к неточностям.
2 фев 16, 11:42    [18760637]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить