|
一. 背景：
?????我们管理和维护的数据库系统基本都是7*24小时运转的，运转时会出现什么情况谁都无法估计，往往一个平时运行很正常的系统，某天晚上突然就抽风了，而此刻并没有系统负责人在旁边，当值班同事的电话打到正在酣睡的DBA手机上时，DBA不得不朦朦胧胧的，条件反射要上数据库看看；要在家里连到公司的内外一般都需要DBA通知运维人员（也可能是个在做梦的哥们）先给自己开个VPN（某些管控严格的公司还需要DBA打车去公司才能处理），然后通过一大堆的验证才通过慢如蜗牛（往往也和此刻的心态有关）的网络，远程连接到了公司内网出问题的数据库上，此刻DBA才开始真正开始排查起问题来；如果说等待DBA登录到问题数据库上，问题依然在出现，那还算好，可以很快找到问题并处理，但是往往这种抽风的现象是短暂的，可能持续几分钟后，系统又恢复了正常，等到DBA"跑山涉水，翻山越岭" 的好不容易登录到数据库上检查时，数据库里的进程、锁、日志一起都正常，系统运行的很Hai；于是第二天上班时老大们问起昨天晚上事故的原因时，DBA只能凭空猜测，可能是网络、数据库阻塞、抑或是程序方面的问题吧......，此刻不同部门的人都猜是其他部门管理的东西出了问题，于是大家都把手头上的数据、监控图拿出来，证明自己这里是没有问题的；如果监控网络和系统的图和APP的Log都没用出现啥问题（说实话监控也不一定准确的），那就基本要把问题推到数据库身上了（DBA往往成为炮灰）。
??????数据库有没有出问题，如果出了问题又是啥问题呢？windows Log和DB Log都没异常，如果我们没有把Profile持续的开启（基于性能的考虑，一般都不会持续开启），就很难说清楚在系统出问题时数据库究竟有没有出问题，出了问题又是因为什么原因引起的；如果说此类问题出现了一次，就消失了，那还算好，成为一个无头案，悲剧的是这类问题无规律，反复的出现，如果DBA不能找出问题的原因，也不能证明数据库当时是正常的话，估计在公司里面就没用立足之地了。
?
二. 对策：
??? 其实对这种灵异的系统抽风事件，有两种比较好的解决方案：
??? 1. 开启Trace 跟踪，就是数据库的Profile功能?，这个还可以结合windows的性能计数器一同使用，能够直观的了解到特殊时间点上运行了什么语句，资源消耗情况是怎样的；不过这个方法比较消耗系统的资源，对访问压力比较大的数据库需要慎重；
????2. 收集数据库DMV当时的情况，使得DBA在故障时间过后，还能通过这些数据了解到事故发生时，数据库里面运行的语句，以及锁和资源的情况；这种方法只是访问系统性能视图，对数据库其他业务影响比较小，也是我接下来要介绍的方案。
?
三. 实施方案：
???思路：根据数据库中某些动态指标，触发收集DMV的过程，必要时发送报警邮件
1.?创建存储系统性能视图的表：
--创建四个记录表
USE[master]
GO
/****** Object:? Table [dbo].[dc_block_info]? ??Script Date: 05/23/2011 11:35:48 ******/
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
SETANSI_PADDING ON
GO
CREATETABLE[dbo].[dc_block_info](
??? [spid][smallint]NULL,
??? [status][nchar](30) NULL,
??? [SQLBuffer][nvarchar](max) NULL,
??? [hostname][nchar](128) NULL,
??? [BlkBy][varchar](10) NULL,
??? [BlockedSQLBuffer][nvarchar](max) NULL,
??? [LoginName][varchar](100) NULL,
??? [DBName][varchar](50) NULL,
??? [CPUTime][int]NULL,
??? [DiskIO][int]NULL,
??? [LastBatch][datetime]NULL,
??? [program_name][nchar](128) NULL,
??? [Command][varchar](100) NULL,
??? [batch_id][int]NULL
) ON[PRIMARY]
GO
SETANSI_PADDING OFF
GO
USE[master]
GO
/****** Object:? Table [dbo].[dc_Blocked_Resource_Info]??? Script Date: 05/23/2011 11:35:48 ******/
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
CREATETABLE[dbo].[dc_Blocked_Resource_Info](
??? [spid][smallint]NULL,
??? [dbid][smallint]NOTNULL,
??? [ObjId][int]NOTNULL,
??? [IndId][smallint]NOTNULL,
??? [Type][nvarchar](4) NULL,
??? [Resource][nvarchar](32) NULL,
??? [Mode][nvarchar](8) NULL,
??? [Status][nvarchar](5) NULL,
??? [batch_id][int]NULL
) ON[PRIMARY]
GO
USE[master]
GO
/****** Object:? Table [dbo].[dc_info_BlockedInfo]??? Script Date: 05/23/2011 11:35:48 ******/
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
SETANSI_PADDING ON
GO
CREATETABLE[dbo].[dc_info_BlockedInfo](
??? [batch_id][int]NULL,
??? [lock_type][varchar](100) NULL,
??? [database_name][varchar](20) NULL,
??? [blk_object][varchar](100) NULL,
??? [lock_req][varchar](100) NULL,
??? [waiter_sid][int]NULL,
??? [wait_time][int]NULL,
??? [waiter_batch][varchar](max) NULL,
??? [waiter_stmt][varchar](max) NULL,
??? [blocker_sid][int]NULL,
??? [blocker_stmt][varchar](max) NULL,
??? [create_date][datetime]NULL
) ON[PRIMARY]
GO
SETANSI_PADDING OFF
GO
USE[master]
GO
/****** Object:? Table [dbo].[dc_info_SessionConn]??? Script Date: 05/23/2011 11:35:48 ******/
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
SETANSI_PADDING ON
GO
CREATETABLE[dbo].[dc_info_SessionConn](
??? [batch_id][int]NULL,
??? [session_id][int]NULL,
??? [blocking_session_id][int]NULL,
??? [textdata][varchar](max) NULL,
??? [login_name][varchar](30) NULL,
? ??[host_name][varchar](100) NULL,
??? [database_name][varchar](30) NULL,
??? [program_name][varchar](200) NULL,
??? [command][varchar](100) NULL,
??? [status][varchar](20) NULL,
??? [cpu_time][int]NULL,
??? [memory_usage_kb][int]NULL,
??? [reads][int]NULL,
??? [writes][int]NULL,
??? [transaction_isolation_level][int]NULL,
??? [connect_time][datetime]NULL,
??? [last_read][datetime]NULL,
??? [last_write][datetime]NULL,
??? [net_transport][varchar](20) NULL,
??? [client_net_address][varchar](30) NULL,
??? [client_tcp_port][int]NULL,
??? [local_tcp_port][int]NULL,
??? [start_time][datetime]NULL,
??? [wait_type][varchar](100) NULL,
??? [last_wait_type][varchar](100) NULL,
??? [wait_resource][varchar](1000) NULL,
??? [open_transaction_count][int]NULL,
??? [create_date][datetime]NULL
) ON[PRIMARY]
GO
SETANSI_PADDING OFF
GO
2. 定义性能收集的存储过程：
USE[master]
GO
--数据库的阻塞和锁信息，该SP可以记录session中显示不出的信息??
createprocedure[dbo].[usp_blocker_info](@batch_idint)???
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
as???
?????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
begin???
???????????????? ???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?setnocount on???
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?declare@spidsmallint,@blockedsmallint???
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ???????????
?declare@c_SQLnvarchar(4000),@b_SQLnvarchar(4000)???
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????
?declare@idint???
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????
?--保存Sysprocesses 的内容?
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????????
?createtable#Temp(spid smallint,status nchar(30),hostname nchar(128),program_name nchar(128)???
???????????????????????????????????????????????????????????????????????????????????????????????????????? ??????????????????????????????????????????????????????????
??,cmd nchar(16),cpu int,physical_io int,blocked smallint,dbid smallint???
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????
??,loginame nchar(128),last_batch datetime???
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????????
??,SQLBuffer nvarchar(4000),BlockedSQLBuffer nvarchar(4000))???
??????
?--保存DBCC InputBuffer 的结果?
??????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????
?createtable#Temp1(id intidentity(1,1),eventtype varchar(20),parameters int,eventinfo nvarchar(4000))???
??
?createtable#Temp_b(id intidentity(1,eventinfo nvarchar(4000))??
?select*into#Temp2???
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ???????????????????????????
? frommaster..sysprocesses (nolock)???
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?
?--保存被阻塞的进程信息?
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????
?insertinto#Temp(spid,status,hostname,program_name,cmd,cpu,physical_io,blocked,dbid????
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????
??,loginame,last_batch )???
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????
? SELECTspid,dbid???
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ??????????????????????????????????????????????????????
????,convert(sysname,rtrim(loginame)),last_batch???
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????
?? from#Temp2???
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????
?? whereblocked >0???
?--保存阻塞的源头?
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????????????????????????????????????????????????????????
?insertinto#Temp(spid,dbid????
???????????????????????????????????????????????????????? ??????????????????????????????????????????????????????????????????????????????????????????????????????????
??,last_batch )???
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????
? SELECTspid,dbid???
????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
????,last_batch???
?????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?? from#Temp2???
???????????????????????????????????????????????????????????????????????????????????????????????????????????? ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?? wherespid in(selectblocked from#Temp)???
??????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
??? andspid notin(selectspid from#Temp)???
???????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?
?select@spid=min(spid) from#Temp???
??????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?
?while@spidisnotnull???
????????????????????????????????????????? ????????????????????????????????????????????????????
?begin???
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????????????????
? set@c_SQL='dbcc inputbuffer('+convert(varchar(5),@spid) +')'???
???
? select@blocked=isnull(blocked,0) from#Tempwherespid=@spid?
???
? if(@blocked<>0)?
?? begin?
??? set@b_SQL='dbcc inputbuffer('+convert(varchar(5),@blocked) +')'???
??? insertinto#Temp_b?
??????? exec(@b_SQL)???
?????????
?? select@id=@@identity???
????
?? update#Temp???
???????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?? setBlockedSQLBuffer =#Temp_b.eventinfo???
????????????????????????????????????? ???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?? from#Temp,#Temp_b???
????????????????????????????????????????????? ??????????????????????????????????????????????
?? where#Temp_b.id =@id???
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ??????????????????????????????????????????????????????
??? and#Temp.blocked =@blocked???
?? end?
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????
?
? insertinto#Temp1???
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ??????????????????????????????????????????????????????????????????????????????????????????????????????
?? exec(@c_SQL)???
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????????????????????
? select@id=@@identity???
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?? update#Temp???
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
??? setSQLBuffer =#Temp1.eventinfo???
?????????????????????????????????????????????????????????????????????????????????????????? ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?? from#Temp,#Temp1???
??????????????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?? where#Temp1.id =@id???
???????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
??? and#Temp.spid =@spid???
??????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?
? select@spid=min(spid) from#Tempwherespid >@spid???
????????????????????????? ???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?end???
?????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????????????????????????????????????????????????????????????????????
????
?insertintodc_block_info(batch_id,spid,SQLBuffer,BlkBy,BlockedSQLBuffer,LoginName,DBName,?
?CPUTime,DiskIO,LastBatch,Command)???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
??????????????????????????? ???????????????????
?SELECT@batch_id,convert(char(5),spid) SPID,CASElower(status) When'sleeping'Thenlower(status) Elseupper(status) ENDStatus???
?????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????
??,SQLBuffer???
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????
??,CASEhostname WhenNull? Then'? .'When' 'Then'? .'Elsehostname ENDHostName???
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ??????????????????????????????????????????????????
??,CASEisnull(convert(char(5),blocked),'0') When'0'Then'? .'???
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????
?????????????????????? Elseisnull(convert(char(5),'0') ENDBlkBy,BlockedSQLBuffer???
????????????????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????
??,loginame Login???
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????
??,db_name(dbid) DBName,convert(varchar,cpu) CPUTime???
???????????????????????????????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????????????????????????????????????????????????????
??,physical_io) DiskIO,Last_Batch LastBatch???
?????????????????????????????????????????????????????????????????????????????????????????????? ??????????????????????????????????????????????????????????????????????????????????????????????????????
??,program_name ProgramName,cmd Command???
?????????????????????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????????????????????
? from#Temp???
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
? orderbyBlkBy,spid???
??????????????????????????????????????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?
?setnocount off???
?????????????????????????????????????????????????????????????????????????????????????????????? ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
end???
Go
USE[master]
GO???
?
--预警SP
CREATEproc[dbo].[RecodeAndAlertInfo]???
as???
begin???
declare@banch_idint???
select@banch_id=isnull(MAX(batch_id),0)+1fromdc_info_SessionConn??
--记录当前所有会话信息
insertintodc_info_SessionConn???
SELECT@banch_id,S.session_id,R.blocking_session_id,???
?current_execute_sql =SUBSTRING(T.text,???
??? R.statement_start_offset /2+1,???
??? CASE???
???? WHENstatement_end_offset =-1THENLEN(T.text)???
???? ELSE(R.statement_end_offset -statement_start_offset) /2+1???
??? END),???
?S.login_name,S.host_name,databaseName=DB_NAME(R.database_id),S.program_name,R.command,???
?S.status,S.cpu_time,memory_usage_kb =S.memory_usage *8,S.reads,S.writes,???
?S.transaction_isolation_level,C.connect_time,C.last_read,C.last_write,???
?C.net_transport,C.client_net_address,C.client_tcp_port,C.local_tcp_port,???
?R.start_time,R.wait_time,R.wait_type,R.last_wait_type,R.wait_resource,???
?R.open_transaction_count,GETDATE()????
FROMsys.dm_exec_sessions S???
?LEFTJOINsys.dm_exec_connections C???
? ONS.session_id =C.session_id???
?LEFTJOINsys.dm_exec_requests R???
? ONS.session_id =R.session_id???
?? ANDC.connection_id =R.connection_id???
?OUTERAPPLY sys.dm_exec_sql_text(R.sql_handle) T???
WHERE? S.is_user_process =1? -- 如果不限制此条件，则查询所有进程（系统和用户进程）?
ANDcommand isnotnull???
???????
???
--记录当前阻塞信息?
insertintodc_info_BlockedInfo???
select@banch_id,t1.resource_type as[lock type],db_name(resource_database_id) as[database]???
,t1.resource_associated_entity_id as[blk object]???
,t1.request_mode as[lock req]????????????????????????? -- lock requested???
,t1.request_session_id as[waiter sid]????????????????????? -- spid of waiter???
,t2.wait_duration_ms as[wait time]?????????
,(selecttextfromsys.dm_exec_requests asr????????????????? --- get sql for waiter???
crossapply sys.dm_exec_sql_text(r.sql_handle)????
wherer.session_id =t1.request_session_id) aswaiter_batch???
,(selectsubstring(qt.text,r.statement_start_offset/2,????
(casewhenr.statement_end_offset =-1thenlen(convert(nvarchar(max),qt.text)) *2????
elser.statement_end_offset end-r.statement_start_offset)/2)????
fromsys.dm_exec_requests asr???
crossapply sys.dm_exec_sql_text(r.sql_handle) asqt???
wherer.session_id =t1.request_session_id) aswaiter_stmt??? --- statement executing now???
,t2.blocking_session_id as[blocker sid]??????????????? --- spid of blocker???
,(selecttextfromsys.sysprocesses asp?????????????????????? --- get sql for blocker???
crossapply sys.dm_exec_sql_text(p.sql_handle)????
wherep.spid =t2.blocking_session_id) asblocker_stmt,getdate()???
fromsys.dm_tran_locks ast1,sys.dm_os_waiting_tasks ast2???
wheret1.lock_owner_address =t2.resource_address???
?????
--记录资源信息
insertintodc_Blocked_Resource_Info?
select? convert(smallint,req_spid) Asspid,???
? rsc_dbid Asdbid,???
? rsc_objid AsObjId,???
? rsc_indid AsIndId,???
? substring(v.name,1,4) AsType,???
? substring(rsc_text,32) asResource,???
? substring(u.name,8) AsMode,???
? substring(x.name,5) AsStatus,@banch_id??
?from? master.dbo.syslockinfo,???
? master.dbo.spt_values v,???
? master.dbo.spt_values x,???
? master.dbo.spt_values u?????
?where?? master.dbo.syslockinfo.rsc_type =v.number???
?? andv.type ='LR'???
?? andmaster.dbo.syslockinfo.req_status =x.number???
?? andx.type ='LS'???
?? andmaster.dbo.syslockinfo.req_mode +1=u.number???
?? andu.type ='L'???
?andsubstring(x.name,5) ='WAIT'?
?orderbyspid? ??
?
?execusp_blocker_info @banch_id
???
end
GO
3. 创建信息收集和邮件报警的SP：
??? 说明：
??? SP定义了一个邮件发送的过程，需要先配置好数据库的邮件发送（google一下很多的）；
??? SP需要做到JOB里面，一分钟运行一次收集信息；
??? SP收集信息时的条件（不同的系统触发条件不一样）：
?????? a. 用户链接数大于550，活动链接数大于40；
?????? b. 阻塞进程比率大于10%；
USE[master]
GO???
--创建触发监控条件，并发邮件
CREATEproc[dbo].[ConAlert]?????????
as?????????
begin?????????
?DECLARE@connfloat,@activeconnfloat,@blockedcountfloat,@spidcountfloat,@spidblockedfloat,@countint??????
?DECLARE@bodyNVARCHAR(MAX),@subject? nvarchar(200)?????
?
?-- User Connections????????
? Select@conn=ISNULL(cntr_value,0) fromsys.dm_os_performance_counters with(nolock)
? wherecounter_name='User Connections'
?????
? if@conn>=550--根据用户链接数来收集信息和触发报警???
?? begin?????
???? --active requests?? ?
???? Select@activeconn=isnull(SUM(cntr_value),0) fromsys.dm_os_performance_counters with(nolock)
?????? wherecounter_name ='Active requests'
?????? groupbycounter_name
??? set@count=1
??? if@activeconn>=40? --根据活动链接数来判断?????
???? begin???????
?????? while1=1? --循环收集信息
??????? begin
??????????? execRecodeAndAlertInfo??
?????????? if(@count%5=0) --连续次就发邮件
???????????? begin
?????????????? set@subject='Server:'+@@SERVERNAME+' Connections Alert'
?????????????? SET@body='Server:'+@@SERVERNAME+CHAR(13)+'; UserConnections:'+cast(@connasvarchar)+CHAR(13)+'; ActiveRequests:'+cast(@activeconnasvarchar)??????????????
?????????????? EXECmsdb.dbo.sp_send_dbmail???????????
??????????????? @recipients=N'Ken@xxx.com;itmon@xxx.com',? ?????????????????????
??????????????? @body=@body,???????????
??????????????? @body_format='TEXT',???????????
??????????????? @subject=@subject,???????????
??????????????? @profile_name='dba_profile'--需要配置该模板?
???????????? end
???????????? Select@activeconn=isnull(SUM(cntr_value),0) fromsys.dm_os_performance_counters with(nolock)
?????????????? wherecounter_name ='Active requests'
?????????????? groupbycounter_name
???????????? if@activeconn>=40
???????????????? waitfordelay '00:00:05'
???????????? else
????????????????? break;
???????????? if@count>50
?????????????????? break;
?????????? set@count=@count+1;
?????? end???
?? end????????
?? end?????
?else--根据Blocked 百分比来收集信息和触发报警??
?? begin?????
???? select@spidcount=count(0) fromsys.sysprocesses with(nolock) wherespid>50???????
???? select@blockedcount=count(0) fromsys.sysprocesses with(nolock) wherespid>50andblocked<>0?
???? --计算百分比
???? set@spidblocked=@blockedcount/@spidcount
???? set@count=1
???? if(@spidblocked>=0.1) --10%时，记录信息?
????? begin???????
?????? while1=1? --循环收集信息
??????? begin
??????????? execRecodeAndAlertInfo??
?????????? if(@count%5=0or@spidblocked>=0.15) --连续5次阻塞比率大于10%或者阻塞比率大于等于15%时，记录信息并发生报警邮件???
???????????? begin
???????? ???????set@subject='Server:'+@@SERVERNAME+' Blocked Alert'?????????????
??????????????? SET@body='Server:'+@@SERVERNAME+CHAR(13)+'; BlockedCount:'+cast(@blockedcountasvarchar)+CHAR(13)+'; SPIDCount:'+cast(@spidcountasvarchar)??????????????
????? ??????????EXECmsdb.dbo.sp_send_dbmail???????????
??????????????? @recipients=N'Ken@xxx.com',??????????????????????
??????????????? @body=@body,???????? ???
??????????????? @profile_name='dba_profile'???
???????????? end
???????????? select@spidcount=count(0) fromsys.sysprocesses with(nolock) wherespid>50???????
???????????? select@blockedcount=count(0) fromsys.sysprocesses with(nolock) wherespid>50andblocked<>0?
???????????? set@spidblocked=@blockedcount/@spidcount
???????????? if@spidblocked>=0.1
???????????????? waitfordelay '00:00:05'
???????????? else
????????????????? break;
???????????? if@count>50
?????????????????? break;
????? ?????set@count=@count+1;
?????? end
????? end?
?? end?
end
4. 查看信息：
USE[master]
GO
--通过以下查询来分析信息
declare@batch_idint
select@batch_id=isnull(MAX(@batch_id),1) fromdc_info_SessionConn
select? *from? dc_info_SessionConn wherebatch_id=@batch_id
select? *from? dc_info_BlockedInfo wherebatch_id=@batch_id
select? *from? dc_block_info?????? wherebatch_id=@batch_id
select? *from? dc_Blocked_Resource_Info where=@batch_id
?
??? 有了这些阻塞、进程和资源的信息，我们就可以了解系统出问题时，是否出现异常，以及数据库异常时都有些什么语句在系统中运行，它们是否造成了大量的阻塞或消耗了大量的资源；这样DBA就方便定位问题了。
?
?作者“飞洋过海”? http://www.dedecms.com/knowledge/data-base/sql-server/2012/0821/11548.html
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
