[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I interpret an SP_SYSMON report?

Posted on 2009-02-10
7
Medium Priority
?
2,530 Views
Last Modified: 2015-02-08
Hi,

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!

THANKS!
0
Comment
Question by:SybaseUk
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
IncisiveOne earned 1000 total points
ID: 23606657
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.
0
 
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 1000 total points
ID: 23609729
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! :)
0
 
LVL 24

Expert Comment

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

Expert Comment

by:Rajesh Neemkar
ID: 40597079
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

Regrads
Rajesh
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will be showing you how to subnet the easiest way possible for IPv4 (Internet Protocol version 4). This article does not cover IPv6. Keep in mind that subnetting requires lots of practice and time.
Tech giants such as Amazon and Google have sold Alexa and Echo to such an extent that they have become household names. And soon they are expected to be used by commoners in their homes, ordering takeout, picking out a song, answering trivia questio…
Screencast - Getting to Know the Pipeline
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

873 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