------------------------ Execute ------------------------
===============================================================================
Sybase Adaptive Server Enterprise System Performance Report
===============================================================================

Server Version: Adaptive Server Enterprise/12.5.4/EBF 16800 ESD#10/P/x86
Server Name: BASIS
Run Date: Mar 06, 2014
Sampling Started at: Mar 06, 2014 08:57:02
Sampling Ended at: Mar 06, 2014 09:07:02
Sample Interval: 00:10:00
Sample Mode: Reset Counters

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

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 38.8 % 14.5 % 46.7 %
Engine 1 20.1 % 12.3 % 67.5 %
Engine 2 23.1 % 2.4 % 74.5 %
------------------------ -------- -------- --------
Summary Total 82.0 % 29.2 % 188.8 %
Average 27.3 % 9.7 % 62.9 %

CPU Yields by Engine per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 585.4 225.0 351249 66.8 %
Engine 1 131.1 50.4 78676 15.0 %
Engine 2 159.8 61.4 95878 18.2 %
------------------------- ------------ ------------ ----------
Total CPU Yields 876.3 336.8 525803

Network Checks
Non-Blocking 288579.4 110921.0 173147650 99.7 %
Blocking 876.3 336.8 525801 0.3 %
------------------------- ------------ ------------ ----------
Total Network I/O Checks 289455.8 111257.8 173673451
Avg Net I/Os per Check n/a n/a 0.00009 n/a

Disk I/O Checks
Total Disk I/O Checks 292419.7 112397.0 175451795 n/a
Checks Returning I/O 274510.5 105513.3 164706313 93.9 %
Avg Disk I/Os Returned n/a n/a 0.00321 n/a


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

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


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

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.1 0.0 45 n/a

Task Context Switches by Engine
Engine 0 534.2 205.3 320547 17.9 %
Engine 1 2409.4 926.1 1445617 80.7 %
Engine 2 40.4 15.5 24244 1.4 %
------------------------- ------------ ------------ ----------
Total Task Switches: 2984.0 1147.0 1790408

Task Context Switches Due To:
Voluntary Yields 9.1 3.5 5470 0.3 %
Cache Search Misses 549.6 211.3 329775 18.4 %
System Disk Writes 1.9 0.7 1120 0.1 %
I/O Pacing 4.7 1.8 2809 0.2 %
Logical Lock Contention 0.1 0.0 54 0.0 %
Address Lock Contention 0.0 0.0 0 0.0 %
Latch Contention 0.0 0.0 0 0.0 %
Log Semaphore Contention 0.0 0.0 2 0.0 %
PLC Lock Contention 0.0 0.0 0 0.0 %
Group Commit Sleeps 0.1 0.0 44 0.0 %
Last Log Page Writes 2.3 0.9 1353 0.1 %
Modify Conflicts 0.0 0.0 18 0.0 %
I/O Device Contention 0.0 0.0 0 0.0 %
Network Packet Received 10.4 4.0 6249 0.3 %
Network Packet Sent 3.7 1.4 2233 0.1 %
Other Causes 2402.1 923.3 1441281 80.5 %


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

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

Application Statistics Summary (All Applications)
-------------------------------------------------
Priority Changes per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
To High Priority 0.1 0.0 67 5.4 %
To Medium Priority 1.1 0.4 636 51.6 %
To Low Priority 0.9 0.3 530 43.0 %
------------------------- ------------ ------------ ----------
Total Priority Changes 2.1 0.8 1233

Allotted Slices Exhausted per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
High Priority 0.0 0.0 0 0.0 %
Medium Priority 3.9 1.5 2360 100.0 %
Low Priority 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Slices Exhausted 3.9 1.5 2360

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 25.6 9.9 15383 97.5 %
Dirty 0.7 0.3 391 2.5 %
------------ ------------ ----------
Total Washes 26.3 10.1 15774

Garbage Collections 0.6 0.2 348 n/a
Pages Processed in GC 0.0 0.0 0 n/a

Statistics Updates 0.4 0.2 246 n/a

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

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)


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

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

Transaction Summary per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Committed Xacts 2.6 n/a 1561 n/a

Transaction Detail per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Inserts
APL Heap Table 5336.8 2051.3 3202080 99.0 %
APL Clustered Table 51.4 19.8 30869 1.0 %
Data Only Lock Table 0.6 0.2 357 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Inserted 5388.8 2071.3 3233306 99.1 %

Updates
APL Deferred 0.4 0.2 244 65.2 %
APL Direct In-place 0.0 0.0 0 0.0 %
APL Direct Cheap 0.0 0.0 1 0.3 %
APL Direct Expensive 0.0 0.0 0 0.0 %
DOL Deferred 0.1 0.0 48 12.8 %
DOL Direct 0.1 0.1 81 21.7 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Updated 0.6 0.2 374 0.0 %

Data Only Locked Updates
DOL Replace 0.2 0.1 102 79.1 %
DOL Shrink 0.0 0.0 13 10.1 %
DOL Cheap Expand 0.0 0.0 5 3.9 %
DOL Expensive Expand 0.0 0.0 9 7.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.2 0.1 129 0.0 %

Deletes
APL Deferred 45.0 17.3 27004 97.8 %
APL Direct 0.5 0.2 316 1.1 %
DOL 0.5 0.2 290 1.1 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Deleted 46.0 17.7 27610 0.8 %
========================= ============ ============ ==========
Total Rows Affected 5435.5 2089.2 3261290

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

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

ULC Flushes to Xact Log per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
by Full ULC 0.1 0.0 75 0.7 %
by End Transaction 2.9 1.1 1769 16.4 %
by Change of Database 0.2 0.1 97 0.9 %
by Single Log Record 3.2 1.2 1926 17.9 %
by Unpin 11.2 4.3 6722 62.5 %
by Other 0.3 0.1 168 1.6 %
------------------------- ------------ ------------ ----------
Total ULC Flushes 17.9 6.9 10757

ULC Log Records 192.0 73.8 115190 n/a
Max ULC Size During Sample n/a n/a 8192 n/a

ULC Semaphore Requests
Granted 388.1 149.2 232859 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total ULC Semaphore Req 388.1 149.2 232859

Log Semaphore Requests
Granted 22.3 8.6 13355 100.0 %
Waited 0.0 0.0 2 0.0 %
------------------------- ------------ ------------ ----------
Total Log Semaphore Req 22.3 8.6 13357

Transaction Log Writes 20.9 8.0 12565 n/a
Transaction Log Alloc 18.7 7.2 11206 n/a
Avg # Writes per Log Page n/a n/a 1.12127 n/a

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

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

Nonclustered Maintenance per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Ins/Upd Requiring Maint 0.6 0.2 351 n/a
# of NC Ndx Maint 0.6 0.2 351 n/a
Avg NC Ndx Maint / Op n/a n/a 1.00000 n/a

Deletes Requiring Maint 0.5 0.2 283 n/a
# of NC Ndx Maint 0.5 0.2 283 n/a
Avg NC Ndx Maint / Op n/a n/a 1.00000 n/a

RID Upd from Clust Split 0.0 0.0 3 n/a
# of NC Ndx Maint 0.0 0.0 24 n/a
Avg NC Ndx Maint / Op n/a n/a 8.00000 n/a

Upd/Del DOL Req Maint 0.7 0.3 419 n/a
# of DOL Ndx Maint 0.6 0.2 364 n/a
Avg DOL Ndx Maint / Op n/a n/a 0.86874 n/a

Page Splits 0.0 0.0 11 n/a
Retries 0.0 0.0 0 0.0 %
Deadlocks 0.0 0.0 0 0.0 %
Add Index Level 0.0 0.0 0 0.0 %

Page Shrinks 0.0 0.0 14 n/a
Deadlocks %
Deadlock Retries Exceeded 0.0 0.0 0 0.0 %

Index Scans per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Ascending Scans 2428.8 933.6 1457277 1.7 %
DOL Ascending Scans 136769.8 52570.1 82061874 98.3 %
Descending Scans 1.0 0.4 612 0.0 %
DOL Descending Scans 0.0 0.0 6 0.0 %
------------ ------------ ----------
Total Scans 139199.6 53504.0 83519769

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

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

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

Open Object Usage
Active n/a n/a 296 n/a
Max Ever Used Since Boot n/a n/a 607 n/a
Free n/a n/a 3000 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 313 n/a
Max Ever Used Since Boot n/a n/a 594 n/a
Free n/a n/a 2000 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 8 n/a
Max Ever Used Since Boot n/a n/a 8 n/a
Free n/a n/a 12 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 %

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 4622.5 1776.7 2773504 n/a
Avg Lock Contention 0.1 0.0 54 0.0 %
Deadlock Percentage 0.0 0.0 0 0.0 %

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

Table Lock Hashtable
Lookups 94.6 36.4 56767 n/a
Avg Chain Length n/a n/a 0.11302 n/a
Spinlock Contention n/a n/a n/a 0.0 %

Exclusive Table
Granted 7.7 2.9 4590 99.6 %
Waited 0.0 0.0 20 0.4 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Table Requests 7.7 3.0 4610 0.2 %

Shared Table
Granted 1.3 0.5 767 98.8 %
Waited 0.0 0.0 9 1.2 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Table Requests 1.3 0.5 776 0.0 %

