set nocount on
go

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

--exec sp_configure 'enable monitoring', 1
go




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

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


create proc sp_dba_cache
/*
# ############################################################################
# @(#) Proc    : sp_dba_cache
# @(#) Auteur  : FA
# @(#) Cree le : 20101122
# @(#) Objet   : cache usage using MDA. 
# @(#) Usage   : sp_dba_cache [ , OUTPUTTYPE=0|1|2 ]   [ , SHOWUSAGE=0|1 ]
# @(#)           OUTPUTTYPE : 0 = Summary / 1 = ObjectsStats / 2 = DetailedPerf
# @(#)           SHOWUSAGE : 0 = hide usage / 1 = show usage
# @(#) Exemples: sp_dba_cache TEST
# @(#) Depend. : 
# @(#) Version : ASE 12.5 -> 15.0.3
# ############################################################################
# Mises a jour
# ----------------------------------------------------------------------------
# ############################################################################
*/

/*
# ############################################################################
# Parameters
*/

	@outputtype 	tinyint = 0 ,
	@showusage 	tinyint = 1
as
begin

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

if @showusage=1
begin
	print ""
	print "-- Usage   : sp_dba_cache [ , OUTPUTTYPE=0|1|2 ]  [ , SHOWUSAGE=0|1 ] "
	print "-- Running : sp_dba_cache %1!, %2!",  @outputtype, @showusage
	print ""
end


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

	set nocount on

	declare @pagefactor int
	select @pagefactor=@@maxpagesize/1024


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

	select 	a.* into #monCachePool from 	master..monCachePool 	a 
	select 	d.* into #monDataCache from 	master..monDataCache 	d 
	select 	c.* into #monCachedObject from 	master..monCachedObject  c

	-- Strange thing : on tempdb cache, some incredible values counted in monCachePool.PagesTouched 
	update #monCachePool set PagesTouched=0, PagesRead=0 where PagesTouched > AllocatedKB

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

	select  'CacheName'     = convert(varchar(30),co.name),
       	 	'Type'		= convert(varchar(10),
				case 
               			when co.status & 1 = 1 then 'Default'
                		when co.status & 2 = 2 then 'Mixed'
                		when co.status & 4 = 4 then 'Log Only'
                		when co.status & 16 = 16 then 'Mixed, HK Ignore'
                		else '??'
                		end)
	into	#type
	from    master.dbo.sysconfigures co
	where 	co.parent = 19
	and  	co.config=19

	

/*
# ----------------------------------------------------------------------------
# Compute stats
*/



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

/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 0 = Summary
*/

if @outputtype=0
begin
	-- pool info
	select 	'CacheID'	= CacheID, 
		'SizeMb'	= sum( AllocatedKB  / 1024 ) ,
		'UsedMb' 	= sum( @pagefactor * PagesTouched ) / 1024 ,
		'PagesRead'	= sum( PagesRead ) ,
		'PagesTouched'	= sum( PagesTouched )
	into 	#pool0
	from 	#monCachePool
	group 	by CacheID

	-- number of objects
	select 	'CacheID'	= CacheID, 
		'CachedMb'	= sum( CachedKB / 1024 ) ,
		'Tbls'		= sum( case when IndexID=0 then 1 else 0 end ),
		'Indx'		= sum( case when IndexID=0 then 0 else 1 end ),
		'DBs'		= count( distinct DBID ) 
	into 	#cached0
	from  	#monCachedObject
	group 	by CacheID

	-- result
	select 	'Cache'		= d.CacheName,
		'Type'		= t.Type,
		'SizeMb'	= p.SizeMb, 
		'UsedMb'	= c.CachedMb,
		'UsedPct'	= convert(int,100.0*c.CachedMb/p.SizeMb),
		'Tbl#'		= c.Tbls, 
		'Idx#'		= c.Indx,
		'DB#'		= c.DBs, 
		'MissPct' 	= convert(int,100.0*PhysicalReads/LogicalReads ),
		'VolPct' 	= convert(int,100.0*PhysicalWrites/PhysicalReads ),
		'Reuse'		= case when PagesTouched = 0 then 0 else PagesRead / PagesTouched end  
	into 	#result0
	from 	#monDataCache 	d
	join 	#pool0 		p on p.CacheID=d.CacheID
	join 	#cached0 	c on c.CacheID=d.CacheID
	join 	#type 		t on t.CacheName=d.CacheName
	order by 1,2 desc

	-- formatted result
	exec sp_autoformat #result0
