37/10/2007 4:26:36 PM51-1010107{BA2DE61E-445D-4D02-86F2-9CB913757C45}-10000ConfigConnection{3B56FA3C-B4D3-457B-AC7A-0D16AB3B5C68}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;0MdwConnection{0F31739E-7104-42DD-A7A9-8BCC6A94F5E1}Connection used to upload collected data to a warehouse.OLEDB@[User::MDWConnectionString]-1Data Source=(local);Initial Catalog=MDW;Provider=SQLNCLI10;Integrated Security=SSPI;APP=SQL Server Data Collector;0TargetConnection{10677B95-D149-4F8E-999E-D251D5EE8B43}Connection used to get information from the monitored (target) instance.OLEDB@[User::TargetConnectionString]-1Data Source=(local);Provider=SQLNCLI10;Integrated Security=SSPI;APP=SQL Server Data Collector;0User::CacheDirectory\Package.Variables[User::CacheDirectory].Properties[Value]CacheDirectory{633E4635-F81C-4E65-9404-A654C3E9B6A5}0User::CacheWindow\Package.Variables[User::CacheWindow].Properties[Value]CacheWindow{8629F0AC-3CED-4F7A-8ECE-379320CED059}0User::InputParameters\Package.Variables[User::InputParameters].Properties[Value]InputParameters{5D0DF460-0DBB-47A0-AC30-B50214D5ADD8}0User::MDWConnectionString\Package.Variables[User::MDWConnectionString].Properties[Value]MDWConnectionString{F558E466-1014-4503-B3C4-F2D117AEA7C2}0User::ConfigConnectionString\Package.Variables[User::ConfigConnectionString].Properties[Value]ConfigConnectionString{4A27EC58-5FFC-442B-89CF-178E52BD3AB5}0User::TargetConnectionString\Package.Variables[User::TargetConnectionString].Properties[Value]TargetConnectionString{B7A483F7-7093-43FF-9899-0BE9B6DB9910}0User::LoopEventName\Package.Variables[User::LoopEventName].Properties[Value]LoopEventName{87D3CF50-E186-4364-9BBA-02F6C3CCEC71}0User::ExitEventName\Package.Variables[User::ExitEventName].Properties[Value]ExitEventName{EC7EA7E7-49B1-4F71-88EF-518390C29D37}0User::CollectorTypeUID\Package.Variables[User::CollectorTypeUID].Properties[Value]CollectorTypeUID{61F2CCDC-9610-4B14-AD74-E1D649991C55}0User::CollectionSetUID\Package.Variables[User::CollectionSetUID].Properties[Value]CollectionSetUID{C61BC861-FA1D-40EA-A080-D968F4E81B53}0User::ItemID\Package.Variables[User::ItemID].Properties[Value]ItemID{9F98F2F5-C9B3-4B12-BF0D-B5C6630BB559}0User::InstanceID\Package.Variables[User::InstanceID].Properties[Value]InstanceID{2BDA5822-41CC-4648-AE06-50C23B981FB7}0User::InstanceName\Package.Variables[User::InstanceName].Properties[Value]InstanceName{938427CA-5938-4AB4-8BD4-6D03F4AA1668}0User::LoggingLevel\Package.Variables[User::LoggingLevel].Properties[Value]LoggingLevel{7FB5AD01-BF4C-48AB-8B19-C304FC6D5856}0User::LogID\Package.Variables[User::LogID].Properties[Value]LogID{EDCDC174-6C33-417B-AC00-2B1D3A9B5E47}0User::ParentLogID\Package.Variables[User::ParentLogID].Properties[Value]ParentLogID{4BE949CA-F6ED-41ED-8FF1-D601FC10EF59}0User::DaysUntilExpiration\Package.Variables[User::DaysUntilExpiration].Properties[Value]DaysUntilExpiration{9C3E3ACE-BCA2-4A90-8C43-611B24BA1386}10.0.1101.000-10001104857610331-10User00c:\perflogsCacheDirectory{0D31F31B-2902-4213-A8A8-7D1431B4E12A}@[User::CacheDirectory] + "\\" + @[System::MachineName] + "_" + @[User::InstanceID] + "_{" + @[User::CollectionSetUID] + "}_" + (DT_WSTR, 10) @[User::ItemID] + "_QueryActivity"-1User00c:\perflogs\AHMEDAY03_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivityCacheFilePrefixPattern{98734813-0BE8-4259-84A3-FBE9005C0060}0User001CacheWindow{127813E5-2306-4B0E-AC18-1CEF5BCFB4F2}0User002DC02BD6-E230-4C05-8516-4E8C0EF21F95CollectionSetUID{5ECFD22C-CC03-4069-8ADC-7B957D774B37}0User001d24f966-c63c-497c-a6bc-df2369b8ebdfCollectorTypeUID{2407AD35-3588-4CE0-90EC-B31D046968A8}0User00Provider=SQLNCLI10;Data Source=(local);Integrated Security=SSPI;Initial Catalog=msdb;APP=SQL Server Data CollectorConfigConnectionString{EDC36B97-6DC3-4112-B66F-020B4CAC543A}0User000DaysUntilExpiration{88409D9D-29AA-4F8A-AD77-828F31B0951F}0User00EXITEVENTExitEventName{F50DEC43-918A-4F5B-ABB2-C5B7B12CF610}0User00InputParameters{778EF6CD-514D-4284-881D-6C88D7BE9273}0User00MSSQL.1InstanceID{0EDD4F59-D95A-4A01-AA55-41F3614C6DAD}0User00MSSQLSERVERInstanceName{D0846FDA-CF2A-4A79-A897-A68487205C76}0User00100ItemID{1481104D-8C33-4ADE-8DA0-BD1B99652190}0User002LoggingLevel{65917F90-BC1B-4AC8-94F7-CCAFCEE39E18}0User000LogID{988F7667-2EA3-4C87-952E-8DB6C04607C0}0User000LoopDuration{7907766C-40A0-4152-9FF6-24103A3CB310}0User00LOOPEVENTLoopEventName{56261049-39E9-40C1-8E84-7F3F4323B03B}0User00Provider=SQLNCLI10;Data Source=(local);Integrated Security=SSPI;Initial Catalog=MDW;APP=SQL Server Data CollectorMDWConnectionString{65ECC61B-DF11-4F7B-B82C-FBE968A64EDF}0User000ParentLogID{8998BC35-7C0F-4234-B82E-0183E6092955}0User00Provider=SQLNCLI10;Data Source=(local);Integrated Security=SSPI;APP=SQL Server Data CollectorTargetConnectionString{E8FA8FF0-7CA5-4F9B-80EC-164E43992C14}102,7,OnError,9,OnWarning-1-1-1-1-1-1-1-1-1-1-1-1-1-1{7283E196-D60C-438F-A58A-0EA1F41BBCF4}{7283E196-D60C-438F-A58A-0EA1F41BBCF4}DC.ForEachEventEnumerator.1@[User::ExitEventName]@[User::LoopEventName]100-100-101048576-1100User000DMVErrorRowCount{95939949-1EF7-4DBA-AEF0-B10AC4B72C90}0User000DMVInOutRowCount{B356F852-7B0D-4AA4-B0AF-8059B4115055}2000-100-101048576-1100User00QueryActivityLockSyncRoot{182E1184-374B-4AE0-A2FB-86F07932D2F3}010Performs 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@[User::CacheFilePrefixPattern] + ".cache"-1User00c:\perflogs\AHMEDAY03_MSSQL.1_{2DC02BD6-E230-4C05-8516-4E8C0EF21F95}_100_QueryActivity.cacheCacheFileName{DEFF42DC-F496-4750-AAAF-1C264D92FC49}00DFT - Collect DMV Data{CA0ED25A-27F6-4388-8E38-1F26D1A55878}Data Flow TaskSSIS.Pipeline.20User::DMVInOutRowCount

