In SQLServer, extended events are used to obtain Session-level wait information and Session-level wait information enhancements in SQLServer 2016. sqlserver2016

In SQLServer, extended events are used to obtain Session-level wait information and Session-level wait information enhancements in SQLServer 2016. sqlserver2016

What is waiting

Briefly describe what is waiting:

When an application initiates a Session request to SQL Server, the Session request will apply for the required resources during database execution,

For example, you may apply for memory resources, table lock resources, physical IO resources, and network resources,

If some resources that need to be applied for during the current Session operation cannot be met immediately, a wait occurs.

SQL Server displays the wait information in an unnecessary way, such as the wait information of active sessions and instance-level wait information.

In SQL Server, wait events are one of the important references for DBA to perform TroubleShooting. SQL Server can obtain wait information in multiple ways.

However, for versions earlier than SQL Server 2016, whether waiting at the active Session level or waiting at the instance level, the reference significance is limited,

In more cases, you need to wait for more detailed analysis. This requires collecting the waiting information generated by sessions that have been executed, that is, the historical records of the specific Session wait information.

This article focuses on how to use extended events to capture wait information, but not on the use of extended events. It focuses on how to use extended events to obtain desired wait event information.

You must have a certain understanding of extended events.

Waiting for Information Retrieval

The System View sys. dm_ OS _wait_stats in SQL Server records the wait information generated since the Database Service was started,

For example, this result is instance-level, that is, the accumulation of all wait events on the entire database server.

Most of the time, the reference is not very significant. For example, if the network latency of a day is very high, a large amount of ASYNC_NETWORK_IO wait information is recorded in sys. dm_ OS _wait_stats.

However, when or when the network is changed, the ASYNC_NETWORK_IO wait information recorded in sys. dm_ OS _wait_stats remains unchanged.

That is to say, sys. dm_ OS _wait_stats cannot reflect the real-time waiting condition.

Of course, it is easy to obtain real-time Wait information. Recording the difference of wait time between two time points in sys. dm_ OS _wait_stats can indirectly reflect the wait information of data for a certain period of time.

However, this information is still relatively rough and still instance-level. In some cases, it is still not enough for reference.

The other is to obtain the waiting information of the Active Session through the System View wait_type and wait_time, such as sys. dm_exec_requests.

For example, but this is the information of the Active Session. After the Session is complete, its waiting information will not be able to see how long it has been generated.

That is to say, you cannot trace the types of waits that have taken place during the execution of a Session or SQL statement (Stored Procedure) in history.

To be honest, whether it is sys. dm_ OS _wait_stats or sys. dm_exec_requests, under normal circumstances, the practical value of the obtained waiting information is not high or the applicable scenario is limited.

