Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

ASE 15.5 update stats

16 posts in Performance and Tuning Last posting was on 2011-10-28 16:20:40.0Z
Isabella Posted on 2011-10-18 20:42:33.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 2.0.0.19 (X11/20090105)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: ASE 15.5 update stats
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e9de4b9@forums-1-dub>
Date: 18 Oct 2011 13:42:33 -0700
X-Trace: forums-1-dub 1318970553 10.22.241.152 (18 Oct 2011 13:42:33 -0700)
X-Original-Trace: 18 Oct 2011 13:42:33 -0700, vip152.sybase.com
Lines: 2084
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:12995
Article PK: 91445

Hello All,
I 'm testing ASE 15.5 performance on a new host. running update stats on
100mil row tables takes longer than I was suspecting , this is round
robin partition tables , I was expecting to see CPU balanced bot not ,
here is the output from sp_sysmon for 10 min , while running only update
stat on this new brand server, I need to know if update stats can be run
parallel , or only other explanation for this CPU in balanced issue
my env ASE 15.5GA qith RHES 6-64bits


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

Your Runnable Process Search Count is set to 2000
and I/O Polling Process Count is set to 10

Engine Busy Utilization CPU Busy I/O Busy Idle
------------------------ -------- -------- --------
Engine 0 0.9 % 4.1 % 95.0 %
Engine 1 0.0 % 0.0 % 100.0 %
Engine 2 0.0 % 0.0 % 100.0 %
Engine 3 0.0 % 0.0 % 100.0 %
Engine 4 0.0 % 0.0 % 100.0 %
Engine 5 0.0 % 0.0 % 100.0 %
Engine 6 14.8 % 80.2 % 5.0 %
Engine 7 0.0 % 0.0 % 100.0 %
Engine 8 0.0 % 0.0 % 100.0 %
Engine 9 0.0 % 0.0 % 100.0 %
Engine 10 0.0 % 0.0 % 100.0 %
Engine 11 0.0 % 0.0 % 100.0 %
Engine 12 0.0 % 0.0 % 100.0 %
Engine 13 0.0 % 0.0 % 100.0 %
Engine 14 0.0 % 0.0 % 100.0 %
Engine 15 0.0 % 0.0 % 100.0 %
Engine 16 0.0 % 0.0 % 100.0 %
Engine 17 0.0 % 0.0 % 100.0 %
Engine 18 0.0 % 0.0 % 100.0 %
Engine 19 0.0 % 0.0 % 100.0 %
Engine 20 0.0 % 0.0 % 100.0 %
Engine 21 0.0 % 0.0 % 100.0 %
Engine 22 0.0 % 0.0 % 100.0 %
Engine 23 0.0 % 0.0 % 100.0 %
Engine 24 0.0 % 0.0 % 100.0 %
Engine 25 0.0 % 0.0 % 100.0 %
------------------------ -------- -------- --------
Summary Total 15.8 % 84.2 % 2500.0 %
Average 0.6 % 3.2 % 96.2 %

CPU Yields by Engine per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Engine 0 54.6 1023.6 32755
3.8 %
Engine 1 58.0 1087.8 34808
4.0 %
Engine 2 58.0 1087.9 34812
4.0 %
Engine 3 58.0 1088.4 34828
4.0 %
Engine 4 58.1 1088.8 34842
4.0 %
Engine 5 58.1 1089.1 34851
4.0 %
Engine 6 2.9 53.9 1726
0.2 %
Engine 7 58.0 1088.3 34825
4.0 %
Engine 8 58.1 1088.5 34831
4.0 %
Engine 9 58.1 1088.7 34837
4.0 %
Engine 10 58.0 1087.9 34812
4.0 %
Engine 11 58.1 1088.7 34839
4.0 %
Engine 12 58.0 1087.7 34807
4.0 %
Engine 13 58.1 1088.5 34833
4.0 %
Engine 14 58.0 1088.1 34819
4.0 %
Engine 15 58.0 1088.1 34819
4.0 %
Engine 16 58.1 1088.8 34843
4.0 %
Engine 17 58.1 1088.8 34842
4.0 %
Engine 18 58.0 1087.9 34812
4.0 %
Engine 19 58.1 1088.8 34840
4.0 %
Engine 20 58.0 1088.3 34827
4.0 %
Engine 21 58.1 1088.5 34831
4.0 %
Engine 22 58.0 1088.3 34826
4.0 %
Engine 23 58.0 1088.3 34827
4.0 %
Engine 24 58.0 1088.2 34823
4.0 %
Engine 25 58.1 1088.7 34839
4.0 %
------------------------- ------------ ------------ ----------
Total CPU Yields 1450.6 27198.6 870354

Network Checks
Non-Blocking 1350779.3 25327112.0 810467572
99.9 %
Blocking 1450.3 27193.7 870199
0.1 %
------------------------- ------------ ------------ ----------
Total Network I/O Checks 1352229.6 25354306.0 811337771
Avg Net I/Os per Check n/a n/a 0.00000
n/a

Disk I/O Checks
Total Disk I/O Checks 1354040.0 25388250.0 812424001
n/a
Checks Returning I/O 1329758.5 24932972.0 797855100
98.2 %
Avg Disk I/Os Returned n/a n/a 0.00156
n/a

Tuning Recommendations for Kernel Utilization
---------------------------------------------
- Consider decreasing the 'runnable process search count'
configuration parameter if you require the CPU's on
the machine to be used for other applications.


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

Worker Process Management
-------------------------
per sec per xact count %
of total
------------ ------------ ----------
----------
Worker Process Requests
Total Requests 0.0 0.0 0
n/a

Worker Process Usage
Total Used 0.0 0.0 0
n/a
Max Ever Used During Sample 0.0 0.0 0
n/a

Memory Requests for Worker Processes
Total Requests 0.0 0.0 0
n/a

Tuning Recommendations for Worker Processes
-------------------------------------------
- Consider decreasing the 'number of worker processes'
configuration parameter.


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

Parallel Query Management
-------------------------

Parallel Query Usage per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total Parallel Queries 0.0 0.0 0
n/a

Merge Lock Requests per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total # of Requests 0.0 0.0 0
n/a

Sort Buffer Waits per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total # of Waits 0.0 0.0 0
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 85.4 1601.2 51238
5.2 %
Engine 1 0.2 4.6 148
0.0 %
Engine 2 0.2 4.3 137
0.0 %
Engine 3 0.2 4.4 140
0.0 %
Engine 4 0.2 4.4 141
0.0 %
Engine 5 0.2 4.2 135
0.0 %
Engine 6 1563.8 29320.4 938254
94.5 %
Engine 7 0.2 4.4 140
0.0 %
Engine 8 0.3 4.8 153
0.0 %
Engine 9 0.3 4.9 157
0.0 %
Engine 10 0.2 4.4 142
0.0 %
Engine 11 0.2 4.4 140
0.0 %
Engine 12 0.2 4.3 139
0.0 %
Engine 13 0.2 4.5 145
0.0 %
Engine 14 0.2 4.4 141
0.0 %
Engine 15 0.2 4.5 143
0.0 %
Engine 16 0.2 4.7 149
0.0 %
Engine 17 0.3 4.7 150
0.0 %
Engine 18 0.2 4.4 141
0.0 %
Engine 19 0.2 4.4 142
0.0 %
Engine 20 0.2 4.4 140
0.0 %
Engine 21 0.2 4.2 135
0.0 %
Engine 22 0.2 4.6 147
0.0 %
Engine 23 0.2 4.6 146
0.0 %
Engine 24 0.2 4.3 137
0.0 %
Engine 25 0.2 4.6 147
0.0 %
------------------------- ------------ ------------ ----------
Total Task Switches: 1654.8 31026.7 992854

Task Context Switches Due To:
Voluntary Yields 11.0 206.5 6609
0.7 %
Cache Search Misses 622.7 11676.4 373646
37.6 %
Exceeding I/O batch size 0.0 0.0 0
0.0 %
System Disk Writes 0.0 0.4 13
0.0 %
Logical Lock Contention 0.0 0.0 0
0.0 %
Address Lock Contention 0.0 0.0 0
0.0 %
Latch Contention 0.0 0.0 0
0.0 %
Physical Lock Transition 0.0 0.0 0
0.0 %
Logical Lock Transition 0.0 0.0 0
0.0 %
Object Lock Transition 0.0 0.0 0
0.0 %
Log Semaphore Contention 0.0 0.0 0
0.0 %
PLC Lock Contention 0.0 0.0 0
0.0 %
Group Commit Sleeps 0.0 0.0 0
0.0 %
Last Log Page Writes 0.0 0.3 8
0.0 %
Modify Conflicts 0.0 0.0 0
0.0 %
I/O Device Contention 0.0 0.0 0
0.0 %
Network Packet Received 0.0 0.0 0
0.0 %
Network Packet Sent 0.0 0.0 0
0.0 %
Interconnect Message Sleeps 0.0 0.0 0
0.0 %
Network services 0.0 0.0 0
0.0 %
Other Causes 1021.0 19143.1 612578
61.7 %


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

Application Management
----------------------

Application Statistics Summary (All Applications)
-------------------------------------------------
Priority Changes per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
To High Priority 0.0 0.0 0
0.0 %
To Medium Priority 0.4 8.2 262
50.0 %
To Low Priority 0.4 8.2 262
50.0 %
------------------------- ------------ ------------ ----------
Total Priority Changes 0.9 16.4 524

Allotted Slices Exhausted per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total Slices Exhausted 0.0 0.0 0
n/a

Skipped Tasks By Engine per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total Engine Skips 0.0 0.0 0
n/a

Engine Scope Changes 0.0 0.0 0
n/a

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

ESP Management per sec per xact count %
of total
--------------------------- ------------ ------------ ----------
----------
ESP Requests 0.0 0.0 0
n/a
===============================================================================

Housekeeper Task Activity
-------------------------
per sec per xact count %
of total
------------ ------------ ----------
Buffer Cache Washes
Clean 437.4 8200.4 262412
99.7 %
Dirty 1.3 24.4 782
0.3 %
------------ ------------ ----------
Total Washes 438.7 8224.8 263194

Garbage Collections 4.9 91.0 2912 n/a
Pages Processed in GC 0.0 0.0 0 n/a

Statistics Updates 0.4 8.1 260 n/a

Tuning Recommendations for Housekeeper
--------------------------------------
- Consider increasing the 'housekeeper free write percent'
configuration parameter.

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

Monitor Access to Executing SQL
-------------------------------
per sec per xact count %
of total
------------ ------------ ----------
----------
Waits on Execution Plans 0.0 0.0 0
n/a
Number of SQL Text Overflows 0.0 0.0 0
n/a
Maximum SQL Text Requested n/a n/a 0
n/a
(since beginning of sample)


