Trapping Online Index Operations

Recently I wrote an article about Capturing Online Index Operations.  In that article, I discussed a problem that I had encountered.  Well, there were multiple problems.  One was an issue with a vendor app that had some hidden module that was performing online index defrags that was causing corruption in a couple of indexes every night.

The second of the issues was not vendor related but more to do with pain I was experiencing with getting the XEvent session I was creating to work properly.  The problem, as I had concluded, was that it would not work to an asynchronous file target.  As it turns out, I finally got it working.

Today, I just want to share that solution.  Being able to store the results to a file target offers a few benefits (less memory consumed and persisted data) that makes it far more ideal for this problem than to use the ring buffer.

For more background on the full issue and a demonstration on the output of the data from this session, please visit the original post on SQL Solutions Group.

-- Create the Event Session
IF EXISTS(SELECT * 
          FROM sys.server_event_sessions 
          WHERE name='OnlineIXOps')
    DROP EVENT SESSION OnlineIXOps 
    ON SERVER;
GO
CREATE EVENT SESSION OnlineIXOps
ON SERVER
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
/* The online index operations */--ADD EVENT sqlserver.progress_report_online_index_operation
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ADD EVENT sqlserver.progress_report_online_index_operation(
    ACTION (sqlserver.database_name,sqlserver.client_hostname,sqlserver.client_app_name,
            sqlserver.sql_text,
sqlserver.session_id
)
 
--Change this to match the database in question, 
WHERE sqlserver.database_id=5
)
ADD TARGET package0.ring_buffer,
ADD TARGET  package0.asynchronous_file_target(
     SET filename='C:DatabaseXEOnlineIXOps.xel',max_file_size = 5,max_rollover_files = 4)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO
 
-- Start the Event Session
ALTER EVENT SESSION OnlineIXOps 
ON SERVER 
STATE = START;
GO

And for the query to parse the data that is stored, you can use something like this next query.

SELECT
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name
    ,event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS timestamp
,event_data.value('(event/action[@name="database_name"]/value)[1]', 'varchar(max)') as DBName
,event_data.value('(event/data[@name="object_name"]/value)[1]', 'varchar(max)') as ObjName
,event_data.value('(event/data[@name="index_name"]/value)[1]', 'varchar(max)') as index_name
,event_data.value('(event/data[@name="partition_number"]/value)[1]', 'varchar(max)') as PartitionNumber
,event_data.value('(event/action[@name="session_id"]/value)[1]', 'varchar(max)') as SessionID
,event_data.value('(event/data[@name="build_stage"]/value)[1]', 'varchar(max)') as Build_Stage
,event_data.value('(event/data[@name="build_stage"]/text)[1]', 'varchar(max)') as BuildStage_Description
,event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(max)') as Client_hostName
,event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'varchar(max)') as Client_AppName
    ,event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
,event_data.value('(event/data[@name="duration"]/value)[1]', 'Decimal(18,2)')/1000 as Duration_ms
,event_data.value('(event/data[@name="rows_inserted"]/value)[1]', 'varchar(max)') as rows_inserted
FROM(    SELECT TargetData.query('.') AS event_data
        FROM
(SELECT CAST(event_data AS xml) AS TargetData
            FROM sys.fn_xe_file_target_read_file('C:DatabaseXEOnlineIXOps*.xel',NULL,NULL, NULL) 
) as tab
    ) AS evts(event_data)
Order by timestamp, Build_Stage

I hope you find this useful.  But before you proceed, I do have a caution to throw out there.  This session is dependent on the file system directory of C:DatabaseXE to exist.  If it does not, you will get a nasty error message.  Despite the error message, the session will create.  And since the session creates, when you try to run the next query, you will get an additional error because the file does not exist since the path does not exist.

Why does the session still create despite an error you ask?  Well, the answer to that is due to the existence of the ring_buffer target that is also specified.  I left that in there despite not needing it. You can safely remove the ring_buffer target in this XEvent session.  If you remove that ring_buffer target and do not have the directory previously noted, then the session will not create once the error is reached.