ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / SSIS. Увеличилось время работы Data Flow Task.
25 сообщений из 38, страница 1 из 2
  1  все
SSIS. Увеличилось время работы Data Flow Task.
    #39789718
Фотография Yagrus2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yagrus2
Участник
Hello World!
На SQL-сервере опубликован SSIS-пакет. В определенный момент время его выполнения увеличилось на порядок.
Выяснил, что зависает Data Flow Task.
У него такая структура:
1. Источник - OLE DB SOURCE, Date Access mode - SQL Command, Сам скрипт - процедура с тремя параметрами, которая возвращает набор записей.
Код: sql
1.
2.
SET  FMTONLY OFF;
EXEC [etl].sp_Get_Sale ?, ?, ?


2. Derived Column Transformation - добавляет несколько полей с константами.
3. Назначение - OLE DB DESTINATION, Sql-таблица

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

Пробовал разобраться с помощью профайлера. Из его лога видно, что в определенный момент все транзакции прекращаются.

Рестарт сервера не помог.
Подскажите, в каком направлении двигаться, чтобы определить из-за чего появляются зависания.
...
Рейтинг: 0 / 0
21.03.2019, 16:16
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39789730
.Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.Евгений
Участник
Yagrus2,

Если без экзотики, то причина может быть либо в процедуре (SSIS не при чем), либо в задержке при логировании SSIS (смотрите активность в БД SSISDB или как там она у вас называется).
...
Рейтинг: 0 / 0
21.03.2019, 16:38
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39789737
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Yagrus2,

кто же знает, может у вас журнал или база расширяются.
...
Рейтинг: 0 / 0
21.03.2019, 16:49
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39789750
Фотография Yagrus2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yagrus2
Участник
Запуск процедуры из Managerment Studuo отрабатывает без задержек.
Вот логи SSISDB
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT  operation_id, 
		message_time,
		message_type, 
		message_source_type,
		message, 
		package_name, 
		event_name, 
		message_source_name, 
		subcomponent_name, 
		package_path 
FROM	[catalog].[event_messages] msg
WHERE	package_name = 'Sale.dtsx' AND
		message_time BETWEEN '2019-03-16' AND '2019-03-19 23:59:59' AND
		execution_path LIKE '%EXTRACT\SOURCES\UT\%' AND
		message_source_name = 'UT Sales' AND
		operation_id = 95166



