1 / 68

The MDA tables - new monitoring capabilities in ASE 12.5.0.3

The MDA tables - new monitoring capabilities in ASE 12.5.0.3. EMEA Technical QuickStart 2003 Rob Verschoor Sypron B.V. 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

eadoin
Download Presentation

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. The MDA tables - new monitoring capabilities in ASE 12.5.0.3 EMEA Technical QuickStart 2003 Rob VerschoorSypron B.V.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 • based in The Netherlands • occasionally doing SPS assignments Contact: • www.sypron.nl • rob@sypron.nl

  3. About myself: Published two books about ASE: "Tips, Tricks & Recipes for Sybase ASE" "The Complete Sybase ASE Quick Reference Guide" (see www.sypron.nl for details)

  4. But first (not MDA-related at all): • Help promote ASE as the best RDBMS on Linux • Go to http://www.linuxworld.com/linux/ and vote for ASE as "best RDBMS on Linux" before 31-Dec-2003.

  5. MDA tables: new monitoring feature in 12.5.0.3+ Overview • MDA tables: new feature in 12.5.0.3 • How it works • Overview of all MDA tables • Installation & configuration • Examples & applications • Comparison with other ASE monitoring tools

  6. MDA tables: new monitoring feature in 12.5.0.3+ What is it? • MDA = 'Monitoring Data Access” (or “Monitoring and Diagnostics for ASE” or “Monitoring and Diagnostic Access or “Monitoring and Diagnostic API ”) • 35 proxy tables in the master database • all named 'mon%' (i.e. 'monObjectActivity') • can be queried with regular SQL • proxy tables mapped to native RPCs

  7. MDA tables: new monitoring feature in 12.5.0.3+ Example:monProcessSQLText -- returns currently executing SQL statements create existing table monProcessSQLText ( SPID smallint, KPID int, BatchID int, LineNumber int, SequenceInLine int, SQLText varchar(255) NULL) external procedure at 'loopback...$monProcessSQLText' go

  8. MDA tables: new monitoring feature in 12.5.0.3+ 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...

  9. MDA tables: new monitoring feature in 12.5.0.3+ MDA licensing: • no license needed • included in ASE base product • DBXRay (by BMC; built on the MDA tables) does require an additional license (ASE_XRAY)

  10. MDA tables: new monitoring feature in 12.5.0.3+ monState • some global server-level info • # days since startup • #deadlocks since startup • current # active connections

  11. MDA tables: new monitoring feature in 12.5.0.3+ monEngine • details about engines • current status • online/offline time • affinity information

  12. MDA tables: new monitoring feature in 12.5.0.3+ monOpenDatabases • details about databases • start time of most recent dump • whether dump currently running • whether dump failed

  13. MDA tables: new monitoring feature in 12.5.0.3+ monDataCache monProcedureCache • overall data about data caches / proc cache • not specific per object in the cache

  14. MDA tables: new monitoring feature in 12.5.0.3+ monCachedObject • all objects (tables, indexes) currently in the data caches • #Kbytes • #processes currently accessing the object • does not indicate which I/O buffer pools are involved

  15. MDA tables: new monitoring feature in 12.5.0.3+ monCachePool • I/O buffer pool usage details • no object-specific details

  16. MDA tables: new monitoring feature in 12.5.0.3+ monCachedProcedures • all plans for compiled objects currently in the proc cache • compilation date • size • no more dbcc procbuf !

  17. MDA tables: new monitoring feature in 12.5.0.3+ monOpenObjectActivity • all plans for compiled objects currently in the proc cache • #logical, physical I/Os (use for cache effectiveness!) • #rows inserted/deleted/updated • #times a lock on this object was not immediately granted • note: these counters are reset when the object descriptor is removed from the cache.

  18. MDA tables: new monitoring feature in 12.5.0.3+ monLocks • similar to syslocks

  19. MDA tables: new monitoring feature in 12.5.0.3+ monProcess monProcessLookup • similar to sysprocesses

  20. MDA tables: new monitoring feature in 12.5.0.3+ monProcessActivity • similar to sysprocesses, but more information about: • #logical I/Os • #physical I/Os • ULC writes/flushes

  21. MDA tables: new monitoring feature in 12.5.0.3+ monProcessObject • objects (tables, indexes) currently being accessed by each process

  22. MDA tables: new monitoring feature in 12.5.0.3+ monProcessProcedures • objects (procedures, triggers) currently being executed by each process

  23. MDA tables: new monitoring feature in 12.5.0.3+ monWaitEventInfo • things processes may wait for • very detailed: no less than 292 different reasons EventID ClassID Description 171 8 waiting for CTLIB event to complete 201 2 waiting for disk read in parallel dbcc 241 9 waiting in sort manager due to traeflag 1511 278 9 wait for killed tasks to die 301 7 waiting for a SSL read to complete

  24. MDA tables: new monitoring feature in 12.5.0.3+ monWaitClassInfo • main categories of things processes may wait for • grouped into 10 categories 0 Process is running 1 waiting to be scheduled 2 waiting for a disk read to complete 3 waiting for a disk write to complete 4 waiting to acquire the log semaphore 5 waiting to take a lock 6 waiting for memory or a buffer 7 waiting for input from the network 8 waiting to output to the network 9 waiting for internal system event 10 waiting on another thread

  25. MDA tables: new monitoring feature in 12.5.0.3+ monProcessWaits • indicates what each process has been waiting for up till now • cumulative wait times (milliseconds) for all event types monSysWaits • statistics about why processes waited for something • cumulative wait times (seconds) for all event types

  26. MDA tables: new monitoring feature in 12.5.0.3+ monIOQueue monDeviceIO • disk I/O statistics • indicates data/log/tempdb I/O • average I/O duration

  27. MDA tables: new monitoring feature in 12.5.0.3+ monProcessNetIO monNetworkIO • network I/O statistics

  28. MDA tables: new monitoring feature in 12.5.0.3+ monProcessStatement • Currently executing statements • indicates procedure ID, statement starttime, #I/Os • does not show the T-SQL text submitted by the client • wait time during execution (for locks, for example --> indicates effect of concurrency improvements) • does not indicate what type of statement is being executed (this information is available in monProcess.Command) monSysStatement • Completed, recently executed statements • pipe

  29. MDA tables: new monitoring feature in 12.5.0.3+ monProcessSQLText • T-SQL text (batch) currently executing monSysSQLText • T-SQL text (batch) that was recently executed but now completed • pipe

  30. MDA tables: new monitoring feature in 12.5.0.3+ monSysPlanText • recently generated query plan • pipe

  31. MDA tables: new monitoring feature in 12.5.0.3+ monDeadlock • recent deadlocks • find SQL text by joining with monSysSQLText • pipe

  32. MDA tables: new monitoring feature in 12.5.0.3+ monErrorLog • most recently errorlog messages • pipe

  33. MDA tables: new monitoring feature in 12.5.0.3+ monSysWorkerThread • currently active worker processes monProcessWorkerThread • parent process of worker processes

  34. 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 • sp_addserver loopback, null, @@servername • (assuming @@servername is also in the interfaces file) • Test this configuration: exec loopback…sp_who (note: 3 dots!) • Run $SYBASE/ASE-12_5/scripts/installmontables (when successful, prints no output whatsoever!) • Assign mon_role to logins allowed MDA access (incl. sa) • grant role mon_role to sa • Test this configuration: select * from master..monState

  35. MDA tables: new monitoring feature in 12.5.0.3+ Installation Set configuration parameters: sp_configure 'enable monitoring', 1 sp_configure 'sql text pipe active', 1 'sql text pipe max messages', 100 'plan text pipe active', 1 'plan text pipe max messages', 100 'statement pipe active', 1 'statement pipe max messages', 100 'errorlog pipe active', 1 'errorlog pipe max messages', 100 'deadlock pipe active', 1 'deadlock pipe max messages', 100

  36. MDA tables: new monitoring feature in 12.5.0.3+ Installation Set configuration parameters (cont’d): sp_configure 'wait event timing', 1 'process wait events', 1 'object lockwait timing', 1 'SQL batch capture', 1 'statement statistics active', 1 'per object statistics active', 1 'max SQL text monitored', 255 (static!) (pick up this list from www.sypron.nl/mda)

  37. 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)

  38. MDA tables: new monitoring feature in 12.5.0.3+ Installation Installation Gotchas • not running the 12.5.0.3/12.5.1 installmaster first (creates mon_role) • forgetting to set the 'enable monitoring' parameter (and lots of other configuration parameters)

  39. MDA tables: new monitoring feature in 12.5.0.3+ MDA table schema information Practical problem: which table/column to query? • 35 tables • 365 columns • MDA tables are self-documenting

  40. MDA tables: new monitoring feature in 12.5.0.3+ monTables monTableColumns monTableParameters • contain documentation about MDA tables and their columns • name • datatype • short description of function • identify parameters • parameters : most efficient filtering columns (‘=‘ condition)

  41. MDA tables: new monitoring feature in 12.5.0.3+ MDA table schema information • sp_mda_helpis an easy interface to these tables (download from www.sypron.nl/mda) 1> sp_mda_help cach 2> go TableName -------------------- ---------------------------------------------------------- monCachePool Provides statistics for all pools allocated for all caches monCachedObject Provides statistics for all objects and indexes that current monCachedProcedures Provides statistics about all procedures currently stored in monDataCache Provides statistics relating to data cache usage [...]

  42. MDA tables: new monitoring feature in 12.5.0.3+ MDA table schema information 1> sp_mda_help monDataCache 2> go TableName Description ---------------- ------------------------------------------------ monDataCache Provides statistics relating to data cache usage ------------------------------------------------------------------------------- CacheID int Unique identifier for the cache RelaxedReplacement int Whether the cache is using Relaxed cached replacement strategy BufferPools int The number of buffer pools within the cache CacheSearches int Cache searches directed to the cache PhysicalReads int Number of buffers read into the cache from disk LogicalReads int Number of buffers retrieved from the cache PhysicalWrites int Number of buffers written from the cache to disk Stalls int Number of 'dirty' buffer retrievals CachePartitions smallint Number of partitions currently configured for the cache CacheName varchar Name of the cache ------------------------------------------------------------------------------- CacheID int Unique identifier for the cache CacheName varchar Name of the cache

  43. MDA tables: new monitoring feature in 12.5.0.3+ MDA table schema information 1> sp_mda_help null, sql 2> go TableName ColumnName TypeName ------------------- ------------ --------- monProcessSQLText SQLText varchar monSysSQLText SQLText varchar

  44. MDA tables: new monitoring feature in 12.5.0.3+ Examples Currently executing SQL statements • Show SQL text: select SPID, SQLText from monProcessSQLText • Show I/O info, start time, procedure name etc: select * from monProcessStatement Rows in these tables disappear immediately when execution is completed

  45. MDA tables: new monitoring feature in 12.5.0.3+ Examples select max(CpuTime) from master..monProcessStatement Which process is currently using the most CPU (and what's it doing?) select ps.SPID, ps.CpuTime, pst.LineNumber, pst.SQLText from master..monProcessSQLText pst, master..monProcessStatement ps where ps.SPID = pst.SPID and ps.CpuTime = ( ) order by SPID, LineNumber Change CpuTime to LogicalReads or PhysicalReads as required

  46. 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) • Always increases; determine the difference between two calls • Can be used to identify session doing heavy queries (= many logical I/Os)

  47. MDA tables: new monitoring feature in 12.5.0.3+ sp_mda_io Measuring # logical I/Os for a process • Easier interface to monProcessActivity : sp_mda_io (download from www.sypron.nl/mda) Usage(1): determine logical I/O since previous call exec sp_mda_io --SQL statement-- exec sp_mda_io --SQL statement-- exec sp_mda_io output: [spid=24]#secs=4 #Log.Reads=1696 #Phys.Reads=49 #Phys.Writes=0

  48. MDA tables: new monitoring feature in 12.5.0.3+ sp_mda_io Measuring # logical I/Os for a process Usage(2): determine logical I/O for a statement batch exec sp_mda_io 'select count(*) from MyTable' exec sp_mda_io 'create index ix1 on MyTable(a)'

  49. MDA tables: new monitoring feature in 12.5.0.3+ sp_mda_io Notes: • MDA tables also measure logical I/O on system tables (set statistics io doesn't). This is quickly a few hundred logical I/Os. • When showplan (or other options) are enabled, the additional logical I/Os are even more.

  50. 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

More Related