Exclusive Intent
Granted 3.5 1.3 2087 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Intent Requests 3.5 1.3 2087 0.1 %

Shared Intent
Granted 80.4 30.9 48220 100.0 %
Waited 0.0 0.0 24 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Intent Requests 80.4 30.9 48244 1.7 %

Page & Row Lock HashTable
Lookups 1141.7 438.8 684997 n/a
Avg Chain Length n/a n/a 0.00830 n/a
Spinlock Contention n/a n/a n/a 0.0 %

Exclusive Page
Granted 18.1 7.0 10870 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Page Requests 18.1 7.0 10870 0.4 %

Update Page
Granted 9.9 3.8 5917 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total UP-Page Requests 9.9 3.8 5917 0.2 %

Shared Page
Granted 793.1 304.8 475856 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Page Requests 793.1 304.8 475856 17.2 %


Exclusive Row
Granted 1.4 0.6 867 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Row Requests 1.4 0.6 867 0.0 %

Update Row
Granted 42.0 16.1 25193 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total UP-Row Requests 42.0 16.1 25193 0.9 %

Shared Row
Granted 140.7 54.1 84448 100.0 %
Waited 0.0 0.0 1 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Row Requests 140.7 54.1 84449 3.0 %


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

Address Lock Hashtable
Lookups 3524.4 1354.7 2114635 n/a
Avg Chain Length n/a n/a 0.00020 n/a
Spinlock Contention n/a n/a n/a 0.0 %

Exclusive Address
Granted 40.2 15.5 24147 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Address Requests 40.2 15.5 24147 0.9 %

Shared Address
Granted 3484.1 1339.2 2090488 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Address Requests 3484.1 1339.2 2090488 75.4 %


Last Page Locks on Heaps
Granted 5336.8 2051.3 3202080 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total Last Pg Locks 5336.8 2051.3 3202080 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 10 n/a
Searches Skipped 0.0 0.0 0 0.0 %
Avg Deadlocks per Search n/a n/a 0.00000 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

Tuning Recommendations for Lock Management
------------------------------------------
- Consider increasing the 'deadlock checking period' parameter
by 50 ms.


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

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

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

Cache Search Summary
Total Cache Hits 312294.4 120036.3 187376664 99.8 %
Total Cache Misses 630.9 242.5 378548 0.2 %
------------------------- ------------ ------------ ----------
Total Cache Searches 312925.4 120278.8 187755212

Cache Turnover
Buffers Grabbed 1896.6 729.0 1137963 n/a
Buffers Grabbed Dirty 0.0 0.0 0 0.0 %

Cache Strategy Summary
Cached (LRU) Buffers 312417.9 120083.8 187450752 100.0 %
Discarded (MRU) Buffers 50.4 19.4 30211 0.0 %

Large I/O Usage
0.0 0.0 0 n/a

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

Asynchronous Prefetch Activity
APFs Issued 228.4 87.8 137052 4.2 %
APFs Denied Due To
APF I/O Overloads 0.0 0.0 7 0.0 %
APF Limit Overloads 0.0 0.0 0 0.0 %
APF Reused Overloads 1.0 0.4 623 0.0 %
APF Buffers Found in Cache
With Spinlock Held 0.0 0.0 0 0.0 %
W/o Spinlock Held 5209.9 2002.5 3125956 95.8 %
------------------------- ------------ ------------ ----------
Total APFs Requested 5439.4 2090.7 3263638

Other Asynchronous Prefetch Statistics
APFs Used 227.2 87.3 136317 n/a
APF Waits for I/O 92.3 35.5 55390 n/a
APF Discards 0.0 0.0 0 n/a

Dirty Read Behavior
Page Requests 1586.3 609.7 951771 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 0.0 %

Cache Searches
Cache Hits 58.6 22.5 35167 99.8 %
Found in Wash 3.3 1.3 1971 5.6 %
Cache Misses 0.1 0.0 68 0.2 %
------------------------- ------------ ------------ ----------
Total Cache Searches 58.7 22.6 35235

Pool Turnover
2 Kb Pool
LRU Buffer Grab 0.1 0.0 68 50.0 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 0.2 0.1 136

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

Cache Strategy
Cached (LRU) Buffers 58.6 22.5 35153 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


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

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

-------------------------------------------------------------------------------
Cache: dev
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

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


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

-------------------------------------------------------------------------------
Cache: kmdat
per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Spinlock Contention n/a n/a n/a 1.0 %

Utilization n/a n/a n/a 94.9 %

Cache Searches
Cache Hits 296544.7 113982.6 177926842 99.8 %
Found in Wash 8.6 3.3 5166 0.0 %
Cache Misses 549.5 211.2 329700 0.2 %
------------------------- ------------ ------------ ----------
Total Cache Searches 297094.2 114193.8 178256542

Pool Turnover
2 Kb Pool
LRU Buffer Grab 801.7 308.2 481049 49.8 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 1608.5 618.2 965073

Buffer Wash Behavior
Buffers Passed Clean 611.7 235.1 366992 99.8 %
Buffers Already in I/O 0.0 0.0 0 0.0 %
Buffers Washed Dirty 1.3 0.5 771 0.2 %

Cache Strategy
Cached (LRU) Buffers 296477.9 113956.9 177886731 100.0 %
Discarded (MRU) Buffers 50.4 19.4 30211 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


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

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

-------------------------------------------------------------------------------
Cache: kmlog
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
Cache Hits 17.1 6.6 10234 100.0 %
Found in Wash 0.0 0.0 0 0.0 %
Cache Misses 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Searches 17.1 6.6 10234

Pool Turnover
------------------------- ------------ ------------ ----------
Total Cache Turnover 16.7 6.4 10021

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

Cache Strategy
Cached (LRU) Buffers 33.3 12.8 19971 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


Tuning Recommendations for Data cache : kmlog
-------------------------------------
- Consider using 'relaxed LRU replacement policy'
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 5.0 %

Cache Searches
Cache Hits 15674.0 6024.6 9404421 99.5 %
Found in Wash 7.6 2.9 4554 0.0 %
Cache Misses 81.3 31.2 48780 0.5 %
------------------------- ------------ ------------ ----------
Total Cache Searches 15755.3 6055.9 9453201

Pool Turnover
2 Kb Pool
LRU Buffer Grab 81.4 31.3 48824 30.0 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 271.2 104.2 162733

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

Cache Strategy
Cached (LRU) Buffers 15848.2 6091.5 9508897 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


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.

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

Procedure Cache Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Procedure Requests 2.7 1.0 1618 n/a
Procedure Reads from Disk 0.6 0.2 336 20.8 %
Procedure Writes to Disk 0.2 0.1 107 6.6 %
Procedure Removals 0.9 0.3 522 n/a
Procedure Recompilations 0.3 0.1 170 n/a

Recompilations Requests:
Execution Phase 0.1 0.0 50 29.4 %
Compilation Phase 0.0 0.0 13 7.6 %
Execute Cursor Execution 0.0 0.0 0 0.0 %
Redefinition Phase 0.2 0.1 107 62.9 %

Recompilation Reasons:
Table Missing 0.3 0.1 168 n/a
Temporary Table Missing 0.1 0.0 61 n/a
Schema Change 0.0 0.0 2 n/a
Index Change 0.0 0.0 0 n/a
Isolation Level Change 0.0 0.0 0 n/a
Permissions Change 0.0 0.0 0 n/a
Cursor Permissions Change 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.9 0.3 528 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 1.0 0.4 579 n/a
Pages Released 1.0 0.4 579 n/a

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

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

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

Avg Time per Normal Chkpt 2.41667 seconds

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

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

Max Outstanding I/Os per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Server n/a n/a 514 n/a
Engine 0 n/a n/a 200 n/a
Engine 1 n/a n/a 513 n/a
Engine 2 n/a n/a 199 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 881.4 338.8 528829

Completed Disk I/O's
Engine 0 477.2 183.4 286345 54.1 %
Engine 1 341.1 131.1 204657 38.7 %
Engine 2 63.1 24.2 37838 7.2 %
------------------------- ------------ ------------ ----------
Total Completed I/Os 881.4 338.8 528840


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

Device:
/dev/mapper/aquarium-basis_dat
basis_dat per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 228.4 87.8 137052 29.0 %
Non-APF 549.4 211.2 329656 69.9 %
Writes 8.7 3.3 5198 1.1 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 786.5 302.3 471906 89.2 %


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

Device:
/dev/mapper/aquarium-basis_log
basis_log per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.1 0.0 40 0.4 %
Writes 18.7 7.2 11247 99.6 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 18.8 7.2 11287 2.1 %


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

Device:
/opt/sybase/data/ivr.dat
ivr_dat 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:
/opt/sybase/data/ivr.log
ivr_log 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:
/opt/sybase/data/km_dev.dat
km_dev 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:
/opt/sybase/data/km_dev1.dat
km_dev1 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:
/opt/sybase/data/master.dat
master per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.0 0.0 15 5.1 %
Writes 0.5 0.2 281 94.9 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.5 0.2 296 0.1 %


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

Device:
/opt/sybase/data/systemdb.dat
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:
/opt/sybase/data/systemprocs.dat
sysprocsdev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.1 0.0 53 100.0 %
Writes 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.1 0.0 53 0.0 %


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

