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

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

Это как раз проявляется в том случае, когда происходит nested loop, хотя уже напрашивается HASH JOIN.
25 мар 19, 19:33    [21843102]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 902
felix_ff,
После очистки кеша, я запустил процедуру из SSMS. Вот что получилось:
SELECT	*  
FROM	sys.objects 
WHERE	type = 'P' AND
	QUOTENAME( SCHEMA_NAME ( schema_id ) ) + '.' + QUOTENAME( name ) = '[etl].[sp_Get_Sale]'

name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
sp_Get_Sale 2037582297 NULL 5 0 P SQL_STORED_PROCEDURE 2018-04-27 16:26:37.643 2019-03-26 11:30:40.250 0 0 0

SELECT	*
FROM	[sys].[dm_exec_procedure_stats]
WHERE	object_id = 2037582297

database_id object_id type type_desc sql_handle plan_handle cached_time last_execution_time execution_count total_worker_time last_worker_time min_worker_time max_worker_time total_physical_reads last_physical_reads min_physical_reads max_physical_reads total_logical_writes last_logical_writes min_logical_writes max_logical_writes total_logical_reads last_logical_reads min_logical_reads max_logical_reads total_elapsed_time last_elapsed_time min_elapsed_time max_elapsed_time
10 2037582297 P SQL_STORED_PROCEDURE 0x03000A00D9097379CBB2BD001CAA00000100000000000000 0x05000A00D90973794081933E0E0000000000000000000000 2019-03-26 11:31:48.977 2019-03-26 11:31:48.987 1 30181726 30181726 30181726 30181726 94552 94552 94552 94552 11411 11411 11411 11411 5845485 5845485 5845485 5845485 30997773 30997773 30997773 30997773

SELECT *
FROM sys.dm_exec_cached_plans cp
WHERE	plan_handle = 0x05000A00D90973794081933E0E0000000000000000000000

bucketid refcounts usecounts size_in_bytes memory_object_address cacheobjtype objtype plan_handle pool_id
34866 2 1 2547712 0x0000000E3E938060 Compiled Plan Proc 0x05000A00D90973794081933E0E0000000000000000000000 1


SELECT	*
FROM	sys.dm_exec_plan_attributes (0x05000A00D90973794081933E0E0000000000000000000000)  


attribute value is_cache_key
set_options 4345 1
objectid 2037582297 1
dbid 10 1
dbid_execute 10 1
user_id 5 1
language_id 0 1
date_format 1 1
date_first 7 1
compat_level 100 1
status 0 1
required_cursor_options 0 1
acceptable_cursor_options 0 1
merge_action_type 0 1
is_replication_specific 0 1
optional_spid 0 1
optional_clr_trigger_dbid 0 1
optional_clr_trigger_objid 0 1
inuse_exec_context 0 0
free_exec_context 1 0
hits_exec_context 0 0
misses_exec_context 1 0
removed_exec_context 0 0
inuse_cursors 0 0
free_cursors 0 0
hits_cursors 0 0
misses_cursors 0 0
removed_cursors 0 0
sql_handle NULL 0
А запрос
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%'
возвратил
cacheobjtype objtype plan_handle
Compiled Plan Proc 0x05000A00D90973794081933E0E0000000000000000000000
26 мар 19, 14:50    [21843827]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 902
А после запуска из SSIS. На момент сбора статистик он еще выполнялся
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%'
cacheobjtype objtype plan_handle text
Compiled Plan Proc 0x05000A00D90973794081933E0E0000000000000000000000 CREATE PROCEDURE [etl].[sp_Get_Sale] ...
Compiled Plan Prepared 0x06000A003AC0EC2040A125FF040000000000000000000000(@P1 datetime2(0);@P2 datetime2(0);@P3 smallint);EXEC [etl].[sp_Get_Sale] @P1; @P2; @P3
SELECT	*  
FROM	sys.objects 
WHERE	type = 'P' AND
	QUOTENAME( SCHEMA_NAME ( schema_id ) ) + '.' + QUOTENAME( name ) = '[etl].[sp_Get_Sale]'

name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
sp_Get_Sale 2037582297 NULL 5 0 P SQL_STORED_PROCEDURE 2018-04-27 16:26:37.643 2019-03-26 11:30:40.250 0 0 0

SELECT	*
FROM	[sys].[dm_exec_procedure_stats]
WHERE	object_id = 2037582297
-- количество plan_handle у процедуры не изменилось