operation_id message_time message_type message_source_type message package_name event_name message_source_name subcomponent_name package_path95166 2019-03-17 01:32:36.5983684 +03:00 10 40 UT Sales:Validation has started. Sale.dtsx OnPreValidate UT Sales NULL \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 01:32:36.5993685 +03:00 70 40 UT Sales:Information: Validation phase is beginning. Sale.dtsx OnInformation UT Sales SSIS.Pipeline \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 01:32:58.1955279 +03:00 20 40 UT Sales:Validation is complete. Sale.dtsx OnPostValidate UT Sales NULL \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 01:33:00.4267510 +03:00 10 40 UT Sales:Validation has started. Sale.dtsx OnPreValidate UT Sales NULL \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 01:33:00.4277511 +03:00 70 40 UT Sales:Information: Validation phase is beginning. Sale.dtsx OnInformation UT Sales SSIS.Pipeline \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 01:33:23.9110992 +03:00 20 40 UT Sales:Validation is complete. Sale.dtsx OnPostValidate UT Sales NULL \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 01:33:25.3312412 +03:00 30 40 UT Sales:Start 1:33:25 AM. Sale.dtsx OnPreExecute UT Sales NULL \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 01:33:25.3312412 +03:00 10 40 UT Sales:Validation has started. Sale.dtsx OnPreValidate UT Sales NULL \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 01:33:25.3342415 +03:00 70 40 UT Sales:Information: Validation phase is beginning. Sale.dtsx OnInformation UT Sales SSIS.Pipeline \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 01:33:47.2844363 +03:00 20 40 UT Sales:Validation is complete. Sale.dtsx OnPostValidate UT Sales NULL \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 01:33:47.2884367 +03:00 70 40 UT Sales:Information: Prepare for Execute phase is beginning. Sale.dtsx OnInformation UT Sales SSIS.Pipeline \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 01:33:47.2964375 +03:00 70 40 UT Sales:Information: Pre-Execute phase is beginning. Sale.dtsx OnInformation UT Sales SSIS.Pipeline \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 09:30:59.2061931 +03:00 70 40 UT Sales:Information: Execute phase is beginning. Sale.dtsx OnInformation UT Sales SSIS.Pipeline \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 09:31:02.3971931 +03:00 70 40 UT Sales:Information: Post Execute phase is beginning. Sale.dtsx OnInformation UT Sales SSIS.Pipeline \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 09:31:02.4061931 +03:00 70 40 UT Sales:Information: "Staging Table" wrote 80277 rows. Sale.dtsx OnInformation UT Sales SSIS.Pipeline \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 09:31:02.4091931 +03:00 70 40 UT Sales:Information: Cleanup phase is beginning. Sale.dtsx OnInformation UT Sales SSIS.Pipeline \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 09:31:02.4121931 +03:00 40 40 UT Sales:Finished 9:31:02 AM Elapsed time: 07:57:37.087. Sale.dtsx OnPostExecute UT Sales NULL \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 09:31:02.3911931 +03:00 70 60 UT Sales:Information: The final commit for the data insertion in "Staging Table" has started. Sale.dtsx OnInformation UT Sales Staging Table [28] \Package\EXTRACT\SOURCES\UT\UT Sales95166 2019-03-17 09:31:02.3941931 +03:00 70 60 UT Sales:Information: The final commit for the data insertion in "Staging Table" has ended. Sale.dtsx OnInformation UT Sales Staging Table [28] \Package\EXTRACT\SOURCES\UT\UT Sales
...
Рейтинг: 0 / 0
21.03.2019, 17:17
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39789768
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Yagrus2Из его лога видно, что в определенный момент все транзакции прекращаются.Да, интересно, Pre-Execute занял 8 часов.
...
Рейтинг: 0 / 0
21.03.2019, 17:50
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39789778
.Евгений
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.Евгений
Участник
Yagrus2,

возможно, на стороне источника была заблокирована проверка метаданных, возвращаемых вашей процедурой. Мониторьте сервера ETL и источников во время выполнения. Лукапов вроде бы у вас нет (начитка данных для их наполнения происходит в этой же фазе).
...
Рейтинг: 0 / 0
21.03.2019, 18:02
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39789921
Ferdipux
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ferdipux
Гость
Yagrus2,

Лог работы пакета наводит на определенные мысли... Из опыта - когда долго происходит валидация и PreExecution, рекомендации следующие (может сработать одна из них или несколько, зависит от конкретной ситуации):
Проверить, что у Data Flow и Connection Manager установлено свойство DelayValidation=true .

У Data Flow - свойство ValidateExternalMetadata=false .

В хранимке - установлено SET ANSI_NULLS ON

В SELECT запросе, возвращающем результат - добавить параметр OPTION (FAST 10000) . Он указывает оптимизатору запросов, чтобы план запроса оптимизировался на быстрый вывод 10000 записей.

Проверить хранимку на предмет parameter sniffing .

BTW, зачем вы запускаете хранимку с командой SET FMTONLY OFF; ? У вас там SQL 2008 в качестве СУБД? Начиная с SQL2012, есть гораздо более изящный и менее глючный способ. Например, для двух столбцов:
Код: sql
1.
2.
3.
4.
5.
EXEC [etl].sp_Get_Sale ?, ?, ?
WITH RESILT SETS ((
   [column1] INT,
   [column2] VARCHAR(36)
));
...
Рейтинг: 0 / 0
22.03.2019, 10:16
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39790064
Фотография Yagrus2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yagrus2
Участник
Подскажите, а как узнать план SQL-процедуры, запущенной из SSIS-пакета?
...
Рейтинг: 0 / 0
22.03.2019, 14:19
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39790081
Фотография StarikNavy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
StarikNavy
Участник
Yagrus2,

