Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 312

OR [error]=(8651)
OR [error]=(8657)
OR [error]=(8902))) ),
ADD EVENT sqlos.scheduler_monitor_non_yielding_ring_buffer_
recorded
add target package0.asynchronous_file_target
(SET filename='c:\temp\page_split.xel', metadatafile='c:\temp\
page_split.xem')
If the SQL Server service account is not a member of the sysadmin group, then it must be a
member of the group's Performance Monitor Users and Performance Log Users on the server.
Let us start the LongRunningQueryExecution event session:
-- START EVENT SESSION
ALTER EVENT SESSION LongRunningQueryExecution ON SERVER
STATE=start;
Now, let us run two queries that runs for more than five seconds to capture in extended
events:
SELECT *
FROM AdventureWorks2008R2.Sales.SalesOrderDetail
ORDER BY UnitPriceDiscount DESC
GO
SELECT *
FROM AdventureWorks.Sales.SalesOrderDetail
ORDER BY UnitPriceDiscount DESC
GO
At this point, the event session needs to be altered to capture a sp_statement_
completed value for a long-running query execution:
alter event session LongRunningQueryExecution
ON SERVER ADD TARGET package0.synchronous_bucketizer
(SET filtering_event_name = 'sqlserver.sp_statement_completed',
source_type=1, source='sqlserver.sql_text')
Capture the XML events to a view from the saved results:
CREATE VIEW dbo.Toread_xe_file as
select object_name as event, CONVERT(xml, event_data) as data
from sys.fn_xe_file_target_read_file('c:\temp\page_split*.xel',
'c:\temp\page_split*.xem', null, null)
go
Finally, parse the results from the RAW XML file to another table:
create view dbo.xe_Tofile_table as
select event