6@[User::ConfigConnectionString]@[User::ConfigConnectionString]@[User::MDWConnectionString]@[User::TargetConnectionString]c:\perflogs1c:\perflogs\SETHS-SSISVSTA2_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity.cachec:\perflogs\SETHS-SSISVSTA2_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity{438B0736-4958-4B2D-95A4-E40C9F506131}.temp.cachec:\perflogs\SETHS-SSISVSTA2_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity2DC02BD6-E230-4C05-8516-4E8C0EF21F9514AF3C12-38E6-4155-BD29-F33E7966BA23Provider=SQLNCLI11;Data Source=(local);Integrated Security=SSPI;Initial Catalog=msdb;APP=SQL Server Data Collector0EXITEVENTMSSQL.1MSSQLSERVER0c:\perflogs\SETHS-SSISVSTA2_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity_QueryStats_LastSnapshot.cachec:\perflogs\SETHS-SSISVSTA2_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity_QueryStats_LastSnapshot.temp.cachec:\perflogs\SETHS-SSISVSTA2_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity_QueryStats_5000500128008100000025000500100200LOOPEVENTProvider=SQLNCLI11;Data Source=(local);Integrated Security=SSPI;Initial Catalog=MDW;APP=SQL Server Data Collector02001Provider=SQLNCLI11;Initial Catalog=master;Data Source=(local);Integrated Security=SSPI;APP=SQL Server Data Collector52,7,OnError,9,OnWarning-1-1-1-1-1-1-1-1-1-1-1-1-1-1"User::InterestingQueriesSnapshotFileNamePrefixPattern"@[User::CollectionCacheFileName]00c:\perflogs\SETHS-SSISVSTA2_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity.cache@[User::CacheDirectory]@[System::MachineName] + "_" + @[User::InstanceID] + "_{" + @[User::CollectionSetUID] + "}_" + (DT_WSTR, 10) @[User::ItemID] + "_QueryActivity*.temp.cache"C:\Users\macies\AppData\Local\Temp\ecodePerfWH\MACIES4_MSSQL.1_{{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}}_100_QueryActivity.cache0

@[User::SnapshotId]@[User::SnapshotId]

0[snapshots].[active_sessions_and_requests]1252false3falsefalseCHECK_CONSTRAINTS0

User::RowCount

1User::CurrentCollectionCacheFileName

00000000000000

@[User::SnapshotId]@[User::SnapshotId]

