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.
Q3. How to know which all Extended event sessions are Started
A3. Below statement shows which are sessions are started.
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
ADD EVENT <package>.<event1> [Where <condition>]..
ADD EVENT <package>.<event2> [Where <condition>]..
ADD TARGET <target>
Q7.How to know list of packages and associated events to be configured for creating session.
Following statement shows list of packages available for session
Following statement shows list of event objects available for session
Joining these two view we will get the list of packags.events available for use of session creation.
SELECT xp.[name], xo.*
WHERE xp.[guid] = xo.[package_guid]
Eg. Sqlserver.sql_statement_completed or Sqlserver. sp_statement_completed
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
Q9. How to START and STOP sessions
A9. You can change the Event Session using ALTER EVENT Session statement.
Eg. Alter event session session1 on server STATE = START | STOP
Q10. How to drop the event session.
A10. Using DROP EVENT SESSION
DROP EVENT SESSION session1
This is the basic QA on Extended Events.