database_id object_id type type_desc sql_handle plan_handle cached_time last_execution_time execution_count total_worker_time last_worker_time min_worker_time max_worker_time total_physical_reads last_physical_reads min_physical_reads max_physical_reads total_logical_writes last_logical_writes min_logical_writes max_logical_writes total_logical_reads last_logical_reads min_logical_reads max_logical_reads total_elapsed_time last_elapsed_time min_elapsed_time max_elapsed_time
10 2037582297 P SQL_STORED_PROCEDURE 0x03000A00D9097379CBB2BD001CAA00000100000000000000 0x05000A00D90973794081933E0E0000000000000000000000 2019-03-26 11:31:48.977 2019-03-26 11:31:48.987 1 30181726 30181726 30181726 30181726 94552 94552 94552 94552 11411 11411 11411 11411 5845485 5845485 5845485 5845485 30997773 30997773 30997773 30997773

SELECT *
FROM sys.dm_exec_cached_plans cp
WHERE	plan_handle = 0x06000A003AC0EC2040A125FF040000000000000000000000

bucketid refcounts usecounts size_in_bytes memory_object_address cacheobjtype objtype plan_handle pool_id
22449 2 1 16384 0x00000004FF25A060 Compiled Plan Prepared 0x06000A003AC0EC2040A125FF040000000000000000000000 1

SELECT	*
FROM	sys.dm_exec_plan_attributes (0x06000A003AC0EC2040A125FF040000000000000000000000)
-- А здесь видно, что у данного plan_handle, другой объект 

attribute value is_cache_key
set_options 249 1
objectid 552386618 1
dbid 10 1
dbid_execute 0 1
user_id -2 1
language_id 0 1
date_format 1 1
date_first 7 1
compat_level 100 1
status 0 1
required_cursor_options 0 1
acceptable_cursor_options 0 1
merge_action_type 0 1
is_replication_specific 0 1
optional_spid 0 1
optional_clr_trigger_dbid 0 1
optional_clr_trigger_objid 0 1
inuse_exec_context 1 0
free_exec_context 0 0
hits_exec_context 0 0
misses_exec_context 1 0
removed_exec_context 0 0
inuse_cursors 0 0
free_cursors 0 0
hits_cursors 0 0
misses_cursors 0 0
removed_cursors 0 0
sql_handle 0x01000A003AC0EC20707926050A0000000000000000000000 0
Во вложении файл с планом, но он странный

К сообщению приложен файл (ExecutionPlan2.sqlplan - 428bytes) cкачать
26 мар 19, 14:51    [21843831]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
felix_ff
Member

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

вам нужна два раза ее запустить, один раз из ssms, второй раз вашим ssis пакетом (или где там у вас тормозит).

и в процедурном кэше искать не по тексту LIKE '%sp_Get_Sale%', а по sys.dm_exec_plan_attributes where attribute = 'objectid' and value = object_id('имя вашей хранимки')
26 мар 19, 14:53    [21843834]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 902
felix_ff,
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('etl.sp_Get_Sale', 'P')
option (recompile);
Пока такой запрос возвращает, план полученный из SSMS.
Запрос и SSIS еще выполняется.

В предыдущем моем посте выведено вся "статистика" , которую получили при запуске из SSIS.
А именно вот plan_handle
cacheobjtype objtype plan_handle text
Compiled Plan Prepared 0x06000A003AC0EC2040A125FF040000000000000000000000 (@P1 datetime2(0);@P2 datetime2(0);@P3 smallint);EXEC [etl].[sp_Get_Sale] @P1; @P2; @P3

А также, что он привязан к объекту objectid = 552386618
Для этого объекта запрос
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) = 552386618
option (recompile);
возвращает "странный" план, который я выложил в предыдущем посте

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

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

select
 qp.query_plan
from
 sys.dm_exec_procedure_stats ps cross apply
 sys.dm_exec_query_plan(ps.plan_handle) qp
where
 ps.database_id = db_id('MyDB') and
 ps.object_id = object_id('MyProc');
26 мар 19, 15:38    [21843933]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 902
invm,
Такой запрос возвращает план, с plan_handle = 0x05000A00D90973794081933E0E0000000000000000000000.
Он получился при запуске моей процедуры из SSMS и с ним проблемы нет. Он привязан к объекту с object_id = 2037582297

Запуск SSIS-пакета добавил в sys.dm_exec_cached_plans cp еще одну запись, у которой
plan_handle = 0x06000A003AC0EC2040A125FF040000000000000000000000
Выборка из sys.dm_exec_sql_text с последним хендлером возвращает такой запрос
(@P1 datetime2(0),@P2 datetime2(0),@P3 smallint)EXEC [etl].[sp_Get_Sale] @P1, @P2, @P3

