MDA tables

Document Sample
MDA tables Powered By Docstoc
					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
   based in The Netherlands
   occasionally doing SPS assignments

Contact:
 www.sypron.nl
 rob@sypron.nl
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)
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.
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
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
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
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...
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)
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
MDA tables: new monitoring feature in 12.5.0.3+

monEngine

   details about engines
       current status
       online/offline time
       affinity information
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
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
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
MDA tables: new monitoring feature in 12.5.0.3+

monCachePool

   I/O buffer pool usage details
       no object-specific details
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 !
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.
MDA tables: new monitoring feature in 12.5.0.3+

monLocks

   similar to syslocks
MDA tables: new monitoring feature in 12.5.0.3+

monProcess
monProcessLookup

   similar to sysprocesses
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
MDA tables: new monitoring feature in 12.5.0.3+

monProcessObject

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

monProcessProcedures

   objects (procedures, triggers) currently being executed by
    each process
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
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
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
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
MDA tables: new monitoring feature in 12.5.0.3+

monProcessNetIO
monNetworkIO

   network I/O statistics
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
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
MDA tables: new monitoring feature in 12.5.0.3+

monSysPlanText

   recently generated query plan

   pipe
MDA tables: new monitoring feature in 12.5.0.3+

monDeadlock

   recent deadlocks
       find SQL text by joining with monSysSQLText




   pipe
MDA tables: new monitoring feature in 12.5.0.3+

monErrorLog

   most recently errorlog messages



   pipe
MDA tables: new monitoring feature in 12.5.0.3+

monSysWorkerThread

   currently active worker processes




monProcessWorkerThread

   parent process of worker processes
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
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
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)
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)
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)
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
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)
MDA tables: new monitoring feature in 12.5.0.3+
MDA table schema information

   sp_mda_help is 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 curren
monCachedProcedures    Provides statistics about all procedures currently stored i
monDataCache           Provides statistics relating to data cache usage
[...]
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 s
 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 c
 CacheName          varchar   Name of the cache

 -------------------------------------------------------------------------------
 CacheID    int       Unique identifier for the cache
 CacheName varchar    Name of the cache
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
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
 MDA tables: new monitoring feature in 12.5.0.3+
 Examples
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 = (
               select max(CpuTime) from
               master..monProcessStatement
 )
 order by SPID, LineNumber


Change CpuTime to LogicalReads or PhysicalReads as
  required
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)
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
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)'
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.
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
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
MDA tables: new monitoring feature in 12.5.0.3+
Examples

Find most frequently accessed/executed tables/procedures

select object_name(ObjectID, DBID), Operations
from master..monOpenObjectActivity
where IndexID = 0
order by 2 desc

                       Operations
 --------------------- ----------
 MyTable                      475
 my_proc                      381
 YourTable                    129
 sp_mda_help                   19
 […]
MDA tables: new monitoring feature in 12.5.0.3+
Examples

Find most frequently accessed/executed tables/procedures

   Operations column doesn't increase by 1 (but more…)
MDA tables: new monitoring feature in 12.5.0.3+
Examples

Find unused indexes
select object_name(ObjectID, DBID), IndexID, OptSelectCount,
       UsedCount, Operations
from master..monOpenObjectActivity
where ObjectID > 99 and DBID > 1
order by 1,2,3

              IndexID OptSelectCount UsedCount Operations
 ------------ ------- -------------- --------- ----------
 MyTab              0            148       148        589
 MyTab              2             95        95          0
 MyTab              3              2         2          0
 MyTab              4              0         0          0
 MyTab              5              0         0          0
 YourTab            0              0         0        779
 YourTab            2              0         0          0
[…]
MDA tables: new monitoring feature in 12.5.0.3+
Examples

Find unused indexes

   OptSelectedCount: increased each time the optimizer selects this index

   UsedCount: increased each time the table is actually accessed by this index

   Unused indexes are indicated by OptSelectCount = 0 / UsedCount = 0

   This information applies only to objects currently in the metadata cache, and
    only since the last time the descriptor was initialised
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)
MDA tables: new monitoring feature in 12.5.0.3+
Examples

SQL 'stacktrace'

Possible applications:

   debug code (where is this procedure executed from?)

   allow updates to a table by one specific procedure only
MDA tables: new monitoring feature in 12.5.0.3+
Examples

Kill idle processes

   much-requested feature

   can now be implemented by using monProcessWaits

   implemented by David Wein (‘sp_idleReaper’)
MDA tables: new monitoring feature in 12.5.0.3+
Programming issues

   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'
MDA tables: new monitoring feature in 12.5.0.3+
Programming issues

Find out what the highest-I/O-consuming process is doing

select ps.SPID, ps.LogicalReads,
       pst.LineNumber, pst.SQLText
from master..monProcessSQLText pst,
     master..monProcessStatement ps
where ps.SPID = pst.SPID
  and ps.CpuTime = (
            select max(LogicalReads) from
            master..monProcessStatement )
order by SPID, LineNumber

This query may not work for various reasons...
MDA tables: new monitoring feature in 12.5.0.3+
Programming issues

Find out what highest-I/O-consuming process is doing

Better way of coding:

     select * into #ps
     from monProcessStatement

     select * into #pst
     from master..monProcessSQLText

     … query as on previous slide, but using these #tables…
MDA tables: new monitoring feature in 12.5.0.3+
Programming issues

History tables will never return the same data twice for a session

The following queries will never return the same result set:

     select * from master..monSysSQLText

     select * from master..monSysSQLText



Solution: regularly copy data into a permanent table
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

       Possibilities for combining MDA tables with the ASE job scheduler (12.5.1) ?
MDA tables: new monitoring feature in 12.5.0.3+

Notes
       When ASE is very busy (99% CPU), access to MDA tables may 'hang' until busy
        period is over (this is a CIS issue)

       Performance impact: to be determined in more detail, but seems minimal (3-5%)

       Some MDA tables are similar to the 'fake' tables in 'master', but don't have this
        problem when tempdb is full

       Counters are set 0 upon ASE reboot; currently no counter reset is possible.

       Counter columns (4-byte int) may wrap around

       Some features aren’t fully clear (like monOpenObjectActivity.Operations)

       Security concerns: be careful with giving out mon_role
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, since ASE start
        sp_sysmon provides cumulative data over a limited interval
        sp_sysmon does interferes with some MDA counters (see next slide)


   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
MDA tables: new monitoring feature in 12.5.0.3+
MDA compared with other features

Interference between MDA tables and sp_sysmon

        Most MDA counters are not affected by sp_sysmon

        … but some are: they'll be reset when sp_sysmon is run. This query shows
         which ones:


          select TableName, ColumnName
          from monTableColumns
          where Indicators & 2=2

        Also (not displayed by the above query):
          monIOQueue.IOs
          monIOQueue.IOTime
MDA tables: new monitoring feature in 12.5.0.3+
Documentation

   12.5.1: Performance & Tuning Guide; ‘Monitoring &
    Analyzing’ volume

   12.5.0.3: 'newfunc.pdf' for 12.5.0.3

   www.sypron.nl/mda
        examples, applications, tricks…
MDA tables: new monitoring feature in 12.5.0.3+




                    That’s it !




          Have fun with the MDA tables!

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:9
posted:3/23/2012
language:
pages:68