Tuning Recommendations for Monitor Access to Executing SQL
----------------------------------------------------------
- Consider decreasing the 'max SQL text monitored' parameter
to 15000 (i.e., half way from its current value to Maximum
SQL Text Requested).

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

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

Transaction Summary per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Committed Xacts 0.1 n/a 32 n/a

Transaction Detail per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Inserts
Fully Logged
APL Heap Table 126.7 2375.2 76006
100.0 %
APL Clustered Table 0.0 0.0 0
0.0 %
Data Only Lock Table 0.0 0.0 0
0.0 %
Fast Bulk Insert 0.0 0.0 0
0.0 %
Minimally Logged
APL Heap Table 0.0 0.0 0
0.0 %
APL Clustered Table 0.0 0.0 0
0.0 %
Data Only Lock Table 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total Rows Inserted 126.7 2375.2 76006
100.0 %

Updates
Fully Logged
APL Deferred 0.0 0.0 0
0.0 %
APL Direct In-place 0.0 0.0 0
0.0 %
APL Direct Cheap 0.0 0.0 0
0.0 %
APL Direct Expensive 0.0 0.0 0
0.0 %
DOL Deferred 0.0 0.0 0
0.0 %
DOL Direct 0.0 0.3 8
100.0 %
Minimally Logged
APL Direct In-place 0.0 0.0 0
0.0 %
APL Direct Cheap 0.0 0.0 0
0.0 %
APL Direct Expensive 0.0 0.0 0
0.0 %
DOL Direct 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total Rows Updated 0.0 0.3 8
0.0 %

Data Only Locked Updates
Fully Logged
DOL Replace 0.0 0.3 8
100.0 %
DOL Shrink 0.0 0.0 0
0.0 %
DOL Cheap Expand 0.0 0.0 0
0.0 %
DOL Expensive Expand 0.0 0.0 0
0.0 %
DOL Expand & Forward 0.0 0.0 0
0.0 %
DOL Fwd Row Returned 0.0 0.0 0
0.0 %
Minimally Logged
DOL Replace 0.0 0.0 0
0.0 %
DOL Shrink 0.0 0.0 0
0.0 %
DOL Cheap Expand 0.0 0.0 0
0.0 %
DOL Expensive Expand 0.0 0.0 0
0.0 %
DOL Expand & Forward 0.0 0.0 0
0.0 %
DOL Fwd Row Returned 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total DOL Rows Updated 0.0 0.3 8
0.0 %

Deletes
Total Rows Deleted 0.0 0.0 0
n/a

========================= ============ ============ ==========
Total Rows Affected 126.7 2375.4 76014
========================= ============ ============ ==========

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

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

ULC Flushes to Xact Log per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Any Logging Mode DMLs
by End Transaction 0.0 0.4 13
3.3 %
by Change of Database 0.0 0.1 2
0.5 %
by Unpin 0.0 0.0 0
0.0 %
by Log markers 0.0 0.0 1
0.3 %

Fully Logged DMLs
by Full ULC 0.6 12.0 383
96.0 %
by Single Log Record 0.0 0.0 0
0.0 %

Minimally Logged DMLs
by Full ULC 0.0 0.0 0
0.0 %
by Single Log Record 0.0 0.0 0
0.0 %
by Start of Sub-Command 0.0 0.0 0
0.0 %
by End of Sub-Command 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total ULC Flushes 0.7 12.5 399

ULC Flushes Skipped per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Fully Logged DMLs
by ULC Discards 0.0 0.9 29
100.0 %
Minimally Logged DMLs
by ULC Discards 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total ULC Flushes Skips 0.0 0.9 29

ULC Log Records per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Fully Logged DMLs 65.3 1224.5 39184
100.0
Minimally Logged DMLs 0.0 0.0 0
0.0
------------------------- ------------ ------------ ----------
Total ULC Log Records 65.3 1224.5 39184

Max ULC Size During Sample
--------------------------
Fully Logged DMLs n/a n/a 0
n/a
Minimally Logged DMLs n/a n/a 0
n/a

ML-DMLs Sub-Command Scans per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total Sub-Command Scans 0.0 0.0 0
n/a

ML-DMLs ULC Efficiency per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total ML-DML Sub-Commands 0.0 0.0 0
n/a

ULC Semaphore Requests
Granted 68.1 1276.4 40845
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total ULC Semaphore Req 68.1 1276.4 40845

Log Semaphore Requests
Granted 1.4 25.4 814
100.0 %
Local Waited 0.0 0.0 0
0.0 %
Global Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total Log Semaphore Req 1.4 25.4 814

Transaction Log Writes 0.0 0.3 11
n/a
Transaction Log Alloc 3.3 61.2 1957
n/a
Avg # Writes per Log Page n/a n/a 0.00562
n/a

Tuning Recommendations for Transaction Management
-------------------------------------------------
- Consider increasing the 'user log cache size'
configuration parameter.

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

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

Nonclustered Maintenance per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Ins/Upd Requiring Maint 0.0 0.0 0
n/a
# of NC Ndx Maint 0.0 0.0 0
n/a

Deletes Requiring Maint 0.0 0.0 0
n/a
# of NC Ndx Maint 0.0 0.0 0
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

Upd/Del DOL Req Maint 0.0 0.3 8
n/a
# of DOL Ndx Maint 0.0 0.3 8
n/a
Avg DOL Ndx Maint / Op n/a n/a 1.00000
n/a

Page Splits 0.0 0.0 0
n/a

Page Shrinks 0.0 0.0 0
n/a

Index Scans per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Ascending Scans 2.4 45.2 1446
63.8 %
DOL Ascending Scans 1.4 25.6 819
36.1 %
Descending Scans 0.0 0.1 2
0.1 %
DOL Descending Scans 0.0 0.0 0
0.0 %
------------ ------------ ----------
Total Scans 3.8 70.8 2267

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

Metadata Cache Management
-------------------------

Metadata Cache Summary per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------

Open Object Usage
Active n/a n/a 52
n/a
Max Ever Used Since Boot n/a n/a 53
n/a
Free n/a n/a 8948
n/a
Reuse Requests
Succeeded n/a n/a 0
n/a
Failed n/a n/a 0
n/a

Open Index Usage
Active n/a n/a 19
n/a
Max Ever Used Since Boot n/a n/a 19
n/a
Free n/a n/a 5481
n/a
Reuse Requests
Succeeded n/a n/a 0
n/a
Failed n/a n/a 0
n/a

Open Partition Usage
Active n/a n/a 28
n/a
Max Ever Used Since Boot n/a n/a 28
n/a
Free n/a n/a 5472
n/a
Reuse Requests
Succeeded n/a n/a 0
n/a
Failed n/a n/a 0
n/a

Open Database Usage
Active n/a n/a 7
n/a
Max Ever Used Since Boot n/a n/a 7
n/a
Free n/a n/a 193
n/a
Reuse Requests
Succeeded n/a n/a 0
n/a
Failed n/a n/a 0
n/a

Object Manager Spinlock Contention n/a n/a n/a
0.0 %

Object Spinlock Contention n/a n/a n/a
0.0 %

Index Spinlock Contention n/a n/a n/a
0.0 %

Index Hash Spinlock Contention n/a n/a n/a
0.0 %

Partition Spinlock Contention n/a n/a n/a
0.0 %

Partition Hash Spinlock Contention n/a n/a n/a
0.0 %

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

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

Lock Summary per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total Lock Requests 14.5 272.5 8719
n/a
Avg Lock Contention 0.0 0.0 0
0.0 %
Cluster Locks Retained 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
------------------------- ------------ ------------ ----------
----------

Table Lock Hashtable
Lookups 0.2 2.9 94
n/a
Avg Chain Length n/a n/a 0.00000
n/a
Spinlock Contention n/a n/a n/a
0.0 %

Exclusive Table
Granted 0.0 0.0 1
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total EX-Table Requests 0.0 0.0 1
0.0 %

Shared Table
Total SH-Table Requests 0.0 0.0 0
n/a

Exclusive Intent
Granted 0.0 0.6 19
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total EX-Intent Requests 0.0 0.6 19
0.2 %

Shared Intent
Granted 0.1 2.1 66
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total SH-Intent Requests 0.1 2.1 66
0.8 %

Page & Row Lock HashTable
Lookups 4.6 85.8 2745
n/a
Avg Chain Length n/a n/a 0.00000
n/a
Spinlock Contention n/a n/a n/a
0.0 %

Exclusive Page
Granted 0.0 0.3 10
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total EX-Page Requests 0.0 0.3 10
0.1 %

Update Page
Total UP-Page Requests 0.0 0.0 0
n/a

Shared Page
Granted 2.4 45.8 1466
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total SH-Page Requests 2.4 45.8 1466
16.8 %


Exclusive Row
Granted 0.0 0.3 8
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total EX-Row Requests 0.0 0.3 8
0.1 %

Update Row
Granted 0.0 0.3 11
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total UP-Row Requests 0.0 0.3 11
0.1 %

Shared Row
Granted 0.8 15.1 482
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total SH-Row Requests 0.8 15.1 482
5.5 %


Next-Key
Total Next-Key Requests 0.0 0.0 0
n/a

Address Lock Hashtable
Lookups 11.1 208.0 6656
n/a
Avg Chain Length n/a n/a 0.00000
n/a
Spinlock Contention n/a n/a n/a
0.0 %

Exclusive Address
Granted 0.6 10.6 339
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total EX-Address Requests 0.6 10.6 339
3.9 %

Shared Address
Granted 10.5 197.4 6317
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total SH-Address Requests 10.5 197.4 6317
72.5 %


Last Page Locks on Heaps
Granted 126.7 2375.2 76006
100.0 %
Waited 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total Last Pg Locks 126.7 2375.2 76006
100.0 %


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



Deadlock Detection
Deadlock Searches 0.0 0.0 0
n/a


Lock Promotions
Total Lock Promotions 0.0 0.0 0
n/a


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

Cluster Lock Summary per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------

Physical Locks Summary per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
No physical locks are acquired


Logical Locks Summary per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------

Object Locks Summary per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------


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

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

Cache Statistics Summary (All Caches)
-------------------------------------
per sec per xact count %
of total
------------ ------------ ----------
----------

Cache Search Summary
Total Cache Hits 13809.9 258934.8 8285915
95.7 %
Total Cache Misses 622.8 11677.4 373677
4.3 %
------------------------- ------------ ------------ ----------
Total Cache Searches 14432.7 270612.3 8659592

