INTRODUCTION TO MDA TABLES:
Starting in ASE 12.5.0.3, a new feature called 'MDA tables' is available to ASE users. These MDA tables provide access to low-level monitoring information in ASE; since the MDA tables can be accessed with regular SQL select statements.
Salient Features
– The MDA tables are proxy tables
– Defined in the master database
– Defined in installmontables script
– Consume no space
– Table definitions map to ASE RPC calls
– 39 proxy tables in ‘master’ database (35 in ASE 12.5)
– Can be accessed with regular SQL statements
– When queried, tables are created on-the-fly from memory structures
– No data is stored in master database
– The proxy tables may also be created in a different database than master
– No license needed: included in ASE base product
– Only small performance impact on ASE (<5%)
– Resource usage in ASE per table/query/entire server
– Current activity in ASE per spid/query/procedure/table
– Recent activity: recently completed statements, with the resources they required
– Provide server status information even when tempdb is full
Some examples of practically relevant information:
– Amount of memory occupied in the data cache by each table or index
– Most frequently used tables/procedures
– Top-N queries for CPU, I/O, elapsed time
– Find unused indexes
– SQL text of currently executing/recently executed statements
– Automatically kill user processes that have been idle for more than X minutes
– Provide server status information even when tempdb is full
STEPS TO SETUP MDA TABLES
For setting u MDA TABLES in ASE 12.5 sybase provides a script instmontables in $SYBASE/ASE-12_5/scripts. In ASE 15 MDA installation scripts is clubbed in instmast script. Below are the detailed steps to setup MDA Tables in ASE-12_5.
isql –Udba –S$DSQUERY –P*****
sp_configure 'enable cis', 1
go
use master
go
sp_addserver loopback, null, @@servername
go
set cis_rpc_handling on
go
exec loopback...sp_who -- note: 3 dots!
go
exit
isql –Udba –S$DSQUERY –P***** -i $SYBASE/$SYBASE_ASE/scripts/installmontables -o /optware/sybase/admin/create/utils/logs/mdainstallation_out
isql –Udba –S$DSQUERY –P*****
use master
go
grant role mon_role to dba
go
exec sp_configure "enable monitoring", 1
go
exec sp_configure "sql text pipe active", 1
go
exec sp_configure "sql text pipe max messages", 100
go
exec sp_configure "plan text pipe active", 1
go
exec sp_configure "plan text pipe max messages", 100
go
exec sp_configure "statement pipe active", 1
go
exec sp_configure "statement pipe max messages", 100
go
exec sp_configure "errorlog pipe active", 1
go
exec sp_configure "errorlog pipe max messages", 100
go
exec sp_configure "deadlock pipe active", 1
go
exec sp_configure "deadlock pipe max messages", 100
go
exec sp_configure "wait event timing", 1
go
exec sp_configure "process wait events", 1
go
exec sp_configure "object lockwait timing", 1
go
exec sp_configure "SQL batch capture", 1
go
exec sp_configure "statement statistics active", 1
go
exec sp_configure "per object statistics active", 1
go
exec sp_configure "max SQL text monitored", 2048
go
exec sp_configure "performance monitoring option", 1
go
DETERMINING SIZE OF MDA
Depends on which table you are configuring
These sp_configure parameters determine the number of rows in the historical tables
§ errorlog pipe max messages
§ plan text pipe max messages
§ sql text pipe max messages
§ statement pipe max messages
§ deadlock pipe max messages
The value of the parameter is the number of rows per engine. Correct size depends on
– Rate at which rows are written to table
– Frequency with which queries will be run against the table
Rate x Frequency = Size. E.g.: 5000/min x 5 min = 25000
For example:
§ 2 engines
§ 5000 rows per minute per engine
§ Select * from monSysStatement every 5 minutes
§ Statement pipe max messages should be greater than or equal to 25000
§ Result set size??? (50000 rows!)
Errorlog and deadlock pipes are usually much smaller than
Plan text, sql text and statement pipes
STEPS TO ARCHIVE MDA DATA
For problem analysis, it is necessary to preserve transient data from MDA Tables into regular tables. Below is the list of tables which needs to be archived regularly:
– monSysSQLText
– monSysPlanText
– monSysStatement
– monErrorLog
– monDeadLock
– monProcedureCache
– monEngine
– monSysStatement
– monSysWaits
– monProcessActivity
– monDeviceIO
– monIOQueue
For Archiving data from these tables, create new permanent tables with same structure as these MDA tables and add a column timestamp. This new column will record the MDA Table archival process start time.
STEPS TO PURGE ARCHIVED MDA DATA
There is a stored procedure mda_data_purge in mon_db, which deletes mda data more than 15 days old.
Syntax:
Use mon_db
Go
Exec mda_data_purge
Go
SQL TO ARCHIVE DATA FROM MDAs
Setup Archive Tables:
select *,getdate() timestamp into mon_db..monSysSQLText from master..monSysSQLText where 1 = 2
select *,getdate() timestamp into mon_db..monSysPlanText from master..monSysPlanText where 1 = 2
select *,getdate() timestamp into mon_db..monSysStatement from master..monSysStatement where 1 = 2
select *,getdate() timestamp into mon_db..monErrorLog from master..monErrorLog where 1 = 2
select *,getdate() timestamp into mon_db..monDeadLock from master..monDeadLock where 1 = 2
select *,getdate() timestamp into mon_db..monProcedureCache from master..monProcedureCache where 1=2
select *,getdate() timestamp into mon_db..monEngine from master..monEngine where 1 = 2
select *,getdate() timestamp into mon_db..monSysStatement from master..monSysStatement where 1 = 2
select *,getdate() timestamp into mon_db..monSysWaits from master..monSysWaits where 1 = 2
select *,getdate() timestamp into mon_db..monProcessActivity from master..monProcessActivity where 1 = 2
select *,getdate() timestamp into mon_db..monDeviceIO from master..monDeviceIO where 1 = 2
select *,getdate() timestamp into mon_db..monIOQueue from master..monIOQueue where 1 = 2
Copy data to Historical Tables:
There is a procedure mda_data_archive, which is scheduled to run in every 5 mins to archive data from Important MDA Tables. This procedure is wrapped in a UNIX executable file (/optware/sybase/admin/oper/utils/appdba/mda_data_collector) and is scheduled in crontab as follows:
05,10,15,20,25,30,35,40,45,50,55 * * * * /optware/sybase/admin/oper/utils/appdba/mda_data_collector >/dev/null 2>&1
Syntax:
Use mon_db
Go
Exec mda_data_archive
Go
USEFUL REPROTS FROM MDA TABLES
Monitoring Index Utilization
select "Database" = db_name(DBID), "Table" = object_name(ObjectID, DBID),
IndID = IndexID, UsedCount, LastUsedDate, OptSelectCount, LastOptSelectDate
from mon_db..monOpenObjectActivity
where timestamp between startdate and enddate
order by UsedCount
Monitoring Table Usage
select "Database" = db_name(DBID), "Table" = object_name(ObjectID, DBID),
IndexID, RowsInserted, RowsDeleted, RowsUpdated, LockWaits
from mon_db..monOpenObjectActivity
where timestamp between startdate and enddate
order by RowsInserted desc
Monitoring Table Scans
select "Database" = db_name(DBID), "Table" = object_name(ObjectID, DBID),
IndID = IndexID, UsedCount, LastUsedDate
from mon_db..monOpenObjectActivity
Where IndexID=0 and UsedCount >0
and timestamp between startdate and enddate
go
Identifying ‘hot’ tables
select TableName = object_name(ObjectID, DBID), IndexID, LogicalReads, PhysicalReads, Operations, LockWaits
from mon_db..monOpenObjectActivity
and timestamp between startdate and enddate
order by 1, 2
go
Analyzing Stored Procedure Performance (Elapsed Time)
select ProcName = isnull(object_name(ProcedureID, DBID), "UNKNOWN"),
DBName = isnull(db_name(DBID), "UNKNOWN"),
ElapsedTime = datediff(ms, min(StartTime), max(EndTime))
from mon_db..monSysStatement
group by SPID, DBID, ProcedureID, BatchID
having ProcedureID != 0
and timestamp between startdate and enddate
Most Frequently Used Stored Procedures
select ProcedureName = isnull(object_name(ProcedureID, DBID), "UNKNOWN"),
"Database" = db_name(DBID),
"Execs" = count(*)
from mon_db..monSysStatement
where ProcedureID != 0
group by DBID, ProcedureID
having timestamp between startdate and enddate
order by 3 desc
go
Determine Procedure Cache Hit Ratio
select Requests, Loads,
"Ratio" = convert(numeric(5,2),(100 - (100 * ((1.0 * Loads)/ Requests))))
from mon_db..monProcedureCache
where timestamp between startdate and enddate
go
Process Consuming most CPU and Plan
select ps.SPID, ps.CpuTime,
pst.LineNumber, pst.SQLText
from master..monProcessSQLText pst,
master..monProcessStatement ps
where ps.SPID = pst.SPID
and ps.CpuTime = (select max(CpuTime) from master..monProcessStatement
)
order by SPID, LineNumber
Engine Statistics
select EngineNumber, SystemCPUTime "CPU_TIME_IN_I/O", UserCPUTime "CPU_TIME_IN_USER_PROCESS", IdleCPUTime "IDLE_CPU_TIME", CPUTime "TOTAL_CPU_TIME" from mon_db..monEngine
where timestamp between startdate and enddate
Note: - –UserCPUTime will reflect bad queries with table scans in memory, Cursors, etc.
–SystemCPUTime will reflect physical and network IO
APPENDIX C
Other Useful SQLs used for performance monitoring:
CHECK BLOCKS
select spid,blocked from sysprocesses where blocked > 0
PHYSICAL_IO of blocked process
select spid,physical_io from sysprocesses where spid in (select blocked from sysprocesses where blocked > 0)
Details of processes which is blocked
select spid,suser_name(suid) Login,db_name(dbid),cmd,time_blocked from sysprocesses
where blocked > 0
order by time_blocked
select 'dbcc sqltext('+convert(varchar,spid)+')' from sysprocesses
where blocked > 0
select 'sp_showplan '+convert(varchar,spid)+',null,null,null' from sysprocesses
where blocked > 0
Details of blocked(Process which is blocking)
select spid,suser_name(suid) Login,db_name(dbid),cmd,cpu,loggedindatetime,physical_io from sysprocesses
where spid in (select blocked from sysprocesses where blocked > 0)
order by cpu desc
select spid,suser_name(suid) Login,db_name(dbid),cmd,cpu,loggedindatetime,physical_io from sysprocesses
where spid in (select blocked from sysprocesses where blocked > 0)
order by loggedindatetime
dbcc traceon(3604)
select 'dbcc sqltext('+convert(varchar,spid)+')' from sysprocesses
where spid in (select blocked from sysprocesses where blocked > 0)
select 'sp_showplan '+convert(varchar,spid)+',null,null,null' from sysprocesses
where spid in (select blocked from sysprocesses where blocked > 0)
CHECK PHYSICAL_IO OF LONG RUNNING TRANSACTIONS
select a.spid,a.physical_io,db_name(b.dbid) 'DATABASE NAME' from sysprocesses a,syslogshold b
where a.spid = b.spid
Hi,
ReplyDeleteIs it possible for you to share the scripts or point me to a location where I can access them ?
Do these work with sybase 15 and up ?
Cheers !