Hello,

I required help to investigate the root cause of the SYBASE CPU TIME.
It cause my application receive alarm 2 hours delay.

The following is my system detail:
1. SQL Server/11.0.3.2/P/HP9000-800/HP-UX 10.0/SWR 7569
2. Glance output show that my database engine CPU varies from 90 -
100% (red)

The following is the detail of the SYBASE:

1. SP_SYSMON result:

DBCC execution completed. If DBCC printed error messages, contact a
user with
System Administrator (SA) role.
================================================== =============================
Sybase SQL Server System Performance Report
================================================== =============================

Run Date Jun 30, 2003
Statistics Cleared at 14:52:10
Statistics Sampled at 14:53:10
Sample Interval 1 min.

================================================== =============================

Kernel Utilization
------------------

Engine Busy Utilization:
Engine 0 95.5 %
Engine 1 3.7 %
----------- --------------- ----------------
Summary: Total: 99.2 % Average: 49.6 %


CPU Yields by Engine per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Engine 0 57.3 41.9 3437
53.6 %
Engine 1 49.7 36.4 2981
46.4 %
------------------------- ------------ ------------ ----------
Total CPU Yields: 107.0 78.3 6418


Network Checks
Non-Blocking 4.2 3.1 253
100.0 %
Blocking 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total Network I/O Checks: 4.2 3.1 253
Avg Net I/Os per Check n/a n/a 0.60870
n/a


Disk I/O Checks
Total Disk I/O Checks 264015.7 193159.7 15839096
n/a
Checks Returning I/O 482.5 353.0 28945
0.2 %
Avg Disk I/Os Returned n/a n/a 1.00100
n/a

================================================== =============================

Task Management per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------

Connections Opened 0.1 0.1 5
n/a


Task Context Switches by Engine
Engine 0 466.8 341.5 28004
94.9 %
Engine 1 24.9 18.2 1495
5.1 %
------------------------- ------------ ------------ ----------
Total Task Switches: 491.7 359.7 29499


Task Context Switches Due To:
Voluntary Yields 1.6 1.2 97
0.3 %
Cache Search Misses 480.4 351.4 28818
97.7 %
System Disk Writes 0.2 0.1 10
0.0 %
I/O Pacing 0.6 0.5 38
0.1 %
Logical Lock Contention 0.0 0.0 0
0.0 %
Address Lock Contention 0.0 0.0 0
0.0 %
Log Semaphore Contention 0.0 0.0 0
0.0 %
Group Commit Sleeps 0.0 0.0 0
0.0 %
Last Log Page Writes 1.5 1.1 92
0.3 %
Modify Conflicts 0.0 0.0 0
0.0 %
I/O Device Contention 0.0 0.0 0
0.0 %
Network Packet Received 1.3 1.0 80
0.3 %
Network Packet Sent 1.2 0.9 74
0.3 %
SYSINDEXES Lookup 0.0 0.0 0
0.0 %
Other Causes 4.8 3.5 290
1.0 %

================================================== =============================

Transaction Profile
-------------------

Transaction Summary per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Committed Xacts 1.4 n/a 82
n/a


Transaction Detail per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Inserts
Heap Table 0.9 0.7 54
36.0 %
Clustered Table 1.6 1.2 96
64.0 %
------------------------- ------------ ------------ ----------
Total Rows Inserted 2.5 1.8 150
59.5 %

Updates
Deferred 0.0 0.0 0
0.0 %
Direct In-place 0.1 0.0 4
33.3 %
Direct Cheap 0.1 0.1 8
66.7 %
Direct Expensive 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total Rows Updated 0.2 0.1 12
4.8 %

Deletes
Deferred 1.2 0.9 70
77.8 %
Direct 0.3 0.2 20
22.2 %
------------------------- ------------ ------------ ----------
Total Rows Deleted 1.5 1.1 90
35.7 %

================================================== =============================

Transaction Management
----------------------