Cache Turnover
Buffers Grabbed 2070.3 38818.9 1242205
n/a
Buffers Grabbed Dirty 0.0 0.0 0
0.0 %

Cache Strategy Summary
Cached (LRU) Buffers 2212.2 41479.5 1327344
14.2 %
Discarded (MRU) Buffers 13372.6 250735.3 8023531
85.8 %

Large I/O Usage
Large I/Os Performed 1709.3 32049.9 1025597
83.3 %

Large I/Os Denied due to
Pool < Prefetch Size 342.2 6417.2 205349
16.7 %
Pages Requested
Reside in Another
Buffer Pool 0.1 1.8 56
0.0 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 2051.7 38468.8 1231002

Large I/O Effectiveness
Pages by Lrg I/O Cached 13674.6 256399.3 8204776
n/a
Pages by Lrg I/O Used 12917.0 242194.2 7750213
94.5 %

Asynchronous Prefetch Activity
APFs Issued 1447.5 27141.5 868528
15.8 %
APFs Denied Due To
APF I/O Overloads 0.0 0.0 0
0.0 %
APF Limit Overloads 0.0 0.0 0
0.0 %
APF Reused Overloads 0.0 0.0 0
0.0 %
APF Buffers Found in Cache
With Spinlock Held 0.0 0.0 0
0.0 %
W/o Spinlock Held 7735.8 145046.0 4641471
84.2 %
------------------------- ------------ ------------ ----------
Total APFs Requested 9183.3 172187.5 5509999

Other Asynchronous Prefetch Statistics
APFs Used 1447.5 27141.5 868527
n/a
APF Waits for I/O 1020.2 19129.3 612137
n/a
APF Discards 0.0 0.0 0
n/a

Dirty Read Behavior
Page Requests 0.0 0.0 0
n/a

-------------------------------------------------------------------------------
Cache: 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
99.0 %

Cache Searches
Cache Hits 13664.6 256210.4 8198732
95.6 %
Found in Wash 0.4 8.3 267
0.0 %
Cache Misses 622.8 11676.8 373658
4.4 %
------------------------- ------------ ------------ ----------
Total Cache Searches 14287.3 267887.2 8572390

Pool Turnover
2 Kb Pool
LRU Buffer Grab 361.0 6768.4 216589
17.4 %
Grabbed Locked Buffer 0.0 0.0 0
0.0 %
Grabbed Dirty 0.0 0.0 0
0.0 %
16 Kb Pool
LRU Buffer Grab 1709.3 32049.9 1025597
82.6 %
Grabbed Locked Buffer 0.0 0.0 0
0.0 %
Grabbed Dirty 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 2070.3 38818.3 1242186

Cluster Cache Behavior
No physical locks are acquired on buffers in this cache

Buffer Wash Behavior
Statistics Not Available - No Buffers Entered Wash Section Yet

Cache Strategy
Cached (LRU) Buffers 2058.7 38600.7 1235221
13.3 %
Discarded (MRU) Buffers 13372.6 250735.3 8023531
86.7 %

Large I/O Usage
Large I/Os Performed 1709.3 32049.9 1025597
83.3 %

Large I/Os Denied due to
Pool < Prefetch Size 342.2 6417.2 205349
16.7 %
Pages Requested
Reside in Another
Buffer Pool 0.1 1.8 56
0.0 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 2051.7 38468.8 1231002

Large I/O Detail
16 Kb Pool
Pages Cached 13674.6 256399.3 8204776
n/a
Pages Used 12917.0 242194.2 7750213
94.5 %

Dirty Read Behavior
Page Requests 0.0 0.0 0
n/a

Tuning Recommendations for Data cache : default data cache
-------------------------------------
- Consider using 'relaxed LRU replacement policy'
for this cache.

-------------------------------------------------------------------------------
Cache: log_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
0.0 %

Cache Searches
Total Cache Searches 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
Total Cache Searches 0.0 0.0 0

Pool Turnover 0.0 0.0 0
n/a

Cluster Cache Behavior
No physical locks are acquired on buffers in this cache

Buffer Wash Behavior
Statistics Not Available - No Buffers Entered Wash Section Yet

Cache Strategy
Statistics Not Available - No Buffers Displaced Yet

Large I/O Usage
Total Large I/O Requests 0.0 0.0 0
n/a

Large I/O Detail
4 Kb Pool
Pages Cached 0.0 0.0 0
n/a
Pages Used 0.0 0.0 0
n/a

Dirty Read Behavior
Page Requests 0.0 0.0 0
n/a

Tuning Recommendations for Data cache : log_cache
-------------------------------------
- Consider using 'relaxed LRU replacement policy'
for this cache.

- Consider removing the 4k pool for this cache.

-------------------------------------------------------------------------------
Cache: tempdb_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
1.0 %

Cache Searches
Cache Hits 145.3 2724.5 87183
100.0 %
Found in Wash 0.0 0.2 5
0.0 %
Cache Misses 0.0 0.6 19
0.0 %
------------------------- ------------ ------------ ----------
Total Cache Searches 145.3 2725.1 87202

Pool Turnover
2 Kb Pool
LRU Buffer Grab 0.0 0.6 19
100.0 %
Grabbed Locked Buffer 0.0 0.0 0
0.0 %
Grabbed Dirty 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 0.0 0.6 19

Cluster Cache Behavior
No physical locks are acquired on buffers in this cache

Buffer Wash Behavior
Statistics Not Available - No Buffers Entered Wash Section Yet

Cache Strategy
Cached (LRU) Buffers 153.5 2878.8 92123
100.0 %
Discarded (MRU) Buffers 0.0 0.0 0
0.0 %

Large I/O Usage
Total Large I/O Requests 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

Tuning Recommendations for Data cache : tempdb_cache
-------------------------------------
- Consider using 'relaxed LRU replacement policy'
for this cache.

- Consider adding a large I/O pool for this cache.

-------------------------------------------------------------------------------
Cache: tempdb_sa_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
0.0 %

Cache Searches
Total Cache Searches 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
Total Cache Searches 0.0 0.0 0

Pool Turnover 0.0 0.0 0
n/a

Cluster Cache Behavior
No physical locks are acquired on buffers in this cache

Buffer Wash Behavior
Statistics Not Available - No Buffers Entered Wash Section Yet

Cache Strategy
Statistics Not Available - No Buffers Displaced Yet

Large I/O Usage
Total Large I/O Requests 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

Tuning Recommendations for Data cache : tempdb_sa_cache
-------------------------------------
- Consider using 'relaxed LRU replacement policy'
for this cache.

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

Procedure Cache Management per sec per xact count %
of total
--------------------------- ------------ ------------ ----------
----------
Procedure Requests 0.0 0.0 1
n/a
Procedure Reads from Disk 0.0 0.0 1
100.0 %
Procedure Writes to Disk 0.0 0.0 0
0.0 %
Procedure Removals 0.0 0.0 1
n/a
Procedure Recompilations 0.0 0.0 0
n/a

SQL Statement Cache:
Statements Cached 0.0 0.0 0
n/a
Statements Found in Cache 0.0 0.0 0
n/a
Statements Not Found 0.0 0.0 0
n/a
Statements Dropped 0.0 0.0 0
n/a
Statements Restored 0.0 0.0 0
n/a
Statements Not Cached 0.0 0.0 0
n/a

Tuning Recommendations for Procedure cache management
-----------------------------------------------------
- Consider increasing the 'procedure cache size'
configuration parameter.


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

Memory Management per sec per xact count %
of total
--------------------------- ------------ ------------ ----------
----------
Pages Allocated 0.3 6.0 192
n/a
Pages Released 0.3 6.0 192
n/a

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

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

Checkpoints per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
# of Normal Checkpoints 0.0 0.1 2
100.0 %
# of Free Checkpoints 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
Total Checkpoints 0.0 0.1 2

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 0
n/a
Engine 0 n/a n/a 128
n/a
Engine 1 n/a n/a 1
n/a
Engine 2 n/a n/a 26
n/a
Engine 3 n/a n/a 13
n/a
Engine 4 n/a n/a 0
n/a
Engine 5 n/a n/a 0
n/a
Engine 6 n/a n/a 24
n/a
Engine 7 n/a n/a 17
n/a
Engine 8 n/a n/a 16
n/a
Engine 9 n/a n/a 0
n/a
Engine 10 n/a n/a 0
n/a
Engine 11 n/a n/a 0
n/a
Engine 12 n/a n/a 0
n/a
Engine 13 n/a n/a 16
n/a
Engine 14 n/a n/a 0
n/a
Engine 15 n/a n/a 1
n/a
Engine 16 n/a n/a 1
n/a
Engine 17 n/a n/a 0
n/a
Engine 18 n/a n/a 15
n/a
Engine 19 n/a n/a 1
n/a
Engine 20 n/a n/a 0
n/a
Engine 21 n/a n/a 0
n/a
Engine 22 n/a n/a 0
n/a
Engine 23 n/a n/a 0
n/a
Engine 24 n/a n/a 0
n/a
Engine 25 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 2071.6 38842.9 1242972

Completed Disk I/O's
Asynchronous I/O's
Engine 0 110.3 2068.9 66206
5.3 %
Engine 1 0.0 0.0 0
0.0 %
Engine 2 0.0 0.0 0
0.0 %
Engine 3 0.0 0.0 0
0.0 %
Engine 4 0.0 0.0 0
0.0 %
Engine 5 0.0 0.0 0
0.0 %
Engine 6 1961.3 36774.9 1176797
94.7 %
Engine 7 0.0 0.5 17
0.0 %
Engine 8 0.0 0.2 6
0.0 %
Engine 9 0.0 0.0 0
0.0 %
Engine 10 0.0 0.0 0
0.0 %
Engine 11 0.0 0.0 0
0.0 %
Engine 12 0.0 0.0 0
0.0 %
Engine 13 0.0 0.0 0
0.0 %
Engine 14 0.0 0.0 0
0.0 %
Engine 15 0.0 0.0 0
0.0 %
Engine 16 0.0 0.0 0
0.0 %
Engine 17 0.0 0.0 0
0.0 %
Engine 18 0.0 0.0 0
0.0 %
Engine 19 0.0 0.0 0
0.0 %
Engine 20 0.0 0.0 0
0.0 %
Engine 21 0.0 0.0 0
0.0 %
Engine 22 0.0 0.0 0
0.0 %
Engine 23 0.0 0.0 0
0.0 %
Engine 24 0.0 0.0 0
0.0 %
Engine 25 0.0 0.0 0
0.0 %
Synchronous I/O's
Total Completed I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
Total Completed I/Os 2071.7 38844.6 1243026


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

Device:
/dev/raw/raw1
master per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads
APF 0.0 0.2 7
17.1 %
Non-APF 0.0 0.1 4
9.8 %
Writes 0.1 0.9 30
73.2 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.1 1.3 41
0.0 %



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

Device:
/dev/raw/raw15
cadc_pub_log1 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads
APF 0.0 0.0 0
0.0 %
Non-APF 0.0 0.0 0
0.0 %
Writes 0.0 0.3 9
100.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.3 9
0.0 %



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

Device:
/dev/raw/raw16
cadc_pub_dat1 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads
APF 1447.5 27140.1 868483
69.9 %
Non-APF 622.7 11676.2 373638
30.1 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 2070.2 38816.3 1242121
99.9 %



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

Device:
/dev/raw/raw2
sysprocsdev per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads
APF 0.2 4.1 130
89.0 %
Non-APF 0.0 0.5 16
11.0 %
Writes 0.0 0.0 0
0.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.2 4.6 146
0.0 %



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

Device:
/dev/raw/raw3
systemdbdev per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/cadc_pub_tempdb.dat2
tempdb_dat2 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads
APF 0.0 0.0 0
0.0 %
Non-APF 0.0 0.1 2
0.3 %
Writes 1.2 23.3 744
99.7 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 1.2 23.3 746
0.1 %



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

Device:
/var/sybase/devices-SSD/cadc_pub_tempdb.log2
tempddb_log2 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads
APF 0.0 0.0 0
0.0 %
Non-APF 0.0 0.3 8
42.1 %
Writes 0.0 0.3 11
57.9 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.6 19
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_dat10.dat
tempdb_dat10 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_dat11.dat
tempdb_dat11 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_dat12.dat
tempdb_dat12 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_dat3.dat
tempdb_dat3 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_dat4.dat
tempdb_dat4 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_dat5.dat
tempdb_dat5 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_dat6.dat
tempdb_dat6 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_dat7.dat
tempdb_dat7 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_dat8.dat
tempdb_dat8 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Reads
APF 0.0 0.0 0
0.0 %
Non-APF 0.0 0.3 9
50.0 %
Writes 0.0 0.3 9
50.0 %
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.6 18
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_dat9.dat
tempdb_dat9 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_log3.log
tempdb_log3 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_log4.log
tempdb_log4 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_log5.log
tempdb_log5 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_log6.dat
tempdb_log6 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_sa_dat1.dat
tempdb_sa_dat1 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_sa_dat2.dat
tempdb_sa_dat2 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_sa_dat3.dat
tempdb_sa_dat3 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_sa_dat4.dat
tempdb_sa_dat4 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_sa_dat5.dat
tempdb_sa_dat5 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_sa_dat6.dat
tempdb_sa_dat6 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_sa_dat7.dat
tempdb_sa_dat7 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_sa_log1.dat
tempdb_sa_log1 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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

Device:
/var/sybase/devices-SSD/tempdb_sa_log2.dat
tempdb_sa_log2 per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
n/a
------------------------- ------------ ------------ ----------
----------
Total I/Os 0.0 0.0 0
0.0 %



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



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

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

Total Network I/O Requests 0.0 0.0 0
n/a


Total TDS Packets Received per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total TDS Packets Rec'd 0.0 0.0 0
n/a


Total Bytes Received per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total Bytes Rec'd 0.0 0.0 0
n/a



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

Total TDS Packets Sent per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total TDS Packets Sent 0.0 0.0 0
n/a


Total Bytes Sent per sec per xact count %
of total
------------------------- ------------ ------------ ----------
----------
Total Bytes Sent 0.0 0.0 0
n/a


Bret Halford Posted on 2011-10-18 21:10:00.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: ASE 15.5 update stats
References: <4e9de4b9@forums-1-dub>
In-Reply-To: <4e9de4b9@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e9deb28$1@forums-1-dub>
Date: 18 Oct 2011 14:10:00 -0700
X-Trace: forums-1-dub 1318972200 10.22.241.152 (18 Oct 2011 14:10:00 -0700)
X-Original-Trace: 18 Oct 2011 14:10:00 -0700, vip152.sybase.com
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:12996
Article PK: 91446


On 10/18/2011 2:42 PM, Isabella wrote:
> Hello All,
> I 'm testing ASE 15.5 performance on a new host. running update stats on
> 100mil row tables takes longer than I was suspecting , this is round
> robin partition tables , I was expecting to see CPU balanced bot not ,
> here is the output from sp_sysmon for 10 min , while running only update
> stat on this new brand server, I need to know if update stats can be run
> parallel , or only other explanation for this CPU in balanced issue
> my env ASE 15.5GA qith RHES 6-64bits
>
>

Are you using the "with consumers" clause?
What is the exact command syntax you are running
(update stats, update index stats, ...)

How big are the tables, how many rows, how many partitions?

-bret


Isabella Posted on 2011-10-18 21:17:51.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 2.0.0.19 (X11/20090105)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: ASE 15.5 update stats
References: <4e9de4b9@forums-1-dub> <4e9deb28$1@forums-1-dub>
In-Reply-To: <4e9deb28$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e9decff$1@forums-1-dub>
Date: 18 Oct 2011 14:17:51 -0700
X-Trace: forums-1-dub 1318972671 10.22.241.152 (18 Oct 2011 14:17:51 -0700)
X-Original-Trace: 18 Oct 2011 14:17:51 -0700, vip152.sybase.com
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:12997
Article PK: 91448

Here are table info :
no consumer "update statistics table_name"
table row count approx 100MIL rows
10 round robin partitions
Thank you
Isabella

Bret Halford wrote:
> On 10/18/2011 2:42 PM, Isabella wrote:
>> Hello All,
>> I 'm testing ASE 15.5 performance on a new host. running update stats on
>> 100mil row tables takes longer than I was suspecting , this is round
>> robin partition tables , I was expecting to see CPU balanced bot not ,
>> here is the output from sp_sysmon for 10 min , while running only update
>> stat on this new brand server, I need to know if update stats can be run
>> parallel , or only other explanation for this CPU in balanced issue
>> my env ASE 15.5GA qith RHES 6-64bits
>>
>>
>
>
> Are you using the "with consumers" clause?
> What is the exact command syntax you are running
> (update stats, update index stats, ...)
>
> How big are the tables, how many rows, how many partitions?
>
> -bret


Bret Halford Posted on 2011-10-18 23:05:02.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: ASE 15.5 update stats
References: <4e9de4b9@forums-1-dub> <4e9deb28$1@forums-1-dub> <4e9decff$1@forums-1-dub>
In-Reply-To: <4e9decff$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e9e061e$1@forums-1-dub>
Date: 18 Oct 2011 16:05:02 -0700
X-Trace: forums-1-dub 1318979102 10.22.241.152 (18 Oct 2011 16:05:02 -0700)
X-Original-Trace: 18 Oct 2011 16:05:02 -0700, vip152.sybase.com
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:12998
Article PK: 91447

Try using the "with consumers" clause to involve worker processes
that will use those other engines.

On such a large table, you might also want to use "with sampling"
to speed things up.

Also, any particular reason you are testing with 15.5 GA?
There have been several 15.5 rollups released since then, and
15.7 GA was recently released as well.

-bret

On 10/18/2011 3:17 PM, Isabella wrote:
>
>
> Here are table info :
> no consumer "update statistics table_name"
> table row count approx 100MIL rows
> 10 round robin partitions
> Thank you
> Isabella
>
>
> Bret Halford wrote:
>> On 10/18/2011 2:42 PM, Isabella wrote:
>>> Hello All,
>>> I 'm testing ASE 15.5 performance on a new host. running update stats on
>>> 100mil row tables takes longer than I was suspecting , this is round
>>> robin partition tables , I was expecting to see CPU balanced bot not ,
>>> here is the output from sp_sysmon for 10 min , while running only update
>>> stat on this new brand server, I need to know if update stats can be run
>>> parallel , or only other explanation for this CPU in balanced issue
>>> my env ASE 15.5GA qith RHES 6-64bits
>>>
>>>
>>
>>
>> Are you using the "with consumers" clause?
>> What is the exact command syntax you are running
>> (update stats, update index stats, ...)
>>
>> How big are the tables, how many rows, how many partitions?
>>
>> -bret


Michael Peppler [Team Sybase] Posted on 2011-10-19 04:50:57.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: ASE 15.5 update stats
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2011.10.19.04.50.57.300397@peppler.org>
Newsgroups: sybase.public.ase.performance+tuning
References: <4e9de4b9@forums-1-dub> <4e9deb28$1@forums-1-dub> <4e9decff$1@forums-1-dub> <4e9e061e$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 18 Oct 2011 21:50:57 -0700
X-Trace: forums-1-dub 1318999857 10.22.241.152 (18 Oct 2011 21:50:57 -0700)
X-Original-Trace: 18 Oct 2011 21:50:57 -0700, vip152.sybase.com
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:12999
Article PK: 91449


On Tue, 18 Oct 2011 16:05:02 -0700, Bret Halford wrote:

> Try using the "with consumers" clause to involve worker processes that
> will use those other engines.
>
> On such a large table, you might also want to use "with sampling" to speed
> things up.

Hi Bret,

Would "with sampling" actually improve the basic "update statistics"
command that Isabella is running?

I thought that the "sampling" bit only affected the collection of stats
for the non-leading index columns calculated with "update index
statistics" (and I guess any direct column level stats calculation not
involving an index such as update all statistics).

Michael


Bret Halford Posted on 2011-10-19 16:20:40.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: ASE 15.5 update stats
References: <4e9de4b9@forums-1-dub> <4e9deb28$1@forums-1-dub> <4e9decff$1@forums-1-dub> <4e9e061e$1@forums-1-dub> <pan.2011.10.19.04.50.57.300397@peppler.org>
In-Reply-To: <pan.2011.10.19.04.50.57.300397@peppler.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e9ef8d8$1@forums-1-dub>
Date: 19 Oct 2011 09:20:40 -0700
X-Trace: forums-1-dub 1319041240 10.22.241.152 (19 Oct 2011 09:20:40 -0700)
X-Original-Trace: 19 Oct 2011 09:20:40 -0700, vip152.sybase.com
Lines: 71
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13001
Article PK: 91450


On 10/18/2011 10:50 PM, Michael Peppler [Team Sybase] wrote:
> On Tue, 18 Oct 2011 16:05:02 -0700, Bret Halford wrote:
>
>> Try using the "with consumers" clause to involve worker processes that
>> will use those other engines.
>>
>> On such a large table, you might also want to use "with sampling" to speed
>> things up.
>
> Hi Bret,
>
> Would "with sampling" actually improve the basic "update statistics"
> command that Isabella is running?
>
> I thought that the "sampling" bit only affected the collection of stats
> for the non-leading index columns calculated with "update index
> statistics" (and I guess any direct column level stats calculation not
> involving an index such as update all statistics).
>
> Michael
>

