Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 313

, data.value('(/event/data[@name=''page_id'']/value)
[1]','int') as 'page_id'
, data.value('(/event/data[@name=''file_id'']/value)
[1]','int') as 'file_id'
, data.value('(/event/action[@name=''database_id'']/value)
[1]','int') as 'database_id'
, convert(datetime, data.value('(/event/action[@
name=''collect_system_time'']/text)[1]','nvarchar(50)')) as 'time'
from dbo.Toread_xe_file
go
-- View results
select * from dbo.xe_Tofile_table
--drop event session Longrunningqueryexecution on server
This completes the required steps to set up monitoring resource usage with Extended Events
using SQL Server 2008 R2.
How it works...
We have used the CREATE EVENT SESSION code that creates the event session to
gather the sp_statement_completed value for a long-running query execution, in addition
to the errors that are caused due to deadlocks on the specified server instance. Further,
the event statement will gather related asyncIO requests and acquired locks on the objects
that use a pre-defined target location that allows us to retrieve the results within SQL Server
during execution.
Internally, the SQL Server threads generate operating system binaries (Win32 process and
Win32 module) that are also called as executable modules. Each of these Windows process
modules can contain one or more extended events packages that contain one or more
extended events objects such as type, target, action, map, predicate, and event.
There's more
To obtain a list of Extended Events names from the existing SQL Server 2008 R2 instance,
execute the following TSQL:
SELECT p.name, c.event, k.keyword, c.channel, c.description FROM
(
SELECT event_package = o.package_guid, o.description,
event=c.object_name, channel = v.map_value
FROM sys.dm_xe_objects o
LEFT JOIN sys.dm_xe_object_columns c ON o.name = c.object_name
INNER JOIN sys.dm_xe_map_values v ON c.type_name = v.name
AND c.column_value = cast(v.map_key AS nvarchar)