ULC Flushes to Xact Log per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
by Full ULC 0.0 0.0 0
0.0 %
by End Transaction 1.2 0.9 72
13.8 %
by Change of Database 0.0 0.0 0
0.0 %
by System Log Record 7.2 5.2 430
82.4 %
by Other 0.3 0.2 20
3.8 %
------------------------- ------------ ------------ ----------
Total ULC Flushes 8.7 6.4 522


ULC Log Records 10.7 7.8 642
n/a
Max ULC Size n/a n/a 800
n/a


ULC Semaphore Requests
Granted 20.2 14.8 1212
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total ULC Semaphore Req 20.2 14.8 1212


Log Semaphore Requests
Granted 8.7 6.3 520
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total Log Semaphore Req 8.7 6.3 520


Transaction Log Writes 2.0 1.5 120
n/a
Transaction Log Alloc 0.5 0.3 28
n/a
Avg # Writes per Log Page n/a n/a 4.28571
n/a

================================================== =============================

Index Management
----------------

Nonclustered Maintenance per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Ins/Upd Requiring Maint 0.6 0.4 36
n/a
# of NC Ndx Maint 0.9 0.6 52
n/a
Avg NC Ndx Maint / Op n/a n/a 1.44444
n/a

Deletes Requiring Maint 0.5 0.3 28
n/a
# of NC Ndx Maint 0.5 0.3 28
n/a
Avg NC Ndx Maint / Op n/a n/a 1.00000
n/a

RID Upd from Clust Split 0.0 0.0 0
n/a
# of NC Ndx Maint 0.0 0.0 0
n/a


Page Splits 0.0 0.0 0
n/a


Page Shrinks 0.5 0.4 30
n/a

================================================== =============================

Lock Management
---------------

Lock Summary per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total Lock Requests 108.2 79.2 6493
n/a
Avg Lock Contention 0.0 0.0 0
0.0 %
Deadlock Percentage 0.0 0.0 0
0.0 %


Lock Detail per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------

Exclusive Table
Granted 4.7 3.4 280
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total EX-Table Requests 4.7 3.4 280
4.3 %


Shared Table
Granted 0.3 0.2 20
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total SH-Table Requests 0.3 0.2 20
0.3 %


Exclusive Intent
Granted 0.9 0.6 52
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total EX-Intent Requests 0.9 0.6 52
0.8 %


Shared Intent
Granted 3.4 2.5 202
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total SH-Intent Requests 3.4 2.5 202
3.1 %


Exclusive Page
Granted 3.4 2.5 204
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total EX-Page Requests 3.4 2.5 204
3.1 %


Update Page
Granted 2.4 1.8 144
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total UP-Page Requests 2.4 1.8 144
2.2 %


Shared Page
Granted 47.8 35.0 2868
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total SH-Page Requests 47.8 35.0 2868
44.2 %


Exclusive Address
Granted 16.4 12.0 986
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total EX-Address Requests 16.4 12.0 986
15.2 %


Shared Address
Granted 29.0 21.2 1737
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total SH-Address Requests 29.0 21.2 1737
26.8 %


Last Page Locks on Heaps
Granted 0.9 0.7 54
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total Last Pg Locks 0.9 0.7 54
0.8 %


Deadlocks by Lock Type per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a


Deadlock Detection
Deadlock Searches 0.0 0.0 0
n/a


Lock Promotions
0.0 0.0 0
n/a

================================================== =============================

Data Cache Management
---------------------

Cache Statistics Summary (All Caches)
-------------------------------------

Cache Search Summary
Total Cache Hits 111.8 81.8 6706
18.8 %
Total Cache Misses 482.5 353.0 28946
81.2 %
------------------------- ------------ ------------ ----------
Total Cache Searches 594.3 434.8 35652


Cache Turnover
Buffers Grabbed 480.4 351.4 28818
n/a
Buffers Grabbed Dirty 0.0 0.0 0
0.0 %


Cache Strategy Summary
Cached (LRU) Buffers 616.0 450.7 36957
100.0 %
Discarded (MRU) Buffers 0.0 0.0 0
0.0 %


Large I/O Usage
0.0 0.0 0
n/a


Large I/O Effectiveness
Pages by Lrg I/O Cached 0.0 0.0 0
n/a