Hi Michael,

The docs say it applies to all flavors of update stats except
(the exception is buried under 'usage') update statistics tablename
indexname, which is probably what you are thinking of.

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36272.1570/html/commands/CIHFHDEF.htm


----------------------
with sampling = N percent

specifies the percentage of the column to be randomly sampled in
order to gather statistics. The value for N is any number between 1 and
100. Sampling applies to all update statistics types:

update statistics table_name

update statistics table_name (col_name)

update index statistics

update all statistics

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

[...]
Usage
[...]
update statistics and sampling

Sampling is not performed for leading columns of indexes. If you specify
an index in update statistics, such as in the following:

update statistics table_name [index_name] with sampling = N percent

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

I will admit that, not being a dba, I don't personally run Update Stats
often. But if sampling doesn't help, that would be behavior contrary
to the docs, and so something I'd look into if it was reported
as a possible bug.

I'll also note that my understanding is the "update statistics table"
is generally considered overkill; "update index statistics table"
is usually good enough.

Cheers,
-bret


Isabella Posted on 2011-10-19 17:14:36.0Z
Message-ID: <4E9F057B.1080100@nrc-cnrc.gc.ca>
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 2.0.0.19 (X11/20090105)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
To: Bret Halford <bret@sybase.com>
Subject: Re: ASE 15.5 update stats
References: <4e9de4b9@forums-1-dub> <4e9deb28$1@forums-1-dub> <4e9decff$1@forums-1-dub> <4e9e061e$1@forums-1-dub> <pan.2011.10.19.04.50.57.300397@peppler.org> <4e9ef8d8$1@forums-1-dub>
In-Reply-To: <4e9ef8d8$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Oct 2011 10:14:36 -0700
X-Trace: forums-1-dub 1319044476 10.22.241.152 (19 Oct 2011 10:14:36 -0700)
X-Original-Trace: 19 Oct 2011 10:14:36 -0700, vip152.sybase.com
Lines: 85
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13003
Article PK: 91453

One more unrelated issues I see here from looking into same sp_sysmon
output is that tempdb_sa_cache is not been used by updates stats which
I run as user : sa. I was expecting to see tempdb_sa_cache been used
for running sa jobs , any thoughts what I'm missing ?
Isabella




Isabella

Bret Halford wrote:
> On 10/18/2011 10:50 PM, Michael Peppler [Team Sybase] wrote:
>> On Tue, 18 Oct 2011 16:05:02 -0700, Bret Halford wrote:
>>
>>> Try using the "with consumers" clause to involve worker processes that
>>> will use those other engines.
>>>
>>> On such a large table, you might also want to use "with sampling" to
>>> speed
>>> things up.
>>
>> Hi Bret,
>>
>> Would "with sampling" actually improve the basic "update statistics"
>> command that Isabella is running?
>>
>> I thought that the "sampling" bit only affected the collection of stats
>> for the non-leading index columns calculated with "update index
>> statistics" (and I guess any direct column level stats calculation not
>> involving an index such as update all statistics).
>>
>> Michael
>>
>
> Hi Michael,
>
> The docs say it applies to all flavors of update stats except
> (the exception is buried under 'usage') update statistics tablename
> indexname, which is probably what you are thinking of.
>
> http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36272.1570/html/commands/CIHFHDEF.htm
>
>
> ----------------------
> with sampling = N percent
>
> specifies the percentage of the column to be randomly sampled in
> order to gather statistics. The value for N is any number between 1 and
> 100. Sampling applies to all update statistics types:
>
> update statistics table_name
>
> update statistics table_name (col_name)
>
> update index statistics
>
> update all statistics
>
> -----------------------
>
> [...]
> Usage
> [...]
> update statistics and sampling
>
> Sampling is not performed for leading columns of indexes. If you specify
> an index in update statistics, such as in the following:
>
> update statistics table_name [index_name] with sampling = N percent
>
> ---------------
>
> I will admit that, not being a dba, I don't personally run Update Stats
> often. But if sampling doesn't help, that would be behavior contrary
> to the docs, and so something I'd look into if it was reported
> as a possible bug.
>
> I'll also note that my understanding is the "update statistics table"
> is generally considered overkill; "update index statistics table"
> is usually good enough.
>
> Cheers,
> -bret


Cory Sane [TeamSybase] Posted on 2011-10-28 02:58:19.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.performance+tuning
References: <4e9de4b9@forums-1-dub> <4e9deb28$1@forums-1-dub> <4e9decff$1@forums-1-dub> <4e9e061e$1@forums-1-dub> <pan.2011.10.19.04.50.57.300397@peppler.org> <4e9ef8d8$1@forums-1-dub> <4E9F057B.1080100@nrc-cnrc.gc.ca>
In-Reply-To: <4E9F057B.1080100@nrc-cnrc.gc.ca>
Subject: Re: ASE 15.5 update stats
Lines: 94
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18197
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18463
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4eaa1a4b$1@forums-1-dub>
Date: 27 Oct 2011 19:58:19 -0700
X-Trace: forums-1-dub 1319770699 10.22.241.152 (27 Oct 2011 19:58:19 -0700)
X-Original-Trace: 27 Oct 2011 19:58:19 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13014
Article PK: 91464

I have found that tempdbs (and the tempdb bound cache) are not used on regular "update statistics"...
but if you did "update index statistics" you would see the tempdb & the tempdb bound cache used for the non leading columns.


--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message news:4E9F057B.1080100@nrc-cnrc.gc.ca...
>
>
> One more unrelated issues I see here from looking into same sp_sysmon
> output is that tempdb_sa_cache is not been used by updates stats which
> I run as user : sa. I was expecting to see tempdb_sa_cache been used
> for running sa jobs , any thoughts what I'm missing ?
> Isabella
>
>
>
>
> Isabella
> Bret Halford wrote:
>> On 10/18/2011 10:50 PM, Michael Peppler [Team Sybase] wrote:
>>> On Tue, 18 Oct 2011 16:05:02 -0700, Bret Halford wrote:
>>>
>>>> Try using the "with consumers" clause to involve worker processes that
>>>> will use those other engines.
>>>>
>>>> On such a large table, you might also want to use "with sampling" to
>>>> speed
>>>> things up.
>>>
>>> Hi Bret,
>>>
>>> Would "with sampling" actually improve the basic "update statistics"
>>> command that Isabella is running?
>>>
>>> I thought that the "sampling" bit only affected the collection of stats
>>> for the non-leading index columns calculated with "update index
>>> statistics" (and I guess any direct column level stats calculation not
>>> involving an index such as update all statistics).
>>>
>>> Michael
>>>
>>
>> Hi Michael,
>>
>> The docs say it applies to all flavors of update stats except
>> (the exception is buried under 'usage') update statistics tablename
>> indexname, which is probably what you are thinking of.
>>
>> http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36272.1570/html/commands/CIHFHDEF.htm
>>
>>
>> ----------------------
>> with sampling = N percent
>>
>> specifies the percentage of the column to be randomly sampled in
>> order to gather statistics. The value for N is any number between 1 and
>> 100. Sampling applies to all update statistics types:
>>
>> update statistics table_name
>>
>> update statistics table_name (col_name)
>>
>> update index statistics
>>
>> update all statistics
>>
>> -----------------------
>>
>> [...]
>> Usage
>> [...]
>> update statistics and sampling
>>
>> Sampling is not performed for leading columns of indexes. If you specify
>> an index in update statistics, such as in the following:
>>
>> update statistics table_name [index_name] with sampling = N percent
>>
>> ---------------
>>
>> I will admit that, not being a dba, I don't personally run Update Stats
>> often. But if sampling doesn't help, that would be behavior contrary
>> to the docs, and so something I'd look into if it was reported
>> as a possible bug.
>>
>> I'll also note that my understanding is the "update statistics table"
>> is generally considered overkill; "update index statistics table"
>> is usually good enough.
>>
>> Cheers,
>> -bret


Sherlock, Kevin [TeamSybase] Posted on 2011-10-28 14:10:56.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <4e9de4b9@forums-1-dub> <4e9deb28$1@forums-1-dub> <4e9decff$1@forums-1-dub> <4e9e061e$1@forums-1-dub> <pan.2011.10.19.04.50.57.300397@peppler.org> <4e9ef8d8$1@forums-1-dub> <4E9F057B.1080100@nrc-cnrc.gc.ca> <4eaa1a4b$1@forums-1-dub>
Subject: Re: ASE 15.5 update stats
Lines: 104
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4eaab7f0$1@forums-1-dub>
Date: 28 Oct 2011 07:10:56 -0700
X-Trace: forums-1-dub 1319811056 10.22.241.152 (28 Oct 2011 07:10:56 -0700)
X-Original-Trace: 28 Oct 2011 07:10:56 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13015
Article PK: 91462

Right. Tempdb (or user defined tempdb) is used for sorting and worktable
creation for gathering stats for non-leading index columns. Leading columns
of indexes are already sorted, so no need for that step to happen in tempdb.
No surprise, right?

