1 / 20

ASE 129: The MDA tables - new monitoring capabilities in ASE 12.5.0.3

ASE 129: The MDA tables - new monitoring capabilities in ASE 12.5.0.3. Rob Verschoor DBA/Consultant rob@sypron.nl. MDA tables: new monitoring feature in 12.5.0.3+. About myself:. Consultant for ASE & Replication Server Worked with ASE since 1989 From Europe (The Netherlands)

yered
Download Presentation

ASE 129: The MDA tables - new monitoring capabilities in ASE 12.5.0.3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ASE 129: The MDA tables - new monitoring capabilities in ASE 12.5.0.3 Rob VerschoorDBA/Consultantrob@sypron.nl

  2. MDA tables: new monitoring feature in 12.5.0.3+ About myself: • Consultant for ASE & Replication Server • Worked with ASE since 1989 • From Europe (The Netherlands) • See www.sypron.nl

  3. MDA tables: new monitoring feature in 12.5.0.3+ Overview • MDA tables: new feature in 12.5.0.3 • how it works • some applications • installation & configuration • compared with other ASE monitoring tools

  4. MDA tables: new monitoring feature in 12.5.0.3+ What is it? • MDA = "Monitoring Data Access" • 35 proxy tables in the master database • all named "mon%" (i.e. "monObjectActivity") • can be queried with regular SQL • fast access to low-level information (native RPCs)

  5. MDA tables: new monitoring feature in 12.5.0.3+ monProcessSQLText: returns currently executing SQL create existing table monProcessSQLText ( SPID smallint, KPID int, BatchID int, LineNumber int, SequenceInLine int, SQLText varchar(255) NULL) external procedure at "loopback...$monProcessSQLText" go grant select on monProcessSQLText to mon_role go

  6. MDA tables: new monitoring feature in 12.5.0.3+ Secondary Title: Arial 16 pt. • MDA provides low-level details not previously available • #logical I/Os for each process • SQL 'stacktrace' (stored proc/trigger call stack) • cache usage details per table • and much, much more...

  7. MDA tables: new monitoring feature in 12.5.0.3+ Installation • Out-of-the-box: no MDA tables present. • Installation steps: • Add a 'loopback' server alias name • Run ..../scripts/installmontables (when successful, prints no output whatsoever!) • Assign 'mon_role' to logins allowed MDA access

  8. MDA tables: new monitoring feature in 12.5.0.3+ Installation • Installation Gotchas • running installmon instead of installmontables • forgetting to create a 'loopback' server alias • running installmontables with sqsh ('$' characters are interpreted by sqsh) --> use isql instead • forgetting to assign 'mon_role' before querying MDA tables (& disconnect/reconnect) • forgetting to set the 'enable monitoring' (and lots of other configuration parameters)

  9. MDA tables: new monitoring feature in 12.5.0.3+ MDA table schema information • Practical problem: which table/column to query? • MDA tables are self-documenting • monTables : describes all MDA tables • monTableColumns : describes all columns in the MDA tables • monTableParameters : describes all 'parameter' columns in the MDA tables • sp_mda_help is an easy interface to these tables (--> www.sypron.nl/mda)

  10. MDA tables: new monitoring feature in 12.5.0.3+ Examples Measuring # logical I/Os for a process select SPID, LogicalReads, PhysicalReads, PhysicalWrites from master..monProcessActivity where SPID=18 • This table shows cumulative I/O counts (zeroed at ASE start) • Must determine the difference between two calls

  11. MDA tables: new monitoring feature in 12.5.0.3+ Examples Measuring # logical I/Os for a process • Easier interface to monProcessActivity : sp_mda_io (download from www.sypron.nl/mda) --SQL statement-- exec sp_mda_io --SQL statement-- exec sp_mda_io --SQL statement-- exec sp_mda_io

  12. MDA tables: new monitoring feature in 12.5.0.3+ Examples Measuring # logical I/Os for a process • monProcessActivity also contains info about other things • log I/O • ULC activity • pages accessed due to table scan • pages accessed due to index scan • number of #temp tables accessed

  13. MDA tables: new monitoring feature in 12.5.0.3+ Examples Number of pages in cache for each table/index select ObjectID, ObjectName, IndexID, CachedKB , ProcessesAccessing from master..monCachedObject where ObjectID > 99 ObjectID ObjectName IndexID CachedKB ProcessesAccessing ----------- ---------------- ------- -------- ------------------ 192933613 MyTable 0 96 0 32000114 spt_values 1 2 0 32000114 spt_values 0 78 0 1041800138 Customers_tb 0 4184 0 1041800138 Customers_tb 2 220 0 320934069 YourTable 0 56 0 160000570 syblicenseslog 0 4 0

  14. MDA tables: new monitoring feature in 12.5.0.3+ Examples Number of pages in cache for each table/index • How much cache does each object use at this moment • This information can be useful when considering/evaluating cache configuration

  15. MDA tables: new monitoring feature in 12.5.0.3+ Examples Most frequently accessed objects select object_name(ObjectID, DBID), IndexID, UsedCount, Operations from monOpenObjectActivity IndexID UsedCount Operations ---------------------- ------ ----------- ----------- MyTable 0 4 475 YourTable 0 2 18 syblicenseslog 0 0 15 sp_mda_help 0 0 10 monCachedObject 0 2 10 syscoordinations 2 0 0 […]

  16. MDA tables: new monitoring feature in 12.5.0.3+ Examples SQL 'stacktrace' select ContextID, DBName, ObjectName, ObjectType from master..monProcessProcedures where SPID = your_spid order by ContextID ContextID DBName ObjectName ObjectType --------- --------------- -------------- ------------------- 1 PROD_DB Trig1 trigger procedure 2 PROD_DB Proc1 stored procedure 3 OTHER_DB Proc2 stored procedure (3 rows affected)

  17. MDA tables: new monitoring feature in 12.5.0.3+ History tables • Some tables contain only current data, others keep some history monProcessSQLText --> currently executing SQL monSysSQLText --> recently executed SQL Amount of history is configurable: sp_configure 'sql text pipe max messages'

  18. MDA tables: new monitoring feature in 12.5.0.3+ • MDA's strength: huge amount of low-level details • Also its weakness: how to distill usable information from this data? • Need an application; DBXRay does this for you • Great opportunities for open-source movement

  19. MDA tables: new monitoring feature in 12.5.0.3+ MDA compared with other features • MDA tables vs. sp_sysmon • MDA provide per-object, per-session data • sp_sysmon provides cumulative data • MDA tables vs. sysprocesses/syslocks • MDA provide *much* more detail • MDA tables vs. MonServer/HistServer • MonServer/HistServer have great capabilities, but aren't easy to use (learning curve) • MDA tables can be queried with straightforward SQL statements • MDA tables vs. DBXRay • MDA is free; DBXRay is a license option • DBXRay pulls MDA details together for you

  20. MDA tables: new monitoring feature in 12.5.0.3+ Documentation • ASE docs: 'newfunc.pdf' for 12.5.0.3 • Chapter 1: 40+ pages of info about MDA • Included in 12.5.0.3 deliverable • Also available at sybooks.sybase.com/as.html ==> 'core documentation set' ==> ASE 12.5.0.x (not ASE 12.5) • www.sypron.nl/mda • examples, applications, tricks…

More Related