Basics QA on Extended Event

I am learning Extended Event so this is my first blog on extended event, There is a great whitepaper on XEvent by Jonathan Kehayias.

Q1. What is extended event?

A1. It is a light weighted profiler kind of trace introduced on sql server 2008 and above.

Q2. How to know which all Extended Events Sessions are available on this server

A2. Below statement will show you all the available sessions for that server.

SELECT
*
FROM
sys.server_event_sessions

Q3. How to know which all Extended event sessions are Started

A3. Below statement shows which are sessions are started.

SELECT
*
FROM
sys.dm_xe_sessions;

Q3. What is the default XEvent

A3. The default XEvent is “system_health” which starts at startup.

Q4. Where events stores and how much by default.

A4. XEvent output is stored on “ring_buffer” target which is on memory only and maximum size of 4MB, when it reach 4MB old data will be flush.

Q5. Can we save the output in file.

A5. Yes we can change the default output from “Ring_Buffer” – memory to physical file but it will require little IO.

Requires data and metadata files which creating XEVEnt session.( .etl,.mta)

Q6. How to Create Event session ?

A6. Event session is created using command

CREATE EVENT SESSION session1 

ON SERVER 

    ADD EVENT <package>.<event1> [Where <condition>].. 

    ADD EVENT <package>.<event2> [Where <condition>].. 

    ADD TARGET <target> 

http://msdn.microsoft.com/en-us/library/bb677289(v=sql.100).aspx

Q7.How to know list of packages and associated events to be configured for creating session.

A7.

Following statement shows list of packages available for session

Select
*
from
sys.dm_xe_packages

Following statement shows list of event objects available for session

Select
*
from
sys.dm_xe_objects

Joining these two view we will get the list of packags.events available for use of session creation.

SELECT xp.[name], xo.*

FROM
sys.dm_xe_objects xo,
sys.dm_xe_packages xp

WHERE xp.[guid] = xo.[package_guid]

ORDER
BY xp.[name],xo.[name];

Eg. Sqlserver.sql_statement_completed or Sqlserver. sp_statement_completed

http://technet.microsoft.com/en-us/magazine/2009.01.sql2008.aspx

Q8. What is target?

A8. targets are event consumer saves output. It can be a file, and can process data synchronously or asynchronously or in ring_buffer(memory) – default

Eg. package0.ring_buffer

http://msdn.microsoft.com/library/bb630339

Q9. How to START and STOP sessions

A9. You can change the Event Session using ALTER EVENT Session statement.

Includes START|STOP

Eg. Alter event session session1 on server STATE = START | STOP

http://msdn.microsoft.com/en-us/library/bb630368(v=sql.100).aspx

Q10. How to drop the event session.

A10. Using DROP EVENT SESSION

DROP EVENT SESSION session1 

ON SERVER 

This is the basic QA on Extended Events.

Reference:

http://technet.microsoft.com/en-us/magazine/2009.01.sql2008.aspx