| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Performance and Tuning Guide Volumes 1 - 3 (Online Only) |
![[-]](/dwicons/i_colpse.gif)
| Chapter 39 Monitoring Performance with sp_sysmon |
Chapter 39
This chapter describes output from sp_sysmon, a system procedure that produces Adaptive Server performance data. It includes suggestions for interpreting its output and deducing possible implications.
sp_sysmon output is most valuable when you have a good understanding of your Adaptive Server environment and its specific mix of applications. Otherwise, you may find that sp_sysmon output has little relevance.
|
| Using |
| When to run |
|
| Invoking |
| Fixed time intervals |
| Using begin_sample and end_sample |
| Specifying report sections for output |
| Specifying the application detail parameter |
| Redirecting output to a file |
|
| How to use the reports |
![[+]](/dwicons/i_expand.gif)
| Reading output |
|
| Interpreting the data |
| Sample interval and time reporting |
|
| Kernel utilization |
| Sample output |
| Engine busy utilization |
| CPU yields by engine |
|
| Network checks |
| Disk I/O checks |
|
| Total disk I/O checks |
|
| Worker process management |
| Sample output |
| Worker process requests |
| Worker process usage |
| Memory requests for worker processes |
| Avg mem ever used by a WP |
|
| Parallel query management |
| Sample output |
| Parallel query usage |
| Merge lock requests |
| Sort buffer waits |
|
| Task management |
| Sample output |
| Connections opened |
| Task context switches by engine |
|
| Task context switches due to |
|
| Application management |
| Requesting detailed application information |
| Sample output |
|
| Application statistics summary (all applications) |
|
| Per application or per application and login |
|
| ESP management |
|
| Sample output |
|
| Housekeeper task activity |
| Sample output |
| Buffer cache washes |
| Garbage collections |
| Statistics updates |
|
| Monitor access to executing SQL |
|
| Sample output |
|
| Transaction profile |
| Sample output |
|
| Transaction summary |
| Transaction detail |
|
| Inserts |
|
| Updates and update detail sections |
|
| Deletes |
|
| Transaction management |
| Sample output |
|
| ULC flushes to transaction log |
| Total ULC flushes |
| ULC log records |
| Maximum ULC size |
| ULC semaphore requests |
|
| Log semaphore requests |
| Transaction log writes |
| Transaction log allocations |
| Avg # writes per log page |
|
| Index management |
| Sample output |
|
| Nonclustered maintenance |
|
| Page splits |
| Page shrinks |
| Index scans |
|
| Metadata cache management |
| Sample output |
| Open object, index, and database usage |
| Object and index spinlock contention |
|
| Hash spinlock contention |
|
| Lock management |
| Sample output |
| Lock summary |
|
| Lock detail |
| Table lock hashtable |
| Deadlocks by lock type |
|
| Deadlock detection |
| Lock promotions |
| Lock time-out information |
|
| Data cache management |
| Sample output |
|
| Cache statistics summary (all caches) |
|
| Cache management by cache |
|
| Procedure cache management |
| Sample output |
| Procedure requests |
| Procedure reads from disk |
| Procedure writes to disk |
| Procedure removals |
|
| Memory management |
| Sample output |
| Pages allocated |
| Pages released |
|
| Recovery management |
| Sample output |
|
| Checkpoints |
| Average time per normal checkpoint |
| Average time per free checkpoint |
| Increasing the housekeeper batch limit |
|
| Disk I/O management |
| Sample output |
| Maximum outstanding I/Os |
|
| I/Os delayed by |
|
| Requested and completed disk I/Os |
|
| Device activity detail |
|
| Network I/O management |
| Sample output |
| Total network I/Os requests |
| Network I/Os delayed |
| Total TDS packets received |
| Total bytes received |
| Average bytes received per packet |
| Total TDS packets sent |
| Total bytes sent |
| Average bytes sent per packet |
| Reducing packet overhead |
|
|