Performance Insights for RDS for SQL Server instances does not provide full SQL statement in file

According to the AWS Performance Insights documentation, there is a maximum character length that can be downloaded for SQL text statements. Specifically for RDS SQL Server, the limit is 4,096 characters. To retrieve the full SQL statement text that gets truncated at 4,096 characters in Performance Insights, you can use the "SQL ID" provided in the Performance Insights console to query the database directly. This will return the complete SQL statement without truncation. I've successfully used below mention workaround before when dealing with long SQL statements that exceeded the character limit for the Performance Insights download.

1. SQL Query:

SELECT last_execution_time, execution_count, text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt WHERE sql_handle =0x02000000C378CE;

Note: Where SQL ID=02000000C378CE, replace this with your SQL ID and add “0x" before the SQL ID.

To get the SQL ID > Go to Performance Insights > Choose RDS Database name > Under TOP SQL (Click on the query and on the top and bottom of the SQL text window you will see SQL ID for that query. You can replace the SQL ID in the above query which will give you full query information.)

2. Enable Query Store

Enabling and reviewing the query store also aided my performance evaluation of queries in the RDS SQL database. The query store automatically captures historical information about queries, execution plans, and runtime statistics, and retain for your review later. It can be used for tuning slower queries and understand plan regressions.

As you may be aware, by default, each row in the Top SQL table shows 500 bytes of SQL text for each SQL statement. When a SQL statement is larger than 500 bytes, you can view more of the SQL statement by opening the statement in the Performance Insights dashboard. Please refer the documentation for more information [1]. However, there is a limitation for how much text is displayed and can be downloaded for each engine [1]. For SQL Server the maximum download size on the console is 4kb and it is that way by design and is not a bug. I do note how the text you have noted on the console is misleading and a feature request is also raised and being worked upon by the RDS engineering team. However, the ETA is not provided of which this feature will be released.

Ref: [1] SQL text engine limits - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.UsingDashboard.SQLTextSize.html#sql-text-engine-limits