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
Useful Stored Procedures
Attached is the list of useful stored procedures.
| SP | USAGE | Description |
| sp__monbackup | sp__monbackup | Shows information about database backups from the mda tables. This lists the last time each database was backed up, whether the backup failed, and if the backup is running. |
| sp__moncache | sp__moncache @num_sec_delay, @num_iter, @dont_format - @num_sec_delay : int : seconds delay between output runs - @num_iter : int : number of iterations - @dont_format : char(1): no output formating if its not null | This procedure uses vmstat/iostat format - you can run it either once or in a loop (@num_iter times with @num_sec_delay seconds between runs). |
| sp__mondump | sp__mondump | MDA table dump. This dumps the current server state - an composite report of everything going on at the current time. The purpose of this procedure is for post-mortems of problems on production systems where you do not have time to actually figure out why there is a problem because you must fix that problem immediately. Run sp__mondump, fix the problem, and then look at the output later to diagnose |
| sp__monengine | sp__monengine @num_sec_delay, @num_iter, @dont_format - @num_sec_delay : int : seconds delay between output runs - @num_iter : int : number of iterations - @dont_format : char(1): no output formating if its not null | Run once, the proc does NOT show a breakdown of %usage and %idle because these numbers are only available since server start - which of course is completely useless. This procedure uses vmstat/iostat format - you can run it either once or in a loop (@num_iter times with @num_sec_delay seconds between runs). |
| sp__monio | sp__monio @num_sec_delay, @num_iter, @dont_format - @num_sec_delay : int : seconds delay between output runs - @num_iter : int : number of iterations - @dont_format : char(1): no output formating if its not null | I/O usage by device |
| sp__monlock | sp__monlock @num_sec_delay, @num_iter, @dont_format - @num_sec_delay : int : seconds delay between output runs - @num_iter : int : number of iterations - @dont_format : char(1): no output formating if its not null | Improved version of sp__lock with additional lockstate information. While that, in and of itself is not that useful, Watching the change in locks can be useful. |
| sp__monlocksql | sp__monlocksql | Show information about locks AND the sql that causes them. This can be rather useful, although you need to run this from some form of windowed isql or it will scroll off and be totally unusable. |
| sp__monnet | sp__monnet @num_sec_delay, @num_iter, @dont_format - @num_sec_delay : int : seconds delay between output runs - @num_iter : int : number of iterations - @dont_format : char(1): no output formating if its not null | Shows Network IO Usage From The Mda tables |
| sp__monobj | sp__monobj @num_sec_delay, @num_iter, @dont_format, @object_name - @num_sec_delay : int : seconds delay between output runs - @num_iter : int : number of iterations - @dont_format : char(1): no output formating if its not null | Shows Highest Usage Objects Based On Mda tables. This uses a heuristic algorithm to find what objects are most busy. LogicalReads/100 + PhysicalReads/10 + PhysicalWrites + RowsInserted + RowsDeleted + RowsUpdated + LockRequests + Lockwaits |
| sp__monopenobj | sp__monopenobj @num_sec_delay, @num_iter, @dont_format, @object_name - @num_sec_delay : int : seconds delay between output runs - @num_iter : int : number of iterations - @dont_format : char(1): no output formating if its not null | Shows IO on open objects From The Mda tables |
| sp__monpwaits | sp__monpwaits @SPID, @num_sec_delay, @num_iter, @dont_format - @SPID : int : Spid of interest - @num_sec_delay : int : seconds delay between output runs - @num_iter : int : number of iterations - @dont_format : char(1): no output formating if its not null | shows wait information |
| sp__monrunning | sp__monrunning | show processes running > 100ms duration |
| sp__monspid | sp__monspid @spid | show everything about a running spid . Can be used to investigate locks and blocks and slow running procs etc |
| sp__monsql | sp__monsql @spid, @dont_format | Shows currently executeing sql statements if no spid is passed and shows all sql history if spid is passed |
| sp__montableusage | sp__montableusage | Shows comprehensive information about how tables are used in the db |
| sp__montopn | sp__montopn @spid | Top processes in use ordered by cputime |
| sp__monunusedindex | sp__monunusedindex @dont_format, @no_print | show indexes that have not been used since server start |
| sp__monusedtables | sp__monusedtables @owner, @objname | show io on used tables |
| sp__monwaits | sp__monwaits @num_sec_delay, @num_iter, @dont_format | shows wait info. This procedure uses vmstat/iostat format - you can run it either once or in a loop (@num_iter times with @num_sec_delay seconds between runs). |
Source Code of SPs:-
These sps are emailed to rediffmail account.
APPENDIX B
Useful Links
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 !