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

Откуда: большая деревня
Сообщений: 266
Есть довольно простая скалярная функция:
ALTER function [dbo].[original_fnTimeStats_Session_ValueAtPos](
	@session int,
	@pos datetime, 
	@agent int, 
	@group varchar(max), 
	@cmp int, 
	@direction char, 
	@contactType int, 
	@contactID uniqueidentifier
) returns tinyInt
as begin

	return (
		select top 1 State 
			from [CRM_STATS_BACKUP].[dbo].TIMESTATS_SESSION_DETAILS
			where StartTime <= @pos 
				and EndTime > @pos 
				and IsNull(agent, 0) = IsNull(@agent, 0)
				and Session = @session
		)

end

Переписал её на TVF.
Функция вызывается в процедуре множество раз таким образом:
--set @c = [CRM_STATS_BACKUP].[dbo].fnTimeStats_Session_ValueAtPos(@session, @pos, @agent, @group, @cmp, @direction, @contactType, @contactID)
set @c = ( select state from [CRM_STATS_BACKUP].[dbo].fnTimeStats_Session_ValueAtPos_fn(@session, @pos, @agent, @group, @cmp, @direction, @contactType, @contactID))


Месяца два всё было хорошо, даже был эффект TVF. А сегодня начались проблемы. Батч, который ранее отрабатывал за 3-5 секунд, стал работать около 50 секунд. Загрузка ЦП при этом держится на 20-30%. Мониторингом пришел к выводу, что проблема в этой функции. Заменил на скалярку, и вновь всё стало хорошо.

По мониторингу видно, что висит тип ожидания либо CXPACKET, либо EXECSYNC. В плане запроса есть параллелизм. Но, что интересно, план не менялся, ранее также был параллелизм. И план скалярки также с параллелизмом по тому же индексу и предикатами.

Каким образом можно повлиять на план запроса? Хотел попробовать прописать option (maxdop 1), но в функции видимо это сделать нельзя, вываливается синтаксическая ошибка. В вызове функции тоже ошибка синтаксиса.

Может ли быть косвенной причиной то, что данная база включена в Always ON с синхронным коммитом. На этой неделе добавили третий узел (до этого было два узла).
30 сен 15, 11:22    [18213581]     Ответить | Цитировать Сообщить модератору
 Re: TVF и ожидания CXPACKET, EXECSYNC  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31951
virtuOS
В плане запроса есть параллелизм. Но, что интересно, план не менялся, ранее также был параллелизм. И план скалярки также с параллелизмом по тому же индексу и предикатами.
Желательно более предметно обсуждать, приложив планы с актуальной статистикой выполнения.

И вообще, откуда и зачем тут "параллелизм"? Тут должен быть поиск по инедксу на таблице TIMESTATS_SESSION_DETAILS (видимо, на поля Session, EndTime, StartTime desc, INCLUDE @agent ?)
30 сен 15, 11:36    [18213696]     Ответить | Цитировать Сообщить модератору
 Re: TVF и ожидания CXPACKET, EXECSYNC  [new]
Сид
Member

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

А зачем такая куча входных параметров, если используются всего 3?
Top 1 без order by звучит странно: нужно значение State из рандомной строки результата?

Бывает ли, что @agent is null? Если нет, избавляйтесь от isnull в условии where. А если бывает, лучше 2 функции написать: с @agent и без него.

Судя по тому, что включается параллелизм, тут явно что-то не так с индексом (происходит сканирование)?
30 сен 15, 11:44    [18213757]     Ответить | Цитировать Сообщить модератору
 Re: TVF и ожидания CXPACKET, EXECSYNC  [new]
Сид
Member

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

В студию структуру таблички TIMESTATS_SESSION_DETAILS (с индексами плз) и примеры, как именно множество раз вызывается функция. Для параллелизма я бы понял, если б было не set @c=(...), а вызов внутри select
30 сен 15, 11:46    [18213776]     Ответить | Цитировать Сообщить модератору
 Re: TVF и ожидания CXPACKET, EXECSYNC  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Немного опишу ситуацию.