Device:
/opt/sybase/data/tempdb.dat
tempdb per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.0 0.0 2 0.0 %
Writes 73.2 28.1 43894 100.0 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 73.2 28.1 43896 8.3 %


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

Device:
/opt/sybase/tempdb_log.log
tempdb_log per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.0 0.0 5 0.4 %
Writes 2.3 0.9 1379 99.6 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 2.3 0.9 1384 0.3 %


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



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

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

Total Network I/O Requests 14.1 5.4 8482 n/a
Network I/Os Delayed 0.0 0.0 0 0.0 %


Total TDS Packets Received per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 2.4 0.9 1447 23.2 %
Engine 1 7.8 3.0 4688 75.1 %
Engine 2 0.2 0.1 108 1.7 %
------------------------- ------------ ------------ ----------
Total TDS Packets Rec'd 10.4 4.0 6243


Total Bytes Received per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 171.5 65.9 102900 6.5 %
Engine 1 2430.7 934.3 1458430 92.5 %
Engine 2 25.5 9.8 15306 1.0 %
------------------------- ------------ ------------ ----------
Total Bytes Rec'd 2627.7 1010.0 1576636


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

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

Total TDS Packets Sent per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 4.2 1.6 2518 28.4 %
Engine 1 10.4 4.0 6248 70.6 %
Engine 2 0.1 0.1 85 1.0 %
------------------------- ------------ ------------ ----------
Total TDS Packets Sent 14.8 5.7 8851


Total Bytes Sent per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 1189.9 457.4 713968 22.9 %
Engine 1 3980.0 1529.8 2388012 76.6 %
Engine 2 25.9 9.9 15525 0.5 %
------------------------- ------------ ------------ ----------
Total Bytes Sent 5195.8 1997.1 3117505


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

=============================== End of Report =================================
return status = 0
(1 rows affected)
------------------------- Done --------------------------

Result 11:42:15.2040 ------------
Table: PlaceOnPerformance scan count 98, logical reads: (regular=1000
apf=0 total=1000), physical reads: (regular=58 apf=115 total=173), apf
IOs used=80
Table: Performance scan count 12607852, logical reads:
(regular=25902687 apf=0 total=25902687), physical reads: (regular=457
apf=238 total=695), apf IOs used=237
Table: TarOnPerf scan count 1, logical reads: (regular=6054 apf=0
total=6054), physical reads: (regular=868 apf=4545 total=5413), apf
IOs used=4545
Table: SchemeTariffContents scan count 1, logical reads: (regular=11
apf=0 total=11), physical reads: (regular=1 apf=0 total=1), apf IOs
used=0
Table: Show scan count 14819, logical reads: (regular=20802 apf=0
total=20802), physical reads: (regular=0 apf=0 total=0), apf IOs
used=0
Table: Hall scan count 1, logical reads: (regular=1 apf=0 total=1),
physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Building scan count 17, logical reads: (regular=17 apf=0
total=17), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Genre scan count 14819, logical reads: (regular=14819 apf=0
total=14819), physical reads: (regular=0 apf=0 total=0), apf IOs
used=0
Table: Zone scan count 14819, logical reads: (regular=14819 apf=0
total=14819), physical reads: (regular=0 apf=0 total=0), apf IOs
used=0
Table: PlaZoShe scan count 1, logical reads: (regular=40 apf=0
total=40), physical reads: (regular=2 apf=20 total=22), apf IOs
used=20
Table: PlaceInSection scan count 17, logical reads: (regular=122 apf=0
total=122), physical reads: (regular=8 apf=28 total=36), apf IOs
used=26
Table: Worktable1 scan count 0, logical reads: (regular=18458 apf=0
total=18458), physical reads: (regular=0 apf=0 total=0), apf IOs
used=0
Table: Worktable2 scan count 3460, logical reads: (regular=10635
apf=0 total=10635), physical reads: (regular=0 apf=0 total=0), apf IOs
used=0
Table: Worktable3 scan count 3594, logical reads: (regular=10508
apf=0 total=10508), physical reads: (regular=0 apf=0 total=0), apf IOs
used=0
Table: Worktable4 scan count 107369, logical reads: (regular=1187075
apf=0 total=1187075), physical reads: (regular=0 apf=0 total=0), apf
IOs used=0
Total writes for this command: 6225

Execution Time 765.
SQL Server cpu time: 76500 ms. SQL Server elapsed time: 76966 ms.
(117 rows affected)
return status = 0
Total writes for this command: 6225

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 76966 ms.
------------------------- Done --------------------------

------------------------ Execute ------------------------

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is DECLARE.


QUERY PLAN FOR STATEMENT 2 (at line 8).


STEP 1
The type of query is SELECT.


QUERY PLAN FOR STATEMENT 3 (at line 9).


STEP 1
The type of query is SELECT.


QUERY PLAN FOR STATEMENT 4 (at line 10).


STEP 1
The type of query is SELECT.


QUERY PLAN FOR STATEMENT 5 (at line 11).


STEP 1
The type of query is SELECT.


QUERY PLAN FOR STATEMENT 6 (at line 12).


STEP 1
The type of query is SELECT.


QUERY PLAN FOR STATEMENT 7 (at line 13).


STEP 1
The type of query is SELECT.


QUERY PLAN FOR STATEMENT 8 (at line 15).


STEP 1
The type of query is EXECUTE.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.
(1 rows affected)
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.
(1 rows affected)
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.
(1 rows affected)
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.
(1 rows affected)
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.
(1 rows affected)
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.
(1 rows affected)

QUERY PLAN FOR STATEMENT 1 (at line 0).


STEP 1
The type of query is DECLARE.


QUERY PLAN FOR STATEMENT 2 (at line 12).


STEP 1
The type of query is COND.


QUERY PLAN FOR STATEMENT 3 (at line 12).


STEP 1
The type of query is SELECT.


QUERY PLAN FOR STATEMENT 4 (at line 13).


STEP 1
The type of query is COND.


QUERY PLAN FOR STATEMENT 5 (at line 13).


STEP 1
The type of query is SELECT.


QUERY PLAN FOR STATEMENT 6 (at line 15).


STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable2 created for REFORMATTING.

FROM TABLE
plazoshe
pzs
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable2.

STEP 2
The type of query is INSERT.
The update mode is direct.
Worktable3 created for REFORMATTING.

FROM TABLE
SchemeTariffContents
stc
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable3.

STEP 3
The type of query is INSERT.
The update mode is direct.
Worktable4 created for REFORMATTING.

FROM TABLE
TarOnPerf
top
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable4.

STEP 4
The type of query is INSERT.
The update mode is direct.
Worktable1 created, in allpages locking mode, for ORDER BY.

FROM TABLE
hall
h
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
building
b
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
placeinsection
pis
Nested iteration.
Index : SectionWithPlaces_F
Forward scan.
Positioning by key.
Keys are:
IdSection ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
Worktable2.
Nested iteration.
Using Clustered Index.
Forward scan.
Positioning by key.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
Worktable3.
Nested iteration.
Using Clustered Index.
Forward scan.
Positioning by key.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
Worktable4.
Nested iteration.
Using Clustered Index.
Forward scan.
Positioning by key.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
performance
p
Nested iteration.
Index : PerfInHall
Forward scan.
Positioning by key.
Keys are:
IdPerformance ASC
IdHall ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
show
s
Nested iteration.
Using Clustered Index.
Index : PK_SHOW
Forward scan.
Positioning by key.
Keys are:
IdShow ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
Genre
g
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
Zone
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.

Run subquery 1 (at nesting level 1).
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.

STEP 5
The type of query is SELECT.
This step involves sorting.

FROM TABLE
Worktable1.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
STEP 1

NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 6.

QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 18).

Correlated Subquery.
Subquery under an EXPRESSION predicate.


STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.

FROM TABLE
PlaceOnPerformance
pop
Nested iteration.
Using Clustered Index.
Index : PK_PLACEONPERFORMANCE
Forward scan.
Positioning by key.
Keys are:
IdPerformance ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

END OF QUERY PLAN FOR SUBQUERY 1.


Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.
The sort for Worktable2 is done in Serial
The sort for Worktable3 is done in Serial
The sort for Worktable4 is done in Serial
The sort for Worktable1 is done in Serial
------------ Result 09:24:34.4990 ------------
Table: PlaceOnPerformance scan count 104, logical reads: (regular=1075 apf=0 total=1075), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Performance scan count 12623406, logical reads: (regular=25936369 apf=0 total=25936369), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: TarOnPerf scan count 1, logical reads: (regular=6062 apf=0 total=6062), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: SchemeTariffContents scan count 1, logical reads: (regular=11 apf=0 total=11), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Show scan count 15816, logical reads: (regular=45039 apf=0 total=45039), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Hall scan count 1, logical reads: (regular=1 apf=0 total=1), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Building scan count 17, logical reads: (regular=17 apf=0 total=17), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Genre scan count 15816, logical reads: (regular=15816 apf=0 total=15816), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Zone scan count 15816, logical reads: (regular=15816 apf=0 total=15816), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: PlaZoShe scan count 1, logical reads: (regular=40 apf=0 total=40), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: PlaceInSection scan count 17, logical reads: (regular=122 apf=0 total=122), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1 scan count 0, logical reads: (regular=19918 apf=0 total=19918), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable2 scan count 3460, logical reads: (regular=10635 apf=0 total=10635), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable3 scan count 3594, logical reads: (regular=10508 apf=0 total=10508), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable4 scan count 107369, logical reads: (regular=1187809 apf=0 total=1187809), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 6227

Execution Time 745.
SQL Server cpu time: 74500 ms. SQL Server elapsed time: 75460 ms.
(125 rows affected)
return status = 0
Total writes for this command: 6227

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 75460 ms.
------------------------- Done --------------------------

------------------------ Execute ------------------------
===============================================================================
Sybase Adaptive Server Enterprise System Performance Report
===============================================================================

Server Version: Adaptive Server Enterprise/12.5.4/EBF 16800 ESD#10/P/x86
Server Name: BASIS
Run Date: Mar 06, 2014
Sampling Started at: Mar 06, 2014 11:41:08
Sampling Ended at: Mar 06, 2014 11:51:10
Sample Interval: 00:10:02
Sample Mode: Reset Counters

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

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 100.0 % 0.0 % 0.0 %
Engine 1 99.9 % 0.0 % 0.0 %
Engine 2 100.0 % 0.0 % 0.0 %
------------------------ -------- -------- --------
Summary Total 299.9 % 0.0 % 0.1 %
Average 100.0 % 0.0 % 0.0 %

CPU Yields by Engine per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 0.0 0.0 9 34.6 %
Engine 1 0.0 0.0 17 65.4 %
Engine 2 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total CPU Yields 0.0 0.1 26

Network Checks
Non-Blocking 195.6 238.4 117772 100.0 %
Blocking 0.0 0.1 26 0.0 %
------------------------- ------------ ------------ ----------
Total Network I/O Checks 195.7 238.5 117798
Avg Net I/Os per Check n/a n/a 0.06609 n/a

Disk I/O Checks
Total Disk I/O Checks 197.8 241.0 119056 n/a
Checks Returning I/O 140.4 171.1 84520 71.0 %
Avg Disk I/Os Returned n/a n/a 0.06532 n/a

Tuning Recommendations for Kernel Utilization
---------------------------------------------
- Consider bringing more engines online


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

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


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

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.1 0.1 43 n/a

Task Context Switches by Engine
Engine 0 12.0 14.7 7242 29.9 %
Engine 1 17.0 20.8 10252 42.4 %
Engine 2 11.1 13.6 6712 27.7 %
------------------------- ------------ ------------ ----------
Total Task Switches: 40.2 49.0 24206

Task Context Switches Due To:
Voluntary Yields 16.2 19.8 9763 40.3 %
Cache Search Misses 3.7 4.5 2241 9.3 %
System Disk Writes 0.1 0.1 63 0.3 %
I/O Pacing 0.4 0.5 268 1.1 %
Logical Lock Contention 0.2 0.2 93 0.4 %
Address Lock Contention 0.0 0.0 0 0.0 %
Latch Contention 0.0 0.0 0 0.0 %
Log Semaphore Contention 0.0 0.0 10 0.0 %
PLC Lock Contention 0.0 0.0 0 0.0 %
Group Commit Sleeps 0.0 0.0 9 0.0 %
Last Log Page Writes 0.2 0.2 102 0.4 %
Modify Conflicts 0.1 0.1 35 0.1 %
I/O Device Contention 0.0 0.0 0 0.0 %
Network Packet Received 5.9 7.2 3542 14.6 %
Network Packet Sent 3.3 4.0 1982 8.2 %
Other Causes 10.1 12.3 6098 25.2 %


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

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

Application Statistics Summary (All Applications)
-------------------------------------------------
Priority Changes per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
To High Priority 0.2 0.3 141 32.3 %
To Medium Priority 0.4 0.5 238 54.5 %
To Low Priority 0.1 0.1 58 13.3 %
------------------------- ------------ ------------ ----------
Total Priority Changes 0.7 0.9 437

Allotted Slices Exhausted per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
High Priority 0.0 0.0 0 0.0 %
Medium Priority 14.9 18.2 8991 100.0 %
Low Priority 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Slices Exhausted 14.9 18.2 8991

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 0.1 0.1 70 100.0 %
Dirty 0.0 0.0 0 0.0 %
------------ ------------ ----------
Total Washes 0.1 0.1 70

Garbage Collections 0.4 0.5 267 n/a
Pages Processed in GC 0.0 0.0 0 n/a

Statistics Updates 0.0 0.1 30 n/a

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

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)


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

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

Transaction Summary per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Committed Xacts 0.8 n/a 494 n/a

Transaction Detail per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Inserts
APL Heap Table 1896.8 2311.4 1141847 99.8 %
APL Clustered Table 2.9 3.5 1717 0.2 %
Data Only Lock Table 0.3 0.4 183 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Inserted 1899.9 2315.3 1143747 99.9 %

Updates
APL Deferred 0.0 0.0 0 0.0 %
APL Direct In-place 0.1 0.1 60 64.5 %
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 14 15.1 %
DOL Direct 0.0 0.0 19 20.4 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Updated 0.2 0.2 93 0.0 %

Data Only Locked Updates
DOL Replace 0.0 0.1 29 87.9 %
DOL Shrink 0.0 0.0 0 0.0 %
DOL Cheap Expand 0.0 0.0 2 6.1 %
DOL Expensive Expand 0.0 0.0 2 6.1 %
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.1 0.1 33 0.0 %

Deletes
APL Deferred 1.9 2.4 1164 78.0 %
APL Direct 0.3 0.3 165 11.1 %
DOL 0.3 0.3 164 11.0 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Deleted 2.5 3.0 1493 0.1 %
========================= ============ ============ ==========
Total Rows Affected 1902.5 2318.5 1145333

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

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

ULC Flushes to Xact Log per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
by Full ULC 0.0 0.0 0 0.0 %
by End Transaction 0.9 1.2 569 29.9 %
by Change of Database 0.0 0.1 25 1.3 %
by Single Log Record 1.6 1.9 945 49.7 %
by Unpin 0.5 0.6 303 15.9 %
by Other 0.1 0.1 59 3.1 %
------------------------- ------------ ------------ ----------
Total ULC Flushes 3.2 3.8 1901

ULC Log Records 14.2 17.4 8577 n/a
Max ULC Size During Sample n/a n/a 4336 n/a

ULC Semaphore Requests
Granted 31.0 37.8 18691 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total ULC Semaphore Req 31.0 37.8 18691

Log Semaphore Requests
Granted 3.7 4.5 2226 99.6 %
Waited 0.0 0.0 10 0.4 %
------------------------- ------------ ------------ ----------
Total Log Semaphore Req 3.7 4.5 2236

Transaction Log Writes 0.9 1.1 548 n/a
Transaction Log Alloc 0.7 0.9 451 n/a
Avg # Writes per Log Page n/a n/a 1.21508 n/a

Tuning Recommendations for Transaction Management
-------------------------------------------------
- Consider decreasing the 'user log cache size'
configuration parameter if it is greater than the
logical database page size.

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

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

Nonclustered Maintenance per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Ins/Upd Requiring Maint 0.3 0.3 168 n/a
# of NC Ndx Maint 0.3 0.3 168 n/a
Avg NC Ndx Maint / Op n/a n/a 1.00000 n/a

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

RID Upd from Clust Split 0.0 0.0 1 n/a
# of NC Ndx Maint 0.0 0.0 8 n/a
Avg NC Ndx Maint / Op n/a n/a 8.00000 n/a

Upd/Del DOL Req Maint 0.3 0.4 197 n/a
# of DOL Ndx Maint 0.3 0.4 186 n/a
Avg DOL Ndx Maint / Op n/a n/a 0.94416 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 2520.8 3071.9 1517541 39.0 %
DOL Ascending Scans 3942.9 4804.9 2373596 61.0 %
Descending Scans 0.4 0.5 251 0.0 %
DOL Descending Scans 0.0 0.0 0 0.0 %
------------ ------------ ----------
Total Scans 6464.1 7877.3 3891388

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

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

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

Open Object Usage
Active n/a n/a 409 n/a
Max Ever Used Since Boot n/a n/a 409 n/a
Free n/a n/a 3000 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 459 n/a
Max Ever Used Since Boot n/a n/a 459 n/a
Free n/a n/a 2000 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 8 n/a
Max Ever Used Since Boot n/a n/a 8 n/a
Free n/a n/a 12 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 %

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 4754.9 5794.4 2862430 n/a
Avg Lock Contention 0.2 0.2 93 0.0 %
Deadlock Percentage 0.0 0.0 0 0.0 %

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

Table Lock Hashtable
Lookups 37.5 45.7 22591 n/a
Avg Chain Length n/a n/a 0.23492 n/a
Spinlock Contention n/a n/a n/a 0.0 %

Exclusive Table
Granted 3.2 3.9 1921 96.1 %
Waited 0.1 0.2 79 4.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Table Requests 3.3 4.0 2000 0.1 %

Shared Table
Granted 0.4 0.4 214 98.2 %
Waited 0.0 0.0 4 1.8 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Table Requests 0.4 0.4 218 0.0 %

Exclusive Intent
Granted 1.7 2.0 1000 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Intent Requests 1.7 2.0 1000 0.0 %

Shared Intent
Granted 31.1 37.9 18735 100.0 %
Waited 0.0 0.0 8 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Intent Requests 31.1 37.9 18743 0.7 %

Page & Row Lock HashTable
Lookups 2174.1 2649.4 1308810 n/a
Avg Chain Length n/a n/a 0.00094 n/a
Spinlock Contention n/a n/a n/a 0.0 %

Exclusive Page
Granted 2.3 2.8 1396 99.9 %
Waited 0.0 0.0 1 0.1 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Page Requests 2.3 2.8 1397 0.0 %

Update Page
Granted 1.6 1.9 953 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total UP-Page Requests 1.6 1.9 953 0.0 %

Shared Page
Granted 2093.6 2551.3 1260319 100.0 %
Waited 0.0 0.0 1 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Page Requests 2093.6 2551.3 1260320 44.0 %


Exclusive Row
Granted 0.7 0.8 417 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Row Requests 0.7 0.8 417 0.0 %

Update Row
Granted 20.7 25.3 12474 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total UP-Row Requests 20.7 25.3 12474 0.4 %

Shared Row
Granted 49.7 60.6 29931 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Row Requests 49.7 60.6 29931 1.0 %


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

Address Lock Hashtable
Lookups 2549.8 3107.2 1534977 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 1.2 1.5 724 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Address Requests 1.2 1.5 724 0.0 %

Shared Address
Granted 2548.6 3105.8 1534253 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Address Requests 2548.6 3105.8 1534253 53.6 %


Last Page Locks on Heaps
Granted 1896.8 2311.4 1141847 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total Last Pg Locks 1896.8 2311.4 1141847 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 23 n/a
Searches Skipped 0.0 0.0 0 0.0 %
Avg Deadlocks per Search n/a n/a 0.00000 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

Tuning Recommendations for Lock Management
------------------------------------------
- Consider increasing the 'deadlock checking period' parameter
by 50 ms.


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

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

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

Cache Search Summary
Total Cache Hits 640793.4 780885.9 385757648 100.0 %
Total Cache Misses 6.4 7.8 3832 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Searches 640799.8 780893.7 385761480

Cache Turnover
Buffers Grabbed 134.3 163.6 80839 n/a
Buffers Grabbed Dirty 0.0 0.0 0 0.0 %

Cache Strategy Summary
Cached (LRU) Buffers 619115.7 754469.0 372707668 100.0 %
Discarded (MRU) Buffers 0.0 0.0 0 0.0 %

Large I/O Usage
0.0 0.0 0 n/a

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

Asynchronous Prefetch Activity
APFs Issued 0.0 0.0 13 0.0 %
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 554.5 675.7 333791 100.0 %
------------------------- ------------ ------------ ----------
Total APFs Requested 554.5 675.7 333804

Other Asynchronous Prefetch Statistics
APFs Used 0.0 0.0 13 n/a
APF Waits for I/O 0.0 0.0 13 n/a
APF Discards 0.0 0.0 0 n/a

Dirty Read Behavior
Page Requests 790.8 963.7 476089 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 0.0 %

Cache Searches
Cache Hits 22.6 27.6 13622 99.3 %
Found in Wash 0.0 0.0 0 0.0 %
Cache Misses 0.2 0.2 96 0.7 %
------------------------- ------------ ------------ ----------
Total Cache Searches 22.8 27.8 13718

Pool Turnover
2 Kb Pool
LRU Buffer Grab 0.2 0.2 109 50.0 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 0.4 0.4 218

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

Cache Strategy
Cached (LRU) Buffers 22.7 27.6 13647 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


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

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

-------------------------------------------------------------------------------
Cache: dev
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

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


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

-------------------------------------------------------------------------------
Cache: kmdat
per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Spinlock Contention n/a n/a n/a 37.0 %

Utilization n/a n/a n/a 99.6 %

Cache Searches
Cache Hits 637928.2 777394.3 384032755 100.0 %
Found in Wash 0.0 0.0 0 0.0 %
Cache Misses 0.0 0.0 2 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Searches 637928.2 777394.3 384032757

Pool Turnover
2 Kb Pool
LRU Buffer Grab 4.3 5.2 2573 50.0 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 8.5 10.4 5146

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

Cache Strategy
Cached (LRU) Buffers 616140.2 750842.9 370916397 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


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

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

- Consider using Named Caches or Cache partitions or both.

-------------------------------------------------------------------------------
Cache: kmlog
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
Cache Hits 0.0 0.0 18 100.0 %
Found in Wash 0.0 0.0 0 0.0 %
Cache Misses 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Searches 0.0 0.0 18

Pool Turnover
------------------------- ------------ ------------ ----------
Total Cache Turnover 0.0 0.0 9

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

Cache Strategy
Cached (LRU) Buffers 0.0 0.0 13 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


Tuning Recommendations for Data cache : kmlog
-------------------------------------
- Consider using 'relaxed LRU replacement policy'
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 0.4 %

Cache Searches
Cache Hits 2842.6 3464.1 1711253 99.8 %
Found in Wash 0.2 0.3 130 0.0 %
Cache Misses 6.2 7.6 3734 0.2 %
------------------------- ------------ ------------ ----------
Total Cache Searches 2848.8 3471.6 1714987

Pool Turnover
2 Kb Pool
LRU Buffer Grab 6.3 7.7 3808 5.0 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 125.4 152.8 75466

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

Cache Strategy
Cached (LRU) Buffers 2952.8 3598.4 1777611 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


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.

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

Procedure Cache Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Procedure Requests 0.9 1.1 537 n/a
Procedure Reads from Disk 0.1 0.1 41 7.6 %
Procedure Writes to Disk 0.0 0.0 0 0.0 %
Procedure Removals 0.1 0.1 70 n/a
Procedure Recompilations 0.0 0.1 30 n/a

Recompilations Requests:
Execution Phase 0.0 0.0 19 63.3 %
Compilation Phase 0.0 0.0 11 36.7 %
Execute Cursor Execution 0.0 0.0 0 0.0 %
Redefinition Phase 0.0 0.0 0 0.0 %

Recompilation Reasons:
Table Missing 0.0 0.1 30 n/a
Temporary Table Missing 0.0 0.1 30 n/a
Schema Change 0.0 0.0 0 n/a
Index Change 0.0 0.0 0 n/a
Isolation Level Change 0.0 0.0 0 n/a
Permissions Change 0.0 0.0 0 n/a
Cursor Permissions Change 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.7 0.8 405 n/a


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

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

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

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

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

Avg Time per Normal Chkpt 15.00000 seconds

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

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

Max Outstanding I/Os per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Server n/a n/a 66 n/a
Engine 0 n/a n/a 42 n/a
Engine 1 n/a n/a 59 n/a
Engine 2 n/a n/a 39 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 9.2 11.2 5512

Completed Disk I/O's
Engine 0 3.5 4.3 2124 38.5 %
Engine 1 3.7 4.6 2254 40.8 %
Engine 2 1.9 2.3 1143 20.7 %
------------------------- ------------ ------------ ----------
Total Completed I/Os 9.2 11.2 5521


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

Device:
/dev/mapper/aquarium-basis_dat
basis_dat per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.0 0.0 2 100.0 %
Writes 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 2 0.0 %


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

Device:
/dev/mapper/aquarium-basis_log
basis_log 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.1 0.1 50 100.0 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.1 0.1 50 0.9 %


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

Device:
/opt/sybase/data/ivr.dat
ivr_dat 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:
/opt/sybase/data/ivr.log
ivr_log 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:
/opt/sybase/data/km_dev.dat
km_dev 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:
/opt/sybase/data/km_dev1.dat
km_dev1 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:
/opt/sybase/data/master.dat
master per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 13 5.1 %
Non-APF 0.2 0.3 125 49.2 %
Writes 0.2 0.2 116 45.7 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.4 0.5 254 4.6 %


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

Device:
/opt/sybase/data/systemdb.dat
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:
/opt/sybase/data/systemprocs.dat
sysprocsdev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.1 0.2 82 100.0 %
Writes 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.1 0.2 82 1.5 %


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

Device:
/opt/sybase/data/tempdb.dat
tempdb per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.0 0.0 14 0.5 %
Writes 4.3 5.2 2586 99.5 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 4.3 5.3 2600 47.2 %


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

Device:
/opt/sybase/tempdb_log.log
tempdb_log per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 3.4 4.1 2018 80.0 %
Writes 0.8 1.0 506 20.0 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 4.2 5.1 2524 45.8 %


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



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

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

Total Network I/O Requests 9.2 11.2 5524 n/a
Network I/Os Delayed 0.0 0.0 0 0.0 %


Total TDS Packets Received per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 0.6 0.7 337 9.5 %
Engine 1 3.6 4.4 2163 61.2 %
Engine 2 1.7 2.1 1037 29.3 %
------------------------- ------------ ------------ ----------
Total TDS Packets Rec'd 5.9 7.2 3537


Total Bytes Received per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 60.7 74.0 36554 2.9 %
Engine 1 1376.0 1676.8 828326 66.0 %
Engine 2 647.4 789.0 389747 31.1 %
------------------------- ------------ ------------ ----------
Total Bytes Rec'd 2084.1 2539.7 1254627


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

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