Dirty Read Behavior
Page Requests 0.0 0.0 0
n/a

-------------------------------------------------------------------------------

default data cache
per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------

Spinlock Contention n/a n/a n/a
0.0 %
Utilization n/a n/a n/a
100.0 %

Cache Searches
Cache Hits 111.8 81.8 6706
18.8 %
Found in Wash 0.0 0.0 0
0.0 %
Cache Misses 482.5 353.0 28946
81.2 %
------------------------- ------------ ------------ ----------
Total Cache Searches 594.3 434.8 35652


Pool Turnover
2 Kb Pool
LRU Buffer Grab 480.4 351.4 28818
100.0 %
Grabbed Dirty 0.0 0.0 0
0.0 %



------------------------- ------------ ------------ ----------
Total Cache Turnover 480.4 351.4 28818


Buffer Wash Behavior
Buffers Passed Clean 480.5 351.6 28829
99.9 %
Buffers Already in I/O 0.0 0.0 0
0.0 %
Buffers Washed Dirty 0.3 0.2 16
0.1 %


Cache Strategy
Cached (LRU) Buffers 616.0 450.7 36957
100.0 %
Discarded (MRU) Buffers 0.0 0.0 0
0.0 %


Large I/O Usage
0.0 0.0 0
n/a


Large I/O Detail
No Large Pool(s) In This Cache

Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a

================================================== =============================

Procedure Cache Management per sec per xact count %
of total
--------------------------- ------------ ------------ ----------
----------
Procedure Requests 0.3 0.2 19
n/a
Procedure Reads from Disk 0.0 0.0 0
0.0 %
Procedure Writes to Disk 0.0 0.0 0
0.0 %
Procedure Removals 0.0 0.0 0
n/a

================================================== =============================

Memory Management per sec per xact count %
of total
--------------------------- ------------ ------------ ----------
----------
Pages Allocated 0.0 0.0 0
n/a
Pages Released 0.0 0.0 0
n/a

================================================== =============================

Recovery Management
-------------------

Checkpoints per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a

================================================== =============================

Disk I/O Management
-------------------

Max Outstanding I/Os per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Server n/a n/a 5
n/a
Engine 0 n/a n/a 5
n/a
Engine 1 n/a n/a 2
n/a


I/Os Delayed by
Disk I/O Structures n/a n/a 0
n/a
Server Config Limit n/a n/a 0
n/a
Engine Config Limit n/a n/a 0
n/a
Operating System Limit n/a n/a 0
n/a


Total Requested Disk I/Os 483.0 353.3 28974
n/a

Completed Disk I/O's
Engine 0 458.7 335.6 27517
95.0 %
Engine 1 24.3 17.8 1457
5.0 %
------------------------- ------------ ------------ ----------
----------
Total Completed I/Os 483.0 353.3 28974


Device Activity Detail
----------------------

/var/sybase/rdsk/.sysprocsdev.BROMC1
sysprocsdev per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %


---------------------------------------------------------------------------

/var/sybase/rdsk/rdatadev1
datadev1 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 479.8 351.0 28782
99.9 %
Writes 0.3 0.2 16
0.1 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 480.0 351.2 28798
99.4 %

Device Semaphore Granted 480.0 351.2 28798
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %

---------------------------------------------------------------------------

/var/sybase/rdsk/rdatadev2
datadev2 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %


---------------------------------------------------------------------------

/var/sybase/rdsk/rdatadev3
datadev3 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %


---------------------------------------------------------------------------

/var/sybase/rdsk/rlogdev1
logdev1 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.0 0.0 1
5.6 %
Writes 0.3 0.2 17
94.4 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.3 0.2 18
0.1 %

Device Semaphore Granted 0.3 0.2 18
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %

---------------------------------------------------------------------------

/var/sybase/rdsk/rlogdev2
logdev2 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %


---------------------------------------------------------------------------

/var/sybase/rdsk/rlogdev3
logdev3 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %


---------------------------------------------------------------------------

/var/sybase/rdsk/rtempvol
tempdev per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %


---------------------------------------------------------------------------

