First published on MSDN on Mar 22, 2012
I have seen several traces and questions relating to the output of the XEvent so I thought I would try to explain them a bit in an effort to reduce confusion.
Terms
Field == Event Data
Action == Action Data - The action data is gathered from the
current session/request
state.
Let's look at the page_split event as that is one of the most relevant events I have seen questions on.
-
The page_split event contains fields (page_id and file_id) and there is an action database_id.
Taking a simple insert into a table in pubs you might see the following.
insert into authors ….
action:database_id = 6
page_id = 1045
file_id = 1
This indicates the location of the page split in the pubs database.
Now take the same insert but the sessions current context is the master database (dbid=1)
insert into pubs..authors ….
action:database_id = 1
page_id = 1045
file_id = 1
The action data is correct based on its design to show the current state of the session. The session is in database_id = 1 (master) but the split really occurs in database_id = 6. What is missing from this example is a field in the page_split event so you can see the context of the page split and the session. The split context would be in an event
field:database_id
and the session context in the
action:database_id
.
The user is logged into the pubs database (dbid = 6) and executing a query that involves tempdb.
Here is an sample showing the table spool with tempdb involvement while doing a select. (
Note most don't expect possible page split events on a select but you must consider spools, spills and sort activity.
)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1014]=[Expr1013], [Expr1036]=[Expr1035], [Expr1058]=[Expr1057], [Expr1080]=[Expr1079], [Expr1102]=[Expr1101], [Expr1124]=[Expr1123]))
|--Sort(ORDER BY:([Expr1013] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:(FN_XE_READ_FILE_TARGET_FILE.[event_data]))
|--Nested Loops(Inner Join, OUTER REFERENCES:(FN_XE_READ_FILE_TARGET_FILE.[event_data]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:(FN_XE_READ_FILE_TARGET_FILE.[event_data]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:(FN_XE_READ_FILE_TARGET_FILE.[event_data]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:(FN_XE_READ_FILE_TARGET_FILE.[event_data]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:(FN_XE_READ_FILE_TARGET_FILE.[event_data]))
| | | | | |--Sort(ORDER BY:(FN_XE_READ_FILE_TARGET_FILE.[event_data] ASC))
| | | | | | |--Table-valued function
| | | | | |--Table Spool
The page_split in this case report the action:database_id = 6.
The existing_connection event illustrates the field and action context as well. The existing connection is output by the session enabling the session. (ALTER SESSION … START). So the action data associated with existing connection events would be that of the session starting the trace and not that of the session associated with the existing connection event.
Bob Dorr - Principal SQL Server Escalation Engineer
