Extended events have been around since SQL Server 2008 version. This was created by Microsoft as a light weight way of tracking and monitoring SQL Server. As DBA, I was traditionally used to using SQL profiler and tracing for performance troubleshooting over the years and correlate the data with other monitoring tools data to find the underlying issues.
With Extended Events we can perform such tasks with ease as it is not only a replacement to SQL Trace but has lot more features included in it which makes troubleshooting easier.
With SQL Server 2016 the Graphical interface adds more to what it can do and making it easier to monitor the live session data as well.
I will demonstrate what data you can find from the default extended events enabled in SQL Server 2016.
>>Open SQL Server Management Studio 2016 and connect to an instance
>>On the left hand side Expand Management Folder and Expand Extended Events
>>Under Sessions you should find a default session named system_health as below
>>Right click on the system_health and click on “Watch Live Data” which would open the live session data being captured on right-hand side as below.
>>By default there are 2 columns displayed, one which shows the event name and other columns shows the time.
>> when you select one of the event it shows the information in the bottom window. But if you want to view that information as column, you can right click on the top of first column name and select add cloumns.
>> If you are looking for any specific event occurrence then we can group by events displayed in first column and look for the particular event.
For Example, I am looking for any connection errors events logged because our application team says that there are database related errors while connecting. Hence here is what I would do.
- First right click on the column Name and click on Group by this Column option as below
- Now you would see the screen as below
- Now I will click on the ‘+’ sign next to “connectivity_ring_buffer_recorded” and navigate to the time when the user reported connection errors.
- Selecting the appropriate event, a detailed information is listed below as in the screenshot.
In my case tds_flags in details says “PhysicalConnectionKilled…..”. This type of message in extended events most of the times means a network connection error where packets are dropped on network and would typically end up logging application time-outs in app error logs.
More on Extended Events in my future posts…