How to detect if system health extended event session has incorrect event specification (T-SQL)

Introduction

This T-SQL script will demo how to detect if system health extended event session has incorrect event specification in SQL Server 2012.

Scenarios

Every instance of SQL Server has a default extended event session created that has the name system_health. This session captures information about the wait_info and wait_info_external events. The definition of the system_health session in SQL Server 2012 contains incorrect map_key specified for the wait_types of interest. The script will help check system health extended event session incorrect event specification in SQL Server 2012.

Script

You can use this script in this way:
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.
2. Copy the code from CheckIncorrectEventSpecification.sql, paste it in a new query and run the script.

After the script runs, we’ll get the following figure:
Here are some code snippet for your reference:

SQL

Edit|Remove

SELECT sese.predicate
FROM sys.server_event_session_events sese 
JOIN sys.server_event_sessions ses ON ses.event_session_id = sese.event_session_id

SELECT sese.predicate 
FROM sys.server_event_session_events sese  
JOIN sys.server_event_sessions ses ON ses.event_session_id = sese.event_session_id

Prerequisites

SQL Server 2012