"Cory Sane [TeamSybase]" <cory!=sane> wrote in message
news:4eaa1a4b$1@forums-1-dub...
>I have found that tempdbs (and the tempdb bound cache) are not used on
>regular "update statistics"... but if you did "update index statistics" you
>would see the tempdb & the tempdb bound cache used for the non leading
>columns.
>
>
> --
> Cory Sane
> [TeamSybase]
> Certified Sybase Associate DBA for ASE 15.0
> "Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
> news:4E9F057B.1080100@nrc-cnrc.gc.ca...
>>
>>
>> One more unrelated issues I see here from looking into same sp_sysmon
>> output is that tempdb_sa_cache is not been used by updates stats which I
>> run as user : sa. I was expecting to see tempdb_sa_cache been used for
>> running sa jobs , any thoughts what I'm missing ?
>> Isabella
>>
>>
>>
>>
>> Isabella
>> Bret Halford wrote:
>>> On 10/18/2011 10:50 PM, Michael Peppler [Team Sybase] wrote:
>>>> On Tue, 18 Oct 2011 16:05:02 -0700, Bret Halford wrote:
>>>>
>>>>> Try using the "with consumers" clause to involve worker processes that
>>>>> will use those other engines.
>>>>>
>>>>> On such a large table, you might also want to use "with sampling" to
>>>>> speed
>>>>> things up.
>>>>
>>>> Hi Bret,
>>>>
>>>> Would "with sampling" actually improve the basic "update statistics"
>>>> command that Isabella is running?
>>>>
>>>> I thought that the "sampling" bit only affected the collection of stats
>>>> for the non-leading index columns calculated with "update index
>>>> statistics" (and I guess any direct column level stats calculation not
>>>> involving an index such as update all statistics).
>>>>
>>>> Michael
>>>>
>>>
>>> Hi Michael,
>>>
>>> The docs say it applies to all flavors of update stats except
>>> (the exception is buried under 'usage') update statistics tablename
>>> indexname, which is probably what you are thinking of.
>>>
>>> http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36272.1570/html/commands/CIHFHDEF.htm -
>>> ---------------------
>>> with sampling = N percent
>>>
>>> specifies the percentage of the column to be randomly sampled in
>>> order to gather statistics. The value for N is any number between 1 and
>>> 100. Sampling applies to all update statistics types:
>>>
>>> update statistics table_name
>>>
>>> update statistics table_name (col_name)
>>>
>>> update index statistics
>>>
>>> update all statistics
>>>
>>> -----------------------
>>>
>>> [...]
>>> Usage
>>> [...]
>>> update statistics and sampling
>>>
>>> Sampling is not performed for leading columns of indexes. If you specify
>>> an index in update statistics, such as in the following:
>>>
>>> update statistics table_name [index_name] with sampling = N percent
>>>
>>> ---------------
>>>
>>> I will admit that, not being a dba, I don't personally run Update Stats
>>> often. But if sampling doesn't help, that would be behavior contrary
>>> to the docs, and so something I'd look into if it was reported
>>> as a possible bug.
>>>
>>> I'll also note that my understanding is the "update statistics table"
>>> is generally considered overkill; "update index statistics table"
>>> is usually good enough.
>>>
>>> Cheers,
>>> -bret


Sherlock, Kevin [TeamSybase] Posted on 2011-10-27 22:19:29.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <4e9de4b9@forums-1-dub> <4e9deb28$1@forums-1-dub> <4e9decff$1@forums-1-dub> <4e9e061e$1@forums-1-dub> <pan.2011.10.19.04.50.57.300397@peppler.org> <4e9ef8d8$1@forums-1-dub> <4E9F057B.1080100@nrc-cnrc.gc.ca>
Subject: Re: ASE 15.5 update stats
Lines: 104
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ea9d8f1$1@forums-1-dub>
Date: 27 Oct 2011 15:19:29 -0700
X-Trace: forums-1-dub 1319753969 10.22.241.152 (27 Oct 2011 15:19:29 -0700)
X-Original-Trace: 27 Oct 2011 15:19:29 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13013
Article PK: 91466

Is the object that you are updating stats for bound to a cache named
"tempdb_sa_cache" ? The only reason ASE should use a specific CACHE for
update stats is because the either the table/indexes are bound to such a
named cache, or there is a tempdb database (user or system) bound to such a
named cache.

So, you need to check:

1. Is your table and/or any indexes bound to a named cache?
2. Is your login/application bound to a user defined tempdb?
3. Using the answer to (2), is the tempdb being used bound to a named
cache?

Are you updating stats for non-leading columns of an index or columns that
aren't indexed? In other words, are you using an update statistics command
that NEEDS to use tempdb?

"Isabella" <isabella.ghiurea@nrc-cnrc.gc.ca> wrote in message
news:4E9F057B.1080100@nrc-cnrc.gc.ca...
>
>
> One more unrelated issues I see here from looking into same sp_sysmon
> output is that tempdb_sa_cache is not been used by updates stats which I
> run as user : sa. I was expecting to see tempdb_sa_cache been used for
> running sa jobs , any thoughts what I'm missing ?
> Isabella
>
>
>
>
> Isabella
> Bret Halford wrote:
>> On 10/18/2011 10:50 PM, Michael Peppler [Team Sybase] wrote:
>>> On Tue, 18 Oct 2011 16:05:02 -0700, Bret Halford wrote:
>>>
>>>> Try using the "with consumers" clause to involve worker processes that
>>>> will use those other engines.
>>>>
>>>> On such a large table, you might also want to use "with sampling" to
>>>> speed
>>>> things up.
>>>
>>> Hi Bret,
>>>
>>> Would "with sampling" actually improve the basic "update statistics"
>>> command that Isabella is running?
>>>
>>> I thought that the "sampling" bit only affected the collection of stats
>>> for the non-leading index columns calculated with "update index
>>> statistics" (and I guess any direct column level stats calculation not
>>> involving an index such as update all statistics).
>>>
>>> Michael
>>>
>>
>> Hi Michael,
>>
>> The docs say it applies to all flavors of update stats except
>> (the exception is buried under 'usage') update statistics tablename
>> indexname, which is probably what you are thinking of.
>>
>> http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36272.1570/html/commands/CIHFHDEF.htm -
>> ---------------------
>> with sampling = N percent
>>
>> specifies the percentage of the column to be randomly sampled in
>> order to gather statistics. The value for N is any number between 1 and
>> 100. Sampling applies to all update statistics types:
>>
>> update statistics table_name
>>
>> update statistics table_name (col_name)
>>
>> update index statistics
>>
>> update all statistics
>>
>> -----------------------
>>
>> [...]
>> Usage
>> [...]
>> update statistics and sampling
>>
>> Sampling is not performed for leading columns of indexes. If you specify
>> an index in update statistics, such as in the following:
>>
>> update statistics table_name [index_name] with sampling = N percent
>>
>> ---------------
>>
>> I will admit that, not being a dba, I don't personally run Update Stats
>> often. But if sampling doesn't help, that would be behavior contrary
>> to the docs, and so something I'd look into if it was reported
>> as a possible bug.
>>
>> I'll also note that my understanding is the "update statistics table"
>> is generally considered overkill; "update index statistics table"
>> is usually good enough.
>>
>> Cheers,
>> -bret


vtpcnk Posted on 2011-10-20 14:48:30.0Z
Sender: 7f93.4ea033ff.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: ASE 15.5 update stats
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ea034be.7fac.1681692777@sybase.com>
References: <4e9ef8d8$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 20 Oct 2011 07:48:30 -0700
X-Trace: forums-1-dub 1319122110 10.22.241.41 (20 Oct 2011 07:48:30 -0700)
X-Original-Trace: 20 Oct 2011 07:48:30 -0700, 10.22.241.41
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13004
Article PK: 91454


>But if sampling doesn't help, that would be behavior
contrary
>to the docs, and so something I'd look into if it was
reported
>as a possible bug.

i remember using the sampling option a few years back. if i
remember right we found that it degraded the performance.

any feedback on others who have tried it?


Sherlock, Kevin [TeamSybase] Posted on 2011-10-25 19:32:24.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <4e9ef8d8$1@forums-1-dub> <4ea034be.7fac.1681692777@sybase.com>
Subject: Re: ASE 15.5 update stats
Lines: 24
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ea70ec8$1@forums-1-dub>
Date: 25 Oct 2011 12:32:24 -0700
X-Trace: forums-1-dub 1319571144 10.22.241.152 (25 Oct 2011 12:32:24 -0700)
X-Original-Trace: 25 Oct 2011 12:32:24 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13008
Article PK: 91457

Degrades performance of what? The "update statistics" command itself, or
subsequent query plans?

If it degrades performance of subsequent query plan activity, then you
should increase your sampling percentage until it doesn't. That's a
documented risk to sampling vs the reward for a faster maintenance window.
By how much you need to increase the sampling percentage depends on the
queries written, and the data distribution of the values of your columns.
Only rigorous testing techniques will determine how large a sampling
percentage will work.

<vtpcnk> wrote in message news:4ea034be.7fac.1681692777@sybase.com...
> >But if sampling doesn't help, that would be behavior
> contrary
>>to the docs, and so something I'd look into if it was
> reported
>>as a possible bug.
>
> i remember using the sampling option a few years back. if i
> remember right we found that it degraded the performance.
>
> any feedback on others who have tried it?


Sherlock, Kevin [TeamSybase] Posted on 2011-10-25 19:27:19.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <4e9de4b9@forums-1-dub> <4e9deb28$1@forums-1-dub> <4e9decff$1@forums-1-dub> <4e9e061e$1@forums-1-dub> <pan.2011.10.19.04.50.57.300397@peppler.org> <4e9ef8d8$1@forums-1-dub>
Subject: Re: ASE 15.5 update stats
Lines: 131
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ea70d97$1@forums-1-dub>
Date: 25 Oct 2011 12:27:19 -0700
X-Trace: forums-1-dub 1319570839 10.22.241.152 (25 Oct 2011 12:27:19 -0700)
X-Original-Trace: 25 Oct 2011 12:27:19 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13007
Article PK: 91458

Well, the docs are either wrong, or misleading, or out-of-date.

As of version:
Adaptive Server Enterprise/15.0.3/ESD#2

Sampling ONLY applies when the column level stats (histogram, density, etc)
are gathered for a column which doesn't appear as a leading column in any
other index within the scope of the specific "update stats" command, or a
column which does not participate in any index.

One exception here is that if you use the "column" specific syntax of
"update statistics" such as:

update statistics mytable(col1 [,col2 ...,col<n>]) with sampling = n

In that case, sampling is applied to the first column in the syntax above
(in this case, "col1") regardless if that column is a leading column in one
or more indexes on the table.

So, there are various scenarios here:

update statistics mytable [myindexname] with sampling = 10
---------------------------------------------------
No sampling is applied. All column level stats for columns that appear as
the first column in any index (or the specific index optionally specified)
are updated at 100% "sampling".

update index statistics mytable with sampling = 10
---------------------------------------------------------
Leading column of all indexes (or index optionally supplied) "sampled" at
100%, all other columns that don't appear in ANY other index (because ALL
indexes are implied by the command) as a leading column are sampled at
"10%".

update index statistics mytable myindexname with sampling = 10
------------------------------------------------------------------------
Leading column of index "myindexname" is sampled at 100%, ALL other columns
in "myindexname" are sampled at 10% EVEN IF they might appear as a leading
column in some other index

update all statistics mytable wtih sampling = 10
----------------------------------------------------
Any column which is a leading index is sampled at 100%. All other columns
are sampled at 10%

update statistics mytable(mycol1 [,mycol2 ...,mycol<n>]) with sampling = 10
----------------------------------------------------------------------------------------
Column level stats for "mycol1" are sampled at 10% regardless if "mycol1" is
a leading column in any index.

Adding further confusion is the fact that when gathering column level stats
for a non-leading index key column, ASE will use the smallest index (fewest
pages) it can find containing that column to scan. When sampling is
specified, I believe this "index covering" method is then ignored, and the
base table is used for the stats scan. I'll verify this and report back.