Total TDS Packets Sent per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 2.0 2.5 1220 28.7 %
Engine 1 3.2 3.9 1906 44.9 %
Engine 2 1.9 2.3 1122 26.4 %
------------------------- ------------ ------------ ----------
Total TDS Packets Sent 7.1 8.6 4248


Total Bytes Sent per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 853.2 1039.8 513644 29.8 %
Engine 1 1281.3 1561.4 771320 44.7 %
Engine 2 730.5 890.2 439756 25.5 %
------------------------- ------------ ------------ ----------
Total Bytes Sent 2865.0 3491.3 1724720


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

=============================== End of Report =================================
return status = 0
(1 rows affected)
------------------------- Done --------------------------

------------------------ Execute ------------------------
===============================================================================
Sybase Adaptive Server Enterprise System Performance Report
===============================================================================

Server Version: Adaptive Server Enterprise/12.5.4/EBF 16800 ESD#10/P/x86
Server Name: BASIS
Run Date: Mar 06, 2014
Sampling Started at: Mar 06, 2014 12:39:23
Sampling Ended at: Mar 06, 2014 12:49:24
Sample Interval: 00:10:01
Sample Mode: Reset Counters

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

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 100.0 % 0.0 % 0.0 %
Engine 1 100.0 % 0.0 % 0.0 %
Engine 2 100.0 % 0.0 % 0.0 %
------------------------ -------- -------- --------
Summary Total 300.0 % 0.0 % 0.0 %
Average 100.0 % 0.0 % 0.0 %

CPU Yields by Engine per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 0.0 0.0 0 0.0 %
Engine 1 0.0 0.0 0 0.0 %
Engine 2 0.0 0.0 6 100.0 %
------------------------- ------------ ------------ ----------
Total CPU Yields 0.0 0.0 6

Network Checks
Non-Blocking 114.5 84.3 68819 100.0 %
Blocking 0.0 0.0 6 0.0 %
------------------------- ------------ ------------ ----------
Total Network I/O Checks 114.5 84.3 68825
Avg Net I/Os per Check n/a n/a 0.19789 n/a

Disk I/O Checks
Total Disk I/O Checks 114.7 84.5 68938 n/a
Checks Returning I/O 97.8 72.0 58750 85.2 %
Avg Disk I/Os Returned n/a n/a 0.05614 n/a

Tuning Recommendations for Kernel Utilization
---------------------------------------------
- Consider bringing more engines online


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

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


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

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 30 n/a

Task Context Switches by Engine
Engine 0 15.3 11.3 9188 35.4 %
Engine 1 16.4 12.1 9848 38.0 %
Engine 2 11.5 8.4 6890 26.6 %
------------------------- ------------ ------------ ----------
Total Task Switches: 43.1 31.8 25926

Task Context Switches Due To:
Voluntary Yields 16.0 11.8 9646 37.2 %
Cache Search Misses 0.5 0.3 272 1.0 %
System Disk Writes 0.2 0.2 127 0.5 %
I/O Pacing 0.6 0.4 362 1.4 %
Logical Lock Contention 0.4 0.3 243 0.9 %
Address Lock Contention 0.0 0.0 0 0.0 %
Latch Contention 0.0 0.0 6 0.0 %
Log Semaphore Contention 0.0 0.0 2 0.0 %
PLC Lock Contention 0.0 0.0 0 0.0 %
Group Commit Sleeps 0.0 0.0 25 0.1 %
Last Log Page Writes 0.3 0.2 187 0.7 %
Modify Conflicts 0.2 0.1 115 0.4 %
I/O Device Contention 0.0 0.0 0 0.0 %
Network Packet Received 8.3 6.1 4991 19.3 %
Network Packet Sent 5.4 4.0 3257 12.6 %
Other Causes 11.1 8.2 6693 25.8 %


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

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

Application Statistics Summary (All Applications)
-------------------------------------------------
Priority Changes per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
To High Priority 0.5 0.4 293 45.1 %
To Medium Priority 0.6 0.4 339 52.2 %
To Low Priority 0.0 0.0 17 2.6 %
------------------------- ------------ ------------ ----------
Total Priority Changes 1.1 0.8 649

Allotted Slices Exhausted per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
High Priority 0.0 0.0 0 0.0 %
Medium Priority 14.9 11.0 8970 100.0 %
Low Priority 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Slices Exhausted 14.9 11.0 8970

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 0.6 0.4 358 97.8 %
Dirty 0.0 0.0 8 2.2 %
------------ ------------ ----------
Total Washes 0.6 0.4 366

Garbage Collections 0.4 0.3 252 n/a
Pages Processed in GC 0.0 0.0 0 n/a

Statistics Updates 0.0 0.0 7 n/a

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

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)


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

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

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

Transaction Detail per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Inserts
APL Heap Table 3708.0 2731.0 2228535 99.9 %
APL Clustered Table 3.7 2.7 2224 0.1 %
Data Only Lock Table 0.4 0.3 224 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Inserted 3712.1 2734.0 2230983 99.9 %

Updates
APL Deferred 0.0 0.0 2 1.9 %
APL Direct In-place 0.0 0.0 0 0.0 %
APL Direct Cheap 0.0 0.0 1 0.9 %
APL Direct Expensive 0.0 0.0 0 0.0 %
DOL Deferred 0.0 0.0 26 24.5 %
DOL Direct 0.1 0.1 77 72.6 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Updated 0.2 0.1 106 0.0 %

Data Only Locked Updates
DOL Replace 0.1 0.1 75 72.8 %
DOL Shrink 0.0 0.0 10 9.7 %
DOL Cheap Expand 0.0 0.0 7 6.8 %
DOL Expensive Expand 0.0 0.0 11 10.7 %
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.2 0.1 103 0.0 %

Deletes
APL Deferred 2.4 1.8 1462 78.3 %
APL Direct 0.3 0.3 207 11.1 %
DOL 0.3 0.2 197 10.6 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Deleted 3.1 2.3 1866 0.1 %
========================= ============ ============ ==========
Total Rows Affected 3715.4 2736.5 2232955

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

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

ULC Flushes to Xact Log per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
by Full ULC 0.0 0.0 0 0.0 %
by End Transaction 1.5 1.1 886 35.6 %
by Change of Database 0.2 0.1 94 3.8 %
by Single Log Record 1.7 1.2 996 40.0 %
by Unpin 0.7 0.5 419 16.8 %
by Other 0.2 0.1 94 3.8 %
------------------------- ------------ ------------ ----------
Total ULC Flushes 4.1 3.1 2489

ULC Log Records 17.9 13.2 10755 n/a
Max ULC Size During Sample n/a n/a 5136 n/a

ULC Semaphore Requests
Granted 39.9 29.4 23993 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total ULC Semaphore Req 39.9 29.4 23993

Log Semaphore Requests
Granted 4.9 3.6 2971 99.9 %
Waited 0.0 0.0 2 0.1 %
------------------------- ------------ ------------ ----------
Total Log Semaphore Req 4.9 3.6 2973

Transaction Log Writes 1.3 0.9 772 n/a
Transaction Log Alloc 1.0 0.7 575 n/a
Avg # Writes per Log Page n/a n/a 1.34261 n/a

Tuning Recommendations for Transaction Management
-------------------------------------------------
- Consider decreasing the 'user log cache size'
configuration parameter if it is greater than the
logical database page size.

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

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

Nonclustered Maintenance per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Ins/Upd Requiring Maint 0.3 0.3 207 n/a
# of NC Ndx Maint 0.3 0.3 207 n/a
Avg NC Ndx Maint / Op n/a n/a 1.00000 n/a

Deletes Requiring Maint 0.3 0.2 201 n/a
# of NC Ndx Maint 0.3 0.2 201 n/a
Avg NC Ndx Maint / Op n/a n/a 1.00000 n/a

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

Upd/Del DOL Req Maint 0.5 0.4 300 n/a
# of DOL Ndx Maint 0.4 0.3 236 n/a
Avg DOL Ndx Maint / Op n/a n/a 0.78667 n/a

Page Splits 0.0 0.0 5 n/a
Retries 0.0 0.0 0 0.0 %
Deadlocks 0.0 0.0 0 0.0 %
Add Index Level 0.0 0.0 0 0.0 %

Page Shrinks 0.0 0.0 0 n/a

Index Scans per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Ascending Scans 3458.0 2546.9 2078262 36.2 %
DOL Ascending Scans 6097.6 4491.0 3664636 63.8 %
Descending Scans 0.5 0.4 317 0.0 %
DOL Descending Scans 0.0 0.0 0 0.0 %
------------ ------------ ----------
Total Scans 9556.1 7038.3 5743215

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

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

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

Open Object Usage
Active n/a n/a 417 n/a
Max Ever Used Since Boot n/a n/a 417 n/a
Free n/a n/a 3000 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 438 n/a
Max Ever Used Since Boot n/a n/a 466 n/a
Free n/a n/a 2000 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 8 n/a
Max Ever Used Since Boot n/a n/a 8 n/a
Free n/a n/a 12 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 %

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 5988.1 4410.3 3598827 n/a
Avg Lock Contention 0.4 0.3 243 0.0 %
Deadlock Percentage 0.0 0.0 0 0.0 %

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

