Reports:
• Count – number of times Replication Agent issued a ct_sendpassthru to Replication Server.
• Amount of Time – length of time, in milliseconds, Replication Agent spent processing results from Replication Server.
• Longest Wait – longest elapsed time, in milliseconds, the Replication Agent spent processing results.
• Average Wait – average length of time, in milliseconds, Replication Agent spent processing results
Replication Agent
A
add index level, sp_sysmon report 90 address locks
deadlocks reported by sp_sysmon 103 sp_sysmon report on 101
allocation pages large I/O and 123 appl_and_login
54
application design 3 user connections and 45 application execution precedence
tuning with sp_sysmon 54 applications
CPU usage report 59 disk I/O report 60 network I/O report 60 priority changes 60
ascinserts (dbcc tune parameter) 89 asynchronous I/O
buffer wash behavior and 122 sp_sysmon report on 133 asynchronous prefetch
denied due to limits 115
average disk I/Os returned, sp_sysmon report on 38 average lock contention, sp_sysmon report on 99
B
batch processing
performance monitoring and 3
blocking network checks, sp_sysmon report on 35 buffers
sp_sysmon report on 113, 118 cache wizard 13, 14
cache, procedure
task switching and 47 cached (LRU) buffers 113 caches, data
strategies chosen by optimizer 122 task switching and 47
total searches 120 sp_sysmon and 128
clustered table, sp_sysmon report on 68 configuration (server)
performance monitoring and 3 sp_sysmon and 3
connections
opened (sp_sysmon report on) 45 contention 3
disk I/O 131 hash spinlock 94
last page of heap tables 101 lock 48, 99
log semaphore requests 51 yields and 46
context switches 45 counters, internal 1 CPU
checkpoint process and usage 24, 33 processes and 13
server use while idle 24, 32 sp_sysmon report and 11 yielding and overhead 36
Index
yields by engine 26, 34 CPU usage
applications, sp_sysmon report on 59 logins, sp_sysmon report on 59 lowering 24, 32
sp_sysmon report on 20, 31
D
data caches
management, sp_sysmon report on 107 database design
ULC flushes and 74 dbcc tune
sp_sysmon report on 100 delete operations
index maintenance and 84 devices
activity detail 135 adding 3 semaphores 136
deviochar (dbcc tune parameter) 130 dirty reads
modify conflicts and 52 requests 124
restarts 117
sp_sysmon report on 117
discarded (MRU) buffers, sp_sysmon report on 113 disk devices
adding 3 average I/Os 38
I/O checks report (sp_sysmon) 37 I/O management report (sp_sysmon) 131 I/O structures 133
disk I/O
application statistics 60 sp_sysmon report on 131
disk i/o structures configuration parameter 133
E
end transaction, ULC flushes and 74 engines
sp_sysmon report on 60
F
fragmentation, data large I/O and 114 free checkpoints 130 full ULC, log flushes and 77
G
grabbed dirty, sp_sysmon report on 120
H
housekeeper free write percent configuration parameter 129
housekeeper task
batch write limit 130 buffer washing 62 checkpoints and 129 garbage collection 62 reclaiming space 62
sp_sysmon and 128 sp_sysmon report on 61
I
Exceeding I/O batch size 47 limits 133
limits, effect on asynchronous prefetch 115 maximum outstanding 133
requested 134
server-wide and database 131 structures 133
total 136
i/o polling process count configuration parameter network checks and 36
idle CPU, sp_sysmon report on 26, 34 indexes
add levels statistics 90 maintenance statistics 83 management 82 insert operations
clustered table statistics 68 heap table statistics 68 index maintenance and 84 total row statistics 69
K
kernel
engine busy utilization 24, 32 utilization 20, 31 total requests 114, 124 usage 114, 123
last page locks on heaps in sp_sysmon report 101 lock hash table
sp_sysmon report on 101
lock hashtable size configuration parameter sp_sysmon report on 101
locking
contention and 48 sp_sysmon report on 99 locks
deadlock percentage 100 sp_sysmon report on 100 total requests 99
loops
runnable process search count and 24, 26, 32, 34
LRU replacement strategy
buffer grab in sp_sysmon report 120
M
maintenance tasks indexes and 84
max async i/os per engine configuration parameter tuning 134
max async i/os per server configuration parameter tuning 133
maximum outstanding I/Os 133
maximum ULC size, sp_sysmon report on 78 memory
allocated 127 released 127
sp_sysmon report on 127 system procedures used for 95 metadata caches
finding usage statistics 94
“Modify conflicts” in sp_sysmon report 52 monitoring
performance 1
multidatabase transactions 66, 74
Index
i/o polling process count and 36 packets 53
sp_sysmon report on 35 total I/O checks 36
nonblocking network checks, sp_sysmon report on 35 nonclustered indexes
monitoring server CPU usage 24, 32 outstanding I/O limit 134
overhead
CPU yields and 36 sp_sysmon 2
P
packets, network
average size received 139 average size sent 139 received 138 sent 139
size, configuring 53
page allocation to transaction log 81 page requests, sp_sysmon report on 117 page splits 85
disk write contention and 47 retries and 89
performance
lock contention and 48 monitoring 8
speed and 3 pools, data cache
sp_sysmon report on size 121 priority
changes, sp_sysmon report on 57, 60 procedure cache
management with sp_sysmon 124 processes (server tasks)
sp_sysmon report on 128 resource limits
sp_sysmon report on violations 60 response time
CPU utilization and 24, 33 sp_sysmon report on 12 retries, page splits and 89 row ID (RID) 85
updates from clustered split 85 updates, index maintenance and 85
S
searches skipped, sp_sysmon report on 104 semaphores 51
disk device contention 136 log contention 51
user log cache requests 80
server config limit, in sp_sysmon report 133 servers log semaphore contention 51
sp_monitor system procedure sp_sysmon interaction 2
sp_monitorconfig system procedure 94 statistics
cache hits 113, 119 deadlocks 100 index add levels 90 index maintenance 83
index maintenance and deletes 84 large I/O 114
locks 96, 99
recovery management 128 transactions 67
stored procedures
sp_sysmon report on 125, 126 stress tests, sp_sysmon and 3
system log record, ULC flushes and (in sp_sysmon report) 75
T
tabular data stream (TDS) protocol network packets and 53 packets received 138 packets sent 139 tasks
context switches 45 testing
performance monitoring and 3 throughput
adding engines and 25, 33 CPU utilization and 24, 33 group commit sleeps and 52 log I/O size and 52
monitoring 12 pool turnover and 120 time interval
sp_sysmon 4
total cache hits in sp_sysmon report 113 total cache misses in sp_sysmon report on 113 total cache searches in sp_sysmon report 113 total disk I/O checks in sp_sysmon report 37 total lock requests in sp_sysmon report 99 total network I/O checks in sp_sysmon report 36 transaction logs
turnover, pools (sp_sysmon report on) 120 turnover, total (sp_sysmon report on) 121
U
update operations
index maintenance and 84
update page deadlocks, sp_sysmon report on 103 user connections
application design and 45 user log cache (ULC)
log records 78 maximum size 78 semaphore requests 80
user log cache size configuration parameter 79 increasing 77
sp_sysmon report on 26, 34
Index