0SET NOCOUNT ON DECLARE @p1 datetime SET @p1 = GETDATE() SELECT [sql_handle], statement_start_offset, statement_end_offset, -- Use ISNULL here and in other columns to handle in-progress queries that are not yet in sys.dm_exec_query_stats. -- These values only come from sys.dm_exec_query_stats. If the plan does not show up in sys.dm_exec_query_stats -- (first execution of a still-in-progress query, visible in sys.dm_exec_requests), these values will be NULL. MAX (plan_generation_num) AS plan_generation_num, plan_handle, MIN (creation_time) AS creation_time, MAX (last_execution_time) AS last_execution_time, SUM (execution_count) AS execution_count, SUM (total_worker_time) AS total_worker_time, MIN (min_worker_time) AS min_worker_time, -- NULLable MAX (max_worker_time) AS max_worker_time, SUM (total_physical_reads) AS total_physical_reads, MIN (min_physical_reads) AS min_physical_reads, -- NULLable MAX (max_physical_reads) AS max_physical_reads, SUM (total_logical_writes) AS total_logical_writes, MIN (min_logical_writes) AS min_logical_writes, -- NULLable MAX (max_logical_writes) AS max_logical_writes, SUM (total_logical_reads) AS total_logical_reads, MIN (min_logical_reads) AS min_logical_reads, -- NULLable MAX (max_logical_reads) AS max_logical_reads, SUM (total_clr_time) AS total_clr_time, MIN (min_clr_time) AS min_clr_time, -- NULLable MAX (max_clr_time) AS max_clr_time, SUM (total_elapsed_time) AS total_elapsed_time, MIN (min_elapsed_time) AS min_elapsed_time, -- NULLable MAX (max_elapsed_time) AS max_elapsed_time, @p1 AS collection_time FROM ( SELECT [sql_handle], statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, creation_time, last_execution_time, execution_count, total_worker_time, min_worker_time, max_worker_time, total_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, min_logical_writes, max_logical_writes, total_logical_reads, min_logical_reads, max_logical_reads, total_clr_time, min_clr_time, max_clr_time, total_elapsed_time, min_elapsed_time, max_elapsed_time FROM sys.dm_exec_query_stats AS q -- Temporary workaround for VSTS #91422. This should be removed if/when sys.dm_exec_query_stats reflects in-progress queries. UNION ALL SELECT r.[sql_handle], r.statement_start_offset, r.statement_end_offset, ISNULL (qs.plan_generation_num, 0) AS plan_generation_num, r.plan_handle, ISNULL (qs.creation_time, r.start_time) AS creation_time, r.start_time AS last_execution_time, 1 AS execution_count, -- dm_exec_requests shows CPU time as ms, while dm_exec_query_stats -- uses microseconds. Convert ms to us. r.cpu_time * CAST(1000 as bigint) AS total_worker_time, qs.min_worker_time, -- min should not be influenced by in-progress queries r.cpu_time * CAST(1000 as bigint) AS max_worker_time, r.reads AS total_physical_reads, qs.min_physical_reads, -- min should not be influenced by in-progress queries r.reads AS max_physical_reads, r.writes AS total_logical_writes, qs.min_logical_writes, -- min should not be influenced by in-progress queries r.writes AS max_logical_writes, r.logical_reads AS total_logical_reads, qs.min_logical_reads, -- min should not be influenced by in-progress queries r.logical_reads AS max_logical_reads, qs.total_clr_time, -- CLR time is not available in dm_exec_requests qs.min_clr_time, -- CLR time is not available in dm_exec_requests qs.max_clr_time, -- CLR time is not available in dm_exec_requests -- dm_exec_requests shows elapsed time as ms, while dm_exec_query_stats -- uses microseconds. Convert ms to us. r.total_elapsed_time * CAST(1000 as bigint) AS total_elapsed_time, qs.min_elapsed_time, -- min should not be influenced by in-progress queries r.total_elapsed_time * CAST(1000 as bigint) AS max_elapsed_time FROM sys.dm_exec_requests AS r LEFT OUTER JOIN sys.dm_exec_query_stats AS qs ON r.plan_handle = qs.plan_handle AND r.statement_start_offset = qs.statement_start_offset AND r.statement_end_offset = qs.statement_end_offset WHERE r.sql_handle IS NOT NULL ) AS query_stats OUTER APPLY sys.dm_exec_sql_text (sql_handle) AS sql GROUP BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset ORDER BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset1252false2

User::InterestingQueriesRowCountIn

1User::InterestingQueriesLastSnapshotFileName0SELECT [sql_handle], [plan_handle], [statement_start_offset], [statement_end_offset], [creation_time] FROM [snapshots].[fn_get_notable_query_plans](1)SELECT [sql_handle] FROM [snapshots].[fn_get_notable_query_text](1)

@[User::SnapshotId]@[User::SnapshotId]

[ISNULL](#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_total_worker_time]}) ? #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[total_worker_time]} : #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[total_worker_time]} - #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_total_worker_time]}ISNULL(old_total_worker_time) ? total_worker_time : total_worker_time - old_total_worker_time[ISNULL](#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_total_physical_reads]}) ? #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[total_physical_reads]} : #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[total_physical_reads]} - #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_total_physical_reads]}ISNULL(old_total_physical_reads) ? total_physical_reads : total_physical_reads - old_total_physical_reads[ISNULL](#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_total_logical_reads]}) ? #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[total_logical_reads]} : #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[total_logical_reads]} - #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_total_logical_reads]}ISNULL(old_total_logical_reads) ? total_logical_reads : total_logical_reads - old_total_logical_reads[ISNULL](#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_total_logical_writes]}) ? #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[total_logical_writes]} : #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[total_logical_writes]} - #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_total_logical_writes]}ISNULL(old_total_logical_writes) ? total_logical_writes : total_logical_writes - old_total_logical_writes[ISNULL](#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_total_clr_time]}) ? #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[total_clr_time]} : #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[total_clr_time]} - #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_total_clr_time]}ISNULL(old_total_clr_time) ? total_clr_time : total_clr_time - old_total_clr_time[ISNULL](#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_total_elapsed_time]}) ? #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[total_elapsed_time]} : #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[total_elapsed_time]} - #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_total_elapsed_time]}ISNULL(old_total_elapsed_time) ? total_elapsed_time : total_elapsed_time - old_total_elapsed_time[ISNULL](#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_execution_count]}) ? #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[execution_count]} : #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[execution_count]} - #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Outputs[Merge Join Output].Columns[old_execution_count]}ISNULL(old_execution_count) ? execution_count : execution_count - old_execution_count

(DT_I8)1(DT_I8)1[GETDATE]()GETDATE()

@[User::SourceId]@[User::SourceId]

@[User::SourceId]@[User::SourceId]

22true5

#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[statement_start_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[statement_end_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[plan_generation_num]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[creation_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[last_execution_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[execution_count]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[total_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[total_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[total_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[total_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[total_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[total_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[max_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[min_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[min_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[max_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[min_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[max_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[min_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[max_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[min_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[max_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[min_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[max_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[collection_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[sql_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[plan_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Inputs[Merge Join Right Input].Columns[snapshot_id]}

25true5

#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[statement_start_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[statement_end_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[plan_generation_num]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[creation_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[last_execution_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[execution_count]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[total_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[total_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[total_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[total_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[total_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[total_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[max_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[min_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[min_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[max_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[min_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[max_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[min_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[max_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[min_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[max_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[min_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[max_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[collection_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[sql_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[plan_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Left Input].Columns[snapshot_id]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Right Input].Columns[total_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Right Input].Columns[total_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Right Input].Columns[total_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Right Input].Columns[total_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Right Input].Columns[total_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Right Input].Columns[total_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append last stats on interesting queries.Inputs[Merge Join Right Input].Columns[execution_count]}

0[snapshots].[query_stats]1252false3falsefalseCHECK_CONSTRAINTS0

0[snapshots].[notable_query_plan]1252false3falsefalseCHECK_CONSTRAINTS1000

0[snapshots].[notable_query_text]1252false3falsefalseCHECK_CONSTRAINTS1000

0SET NOCOUNT ON DECLARE @p1 datetime SET @p1 = GETDATE() SELECT [sql_handle], statement_start_offset, statement_end_offset, -- Use ISNULL here and in other columns to handle in-progress queries that are not yet in sys.dm_exec_query_stats. -- These values only come from sys.dm_exec_query_stats. If the plan does not show up in sys.dm_exec_query_stats -- (first execution of a still-in-progress query, visible in sys.dm_exec_requests), these values will be NULL. MAX (plan_generation_num) AS plan_generation_num, plan_handle, MIN (creation_time) AS creation_time, MAX (last_execution_time) AS last_execution_time, SUM (execution_count) AS execution_count, SUM (total_worker_time) AS total_worker_time, MIN (min_worker_time) AS min_worker_time, -- NULLable MAX (max_worker_time) AS max_worker_time, SUM (total_physical_reads) AS total_physical_reads, MIN (min_physical_reads) AS min_physical_reads, -- NULLable MAX (max_physical_reads) AS max_physical_reads, SUM (total_logical_writes) AS total_logical_writes, MIN (min_logical_writes) AS min_logical_writes, -- NULLable MAX (max_logical_writes) AS max_logical_writes, SUM (total_logical_reads) AS total_logical_reads, MIN (min_logical_reads) AS min_logical_reads, -- NULLable MAX (max_logical_reads) AS max_logical_reads, SUM (total_clr_time) AS total_clr_time, MIN (min_clr_time) AS min_clr_time, -- NULLable MAX (max_clr_time) AS max_clr_time, SUM (total_elapsed_time) AS total_elapsed_time, MIN (min_elapsed_time) AS min_elapsed_time, -- NULLable MAX (max_elapsed_time) AS max_elapsed_time, @p1 AS collection_time FROM ( SELECT [sql_handle], statement_start_offset, statement_end_offset, plan_generation_num, plan_handle, creation_time, last_execution_time, execution_count, total_worker_time, min_worker_time, max_worker_time, total_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, min_logical_writes, max_logical_writes, total_logical_reads, min_logical_reads, max_logical_reads, total_clr_time, min_clr_time, max_clr_time, total_elapsed_time, min_elapsed_time, max_elapsed_time FROM sys.dm_exec_query_stats AS q -- Temporary workaround for VSTS #91422. This should be removed if/when sys.dm_exec_query_stats reflects in-progress queries. UNION ALL SELECT r.[sql_handle], r.statement_start_offset, r.statement_end_offset, ISNULL (qs.plan_generation_num, 0) AS plan_generation_num, r.plan_handle, ISNULL (qs.creation_time, r.start_time) AS creation_time, r.start_time AS last_execution_time, 1 AS execution_count, -- dm_exec_requests shows CPU time as ms, while dm_exec_query_stats -- uses microseconds. Convert ms to us. r.cpu_time * CAST(1000 as bigint) AS total_worker_time, qs.min_worker_time, -- min should not be influenced by in-progress queries r.cpu_time * CAST(1000 as bigint) AS max_worker_time, r.reads AS total_physical_reads, qs.min_physical_reads, -- min should not be influenced by in-progress queries r.reads AS max_physical_reads, r.writes AS total_logical_writes, qs.min_logical_writes, -- min should not be influenced by in-progress queries r.writes AS max_logical_writes, r.logical_reads AS total_logical_reads, qs.min_logical_reads, -- min should not be influenced by in-progress queries r.logical_reads AS max_logical_reads, qs.total_clr_time, -- CLR time is not available in dm_exec_requests qs.min_clr_time, -- CLR time is not available in dm_exec_requests qs.max_clr_time, -- CLR time is not available in dm_exec_requests -- dm_exec_requests shows elapsed time as ms, while dm_exec_query_stats -- uses microseconds. Convert ms to us. r.total_elapsed_time * CAST(1000 as bigint) AS total_elapsed_time, qs.min_elapsed_time, -- min should not be influenced by in-progress queries r.total_elapsed_time * CAST(1000 as bigint) AS max_elapsed_time FROM sys.dm_exec_requests AS r LEFT OUTER JOIN sys.dm_exec_query_stats AS qs ON r.plan_handle = qs.plan_handle AND r.statement_start_offset = qs.statement_start_offset AND r.statement_end_offset = qs.statement_end_offset WHERE r.sql_handle IS NOT NULL ) AS query_stats OUTER APPLY sys.dm_exec_sql_text (sql_handle) AS sql GROUP BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset ORDER BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset1252false2

User::SqlPlanRowCountIn

User::SqlTextRowCountIn

User::SqlPlanRowCountError

User::SqlTextRowCountError

User::InterestingQueriesRowCountOut

User::InterestingQueriesRowCountIn

1User::InterestingQueriesLastSnapshotFileNameOutput01User::CollectionCacheFileNameOutput

1User::InterestingQueriesLastSnapshotFileName

(#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\RFS - Read current cache with dm_exec_requests.Outputs[Raw File Source Output].Columns[wait_duration_ms]} > @[User::InterestingQueries_LongWaitingRequest]) || (#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\RFS - Read current cache with dm_exec_requests.Outputs[Raw File Source Output].Columns[blocking_session_id]} == 0 && #{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\RFS - Read current cache with dm_exec_requests.Outputs[Raw File Source Output].Columns[is_blocking]} == TRUE ) || (#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\RFS - Read current cache with dm_exec_requests.Outputs[Raw File Source Output].Columns[open_transaction_count]} > @[User::InterestingQueries_HighTransactionCount]) || (#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\RFS - Read current cache with dm_exec_requests.Outputs[Raw File Source Output].Columns[request_total_elapsed_time]} > @[User::InterestingQueries_LongRunningRequest]) || (#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\RFS - Read current cache with dm_exec_requests.Outputs[Raw File Source Output].Columns[request_cpu_time]} > @[User::InterestingQueries_HighCpuUsage]) || (#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\RFS - Read current cache with dm_exec_requests.Outputs[Raw File Source Output].Columns[pending_io_count]} > @[User::InterestingQueries_HighIoCount]) || (#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\RFS - Read current cache with dm_exec_requests.Outputs[Raw File Source Output].Columns[memory_usage]} > @[User::InterestingQueries_HighMemoryUsage]) || (#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\RFS - Read current cache with dm_exec_requests.Outputs[Raw File Source Output].Columns[session_row_count]} > @[User::InterestingQueries_LargeResultset])(wait_duration_ms > @[User::InterestingQueries_LongWaitingRequest]) || (blocking_session_id == 0 && is_blocking == TRUE ) || (open_transaction_count > @[User::InterestingQueries_HighTransactionCount]) || (request_total_elapsed_time > @[User::InterestingQueries_LongRunningRequest]) || (request_cpu_time > @[User::InterestingQueries_HighCpuUsage]) || (pending_io_count > @[User::InterestingQueries_HighIoCount]) || (memory_usage > @[User::InterestingQueries_HighMemoryUsage]) || (session_row_count > @[User::InterestingQueries_LargeResultset])0

true

false-103040500000000000000000000000000000000000000000000010200

#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[statement_start_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[statement_end_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[plan_generation_num]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[creation_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[last_execution_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[execution_count]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[total_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[total_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[total_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[total_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[total_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[total_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[max_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[min_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[min_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[max_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[min_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[max_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[min_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[max_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[min_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[max_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[min_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[max_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[collection_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[sql_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[plan_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\MRG - Append current stats on interesting queries.Outputs[Merge Join Output].Columns[snapshot_id]}

true-1010203040005

#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[sql_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[plan_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[statement_start_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[statement_end_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[creation_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[plan_generation_num]}

true-101

#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\SRT - Find unique sql_handle and plan_handle combinations.Outputs[Sort Output].Columns[sql_handle]}

true-1000102

#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\TPQ - Analyze Interesting Queries.Outputs[TxTopQueries Only Output].Columns[Top Reason]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\TPQ - Analyze Interesting Queries.Outputs[TxTopQueries Only Output].Columns[Top SQL Handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\TPQ - Analyze Interesting Queries.Outputs[TxTopQueries Only Output].Columns[Top Plan Handle]}

#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[old_total_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[old_total_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[old_total_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[old_total_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[old_total_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[old_total_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[statement_start_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[statement_end_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[creation_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[plan_generation_num]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[last_execution_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[execution_count]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[total_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[total_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[total_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[total_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[total_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[total_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[max_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[min_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[min_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[max_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[min_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[max_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[min_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[max_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[min_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[max_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[min_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[max_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[sql_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[plan_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[snapshot_id]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[snapshot_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[snapshot_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[snapshot_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[snapshot_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[snapshot_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[snapshot_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[collection_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[snapshot_execution_count]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[old_execution_count]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[statement_start_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[statement_end_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[collection_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[sql_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[plan_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[plan_generation_num]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine interesting requests and interesting stats.Outputs[Union All Output 1].Columns[creation_time]}

#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[snapshot_id]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[statement_start_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[statement_end_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[plan_generation_num]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[creation_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[last_execution_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[execution_count]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[total_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[total_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[total_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[total_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[total_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[total_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[max_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[min_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[min_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[max_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[min_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[max_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[min_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[max_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[min_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[max_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[min_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[max_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[collection_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[sql_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[plan_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[statement_start_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[statement_end_offset]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[plan_generation_num]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[creation_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[last_execution_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[execution_count]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[total_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[total_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[total_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[total_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[total_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[total_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[max_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[min_worker_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[min_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[max_physical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[min_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[max_logical_writes]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[min_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[max_logical_reads]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[min_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[max_clr_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[min_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[max_elapsed_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[snapshot_id]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[collection_time]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[sql_handle]}#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Create Interesting Queries Upload Batch\UNION - Combine last and current snapshots for TopN processing.Outputs[Union All Output 1].Columns[plan_handle]}

EXEC [snapshots].[sp_get_unknown_query_plan] @source_id = 1[dbo].[sp_syscollector_text_query_plan_lookpup] ?,?,?[dbo].[sp_syscollector_text_query_plan_lookpup] ?,?,?02102525<referenceMetadata><referenceColumns><referenceColumn name="plan_handle" dataType="DT_BYTES" length="64" precision="0" scale="0" codePage="0"/><referenceColumn name="statement_start_offset" dataType="DT_I4" length="0" precision="0" scale="0" codePage="0"/><referenceColumn name="statement_end_offset" dataType="DT_I4" length="0" precision="0" scale="0" codePage="0"/><referenceColumn name="database_id" dataType="DT_I2" length="0" precision="0" scale="0" codePage="0"/><referenceColumn name="object_id" dataType="DT_I4" length="0" precision="0" scale="0" codePage="0"/><referenceColumn name="object_name" dataType="DT_WSTR" length="128" precision="0" scale="0" codePage="0"/><referenceColumn name="query_plan" dataType="DT_NTEXT" length="0" precision="0" scale="0" codePage="0"/></referenceColumns></referenceMetadata>#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Find and save plan text\ODS - Get all plan handles without saved plan.Outputs[OLE DB Source Output].Columns[plan_handle]};#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Find and save plan text\ODS - Get all plan handles without saved plan.Outputs[OLE DB Source Output].Columns[statement_start_offset]};#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Find and save plan text\ODS - Get all plan handles without saved plan.Outputs[OLE DB Source Output].Columns[statement_end_offset]};1252falsestatement_start_offsetstatement_end_offsetplan_handle

database_idobject_idobject_namequery_plan

0EXEC snapshots.sp_update_query_plan ?,?,?,?,?,?,?,?,?,?1252816565818181818165193

0User::SourceSqlQuery1252false3

User::SqlPlanUpdateRowCountOut

User::SqlPlanUpdateRowCountError

User::SqlPlanUpdateRowCountIn

EXEC [snapshots].[sp_get_unknown_query_text] @source_id = 1[dbo].[sp_syscollector_sql_text_lookup] ?[dbo].[sp_syscollector_sql_text_lookup] ?02102525<referenceMetadata><referenceColumns><referenceColumn name="sql_handle" dataType="DT_BYTES" length="64" precision="0" scale="0" codePage="0"/><referenceColumn name="database_id" dataType="DT_I2" length="0" precision="0" scale="0" codePage="0"/><referenceColumn name="object_id" dataType="DT_I4" length="0" precision="0" scale="0" codePage="0"/><referenceColumn name="object_name" dataType="DT_WSTR" length="128" precision="0" scale="0" codePage="0"/><referenceColumn name="sql_text" dataType="DT_NTEXT" length="0" precision="0" scale="0" codePage="0"/></referenceColumns></referenceMetadata>#{Package\SEQ - Capture and analyze query statistics and query plan and text\DFT - Find and save sql text\ODS - Get all sql handles without saved sql text.Outputs[OLE DB Source Output].Columns[sql_handle]};1252falsesql_handle

database_idobject_idobject_namesql_text

0snapshots.sp_get_unknown_query_text @source_id = 3User::SourceSqlQuery1252false3

0EXEC snapshots.sp_update_query_text ?, ?, ?, ?, ?, ?12526581818165193

User::SqlTextUpdateRowCountOut

User::SqlTextUpdateRowCountError

User::SqlTextUpdateRowCountIn

@[User::InterestingQueriesLastSnapshotFileName]0c:\perflogs\SETHS-SSISVSTA2_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity_QueryStats_LastSnapshot.cacheQueryActivityLock