SQL Server 2012 Denali: Discussion On Usage Scenario for “Extended Events” (Part-1)
SQL Server 2008 introduces Extended Events - A light weight performance monitoring system that uses very few performance resources. Read more about
Extended Events if you have not explored it yet. With the release of SQL Server 2012 (aka "Denali") CTP3 it gets even better. Now, it has two graphical user interfaces (the New Session Wizard or New Session UI) to easily and quickly create, modify, display, and analyse your session data. Not only that, it now includes an
Extended Events Reader to View the Target Data . These new additions make use of Extended Events very easy as you will see later in this post. As Microsoft has announced
deprecation of SQL Profiler Trace to be
replaced with Extended Events in the next major release, I thought this is the time to have a good hands-on on Extended Events. Especially because I deal with SQL Profiler frequently to troubleshoot SQL Server Performance and other issues. Be advised, Extended Events is not limited to troubleshoot only the SQL Server performance issues. Check the section
Scenarios for Using Extended Events to get a glimpse of some scenarios where Extended Events can be used. In this blog,
I am going to demonstrate how we can use the wait event category
sqlos.wait_info*
to track the wait resources the queries running in a particular session undergo. I know we can do this in prior versions as well. However, It becomes challenging if we have to find the wait types, duration etc. about a query or multiple queries running in a particular session when it completes quickly e.g. less than 1 sec or 500 ms. In such case diagnostics using Extended Events is quite useful. This is why this blog. P.S: All the demo is based on SQL Server 2012 CTP3 (aka Denali) which is subject to change in the RTM release.