Using XEvents (Extended Events) in SQL Server 2008 to detect which queries are causing Page Splits
From http://msdn.microsoft.com/en-us/library/bb630354.aspx:
SQL Server Extended Events (Extended Events) is a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) in order to correlate the event data with operating system or application event data.
SQL Server Extended Events Packages
http://msdn.microsoft.com/en-us/library/bb677278.aspx
A package is a container for SQL Server Extended Events objects. Objects from different packages can be mixed in an event session. For more information, see SQL Server Extended Events Sessions.
A package can contain any or all of the following objects, which are discussed in greater detail later in this topic:
- Events
- Targets
- Actions
- Types
- Predicates
- Maps
Packages are identified by a name, a GUID, and the binary module that contains the package.
1) we need to know the event name that fires for every page split:
SELECT xp.[name], xo.*
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE xp.[guid] = xo.[package_guid]
AND xo.[object_type] = 'event'
ORDER BY xp.[name];
for this sample, we will use sqlserver.page_split.
2) we need to know what columns “exposes” the event page_split:
select * from
sys.dm_xe_object_columns
where [object_name] = 'page_split'
file_id, and page_id is exposed on this event, but we still need more information such us database_id, or SQL query that causes the page split.
3) find out what additional columns we can add to the event:
SELECT xp.[name], xo.*
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE xp.[guid] = xo.[package_guid]
AND xo.[object_type] = 'action'
ORDER BY xp.[name], xo.[name];
You can see in the list that actions number 25, and 34 includes database_id, and sql_text. Please review the list because you may need include that application name, the username, or the plan_handle.
4) Destination (target) of the event:
SELECT xp.[name], xo.*
FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp
WHERE xp.[guid] = xo.[package_guid]
AND xo.[object_type] = 'target'
ORDER BY xp.[name], xo.[name];
for better performance we select an asynchronous target, such us file system (3).
5) Now we’re ready to create the Extended Event with the following code:
create event session xe_page_split on server
add event sqlserver.page_split
(action (sqlserver.database_id, sqlserver.sql_text)
where sqlserver.database_id > 4)
add target package0.asynchronous_file_target
(set filename=N'c:\temp\xe_page_split.xel',
metadatafile=N'c:\temp\xe_page_split.xem');
Explanation:
- The event name is sqlserver.event
- As action we add sqlserver.database_id, and sqlserver.sqltext; in this case the action is just adding new columns to the event (remember that the event exposed the file_id, and the page_id columns).
- Additionally we can add filters to the event: for this example, we’ll capture page splits only for user databases.
- The event destination will be a file: c:\temp\xe_page_split.xel.
6) start the Extended Event:
alter event session xe_page_split on server state = start;
7) let’s populate a table:
use Northwind
go
if not object_id ('dbo.t') is null
drop table dbo.t
go
create table dbo.t
(id int identity, v char(100) default 'a', constraint pk_t primary key (id))
go
insert dbo.t (v) select top 100000 'a'
from master.dbo.spt_values v cross join master.dbo.spt_values
8) wait a few seconds, because 1) the event is asynchronous, and 2) we haven’t specified the argument MAX_DISPATCH_LATENCY for the event (default value INFINITE).
9) find out if we have any row:
select COUNT(*)
from sys.fn_xe_file_target_read_file
('c:\temp\xe_page_split*.xel', 'c:\temp\xe_page_split*.xem', null, null)
10) Some XPath querying:
select
xml_data
, xml_data.value('(/event[@name=''page_split'']/@timestamp)[1]','datetime') time
, xml_data.value('(/event/data[@name=''file_id'']/value)[1]','int') file_id
, xml_data.value('(/event/data[@name=''page_id'']/value)[1]','int') page_id
, xml_data.value('(/event/action[@name=''database_id'']/value)[1]','int') database_id
, xml_data.value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)') sql_text
from
(select object_name as event, CONVERT(xml, event_data) as xml_data
from sys.fn_xe_file_target_read_file
('c:\temp\xe_page_split*.xel', 'c:\temp\xe_page_split*.xem', null, null)
) v order by time
that basically shows:
- when the event was fired.
- what file and page was involved.
- what database was affected.
- the query that forced the page split.
Note: Before running this event session in Production, please test it carefully and validate the performance penalties can cause.
Next Steps:
- As far as I know the object can not be identified but reading the TSQL sentence will be a good start.
- Page split is fired for every single page spitted: splits at the first and last page of the object is counted as well. You will have wisely to identify in what part of the object is happening: for example, you could use DBCC PAGE to find out that correlation.
Additional readings: