How do I interpret an SP_SYSMON report?


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!

Who is Participating?
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.
Joe WoodhousePrincipal ConsultantCommented:
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! :)
Joe WoodhousePrincipal ConsultantCommented:
Fine with a split, thank you, I would've suggested it otherwise.
Rajesh NeemkarSybase DBACommented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.