set nocount on
go

-- ------------------------------------------------------------------
-- Enable capture

use master
go
--exec sp_configure 'enable monitoring', 1  
--exec sp_configure 'per object statistics active', 1
--exec sp_configure 'wait event timing', 1  
go 

-- ------------------------------------------------------------------
-- Prepare temporary objects

-- first in model first to be available when server restarts

use model
go 

if object_id('dba_mon_monProcessActivity') is not null 
	drop table dba_mon_monProcessActivity
go
 CREATE TABLE dba_mon_monProcessActivity
 (                                                                     
	DT		time not null,
  SPID             smallint                     NOT NULL,              
  KPID             int                        NOT NULL,                
  ServerUserID     int                        NOT NULL,                
  CPUTime          int                        NOT NULL,                
  WaitTime         int                        NOT NULL,                
  PhysicalReads    int                        NOT NULL,                
  LogicalReads     int                        NOT NULL,                
  PagesRead        int                        NOT NULL,                
  PhysicalWrites   int                        NOT NULL,                
  PagesWritten     int                        NOT NULL,                
  MemUsageKB       int                        NOT NULL,                
  LocksHeld        int                        NOT NULL,                
  TableAccesses    int                        NOT NULL,                
  IndexAccesses    int                        NOT NULL,                
  TempDbObjects    int                        NOT NULL,                
  WorkTables       int                        NOT NULL,                
  ULCBytesWritten  int                        NOT NULL,                
  ULCFlushes       int                        NOT NULL,                
  ULCFlushFull     int                        NOT NULL,                
  ULCMaxUsage      int                        NOT NULL,                
  ULCCurrentUsage  int                        NOT NULL,                
  Transactions     int                        NOT NULL,                
  Commits          int                        NOT NULL,                
  Rollbacks        int                        NOT NULL                 
 )                                                                     

go
create index idx1 on dba_mon_monProcessActivity( SPID ) 
go


-- then in tempdb

use tempdb
go 
if object_id('dba_mon_monProcessActivity') is not null 
	drop table dba_mon_monProcessActivity
go
select * into dba_mon_monProcessActivity from model.dbo.dba_mon_monProcessActivity
go
create index idx1 on dba_mon_monProcessActivity( SPID ) 
go


-- ------------------------------------------------------------------
-- Create proc

use sybsystemprocs
go
dump tran sybsystemprocs with truncate_only
go

if object_id('sp_dba_spid') is not null 
        drop proc sp_dba_spid
go

create proc sp_dba_spid
/*
# ############################################################################
# @(#) Proc    : sp_dba_spid
# @(#) Auteur  : FA
# @(#) Cree le : 20101013
# @(#) Objet   : spid activity using mda
# @(#) Usage   : sp_dba_spid SPID [ , [REPORTTYPE=0|1|2] , [SHOWUSAGE=0|1]
# @(#)           REPORTTYPE : 0 = physical / 1 = logical / 2 = Transactional
# @(#)           SHOWUSAGE  : 0 = hide usage / 1 = show usage
# @(#) Exemples: sp_dba_spid
# @(#)           sp_dba_spid 14
# @(#)           sp_dba_spid 14, 1
# @(#) Depend. : MDA history maintained in tempdb, structure is first created 
# @(#)           on model db to maintain the structure after instance reboot
# @(#) Version : ASE 15.0
# ############################################################################
# Mises a jour
# ----------------------------------------------------------------------------
# ############################################################################
*/

/*
# ############################################################################
# Parameters
*/
	@SPID int ,
	@outputtype tinyint = 0,
	@showusage      tinyint = 1
as
        
/*
# ############################################################################
# Environment
*/

set nocount on

/*
# ----------------------------------------------------------------------------
# Show usage
*/

if @showusage = 1
begin
        declare @SPID_str varchar(5)
        select @SPID_str=isnull(convert(varchar(5), @SPID),"NULL")

        print ""
        print "-- Usage   : sp_dba_spid [SPID] , [OUTPUTTYPE=0|1|2] , [SHOWUSAGE=0|1] "
        print "-- Running : sp_dba_spid %1! , %2! , %3!",  @SPID_str , @outputtype, @showusage
        print ""
end

/*
# ----------------------------------------------------------------------------
# Extract MDA
*/

select * into #tmp_monProcessActivity 	from master.dbo.monProcessActivity 

/*
# ----------------------------------------------------------------------------
# Store history
*/

-- contexte historique monSysSQLText   
insert tempdb.dbo.dba_mon_monProcessActivity select 'DT'=getdate(), * from #tmp_monProcessActivity


/*
# ############################################################################
# Program
*/

/*
# ----------------------------------------------------------------------------
# Extract data
*/
declare @rows int
select rownum=identity(3), *
into #tmp1
from    	tempdb.dbo.dba_mon_monProcessActivity
where 		SPID=@SPID
select @rows=@@rowcount


/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 0 = Physical activity
*/

if @outputtype = 0 
begin
	select 	'Date'		= "  " + convert(char(8),DT,108) , 
		'CPUTime'	= CPUTime, 
		'WaitTime'	= WaitTime, 
		'MemKB'		= MemUsageKB, 
		'ReadsP'	= PhysicalReads, 
		'ReadsL'	= LogicalReads, 
		'PagesR'	= PagesRead, 
		'WritesP' 	= PhysicalWrites,
		'PagesW'	= PagesWritten 
	into #result0
	from  #tmp1
if @rows > 1
begin
	insert #result0
	select 	'Date'		= "< " + convert(char(8),b.DT,108),
		'CPUTime'	= a.CPUTime-b.CPUTime,
		'WaitTime'	= a.WaitTime-b.WaitTime,
		'MemKB'		= a.MemUsageKB-b.MemUsageKB,
		'ReadsP'	= a.PhysicalReads-b.PhysicalReads,
		'ReadsL'	= a.LogicalReads-b.LogicalReads,
		'PagesR'	= a.PagesRead-b.PagesRead,
		'WritesP'	= a.PhysicalWrites-b.PhysicalWrites,
		'PagesW'	= a.PagesWritten-b.PagesWritten
	from 	#tmp1 a
	join 	#tmp1 b on b.rownum=1
	where 	a.rownum=@rows 
	union all	
	select 	'Date'		= "> " + convert(char(8),b.DT,108),
		'CPUTime'	= a.CPUTime-b.CPUTime,
		'WaitTime'	= a.WaitTime-b.WaitTime,
		'MemKB'		= a.MemUsageKB-b.MemUsageKB,
		'ReadsP'	= a.PhysicalReads-b.PhysicalReads,
		'ReadsL'	= a.LogicalReads-b.LogicalReads,
		'PagesR'	= a.PagesRead-b.PagesRead,
		'WritesP'	= a.PhysicalWrites-b.PhysicalWrites,
		'PagesW'	= a.PagesWritten-b.PagesWritten
	from 	#tmp1 a
	join 	#tmp1 b on b.rownum=a.rownum-1
	where 	a.rownum=@rows 

	exec sp_autoformat #result0
end
end


/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 1 = Logical activity
*/

if @outputtype = 1 
begin
	select 	'Date'		= "  " + convert(char(8),DT,108) , 
		'Locks'		= LocksHeld,
		'TblAccess'	= TableAccesses, 
		'IdxAccess'	= IndexAccesses, 
		'TmpObj'	= TempDbObjects, 
		'WorkTables'	= WorkTables, 
		'Trans'		= Transactions
	into	#result1
	from  #tmp1
