Sybase ASE 统计当前执行的SQL语句的存储过程
Sybase ASE自V12.5.0.3引入了MDA表来监控Sybase数据库的性能，ASE15以来MDA的监控能力越来越强。本博客中有关于如何配置MDA表的文章：
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