SQL Server 2008 Extended Events 1:
This is a new bee article for Extended Events.
--Now quickly searching to view all events based on sql keyword
select * from sys.dm_xe_objects
where OBJECT_type = 'event'
and name like'%sql%'
--Now for viewing all object columns of any events
select * from sys.dm_xe_object_columns
where [object_name] = 'sql_statement_completed'
--To view actions in Extended events
select * from sys.dm_xe_objects
where OBJECT_type = 'action'
Events: The SQL Server code is populated with event calls that which is in disabled state by default. Adding events to a session enables those event calls. Once enabled, they will execute the set of functionality defined by the session.
Target: This is an Extended Event Object that can be used to log event information.
Session: This is a Server Object created by the user that defines functionality to be executed every time a set of events happen.
Let us go for a quick Demo now:
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Demo_session')
DROP EVENT SESSION Demo_session ON SERVER
GO
-- CREATES THE SESSION
CREATE EVENT SESSION Demo_session ON SERVER
ADD EVENT sqlserver.error_reported
ADD TARGET package0.asynchronous_file_target
-- CONFIGURES THE FILE TARGET
(set filename = 'G:Datadata1.xel' , metadatafile = 'G:Datadata1.xem')
GO
-- STARTS THE SESSION
ALTER EVENT SESSION Demo_session ON SERVER STATE = START
GO
-- GENERATES AN ERROR
RAISERROR (N'This is Extended Events Demo', -- Message text.
1, -- Severity,
1, 7, 3, N'Demo'); -- Other parameters
GO
-- STOPS LISTENING FOR THE EVENT
ALTER EVENT SESSION Demo_session ON SERVER
STATE = STOP
GO
-- REMOVES THE EVENT SESSION FROM THE SERVER
DROP EVENT SESSION Demo_session ON SERVER
GO
-- REMOVES THE EVENT SESSION FROM THE SERVER
select CAST(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file
('G:Datadata1*.xel','G:Datadata1*.xem', null, null)
In the above demo a Asynchronous file target is used for storing the xel and xem files. These files can be read as shown in last query.
Now let us use “Action” in the Event Sessions to capture SQL_Text when the particular event occurs and also we will use predicate to filter out the actions(like Severity=2 used below) as below:
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Demo_session')
DROP EVENT SESSION Demo_session ON SERVER
GO
-- CREATES THE SESSION
CREATE EVENT SESSION Demo_session ON SERVER
ADD EVENT sqlserver.error_reported
(ACTION (sqlserver.sql_text) WHERE severity = 2 )
ADD TARGET package0.asynchronous_file_target
-- CONFIGURES THE FILE TARGET
(set filename = 'G:Datadata1.xel' , metadatafile = 'G:Datadata1.xem')
GO
-- STARTS THE SESSION
ALTER EVENT SESSION Demo_session ON SERVER STATE = START
GO
-- GENERATES AN ERROR
RAISERROR (N'This is Extended Events Demo with Action Clause to capture SQL Text and we are also using predicate to filter only severity=2', -- Message text.
2, -- Severity,
1, 7, 3, N'Demo'); -- Other parameters
Go
-- GENERATES AN ERROR which is not captured since Severity is not equal to 2
RAISERROR (N'This is Extended Events Demo-This message is not captured', -- Message text.
1, -- Severity,
1, 7, 3, N'Demo'); -- Other parameters
GO
-- STOPS LISTENING FOR THE EVENT
ALTER EVENT SESSION Demo_session ON SERVER
STATE = STOP
GO
-- REMOVES THE EVENT SESSION FROM THE SERVER
DROP EVENT SESSION Demo_session ON SERVER
GO
-- REMOVES THE EVENT SESSION FROM THE SERVER
select CAST(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file
('G:Datadata1*.xel','G:Datadata1*.xem', null, null)