запустить, отловить запрос профайлером, посмотреть план
...
Рейтинг: 0 / 0
22.03.2019, 14:24
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39790422
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Yagrus2Подскажите, а как узнать план SQL-процедуры, запущенной из SSIS-пакета?Узнать можно, но ведь уже сказали, что план никак не связан с долгим выполнением.
...
Рейтинг: 0 / 0
23.03.2019, 14:24
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39790661
Ferdipux
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ferdipux
Гость
alexeyvg...Узнать можно, но ведь уже сказали, что план никак не связан с долгим выполнением.
Хм, не согласен. ИМХО, Pre-Execute - это фаза начала запроса Data Source, на нее же приходится заполнение кешей Lookup. В случае автора - запуск хранимки. Execute - это когда Data Source начал получать данные.
Чем можно объяснить такую задержку между запуском хранимки и началом возврата данных, если не оптимальными планами выполнения?
...
Рейтинг: 0 / 0
24.03.2019, 18:52
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39790686
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Ferdipuxalexeyvg...Узнать можно, но ведь уже сказали, что план никак не связан с долгим выполнением.
Хм, не согласен. ИМХО, Pre-Execute - это фаза начала запроса Data Source, на нее же приходится заполнение кешей Lookup. В случае автора - запуск хранимки. Execute - это когда Data Source начал получать данные.Разве? По моему, Pre-Execute - это фаза получения метаданных источника, то есть получение списка полей с типами и прочим.

https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-custom-objects/data-flow/run-time-methods-of-a-data-flow-component?view=sql-server-2017

В частности, там есть пример реализации Pre-Execute; видно, что делается только получение метаданных:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
Public Overrides Sub PreExecute()  
  
    Dim input As IDTSInput100 = ComponentMetaData.InputCollection(0)  
  
    ReDim bufferColumnIndex(input.InputColumnCollection.Count)  
  
    For x As Integer = 0 To input.InputColumnCollection.Count  
  
        Dim column As IDTSInputColumn100 = input.InputColumnCollection(x)  
        bufferColumnIndex(x) = BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID)  

    Next  
  
End Sub
...
Рейтинг: 0 / 0
24.03.2019, 22:33
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39790746
Ferdipux
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ferdipux
Гость
alexeyvgРазве? По моему, Pre-Execute - это фаза получения метаданных источника, то есть получение списка полей с типами и прочим.

https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-custom-objects/data-flow/run-time-methods-of-a-data-flow-component?view=sql-server-2017

В частности, там есть пример реализации Pre-Execute; видно, что делается только получение метаданных:
Не только. Pre-Execute вызывается после AcquireConnections , поэтому производится непосредственная валидация получаемых полей (это когда DelayValidation=true ). Но в документации приведен пример использования Pre-Execute - заполнение справочника имен полей и lineage IDs, с объяснением, почему именно на этом шаге - ранее BufferManager не доступен.
В ситуации, подобной автору, проводил исследования с профайлером - после старта Pre-Execute выдаются запросы на получение данных кешей (если это полные кеши) и данных в Data Source. Про кеши - можете убедиться сами без профайлера - на этапе Pre-Execute в логе исполнения пакета сообщается о получении количества записей в кеши, их процессинге и размещении в память. Запрос к источнику данных идет после подготовки кешей, но тоже на шаге Pre-Excute.
У автора нет lookup, поэтому и кешей нет. Значит, задержка объясняется только паузой между запросом и началом получения данных. ИМХО, самое простое объяснение факту разного времени выполнения хранимой процедуры -- неоптимальные планы исполнения, хотя могут быть и другие причины.
...
Рейтинг: 0 / 0
25.03.2019, 08:22
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39790934
Фотография Yagrus2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yagrus2
Участник
В моем случае, скорее всего дело в плане запроса.
Во-первых, у данной процедуры три плана:
Код: sql
1.
2.
3.
4.
5.
SELECT TOP 10 cp.cacheobjtype, cp.objtype, cp.plan_handle, t.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
WHERE t.text LIKE '%sp_Get_Sale%'
	AND t.dbid = DB_ID('MyDB')