Есть историческая CRM таблица с событиями и их датами. Разработчики софта написали ряд процедур и функций, которые заполняют данными таблицу TIMESTATS_SESSION_DETAILS (для последующего формирования отчетов). Работает всё это в job-е, который берет последние 100 записей, обрабатывает их в цикле поштучно (в цикле while) и пишет данные в таблицу TIMESTATS_SESSION_DETAILS. В итоге на эти 100 записей получается от 100 до 500 операций вставки/обновления.

Скрипты таблицы:
+
CREATE TABLE [dbo].[TIMESTATS_SESSION_DETAILS](
	[DetailID] [bigint] IDENTITY(1,1) NOT NULL,
	[Session] [int] NOT NULL,
	[StartTime] [datetime] NOT NULL,
	[EndTime] [datetime] NULL,
	[State] [tinyint] NULL,
	[Agent] [int] NULL,
	[AgentGroup] [int] NULL,
	[Campaign] [int] NULL,
	[Direction] [char](1) NULL,
	[ContactType] [int] NULL,
	[ContactID] [uniqueidentifier] NULL,
	[TS] [timestamp] NOT NULL,
 CONSTRAINT [PK_TIMESTATS_SESSION_DETAILS] PRIMARY KEY NONCLUSTERED 
(
	[DetailID] 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

/****** Object:  Index [IX_TIMESTATS_SESSION_DETAILS_ENDTIME]    Script Date: 30.09.2015 12:21:20 ******/
CREATE CLUSTERED INDEX [IX_TIMESTATS_SESSION_DETAILS_ENDTIME] ON [dbo].[TIMESTATS_SESSION_DETAILS]
(
	[EndTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_TIMESTATS_SESSION_DETAILS_CONTACTID]    Script Date: 30.09.2015 12:21:20 ******/
CREATE NONCLUSTERED INDEX [IX_TIMESTATS_SESSION_DETAILS_CONTACTID] ON [dbo].[TIMESTATS_SESSION_DETAILS]
(
	[ContactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_TIMESTATS_SESSION_DETAILS_STARTTIME]    Script Date: 30.09.2015 12:21:20 ******/
CREATE NONCLUSTERED INDEX [IX_TIMESTATS_SESSION_DETAILS_STARTTIME] ON [dbo].[TIMESTATS_SESSION_DETAILS]
(
	[StartTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_TIMESTATS_SESSION_DETAILS_TS]    Script Date: 30.09.2015 12:21:20 ******/
CREATE NONCLUSTERED INDEX [IX_TIMESTATS_SESSION_DETAILS_TS] ON [dbo].[TIMESTATS_SESSION_DETAILS]
(
	[TS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

План TVF в аттаче. План скалярки сложнее вытащить, и совершенно невозможно обеспечить те же самые параметры.
Индекс не создавал осознанно, т.к. не хотел менять структуру таблицы, созданной разработчиками, а ещё много инсертов и апдейтов на таблице, да и место на диске экономлю. Кроме того, поиск по дате ENDTIME осуществляется не более чем за последние 24 часа, а в среднем за последний час. При текущей скорости роста таблицы (около 300 тыс. записей в день) мне кажется не много. Но если других идей не будет, индекс создам и проверю.


Вызов функции осуществляется в процедуре, а сама процедура вызывается несколько раз для каждой строки исходных данных.

+
ALTER procedure [dbo].[spTimeStats_Session_PaintOverLower]
		@session int,
        @lowerThanState tinyint,
		@dtFrom datetime,
		@dtTo datetime,
		@paintWithState tinyint,
		@agent int,
		@group varchar(max),
		@cmp int,
		@direction char,
		@contactType int,
		@contactID uniqueidentifier,
		@debug tinyint  -- 1=on
as begin
	if @debug <> 0 
		print 'PaintOverLower: ' + IsNull(convert(varchar(max), @dtFrom, 120), 'null') + 
			' - ' + IsNull(convert(varchar(max), @dtTo, 120), 'null') + 
			' lower than ' + IsNull(convert(varchar(max), @lowerThanState), 'null') +
			' with ' + IsNull(convert(varchar(max), @paintWithState), 'null') +
			' for a=' + IsNull(Convert(varchar(max), @agent), 'null') +
			' g=' + IsNull(@group, 'null') +
			' c=' + IsNull(Convert(varchar(max), @cmp), 'null') +
			' d=' + IsNull(@direction, 'null') +
			' t=' + IsNull(convert(varchar(max), @contactType), 'null') +
			' id=' + IsNull(convert(varchar(max), @contactID), 'null') +
			' sid=' + IsNull(convert(varchar(max), @session), 'null')
		
	set @dtFrom = IsNull(@dtFrom, DateAdd(hour, -24, @dtTo))
	set @dtTo = IsNull(@dtTo, DateAdd(hour, 24, @dtFrom))
		
	if @dtTo <= @dtFrom begin
		if @dtTo < @dtFrom 
			print 'PaintOverLower WARNING: dtTo < dtFrom. ContactID=' + IsNull(convert(varchar(max), @contactID), 'null')
		return
	end
		
-- TODO: handle overlapping states		
		
	declare @pos datetime, @pos2 datetime, @c tinyInt, @c2 tinyInt, @i int
	set @pos = @dtFrom
	set @i = 0
	while (@pos < @dtTo) 
		begin
			set @i = @i + 1

            set @c = [CRM_STATS_BACKUP].[dbo].fnTimeStats_Session_ValueAtPos(@session, @pos, @agent, @group, @cmp, @direction, @contactType, @contactID)
	--		set @c = ( select state from [CRM_STATS_BACKUP].[dbo].fnTimeStats_Session_ValueAtPos_fn(@session, @pos, @agent, @group, @cmp, @direction, @contactType, @contactID))
			set @pos2 = [CRM_STATS_BACKUP].[dbo].fnTimeStats_Session_FindEnd(@session, @pos, @c, @agent, @group, @cmp, @direction, @contactType, @contactID)
			if @debug <> 0
				print 'PaintOverLower loop:' +
					' pos=' + IsNull(Convert(varchar(max), @pos, 120), 'null') + 
					' clr=' + IsNull(Convert(varchar(max), @c), 'null') + 
					' pos2=' + IsNull(Convert(varchar(max), @pos2, 120), 'null')
--todo: grab multiple segments at once, if they are all lower than our state					
			if (@pos2 is null) or (@pos2 > @dtTo) 
				set @pos2 = @dtTo
			if IsNull(@c, 0) < @lowerThanState 
				execute [CRM_STATS_BACKUP].[dbo].spTimeStats_Session_Paint @session, @pos, @pos2, @paintWithState, @agent, @group, @cmp, @direction, @contactType, @contactID, @debug
			set @pos = @pos2 
			if @i >= 10000 begin /* just in case we deadlock in this loop*/ 
				print 'PaintOverLower ERROR: over 1000 iterations' + 
					'; ' + IsNull(convert(varchar(max), @dtFrom, 120), 'null') + 
					' - ' + IsNull(convert(varchar(max), @dtTo, 120), 'null') + 
					' lower than ' + IsNull(convert(varchar(max), @lowerThanState), 'null') +
					' with ' + IsNull(convert(varchar(max), @paintWithState), 'null') +
					' for a=' + IsNull(Convert(varchar(max), @agent), 'null') +
					' g=' + IsNull(@group, 'null') +
					' c=' + IsNull(Convert(varchar(max), @cmp), 'null') +
					' d=' + IsNull(@direction, 'null') +
					' t=' + IsNull(convert(varchar(max), @contactType), 'null') +
					' id=' + IsNull(convert(varchar(max), @contactID), 'null') +
					' sid=' + IsNull(convert(varchar(max), @session), 'null')
				break
			end
		end
end		


Я понимаю, что так писать нельзя, SQL не процедурный язык, но кардинально менять подход на данный момент не рентабельно.

К сообщению приложен файл (TVF.sqlplan - 26Kb) cкачать
30 сен 15, 12:58    [18214307]     Ответить | Цитировать Сообщить модератору
 Re: TVF и ожидания CXPACKET, EXECSYNC  [new]
Владислав Колосов
Member

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

Вы ходите вокруг да около, покажите пример, который можно протестировать, а не какие-то вырванные из контекста цитаты. Если скалярная функция устраивает, то не надо ничего менять.
30 сен 15, 13:05    [18214347]     Ответить | Цитировать Сообщить модератору
 Re: TVF и ожидания CXPACKET, EXECSYNC  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Владислав Колосов,
я не представляю, как можно сформировать тестовый набор данных. Скрипт только создания всех нужных объектов содержит около 2000 строк кода, к которым ещё нужно сформировать набор данных. Неужели кто во всём этом будет детально разбираться? Я надеялся на какой-нибудь простой совет или направление для дальнейших изысканий. Может, я упускаю какой-то очевидный для других момент.
30 сен 15, 13:35    [18214501]     Ответить | Цитировать Сообщить модератору
 Re: TVF и ожидания CXPACKET, EXECSYNC  [new]
Сид
Member

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

попробуйте создать индекс, предложенный alexeyvg, но в include добавить ещё и state. Не могу сказать, куда лучше поместить agent: в ключ или в include, можно попробовать оба варианта и сравнить.
И найдите по форуму или в интернете скрипт, который отображает неиспользуемые индексы. Посмотрите в результат выполнения, может имеет смысл грохнуть лишнее.
30 сен 15, 13:37    [18214515]     Ответить | Цитировать Сообщить модератору
 Re: TVF и ожидания CXPACKET, EXECSYNC  [new]
Владислав Колосов
Member

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

а проблема-то в чем, зачем что-то менять? Вы всё равно результат запроса превращаете в константу.
30 сен 15, 14:00    [18214641]     Ответить | Цитировать Сообщить модератору
 Re: TVF и ожидания CXPACKET, EXECSYNC  [new]
virtuOS
Member

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

Владислав Колосов,
проблема в том, что обработка перестала успевать за поступающими данными. Путем замены всех скалярок на табличные функции удалось повысить скорость обработки в 2-3 раза. А через два месяца вдруг резко начались проблемы. Причем график длительности одного пакета обработки стал сильно колебаться: 58 сек, 64, 51, 63, 50, 24, 4 ,4 ,4 ,7 ,6 ,34 ,53 ,42 ,45.
Складывается впечатление, что помимо ожиданий CXPACKET, EXECSYNC обработку ещё что-то держит. Но блокировок вроде бы нет.
Если что, мониторю скриптом от DeColo®es

И ещё раз хотел уточнить, нет ли тут связи с добавлением третьего узла в группу доступности, включенный два дня назад. Из sys.dm_hadr_database_replica_states log_send_queue_size всегда держится около 0, log_send_rate около 30 тысяч. Вроде немного и есть базы с большим объемом передаваемых данных.
30 сен 15, 14:58    [18215098]     Ответить | Цитировать Сообщить модератору
 Re: TVF и ожидания CXPACKET, EXECSYNC  [new]
Сид
Member

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

Какова характеристика нагрузки на сервер? Много коротких транзакций, обработка в основном менее 5000 строк за раз? Или, может, преимущественно аналитика с агрегацией больших объёмов данных? Если я правильно понял, то первое. И в этом случае лучше тупо установить MAXDOP 1 на уровне экземпляра, и забыть про CXPACKET как про страшный сон. Да и оптимизатору не придётся думать, стоит ли строить параллельный план: ответ всегда будет "нет". :)
Правда, без нормального индекса, о котором тут не раз говорили, производительность после такого шага просядет. Сейчас всё выезжает только за счёт параллелизма, хотя, по сути, он не нужен для таких запросов. С хорошим индексом в плане будет Index Seek и почти нулевое время выполнения запроса.

Так что индексы - это не "один из вариантов оптимизации", а "первый и наиважнейший вариант оптимизации".
30 сен 15, 16:16    [18215699]     Ответить | Цитировать Сообщить модератору
 Re: TVF и ожидания CXPACKET, EXECSYNC  [new]
Владислав Колосов
Member

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

автор
Путем замены всех скалярок на табличные функции удалось повысить скорость обработки в 2-3 раза

Не имеет никакого значения такая замена, если результат выборки записывается в переменную. Дело в другом совершенно.
30 сен 15, 18:03    [18216605]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить