set nocount on
go

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

use master
go
--exec sp_configure 'enable monitoring', 1  
go 


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

use sybsystemprocs
go
dump tran sybsystemprocs with truncate_only
go
if object_id('sp_dba_io') is not null 
        drop proc sp_dba_io
go

create proc sp_dba_io
/*
# ############################################################################
# @(#) Proc    : sp_dba_io
# @(#) Auteur  : FA
# @(#) Cree le : 20101013
# @(#) Objet   : IO activity using mda
# @(#) Usage   : sp_dba_io [ device ]  [ , [REPORTTYPE=0|1] ] [ , SHOWUSAGE=0|1 ]
# @(#)           DEVICE : device name ,wildcard managed
# @(#)           OUTPUTTYPE : 0 = summary, 1 = detailed 
# @(#)           SHOWUSAGE : 0 = hide usage / 1 = show usage
# @(#) Exemples: sp_dba_io
# @(#)           sp_dba_io data_sma_01
# @(#) Version : ASE 12.5 => 15.0
# ############################################################################
# Mises a jour
# ----------------------------------------------------------------------------
# ############################################################################
*/

/*
# ############################################################################
# Parameters
*/
	@device varchar(30) = null, 
	@reporttype tinyint = 0, 
	@showusage tinyint=1
as
begin

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

if @showusage=1
begin
	print ""
	print "-- Usage : sp_dba_io [ device ]  [ , [REPORTTYPE=0|1] ] [ , SHOWUSAGE=0|1 ]"
	print "-- Currently running : sp_dba_io %1! , %2! , %3! ", @device, @reporttype, @showusage
	print ""
end


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

	set nocount on

	if @device is null
		set @device='%'

/*
# ----------------------------------------------------------------------------
# Extract MDA and sysinfo
*/

select * into #tmp_monIOQueue 	from master.dbo.monIOQueue 
select * into #tmp_monDeviceIO 	from master.dbo.monDeviceIO 
select distinct b.name, 
	case 
	when ( a.segmap & 3 ) = 3 and ( a.segmap & 4 ) = 4 then 'D&L'
	when ( a.segmap & 3 ) = 3 then 'DATA'
	when ( a.segmap & 4 ) = 4 then 'LOG'
	else 'TBD'
	end 'TYPE'
into #tmp_usage 
from master.dbo.sysusages a 
join master.dbo.sysdevices b on b.vdevno=a.vdevno

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

/*
# ----------------------------------------------------------------------------
# Extract data
*/

        select  
                LogicalName,
                max(case IOType when 'User Data' then IOs else 0 end ) 'UD_IO',
                max(case IOType when 'User Data' then IOTime else 0 end ) 'UD_IOT',
                max(case IOType when 'User Log' then IOs else 0 end ) 'UL_IO',
                max(case IOType when 'User Log' then IOTime else 0 end ) 'UL_IOT',
                max(case IOType when 'Tempdb Data' then IOs else 0 end ) 'TD_IO',
                max(case IOType when 'Tempdb Data' then IOTime else 0 end ) 'TD_IOT',
                max(case IOType when 'Tempdb Log' then IOs else 0 end ) 'TL_IO',
                max(case IOType when 'Tempdb Log' then IOTime else 0 end ) 'TL_IOT',
                max(case IOType when 'System' then IOs else 0 end ) 'SY_IO',
                max(case IOType when 'System' then IOTime else 0 end ) 'SY_IOT',
                max(convert(int,0)) 'TOT_IO',
                max(convert(int,0)) 'TOT_IOT',
		max(convert(varchar(5),null)) 'TYPE'
        into 	#data
        from 	#tmp_monIOQueue 
        group by LogicalName

	update #data set TYPE=b.TYPE from #data a join #tmp_usage b on b.name=a.LogicalName

	update 	#data 
	set 	TOT_IO=	case TYPE
			when "DATA" 	then UD_IO + TD_IO 
			when "LOG" 	then UL_IO + TL_IO
			when "D&L" 	then UD_IO + UL_IO + TD_IO + TL_IO
			else TOT_IO
			end, 
		TOT_IOT=case TYPE
			when "DATA" 	then UD_IOT + TD_IOT
			when "LOG" 	then UL_IOT + TL_IOT
			when "D&L" 	then UD_IOT + UL_IOT + TD_IOT + TL_IOT
			else TOT_IO
			end

	
	declare @totio numeric(18), @totiot numeric(18) 
	select @totio=sum(TOT_IO*1.0),  @totiot=sum(TOT_IOT*1.0)+1 from #data
	declare @totread unsigned bigint, @totwrite unsigned bigint
	select @totread=sum(Reads)+1, @totwrite=sum(Writes)+1 from #tmp_monDeviceIO
/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 0 = Physical activity
*/

if @reporttype = 0
begin
	select 	'Device'= a.LogicalName, 
		'Type'	= a.TYPE, 
		'IO'	= a.TOT_IO , 
		'IOpc'	= convert(int , 100.0*a.TOT_IO/@totio)  ,
		'IOT'	= a.TOT_IOT, 
		'IOTpc'	= convert(int , 100.0*a.TOT_IOT/@totiot) ,
		'ms_IO'	= convert(int,1.*a.TOT_IOT/case a.TOT_IO when 0 then 1 else a.TOT_IO end )
	into 	#result0
	from 	#data a
	where 	a.LogicalName like @device
	order by TOT_IO desc

	exec sp_autoformat #result0
end 

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

if @reporttype = 1
begin
	select 	'Device'= a.LogicalName, 
		'Type'	= a.TYPE, 
		'IO'	= a.TOT_IO , 
		'IOpc'	= convert(int , 100.0*a.TOT_IO/@totio)  ,
		'ms_IO'	= convert(int,1.*a.TOT_IOT/case a.TOT_IO when 0 then 1 else a.TOT_IO end ),
		'Reads'	= b.Reads, 
		'Rpc'	= convert(int , 100.0*b.Reads/@totread) ,
		'ReadsAPF'= b.APFReads, 
		'Writes'= b.Writes,
		'Wpct'	= convert(int , 100.0*b.Writes/@totwrite) ,
		'R_Wpc'	= convert(varchar(6),
			convert(varchar(4),convert(int , 100.0*Reads/(Reads+Writes))) +'/' +
			convert(varchar(4),convert(int , 100.0*Writes/(Reads+Writes))) 
		) 
	into 	#result1
	from 	#data a
	join 	#tmp_monDeviceIO b on b.LogicalName=a.LogicalName
	where 	a.LogicalName like @device
	order by TOT_IO desc
	
	exec sp_autoformat #result1
end 


end
go


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


grant exec on sp_dba_io to public
go


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

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