User::DMVErrorRowCount

1User::CacheFileName20SET NOCOUNT ON -- Get the collection time as UTC time DECLARE @collection_time datetime SET @collection_time = GETDATE() SELECT CONVERT(int, ROW_NUMBER() OVER (ORDER BY sess.session_id, ISNULL (req.request_id, -1), ISNULL (tasks.exec_context_id, -1)) ) AS row_id, -- IDs and Blocking IDs sess.session_id, ISNULL (req.request_id, -1) AS request_id, ISNULL (tasks.exec_context_id, -1) AS exec_context_id, ISNULL (req.blocking_session_id, 0) AS blocking_session_id, CONVERT (bit, CASE WHEN EXISTS (SELECT TOP 1 session_id FROM sys.dm_exec_requests bl WHERE bl.blocking_session_id = req.session_id) THEN 1 ELSE 0 END) AS is_blocking, ISNULL (waits.blocking_exec_context_id, 0) AS blocking_exec_context_id, tasks.scheduler_id, DB_NAME(req.database_id) as database_name, req.[user_id], -- State information LEFT (tasks.task_state, 10) AS task_state, LEFT (req.status, 15) AS request_status, LEFT (sess.status, 15) AS session_status, req.executing_managed_code, -- Session information sess.login_time, sess.is_user_process, LEFT (ISNULL (sess.[host_name], ''), 20) AS [host_name], LEFT (ISNULL (sess.[program_name], ''), 50) AS [program_name], LEFT (ISNULL (sess.login_name, ''), 30) AS login_name, -- Waits information LEFT (ISNULL (req.wait_type, ''), 45) AS wait_type, LEFT (ISNULL (req.last_wait_type, ''), 45) AS last_wait_type, ISNULL (waits.wait_duration_ms, 0) AS wait_duration_ms, LEFT (ISNULL (req.wait_resource, ''), 50) AS wait_resource, LEFT (ISNULL (waits.resource_description, ''), 140) AS resource_description, -- Transaction information req.transaction_id, ISNULL(req.open_transaction_count, 0) AS open_transaction_count, COALESCE(req.transaction_isolation_level, sess.transaction_isolation_level) AS transaction_isolation_level, -- Request stats req.cpu_time AS request_cpu_time, req.logical_reads AS request_logical_reads, req.reads AS request_reads, req.writes AS request_writes, req.total_elapsed_time AS request_total_elapsed_time, req.start_time AS request_start_time, -- Session stats sess.memory_usage, sess.cpu_time AS session_cpu_time, sess.reads AS session_reads, sess.writes AS session_writes, sess.logical_reads AS session_logical_reads, sess.total_scheduled_time AS session_total_scheduled_time, sess.total_elapsed_time AS session_total_elapsed_time, sess.last_request_start_time, sess.last_request_end_time, req.open_resultset_count AS open_resultsets, sess.row_count AS session_row_count, sess.prev_error, tasks.pending_io_count, -- Text/Plan handles ISNULL (req.command, 'AWAITING COMMAND') AS command, req.plan_handle, req.sql_handle, req.statement_start_offset, req.statement_end_offset, @collection_time AS collection_time FROM sys.dm_exec_sessions sess LEFT OUTER MERGE JOIN sys.dm_exec_requests req ON sess.session_id = req.session_id LEFT OUTER MERGE JOIN sys.dm_os_tasks tasks ON tasks.session_id = sess.session_id AND tasks.request_id = req.request_id AND tasks.task_address = req.task_address LEFT OUTER MERGE JOIN sys.dm_os_waiting_tasks waits ON waits.session_id = sess.session_id AND waits.waiting_task_address = req.task_address WHERE sess.session_id <> @@SPID AND ( (req.session_id IS NOT NULL AND (sess.is_user_process = 1 OR req.status COLLATE Latin1_General_BIN NOT IN ('background', 'sleeping'))) -- active request OR (sess.session_id IN (SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0)) -- not active, but head blocker ) OPTION (FORCE ORDER)User::DMVSelectSQL1252false2

SEQ - Synchronize Access to Cache File{248F0EC3-4871-4BFD-9DA7-63A3B581738D}Sequence ContainerSTOCK:SEQUENCE00Execute 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{85357745-D02F-4BB7-ABBC-28E41047CD03}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 Iteration{F9DBCB6D-CA5E-49E5-8C4D-5AD6A9EEBBBF}Execute SQL TaskMicrosoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91022-1Constraint 1{1C376D39-3871-4711-8851-75863FF0845A}23-1@[User::LoggingLevel]>=1Constraint{0A82779D-65F0-460C-9C72-D270AADB77FD}LOOP - Query the query statitics DMV at collection frequency{6001FD2A-A659-4BE6-82A0-98841B568942}Foreach Loop ContainerSTOCK:FOREACHLOOP0User::LoopDuration0{FAB5ABC8-B3C8-4260-80D3-A7BF47E886B1}{FAB5ABC8-B3C8-4260-80D3-A7BF47E886B1}QueryActivityCollect{0B68FC9D-23DC-48F3-A937-90A0A8943D0E}SSIS.Package.20