37/10/2007 4:27:11 PM51-1010342{B5039480-2A73-44B8-9C54-174493C8A829}-10000ConfigConnection{F7E01FB6-8623-4709-92F3-6F3AA321E26E}Connection used to get information about collection configuration and for logging purposes.OLEDB@[User::ConfigConnectionString]0Data Source=(local);Initial Catalog=msdb;Provider=SQLNCLI10;Integrated Security=SSPI;APP=SQL Server Data Collector;0ConfigConnection2{0742890B-9C4D-4A50-B3DC-5B95C23AE7E2}OLEDB@[User::ConfigConnectionString]0Data Source=(local);Initial Catalog=msdb;Provider=SQLNCLI10;Integrated Security=SSPI;APP=SQL Server Data Collector;0MdwConnection{6F8B3DEC-B78D-4784-B3DB-455D7A51DA93}Connection used to upload collected data to a warehouse.OLEDB@[User::MDWConnectionString]0Data Source=(local);Initial Catalog=MDW;Provider=SQLNCLI10;Integrated Security=SSPI;APP=SQL Server Data Collector;0TargetConnection{824F4D6D-D478-4DA6-A12F-89D59EA9916C}Connection used to get information from the monitored (target) instance.OLEDB@[User::TargetConnectionString]-1Data Source=(local);Initial Catalog=master;Provider=SQLNCLI10;Integrated Security=SSPI;APP=SQL Server Data Collector;0User::CacheDirectory\Package.Variables[User::CacheDirectory].Properties[Value]CacheDirectory{3BF030A5-F9F3-48EC-991F-AFC03D683C78}0User::CacheWindow\Package.Variables[User::CacheWindow].Properties[Value]CacheWindow{AE9CDFC3-4CC4-464F-B35F-7810BA1ABBD2}0User::InputParameters\Package.Variables[User::InputParameters].Properties[Value]InputParameters{9D55EE56-6910-4E32-B537-25B0C128C430}0User::MDWConnectionString\Package.Variables[User::MDWConnectionString].Properties[Value]MDWConnectionString{32958205-BE98-4005-848D-9CDAD18B485C}0User::ConfigConnectionString\Package.Variables[User::ConfigConnectionString].Properties[Value]ConfigConnectionString{82D5D5A2-D6CF-480D-93EF-312C238B6140}0User::TargetConnectionString\Package.Variables[User::TargetConnectionString].Properties[Value]TargetConnectionString{D036A0FF-DD97-48FB-85FC-9153012354AC}0User::LoopEventName\Package.Variables[User::LoopEventName].Properties[Value]LoopEventName{5652572B-C4D6-446B-B4DA-D930950D088F}0User::ExitEventName\Package.Variables[User::ExitEventName].Properties[Value]ExitEventName{A9B77148-085F-4D14-9E2E-37A57D6757BC}0User::CollectorTypeUID\Package.Variables[User::CollectorTypeUID].Properties[Value]CollectorTypeUID{9E1D409F-1A3C-4174-A505-0FFAA612160D}0User::CollectionSetUID\Package.Variables[User::CollectionSetUID].Properties[Value]CollectionSetUID{89536777-0CCF-4694-9F39-D5E318BCB934}0User::ItemID\Package.Variables[User::ItemID].Properties[Value]ItemID{6EBC5A5F-39A5-4286-8734-21B59CDE9649}0User::InstanceID\Package.Variables[User::InstanceID].Properties[Value]InstanceID{F6E7BDA0-036F-4B1B-94EB-8406B1E9B20F}0User::InstanceName\Package.Variables[User::InstanceName].Properties[Value]InstanceName{84CBFFD3-0BD5-4CC1-9897-5AEB3F7D4DA6}0User::LoggingLevel\Package.Variables[User::LoggingLevel].Properties[Value]LoggingLevel{A3026DB9-2F6D-4E83-BD74-44A6F4D1A7CC}0User::LogID\Package.Variables[User::LogID].Properties[Value]LogID{EA9FBE84-EE1C-4E72-ADF0-EF0FD3A11359}0User::ParentLogID\Package.Variables[User::ParentLogID].Properties[Value]ParentLogID{FFB4EB4D-E314-4D92-8187-3E00EB488A86}0User::DaysUntilExpiration\Package.Variables[User::DaysUntilExpiration].Properties[Value]DaysUntilExpiration{8C4258C5-F791-4524-8ECA-66D01EFDC80D}10.0.1600.2200-100-11104857610331-10User002345c:\perflogsCacheDirectory{ECDC2A49-72C5-46F3-BD6E-DCEB9DEEF72D}0User0067891CacheWindow{DE97580A-42C9-4CB8-9D44-303453E76B39}@[User::CollectionCacheFilePrefixPattern] + ".cache"-1User002345c:\perflogs\BARTD1_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity.cacheCollectionCacheFileName{80DBEFB8-CF7D-49AF-98DB-A24454AAE017}Stores snapshots of dm_exec_requests@[User::CollectionCacheFilePrefixPattern] + @[System::ExecutionInstanceGUID] + ".temp.cache"-1User002345c:\perflogs\BARTD1_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity{B94D129B-6845-44DC-B401-1F01C2260665}.temp.cacheCollectionCacheFileNameOutput{DE5A4983-C608-4B75-8DE1-3BB14CD6A6F3}Stores snpashots of dm_exec_requests. This variable is used as temporary cached file name during the upload.@[User::CacheDirectory] + "\\" + @[System::MachineName] + "_" + @[User::InstanceID] + "_{" + @[User::CollectionSetUID] + "}_" + (DT_WSTR, 10) @[User::ItemID] + "_QueryActivity"-1User002345c:\perflogs\BARTD1_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivityCollectionCacheFilePrefixPattern{3530767F-ABC9-4E82-9B5A-25C464FDC0FF}Prefix for files related to dm_exec_requets collection0User0023452DC02BD6-E230-4C05-8516-4E8C0EF21F95CollectionSetUID{A1EACD35-8916-4D0F-A685-D77F328496D0}0User00234514AF3C12-38E6-4155-BD29-F33E7966BA23CollectorTypeUID{207FCBB5-8712-48C8-9536-D21B01E44307}0User002345Provider=SQLNCLI10;Data Source=(local);Integrated Security=SSPI;Initial Catalog=msdb;APP=SQL Server Data CollectorConfigConnectionString{ADFFFFF9-2BA8-4A62-B0E5-E634A7963AD5}0User0067890DaysUntilExpiration{9428F544-570A-47AC-9430-B3CBAA8CBBF5}0User002345EXITEVENTExitEventName{EDD12792-66C0-4339-AD29-2C1BA6A72377}0User002345InputParameters{2F1DD347-A479-4B20-9553-9F753A9590DE}0User002345MSSQL.1InstanceID{69E5C04F-C310-4DE5-B16D-AD03966D10CF}0User002345MSSQLSERVERInstanceName{461689AB-102E-4FDB-976F-A0443165631D}0User0067895000InterestingQueries_HighCpuUsage{3D154C22-E253-487D-9459-994FD35B5992}0User006789500InterestingQueries_HighIoCount{5DCD9A0E-9304-4624-85AF-FE7C903452D6}0User00678912800InterestingQueries_HighMemoryUsage{29BBAFE9-D1B1-4B9F-BDBF-F56F438B3D11}0User0067898InterestingQueries_HighTransactionCount{F749610B-0097-4E18-AC45-92448389B8CC}0User0067891000000InterestingQueries_LargeResultset{05FDE7F5-E28F-4B0D-A564-328115F89665}0User00678925000InterestingQueries_LongRunningRequest{F3053BBB-B032-4523-80B0-BBDFB3FAA960}0User006789500InterestingQueries_LongWaitingRequest{2ED44D20-89E3-4FEA-88E6-CE8A86AD7B57}0User0067890InterestingQueriesCount{82294C7A-5F89-4854-985C-B7BC5057328A}@[User::InterestingQueriesSnapshotFileNamePrefixPattern] + "LastSnapshot.cache"-1User002345c:\perflogs\BARTD1_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity_QueryStats_LastSnapshot.cacheInterestingQueriesLastSnapshotFileName{D3D09BF8-5470-485D-AEA6-A60E8A9C4BD2}Stores the most recent copy of snapshot of dm_exec_query_stats saved from the previous execution of this package.@[User::InterestingQueriesSnapshotFileNamePrefixPattern] + "LastSnapshot.temp.cache"-1User002345c:\perflogs\BARTD1_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity_QueryStats_LastSnapshot.temp.cacheInterestingQueriesLastSnapshotFileNameOutput{8391E18B-AD8C-43B0-8D25-4493C59FE8A2}Stores temporary copy of the most recent snapshot of dm_exec_query_stats@[User::CollectionCacheFilePrefixPattern] + "_QueryStats_"-1User002345c:\perflogs\BARTD1_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity_QueryStats_InterestingQueriesSnapshotFileNamePrefixPattern{99DA43BE-ACDE-416C-86D7-CE128B6CD421}Prefix for files related to dm_exec_query_stats collection.0User006789100ItemID{5F9FBD77-E7C8-47C8-B215-BAD55CD8B1F6}0User0067892LoggingLevel{BAF382F0-D512-41DD-B071-3C911D345679}0User0067890LogID{2815FBB1-5FF8-4239-B64F-157684EDC541}0User0067890LoopDuration{91DF209A-CDD3-4D5F-8B66-01F5BCD68451}0User002345LOOPEVENTLoopEventName{63509AC4-9FDF-401B-8BF7-D5E8A4E15D3E}0User002345Provider=SQLNCLI10;Data Source=(local);Integrated Security=SSPI;Initial Catalog=MDW;APP=SQL Server Data CollectorMDWConnectionString{6B59DA82-0FC8-4852-9B0A-72C222FEB41A}0User0067890ParentLogID{9CC205C1-0562-4615-8E43-C03F1149C479}0User006789200SnapshotId{E0926573-ABF4-40F5-A41E-B7402B414B5E}0User0067891SourceId{786411BF-45F1-40DE-9276-9C6315B227B7}0User002345Provider=SQLNCLI10;Initial Catalog=master;Data Source=(local);Integrated Security=SSPI;APP=SQL Server Data CollectorTargetConnectionString{020B6D3D-5EA9-4AE5-92D6-8A3D3A500796}0User0067895TopThreshold{5E1A6507-A910-4627-85D2-E086F98658AE}102,7,OnError,9,OnWarning-1-1-1-1-1-1-1-1-1-1-1-1-1-10Execute SQL Task; Microsoft Corporation; Microsoft SQL Server code name "Katmai"; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100-101048576-11020SQL - Get SourceId{AAE49284-A0BD-4629-80BE-DC3A67047B4B}Execute SQL TaskMicrosoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc9100File Access;Microsoft Corporation; Microsoft SQL Server codename Katmai; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100011048576-11001@[User::CollectionCacheFileName]0FAT - Check Cache File Exists{E84D592A-C532-49A6-9F99-108607D1A4E2}File Access TaskDTS.FileAccessTask.100c:\perflogs\BARTD1_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity.cache00-100-101048576-1100User002345QueryActivityLockSyncRoot{3EEA7BDD-73CC-465C-8727-577DF6DDE716}200File System Task;Microsoft Corporation; Microsoft SQL Server code name "Katmai"; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100-101048576-11000FST - Rename Cache File{6C243850-BA61-44CA-85FF-EE9C4AF98AB1}File System TaskMicrosoft.SqlServer.Dts.Tasks.FileSystemTask.FileSystemTask, Microsoft.SqlServer.FileSystemTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc910SEQ - Rename Collection Cache File{0007AEF3-7F17-4168-89F0-4B015E6004EA}Sequence ContainerSTOCK:SEQUENCE00Cache Window Maintainance Task; Microsoft Corporation; Microsoft SQL Server v10; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100-101048576-11020"User::InterestingQueriesSnapshotFileNamePrefixPattern"CWM - Remove Upload Snapshots Older Than CacheWindow{AFE8DBBE-1BFF-485D-84F7-C8E187C21AAD}Cache Window Maintainance TaskMicrosoft.SqlServer.Management.Collector.Tasks.CacheWindowMaintainanceTask, Microsoft.SqlServer.Management.CollectorTasks, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc9100Execute SQL Task; Microsoft Corporation; Microsoft SQL Server code name "Katmai"; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100-101048576-11020SQL - Get SnapshotId{7E54C204-6CEE-47E5-9970-9CA34861603C}Execute SQL TaskMicrosoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91000-100-101048576-1100User0067890InterestingQueriesRowCountIn{9282ECEC-3842-4736-BD6F-5EBE84C40B65}0User0067890InterestingQueriesRowCountOut{2C3D5109-A414-4C3A-AA00-9B7C26C1BA7F}0User0067890SqlPlanRowCountError{E313A9FA-038A-45C4-8B2C-36500F3B1B36}0User0067890SqlPlanRowCountIn{30F0C986-5BCC-417D-93B3-A914D23B2ADA}@[User::SqlPlanRowCountIn] - @[User::SqlPlanRowCountError]-1User0023450SqlPlanRowCountOut{E79C8107-AB1D-457D-8EF4-3CECE6D23ED6}0User0067890SqlPlanUpdateRowCountError{6286A49C-E37E-48CB-B3A5-37A67DC33CA6}0User0067890SqlPlanUpdateRowCountIn{4830053E-1F97-4654-B88C-19EA5D3DD6E1}0User0067890SqlPlanUpdateRowCountOut{050E62F6-E62C-45A8-B626-EDE564DEE50B}0User0067890SqlTextRowCountError{1941E039-AD56-4AB1-AAE0-A7C43942502C}0User0067890SqlTextRowCountIn{0F9E4107-AABD-469E-AC4B-CD12AF2F6041}@[User::SqlTextRowCountIn] - @[User::SqlTextRowCountError]-1User0023450SqlTextRowCountOut{61DBE0AB-4BD0-4454-93D9-9C5C3913505C}0User0067890SqlTextUpdateRowCountError{0C51AC18-BC56-4702-9F11-73693BA426B4}0User0067890SqlTextUpdateRowCountIn{C27F9149-D1C4-4532-9707-758955380CE8}0User0067890SqlTextUpdateRowCountOut{0A692E40-19DB-46F4-9E37-F6155DE3385D}200File Access;Microsoft Corporation; Microsoft SQL Server codename Katmai; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100001048576-11000@[User::InterestingQueriesLastSnapshotFileName]FAT - Check Interesting Queries Last Snapshot exists{5697BEEE-7505-4E6D-B74B-5E6474C31775}File Access TaskDTS.FileAccessTask.100c:\perflogs\BARTD1_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity_QueryStats_LastSnapshot.cache0Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v10; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100-101048576-110"SELECT [sql_handle], [plan_handle], [statement_start_offset], [statement_end_offset], [creation_time] FROM [snapshots].[fn_get_notable_query_plans](" + (DT_WSTR, 10) @[User::SourceId] + ")"-1User002345SELECT [sql_handle], [plan_handle], [statement_start_offset], [statement_end_offset], [creation_time] FROM [snapshots].[fn_get_notable_query_plans](1)NotableQueryPlan{64559B51-01E3-4347-9BB6-EF6BFF69BE07}"SELECT [sql_handle] FROM [snapshots].[fn_get_notable_query_text](" + (DT_WSTR, 10) @[User::SourceId] + ")"-1User002345SELECT [sql_handle] FROM [snapshots].[fn_get_notable_query_text](1)NotableQueryText{8E903CA4-55FB-4E21-8CD3-08E6E08BC424}00DFT - Create Interesting Queries Upload Batch{6821C05A-E8A1-4C78-AEDB-33155F9CB281}Data Flow TaskSSIS.Pipeline.20

