How do I interpret an SP_SYSMON report?

Posted on 2009-02-10
Last Modified: 2015-02-08

I know there is documentation available to explain sp_sysmon but I find it very difficult to interpret the data/results from the sysmon report.

Can somebody show me a sample or example of a sysmon report and explain how to interpret it? I'm a complete beginner here but I've been to the P&T courses and still struggling to understand sp_sysmon and what the results mean? How do i identify a bottleneck or a slow sluggish performance? What's the most important thing to look at? etc etc.

Any help/tips/guidance would be much appreciated and I will be indebted! Unlimited points on offer!

Question by:SybaseUk
    LVL 6

    Accepted Solution

    This is not a fair "question", it will lead into a tutorial, with the complication of several experts having several different viewpoints.  You are better off (a) posting the sysmon report [please ensure it is for a few hours duration on a busy server] and (b) asking EE to identify the bottlenecks and suggest enhancements.  If you have been on the courses and you are still struggling then we have an additional obstacle to providing you with help (we will be giving you the same information).  Unless you have formal IT education, and an understanding of the hardware; server software; and database architecture in general, it is no use trying to understand Sybase ASE in particular.

    Remember also, there are many layers of P&T, in reducing order of importance/benefit obtained:
    - Data Model (your particular db)
    - application (transaction design)
    - server resource allocation
    - server coupling to the machine (your particular machine)
    - server configuration (tweaking parms)

    That's why P&T is a hands-on exercise, after you have gained substantial experience actually improving each of the above areas.
    LVL 24

    Assisted Solution

    by:Joe Woodhouse
    Hm, it is rather a broad question. One of the Sybase P&T modules talks about sp_sysmon output but doesn't put a lot of hard numbers behind it.

    I haven't taught the P&T courses as of ASE 15.0.x, but the 12.5.x versions at least mentioned the parts of sp_sysmon relevant to each section.

    Asking "how do I interpret the output" is a bit like asking "how do I interpret an x-ray or ultrasound scan?" We kinda need to know what you were looking for and what motivated you to run sp_sysmon in the first place. I know I have a few sections I always scan first (context switches, data cache hit rates per second, device semaphore waits on devices), but the next area I look at after those totally depends on what I saw there.

    Start with context switches. Roughly speaking this is where ASE is telling you "these are the reasons I didn't get more work done."

    Note that a very good reason for not getting more work done is because there wasn't any more work to do! If the application tries to do most of the work itself and doesn't ask much of the database (or asks it very slowly) then Sybase will never show anything much in sp_sysmon.

    I will slightly disagree with my learned colleague IncisiveOne and suggest a better starting place for sp_sysmon duration might be 20-30 minutes. It uses integer counters and in a very busy server these will overflow for long durations. Pick the busiest/worst time on your busiest/worst server, then we might see something interesting. Post an output here, you'll have half a dozen of us picking it to pieces immediately! :)
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    Fine with a split, thank you, I would've suggested it otherwise.

    Expert Comment

    by:Rajesh Neemkar
    It would be great if an expert explains  sp_sysmon output.

    Example: Consider the following

    Transaction Management

    ULC Flushes to Xact Log      per sec      per xact      count      % of total
    -------------------------      ------------      ------------      ----------      ----------
    by Full ULC                              6.6               0.1      3946      4.3 %
    by End Transaction              66.3         0.8      39766      42.9 %
    by Change of Database      0.7               0.0      423              0.5 %
    by Single Log Record              62.7              0.8      37590      40.5 %
    by Unpin                              1.3              0.0      805              0.9 %
    by Other                                      17.0              0.2      10206      11.0 %

    1. Too high "by Full ULC", consider increase of the configuration "user log cache size"
    2. Too high "by Change of Database", consider moving the SP/table to same database context to avoid unnecessary transaction log flushes
    3. Too high "by Single Log Record", consider better batches/transactions, committing on every command does put a strain on transaction log

    NOTE: A healthy server would show high value for "By End Transaction"
    A healthy server would show high value on "Other Causes"
    Procedure Cache Management

    Procedure Cache
    Consider increase the "procedure Cache" if Procedure Reads from Disk is too high

    SQL Statement Cache
    Increase the SQL statement cache if the values are too high on Statements Not Cached


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
    DECT technology has become a popular standard for wireless voice communication. DECT devices are not likely to be affected by other electronic devices and signals because they operate in a separate frequency-band.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now