if @rows > 1
begin
	insert #result1
	select 	'Date'		= "< " + convert(char(8),b.DT,108),
		'LocksHeld'	= a.LocksHeld-b.LocksHeld,
		'TableAccesses'	= a.TableAccesses-b.TableAccesses,
		'IndexAccesses'	= a.IndexAccesses-b.IndexAccesses,
		'TempDbObjects'	= a.TempDbObjects-b.TempDbObjects,
		'WorkTables'	= a.WorkTables-b.WorkTables,
		'Transactions'	= a.Transactions-b.Transactions
	from 	#tmp1 a
	join 	#tmp1 b on b.rownum=1
	where 	a.rownum=@rows 
	union all	
	select 	'Date'		= "> " + convert(char(8),b.DT,108),
		'LocksHeld'	= a.LocksHeld-b.LocksHeld,
		'TableAccesses'	= a.TableAccesses-b.TableAccesses,
		'IndexAccesses'	= a.IndexAccesses-b.IndexAccesses,
		'TempDbObjects'	= a.TempDbObjects-b.TempDbObjects,
		'WorkTables'	= a.WorkTables-b.WorkTables,
		'Transactions'	= a.Transactions-b.Transactions
	from 	#tmp1 a
	join 	#tmp1 b on b.rownum=a.rownum-1
	where 	a.rownum=@rows 

	exec sp_autoformat #result1
end

end

/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 2 = Transactional activity
*/

if @outputtype = 2
begin
	select 'Date'		= "  " + convert(char(8),DT,108) , 
		'Trans'		= Transactions,
		'Commits'	= Commits,
		'Rollbacks'	= Rollbacks,
		ULCBytesWritten,
		ULCFlushes,
		ULCMaxUsage,
		ULCCurrentUsage
	into 	#result2
	from  #tmp1
if @rows > 1
begin
	insert #result2
	select 	'Date'		= "< " + convert(char(8),b.DT,108),
		'Transactions'	= a.Transactions-b.Transactions,
		'Commits'	= a.Commits-b.Commits,
		'Rollbacks'	= a.Rollbacks-b.Rollbacks,
		'ULCBytesWritten' = a.ULCBytesWritten-b.ULCBytesWritten,
		'ULCFlushes'	= a.ULCFlushes-b.ULCFlushes,
		'ULCMaxUsage'	= a.ULCMaxUsage-b.ULCMaxUsage,
		'ULCCurrentUsage'= a.ULCCurrentUsage-b.ULCCurrentUsage
	from 	#tmp1 a
	join 	#tmp1 b on b.rownum=1
	where 	a.rownum=@rows 
	union all	
	select 	'Date'		= "> " + convert(char(8),b.DT,108),
		'Transactions'	= a.Transactions-b.Transactions,
		'Commits'	= a.Commits-b.Commits,
		'Rollbacks'	= a.Rollbacks-b.Rollbacks,
		'ULCBytesWritten' = a.ULCBytesWritten-b.ULCBytesWritten,
		'ULCFlushes'	= a.ULCFlushes-b.ULCFlushes,
		'ULCMaxUsage'	= a.ULCMaxUsage-b.ULCMaxUsage,
		'ULCCurrentUsage'= a.ULCCurrentUsage-b.ULCCurrentUsage
	from 	#tmp1 a
	join 	#tmp1 b on b.rownum=a.rownum-1
	where 	a.rownum=@rows 

	exec sp_autoformat #result2
end
end


/*
# ############################################################################
# Environment
*/

/*
# ----------------------------------------------------------------------------
# Limit history
*/

declare @rowcount int  
select @rowcount=count(1) from tempdb.dbo.dba_mon_monProcessActivity
if @rowcount > 100000
begin
	set rowcount 10000
	delete tempdb.dbo.dba_mon_monProcessActivity
	set rowcount 0
end

go



-- ------------------------------------------------------------------
-- Permissions

grant exec on sp_dba_whodoes to public
go

-- ------------------------------------------------------------------
-- Control

if object_id('sp_dba_spid') is not null
        print 'sp_dba_spid install OK on %1!', @@servername
else
        print 'sp_dba_spid install FAILED on %1!', @@servername
go

