SQL Server Extended Event Setup forView Tracing

This document outlines the step-by-step process to set up SQL Server Extended Events to
trace a specific view’s execution, capture parameter values, monitor performance, and
export the results to an Excel report.

Step 1: Create the Extended Event
Run the following script to create an Extended Event session that logs the execution of the
specified view.
Replace ‘YourViewName’ with the name of the view you want to trace.

SQL Script:

CREATE EVENT SESSION ViewExecutionTrace
ON SERVER
ADD EVENT sqlserver.sql_statement_starting (
ACTION (
sqlserver.sql_text,
sqlserver.database_name,
sqlserver.client_hostname,
sqlserver.username,
sqlserver.server_principal_name,
sqlserver.client_app_name,
sqlserver.query_hash,
sqlserver.query_plan_hash,
sqlserver.session_id,
sqlserver.transaction_id,
sqlserver.context_info
)
WHERE sqlserver.sql_text LIKE ‘%YourViewName%’
)
ADD EVENT sqlserver.query_post_execution_showplan (
ACTION (
sqlserver.sql_text,

sqlserver.database_name,
sqlserver.client_hostname,
sqlserver.username,
sqlserver.server_principal_name,
sqlserver.client_app_name,
sqlserver.query_hash,
sqlserver.query_plan_hash,
sqlserver.session_id,
sqlserver.transaction_id
)
WHERE sqlserver.sql_text LIKE ‘%YourViewName%’
)
ADD TARGET package0.event_file (
SET filename = ‘C:\SQL\Traces\ViewExecutionTrace.xel’,
max_file_size = 5,
max_rollover_files = 5 );

Step 2: Start the Event Session
Execute the following SQL command to start the Extended Event session:

SQL Script:

ALTER EVENT SESSION View Execution Trace ON SERVER STATE = START;

Step 3: Export Data to Excel
Run the provided Python script to extract the data from the event logs and export it to an
Excel file.
The Excel report will contain:

• SQL Text
• Database Name
• Client Hostname
• Username
• Application Name
• Session ID
• Transaction ID
• Duration (ms)
• Execution Time
• Execution Plan
• Context Information
  The script will save the report to: /mnt/data/ViewExecutionTrace_Report.xlsx

Step 4: Stop and Clean Up
To stop the session and drop it if no longer needed, run the following:

SQL Script:

ALTER EVENT SESSION ViewExecutionTrace ON SERVER STATE = STOP;
DROP EVENT SESSION ViewExecutionTrace ON SERVER;

query event

SELECT
event_data.value(‘(event/action[@name=”sql_text”]/value)[1]’, ‘NVARCHAR(MAX)’) AS sql_text,
event_data.value(‘(event/action[@name=”database_name”]/value)[1]’, ‘NVARCHAR(MAX)’) AS database_name,
event_data.value(‘(event/action[@name=”client_hostname”]/value)[1]’, ‘NVARCHAR(MAX)’) AS client_hostname,
event_data.value(‘(event/action[@name=”username”]/value)[1]’, ‘NVARCHAR(MAX)’) AS username,
event_data.value(‘(event/action[@name=”server_principal_name”]/value)[1]’, ‘NVARCHAR(MAX)’) AS server_principal,
event_data.value(‘(event/action[@name=”client_app_name”]/value)[1]’, ‘NVARCHAR(MAX)’) AS application_name,
event_data.value(‘(event/action[@name=”session_id”]/value)[1]’, ‘INT’) AS session_id,
event_data.value(‘(event/action[@name=”transaction_id”]/value)[1]’, ‘BIGINT’) AS transaction_id,
event_data.value(‘(event/data[@name=”duration”]/value)[1]’, ‘BIGINT’) / 1000 AS duration_ms,
event_data.value(‘(event/@timestamp)[1]’, ‘DATETIME’) AS event_time,
event_data.value(‘(event/data[@name=”showplan_xml”]/value)[1]’, ‘XML’) AS execution_plan,
event_data.value(‘(event/action[@name=”context_info”]/value)[1]’, ‘VARBINARY(MAX)’) AS context_info
FROM
sys.fn_xe_file_target_read_file(‘C:\SQL\Traces\ViewExecutionTrace*.xel’, NULL, NULL, NULL);