Wait Events drilldown | Spotlight Enterprise Documentation

The Wait Events drilldown assesses sampled workload waits, that is waits that occur as a result of running SQL statements (via an XEvents trace). The Wait Events drilldown is designed to assist in identifying SQL Statements involved with specific waits to the extent that this information is actually available.

How to open the Wait Events drilldown

From the Spotlight Client

Select the connection from the left Connections pane.
Click Monitor | Wait Events from the ribbon.

Tip: Click for more information on using Spotlight Charts and Spotlight Grids.

About the Wait Events drilldown

Category and Workload

The category and workload selected qualify the information shown on the grids and charts on this page.

Workload

Select a workload to investigate for the selected category of wait.

The workload items are dependent on the Category selected.

Workload	Description
Database	Which database is contributing most to the wait time?
Application	Which application is contributing most to the wait time?
User	Which user is contributing most to the wait time?
Host	Which host is contributing most to the wait time?
Wait Type	Which wait type is contributing most to the wait time?
Statement	Which SQL statement is contributing most to the wait time?

Possible actions you can take once the category and workload are selected:

Click on the SQL statement to show the Overview, SQL and Plan tabs.
To open the SQL statement in an external editor, right click on it.
To show the SQL statement in the Amazon RDS for SQL Server | Workload Analysis drilldown right click on it. Workload Analysis will provide execution details of the SQL statement like how many times the query ran.
Export data from statement nodes to an .csv file for further analysis. Select the SQL statement then click . Data includes: WAIT TYPE, WAIT TIME, % OF TOTAL WAIT, % OF SAMPLE TIME, CATEGORY.

Grids and Charts

Possible actions you can take on the grids and charts:

When a Workload Statement is selected, click View Workload Analysis to show the statement in the Amazon RDS for SQL Server | Workload Analysis drilldown. Workload Analysis will provide execution details of the SQL statement like how many times the query ran.
Use Export all the statements to save all SQL statements to a file (default filename SQLStatementList.sql). If the exporting tree list is large, Spotlight will save to multiple files; each output file no greater than 10Mb.

Category : Category and Workload wait

Grid	Description
Wait Type	The wait type impacting on wait time. For additional information, see https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql?view=sql-server-ver16
Wait Time	The wait time spent on this wait type aggregated over the last hour.
% of Total Wait	The proportion of wait time spent on this wait type, compared to other wait types of the selected workload.
% of Sample Time	The proportion of wait time (for this wait type) over the time frame selected on this drilldown page.

Category : Selected item compared to All

Chart	Description
Selected item compared to All	Show on a time series chart the wait time rate (ms/s) attributed to the selected workload item and the selected category of wait.
Current selected item compared	Show on a pie chart the wait time rate (ms/s) currently attributed to the selected workload item as a proportion of the selected category of wait.

Category : Selected category compared to Total

Chart	Description
Selected category comparison	Show on a time series chart the wait time rate (ms/s) attributed to the selected category and the total wait time rate.
Current Wait category comparison	Show on a pie chart the wait time rate (ms/s) currently attributed to the selected category as a proportion of the total wait time rate.

Category : CLR (Common Language Runtime)

CLR is partially supported in Amazon RDS for SQL Server (Based on the SQL Server versions installed on Amazon RDS).

Major version	CLR
SQL Server 2022	Not supported
SQL Server 2019	Not supported
SQL Server 2017	Not supported
SQL Server 2016	Supported

Official reference for supported versions: [Amazon RDS for Microsoft SQL Server - Amazon Relational Database Service] (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html)

Official reference for CLR support-related information: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.FeatureNonSupport.html

Among the four major versions of SQL Server available in Amazon RDS for SQL Server, only the oldest version supports CLR.

Since CLR is not supported in the latest SQL Server versions available on Amazon RDS, we do not provide official support for CLR.

Note: We are retaining the category in the UI to accommodate users of SQL Server 2016 on Amazon RDS who may use CLR. While such users might retrieve data, it is important to note that this functionality has not been officially tested or supported.

Category : XTP (Extreme Transaction Processing)

XTP has limited support in Amazon RDS for SQL Server.

XTP waits are associated with In-Memory OLTP objects. As indicated in the link below, there are limitations to OLTP functionality in Amazon RDS for SQL Server.

Official reference for limited OLTP support: https://aws.amazon.com/blogs/database/amazon-rds-for-sql-server-now-supports-sql-server-2019/

Due to these limitations, we do not provide official support for XTP in Amazon RDS for SQL Server connections.

Note: We are retaining the category in the UI to allow users encountering XTP-related waits to access relevant data. However, it is important to note that this functionality has not been officially tested or supported.