Updated on 2022-12-07 GMT+08:00

Supporting DMVs

RDS for SQL Server supports dynamic management views (DMVs), which enables users to quickly find 10 SQL statements with the highest performance consumption.

Scenarios

  • 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.

Procedure

  1. 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

  2. You can view the SQL execution records and resource consumption details of the corresponding database in the query result.