mysql追溯性能问题_SQLServer 通过DMV实现低影响的自动监控和历史场景追溯

USE[master]GO--数据库的阻塞和锁信息，该SP可以记录session中显示不出的信息createprocedure[dbo].[usp_blocker_info](@batch_idint)asbeginsetnocountondeclare@spidsmallint,@blockedsmallintdeclare@c_SQLnvarchar(4000),@b_SQLnvarchar(4000)declare@idint--保存Sysprocesses 的内容createtable#Temp(spidsmallint,statusnchar(30),hostnamenchar(128),program_namenchar(128)

,cmdnchar(16),cpuint,physical_ioint,blockedsmallint,dbidsmallint,loginamenchar(128),last_batchdatetime,SQLBuffernvarchar(4000),BlockedSQLBuffernvarchar(4000))--保存DBCC InputBuffer 的结果createtable#Temp1(idintidentity(1,1),eventtypevarchar(20),parametersint,eventinfonvarchar(4000))createtable#Temp_b(idintidentity(1,1),eventtypevarchar(20),parametersint,eventinfonvarchar(4000))select*into#Temp2frommaster..sysprocesses (nolock)--保存被阻塞的进程信息insertinto#Temp(spid ,status ,hostname ,program_name ,cmd ,cpu ,physical_io ,blocked ,dbid

,loginame ,last_batch )SELECTspid ,status ,hostname ,program_name ,cmd ,cpu ,physical_io ,blocked ,dbid

,convert(sysname,rtrim(loginame)) ,last_batchfrom#Temp2whereblocked>0--保存阻塞的源头insertinto#Temp(spid ,status ,hostname ,program_name ,cmd ,cpu ,physical_io ,blocked ,dbid

,loginame ,last_batch )SELECTspid ,status ,hostname ,program_name ,cmd ,cpu ,physical_io ,blocked ,dbid

,convert(sysname,rtrim(loginame)) ,last_batchfrom#Temp2wherespidin(selectblockedfrom#Temp)andspidnotin(selectspidfrom#Temp)select@spid=min(spid)from#Tempwhile@spidisnotnullbeginset@c_SQL='dbcc inputbuffer('+convert(varchar(5),@spid)+')'select@blocked=isnull(blocked,0)from#Tempwherespid=@spidif(@blocked<>0)beginset@b_SQL='dbcc inputbuffer('+convert(varchar(5),@blocked)+')'insertinto#Temp_bexec(@b_SQL)select@id=@@identityupdate#TempsetBlockedSQLBuffer=#Temp_b.eventinfofrom#Temp,#Temp_bwhere#Temp_b.id=@idand#Temp.blocked=@blockedendinsertinto#Temp1exec(@c_SQL)select@id=@@identityupdate#TempsetSQLBuffer=#Temp1.eventinfofrom#Temp,#Temp1where#Temp1.id=@idand#Temp.spid=@spidselect@spid=min(spid)from#Tempwherespid>@spidendinsertintodc_block_info(batch_id,spid,status,SQLBuffer,hostname,BlkBy,BlockedSQLBuffer,LoginName,DBName,

CPUTime,DiskIO,LastBatch,program_name,Command)SELECT@batch_id,convert(char(5),spid) SPID,CASElower(status)When'sleeping'Thenlower(status)Elseupper(status)ENDStatus

,SQLBuffer

,CASEhostnameWhenNullThen'.'When''Then'.'ElsehostnameENDHostName

,CASEisnull(convert(char(5),blocked),'0')When'0'Then'.'Elseisnull(convert(char(5),blocked),'0')ENDBlkBy,BlockedSQLBuffer

,loginame Login

,db_name(dbid) DBName,convert(varchar,cpu) CPUTime

,convert(varchar,physical_io) DiskIO,Last_Batch LastBatch

,program_name ProgramName, cmd Commandfrom#TemporderbyBlkBy, spidsetnocountoffendGoUSE[master]GO--预警SPCREATEproc[dbo].[RecodeAndAlertInfo]asbegindeclare@banch_idintselect@banch_id=isnull(MAX(batch_id),0)+1fromdc_info_SessionConn--记录当前所有会话信息insertintodc_info_SessionConnSELECT@banch_id,S.session_id, R.blocking_session_id,

current_execute_sql=SUBSTRING(T.text,

R.statement_start_offset/2+1,CASEWHENstatement_end_offset=-1THENLEN(T.text)ELSE(R.statement_end_offset-statement_start_offset)/2+1END),

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 SLEFTJOINsys.dm_exec_connections CONS.session_id=C.session_idLEFTJOINsys.dm_exec_requests RONS.session_id=R.session_idANDC.connection_id=R.connection_idOUTERAPPLY sys.dm_exec_sql_text(R.sql_handle) TWHERES.is_user_process=1--如果不限制此条件，则查询所有进程(系统和用户进程)ANDcommandisnotnull--记录当前阻塞信息insertintodc_info_BlockedInfoselect@banch_id,t1.resource_typeas[lock type],db_name(resource_database_id)as[database],t1.resource_associated_entity_idas[blk object],t1.request_modeas[lock req]--lock requested,t1.request_session_idas[waiter sid]--spid of waiter,t2.wait_duration_msas[wait time],(selecttextfromsys.dm_exec_requestsasr--- get sql for waitercrossapply 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))*2elser.statement_end_offsetend-r.statement_start_offset)/2)fromsys.dm_exec_requestsasrcrossapply sys.dm_exec_sql_text(r.sql_handle)asqtwherer.session_id=t1.request_session_id)aswaiter_stmt--- statement executing now,t2.blocking_session_idas[blocker sid]--- spid of blocker,(selecttextfromsys.sysprocessesasp--- get sql for blockercrossapply sys.dm_exec_sql_text(p.sql_handle)wherep.spid=t2.blocking_session_id)asblocker_stmt,getdate()fromsys.dm_tran_locksast1, sys.dm_os_waiting_tasksast2wheret1.lock_owner_address=t2.resource_address--记录资源信息insertintodc_Blocked_Resource_Infoselectconvert(smallint, req_spid)Asspid,

rsc_dbidAsdbid,

rsc_objidAsObjId,

rsc_indidAsIndId,substring(v.name,1,4)AsType,substring(rsc_text,1,32)asResource,substring(u.name,1,8)AsMode,substring(x.name,1,5)AsStatus ,@banch_idfrommaster.dbo.syslockinfo,

master.dbo.spt_values v,

master.dbo.spt_values x,

master.dbo.spt_values uwheremaster.dbo.syslockinfo.rsc_type=v.numberandv.type='LR'andmaster.dbo.syslockinfo.req_status=x.numberandx.type='LS'andmaster.dbo.syslockinfo.req_mode+1=u.numberandu.type='L'andsubstring(x.name,1,5)='WAIT'orderbyspidexecusp_blocker_info@banch_idendGO