cacheobjtype objtype plan_handle textCompiled Plan Proc 0x05000A00D909737940E163FC090000000000000000000000 CREATE PROCEDURE [etl].[sp_Get_Sale] ...Compiled Plan Proc 0x05000A00D909737940E15E310A0000000000000000000000 CREATE PROCEDURE [etl].[sp_Get_Sale] ...Compiled Plan Prepared 0x06000A003AC0EC2040A17D080A0000000000000000000000 (@P1 datetime2(0);@P2 datetime2(0);@P3 smallint)EXEC [etl].[sp_Get_Sale] @P1; @P2; @P3
Также, чтобы разобраться с механизмом логирования SSISDB, я создал вспомогательную таблицу,
в которую делаются вставки, если выполнилась определенная часть кода. Вот, что получилось на 24.03.2019
DT_Log Par12019-03-24 01:24:51.067 CREATE TABLE #SALE2019-03-24 01:24:51.070 Список всех регистраторов2019-03-24 01:24:52.470 /*1. РТУ*/2019-03-24 01:24:55.323 /*2. Реализации товаров отложенной отгрузки */2019-03-24 01:24:55.520 /*3. Возвраты*/2019-03-24 01:24:57.593 /*4. Корректировки*/2019-03-24 01:24:59.623 UPDATE -- Дата и номер заказа2019-03-24 01:25:01.050 UPDATE --1. Ищем себестоимость2019-03-24 11:00:41.100 UPDATE --2. Ищем первую себестоимость2019-03-24 11:00:46.833 UPDATE -- Себестоимость на дату реализации2019-03-24 11:00:48.753 UPDATE -- Конвертация валюты2019-03-24 11:00:50.997 TRUNCATE TABLE2019-03-24 11:00:51.003 INSERT INTO
А ниже логи из SSISDB
message_time message2019-03-24 01:24:47.7374354 +03:00 UT Sales:Validation has started.2019-03-24 01:24:47.7384340 +03:00 UT Sales:Information: Validation phase is beginning.2019-03-24 01:24:47.7604032 +03:00 UT Sales:Validation is complete.2019-03-24 01:24:49.9383498 +03:00 UT Sales:Validation has started.2019-03-24 01:24:49.9383498 +03:00 UT Sales:Information: Validation phase is beginning.2019-03-24 01:24:50.1290824 +03:00 UT Sales:Validation is complete.2019-03-24 11:00:53.5708178 +03:00 UT Sales:Start; 11:00:53 AM.2019-03-24 11:00:53.5718178 +03:00 UT Sales:Validation has started.2019-03-24 11:00:53.5738178 +03:00 UT Sales:Information: Validation phase is beginning.2019-03-24 11:00:53.7738178 +03:00 UT Sales:Validation is complete.2019-03-24 11:00:53.7778178 +03:00 UT Sales:Information: Prepare for Execute phase is beginning.2019-03-24 11:00:53.7888178 +03:00 UT Sales:Information: Pre-Execute phase is beginning.2019-03-24 11:00:53.9798178 +03:00 UT Sales:Information: Execute phase is beginning.2019-03-24 11:00:56.9438178 +03:00 UT Sales:Information: The final commit for the data insertion in "Staging Table" has started.2019-03-24 11:00:56.9468178 +03:00 UT Sales:Information: The final commit for the data insertion in "Staging Table" has ended.2019-03-24 11:00:56.9498178 +03:00 UT Sales:Information: Post Execute phase is beginning.2019-03-24 11:00:56.9568178 +03:00 UT Sales:Information: "Staging Table" wrote 80117 rows.2019-03-24 11:00:56.9598178 +03:00 UT Sales:Information: Cleanup phase is beginning.2019-03-24 11:00:56.9628178 +03:00 UT Sales:Finished; 11:00:56 AM; Elapsed time: 00:00:03.386.
То есть, лог SSIS-пакета сообщил о старте исполнения хранимой процедуры, но на самом деле она почти выполнилась.
...
Рейтинг: 0 / 0
25.03.2019, 13:54
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39790948
Фотография Yagrus2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yagrus2
Участник
Также у меня был запущен профайлер. Из его логов видно, что сканирование кластерных индексов ничего не вернуло:
StartTime: 2019-03-24 01:25:01
...
Рейтинг: 0 / 0
25.03.2019, 14:14
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39790950
Фотография Yagrus2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yagrus2
Участник
А в StartTime: 2019-03-24 01:25:01 стартует тот же самый запрос, но скан тех же индексов дает огромное количество записей.