"Bret Halford" <bret@sybase.com> wrote in message
news:4e9ef8d8$1@forums-1-dub...
> On 10/18/2011 10:50 PM, Michael Peppler [Team Sybase] wrote:
>> On Tue, 18 Oct 2011 16:05:02 -0700, Bret Halford wrote:
>>
>>> Try using the "with consumers" clause to involve worker processes that
>>> will use those other engines.
>>>
>>> On such a large table, you might also want to use "with sampling" to
>>> speed
>>> things up.
>>
>> Hi Bret,
>>
>> Would "with sampling" actually improve the basic "update statistics"
>> command that Isabella is running?
>>
>> I thought that the "sampling" bit only affected the collection of stats
>> for the non-leading index columns calculated with "update index
>> statistics" (and I guess any direct column level stats calculation not
>> involving an index such as update all statistics).
>>
>> Michael
>>
>
> Hi Michael,
>
> The docs say it applies to all flavors of update stats except
> (the exception is buried under 'usage') update statistics tablename
> indexname, which is probably what you are thinking of.
>
> http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36272.1570/html/commands/CIHFHDEF.htm
>
> ----------------------
> with sampling = N percent
>
> specifies the percentage of the column to be randomly sampled in order
> to gather statistics. The value for N is any number between 1 and 100.
> Sampling applies to all update statistics types:
>
> update statistics table_name
>
> update statistics table_name (col_name)
>
> update index statistics
>
> update all statistics
>
> -----------------------
>
> [...]
> Usage
> [...]
> update statistics and sampling
>
> Sampling is not performed for leading columns of indexes. If you specify
> an index in update statistics, such as in the following:
>
> update statistics table_name [index_name] with sampling = N percent
>
> ---------------
>
> I will admit that, not being a dba, I don't personally run Update Stats
> often. But if sampling doesn't help, that would be behavior contrary
> to the docs, and so something I'd look into if it was reported
> as a possible bug.
>
> I'll also note that my understanding is the "update statistics table"
> is generally considered overkill; "update index statistics table"
> is usually good enough.
>
> Cheers,
> -bret


Cory Sane [TeamSybase] Posted on 2011-10-27 04:10:05.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.performance+tuning
References: <4e9de4b9@forums-1-dub> <4e9deb28$1@forums-1-dub> <4e9decff$1@forums-1-dub> <4e9e061e$1@forums-1-dub> <pan.2011.10.19.04.50.57.300397@peppler.org> <4e9ef8d8$1@forums-1-dub> <4ea70d97$1@forums-1-dub>
In-Reply-To: <4ea70d97$1@forums-1-dub>
Subject: Re: ASE 15.5 update stats
Lines: 145
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18197
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18463
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ea8d99d@forums-1-dub>
Date: 26 Oct 2011 21:10:05 -0700
X-Trace: forums-1-dub 1319688605 10.22.241.152 (26 Oct 2011 21:10:05 -0700)
X-Original-Trace: 26 Oct 2011 21:10:05 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13010
Article PK: 91465

Kevin,
Your last paragraph was most important...

"With sampling" causes table scans...
without sampling allows the indexes to be read.

On large tables with small indexes... it can be the difference between many hours of i/o.


--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com> wrote in message news:4ea70d97$1@forums-1-dub...
> Well, the docs are either wrong, or misleading, or out-of-date.
>
> As of version:
> Adaptive Server Enterprise/15.0.3/ESD#2
>
> Sampling ONLY applies when the column level stats (histogram, density, etc)
> are gathered for a column which doesn't appear as a leading column in any
> other index within the scope of the specific "update stats" command, or a
> column which does not participate in any index.
>
> One exception here is that if you use the "column" specific syntax of
> "update statistics" such as:
>
> update statistics mytable(col1 [,col2 ...,col<n>]) with sampling = n
>
> In that case, sampling is applied to the first column in the syntax above
> (in this case, "col1") regardless if that column is a leading column in one
> or more indexes on the table.
>
> So, there are various scenarios here:
>
> update statistics mytable [myindexname] with sampling = 10
> ---------------------------------------------------
> No sampling is applied. All column level stats for columns that appear as
> the first column in any index (or the specific index optionally specified)
> are updated at 100% "sampling".
>
> update index statistics mytable with sampling = 10
> ---------------------------------------------------------
> Leading column of all indexes (or index optionally supplied) "sampled" at
> 100%, all other columns that don't appear in ANY other index (because ALL
> indexes are implied by the command) as a leading column are sampled at
> "10%".
>
> update index statistics mytable myindexname with sampling = 10
> ------------------------------------------------------------------------
> Leading column of index "myindexname" is sampled at 100%, ALL other columns
> in "myindexname" are sampled at 10% EVEN IF they might appear as a leading
> column in some other index
>
> update all statistics mytable wtih sampling = 10
> ----------------------------------------------------
> Any column which is a leading index is sampled at 100%. All other columns
> are sampled at 10%
>
> update statistics mytable(mycol1 [,mycol2 ...,mycol<n>]) with sampling = 10
> ----------------------------------------------------------------------------------------
> Column level stats for "mycol1" are sampled at 10% regardless if "mycol1" is
> a leading column in any index.
>
> Adding further confusion is the fact that when gathering column level stats
> for a non-leading index key column, ASE will use the smallest index (fewest
> pages) it can find containing that column to scan. When sampling is
> specified, I believe this "index covering" method is then ignored, and the
> base table is used for the stats scan. I'll verify this and report back.
>
> "Bret Halford" <bret@sybase.com> wrote in message
> news:4e9ef8d8$1@forums-1-dub...
>> On 10/18/2011 10:50 PM, Michael Peppler [Team Sybase] wrote:
>>> On Tue, 18 Oct 2011 16:05:02 -0700, Bret Halford wrote:
>>>
>>>> Try using the "with consumers" clause to involve worker processes that
>>>> will use those other engines.
>>>>
>>>> On such a large table, you might also want to use "with sampling" to
>>>> speed
>>>> things up.
>>>
>>> Hi Bret,
>>>
>>> Would "with sampling" actually improve the basic "update statistics"
>>> command that Isabella is running?
>>>
>>> I thought that the "sampling" bit only affected the collection of stats
>>> for the non-leading index columns calculated with "update index
>>> statistics" (and I guess any direct column level stats calculation not
>>> involving an index such as update all statistics).
>>>
>>> Michael
>>>
>>
>> Hi Michael,
>>
>> The docs say it applies to all flavors of update stats except
>> (the exception is buried under 'usage') update statistics tablename
>> indexname, which is probably what you are thinking of.
>>
>> http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36272.1570/html/commands/CIHFHDEF.htm
>>
>> ----------------------
>> with sampling = N percent
>>
>> specifies the percentage of the column to be randomly sampled in order
>> to gather statistics. The value for N is any number between 1 and 100.
>> Sampling applies to all update statistics types:
>>
>> update statistics table_name
>>
>> update statistics table_name (col_name)
>>
>> update index statistics
>>
>> update all statistics
>>
>> -----------------------
>>
>> [...]
>> Usage
>> [...]
>> update statistics and sampling
>>
>> Sampling is not performed for leading columns of indexes. If you specify
>> an index in update statistics, such as in the following:
>>
>> update statistics table_name [index_name] with sampling = N percent
>>
>> ---------------
>>
>> I will admit that, not being a dba, I don't personally run Update Stats
>> often. But if sampling doesn't help, that would be behavior contrary
>> to the docs, and so something I'd look into if it was reported
>> as a possible bug.
>>
>> I'll also note that my understanding is the "update statistics table"
>> is generally considered overkill; "update index statistics table"
>> is usually good enough.
>>
>> Cheers,
>> -bret
>
>


Sherlock, Kevin [TeamSybase] Posted on 2011-10-27 13:16:39.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <4e9de4b9@forums-1-dub> <4e9deb28$1@forums-1-dub> <4e9decff$1@forums-1-dub> <4e9e061e$1@forums-1-dub> <pan.2011.10.19.04.50.57.300397@peppler.org> <4e9ef8d8$1@forums-1-dub> <4ea70d97$1@forums-1-dub> <4ea8d99d@forums-1-dub>
Subject: Re: ASE 15.5 update stats
Lines: 174
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ea959b7$1@forums-1-dub>
Date: 27 Oct 2011 06:16:39 -0700
X-Trace: forums-1-dub 1319721399 10.22.241.152 (27 Oct 2011 06:16:39 -0700)
X-Original-Trace: 27 Oct 2011 06:16:39 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13011
Article PK: 91460

I haven't verified if that is the way it works yet. But, even so, if your
smallest index is 20% the size of your table, I would assume a 20% sampling
rate of the base table might help to mitigate this difference in io.

What is also a factor is how many physical io's have to be done to
accomplish the stats gathering. Even if an index is used, if there is a
large difference in how many physical io's are done vs a table scan
consisting of mostly cache hits, that can be a factor as well.

Sampling has it's drawbacks, and I would do everything I could to avoid
them. Biggest drawback in my mind is the fact that the density numbers are
not updated after sampling (only a new histogram is generated). Usually, a
sampling generated histogram is sufficient (not always, but usually).

Creating the fastest method of updating statistics has always been somewhat
of an art form. Even more so now with the ability to update stats for
partitions and the fact that there is the notion of index covering for this
operation. During a maintenance window (if you even have one), DBA's can
leverage their knowledge about how this all works to use as many resources
as possible, and create an "update stats" job that takes the minimal amount
of elapsed time without sacrificing accuracy and effectiveness of statistics
wrt their query plans.