Из sys.dm_exec_plan_attributes вижу, что данный план привязан к объекту object_id = 552386618,
а это уже не моя процедура.
26 мар 19, 17:32    [21844089]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
felix_ff
Member

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

не путайте планы, под хранимую процедуру (именно под сам модуль) и план батча который содержит код запуска хп. это разные планы.


я уже уточнил вам почему не следует использовать поиск по dm_exec_cached_plans + dm_exec_sql_text, он вам даст план батча и план хранимки их сравнивать не нужно.

вот для примера
create or alter procedure [sp_test]
as
select * from master.dbo.spt_values where 1= 0
go

dbcc freeproccache;
go

--запустить пару раз
set arithabort on;
select 1 from master.dbo.spt_values
exec sp_test
go

set arithabort off;
select 1 from master.dbo.spt_values
exec sp_test;
go
--//запустить пару раз


и потом сравните результаты:
select p.plan_handle, pp.query_plan, t.text from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) t
outer apply sys.dm_exec_query_plan(p.plan_handle) pp
where t.text like '%sp_test%'
option (recompile);

select p.plan_handle, ppp.query_plan
from sys.dm_exec_cached_plans p
    cross apply sys.dm_exec_plan_attributes(p.plan_handle) pp
    cross apply sys.dm_exec_query_plan(p.plan_handle) ppp
where pp.attribute = 'objectid'
  and try_cast(pp.value as int) = object_id('sp_test', 'P')
option (recompile)
go


потом их последнего резалтсета возьмите два хендла:
select * from sys.dm_exec_plan_attributes(0x05000600346B784420CAB3BFA000000001000000000000000000000000000000000000000000000000000000) where attribute in ('sql_handle', 'set_options', 'user_id', 'date_format', 'date_first')
select * from sys.dm_exec_plan_attributes(0x05000600346B7844C0C2B3BFA000000001000000000000000000000000000000000000000000000000000000) where attribute in ('sql_handle', 'set_options', 'user_id', 'date_format', 'date_first')

select * from sys.dm_exec_plan_attributes(0x05000600346B784420CAB3BFA000000001000000000000000000000000000000000000000000000000000000) where attribute in ('sql_handle', 'set_options', 'user_id', 'date_format', 'date_first')
except
select * from sys.dm_exec_plan_attributes(0x05000600346B7844C0C2B3BFA000000001000000000000000000000000000000000000000000000000000000) where attribute in ('sql_handle', 'set_options', 'user_id', 'date_format', 'date_first')


и сравните, у них sql_handle будет один и тотже, а вот set_options будут различаться
26 мар 19, 18:54    [21844172]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
felix_ff
Member

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

invm вам кстати компактней написал как получить только планы вашей хранимки. его запрос вам сколько строк возвращает?
26 мар 19, 18:56    [21844173]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
felix_ff
Member

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

хотя я уже по вашим скринам видно что для исполнения SSIS пакета ARITHABORT выключен.

попробуйте в Execute-SQL task пакета добавить SET ARITHABORT ON;

или наоборот в SSMS SET ARITHABORT OFF и выполните процедуру, посмотрите изменится в плане алгоритмы соединений.
что еще заметил: у вас план для SSIS пакета зависим от пользователя user_id = 5 это id конкретного пользователя БД, овнера схемы etl.

вам по хорошему еще надо моделировать запуск из под идентичных пользователей.
26 мар 19, 19:17    [21844193]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
Yagrus2
Member

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

не путайте планы, под хранимую процедуру (именно под сам модуль) и план батча который содержит код запуска хп. это разные планы.
я уже уточнил вам почему не следует использовать поиск по dm_exec_cached_plans + dm_exec_sql_text, он вам даст план батча и план хранимки их сравнивать не нужно.
Спасибо, с этим разобрался!
27 мар 19, 13:38    [21845028]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
Yagrus2
Member

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

invm вам кстати компактней написал как получить только планы вашей хранимки. его запрос вам сколько строк возвращает?

Возвращается две строки.
А как понять, какой из планов используется во время выполнения конкретного батча?
То есть, у процедуры есть два плана. Пусть я запустил из SSIS-пакет. Как понять какой из планов он выбрал?
27 мар 19, 13:41    [21845044]     Ответить | Цитировать Сообщить модератору
 Re: SSIS. Увеличилось время работы Data Flow Task.  [new]
Yagrus2
Member

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

хотя я уже по вашим скринам видно что для исполнения SSIS пакета ARITHABORT выключен.

Да, вы правы. Влияет настройка ARITHABORT
27 мар 19, 13:43    [21845047]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
Все форумы / Microsoft SQL Server Ответить