建议使用以下浏览器,以获得最佳体验。 IE 9.0+以上版本 Chrome 31+ 谷歌浏览器 Firefox 30+ 火狐浏览器
请选择 进入手机版 | 继续访问电脑版
设置昵称

在此一键设置昵称,即可参与社区互动!

确定
我再想想
选择版块

天地志鹏

发帖: 17粉丝: 5

级别 : 新手上路

Rank: 1

发消息 + 关注

发表于2019-4-22 17:13:23 3102 1 楼主 显示全部楼层
[Microsoft SQL Server] 三招搞定!RDS SQL Server实例上各种T-SQL语句的运行问题,你也是高手

背景说明:随着华为云 SQL Server的用户越来越多,很多的小伙伴对于自己云上的数据库实例,在运行过程中产生的各种问题如何快速有效的进行排查,定位,优化?在本篇文章中我会拉通一一给大家解答:

 

适用场景:

1.对实例上资源消耗最高的SQL语句进行定位及优化

2.对实例上特定时间点内出现的性能峰值进行跟踪和判断优化

3.对实例上突发的性能问题进行快速的排查和解决

总结:起来就是查历史,跟详情,抓现场,三招搞定你的所有困惑

 

操作步骤:

1.查历史:用户实例如果平均CPU消耗在50%以上,如何能够全局性的对实例上运行的T-SQL语句快速的定位,找出消耗资源最高的那几个,以最低的优化成本降低整个实例的运行压力(推荐采用SSMSDAS工具登录执行)

a.通过rdsuser用户登录目标实例,登录后直接在SQL查询窗口下执行以下语句:

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 text not like '%sys.%'and text not like '%[[]sys]%'

order by avg_worker_time desc

 

b.本语句能对当前实例上所有运行过的T-SQL语句进行了全方面的记录,并对每条语句的统计结果进行了详细的排查,通过该语句的查询结果,整个实例上的T-SQL执行记录以及对应的资源消耗详情。

官方字段说明详见:http://technet.microsoft.com/zh-tw/library/ms189741.aspx

 

c.其中最常用的是total_worker_time total_elapsed_time,这两个参数一个是CPU累计消耗时间,一个是SQL执行累计消耗时间,通过倒序排序,从而获得该数据库或整个实例上面消耗最高的前10 SQL语句(根据经验,每个实例上CPU消耗的70%都在前10SQL语句上!)

 

d.后面就需要用户自己通过Statement中的SQL语句,结合自身应用业务场景进行优化了。

注:query_plan里面的执行计划有很好的参考价值

 

2.跟详情:用户在实际使用过程中,通过云上监控记录发现每天的固定时间点会出现有规律的性能波峰情况,例如:每天早上9点到10点,CPU消耗陡升到80%以上,用户侧出现明显的性能下降等情况(推荐采用SSMS登录执行)

a.针对该类场景我们要开通SQL Server Profile去跟踪这个时间段的具体执行情况,并判断该时间段具体的SQL执行情况,针对异常SQL脚本进行针对性调优。

b.用户可通过如下脚本快速在RDS SQL Server实例上开通Profile 脚本抓取进程,该脚本会默认在用户的实例上创建一个自动抓取所有T-sql运行脚本的进程,并按照每个文件500MB,一共7文件的方式自动滚动保存。

注:以下脚本中的跟踪事件可以自行添加,但是要当心性能问题,执行以下语句会对实例整体性能造成5%--10%左右的压力,如果用户不当添加跟踪事件造成服务器压力聚升,概不负责哦!

 

--创建跟踪文件返回值

declare @RC int

--创建一个跟踪句柄

declare @TraceID int

--创建跟踪文件路径

declare @TraceFilePath nvarchar(500)

set @TraceFilePath=N'D:\RDSDBDATA\MyTrace'

--跟踪文件的大小

declare @maxfilesize bigint

set @maxfilesize=500

--设置停止的时间

declare @EndTime datetime

set @EndTime=null

--设置系统默认的操作

declare @options int

set @options=2

--设置默认滚动文件的数目

declare @filecount int

set @filecount=5

exec @rc=sp_trace_Create

@TraceID output,

@options,

@TraceFilePath,

@maxfilesize,

@EndTime,

@filecount

 

if (@rc != 0) goto error

if(@rc=0)

select  @TraceID

 

 

-- Client side File and Table cannot be scripted

 

-- Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 10, 1, @on

exec sp_trace_setevent @TraceID, 10, 9, @on

exec sp_trace_setevent @TraceID, 10, 2, @on

exec sp_trace_setevent @TraceID, 10, 66, @on

exec sp_trace_setevent @TraceID, 10, 10, @on

exec sp_trace_setevent @TraceID, 10, 3, @on

exec sp_trace_setevent @TraceID, 10, 4, @on

exec sp_trace_setevent @TraceID, 10, 6, @on

exec sp_trace_setevent @TraceID, 10, 7, @on

exec sp_trace_setevent @TraceID, 10, 8, @on

exec sp_trace_setevent @TraceID, 10, 11, @on

exec sp_trace_setevent @TraceID, 10, 12, @on

exec sp_trace_setevent @TraceID, 10, 13, @on

exec sp_trace_setevent @TraceID, 10, 14, @on

