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.
Create an Extended Events Session to track the wait resources the queries running in a particular session undergo:
1. Launch SSMS to navigate through the all new Extended Events user interface under Management folder. Right click on the New Session… under Sessions folder. A picture is worth thousand words so here is the pictorial tutorial.![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
Why these two wait events?
Well, let’s first look at the description we have in sys.dm_xe_objects about these two waits events. You may run the query below to get this: [sourcecode language="sql"] select name, object_type, description from sys.dm_xe_objects WHERE name LIKE 'wait_info%' [/sourcecode]![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
Bit more on wait_info:
As it says, wait_info is a wait on a SQLOS controlled resource. Essentially we may use this event to troubleshoot any type of wait on such resources. This includes a comprehensive list of waits from sys.dm_os_wait_stats. Note that this wait event gives information about the state of a worker thread when the particular wait has completed. The most common predicate expression to use for wait_info is wait_type.![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
Ok, so what is wait_info_external now?
During the execution, a worker may have to switch to the pre-emptive mode to call a windows API. Wait_info_external corresponds to such kind of pre-emptive waits. To see a list of all such pre-emptive waits you may run a query like this: [sourcecode language="sql"] select * from sys.dm_xe_map_values where name = 'wait_types' and map_value like '%PREEMPTIVE%' [/sourcecode] 4. Now let’s select these two events:![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
Tip:
You may apply filters on the applicable Events Field for the selected event category viz. a filter on wait_type or on duration column e.g. wait_type =187 means CXPACKET waits. Jonathan Kehayias has written such a query on his blog (as below) to see the list: [sourcecode language="sql"] SELECT map_key, map_value FROM sys.dm_xe_map_values WHERE name = 'wait_types' AND ((map_key > 0 AND map_key < 22) -- LCK_ waits OR (map_key > 31 AND map_key < 38) -- LATCH_ waits OR (map_key > 47 AND map_key < 54) -- PAGELATCH_ waits OR (map_key > 63 AND map_key < 70) -- PAGEIOLATCH_ waits OR (map_key > 96 AND map_key < 100) -- IO (Disk/Network) waits OR (map_key = 107) -- RESOURCE_SEMAPHORE waits OR (map_key = 113) -- SOS_WORKER waits OR (map_key = 120) -- SOS_SCHEDULER_YIELD waits OR (map_key = 178) -- WRITELOG waits OR (map_key > 174 AND map_key < 177) -- FCB_REPLICA_ waits OR (map_key = 186) -- CMEMTHREAD waits OR (map_key = 187) -- CXPACKET waits OR (map_key = 207) -- TRACEWRITE waits OR (map_key = 269) -- RESOURCE_SEMAPHORE_MUTEX waits OR (map_key = 283) -- RESOURCE_SEMAPHORE_QUERY_COMPILE waits OR (map_key = 284) -- RESOURCE_SEMAPHORE_SMALL_QUERY waits ) [/sourcecode] If you want to track all the waits which were more than 5 secs you may apply the filter duration>5000 (in milliseconds) 8. Now click on the Data Storage page. Select the type Event File![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
Still not it… Time for the reverse engineering.
Well if you prefer to do the things from User Interface you may stop reading this post now ? But if you happen to like T-SQL and can make yourself familiar with packages, events etc. of extended events here is a quick way to do this using T-SQL. Just script out the Extended Events session we created:![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
Share this
Next story
Hashing Algorithm in MySQL PASSWORD() →
You May Also Like
These Related Stories
Mining the AWR to Identify Performance Trends
Mining the AWR to Identify Performance Trends
Oct 31, 2013
8
min read
SQL Server In-Place Upgrade Failed: Wait on the Database Engine Recovery Handle Failed
![](https://www.pythian.com/hubfs/Imported_Blog_Media/Upgrade-Failed-1.png)
SQL Server In-Place Upgrade Failed: Wait on the Database Engine Recovery Handle Failed
Jun 29, 2022
4
min read
Connection timeout parameters in MySQL
Connection timeout parameters in MySQL
Mar 17, 2016
6
min read
No Comments Yet
Let us know what you think