Learning the Tricks of the Trade - Microsoft SQL Server 2008 R2 Administration - page 418

5. Now, let us run two queries that run for more than five seconds to capture the
extended events:
SELECT *
FROM AdventureWorks2008R2.Sales.SalesOrderDetail
ORDER BY UnitPriceDiscount DESC
GO
SELECT *
FROM AdventureWorks.Sales.SalesOrderDetail
ORDER BY UnitPriceDiscount DESC
GO

6. At this point, the event session needs to be altered to capture the sp_statement_

completed value for long running query execution:
alter event session LongRunningQueryExecution_ETW
ON SERVER ADD TARGET package0.synchronous_bucketizer
(SET filtering_event_name = 'sqlserver.sp_statement_completed',
source_type=1, source='sqlserver.sql_text')

7. Now, let us start the EVENT SESSION :

ALTER EVENT SESSION LongRunningQueryExecution_ETW ON SERVER
STATE=STOP;

8. As the query generates the events, at this point STOP the system ETW collection and

Extended Events external ETW session from the command prompt that was opened
in step 3.
logman update "NT Kernel Logger" -fd -ets
logman stop "NT Kernel Logger" -ets
logman update XE_DEFAULT_ETW_SESSION -fd -ets
logman stop XE_DEFAULT_ETW_SESSION -ets

9. Finally, let us combine the two .etl files using TRACERPT utility into a single .csv

file on the command prompt window.
tracerpt D:\sqletwtarget.etl D:\ENTZ\FS\ETW\SQLETW_
LongRunningQueries.etl -y -o SQLETW_LRQ.CSV

10. The newly created .CSV file can be opened using Microsoft Excel to obtain the
collected trace events.

This completes the steps to go through the methods on how to manage events capture using
Extended Events with ETW and TRACERPT tools.