Attn all Sybase Tuning Gurus: Large Number of Cache Misses
Margie
Thanks in advance...
Hi All. I'm hoping that this one will be easy. About 2 weeks ago, my
customer switched servers. About 4 days ago, they encountered a major
hit in performance. I'm not sure why, except the number of cache
misses is huge when it used to be nominal.
The number of engines has changed, because the new server has fewer
CPU's. However, the hardware people said that this wouldn't be a
problem because the new CPU's are faster. ??? Also, the buffer pools
seem to have changed, but after talking to the app people, I think they're correct.
The only thing I've done is increased the total memory by 200 MB.
31,33c31,33
old: [16K I/O Buffer Pool]
old: pool size = 25.0000M
old: wash size = DEFAULT
---
new: [2K I/O Buffer Pool]
new: pool size = 25600.0000K
new: wash size = 512 K
39c39
old: number of devices = 45
---
new: number of devices = 100
62c62
old: total memory = 393216
---
new: total memory = 500000
68c68
old: max online engines = 4
---
new: max online engines = 2
Here's the latest sp_sysmon -- Pitiful cache misses.
===============================================================================
Sybase SQL Server System Performance Report
===============================================================================
Run Date Sep 17, 2001
Statistics Cleared at 14:12:19
Statistics Sampled at 14:14:19
Sample Interval 2 min.
===============================================================================
Kernel Utilization
------------------
Engine Busy Utilization:
Engine 0 45.0 %
Engine 1 55.9 %
----------- --------------- ----------------
Summary: Total: 100.9 % Average: 50.4 %
CPU Yields by Engine per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Engine 0 12.8 14.0 1529
53.1 %
Engine 1 11.3 12.4 1349
46.9 %
------------------------- ------------ ------------ ----------
Total CPU Yields: 24.0 26.4 2878
Network Checks
Non-Blocking 35.7 39.2 4278
100.0 %
Blocking 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total Network I/O Checks: 35.7 39.2 4278
Avg Net I/Os per Check n/a n/a 0.71996
n/a
Disk I/O Checks
Total Disk I/O Checks 141917.0 156116.5 17016695
n/a
Checks Returning I/O 129553.4 142515.9 15534232
91.3 %
Avg Disk I/Os Returned n/a n/a 0.00120
n/a
===============================================================================
Task Management per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Connections Opened 0.0 0.0 0
n/a
Task Context Switches by Engine
Engine 0 91.6 100.8 10984
43.8 %
Engine 1 117.7 129.5 14118
56.2 %
------------------------- ------------ ------------ ----------
Total Task Switches: 209.3 230.3 25102
Task Context Switches Due To:
Voluntary Yields 1.0 1.1 116
0.5 %
Cache Search Misses 154.2 169.7 18494
73.7 %
System Disk Writes 0.1 0.1 11
0.0 %
I/O Pacing 0.2 0.2 25
0.1 %
Logical Lock Contention 0.0 0.0 0
0.0 %
Address Lock Contention 0.0 0.0 4
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.0 1.1 116
0.5 %
Modify Conflicts 0.0 0.0 1
0.0 %
I/O Device Contention 0.0 0.0 0
0.0 %
Network Packet Received 12.3 13.5 1476
5.9 %
Network Packet Sent 13.4 14.7 1604
6.4 %
SYSINDEXES Lookup 0.0 0.0 0
0.0 %
Other Causes 27.1 29.9 3255
13.0 %
===============================================================================
Transaction Profile
-------------------
Transaction Summary per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Committed Xacts 0.9 n/a 109
n/a
Transaction Detail per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Inserts
Heap Table 1.8 2.0 218
77.9 %
Clustered Table 0.5 0.6 62
22.1 %
------------------------- ------------ ------------ ----------
Total Rows Inserted 2.3 2.6 280
75.3 %
Updates
Deferred 0.1 0.1 10
15.2 %
Direct In-place 0.5 0.5 56
84.8 %
Direct Cheap 0.0 0.0 0
0.0 %
Direct Expensive 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total Rows Updated 0.6 0.6 66
17.7 %
Deletes
Deferred 0.2 0.2 25
96.2 %
Direct 0.0 0.0 1
3.8 %
------------------------- ------------ ------------ ----------
Total Rows Deleted 0.2 0.2 26
7.0 %
===============================================================================
Transaction Management
----------------------
ULC Flushes to Xact Log per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
by Full ULC 0.0 0.0 1
0.4 %
by End Transaction 0.9 1.0 110
48.5 %
by Change of Database 0.0 0.0 1
0.4 %
by System Log Record 0.9 1.0 109
48.0 %
by Other 0.1 0.1 6
2.6 %
------------------------- ------------ ------------ ----------
Total ULC Flushes 1.9 2.1 227
ULC Log Records 5.5 6.1 663
n/a
Max ULC Size n/a n/a 2044
n/a
ULC Semaphore Requests
Granted 11.3 12.4 1350
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total ULC Semaphore Req 11.3 12.4 1350
Log Semaphore Requests
Granted 2.3 2.5 275
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total Log Semaphore Req 2.3 2.5 275
Transaction Log Writes 1.2 1.3 139
n/a
Transaction Log Alloc 0.2 0.2 22
n/a
Avg # Writes per Log Page n/a n/a 6.31818
n/a
===============================================================================
Index Management
----------------
Nonclustered Maintenance per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Ins/Upd Requiring Maint 0.9 0.9 103
n/a
# of NC Ndx Maint 0.9 1.0 112
n/a
Avg NC Ndx Maint / Op n/a n/a 1.08738
n/a
Deletes Requiring Maint 0.5 0.5 57
n/a
# of NC Ndx Maint 0.0 0.0 1
n/a
Avg NC Ndx Maint / Op n/a n/a 0.01754
n/a
RID Upd from Clust Split 0.0 0.0 4
n/a
# of NC Ndx Maint 0.5 0.6 60
n/a
Avg NC Ndx Maint / Op n/a n/a 15.00000
n/a
Page Splits 0.0 0.0 2
n/a
Retries 0.0 0.0 0
0.0 %
Deadlocks 0.0 0.0 0
0.0 %
Empty Page Flushes 0.0 0.0 0
0.0 %
Add Index Level 0.0 0.0 0
0.0 %
Page Shrinks 0.0 0.0 0
n/a
===============================================================================
Lock Management
---------------
Lock Summary per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total Lock Requests 884.9 973.5 106107
n/a
Avg Lock Contention 0.0 0.0 4
0.0 %
Deadlock Percentage 0.0 0.0 0
0.0 %
Lock Detail per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Exclusive Table
Granted 0.2 0.2 19
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total EX-Table Requests 0.2 0.2 19
0.0 %
Shared Table
Granted 0.0 0.0 1
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total SH-Table Requests 0.0 0.0 1
0.0 %
Exclusive Intent
Granted 0.9 0.9 103
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total EX-Intent Requests 0.9 0.9 103
0.1 %
Shared Intent
Granted 44.8 49.2 5366
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total SH-Intent Requests 44.8 49.2 5366
5.1 %
Exclusive Page
Granted 2.5 2.7 295
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total EX-Page Requests 2.5 2.7 295
0.3 %
Update Page
Granted 1.0 1.1 121
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total UP-Page Requests 1.0 1.1 121
0.1 %
Shared Page
Granted 518.6 570.4 62179
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total SH-Page Requests 518.6 570.4 62179
58.6 %
Exclusive Address
Granted 29.5 32.5 3539
99.9 %
Waited 0.0 0.0 4
0.1 %
------------------------- ------------ ------------ ----------
Total EX-Address Requests 29.5 32.5 3543
3.3 %
Shared Address
Granted 287.6 316.3 34480
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total SH-Address Requests 287.6 316.3 34480
32.5 %
Last Page Locks on Heaps
Granted 1.8 2.0 218
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total Last Pg Locks 1.8 2.0 218
0.2 %
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 677.3 745.0 81209
81.1 %
Total Cache Misses 157.9 173.7 18935
18.9 %
------------------------- ------------ ------------ ----------
Total Cache Searches 835.2 918.8 100144
Cache Turnover
Buffers Grabbed 154.2 169.7 18494
n/a
Buffers Grabbed Dirty 0.0 0.0 0
0.0 %
Cache Strategy Summary
Cached (LRU) Buffers 1034.2 1137.7 124010
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 677.3 745.0 81209
81.1 %
Found in Wash 7.4 8.2 893
1.1 %
Cache Misses 157.9 173.7 18935
18.9 %
------------------------- ------------ ------------ ----------
Total Cache Searches 835.2 918.8 100144
Pool Turnover
2 Kb Pool
LRU Buffer Grab 154.2 169.7 18494
100.0 %
Grabbed Dirty 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 154.2 169.7 18494
Buffer Wash Behavior
Statistics Not Available - No Buffers Entered Wash Section Yet
Cache Strategy
Cached (LRU) Buffers 1034.2 1137.7 124010
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 14.8 16.3 1775
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 1
n/a
Pages Released 0.0 0.0 1
n/a
===============================================================================
Recovery Management
-------------------
Checkpoints per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
# of Normal Checkpoints 0.0 0.0 1
100.0 %
# of Free Checkpoints 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total Checkpoints 0.0 0.0 1
n/a
Avg Time per Normal Chkpt 0.00000 seconds
===============================================================================
Disk I/O Management
-------------------
Max Outstanding I/Os per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Server n/a n/a 4
n/a
Engine 0 n/a n/a 4
n/a
Engine 1 n/a n/a 3
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 155.6 171.2 18656
n/a
Completed Disk I/O's
Engine 0 71.6 78.7 8581
46.0 %
Engine 1 84.0 92.4 10075
54.0 %
------------------------- ------------ ------------ ----------
----------
Total Completed I/Os 155.6 171.2 18656
Device Activity Detail
----------------------
/dev/rdsk/syblog01
log01 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/syblog02
log02 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/syblog03
log03 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.0 0.0 0
0.0 %
Writes 1.0 1.1 125
100.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 1.0 1.1 125
0.7 %
Device Semaphore Granted 1.0 1.1 125
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg01
data01 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.1 0.1 8
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.1 0.1 8
0.0 %
Device Semaphore Granted 0.1 0.1 8
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg02
data02 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 2.1 2.3 249
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 2.1 2.3 249
1.3 %
Device Semaphore Granted 2.1 2.3 249
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg03
data03 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.1 0.1 12
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.1 0.1 12
0.1 %
Device Semaphore Granted 0.1 0.1 12
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg04
data04 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 5.9 6.5 710
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 5.9 6.5 710
3.8 %
Device Semaphore Granted 5.9 6.5 710
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg05
data05 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.1 0.1 10
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.1 0.1 10
0.1 %
Device Semaphore Granted 0.1 0.1 10
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg06
data06 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.0 0.0 4
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 4
0.0 %
Device Semaphore Granted 0.0 0.0 4
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg07
data07 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.1 0.1 7
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.1 0.1 7
0.0 %
Device Semaphore Granted 0.1 0.1 7
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg08
data08 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 5.8 6.4 693
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 5.8 6.4 693
3.7 %
Device Semaphore Granted 5.8 6.4 693
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg09
data09 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.0 0.0 5
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 5
0.0 %
Device Semaphore Granted 0.0 0.0 5
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg10
data10 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 6.0 6.6 715
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 6.0 6.6 715
3.8 %
Device Semaphore Granted 6.0 6.6 715
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg11
data11 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.1 0.1 7
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.1 0.1 7
0.0 %
Device Semaphore Granted 0.1 0.1 7
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg12
data12 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 7.1 7.8 848
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 7.1 7.8 848
4.5 %
Device Semaphore Granted 7.1 7.8 848
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg13
data13 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 12.1 13.3 1452
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 12.1 13.3 1452
7.8 %
Device Semaphore Granted 12.1 13.3 1452
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg14
data14 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.2 0.2 19
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.2 0.2 19
0.1 %
Device Semaphore Granted 0.2 0.2 19
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg15
data15 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.1 0.1 14
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.1 0.1 14
0.1 %
Device Semaphore Granted 0.1 0.1 14
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg16
data16 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 11.8 13.0 1414
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 11.8 13.0 1414
7.6 %
Device Semaphore Granted 11.8 13.0 1414
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg17
data17 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg18
data18 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg19
data19 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.1 0.1 10
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.1 0.1 10
0.1 %
Device Semaphore Granted 0.1 0.1 10
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg20
data20 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.1 0.1 8
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.1 0.1 8
0.0 %
Device Semaphore Granted 0.1 0.1 8
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg21
data21 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 10.1 11.1 1212
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 10.1 11.1 1212
6.5 %
Device Semaphore Granted 10.1 11.1 1212
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg23
data23 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg24
data24 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg25
data25 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.1 0.1 7
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.1 0.1 7
0.0 %
Device Semaphore Granted 0.1 0.1 7
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg26
data26 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 7.5 8.3 904
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 7.5 8.3 904
4.8 %
Device Semaphore Granted 7.5 8.3 904
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg27
data27 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.1 0.1 12
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.1 0.1 12
0.1 %
Device Semaphore Granted 0.1 0.1 12
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg28
data28 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 17.2 18.9 2061
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 17.2 18.9 2061
11.0 %
Device Semaphore Granted 17.2 18.9 2061
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg29
data29 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.2 0.2 20
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.2 0.2 20
0.1 %
Device Semaphore Granted 0.2 0.2 20
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg30
data30 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.2 0.2 20
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.2 0.2 20
0.1 %
Device Semaphore Granted 0.2 0.2 20
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg31
data31 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.2 0.2 24
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.2 0.2 24
0.1 %
Device Semaphore Granted 0.2 0.2 24
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg32
data32 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 16.9 18.6 2024
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 16.9 18.6 2024
10.8 %
Device Semaphore Granted 16.9 18.6 2024
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg33
data33 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.1 0.1 15
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.1 0.1 15
0.1 %
Device Semaphore Granted 0.1 0.1 15
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg34
data34 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.2 0.2 19
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.2 0.2 19
0.1 %
Device Semaphore Granted 0.2 0.2 19
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg35
data35 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 17.1 18.8 2045
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 17.1 18.8 2045
11.0 %
Device Semaphore Granted 17.1 18.8 2045
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg36
data36 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.2 0.2 22
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.2 0.2 22
0.1 %
Device Semaphore Granted 0.2 0.2 22
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg37
data37 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 17.6 19.4 2113
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 17.6 19.4 2113
11.3 %
Device Semaphore Granted 17.6 19.4 2113
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg38
data38 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.2 0.2 20
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.2 0.2 20
0.1 %
Device Semaphore Granted 0.2 0.2 20
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg39
data39 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.2 0.2 25
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.2 0.2 25
0.1 %
Device Semaphore Granted 0.2 0.2 25
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg40
data40 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 14.0 15.4 1679
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 14.0 15.4 1679
9.0 %
Device Semaphore Granted 14.0 15.4 1679
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg41
data41 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.2 0.2 23
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.2 0.2 23
0.1 %
Device Semaphore Granted 0.2 0.2 23
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg42
data42 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.2 0.2 22
100.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.2 0.2 22
0.1 %
Device Semaphore Granted 0.2 0.2 22
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg43
data43 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.3 0.3 33
86.8 %
Writes 0.0 0.0 5
13.2 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.3 0.3 38
0.2 %
Device Semaphore Granted 0.3 0.3 38
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg45
data45 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg46
data46 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads 0.1 0.1 9
90.0 %
Writes 0.0 0.0 1
10.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.1 0.1 10
0.1 %
Device Semaphore Granted 0.1 0.1 10
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg47
data50 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg48
data51 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg49
data52 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg50
data53 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg51
data47 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg52
data48 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybseg53
data49 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybsysproc
sybsysproc per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
/dev/rdsk/sybtemp01
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 %
---------------------------------------------------------------------------
/dev/rdsk/sybtemp02
tempdev2 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.0 0.0 0
0.0 %
Writes 0.3 0.3 31
100.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.3 0.3 31
0.2 %
Device Semaphore Granted 0.3 0.3 31
100.0 %
Device Semaphore Waited 0.0 0.0 0
0.0 %
---------------------------------------------------------------------------
===============================================================================
Network I/O Management
----------------------
Total Network I/O Requests 25.7 28.3 3080
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 4.5 4.9 536
36.3 %
Engine 1 7.8 8.6 940
63.7 %
------------------------- ------------ ------------ ----------
----------
Total TDS Packets Rec'd 12.3 13.5 1476
Total Bytes Received per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Engine 0 978.0 1075.9 117269
36.2 %
Engine 1 1722.4 1894.7 206526
63.8 %
------------------------- ------------ ------------ ----------
----------
Total Bytes Rec'd 2700.4 2970.6 323795
Avg Bytes Rec'd per Packet n/a n/a 219
n/a
-----------------------------------------------------------------------------
Total TDS Packets Sent per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Engine 0 4.7 5.2 562
35.0 %
Engine 1 8.7 9.6 1042
65.0 %
------------------------- ------------ ------------ ----------
----------
Total TDS Packets Sent 13.4 14.7 1604
Total Bytes Sent per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Engine 0 1154.8 1270.3 138467
34.1 %
Engine 1 2226.9 2449.7 267020
65.9 %
------------------------- ------------ ------------ ----------
----------
Total Bytes Sent 3381.7 3720.1 405487
Avg Bytes Sent per Packet n/a n/a 252
n/a
=============================== End of Report
=================================
(return status = 0)
David Owen
Margie> The only thing I've done is increased the total memory by 200
Margie> MB.
I believe you, honest :-) However, has anybody else changed anything?
Margie> Here's the latest sp_sysmon -- Pitiful cache misses.
This looks like an old version of sp_sysmon, what release of SQL Server?
11.5? There are some other bizarre things quite apart from the cache hit
stuff. Take a look at the disk and network checks. These numbers seem out
of whack to me. Why would ASE do 17 million disk checks and only 5000
network checks?
What does this system actually do? I assume that it must be some sort of
DSS machine, since 2 engines 50% busy for a total of less than 500 rows
affected over 2 minutes. Our 4 engine test box runs through more than 1500
rows per second for less activity on the CPU front.
Have you updated the statistics recently? Anyone dropped any indexes? I
think this server is table scanning like mad. If not that, has anyone
enable CIS? What version is ASE? You might even suspect sp_sysmon of
being wrong. See if Sybase have a newer version that you can use.
Also, you give a 16k I/O pool in "old:" but a 2k pool in "new:", and only
have 25M allocated to it. Where is the rest of the memory allocted? This
could be the problem. If all of it is allocated to the 16k pool, then
there is strong likelihood of tables scans with some versions of the
optimiser, especially with 11.5 and early versions of 11.9.
dowen
> Network Checks
> Non-Blocking 35.7 39.2 4278
> 100.0 %
> Blocking 0.0 0.0 0
> 0.0 %
> ------------------------- ------------ ------------ ----------
> Total Network I/O Checks: 35.7 39.2 4278
> Avg Net I/Os per Check n/a n/a 0.71996
> n/a
>
>
> Disk I/O Checks
> Total Disk I/O Checks 141917.0 156116.5 17016695
> n/a
> Checks Returning I/O 129553.4 142515.9 15534232
> 91.3 %
> Avg Disk I/Os Returned n/a n/a 0.00120
> n/a
>
...
Data Cache Management
---------------------
Cache Statistics Summary (All Caches)
-------------------------------------
Cache Search Summary
Total Cache Hits 677.3 745.0 81209
81.1 %
Total Cache Misses 157.9 173.7 18935
18.9 %
------------------------- ------------ ------------ ----------
Total Cache Searches 835.2 918.8 100144
--
David Owen Midsomer Consultants Inc. do...@midsomer.org
Maintainer of the Sybase FAQ: http://www.isug.com/Sybase_FAQ
Willie Kraatz
rel 11.9.2. The only identified difference was the amount of total memory.
The system with the larger memory performed poorly. The query plans
indicated a switch from index use to table scans.
"Margie" <spy_2...@yahoo.com> wrote in message
news:8a535156.01091...@posting.google.com...
Margie
out-of-date statistics. I thought their problems were due to moving
to a different server, but in fact it was because they hadn't run
update stats for a long time. I was a little afraid to run update
statistics because it takes so long, but I ran update stats on some
heavily-used tables and the cache misses changed from 20% to 4%. It's
still not looking wonderful, but it's much better than it was.
Thanks for looking at this.
Bob Meggitt
servers. The indexes in pre servers were histogram based and post they are
step based. Sometimes the upgrade does not create the stats correctly. The
default is 10 steps in post. By using optdiag on the indexes you can find
out which one didn't create correctly. They will have less than 10 steps.
Updating stats will not fix the missing steps. You will need to drop and
recreate the index. On large tables, you mat want to increase the number of
steps. Sometimes this will increase the performance.
don't know if this applies to your situation, but it;s food for thought.
"Margie" <spy_2...@yahoo.com> wrote in message
news:8a535156.01091...@posting.google.com...
(Removed rest of post to save space)