"Cory Sane [TeamSybase]" <cory!=sane> wrote in message
news:4ea8d99d@forums-1-dub...
> Kevin,
> Your last paragraph was most important...
>
> "With sampling" causes table scans...
> without sampling allows the indexes to be read.
>
> On large tables with small indexes... it can be the difference between
> many hours of i/o.
>
>
> --
> Cory Sane
> [TeamSybase]
> Certified Sybase Associate DBA for ASE 15.0
> "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com> wrote in
> message news:4ea70d97$1@forums-1-dub...
>> Well, the docs are either wrong, or misleading, or out-of-date.
>>
>> As of version:
>> Adaptive Server Enterprise/15.0.3/ESD#2
>>
>> Sampling ONLY applies when the column level stats (histogram, density,
>> etc) are gathered for a column which doesn't appear as a leading column
>> in any other index within the scope of the specific "update stats"
>> command, or a column which does not participate in any index.
>>
>> One exception here is that if you use the "column" specific syntax of
>> "update statistics" such as:
>>
>> update statistics mytable(col1 [,col2 ...,col<n>]) with sampling = n
>>
>> In that case, sampling is applied to the first column in the syntax above
>> (in this case, "col1") regardless if that column is a leading column in
>> one or more indexes on the table.
>>
>> So, there are various scenarios here:
>>
>> update statistics mytable [myindexname] with sampling = 10
>> ---------------------------------------------------
>> No sampling is applied. All column level stats for columns that appear
>> as the first column in any index (or the specific index optionally
>> specified) are updated at 100% "sampling".
>>
>> update index statistics mytable with sampling = 10
>> ---------------------------------------------------------
>> Leading column of all indexes (or index optionally supplied) "sampled" at
>> 100%, all other columns that don't appear in ANY other index (because ALL
>> indexes are implied by the command) as a leading column are sampled at
>> "10%".
>>
>> update index statistics mytable myindexname with sampling = 10
>> ------------------------------------------------------------------------
>> Leading column of index "myindexname" is sampled at 100%, ALL other
>> columns in "myindexname" are sampled at 10% EVEN IF they might appear as
>> a leading column in some other index
>>
>> update all statistics mytable wtih sampling = 10
>> ----------------------------------------------------
>> Any column which is a leading index is sampled at 100%. All other
>> columns are sampled at 10%
>>
>> update statistics mytable(mycol1 [,mycol2 ...,mycol<n>]) with sampling =
>> 10
>> ----------------------------------------------------------------------------------------
>> Column level stats for "mycol1" are sampled at 10% regardless if "mycol1"
>> is a leading column in any index.
>>
>> Adding further confusion is the fact that when gathering column level
>> stats for a non-leading index key column, ASE will use the smallest index
>> (fewest pages) it can find containing that column to scan. When sampling
>> is specified, I believe this "index covering" method is then ignored, and
>> the base table is used for the stats scan. I'll verify this and report
>> back.
>>
>> "Bret Halford" <bret@sybase.com> wrote in message
>> news:4e9ef8d8$1@forums-1-dub...
>>> On 10/18/2011 10:50 PM, Michael Peppler [Team Sybase] wrote:
>>>> On Tue, 18 Oct 2011 16:05:02 -0700, Bret Halford wrote:
>>>>
>>>>> Try using the "with consumers" clause to involve worker processes that
>>>>> will use those other engines.
>>>>>
>>>>> On such a large table, you might also want to use "with sampling" to
>>>>> speed
>>>>> things up.
>>>>
>>>> Hi Bret,
>>>>
>>>> Would "with sampling" actually improve the basic "update statistics"
>>>> command that Isabella is running?
>>>>
>>>> I thought that the "sampling" bit only affected the collection of stats
>>>> for the non-leading index columns calculated with "update index
>>>> statistics" (and I guess any direct column level stats calculation not
>>>> involving an index such as update all statistics).
>>>>
>>>> Michael
>>>>
>>>
>>> Hi Michael,
>>>
>>> The docs say it applies to all flavors of update stats except
>>> (the exception is buried under 'usage') update statistics tablename
>>> indexname, which is probably what you are thinking of.
>>>
>>> http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36272.1570/html/commands/CIHFHDEF.htm
>>>
>>> ----------------------
>>> with sampling = N percent
>>>
>>> specifies the percentage of the column to be randomly sampled in
>>> order to gather statistics. The value for N is any number between 1 and
>>> 100. Sampling applies to all update statistics types:
>>>
>>> update statistics table_name
>>>
>>> update statistics table_name (col_name)
>>>
>>> update index statistics
>>>
>>> update all statistics
>>>
>>> -----------------------
>>>
>>> [...]
>>> Usage
>>> [...]
>>> update statistics and sampling
>>>
>>> Sampling is not performed for leading columns of indexes. If you specify
>>> an index in update statistics, such as in the following:
>>>
>>> update statistics table_name [index_name] with sampling = N percent
>>>
>>> ---------------
>>>
>>> I will admit that, not being a dba, I don't personally run Update Stats
>>> often. But if sampling doesn't help, that would be behavior contrary
>>> to the docs, and so something I'd look into if it was reported
>>> as a possible bug.
>>>
>>> I'll also note that my understanding is the "update statistics table"
>>> is generally considered overkill; "update index statistics table"
>>> is usually good enough.
>>>
>>> Cheers,
>>> -bret
>>


Sherlock, Kevin [TeamSybase] Posted on 2011-10-28 16:20:40.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <4e9de4b9@forums-1-dub> <4e9deb28$1@forums-1-dub> <4e9decff$1@forums-1-dub> <4e9e061e$1@forums-1-dub> <pan.2011.10.19.04.50.57.300397@peppler.org> <4e9ef8d8$1@forums-1-dub> <4ea70d97$1@forums-1-dub> <4ea8d99d@forums-1-dub>
Subject: Re: ASE 15.5 update stats
Lines: 162
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4eaad658$1@forums-1-dub>
Date: 28 Oct 2011 09:20:40 -0700
X-Trace: forums-1-dub 1319818840 10.22.241.152 (28 Oct 2011 09:20:40 -0700)
X-Original-Trace: 28 Oct 2011 09:20:40 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13016
Article PK: 91463

I verified this behavior in ASE 15.0.3 ESD#2.

Whenever sampling is requested, all column stats gathering/scanning for
non-leading index columns are scanned from the base table, rather than any
smaller index that contains that column.

Again, that doesn't imply that it will be slower, or less io bound because
it depends on the sampling rate requested and the state of cache at runtime.
But, it could be argued that this might be another disadvantage to sampling
in some cases.

"Cory Sane [TeamSybase]" <cory!=sane> wrote in message
news:4ea8d99d@forums-1-dub...
> Kevin,
> Your last paragraph was most important...
>
> "With sampling" causes table scans...
> without sampling allows the indexes to be read.
>
> On large tables with small indexes... it can be the difference between
> many hours of i/o.
>
>
> --
> Cory Sane
> [TeamSybase]
> Certified Sybase Associate DBA for ASE 15.0
> "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com> wrote in
> message news:4ea70d97$1@forums-1-dub...
>> Well, the docs are either wrong, or misleading, or out-of-date.
>>
>> As of version:
>> Adaptive Server Enterprise/15.0.3/ESD#2
>>
>> Sampling ONLY applies when the column level stats (histogram, density,
>> etc) are gathered for a column which doesn't appear as a leading column
>> in any other index within the scope of the specific "update stats"
>> command, or a column which does not participate in any index.
>>
>> One exception here is that if you use the "column" specific syntax of
>> "update statistics" such as:
>>
>> update statistics mytable(col1 [,col2 ...,col<n>]) with sampling = n
>>
>> In that case, sampling is applied to the first column in the syntax above
>> (in this case, "col1") regardless if that column is a leading column in
>> one or more indexes on the table.
>>
>> So, there are various scenarios here:
>>
>> update statistics mytable [myindexname] with sampling = 10
>> ---------------------------------------------------
>> No sampling is applied. All column level stats for columns that appear
>> as the first column in any index (or the specific index optionally
>> specified) are updated at 100% "sampling".
>>
>> update index statistics mytable with sampling = 10
>> ---------------------------------------------------------
>> Leading column of all indexes (or index optionally supplied) "sampled" at
>> 100%, all other columns that don't appear in ANY other index (because ALL
>> indexes are implied by the command) as a leading column are sampled at
>> "10%".
>>
>> update index statistics mytable myindexname with sampling = 10
>> ------------------------------------------------------------------------
>> Leading column of index "myindexname" is sampled at 100%, ALL other
>> columns in "myindexname" are sampled at 10% EVEN IF they might appear as
>> a leading column in some other index
>>
>> update all statistics mytable wtih sampling = 10
>> ----------------------------------------------------
>> Any column which is a leading index is sampled at 100%. All other
>> columns are sampled at 10%
>>
>> update statistics mytable(mycol1 [,mycol2 ...,mycol<n>]) with sampling =
>> 10
>> ----------------------------------------------------------------------------------------
>> Column level stats for "mycol1" are sampled at 10% regardless if "mycol1"
>> is a leading column in any index.
>>
>> Adding further confusion is the fact that when gathering column level
>> stats for a non-leading index key column, ASE will use the smallest index
>> (fewest pages) it can find containing that column to scan. When sampling
>> is specified, I believe this "index covering" method is then ignored, and
>> the base table is used for the stats scan. I'll verify this and report
>> back.
>>
>> "Bret Halford" <bret@sybase.com> wrote in message
>> news:4e9ef8d8$1@forums-1-dub...
>>> On 10/18/2011 10:50 PM, Michael Peppler [Team Sybase] wrote:
>>>> On Tue, 18 Oct 2011 16:05:02 -0700, Bret Halford wrote:
>>>>
>>>>> Try using the "with consumers" clause to involve worker processes that
>>>>> will use those other engines.
>>>>>
>>>>> On such a large table, you might also want to use "with sampling" to
>>>>> speed
>>>>> things up.
>>>>
>>>> Hi Bret,
>>>>
>>>> Would "with sampling" actually improve the basic "update statistics"
>>>> command that Isabella is running?
>>>>
>>>> I thought that the "sampling" bit only affected the collection of stats
>>>> for the non-leading index columns calculated with "update index
>>>> statistics" (and I guess any direct column level stats calculation not
>>>> involving an index such as update all statistics).
>>>>
>>>> Michael
>>>>
>>>
>>> Hi Michael,
>>>
>>> The docs say it applies to all flavors of update stats except
>>> (the exception is buried under 'usage') update statistics tablename
>>> indexname, which is probably what you are thinking of.
>>>
>>> http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36272.1570/html/commands/CIHFHDEF.htm
>>>
>>> ----------------------
>>> with sampling = N percent
>>>
>>> specifies the percentage of the column to be randomly sampled in
>>> order to gather statistics. The value for N is any number between 1 and
>>> 100. Sampling applies to all update statistics types:
>>>
>>> update statistics table_name
>>>
>>> update statistics table_name (col_name)
>>>
>>> update index statistics
>>>
>>> update all statistics
>>>
>>> -----------------------
>>>
>>> [...]
>>> Usage
>>> [...]
>>> update statistics and sampling
>>>
>>> Sampling is not performed for leading columns of indexes. If you specify
>>> an index in update statistics, such as in the following:
>>>
>>> update statistics table_name [index_name] with sampling = N percent
>>>
>>> ---------------
>>>
>>> I will admit that, not being a dba, I don't personally run Update Stats
>>> often. But if sampling doesn't help, that would be behavior contrary
>>> to the docs, and so something I'd look into if it was reported
>>> as a possible bug.
>>>
>>> I'll also note that my understanding is the "update statistics table"
>>> is generally considered overkill; "update index statistics table"
>>> is usually good enough.
>>>
>>> Cheers,
>>> -bret
>>