There have been a spate of new features and enhancements to existing functionality in SQL Server 2012. The System Health Extended Events session is one of the benefactors. I had previously blogged about the usage of the System Health Session data using custom SSMS Reports in a four part series. Now it is time to talk about the enhancements to the System Health Session’s predecessor.

The SQL Server 2012 health session adds additional events to the default extended event health session running on the instance which is listed below to collect data for the following:

• Out of memory errors for CLR and the database engine using the events:
• sqlclr.clr_allocation_failure
• sqlclr.clr_virtual_alloc_failure
• sqlos.memory_broker_ring_buffer_recorded
• sqlos.memory_node_oom_ring_buffer_recorded
• Scheduler health issues using the events:
• sqlos.scheduler_monitor_deadlock_ring_buffer_recorded
• sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded
• sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded
• sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded
• sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded
• sqlos.scheduler_monitor_system_health_ring_buffer_recorded
• The results of the sp_server_diagnostics component
• Security and connectivity errors encountered using the events:
• sqlserver.connectivity_ring_buffer_recorded
• sqlserver.security_error_ring_buffer_recorded

Additional wait types are also tracked which adds to the existing list of waits captured by this session. Since SQL Server 2012 Management Studio allows you to view the configured Extended Events session on the SQL Server 2012 instance, you can get to the system_health session using the following steps from Object Explorer (see Screenshot 1):

Expand Management –> Extended Events –> system_health.

The Script Session as option now provides you with a way to view the session definition. Another enhancement made to this session is the target for this session is now also a .xel file which by default is located in your SQL Server LOG folder. The target definition for the System Health session are now:

a. Ring Buffer (as was in SQL Server 2008) 
b. XEL file – With a maximum size of 5 MB and a file retention policy of 4 files.

So now you can leverage the management studio XEvent UI to view the results. You can right-click on the ring buffer target in the UI and click on the View Target Data option which will give you an XML document.

The coolest thing that I like here is the viewer’s capability to show you all the data from all the .xel files. Once you have the events loaded, you should see a view as shown in Screenshot 2. This gives you a list of all the events that were generated. You can sort on the event columns by clicking on them. The progress of the sorting will be visible towards the top right hand corner of SSMS as shown in Screenshot 3. Now that you have the basic view, let’s find out what more can we do with the System Health Session data displayed.

You can click on the Choose Columns option in the toolbar, select the columns that you want to view additionally apart from the name and timestamp by using the Available Columns list. See Screenshot 4 for an example.

Now that you have selected the additional columns, you can also set grouping (using the Grouping button) to view each of the event name categories, the number of events reported and aggregations on the column values (using the Aggregations button). So finally, I landed up with a view as shown below in Screenshot 5. What’s more, you can save the view settings using the Display Settings button!

I am currently writing a series on SSWUG for designing SSRS reports for viewing the events reported by the System Health Session. You can following the series here. If you have SQL Server 2012 RTM installed, then you need to download the T-SQL script attached to Mike Wachal’s blog post to address the wait type information captured.

Monitoring just got a whole lot easier!!