Хотя [dbo].[_InfoRg9112] содержит 470786, а [dbo].[_InfoRg9882] - 1389724 записей.

Что это может значить и как мне поступить?
...
Рейтинг: 0 / 0
25.03.2019, 14:15
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39790952
Фотография Yagrus2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yagrus2
Участник
В после выше StartTime: 2019-03-24 11:00:41.100
...
Рейтинг: 0 / 0
25.03.2019, 14:16
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39790980
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
Yagrus2,

выложили бы план в виде .sqlplan

а так чисто из картинки: у вас на таблице _InfoRg9112 нет нормального индекса удовлетворяющего вашему запросу.
у вас идет соединение NL вашей темповой таблицы и скан некластерного индекса, чем больше строк будет в вашей #SALE тем медленней будет выборка.
...
Рейтинг: 0 / 0
25.03.2019, 14:53
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39791003
Фотография Yagrus2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yagrus2
Участник
Yagrus2Также у меня был запущен профайлер. Из его логов видно, что сканирование кластерных индексов ничего не вернуло:
StartTime: 2019-03-24 01:25:01 В этот момент времени было перехвачено событие Showplan XML, поэтому у всех операторов Actual Number of Row = 0
...
Рейтинг: 0 / 0
25.03.2019, 15:30
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39791006
Фотография Yagrus2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yagrus2
Участник
felix_ffYagrus2,

выложили бы план в виде .sqlplan

а так чисто из картинки: у вас на таблице _InfoRg9112 нет нормального индекса удовлетворяющего вашему запросу.
у вас идет соединение NL вашей темповой таблицы и скан некластерного индекса, чем больше строк будет в вашей #SALE тем медленней будет выборка.
...
Рейтинг: 0 / 0
25.03.2019, 15:35
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39791008
Фотография Yagrus2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yagrus2
Участник
felix_ff,
Запустил, процедуру и ManagermentStudio. Время ее работы порядка 1 мин.
Во вложении план, того же участка кода.
...
Рейтинг: 0 / 0
25.03.2019, 15:39
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39791045
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
Yagrus2,

кусок что ли плана выдрали?
нет информации о set_options и самой инструкции t-sql.

но планы у вас как раз таки различаются для ssms используется hash-соединение вместо NL.

сравните настройки сессии с которыми запускаете в ssms и в вашем SSIS пакете, может ARITHABORT или QUOTED_IDENTIFIER отличаются.
...
Рейтинг: 0 / 0
25.03.2019, 16:33
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39791080
Фотография Yagrus2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yagrus2
Участник
felix_ffкусок что ли плана выдрали? Нет, просто выбрал Extract Event Data ... для EventClass - "Showplan XML Statistics Profile"
felix_ffнет информации о set_options и самой инструкции t-sql. Как профайлером отловить set_options?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
	;WITH [Cost] AS
	(
		SELECT
			 YEAR([Cost_Register].[Период])*100 + MONTH([Cost_Register].[Период])	AS [Month]
			,[Product_Analytics].[Номенклатура]					AS [Product_ID]
			,[Product_Analytics].[Склад_RRRef]					AS [Warehouse_ID]		
			,[Cost_Register].[Организация]						AS [Organisation_ID]
			,[Cost_Register].[Стоимость]						AS [Cost]
			,[Cost_Register].[СтоимостьДопРасходыБезНДС]				AS [AddCostNoVAT]
			,[Cost_Register].[СтоимостьДопРасходы]					AS [AddCostWithVAT]			
		FROM
			[ut_filial].[dbo].[_РегистрСведений_СтоимостьТоваров] AS [Cost_Register]
		JOIN
			[ut_filial].[dbo].[_РегистрСведений_АналитикаУчетаНоменклатуры] AS [Product_Analytics]
		ON	[Product_Analytics].[КлючАналитики] = [Cost_Register].[АналитикаУчетаНоменклатуры]
		WHERE
			[Cost_Register].[Активность] = 0x01
	)
	UPDATE
		#SALE
	SET
		#SALE.[Cost]		= ([Cost].[Cost] * #SALE.[Quantity]),
		#SALE.[AddCostNoVAT]	= ([Cost].[AddCostNoVAT] * #SALE.[Quantity]),
		#SALE.[AddCostWithVAT]	= ([Cost].[AddCostWithVAT] * #SALE.[Quantity])
	FROM
		#SALE
	JOIN
		[Cost]
			ON	[Cost].[Month]		 = #SALE.[Action_Month]
			AND	[Cost].[Product_ID]	 = #SALE.[Product_ID]
			AND	[Cost].[Warehouse_ID]	 = #SALE.[Warehouse_ID]
			AND	[Cost].[Organisation_ID] = #SALE.[Organisation_ID]
	WHERE
		#SALE.[Cost] IS NULL


