Sybase数据库技术，数据库恢复专家

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