exec sp_trace_setevent @TraceID, 10, 15, @on

exec sp_trace_setevent @TraceID, 10, 16, @on

exec sp_trace_setevent @TraceID, 10, 17, @on

exec sp_trace_setevent @TraceID, 10, 18, @on

exec sp_trace_setevent @TraceID, 10, 25, @on

exec sp_trace_setevent @TraceID, 10, 26, @on

exec sp_trace_setevent @TraceID, 10, 31, @on

exec sp_trace_setevent @TraceID, 10, 34, @on

exec sp_trace_setevent @TraceID, 10, 35, @on

exec sp_trace_setevent @TraceID, 10, 41, @on

exec sp_trace_setevent @TraceID, 10, 48, @on

exec sp_trace_setevent @TraceID, 10, 49, @on

exec sp_trace_setevent @TraceID, 10, 50, @on

exec sp_trace_setevent @TraceID, 10, 51, @on

exec sp_trace_setevent @TraceID, 10, 60, @on

exec sp_trace_setevent @TraceID, 10, 64, @on

exec sp_trace_setevent @TraceID, 12, 1, @on

exec sp_trace_setevent @TraceID, 12, 9, @on

exec sp_trace_setevent @TraceID, 12, 3, @on

exec sp_trace_setevent @TraceID, 12, 11, @on

exec sp_trace_setevent @TraceID, 12, 4, @on

exec sp_trace_setevent @TraceID, 12, 6, @on

exec sp_trace_setevent @TraceID, 12, 7, @on

exec sp_trace_setevent @TraceID, 12, 8, @on

exec sp_trace_setevent @TraceID, 12, 10, @on

exec sp_trace_setevent @TraceID, 12, 12, @on

exec sp_trace_setevent @TraceID, 12, 13, @on

exec sp_trace_setevent @TraceID, 12, 14, @on

exec sp_trace_setevent @TraceID, 12, 15, @on

exec sp_trace_setevent @TraceID, 12, 16, @on

exec sp_trace_setevent @TraceID, 12, 17, @on

exec sp_trace_setevent @TraceID, 12, 18, @on

exec sp_trace_setevent @TraceID, 12, 26, @on

exec sp_trace_setevent @TraceID, 12, 31, @on

exec sp_trace_setevent @TraceID, 12, 35, @on

exec sp_trace_setevent @TraceID, 12, 41, @on

exec sp_trace_setevent @TraceID, 12, 48, @on

exec sp_trace_setevent @TraceID, 12, 49, @on

exec sp_trace_setevent @TraceID, 12, 50, @on

exec sp_trace_setevent @TraceID, 12, 51, @on

exec sp_trace_setevent @TraceID, 12, 60, @on

exec sp_trace_setevent @TraceID, 12, 64, @on

exec sp_trace_setevent @TraceID, 12, 66, @on

 

--设置跟踪状态以开启

exec sp_trace_setstatus @TraceID,1

 

-- display trace id for future references

select TraceID=@TraceID

goto finish

 

error:

select ErrorCode=@rc

 

finish:

go

 

c.以上脚本请在微软SSMS官方工具最新版本上执行,执行成功后可通过以下脚本获取当前开通的TraceID和运行状态:

----查看跟踪事件

select * from ::fn_trace_getinfo(default)

----查看跟踪信息

select * from ::fn_trace_gettable('D:\RDSDBDATA\MyTrace_1.trc', default)

 

d.如果需要对该事件进行删除请运行如下语句:

--设置跟踪状态以停止

exec sp_trace_setstatus 2,0

 

--从系统中移除跟踪

exec sp_trace_setstatus 2,2

 

 

3.抓现场:用户实例在使用过程中,偶发出现语句阻塞或执行超时的现象,该类问题一般是因为锁等待引起的,但是也不排除其他各种外界因素,为了能够准确的定位根因,获取当前实例的运行状况是最有效的判断方式(推荐采用SSMSDAS工具登录执行)

a.当你觉得实例运行缓慢的时候,请在查询窗口内执行如下语句,获取当前实例的具体运行情况,运行出来的结果如果你不方便分析,可以将结果保存在本地,提交给华为方帮助分析。

--当前实例查询

SELECT     [Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid),

 [User] = nt_username, [Status] = er.status,

  [Wait] = wait_type,

  [Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2, (CASE WHEN er.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))

                       * 2 ELSE er.statement_end_offset END - er.statement_start_offset) / 2),

                        [Parent Query] = qt.text,

                        Program = program_name, Hostname,

                        nt_domain, start_time

 FROM   

      sys.dm_exec_requests er INNER JOIN  sys.sysprocesses sp ON er.session_id = sp.spid

      CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

 WHERE     session_Id > 50 /* Ignore system spids.*/ AND session_Id NOT IN (@@SPID)

 

总结:以上三招组合运行,即可连绵不绝的帮助你摆平各种场景。


举报
分享

分享文章到朋友圈

分享文章到微博

十几个啤酒

发帖: 0粉丝: 0

级别 : 新手上路

Rank: 1

发消息 + 关注

发表于2019-4-22 17:43:00 沙发 显示全部楼层

大佬威武

点赞 回复 举报

游客

富文本
Markdown
您需要登录后才可以回帖 登录 | 立即注册