More often, we want more detailed wait information, such as the waiting information generated during the execution of a Login, a part of sessions, or even some specific SQL statements (stored procedures.

For example, another 10 logins in the database are used to access 10 different applications. Only one application can provide feedback about slow database access or performance problems, all other logins provide normal feedback.

It is very likely that this Login request has its own problem. At this time, we need to conduct targeted analysis on this Login situation, rather than analyzing and diagnosing it at the instance level.

If you can get the waiting status of the Session executed by this Login, or the waiting information in the execution process of some specific database objects of this Login, it is more targeted to locate the problem.

This article describes and demonstrates how to obtain Session-level wait information.

Obtain Session-level wait information in SQL Server 2016

In SQL Server 2016, it is convenient to obtain Session-level wait information. A direct System View sys. dm_exec_session_wait_stats can be used.

In the current situation, it is easy to know the waiting information of a Session,

At the beginning of the SQL statement, record the wait information of the current Session.

Record the wait information of the current Session at the end of the SQL statement.

When calculating the difference between the two waits, you can know what waits are in the current Session running process.

The SQL Server development team may also be aware of the need for more wait information, rather than a general instance-level wait.

Therefore, sys. dm_exec_session_wait_stats is added to SQL Server 2016. This view supports Session-level waiting statistics.

Unfortunately, in versions earlier than SQL server, this system view is not available to easily record Session-level waits.

However, you can use extended events to implement similar functions.

Use extended events to capture Session-level wait Information

Because this is implemented by using extended events, readers are required to have a basic understanding of extended events, and the extended events themselves will not be said much.

Run the code above to start an extended Event to record the wait event information for SQL statements that have been executed for more than three seconds.

Of course, the captured information can be added with various filtering conditions. For more information, see the code remarks.

If exists (SELECT * FROM sys. server_event_sessions WHERE name = 'collectionsessionwaitstats') drop event session CollectionSessionWaitStats on server; GO -- create event session CollectionSessionWaitStats on server add event sqlserver. rpc_completed (ACTION (sqlos. task_time, sqlserver. database_name, sqlserver. nt_username, sqlserver. username, sqlserver. SQL _text, sqlserver. session_id, sqlserver. transaction_id) WHERE [duration] >=3000000), add event sqlserver. SQL _batch_completed (ACTION (sqlos. task_time, sqlserver. database_name, sqlserver. nt_username, sqlserver. username, sqlserver. SQL _text, sqlserver. session_id, sqlserver. transaction_id) WHERE [duration] >=3000000), add event sqlos. wait_info (ACTION (sqlos. task_time, sqlserver. database_name, sqlserver. nt_username, sqlserver. SQL _text, sqlserver. username, sqlserver. session_id, sqlserver. transaction_id) WHERE session_id> 50 and opcode = 1 and duration> 0 and SQL _text not like '% sp_MScdc_capture_job %' -- exclude some information -- and username = ''-- only some information is recorded ), add event sqlos. wait_info_external (ACTION (sqlos. task_time, sqlserver. database_name, sqlserver. nt_username, sqlserver. username, sqlserver. SQL _text, sqlserver. session_id, sqlserver. transaction_id) WHERE session_id> 50 and opcode = 1 and duration> 0 and SQL _text not like '% sp_MScdc_capture_job %' -- and username = '') add target package0.event _ file (SET filename = n'd: \ XEventFiles \ CollectionSessionWaitStats ', max_file_size = (1024), max_rolover_files = (10) WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = pause, timeout = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON) GO -- enable (STOP) Event session (START/STOP) alter event session CollectionSessionWaitStats on server state = START GO

Session-level wait information collected by extended events has the following characteristics:

1. SessionId can be repeated.

For example, for a Session with SessionId = 80, You can execute SQLA for the first time and SQLB for the second time.

At the same time, the execution of these two SQL statements and the wait information generated during execution can be captured.

2. One SessionId cannot be executed simultaneously within the same time period,

For example, from 16:46:36 to 16:46:46, the Session with SessionId = 80 is being executed, and the extended Event captures the generated wait information.

However, during the period from 16:46:36 to 16:46:46, it is not difficult to understand this logic that another SessionId = 80 is also being executed.

Why do you want to explain this problem?

There is no direct correspondence between the SQL statement completion event (rpc_completed or SQL _batch_completed) in the event information collected by the extended Event and the generated wait.

How can this problem be solved? The problem can be discovered only when you start to work. I am here for a long time, for example.

As follows, the sessionids of the two target events captured by the extended event are both 58, but the executed SQL statements are different,

The following shows all the event information captured without distinguishing the event type.

As mentioned above, for Session 58:

The first timeSELECT COUNT(1) FROM TestCollectionSessionWaitStats

The second operationSELECT COUNT(1) FROM TestCollectionSessionWaitStats WHERE Id>10

The consistency or inconsistency between the two running SQL statements is not a problem. The key is that the SessionId of the captured wait event confidence is also 58. How can we tell which operation the generated event belongs?

For example, if Session 58 runs two SQL statements and generates 10 waiting messages, how can we tell which of the 10 waiting messages are generated during the first run and which are generated during the second run?

This relies on the second point mentioned above, "the same SessionId cannot be executed simultaneously in the same time period"

For the same SessionId, In the SQL _batch_completed event, from the perspective of time

The time that is earlier than the completion time of the first SQL _batch_completed event must be the first time that Session 58 is executed and generated.

More than the first SQL _batch_completed event completion event and less than the second SQL _batch_completed event must be generated by the second execution of Session 58

With this theoretical basis, it is easier to count the waiting time at the Session level. I believe this logic is not difficult to implement.

If the wait details generated by executing an SQL statement in Session 58 are displayed, the CXPACKET and PAGEIOLATCH_SH waits for multiple times.

In view of the problem, there is another wait information captured by another SQL statement, which is also the execution of Session 58.

This statistical method is mentioned above. In the two SQL _batch_completed events, although the SessionId of the event is the same,

However, it occurs before SQL _batch_completed of the current event. After SQL _batch_completed,

In this way, the SQL _batch_completed event and its corresponding wait_info event can be perfectly matched.

In this case, it is not difficult to obtain the result similar to sys. dm_ OS _wait_stats in SQL Server 2016.

Compared with the result of sys. dm_ OS _wait_stats wait information, is the wait information obtained through the extended event more detailed and specific?

For example, for the CXPACKET wait time,

It is not difficult to find that if the count is calculated, it is similar to waiting_tasks_count in sys. dm_ OS _wait_stats, and the total time (sum) is calculated, it is similar to wait_time_ms.

However, the above time information has been refined to the Session level, which is more useful for reference than the wait information in sys. dm_ OS _wait_stats.
The problem diagnosis and analysis will also be more effective.

SQL statement of the preceding statistics

-- Parse the XML to show rpc_completed,sql_batch_completed detailsif object_id('tempdb..#t1') is not null  drop table #t1SELECT   event_xml.value('(./action[@name="session_id"]/value)[1]', 'INT') as session_id,  event_xml.value('(./@timestamp)', 'varchar(1000)') as timestamp,  event_xml.value('(./data[@name="statement"]/value)[1]', 'varchar(max)') as statement,  event_xml.value('(./data[@name="batch_text"]/value)[1]', 'varchar(max)') as batch_text,  event_xml.value('(./@name)', 'varchar(1000)') as Event_Name,  event_xml.value('(./data[@name="duration"]/value)[1]', 'bigint') as Duration,  event_xml.value('(./data[@name="cpu_time"]/value)[1]', 'bigint') as cpu_time,  event_xml.value('(./data[@name="physical_reads"]/value)[1]', 'bigint') as physical_reads,  event_xml.value('(./data[@name="logical_reads"]/value)[1]', 'bigint') as logical_reads,  event_xml.value('(./action[@name="username"]/value)[1]', 'varchar(max)') as usernameINTO #t1FROM (    SELECT CAST(event_data AS XML) xml_event_data     FROM sys.fn_xe_file_target_read_file(N'D:\XEventFiles\CollectionSessionWaitStats*', NULL, NULL, NULL)   ) AS event_tableCROSS APPLY xml_event_data.nodes('//event') n (event_xml)WHERE event_xml.value('(./@name)', 'varchar(1000)') in ('rpc_completed','sql_batch_completed')order by Event_Name-- Parse the XML to show wait_info,wait_info_external detailsif object_id('tempdb..#t2') is not null  drop table #t2SELECT   cast(event_xml.value('(./@timestamp)', 'varchar(1000)') as DATETIME2) as timestamp,  event_xml.value('(./data[@name="duration"]/value)[1]', 'bigint') as duration,  event_xml.value('(./action[@name="session_id"]/value)[1]', 'INT') as session_id,  event_xml.value('(./data[@name="wait_type"]/text)[1]', 'VARCHAR(200)') as wait_typeINTO #t2FROM (    SELECT CAST(event_data AS XML) xml_event_data     FROM sys.fn_xe_file_target_read_file(N'D:\XEventFiles\CollectionSessionWaitStats*', NULL, NULL, NULL)   ) AS event_table   CROSS APPLY xml_event_data.nodes('//event') n (event_xml)WHERE event_xml.value('(./@name)', 'varchar(1000)') in ('wait_info','wait_info_external')if object_id('tempdb..#t3') is not null  drop table #t3SELECT   a.session_id          AS SessionId,  isnull(statement,batch_text)  AS SQLTEXT,  a.Duration            AS TotalExecuteTime,  CAST(a.timestamp AS DATETIME2)  AS CompletedTime,  CAST(b.timestamp AS DATETIME2)  AS WaitTypeStartTime,  b.wait_type            AS WaitType,  b.duration            AS WaitDurationINTO #t3FROM #t1 a INNER JOIN #t2 b on a.session_id = b.session_id  and b.timestamp < a.timestamp   and b.timestamp>(             select top 1 timestamp from #t1 c              where a.session_id = a.session_id and a.timestamp > b.timestamp             order by a.timestamp          )select   case when rn = 1 then SessionId else NULL end as SessionId,  case when rn = 1 then SQLTEXT else NULL end as SQLTEXT,  case when rn = 1 then TotalExecuteTime else NULL end as TotalExecuteTime,  CompletedTime as CompletedTime,  WaitType,  WaitTypeStartTime as WaitTypeStartTime,  WaitDurationfrom (  select ROW_NUMBER()over(partition by SessionId,SQLTEXT,TotalExecuteTime,CompletedTime order by CompletedTime,WaitTypeStartTime) as rn,  * FROM #t3)

Of course, this statement is for reference only. It aims to collect Session-level statistics. When Session-level statistics are collected, the specific statistical method is not difficult.

Summary

Wait events can help us diagnose some resource bottlenecks on SQL Server. They are of great reference significance for troubleshooting and solving problems. If we can collect wait events in detail, it is of greater significance for solving the problem.

This article uses a simple example to collect wait information in some specific scenarios of SQL Server by using extended events to diagnose and identify problems in a more targeted manner, this makes the problem analysis more efficient and targeted.

The above section describes how to use extended events in SQLServer to obtain Session-level wait information and enhance Session-level wait information in SQLServer 2016. I hope this will help you, if you have any questions, please leave a message and the editor will reply to you in time. Thank you very much for your support for the help House website!