Table Lock Hashtable
Lookups 82.7 60.9 49696 n/a
Avg Chain Length n/a n/a 0.18609 n/a
Spinlock Contention n/a n/a n/a 0.0 %

Exclusive Table
Granted 4.5 3.3 2695 94.3 %
Waited 0.3 0.2 163 5.7 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Table Requests 4.8 3.5 2858 0.1 %

Shared Table
Granted 0.6 0.5 384 97.7 %
Waited 0.0 0.0 9 2.3 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Table Requests 0.7 0.5 393 0.0 %

Exclusive Intent
Granted 2.2 1.6 1326 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Intent Requests 2.2 1.6 1326 0.0 %

Shared Intent
Granted 73.0 53.8 43876 99.8 %
Waited 0.1 0.1 70 0.2 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Intent Requests 73.1 53.9 43946 1.2 %

Page & Row Lock HashTable
Lookups 2417.7 1780.7 1453015 n/a
Avg Chain Length n/a n/a 0.00174 n/a
Spinlock Contention n/a n/a n/a 0.0 %

Exclusive Page
Granted 2.8 2.1 1686 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Page Requests 2.8 2.1 1686 0.0 %

Update Page
Granted 2.1 1.6 1269 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total UP-Page Requests 2.1 1.6 1269 0.0 %

Shared Page
Granted 2277.2 1677.2 1368576 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Page Requests 2277.2 1677.2 1368576 38.0 %


Exclusive Row
Granted 0.9 0.7 561 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Row Requests 0.9 0.7 561 0.0 %

Update Row
Granted 1.9 1.4 1143 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total UP-Row Requests 1.9 1.4 1143 0.0 %

Shared Row
Granted 125.5 92.4 75415 100.0 %
Waited 0.0 0.0 1 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Row Requests 125.5 92.4 75416 2.1 %


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

Address Lock Hashtable
Lookups 3496.9 2575.6 2101653 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 1.5 1.1 930 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Address Requests 1.5 1.1 930 0.0 %

Shared Address
Granted 3495.4 2574.4 2100723 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Address Requests 3495.4 2574.4 2100723 58.4 %


Last Page Locks on Heaps
Granted 3708.0 2731.0 2228535 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total Last Pg Locks 3708.0 2731.0 2228535 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.1 0.0 37 n/a
Searches Skipped 0.0 0.0 0 0.0 %
Avg Deadlocks per Search n/a n/a 0.00000 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

Tuning Recommendations for Lock Management
------------------------------------------
- Consider increasing the 'deadlock checking period' parameter
by 50 ms.


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

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

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

Cache Search Summary
Total Cache Hits 630395.6 464298.8 378867782 100.0 %
Total Cache Misses 3.1 2.3 1841 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Searches 630398.7 464301.0 378869623

Cache Turnover
Buffers Grabbed 205.9 151.7 123765 n/a
Buffers Grabbed Dirty 0.0 0.0 0 0.0 %

Cache Strategy Summary
Cached (LRU) Buffers 616246.0 453877.3 370363858 100.0 %
Discarded (MRU) Buffers 0.0 0.0 0 0.0 %

Large I/O Usage
Large I/Os Performed 0.8 0.6 464 99.8 %

Large I/Os Denied due to
Pool < Prefetch Size 0.0 0.0 1 0.2 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 0.8 0.6 465

Large I/O Effectiveness
Pages by Lrg I/O Cached 3.1 2.3 1856 n/a
Pages by Lrg I/O Used 0.8 0.6 464 25.0 %

Asynchronous Prefetch Activity
APFs Issued 0.0 0.0 22 0.0 %
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.5 0.4 300 0.0 %
W/o Spinlock Held 1191.7 877.7 716210 100.0 %
------------------------- ------------ ------------ ----------
Total APFs Requested 1192.2 878.1 716532

Other Asynchronous Prefetch Statistics
APFs Used 0.0 0.0 15 n/a
APF Waits for I/O 0.0 0.0 6 n/a
APF Discards 0.0 0.0 0 n/a

Dirty Read Behavior
Page Requests 3323.7 2448.0 1997562 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 0.0 %

Cache Searches
Cache Hits 33.2 24.4 19941 99.9 %
Found in Wash 0.2 0.1 104 0.5 %
Cache Misses 0.0 0.0 15 0.1 %
------------------------- ------------ ------------ ----------
Total Cache Searches 33.2 24.5 19956

Pool Turnover
2 Kb Pool
LRU Buffer Grab 0.0 0.0 15 50.0 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 0.0 0.0 30

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

Cache Strategy
Cached (LRU) Buffers 33.1 24.4 19883 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
16 Kb Pool
Pages Cached 0.0 0.0 0 n/a
Pages Used 0.0 0.0 0 n/a

Dirty Read Behavior


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

- Consider removing the 16k pool for this cache.

-------------------------------------------------------------------------------
Cache: dev
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

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


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

-------------------------------------------------------------------------------
Cache: kinomirdat
per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Spinlock Contention n/a n/a n/a 37.0 %

Utilization n/a n/a n/a 99.2 %

Cache Searches
Cache Hits 625432.0 460642.9 375884634 100.0 %
Found in Wash 2.1 1.6 1270 0.0 %
Cache Misses 0.4 0.3 233 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Searches 625432.4 460643.2 375884867

Pool Turnover
2 Kb Pool
LRU Buffer Grab 11.9 8.8 7164 49.8 %
Grabbed Dirty 0.0 0.0 0 0.0 %
8 Kb Pool
LRU Buffer Grab 0.0 0.0 26 0.2 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 23.9 17.6 14376

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

Cache Strategy
Cached (LRU) Buffers 611118.4 450100.7 367282180 100.0 %
Discarded (MRU) Buffers 0.0 0.0 0 0.0 %

Large I/O Usage
Large I/Os Performed 0.0 0.0 26 96.3 %

Large I/Os Denied due to
Pool < Prefetch Size 0.0 0.0 1 3.7 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 0.0 0.0 27

Large I/O Detail
8 Kb Pool
Pages Cached 0.2 0.1 104 n/a
Pages Used 0.0 0.0 26 25.0 %

Dirty Read Behavior


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

- Consider using Named Caches or Cache partitions or both.

-------------------------------------------------------------------------------
Cache: kinomirlog
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
Cache Hits 0.2 0.2 134 100.0 %
Found in Wash 0.0 0.0 0 0.0 %
Cache Misses 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Searches 0.2 0.2 134

Pool Turnover
------------------------- ------------ ------------ ----------
Total Cache Turnover 0.1 0.0 32

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

Cache Strategy
Cached (LRU) Buffers 0.1 0.1 77 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


Tuning Recommendations for Data cache : kinomirlog
-------------------------------------
- Consider using 'relaxed LRU replacement policy'
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 0.8 %

Cache Searches
Cache Hits 4930.2 3631.2 2963073 99.9 %
Found in Wash 0.8 0.6 487 0.0 %
Cache Misses 2.7 2.0 1593 0.1 %
------------------------- ------------ ------------ ----------
Total Cache Searches 4932.9 3633.2 2964666

Pool Turnover
2 Kb Pool
LRU Buffer Grab 0.2 0.1 122 0.1 %
Grabbed Dirty 0.0 0.0 0 0.0 %
8 Kb Pool
LRU Buffer Grab 0.7 0.5 438 0.4 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 181.9 134.0 109327

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

Cache Strategy
Cached (LRU) Buffers 5094.4 3752.1 3061718 100.0 %
Discarded (MRU) Buffers 0.0 0.0 0 0.0 %

Large I/O Usage
Large I/Os Performed 0.7 0.5 438 100.0 %

Large I/Os Denied due to
Pool < Prefetch Size 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 0.7 0.5 438

Large I/O Detail
8 Kb Pool
Pages Cached 2.9 2.1 1752 n/a
Pages Used 0.7 0.5 438 25.0 %

Dirty Read Behavior


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

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

Procedure Cache Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Procedure Requests 2.2 1.6 1342 n/a
Procedure Reads from Disk 0.3 0.2 155 11.5 %
Procedure Writes to Disk 0.0 0.0 5 0.4 %
Procedure Removals 0.4 0.3 270 n/a
Procedure Recompilations 0.2 0.1 115 n/a

Recompilations Requests:
Execution Phase 0.1 0.1 79 68.7 %
Compilation Phase 0.1 0.0 36 31.3 %
Execute Cursor Execution 0.0 0.0 0 0.0 %
Redefinition Phase 0.0 0.0 0 0.0 %

Recompilation Reasons:
Table Missing 0.2 0.1 115 n/a
Temporary Table Missing 0.2 0.1 115 n/a
Schema Change 0.0 0.0 0 n/a
Index Change 0.0 0.0 0 n/a
Isolation Level Change 0.0 0.0 0 n/a
Permissions Change 0.0 0.0 0 n/a
Cursor Permissions Change 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 1.2 0.9 733 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 0.2 184 n/a
Pages Released 0.3 0.2 185 n/a

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

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

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

Avg Time per Normal Chkpt 9.50000 seconds

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

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

Max Outstanding I/Os per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Server n/a n/a 48 n/a
Engine 0 n/a n/a 36 n/a
Engine 1 n/a n/a 38 n/a
Engine 2 n/a n/a 37 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 5.5 4.0 3297

