Help Center > > Best Practices> Microsoft SQL Server> Supporting DMVs

Supporting DMVs

Updated at: Jun 17, 2019 GMT+08:00

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.

    For details about the fields, visit sys.dm_exec_query_stats (Transact-SQL).

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel