SQL Server Extended Events (Extended events)-Extended events session with System_health

SQL Server Extended events (Extended events)-Extended events session with System_health

The System_health session is an extended events session that SQL Server contains by default. The session starts automatically when the SQL Server database engine starts, and the runtime does not have any noticeable performance impact. The session collects system data that can be used to help troubleshoot performance issues with the database engine. Therefore, we recommend that you do not stop or delete the session.

This session originates from the idea of the product support team, which tracks information that is typically used to debug a customer system, such as when a customer system is deadlocked or other critical errors occur. This session is created and started as part of the SQL Server instance installation process, which tracks events in the ring buffer and therefore does not consume too much memory.

The information collected by this session includes:

L Sql_text and session_id of any sessions where the severity >=20 errors occur.

L Sql_text and session_id of any sessions that occur with memory-related errors. These errors include 17803, 701, 802, 8645, 8651, 8657, and 8902.

L Any record of scheduling problems that cannot be completed. (These issues appear in the SQL Server error log as Error 17883.) ）

L any deadlock detected.

L CallStack, Sql_text, and session_id for any session that waits for a latch (or other related resource) for the time > 15 seconds.

L Wait for lock time > 30 seconds for any session of CallStack, Sql_text and session_id.

L CallStack, Sql_text, and session_id for any sessions that have been waiting for a long time to get preemptive wait. The duration varies depending on the wait type. In the preemptive wait, SQL Server waits for an external API call.

The CLR allocates failed and virtual allocations for failed call stacks and session_id.

• Ring_buffer events for memory Broker, scheduler Monitoring, Memory node OOM, security, and connections.

The results of the system components in the L sp_server_diagnostics.

L SCHEDULER_MONITOR_SYSTEM_HEALTH_RING_BUFFER_RECORDED The collected instance health.

L CLR allocation failed.

L connection error when using connectivity_ring_buffer_recorded.

L Security error when using security_error_ring_buffer_recorded.

Viewing session definitions

The following shows the definition of the default event session System_health:

SELECT Name,event_retention_mode_desc as Event_retention_mode,max_dispatch_latency,max_memory,max_event_size, Memory_partition_mode_desc as Memory_partition_mode,track_causality,startup_statefrom Sys.server_event_ Sessionswhere name = ' System_health ';

The following shows the events, predicates, and actions:

Select Package, E.name, predicate, (select Package + '. ' + name + ', ' from Sys.server_event_session_actions awhere a.event_ session_id = E.event_session_idand a.event_id = E.event_idorder by package, namefor XML PATH (")) as Actionsfrom Sys.serve R_event_session_events einner JOIN sys.server_event_sessions es on e.event_session_id = Es.event_session_idwhere Es.name = ' system_health ';

The following shows the target and its options:

Select Package, T.name, (select name + ' = ' + "cast (value as varchar) + ', ' from Sys.server_event_session_fields fwhere f.eve nt_session_id = T.event_session_idand f.object_id = t.target_idfor XML PATH (")) as Optionsfrom sys.server_event_session _targets tinner JOIN sys.server_event_sessions es on t.event_session_id = es.event_session_idwhere Es.name = ' System_heal Th ';

Viewing session data

When an event session is created and run, data collection is used for subsequent use in the target. In addition to the file target (event_file), from the Sys.dm_xe_session_targets view you can query to all the target data and need to read the data through the sys.fn_xe_file_target_read_file () table-valued function. The Sys.dm_xe_session_targets conversion Target_data is listed in XML format.

You can use the following code to view what is contained in the ring buffer:

SELECT name, Target_name, CAST (target_data as XML) target_datafrom sys.dm_xe_sessions Sinner JOIN Sys.dm_xe_session_ Targets TON s.address = t.event_session_addresswhere s.name = ' system_health '; GO

To view session data in the event file, use the Extended Events user interface provided in Management Studio.

Restore a system_health session

If you delete a system_health session, you can restore the session by executing the U_tables.sql file in the Query Editor. The file is located in the following folder, where C: Represents the drive where you installed the SQL Server Program files:

C:\Program Files\Microsoft SQL Server\mssql11.<instanceid>\mssql\install

Note that after you restore the session, you must start the session by using the ALTER event session statement or by using the Extended Events node in Object Explorer. Otherwise, the session will start automatically the next time you restart the SQL Server service.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1599384

SQL Server Extended Events (Extended events)-Extended events session with System_health