end

/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 1 = DB Detail
*/

if @outputtype=1
begin

	-- pool info
	select 	'CacheID'	= CacheID, 
		'CacheName'	= CacheName, 
		'SizeMb'	= sum( AllocatedKB ) / 1024 ,
		'UsedMb' 	= sum( @pagefactor * PagesTouched ) / 1024 ,
		'PagesRead'	= sum( PagesRead ) ,
		'PagesTouched'	= sum( PagesTouched )
	into 	#pool1 
	from 	#monCachePool 
	group 	by CacheID, 
		CacheName

	-- number of objects
	select 	'CacheID'	= CacheID, 
		'CachedMb'	= sum( CachedKB ) / 1024 ,
		'Tbls'		= sum( case when IndexID=0 then 1 else 0 end ),
		'Indx'		= sum( case when IndexID=0 then 0 else 1 end ),
		'DB'		= db_name(DBID)
	Into 	#cached1
	from  	#monCachedObject
	group 	by CacheID, db_name(DBID)


	-- result
	select 	'Cache'		= p.CacheName,
		'Type'		= t.Type,
		'SizeMb'	= p.SizeMb, 
		'UsedMb'	= c.CachedMb, 
		'UsedPct'	= 100*c.CachedMb/p.SizeMb ,
		'Tbl#'		= c.Tbls, 
		'Idx#'		= c.Indx,
		'DB'		= c.DB
	into 	#result1
	from 	#pool1 		p 
	join 	#cached1 	c on c.CacheID=p.CacheID
	join 	#type 		t on t.CacheName=p.CacheName
	order by 1,2 desc

	-- formatted result
	exec sp_autoformat #result1
end

/*
# ----------------------------------------------------------------------------
# OUTPUT TYPE 2 = DB Detail - Cache usage and turn over
*/

if @outputtype=2
begin
	select	'CacheName'	= p.CacheName ,
		'Pool'		= p.IOBufferSize/1024 ,
		'Type'		= t.Type,
		'SizeMb'	= p.AllocatedKB / 1024 ,
		'UsedMb' 	= @pagefactor * p.PagesTouched / 1024 ,
                'ReadMb'	= @pagefactor * p.PagesRead / 1024,
		'Reuse'		= case when p.PagesTouched = 0 then 0 else p.PagesRead / PagesTouched end,
		'PagesRead'	= p.PagesRead,
		'PagesTouched'	= p.PagesTouched,
		'Stalls'	= p.Stalls,
                MRUPct		= convert(int,case when p.PagesRead = 0 then 0 else 100.* p.BuffersToMRU/p.PagesRead end ),
                LRUPct		= convert(int,case when p.PagesRead = 0 then 0 else 100.* p.BuffersToLRU/p.PagesRead end )
	into 	#pool2
	from 	#monCachePool 	p
	join 	#type 		t on t.CacheName=p.CacheName

	-- result
	select 	p.CacheName, 
		p.Pool,
		p.Type,
		SizeMb,
		UsedMb,
		'UsedPct'	= 100*UsedMb/SizeMb,
		'Reuse'		= case when PagesTouched = 0 then 0 else PagesRead / PagesTouched end,
		Stalls,
		PagesRead,
		MRUPct,
		LRUPct
	into	#result2
	from 	#pool2 	p
	order by 1,2

	-- formatted result
	exec sp_autoformat #result2
end
   

end

go

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

grant exec on sp_dba_cache to public
go

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

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

I