@[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

1User::InterestingQueriesLastSnapshotFileNameOutput0true-1000102

806280638064

22true5

9229923292359238924192449247925092539256925992629265926892719274927792809283928692899292929592989301910891099308

75107438744174447447745074537456745974627465746874717474747774807483748674897492749574987501750475077513751675197438744174447447745074537456745974627465746874717474747774807483748674897492749574987501750475077510751375167519

User::InterestingQueriesRowCountIn

User::InterestingQueriesRowCountOut

User::SqlPlanRowCountIn

User::SqlTextRowCountIn

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

User::SqlTextRowCountError

User::SqlPlanRowCountError

1User::CollectionCacheFileNameOutput

false-103040500000000000000000000000000000000000000000000010200

9230923392369239924292459248925192549257926092639266926992729275927892819284928792909293929692999302930493069309

1User::InterestingQueriesLastSnapshotFileName

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

25true5

2109921100211012113621139211422114521148211512115421157211602116321166211692117221175211782118121184211872119021193211962119921102211032120621209212122121521218212212122421328

[ISNULL](#21210) ? #21146 : #21146 - #21210ISNULL(old_total_worker_time) ? total_worker_time : total_worker_time - old_total_worker_time[ISNULL](#21213) ? #21149 : #21149 - #21213ISNULL(old_total_physical_reads) ? total_physical_reads : total_physical_reads - old_total_physical_reads[ISNULL](#21219) ? #21155 : #21155 - #21219ISNULL(old_total_logical_reads) ? total_logical_reads : total_logical_reads - old_total_logical_reads[ISNULL](#21216) ? #21152 : #21152 - #21216ISNULL(old_total_logical_writes) ? total_logical_writes : total_logical_writes - old_total_logical_writes[ISNULL](#21222) ? #21158 : #21158 - #21222ISNULL(old_total_clr_time) ? total_clr_time : total_clr_time - old_total_clr_time[ISNULL](#21225) ? #21161 : #21161 - #21225ISNULL(old_total_elapsed_time) ? total_elapsed_time : total_elapsed_time - old_total_elapsed_time[ISNULL](#21329) ? #21143 : #21143 - #21329ISNULL(old_execution_count) ? execution_count : execution_count - old_execution_count

0[snapshots].[query_stats]1252false3falsefalseCHECK_CONSTRAINTS0

18727187301873318736187391874218745187481875118754187571876018763187661876918772187751877818781187841878718790187931879618799188021880518808188111881418820188231882618829188321883518838188411884418817214232142618745187481881718820188231875418751

true-1010203040005

188201882318745187481875118754

true-101

13644

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

0[snapshots].[notable_query_text]1252false3falsefalseCHECK_CONSTRAINTS1000

0[snapshots].[notable_query_plan]1252false3falsefalseCHECK_CONSTRAINTS1000

(#18276 > @[User::InterestingQueries_LongWaitingRequest]) || (#18242 == 0 && #18244 == TRUE ) || (#18284 > @[User::InterestingQueries_HighTransactionCount]) || (#18296 > @[User::InterestingQueries_LongRunningRequest]) || (#18288 > @[User::InterestingQueries_HighCpuUsage]) || (#18324 > @[User::InterestingQueries_HighIoCount]) || (#18300 > @[User::InterestingQueries_HighMemoryUsage]) || (#18320 > @[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

0File System Task;Microsoft Corporation; Microsoft SQL Server code name "Katmai"; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100-101048576-11000FST - Rename Current Snapshot File as Last Snapshot File{718CDB90-C4F6-4B10-8621-CB6B5B712EF1}File System TaskMicrosoft.SqlServer.Dts.Tasks.FileSystemTask.FileSystemTask, Microsoft.SqlServer.FileSystemTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc9100Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v10; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100-101048576-11-1"EXEC [snapshots].[sp_get_unknown_query_text] @source_id = " + (DT_WSTR,50) @[User::SourceId]-1User002345EXEC [snapshots].[sp_get_unknown_query_text] @source_id = 1SourceSqlQuery{E0C36EF4-0371-4EDB-B268-696CAEA1EC54}01DFT - Find and save sql text{94FEA859-9BBC-479F-ACE0-3C421AE9D4A9}Data Flow TaskSSIS.Pipeline.200snapshots.sp_get_unknown_query_text @source_id = 3User::SourceSqlQuery1252false3

User::SqlTextUpdateRowCountIn

User::SqlTextUpdateRowCountError

User::SqlTextUpdateRowCountOut

[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>#35;1252sql_handle

database_idobject_idobject_namesql_text

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

0Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v10; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100-101048576-11000DFT - Create Interesting Queries Last Snapshot{D0100C41-766D-4E42-8B02-AD4232994DBA}Data Flow TaskSSIS.Pipeline.20

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

1User::InterestingQueriesLastSnapshotFileName0User::InterestingQueriesRowCountIn

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

0Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v10; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100-101048576-11-1"EXEC [snapshots].[sp_get_unknown_query_plan] @source_id = " + (DT_WSTR,50) @[User::SourceId]-1User002345EXEC [snapshots].[sp_get_unknown_query_plan] @source_id = 1SourceSqlQuery{9AF62F8B-3BF0-4B7E-95F6-74C81F03F8C4}01DFT - Find and save plan text{EA6DD0E7-38BB-42D8-AD2A-552098635875}Data Flow TaskSSIS.Pipeline.20User::SqlPlanUpdateRowCountIn

User::SqlPlanUpdateRowCountOut

User::SqlPlanUpdateRowCountError

0User::SourceSqlQuery1252false3

[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>#68;#71;#74;1252statement_start_offsetstatement_end_offsetplan_handle

database_idobject_idobject_namequery_plan

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

0Execute SQL Task; Microsoft Corporation; Microsoft SQL Server code name "Katmai"; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100001048576-11001SQL - Log stats for Interesting Queries{AAC361CD-FC09-4117-A76E-0692170B81D6}Execute SQL TaskMicrosoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc9100Execute SQL Task; Microsoft Corporation; Microsoft SQL Server code name "Katmai"; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100001048576-11001SQL - Log stats for sql plan{844bc1e9-a504-49a3-a994-028d3a5a6436}Execute SQL TaskMicrosoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc9100Execute SQL Task; Microsoft Corporation; Microsoft SQL Server code name "Katmai"; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100001048576-11001SQL - Log stats for sql text{936a6657-376b-431e-9816-f2c077c05dda}Execute SQL TaskMicrosoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91002-1Constraint 1{39822253-B0D1-4DEC-9524-A5AA5D30585C}12-1Last Snapshot Missing{1257FA13-8C12-433B-9CFA-6EFEB3E660DF}02-1Constraint 6{429E60DF-8CEF-4B6F-AFF0-81C2D60D4A7C}230@[User::LoggingLevel]>=1Constraint 5{1913B403-D48A-4B0D-822C-670B12C03949}22-1Constraint 7{D1DB4BA0-FC27-40E3-AD49-7F8BE49D83AD}22-1Constraint 8{1377503B-2FD9-446B-8B67-23FCCFD4A285}230@[User::LoggingLevel]>=1Constraint 3{FA1B6CBE-7A5B-4831-B278-3FE40FC8232D}02-1Constraint{4652B58B-703A-4A8A-961A-2A31B8F6A305}02-1Constraint 2{A09F0730-D4CC-4403-B7B8-6454EF9FC0B7}SEQ - Capture and analyze query statistics and query plan and text{42B4C805-BA61-4093-8D32-10CF7AF6837E}Sequence ContainerSTOCK:SEQUENCE0{812F6BCC-8665-42D5-9F36-96177AD3A6EB}{812F6BCC-8665-42D5-9F36-96177AD3A6EB}DTS.ForEachFileEnumerator.2@[User::CacheDirectory]@[System::MachineName] + "_" + @[User::InstanceID] + "_{" + @[User::CollectionSetUID] + "}_" + (DT_WSTR, 10) @[User::ItemID] + "_QueryActivity*.temp.cache"100-100-101048576-1100User002345C:\Users\macies\AppData\Local\Temp\ecodePerfWH\MACIES4_MSSQL.1_{{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}}_100_QueryActivity.cacheCurrentCollectionCacheFileName{10AD1705-C9D0-4C30-8228-74AE5AF6B586}0User0067890RowCount{6F9F2E68-57A0-4C96-8BDC-08F8CDBB329C}200Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v10; (C) 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100-101048576-11000DFT - Upload collection snapshot{1639B3FF-5CEF-4F96-82FB-DB30263CE1B2}Data Flow TaskSSIS.Pipeline.201User::CurrentCollectionCacheFileName

User::RowCount

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

0[snapshots].[active_sessions_and_requests]1252false3falsefalseCHECK_CONSTRAINTS0

0File System Task;Microsoft Corporation; Microsoft SQL Server code name "Katmai"; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100001048576-11001FST - Delete uploaded cache file{E7565A81-3A70-4D43-B46D-367BBE221C58}File System TaskMicrosoft.SqlServer.Dts.Tasks.FileSystemTask.FileSystemTask, Microsoft.SqlServer.FileSystemTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc9100Execute SQL Task; Microsoft Corporation; Microsoft SQL Server code name "Katmai"; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100-101048576-11000SQL - Log Package Iteration{1351971A-0C19-48B9-861A-99803EC9D0FE}Execute SQL TaskMicrosoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc9100Execute SQL Task; Microsoft Corporation; Microsoft SQL Server code name "Katmai"; © 2007 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;100-100-101048576-11000SQL - Log Package Stats{D2EBDB50-34A1-4056-A229-724B11CFF63A}Execute SQL TaskMicrosoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91023-1@[User::LoggingLevel]>=1Constraint{C16FCF88-EA40-4AEB-B237-9ACBF0FD605A}22-1Constraint 1{B6545A2A-C596-4529-813D-87EAE3E5FFCE}02-1Constraint 2{C4644D87-FBBA-4F47-B925-6A77D6A0A220}FEF - For each collected cache file try upload{60544D92-92C2-4CB1-825A-8056E097A667}Foreach Loop ContainerSTOCK:FOREACHLOOP0User::CurrentCollectionCacheFileName0{B537198C-297D-40F6-B54E-B412D1841E32}{B537198C-297D-40F6-B54E-B412D1841E32}02-1Constraint 3{5975893F-5767-46D9-960E-C8CACC85D96F}02-1Constraint{FC5E7799-EF9B-4652-8AB2-49F2755E2CE4}02-1Constraint 7{AD90E175-8AE5-4D3B-ACA5-78B6A0DC912F}02-1Constraint 8{06FC194D-5FAD-44A7-8DD5-42B7254D858D}02-1Constraint 6{085213EE-5C41-45B6-9560-3FE9ACFCDF0E}02-1Constraint 12{DD28D99F-645B-4DAC-83E0-8F143E5F2BFB}QueryActivityUpload{833DB628-8E19-47A3-92C5-FB1779B52E76}SSIS.Package.20