felix_ffно планы у вас как раз таки различаются для ssms используется hash-соединение вместо NL. Это да, но мне кажется что первопричина - громадное значение Actual Number of Row

felix_ffсравните настройки сессии с которыми запускаете в ssms и в вашем SSIS пакете, может ARITHABORT или QUOTED_IDENTIFIER отличаются. Как в пакете посмотреть эти настройки? Не нашел таких опций в ConnectionManager-е.
...
Рейтинг: 0 / 0
25.03.2019, 17:29
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39791124
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
Yagrus2,


actual number of rows у вас большой из-за NL: оно равно кол-во выполнений * кол-ко строк возвращенных оператором за один проход.
Так что это вас не должно смущать, а должно смущать что выбирается для соединения именно NL.

у вас извлеченное событие профилировки это кусок общего плана процедуры или батча.

вам нужно взять его query_plan_hash
и выполнить запрос:
Код: sql
1.
2.
3.
4.
select p.query_plan
from sys.dm_exec_query_stats s
     cross apply sys.dm_exec_query_plan(s.plan_handle) p
where s.query_plan_hash = 0xXXXXXXXX; --где тут хэш из плана



оно вам даст общий план для всей процедуры если оно находится в кэше. там нужно посмотреть параметры времени компиляции для инструкции они там в xml представленны в виде узла StatementSetOptions

скорее всего в кеше у вас будет два разных плана для двух запуской из SSMS и SSIS.

можете планы получить так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
select p.[plan_handle], pp.[query_plan] 
from sys.dm_exec_cached_plans p
    cross apply sys.dm_exec_query_plan(p.plan_handle) pp
    cross apply sys.dm_exec_plan_attributes (p.plan_handle) pp2
where pp2.attribute = 'objectid'
and cast(pp2.value as int) = object_id('имя вашей процедурки', 'P')
option (recompile);



для чистоты душевной можете проделать эксперимент: (желательно не на бою а на тестовом стенде)
Код: sql
1.
dbcc freeproccache(XXXX) --где XXXX plan_handle полученный из запроса выше


очистить процедурный кэш от планов вашей хранимки. запустить на исполнение SSIS, потом из SSMS
и посмотреть сколько будет планов.

касательно параметров SET они предопределены для каждого провайдера заранее, если явно не переопределяются настройками сервера/сессии. на msdn где то была таблица.

как вариант для определения выставленных настроек можете запихнуть в процедуру что то типа логирования, что бы понять в какой настройке различия. вам надо выйти на то что бы для запуска из SSIS хранимка запускалась с планом на hash-join для больших объемов а не NL
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
DECLARE @options INT = @@OPTIONS;

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT'
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER'
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT'
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON'
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF'
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL'
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT'
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'
...
Рейтинг: 0 / 0
25.03.2019, 18:43
| Ответить | Цитировать | Написать  
SSIS. Увеличилось время работы Data Flow Task.
    #39791135
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
Yagrus2,

Попробуйте делать принудительное обновление статистик. Такое может происходить, когда вбрасывается новый ключ, с которым связано небольшое кол-во записей, а потом количество зависимых записей многократно возрастает.
...
Рейтинг: 0 / 0
25.03.2019, 19:31
| Ответить | Цитировать | Написать  
25 сообщений из 38, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / SSIS. Увеличилось время работы Data Flow Task.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]