Creating Extended Events session to capture long running queries – SQL Server 2012

In this article, we’ll create a extended event session that collects event information about T-SQL statements which are running more than 10 seconds.

1. In Object Explorer, expand the Extended events then right-click the Sessions folder and then click New Session wizard. Type a name for your session in the Session name text box.
2. select Do not use a template.
3. select events to capture ( sql_statement_completed, sp_statement_completed)
4. capture global fields (sql_text, tsql_stack)
5. specify a file name to storage captured data and click next and finish.
6. To filter the statements based on duration , right click on the new session -> properties -> events -> click on configure button on right top of the page.
7. select the event and specify the filter values (duration > 10000000) and select ok.
8. Now right click on new session and select start session  then select watch live data to watch the captured sessions.

This slideshow requires JavaScript.