Extended Events in SQL Azure

Hi Everybody

 

Today an English post about 'Extended Events in SQL Azure', some of you shorten the name to 'EE' and some to 'XEvent'. I Love EE so this is how I will call it in this post.

 

This feature was introduce in SQL Server 2008 and its should help collecting DATA about what is running in the Server.

 

More Details about this SQL Server feature can be found in this Link: https://msdn.microsoft.com/library/bb630282.aspx?f=255&MSPPError=-2147217396

 

There are a few differences between EE in SQL Azure and regular SQL Server:

1. In SQL Server versions the EE are on the Server level and therefore you create sessions on Server. In SQL Azure the server is a virtual entity - so the EE is in DB level and you create the session on DataBase.
2. In SQL Server versions the EE can write to files on the server. SQL Azure does not have drives for files (SQL Azure is PAAS.....:-)). There is an option to write to blob storage, for this we need to grant special credentials.
3. In SQL Server versions the EE uses some server level DMVs, as we wrote in point 1, we are in a virtual server entity, we have other new DMV's to use.

A few links to help explain things:

• Extended events in SQL Database High Level
• Event File target code for extended events in SQL Database
• Ring Buffer target code for extended events in SQL Database

Below is an example that creates an event into the Buffer Pool (avoiding writing to blob storage) on DB errors (This is a only sample script...). The scripts create event, create error (ignore the error in the results) and select from the XML in the Buffer Pool that holds the DATA.

 

--Drop Session

IF EXISTS (SELECT * from sys.database_event_sessions WHERE name = 'ErrorWithSQL')
BEGIN
    DROP EVENT SESSION  [ErrorWithSQL]     ON DATABASE;
END

--Create Session
CREATE EVENT SESSION [ErrorWithSQL] ON DATABASE
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.username))
ADD TARGET
        package0.ring_buffer   (SET
                max_memory = 500   -- Units of KB.
            );
GO

--Start Session
ALTER EVENT SESSION [ErrorWithSQL]
    ON DATABASE
    STATE = START;
GO

--create error
select 1/0

--select from EE
declare @xml xml
SELECT @xml= CAST(target_data AS XML)
FROM  sys.dm_xe_database_session_targets AS t ;
--select @xml;

with MyCTE as (
 select tbl.xcol.query('.') TheXML,
 tbl.xcol.value('(@timestamp)[1]','datetime') as ErrorDate,
 tbl.xcol.value('(data[@name="error_number"]/value)[1]', 'int') as ErrorNumber,
 tbl.xcol.value('(data[@name="message"]/value)[1]', 'varchar(300)') as ErrorMessage,
 tbl.xcol.value('(action[@name="sql_text"]/value)[1]', 'varchar(300)') as SQLText,
  tbl.xcol.value('(action[@name="tsql_stack"]/value/frames/frame/@offsetStart)[1]','int') as OffsetStart,
 tbl.xcol.value('(action[@name="tsql_stack"]/value/frames/frame/@offsetEnd)[1]','int') as offsetEnd,
 tbl.xcol.value('(action[@name="tsql_stack"]/value/frames/frame/@handle)[1]','binary(64)') as SQLHandle,
 tbl.xcol.value('(action[@name="client_app_name"]/value)[1]', 'varchar(300)') as client_app_name,
 tbl.xcol.value('(action[@name="client_hostname"]/value)[1]', 'varchar(300)') as client_hostname,
 tbl.xcol.value('(action[@name="username"]/value)[1]', 'varchar(300)') as username
 from @xml.nodes ('/RingBufferTarget/event') tbl (xcol))
select top 100 * from MyCTE
order by ErrorDate desc

 

 

I was very happy to get this feature I think its a game changer.

 

Pini