The Problem:
The SQL Server Performance and Tuning Guide states:
"In environments with high transaction rates or transactions that create large log records, the 2K transaction log pages fill quickly, and a large proportion of log writes are due to full log pages, rather than group commits. Creating a 4K buffer pool for the transaction log can greatly reduce the log writes in these environments."
Stated another way, there will be fewer physical writes when 4K pages fill and are flushed to disk than there are when 2K pages fill and are flushed to disk.
The problem is that setting up a 4K buffer pool is complex and challenging.
The Resolution:
1. Create a named cache with sp_cacheconfig.
Example:
sp_cacheconfig testcache, "8M",logonly
2. Restart SQL server to bring the cache online.
3. Create a 4K cache pool using the named cache with sp_poolconfigure testcache.
Example:
sp_poolconfigure testcache,"7.5M","4K"
4. Change database to single user mode to allow the use of binding cache.
5. Bind the named cache to syslogs for the database with sp_bindcache.
Example:
sp_bindcache testcache, "pubs2", "syslogs"
6. Use the database.
Example:
use pubs2
7. Change log I/O size to 4K in the database with sp_logiosize.
Example:
sp_logiosize "4"
Extended Example:
To demonstrate, follow the steps above using the example commands shown, then use sp_sysmon to monitor the behaviour of the server. This was tested by dropping all records in selected pubs2's user tables (e.g. delete authors etc...) and inserting all records back using a modified version of instpbs2 scripts. The sp_sysmon output is shown below.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
===============================================================================
Sybase SQL Server System Performance Report
===============================================================================
Run Date Sep 08, 1997
Statistics Cleared at 14:58:45
Statistics Sampled at 15:00:45
Sample Interval 2 min.
===============================================================================
Kernel Utilization
------------------
Engine Busy Utilization:
Engine 0 10.4 %
CPU Yields by Engine per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 85.9 31.1 10255 100.0 %
Network Checks
0.0 0.0 0 n/a
Disk I/O Checks
Total Disk I/O Checks 5142.2 1860.7 614036 n/a
Checks Returning I/O 4.4 1.6 526 0.1 %
Avg Disk I/Os Returned n/a n/a 1.04183 n/a
===============================================================================
Task Management per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Connections Opened 0.0 0.0 2 n/a
Task Context Switches by Engine
Engine 0 13.0 4.7 1558 100.0 %
Task Context Switches Due To:
Voluntary Yields 2.9 1.1 350 22.5 %
Cache Search Misses 0.8 0.3 99 6.4 %
System Disk Writes 0.0 0.0 5 0.3 %
I/O Pacing 0.3 0.1 35 2.2 %
Logical Lock Contention 0.0 0.0 0 0.0 %
Address Lock Contention 0.0 0.0 0 0.0 %
Log Semaphore Contention 0.0 0.0 0 0.0 %
Group Commit Sleeps 0.0 0.0 0 0.0 %
Last Log Page Writes 2.8 1.0 334 21.4 %
Modify Conflicts 0.0 0.0 0 0.0 %
I/O Device Contention 0.0 0.0 0 0.0 %
Network Packet Received 2.8 1.0 333 21.4 %
Network Packet Sent 2.7 1.0 319 20.5 %
SYSINDEXES Lookup 0.0 0.0 0 0.0 %
Other Causes 0.7 0.3 83 5.3 %
===============================================================================
Transaction Profile
-------------------
Transaction Summary per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Committed Xacts 2.8 n/a 330 n/a
Transaction Detail per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Inserts
Heap Table 1.8 0.7 220 65.7 %
Clustered Table 1.0 0.3 115 34.3 %
------------------------- ------------ ------------ ----------
Total Rows Inserted 2.8 1.0 335 48.8 %
Updates
Deferred 0.1 0.0 16 100.0 %
Direct In-place 0.0 0.0 0 0.0 %
Direct Cheap 0.0 0.0 0 0.0 %
Direct Expensive 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Rows Updated 0.1 0.0 16 2.3 %
Deletes
Deferred 0.1 0.0 16 4.8 %
Direct 2.7 1.0 319 95.2 %
------------------------- ------------ ------------ ----------
Total Rows Deleted 2.8 1.0 335 48.8 %
===============================================================================
Transaction Management
----------------------
ULC Flushes to Xact Log per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
by Full ULC 0.2 0.1 21 5.7 %
by End Transaction 2.8 1.0 329 88.7 %
by Change of Database 0.0 0.0 0 0.0 %
by System Log Record 0.1 0.0 15 4.0 %
by Other 0.1 0.0 6 1.6 %
------------------------- ------------ ------------ ----------
Total ULC Flushes 3.1 1.1 371
ULC Log Records 19.4 7.0 2321 n/a
Max ULC Size n/a n/a 2044 n/a
ULC Semaphore Requests
Granted 39.8 14.4 4749 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total ULC Semaphore Req 39.8 14.4 4749
Log Semaphore Requests
Granted 4.2 1.5 500 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Log Semaphore Req 4.2 1.5 500
Transaction Log Writes 3.2 1.2 381 n/a
Transaction Log Alloc 0.8 0.3 92 n/a
Avg # Writes per Log Page n/a n/a 4.14130 n/a
===============================================================================
Index Management
----------------
Nonclustered Maintenance per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Ins/Upd Requiring Maint 2.4 0.9 285 n/a
# of NC Ndx Maint 3.6 1.3 426 n/a
Avg NC Ndx Maint / Op n/a n/a 1.49474 n/a
Deletes Requiring Maint 2.4 0.9 285 n/a
# of NC Ndx Maint 3.6 1.3 426 n/a
Avg NC Ndx Maint / Op n/a n/a 1.49474 n/a
RID Upd from Clust Split 0.0 0.0 2 n/a
# of NC Ndx Maint 0.1 0.0 9 n/a
Avg NC Ndx Maint / Op n/a n/a 4.50000 n/a
Page Splits 0.0 0.0 3 n/a
Retries 0.0 0.0 0 0.0 %
Deadlocks 0.0 0.0 0 0.0 %
Empty Page Flushes 0.0 0.0 0 0.0 %
Add Index Level 0.0 0.0 1 33.3 %
Page Shrinks 0.0 0.0 5 n/a
===============================================================================
Lock Management
---------------
Lock Summary per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Lock Requests 64.2 23.2 7668 n/a
Avg Lock Contention 0.0 0.0 0 0.0 %
Deadlock Percentage 0.0 0.0 0 0.0 %
Lock Detail per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Exclusive Table
Granted 1.1 0.4 128 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total EX-Table Requests 1.1 0.4 128 1.7 %
Shared Table
Granted 0.1 0.0 6 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total SH-Table Requests 0.1 0.0 6 0.1 %
Exclusive Intent
Granted 2.7 1.0 319 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total EX-Intent Requests 2.7 1.0 319 4.2 %
Shared Intent
Granted 6.1 2.2 730 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total SH-Intent Requests 6.1 2.2 730 9.5 %
Exclusive Page
Granted 6.3 2.3 749 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total EX-Page Requests 6.3 2.3 749 9.8 %
Update Page
0.0 0.0 0 n/a
------------------------- ------------ ------------ ----------
Total UP-Page Requests 0.0 0.0 0 0.0 %
Shared Page
Granted 13.2 4.8 1581 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total SH-Page Requests 13.2 4.8 1581 20.6 %
Exclusive Address
Granted 15.5 5.6 1852 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total EX-Address Requests 15.5 5.6 1852 24.2 %
Shared Address
Granted 19.3 7.0 2303 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total SH-Address Requests 19.3 7.0 2303 30.0 %
Last Page Locks on Heaps
Granted 1.8 0.7 220 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Last Pg Locks 1.8 0.7 220 2.9 %
Deadlocks by Lock Type per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
Deadlock Detection
Deadlock Searches 0.0 0.0 0 n/a
Lock Promotions
0.0 0.0 0 n/a
===============================================================================
Data Cache Management
---------------------
Cache Statistics Summary (All Caches)
-------------------------------------
Cache Search Summary
Total Cache Hits 65.0 23.5 7760 98.0 %
Total Cache Misses 1.3 0.5 161 2.0 %
------------------------- ------------ ------------ ----------
Total Cache Searches 66.3 24.0 7921
Cache Turnover
Buffers Grabbed 0.8 0.3 99 n/a
Buffers Grabbed Dirty 0.0 0.0 0 0.0 %
Cache Strategy Summary
Cached (LRU) Buffers 93.0 33.7 11110 100.0 %
Discarded (MRU) Buffers 0.0 0.0 0 0.0 %
Large I/O Usage
Large I/Os Performed 0.7 0.2 82 88.2 %
Large I/Os Denied 0.1 0.0 11 11.8 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 0.8 0.3 93
Large I/O Effectiveness
Pages by Lrg I/O Cached 0.0 0.0 2 n/a
Pages by Lrg I/O Used 0.0 0.0 0 0.0 %
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
-------------------------------------------------------------------------------
default data cache
per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
Utilization n/a n/a n/a 91.0 %
Cache Searches
Cache Hits 59.4 21.5 7098 98.4 %
Found in Wash 0.2 0.1 25 0.4 %
Cache Misses 0.9 0.3 113 1.6 %
------------------------- ------------ ------------ ----------
Total Cache Searches 60.4 21.9 7211
Pool Turnover
2 Kb Pool
LRU Buffer Grab 0.8 0.3 98 100.0 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 0.8 0.3 98
Buffer Wash Behavior
Statistics Not Available - No Buffers Entered Wash Section Yet
Cache Strategy
Cached (LRU) Buffers 88.2 31.9 10535 100.0 %
Discarded (MRU) Buffers 0.0 0.0 0 0.0 %
Large I/O Usage
0.0 0.0 0 n/a
Large I/O Detail
No Large Pool(s) In This Cache
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
-------------------------------------------------------------------------------
testcache
per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
Utilization n/a n/a n/a 9.0 %
Cache Searches
Cache Hits 5.5 2.0 662 93.2 %
Found in Wash 0.0 0.0 0 0.0 %
Cache Misses 0.4 0.1 48 6.8 %
------------------------- ------------ ------------ ----------
Total Cache Searches 5.9 2.2 710
Pool Turnover
4 Kb Pool
LRU Buffer Grab 0.0 0.0 1 100.0 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 0.0 0.0 1
Buffer Wash Behavior
Statistics Not Available - No Buffers Entered Wash Section Yet
Cache Strategy
Cached (LRU) Buffers 4.8 1.7 575 100.0 %
Discarded (MRU) Buffers 0.0 0.0 0 0.0 %
Large I/O Usage
Large I/Os Performed 0.7 0.2 82 88.2 %
Large I/Os Denied 0.1 0.0 11 11.8 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 0.8 0.3 93
Large I/O Detail
4 Kb Pool
Pages Cached 0.0 0.0 2 n/a
Pages Used 0.0 0.0 0 0.0 %
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
===============================================================================
Procedure Cache Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Procedure Requests 2.2 0.8 260 n/a
Procedure Reads from Disk 0.1 0.0 6 2.3 %
Procedure Writes to Disk 0.0 0.0 0 0.0 %
Procedure Removals 0.0 0.0 2 n/a
===============================================================================
Memory Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Pages Allocated 0.0 0.0 0 n/a
Pages Released 0.0 0.0 0 n/a
===============================================================================
Recovery Management
-------------------
Checkpoints per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
===============================================================================
Disk I/O Management
-------------------
Max Outstanding I/Os per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Server n/a n/a 10 n/a
Engine 0 n/a n/a 10 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 4.6 1.7 548 n/a
Completed Disk I/O's
Engine 0 4.6 1.7 548 100.0 %
------------------------- ------------ ------------ ---------- ----------
Total Completed I/Os 4.6 1.7 548
Device Activity Detail
----------------------
d:\sybase\DATA\SYBPROCS.DAT
sysprocsdev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
d:\sybase\data\psdemodb.dev
psdemodb_dev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
d:\sybase\data\pubs2d.dat
pubs2d1 per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads 0.0 0.0 3 0.8 %
Writes 3.2 1.2 383 99.2 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 3.2 1.2 386 70.4 %
Device Semaphore Granted 3.2 1.2 386 100.0 %
Device Semaphore Waited 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
d:\sybase\data\pubs2d2.dat
pubs2d2 per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads 0.8 0.3 96 59.3 %
Writes 0.6 0.2 66 40.7 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 1.4 0.5 162 29.6 %
Device Semaphore Granted 1.4 0.5 162 100.0 %
Device Semaphore Waited 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
d:\sybase\data\tempdb.dev
tempdb_dev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
master.dat
master per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
testdb1.dat
testdb1 per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
testlog1.dat
testlog1 per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
===============================================================================
Network I/O Management
----------------------
Total Network I/O Requests 5.5 2.0 652 n/a
Network I/Os Delayed 0.0 0.0 0 0.0 %
Total TDS Packets Received per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total TDS Packets Rec'd 0.0 0.0 0
Total Bytes Received per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total Bytes Rec'd 0.0 0.0 0
-----------------------------------------------------------------------------
Total TDS Packets Sent per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total TDS Packets Sent 0.0 0.0 0
Total Bytes Sent per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total Bytes Sent 0.0 0.0 0
=============================== End of Report =================================
(return status = 0)
The SQL Server Performance and Tuning Guide states:
"In environments with high transaction rates or transactions that create large log records, the 2K transaction log pages fill quickly, and a large proportion of log writes are due to full log pages, rather than group commits. Creating a 4K buffer pool for the transaction log can greatly reduce the log writes in these environments."
Stated another way, there will be fewer physical writes when 4K pages fill and are flushed to disk than there are when 2K pages fill and are flushed to disk.
The problem is that setting up a 4K buffer pool is complex and challenging.
The Resolution:
1. Create a named cache with sp_cacheconfig.
Example:
sp_cacheconfig testcache, "8M",logonly
2. Restart SQL server to bring the cache online.
3. Create a 4K cache pool using the named cache with sp_poolconfigure testcache.
Example:
sp_poolconfigure testcache,"7.5M","4K"
4. Change database to single user mode to allow the use of binding cache.
5. Bind the named cache to syslogs for the database with sp_bindcache.
Example:
sp_bindcache testcache, "pubs2", "syslogs"
6. Use the database.
Example:
use pubs2
7. Change log I/O size to 4K in the database with sp_logiosize.
Example:
sp_logiosize "4"
Extended Example:
To demonstrate, follow the steps above using the example commands shown, then use sp_sysmon to monitor the behaviour of the server. This was tested by dropping all records in selected pubs2's user tables (e.g. delete authors etc...) and inserting all records back using a modified version of instpbs2 scripts. The sp_sysmon output is shown below.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
===============================================================================
Sybase SQL Server System Performance Report
===============================================================================
Run Date Sep 08, 1997
Statistics Cleared at 14:58:45
Statistics Sampled at 15:00:45
Sample Interval 2 min.
===============================================================================
Kernel Utilization
------------------
Engine Busy Utilization:
Engine 0 10.4 %
CPU Yields by Engine per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 85.9 31.1 10255 100.0 %
Network Checks
0.0 0.0 0 n/a
Disk I/O Checks
Total Disk I/O Checks 5142.2 1860.7 614036 n/a
Checks Returning I/O 4.4 1.6 526 0.1 %
Avg Disk I/Os Returned n/a n/a 1.04183 n/a
===============================================================================
Task Management per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Connections Opened 0.0 0.0 2 n/a
Task Context Switches by Engine
Engine 0 13.0 4.7 1558 100.0 %
Task Context Switches Due To:
Voluntary Yields 2.9 1.1 350 22.5 %
Cache Search Misses 0.8 0.3 99 6.4 %
System Disk Writes 0.0 0.0 5 0.3 %
I/O Pacing 0.3 0.1 35 2.2 %
Logical Lock Contention 0.0 0.0 0 0.0 %
Address Lock Contention 0.0 0.0 0 0.0 %
Log Semaphore Contention 0.0 0.0 0 0.0 %
Group Commit Sleeps 0.0 0.0 0 0.0 %
Last Log Page Writes 2.8 1.0 334 21.4 %
Modify Conflicts 0.0 0.0 0 0.0 %
I/O Device Contention 0.0 0.0 0 0.0 %
Network Packet Received 2.8 1.0 333 21.4 %
Network Packet Sent 2.7 1.0 319 20.5 %
SYSINDEXES Lookup 0.0 0.0 0 0.0 %
Other Causes 0.7 0.3 83 5.3 %
===============================================================================
Transaction Profile
-------------------
Transaction Summary per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Committed Xacts 2.8 n/a 330 n/a
Transaction Detail per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Inserts
Heap Table 1.8 0.7 220 65.7 %
Clustered Table 1.0 0.3 115 34.3 %
------------------------- ------------ ------------ ----------
Total Rows Inserted 2.8 1.0 335 48.8 %
Updates
Deferred 0.1 0.0 16 100.0 %
Direct In-place 0.0 0.0 0 0.0 %
Direct Cheap 0.0 0.0 0 0.0 %
Direct Expensive 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Rows Updated 0.1 0.0 16 2.3 %
Deletes
Deferred 0.1 0.0 16 4.8 %
Direct 2.7 1.0 319 95.2 %
------------------------- ------------ ------------ ----------
Total Rows Deleted 2.8 1.0 335 48.8 %
===============================================================================
Transaction Management
----------------------
ULC Flushes to Xact Log per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
by Full ULC 0.2 0.1 21 5.7 %
by End Transaction 2.8 1.0 329 88.7 %
by Change of Database 0.0 0.0 0 0.0 %
by System Log Record 0.1 0.0 15 4.0 %
by Other 0.1 0.0 6 1.6 %
------------------------- ------------ ------------ ----------
Total ULC Flushes 3.1 1.1 371
ULC Log Records 19.4 7.0 2321 n/a
Max ULC Size n/a n/a 2044 n/a
ULC Semaphore Requests
Granted 39.8 14.4 4749 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total ULC Semaphore Req 39.8 14.4 4749
Log Semaphore Requests
Granted 4.2 1.5 500 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Log Semaphore Req 4.2 1.5 500
Transaction Log Writes 3.2 1.2 381 n/a
Transaction Log Alloc 0.8 0.3 92 n/a
Avg # Writes per Log Page n/a n/a 4.14130 n/a
===============================================================================
Index Management
----------------
Nonclustered Maintenance per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Ins/Upd Requiring Maint 2.4 0.9 285 n/a
# of NC Ndx Maint 3.6 1.3 426 n/a
Avg NC Ndx Maint / Op n/a n/a 1.49474 n/a
Deletes Requiring Maint 2.4 0.9 285 n/a
# of NC Ndx Maint 3.6 1.3 426 n/a
Avg NC Ndx Maint / Op n/a n/a 1.49474 n/a
RID Upd from Clust Split 0.0 0.0 2 n/a
# of NC Ndx Maint 0.1 0.0 9 n/a
Avg NC Ndx Maint / Op n/a n/a 4.50000 n/a
Page Splits 0.0 0.0 3 n/a
Retries 0.0 0.0 0 0.0 %
Deadlocks 0.0 0.0 0 0.0 %
Empty Page Flushes 0.0 0.0 0 0.0 %
Add Index Level 0.0 0.0 1 33.3 %
Page Shrinks 0.0 0.0 5 n/a
===============================================================================
Lock Management
---------------
Lock Summary per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Lock Requests 64.2 23.2 7668 n/a
Avg Lock Contention 0.0 0.0 0 0.0 %
Deadlock Percentage 0.0 0.0 0 0.0 %
Lock Detail per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Exclusive Table
Granted 1.1 0.4 128 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total EX-Table Requests 1.1 0.4 128 1.7 %
Shared Table
Granted 0.1 0.0 6 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total SH-Table Requests 0.1 0.0 6 0.1 %
Exclusive Intent
Granted 2.7 1.0 319 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total EX-Intent Requests 2.7 1.0 319 4.2 %
Shared Intent
Granted 6.1 2.2 730 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total SH-Intent Requests 6.1 2.2 730 9.5 %
Exclusive Page
Granted 6.3 2.3 749 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total EX-Page Requests 6.3 2.3 749 9.8 %
Update Page
0.0 0.0 0 n/a
------------------------- ------------ ------------ ----------
Total UP-Page Requests 0.0 0.0 0 0.0 %
Shared Page
Granted 13.2 4.8 1581 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total SH-Page Requests 13.2 4.8 1581 20.6 %
Exclusive Address
Granted 15.5 5.6 1852 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total EX-Address Requests 15.5 5.6 1852 24.2 %
Shared Address
Granted 19.3 7.0 2303 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total SH-Address Requests 19.3 7.0 2303 30.0 %
Last Page Locks on Heaps
Granted 1.8 0.7 220 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Last Pg Locks 1.8 0.7 220 2.9 %
Deadlocks by Lock Type per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
Deadlock Detection
Deadlock Searches 0.0 0.0 0 n/a
Lock Promotions
0.0 0.0 0 n/a
===============================================================================
Data Cache Management
---------------------
Cache Statistics Summary (All Caches)
-------------------------------------
Cache Search Summary
Total Cache Hits 65.0 23.5 7760 98.0 %
Total Cache Misses 1.3 0.5 161 2.0 %
------------------------- ------------ ------------ ----------
Total Cache Searches 66.3 24.0 7921
Cache Turnover
Buffers Grabbed 0.8 0.3 99 n/a
Buffers Grabbed Dirty 0.0 0.0 0 0.0 %
Cache Strategy Summary
Cached (LRU) Buffers 93.0 33.7 11110 100.0 %
Discarded (MRU) Buffers 0.0 0.0 0 0.0 %
Large I/O Usage
Large I/Os Performed 0.7 0.2 82 88.2 %
Large I/Os Denied 0.1 0.0 11 11.8 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 0.8 0.3 93
Large I/O Effectiveness
Pages by Lrg I/O Cached 0.0 0.0 2 n/a
Pages by Lrg I/O Used 0.0 0.0 0 0.0 %
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
-------------------------------------------------------------------------------
default data cache
per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
Utilization n/a n/a n/a 91.0 %
Cache Searches
Cache Hits 59.4 21.5 7098 98.4 %
Found in Wash 0.2 0.1 25 0.4 %
Cache Misses 0.9 0.3 113 1.6 %
------------------------- ------------ ------------ ----------
Total Cache Searches 60.4 21.9 7211
Pool Turnover
2 Kb Pool
LRU Buffer Grab 0.8 0.3 98 100.0 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 0.8 0.3 98
Buffer Wash Behavior
Statistics Not Available - No Buffers Entered Wash Section Yet
Cache Strategy
Cached (LRU) Buffers 88.2 31.9 10535 100.0 %
Discarded (MRU) Buffers 0.0 0.0 0 0.0 %
Large I/O Usage
0.0 0.0 0 n/a
Large I/O Detail
No Large Pool(s) In This Cache
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
-------------------------------------------------------------------------------
testcache
per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
Utilization n/a n/a n/a 9.0 %
Cache Searches
Cache Hits 5.5 2.0 662 93.2 %
Found in Wash 0.0 0.0 0 0.0 %
Cache Misses 0.4 0.1 48 6.8 %
------------------------- ------------ ------------ ----------
Total Cache Searches 5.9 2.2 710
Pool Turnover
4 Kb Pool
LRU Buffer Grab 0.0 0.0 1 100.0 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 0.0 0.0 1
Buffer Wash Behavior
Statistics Not Available - No Buffers Entered Wash Section Yet
Cache Strategy
Cached (LRU) Buffers 4.8 1.7 575 100.0 %
Discarded (MRU) Buffers 0.0 0.0 0 0.0 %
Large I/O Usage
Large I/Os Performed 0.7 0.2 82 88.2 %
Large I/Os Denied 0.1 0.0 11 11.8 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 0.8 0.3 93
Large I/O Detail
4 Kb Pool
Pages Cached 0.0 0.0 2 n/a
Pages Used 0.0 0.0 0 0.0 %
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
===============================================================================
Procedure Cache Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Procedure Requests 2.2 0.8 260 n/a
Procedure Reads from Disk 0.1 0.0 6 2.3 %
Procedure Writes to Disk 0.0 0.0 0 0.0 %
Procedure Removals 0.0 0.0 2 n/a
===============================================================================
Memory Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Pages Allocated 0.0 0.0 0 n/a
Pages Released 0.0 0.0 0 n/a
===============================================================================
Recovery Management
-------------------
Checkpoints per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
===============================================================================
Disk I/O Management
-------------------
Max Outstanding I/Os per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Server n/a n/a 10 n/a
Engine 0 n/a n/a 10 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 4.6 1.7 548 n/a
Completed Disk I/O's
Engine 0 4.6 1.7 548 100.0 %
------------------------- ------------ ------------ ---------- ----------
Total Completed I/Os 4.6 1.7 548
Device Activity Detail
----------------------
d:\sybase\DATA\SYBPROCS.DAT
sysprocsdev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
d:\sybase\data\psdemodb.dev
psdemodb_dev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
d:\sybase\data\pubs2d.dat
pubs2d1 per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads 0.0 0.0 3 0.8 %
Writes 3.2 1.2 383 99.2 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 3.2 1.2 386 70.4 %
Device Semaphore Granted 3.2 1.2 386 100.0 %
Device Semaphore Waited 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
d:\sybase\data\pubs2d2.dat
pubs2d2 per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads 0.8 0.3 96 59.3 %
Writes 0.6 0.2 66 40.7 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 1.4 0.5 162 29.6 %
Device Semaphore Granted 1.4 0.5 162 100.0 %
Device Semaphore Waited 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
d:\sybase\data\tempdb.dev
tempdb_dev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
master.dat
master per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
testdb1.dat
testdb1 per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
testlog1.dat
testlog1 per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
---------------------------------------------------------------------------
===============================================================================
Network I/O Management
----------------------
Total Network I/O Requests 5.5 2.0 652 n/a
Network I/Os Delayed 0.0 0.0 0 0.0 %
Total TDS Packets Received per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total TDS Packets Rec'd 0.0 0.0 0
Total Bytes Received per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total Bytes Rec'd 0.0 0.0 0
-----------------------------------------------------------------------------
Total TDS Packets Sent per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total TDS Packets Sent 0.0 0.0 0
Total Bytes Sent per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total Bytes Sent 0.0 0.0 0
=============================== End of Report =================================
(return status = 0)
No comments:
Post a Comment