Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 SSIS. Увеличилось время работы Data Flow Task.  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 894
Hello World!
На SQL-сервере опубликован SSIS-пакет. В определенный момент время его выполнения увеличилось на порядок.
Выяснил, что зависает Data Flow Task.
У него такая структура:
1. Источник - OLE DB SOURCE, Date Access mode - SQL Command, Сам скрипт - процедура с тремя параметрами, которая возвращает набор записей.
SET  FMTONLY OFF;
EXEC [etl].sp_Get_Sale ?, ?, ?

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

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

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

Рестарт сервера не помог.
Подскажите, в каком направлении двигаться, чтобы определить из-за чего появляются зависания.
21 мар 19, 16:16    [21839814]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
.Евгений
Member

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

Если без экзотики, то причина может быть либо в процедуре (SSIS не при чем), либо в задержке при логировании SSIS (смотрите активность в БД SSISDB или как там она у вас называется).
21 мар 19, 16:38    [21839851]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
Владислав Колосов
Member

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

кто же знает, может у вас журнал или база расширяются.
21 мар 19, 16:49    [21839866]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 894
Запуск процедуры из Managerment Studuo отрабатывает без задержек.
Вот логи SSISDB
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_path
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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 Sales
95166 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
21 мар 19, 17:17    [21839902]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30829
Yagrus2
Из его лога видно, что в определенный момент все транзакции прекращаются.
Да, интересно, Pre-Execute занял 8 часов.
21 мар 19, 17:50    [21839943]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
.Евгений
Member

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

возможно, на стороне источника была заблокирована проверка метаданных, возвращаемых вашей процедурой. Мониторьте сервера ETL и источников во время выполнения. Лукапов вроде бы у вас нет (начитка данных для их наполнения происходит в этой же фазе).
21 мар 19, 18:02    [21839965]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
Ferdipux
Member

