Home » Extended Events

Extended Events

sql server DBA 999

SQL Server 2008 Extended Events 3

Now lets create a New Event Session to capture Deadlock Victims


IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Demo_session_Deadlock')
DROP EVENT SESSION Demo_session_Deadlock ON SERVER
GO

-- CREATES THE SESSION
CREATE EVENT SESSION Demo_session_Deadlock
ON SERVER
ADD EVENT
sqlserver.lock_deadlock
( ACTION ( sqlserver.database_id,
sqlserver.client_app_name,
sqlserver.sql_text ) )
ADD TARGET package0.asynchronous_file_target
-- CONFIGURES THE FILE TARGET
(set filename = 'G:Datadata2.xel' , metadatafile = 'G:Datadata2.xem')
GO

-- STARTS THE SESSION
ALTER EVENT SESSION Demo_session_Deadlock ON SERVER STATE = START
GO

Now create a deadlock by using some updates

--DeadLock Simulation
--First Session
begin tran
update [Demo1] set [Column1] = 'abcd'

/*

--Second Session, Run this from Another window

begin tran
update [Demo2] set [column1] = 'abcd'
update [Demo1] … Read the rest

sql server DBA 999

SQL Server 2008 Extended Events 2

SQL Server 2008 Extended Events 2:

Now lets create a New Event Session to capture LongRunning Queries which are running for more that 10 seconds

Now in this post we will write the details of the event Ringer Buffer and XML file. It is not necessary to write at both places but for this demo we will write to both of them.


-- Extended Event for finding the long running queries
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRQ')
DROP EVENT SESSION LongRQ ON SERVER
GO
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRQ')
DROP EVENT SESSION LongRQ ON SERVER
GO
-- Create Event Session
CREATE EVENT SESSION LongRQ
ON SERVER
-- Add events to capture events
Read the rest

sql server DBA 999

SQL Server 2008 Extended Events 1

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 … Read the rest