d_master
master per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.6 0.4 35
22.2 %
Writes 2.1 1.5 123
77.8 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 2.6 1.9 158
0.5 %

Device Semaphore Granted 2.6 1.9 158
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %

---------------------------------------------------------------------------

================================================== =============================

Network I/O Management
----------------------

Total Network I/O Requests 2.6 1.9 155
n/a
Network I/Os Delayed 0.0 0.0 0
0.0 %


Total TDS Packets Received per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Engine 0 1.2 0.9 74
92.5 %
Engine 1 0.1 0.1 6
7.5 %
------------------------- ------------ ------------ ----------
----------
Total TDS Packets Rec'd 1.3 1.0 80


Total Bytes Received per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Engine 0 169.6 124.1 10176
92.6 %
Engine 1 13.5 9.9 810
7.4 %
------------------------- ------------ ------------ ----------
----------
Total Bytes Rec'd 183.1 134.0 10986


Avg Bytes Rec'd per Packet n/a n/a 137
n/a

-----------------------------------------------------------------------------

Total TDS Packets Sent per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Engine 0 1.1 0.8 68
91.9 %
Engine 1 0.1 0.1 6
8.1 %
------------------------- ------------ ------------ ----------
----------
Total TDS Packets Sent 1.2 0.9 74


Total Bytes Sent per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Engine 0 127.0 92.9 7619
90.4 %
Engine 1 13.4 9.8 806
9.6 %
------------------------- ------------ ------------ ----------
----------
Total Bytes Sent 140.4 102.7 8425


Avg Bytes Sent per Packet n/a n/a 113
n/a

=============================== End of Report
=================================
(return status = 0)



In sp_who, I found 1 process in running state as follow:

71 running sa s09brf01 0
master SELECT
72 lock sleep sa 9
fmadb_1_1 AWAITING COMMAND

In sp_lock, I found the following info:

The class column will display the cursor name for locks associated
with a cursor
for the current user and the cursor id for other users.
spid locktype table_id page dbname
class
------ ---------------------------- ----------- -----------
---------------
------------------------------
9 Ex_table 80003316 0 fmadb_1_1
Non Cursor Lock
9 Ex_page 80003316 1040 fmadb_1_1
Non Cursor Lock
(2 rows affected, return status = 0)

Would really appreciate if someone can help me to narrow down the
possibilties cause of the problem as I do not know how to start the
troubleshooting.

Best Regards,
Munawwar

ahmad_munawwarr (AT) hotmail (DOT) com (munawwar) wrote in message news:<ed3fa9e7.0306301733.4f5c4c69 (AT) posting (DOT) google.com>...

My guess is that your application is accessing table using a
tablescan. This are my suggestions
- run several sysmon using 3 min interval instead of 1 min (This
should give you a more meaningfull stats to work with).
- If you see the same high CPU utilization pattern, run sp_lock in a
script every 3 secs for 5 mins and direct the output to a file. If you
see a lot of locks, it's time to narrow down on the query that is
scanning tables and make sure their is apprioprate indexing on the
table.


Good luck

munawwar wrote:

I didn't have a chance to go through your sysmon in detail, but
I did take a quick glance at it. I don't know if this is a
bottleneck that's contributing to your problem or if it's
unrelated, but this really sticks out. It could just be due
to your low throughput. But here's mine for the same interval
(whitespace removed to try to prevent line wrap):

Cache Statistics Summary (All Caches)
-------------------------------------
per sec per xact count % of total
------- -------- ------ ----------
Cache Search Summary
Total Cache Hits 12160.0 1993.4 729598 99.8 %
Total Cache Misses 25.0 4.1 1497 0.2 %
--------------------- ------- -------- ------
Total Cache Searches 12184.9 1997.5 731095


So you're getting 18.8% cache hits, and I'm getting 99.8%...


Larry Coon
University of California

Hi,

Lastly, I found where the root cause of the problem. My user database
full whne checking using "sp_helpdb <user database>" no free kbytes at
all (0). I dump the databse and get some free space. Now, I'm thinking
of alter (extend) this user databse. As I never did before, what are
preliminary parameter to consider and how to entend the database.

Regards,
Munawwar