RDS for SQL Server supports dynamic management views (DMVs), which enables users to quickly find 10 SQL statements with the highest performance consumption.
- A performance bottleneck occurs and the database execution efficiency becomes low.
- The monitoring result shows that the CPU and I/O are high in some time segments.
- Use the rdsuser account to connect to the target DB instance through a client and run the following statements on the management plane:
declare @DatabaseName nvarchar(100) set @DatabaseName = 'Wisdom_TT_ODS' select top 100 DB_NAME(st.dbid) as DBName, OBJECT_NAME(st.objectid,st.dbid) as ObjectName, substring(st.text,(qs.statement_start_offset/2)+1,((case qs.statement_end_offset when -1 then datalength(st.text) else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as Statement, st.text as Query, qp.query_plan, plan_generation_num, creation_time, last_execution_time, execution_count, total_worker_time, min_worker_time, max_worker_time, total_logical_reads, min_logical_reads, max_logical_reads, total_elapsed_time, min_elapsed_time, max_elapsed_time, total_rows, min_rows, max_rows, ,total_worker_time/execution_count as avg_worker_time --- Average CPU duration ,total_logical_reads/execution_count as avg_logical_reads --- Average logical reads ,total_elapsed_time/execution_count as avg_elapsed_time --- Average total duration ,total_rows/execution_count as avg_rows --- Average data processing rows sql_handle, plan_handle, query_hash, query_plan_hash, from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(plan_handle) st cross apply sys.dm_exec_query_plan(plan_handle) qp where st.dbid=DB_ID(@DatabaseName) and text not like '%sys.%'and text not like '%[sys]%' order by avg_worker_time desc
- You can view the SQL execution records and resource consumption details of the corresponding database in the query result.
For details about the fields, visit sys.dm_exec_query_stats (Transact-SQL).