rds sql server支持dmv动态管理视图-九游平台

操作场景

• 数据库执行效率较低。
• 某些时段的cpu，io较高。

操作步骤

1. 登录sql server客户端，通过rdsuser账号连接目标实例，在管理面中执行以下sql语句。

   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,                            --平均cpu耗时
   total_logical_reads/execution_count as avg_logical_reads,                        --平均逻辑读
   total_elapsed_time/execution_count as avg_elapsed_time,                          --平均总耗时
   total_rows/execution_count as avg_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. 查看结果中对应数据库的sql执行记录及资源消耗情况。