Completed Disk I/O's
Engine 0 1.1 0.8 644 19.5 %
Engine 1 3.3 2.4 1980 60.0 %
Engine 2 1.1 0.8 674 20.4 %
------------------------- ------------ ------------ ----------
Total Completed I/Os 5.5 4.0 3298


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

Device:
/dev/mapper/aquarium-basis_dat
basis_dat per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 22 8.5 %
Non-APF 0.4 0.3 233 90.3 %
Writes 0.0 0.0 3 1.2 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.4 0.3 258 7.8 %


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

Device:
/dev/mapper/aquarium-basis_log
basis_log 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.2 0.2 129 100.0 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.2 0.2 129 3.9 %


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

Device:
/opt/sybase/data/ivr.dat
ivr_dat 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:
/opt/sybase/data/ivr.log
ivr_log 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:
/opt/sybase/data/km_dev.dat
km_dev 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:
/opt/sybase/data/km_dev1.dat
km_dev1 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:
/opt/sybase/data/master.dat
master per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.0 0.0 15 7.5 %
Writes 0.3 0.2 186 92.5 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.3 0.2 201 6.1 %


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

Device:
/opt/sybase/data/systemdb.dat
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:
/opt/sybase/data/systemprocs.dat
sysprocsdev 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:
/opt/sybase/data/tempdb.dat
tempdb per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.0 0.0 24 1.2 %
Writes 3.4 2.5 2028 98.8 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 3.4 2.5 2052 62.2 %


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

Device:
/opt/sybase/tempdb_log.log
tempdb_log 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 1.1 0.8 657 100.0 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 1.1 0.8 657 19.9 %


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



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

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

Total Network I/O Requests 13.7 10.1 8248 n/a
Network I/Os Delayed 0.0 0.0 0 0.0 %


Total TDS Packets Received per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 1.9 1.4 1116 22.4 %
Engine 1 5.7 4.2 3419 68.5 %
Engine 2 0.8 0.6 455 9.1 %
------------------------- ------------ ------------ ----------
Total TDS Packets Rec'd 8.3 6.1 4990


Total Bytes Received per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 634.5 467.3 381308 21.4 %
Engine 1 2248.6 1656.1 1351380 75.9 %
Engine 2 79.9 58.9 48024 2.7 %
------------------------- ------------ ------------ ----------
Total Bytes Rec'd 2962.9 2182.2 1780712


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

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

Total TDS Packets Sent per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 3.3 2.5 2002 23.2 %
Engine 1 8.2 6.0 4922 57.0 %
Engine 2 2.8 2.1 1706 19.8 %
------------------------- ------------ ------------ ----------
Total TDS Packets Sent 14.4 10.6 8630


Total Bytes Sent per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 1459.3 1074.8 877046 23.2 %
Engine 1 3628.2 2672.2 2180555 57.7 %
Engine 2 1197.7 882.2 719838 19.1 %
------------------------- ------------ ------------ ----------
Total Bytes Sent 6285.3 4629.2 3777439


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

=============================== End of Report =================================
return status = 0
(1 rows affected)
------------------------- Done --------------------------

------------------------ Execute ------------------------

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION ON.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION ON.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SELECT.

FROM TABLE
plazoshe
pzs
Nested iteration.
Using Clustered Index.
Index : PK_PLAZOSHE
Forward scan.
Positioning by key.
Keys are:
IdZoScheme ASC
Using I/O Size 8 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 8 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
placeinsection
pis
Nested iteration.
Using Clustered Index.
Index : P_PlaceInSection
Forward scan.
Positioning by key.
Keys are:
IdPlace ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.

Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
Total writes for this command: 0

Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 0 ms.
------------------------- Done --------------------------

------------------------ Execute ------------------------

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION ON.


QUERY PLAN FOR STATEMENT 1 (at line 2).


STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable1 created, in allpages locking mode, for ORDER BY.

FROM TABLE
h
Nested iteration.
Using Clustered Index.
Index : PK_HID
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
b
Nested iteration.
Using Clustered Index.
Index : PK_BID
Forward scan.
Positioning by key.
Keys are:
IdBuilding ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
pzs
Nested iteration.
Using Clustered Index.
Index : PK_PZS
Forward scan.
Positioning at index start.
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
pis
Nested iteration.
Using Clustered Index.
Index : P_PIS
Forward scan.
Positioning by key.
Keys are:
IdPlace ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
p
Nested iteration.
Index : ByDate
Forward scan.
Positioning at index start.
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
top
Nested iteration.
Using Clustered Index.
Index : PK_TOP
Forward scan.
Positioning by key.
Keys are:
IdPerformance ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
stc
Nested iteration.
Using Clustered Index.
Index : PK_STC
Forward scan.
Positioning by key.
Keys are:
IdTaScheme ASC
IdZone ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
s
Nested iteration.
Using Clustered Index.
Index : PK_SID
Forward scan.
Positioning by key.
Keys are:
IdShow ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
g
Nested iteration.
Using Clustered Index.
Index : PK_GID
Forward scan.
Positioning by key.
Keys are:
gid ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
z
Nested iteration.
Using Clustered Index.
Index : PK_ZID
Forward scan.
Positioning by key.
Keys are:
Id ASC

Run subquery 1 (at nesting level 1).
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.

STEP 2
The type of query is SELECT.
This step involves sorting.

FROM TABLE
Worktable1.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 8 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
STEP 1

NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.

QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 5).

Correlated Subquery.
Subquery under an EXPRESSION predicate.


STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.

FROM TABLE
pop
Nested iteration.
Index : Id
Forward scan.
Positioning by key.
Keys are:
PID ASC
OID ASC
Using I/O Size 8 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 8 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

END OF QUERY PLAN FOR SUBQUERY 1.



QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.


QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.

------------------------- Done --------------------------

------------------------ Execute ------------------------

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION ON.


QUERY PLAN FOR STATEMENT 1 (at line 2).


STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable1 created, in allpages locking mode, for ORDER BY.

FROM TABLE
h
Nested iteration.
Using Clustered Index.
Index : PK_HШВ
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
b
Nested iteration.
Using Clustered Index.
Index : PK_BID
Forward scan.
Positioning by key.
Keys are:
Id ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
p
Nested iteration.
Index : ByDate
Forward scan.
Positioning at index start.
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
s
Nested iteration.
Using Clustered Index.
Index : PK_SID
Forward scan.
Positioning by key.
Keys are:
Id ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
g
Nested iteration.
Using Clustered Index.
Index : PK_GID
Forward scan.
Positioning by key.
Keys are:
Id ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
top
Nested iteration.
Using Clustered Index.
Index : PK_TOP
Forward scan.
Positioning by key.
Keys are:
IdPerformance ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
stc
Nested iteration.
Using Clustered Index.
Index : PK_STC
Forward scan.
Positioning by key.
Keys are:
IdTaScheme ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
z
Nested iteration.
Using Clustered Index.
Index : PK_ZID
Forward scan.
Positioning by key.
Keys are:
Id ASC

Run subquery 1 (at nesting level 1).
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.

STEP 2
The type of query is SELECT.
This step involves sorting.

FROM TABLE
Worktable1.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 8 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
STEP 1

NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.

QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 5).

Correlated Subquery.
Subquery under an EXPRESSION predicate.


STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.

FROM TABLE
pop
Nested iteration.
Index : Id
Forward scan.
Positioning by key.
Keys are:
PID ASC
OID ASC
Using I/O Size 8 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 8 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

END OF QUERY PLAN FOR SUBQUERY 1.



QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.


QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.

------------------------- Done --------------------------

------------------------ Execute ------------------------

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION ON.


QUERY PLAN FOR STATEMENT 1 (at line 2).


STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable1 created, in allpages locking mode, for ORDER BY.

FROM TABLE
h
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
b
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
p
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 8 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
s
Nested iteration.
Using Clustered Index.
Index : PK_SID
Forward scan.
Positioning by key.
Keys are:
Id ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
g
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
top
Nested iteration.
Using Clustered Index.
Index : PK_TOP
Forward scan.
Positioning by key.
Keys are:
IdPerformance ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
stc
Nested iteration.
Using Clustered Index.
Index : PK_STC
Forward scan.
Positioning by key.
Keys are:
IdTaScheme ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
z
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
pzs
EXISTS TABLE : nested iteration.
Index : ZISZ_F
Forward scan.
Positioning by key.
Keys are:
IdZone ASC
Using I/O Size 8 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 8 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
pis
EXISTS TABLE : nested iteration.
Using Clustered Index.
Index : P_PIS
Forward scan.
Positioning by key.
Keys are:
PID ASC

Run subquery 1 (at nesting level 1).
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.

STEP 2
The type of query is SELECT.
This step involves sorting.

FROM TABLE
Worktable1.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 8 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
STEP 1

NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.

QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 5).

Correlated Subquery.
Subquery under an EXPRESSION predicate.


STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.

FROM TABLE
pop
Nested iteration.
Using Clustered Index.
Index : PK_POP
Forward scan.
Positioning by key.
Keys are:
PID ASC
Using I/O Size 8 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 8 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

END OF QUERY PLAN FOR SUBQUERY 1.



QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.


QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.

------------------------- Done --------------------------