Sybase ASE 统计当前执行的SQL语句的存储过程

Sybase ASE自V12.5.0.3引入了MDA表来监控Sybase数据库的性能,ASE15以来MDA的监控能力越来越强。本博客中有关于如何配置MDA表的文章:

如何配置监控表MonTables来实现ASE的监控功能

MDA中的代理表monProcessSQLText包含当前所有会话正在执行的SQL语句。如果已经配置了MDA,可以通过查询该代理表monProcessSQLText来获得正在执行的SQL语句。

在没有配置MDA监控的环境中,可以使用更加通用的命令来查询当前会话正在执行的SQL语句。dbcc sqltext命令需要sybase_ts_role角色才能执行,过程sp_showplan需要sa_role角色才能执行

下面提供一个存储过程来查询当前Sybase服务器内正在执行的所有SQL语句。思路是:获得当前使用锁的会话ID(spid),利用游标遍历得到每个spid的正在执行的SQL语句。

use sybsystemprocs
go

if exists (select 1 from sysobjects where (sysstat & 7) = 4 and name = 'sp_currentsql')
begin
  drop procedure sp_currentsql
end
go

print "Installing sp_currentsql"
go
--参数:dbid 允许为空,表示查询相应数据库内的SQL语句!
create procedure sp_currentsql @dbid int = NULL
as
declare @retvalue int,@spid int,@spidcount int
begin
  /*
  ** Must have sa_role to run these commands
  */
  if (proc_role("sa_role") < 1 or proc_role("sybase_ts_role") < 1)
    begin
      print "Must have sa_role to execute sp_showplan and sybase_ts_role role to run dbcc sqltext"
      return(1)
    end

  if @@trancount = 0
  begin
    set chained off
  end
  set transaction isolation level 1
  --set flushmessage on
  set nocount on
  create table #tblspid(spid int)

  if(@dbid is null)
    begin
      insert into #tblspid
      select distinct spid from master.dbo.syslocks where spid > 0
      union
      select distinct spid from master.dbo.syslogshold where spid > 0
    end
  else
    begin
      insert into #tblspid
      select distinct spid from master.dbo.syslocks where spid > 0 and dbid=@dbid
      union
      select distinct spid from master.dbo.syslogshold where spid > 0 and dbid=@dbid
    end

  delete #tblspid from #tblspid a,master..sysprocesses b where a.spid=b.spid and (b.hostname is null or b.suid=0)

  select @spidcount=count(*) from #tblspid where spid<>@@spid
  if(@spidcount>0)
  begin
    declare cur_spid cursor for select spid from #tblspid where spid<>@@spid
    open cur_spid
    fetch cur_spid into @spid
    dbcc traceon(3604)
    while @@sqlstatus=0
    begin
      print "============================%1!===================================================",@spid
      dbcc sqltext(@spid)
      print "-----------------------------------------------------------------------------------"
      --exec sp_showplan @spid,null,null,null
      fetch cur_spid into @spid
    end
    dbcc traceoff(3604)
    close cur_spid
    deallocate cursor cur_spid
  end
  drop table #tblspid
end
go

exec sp_procxmode 'sp_currentsql', 'anymode'
go
grant execute on sp_currentsql to public
go

下载该存储过程:sp_currentsql.sql

  • 本文链接地址:http://www.dbainfo.net/get-current-running-sql-statement.htm
  • 本文为dbainfo个人原创,请在尊重作者劳动成果的前提下进行转载;
  • 转载务必注明原始出处 : Sybase数据库技术,数据库恢复专家
  • 对《Sybase ASE 统计当前执行的SQL语句的存储过程》有何疑问或见解,请在本文下方发表;
  • 对网站还有其他问题或建议,请提交在留言板,谢谢!
  • 目前还没有任何评论.
    :wink: :twisted: :roll: :oops: :mrgreen: :lol: :idea: :evil: :cry: :arrow: :?: :-| :-x :-o :-P :-D :-? :) :( :!: 8-O 8)