Откуда: Москва
Сообщений: 537
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, есть гораздо более изящный и менее глючный способ. Например, для двух столбцов:
    EXEC [etl].sp_Get_Sale ?, ?, ?
    WITH RESILT SETS ((
       [column1] INT,
       [column2] VARCHAR(36)
    ));
    
  • 22 мар 19, 10:16    [21840395]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    Yagrus2
    Member

    Откуда: Москва
    Сообщений: 894
    Подскажите, а как узнать план SQL-процедуры, запущенной из SSIS-пакета?
    22 мар 19, 14:19    [21840786]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    StarikNavy
    Member

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

    запустить, отловить запрос профайлером, посмотреть план
    22 мар 19, 14:24    [21840807]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 30829
    Yagrus2
    Подскажите, а как узнать план SQL-процедуры, запущенной из SSIS-пакета?
    Узнать можно, но ведь уже сказали, что план никак не связан с долгим выполнением.
    23 мар 19, 14:24    [21841670]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    Ferdipux
    Member

    Откуда: Москва
    Сообщений: 537
    alexeyvg
    ...Узнать можно, но ведь уже сказали, что план никак не связан с долгим выполнением.

    Хм, не согласен. ИМХО, Pre-Execute - это фаза начала запроса Data Source, на нее же приходится заполнение кешей Lookup. В случае автора - запуск хранимки. Execute - это когда Data Source начал получать данные.
    Чем можно объяснить такую задержку между запуском хранимки и началом возврата данных, если не оптимальными планами выполнения?
    24 мар 19, 18:52    [21842202]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 30829
    Ferdipux
    alexeyvg
    ...Узнать можно, но ведь уже сказали, что план никак не связан с долгим выполнением.

    Хм, не согласен. ИМХО, 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; видно, что делается только получение метаданных:
    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 
    
    24 мар 19, 22:33    [21842287]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    Ferdipux
    Member

    Откуда: Москва
    Сообщений: 537
    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, поэтому и кешей нет. Значит, задержка объясняется только паузой между запросом и началом получения данных. ИМХО, самое простое объяснение факту разного времени выполнения хранимой процедуры -- неоптимальные планы исполнения, хотя могут быть и другие причины.
    25 мар 19, 08:22    [21842407]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    Yagrus2
    Member

    Откуда: Москва
    Сообщений: 894
    В моем случае, скорее всего дело в плане запроса.
    Во-первых, у данной процедуры три плана:
    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 text
    Compiled 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 Par1
    2019-03-24 01:24:51.067 CREATE TABLE #SALE
    2019-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 TABLE
    2019-03-24 11:00:51.003 INSERT INTO

    А ниже логи из SSISDB
    message_time message
    2019-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-пакета сообщил о старте исполнения хранимой процедуры, но на самом деле она почти выполнилась.
    25 мар 19, 13:54    [21842764]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    Yagrus2
    Member

    Откуда: Москва
    Сообщений: 894
    Также у меня был запущен профайлер. Из его логов видно, что сканирование кластерных индексов ничего не вернуло:
    StartTime: 2019-03-24 01:25:01

    К сообщению приложен файл. Размер - 134Kb
    25 мар 19, 14:14    [21842788]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    Yagrus2
    Member

    Откуда: Москва
    Сообщений: 894
    А в StartTime: 2019-03-24 01:25:01 стартует тот же самый запрос, но скан тех же индексов дает огромное количество записей.

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

    Что это может значить и как мне поступить?

    К сообщению приложен файл. Размер - 139Kb
    25 мар 19, 14:15    [21842791]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    Yagrus2
    Member

    Откуда: Москва
    Сообщений: 894
    В после выше StartTime: 2019-03-24 11:00:41.100
    25 мар 19, 14:16    [21842794]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1172
    Yagrus2,

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

    а так чисто из картинки: у вас на таблице _InfoRg9112 нет нормального индекса удовлетворяющего вашему запросу.
    у вас идет соединение NL вашей темповой таблицы и скан некластерного индекса, чем больше строк будет в вашей #SALE тем медленней будет выборка.
    25 мар 19, 14:53    [21842851]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    Yagrus2
    Member

    Откуда: Москва
    Сообщений: 894
    Yagrus2
    Также у меня был запущен профайлер. Из его логов видно, что сканирование кластерных индексов ничего не вернуло:
    StartTime: 2019-03-24 01:25:01
    В этот момент времени было перехвачено событие Showplan XML, поэтому у всех операторов Actual Number of Row = 0
    25 мар 19, 15:30    [21842894]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    Yagrus2
    Member

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

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

    а так чисто из картинки: у вас на таблице _InfoRg9112 нет нормального индекса удовлетворяющего вашему запросу.
    у вас идет соединение NL вашей темповой таблицы и скан некластерного индекса, чем больше строк будет в вашей #SALE тем медленней будет выборка.


    К сообщению приложен файл (Pq.SQLPlan - 39Kb) cкачать
    25 мар 19, 15:35    [21842903]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    Yagrus2
    Member

    Откуда: Москва
    Сообщений: 894
    felix_ff,
    Запустил, процедуру и ManagermentStudio. Время ее работы порядка 1 мин.
    Во вложении план, того же участка кода.

    К сообщению приложен файл (g.SQLPlan - 40Kb) cкачать
    25 мар 19, 15:39    [21842906]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1172
    Yagrus2,

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

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

    сравните настройки сессии с которыми запускаете в ssms и в вашем SSIS пакете, может ARITHABORT или QUOTED_IDENTIFIER отличаются.
    25 мар 19, 16:33    [21842962]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    Yagrus2
    Member

    Откуда: Москва
    Сообщений: 894
    felix_ff
    кусок что ли плана выдрали?
    Нет, просто выбрал Extract Event Data ... для EventClass - "Showplan XML Statistics Profile"
    felix_ff
    нет информации о set_options и самой инструкции t-sql.
    Как профайлером отловить set_options?
    	;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-е.
    25 мар 19, 17:29    [21843007]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1172
    Yagrus2,


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

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

    вам нужно взять его query_plan_hash
    и выполнить запрос:
    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.

    можете планы получить так:
    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);
    


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

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

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

    как вариант для определения выставленных настроек можете запихнуть в процедуру что то типа логирования, что бы понять в какой настройке различия. вам надо выйти на то что бы для запуска из SSIS хранимка запускалась с планом на hash-join для больших объемов а не NL
    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' 
    
    25 мар 19, 18:43    [21843070]     Ответить | Цитировать Сообщить модератору
     Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
    a_voronin
    Member

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

    Попробуйте делать принудительное обновление статистик. Такое может происходить, когда вбрасывается новый ключ, с которым связано небольшое кол-во записей, а потом количество зависимых записей многократно возрастает.
    25 мар 19, 19:31    [21843101]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить