This article describes that there is a comprehensive system for the operation of Adaptive Server systems via sp_sysmon, which is conducive to better familiarity with system performance, more efficient system management, reasonably uses and configures system resources to achieve system performance tuning. purpose.
From 18 aspects, it is known to use system performance status and use environmental parameters to perform performance tuning when appropriate:
1, kernel management (kerNAL) 2, Application Management (AppMGMT) 3, Data Cache Management (DCACHE) 4, ESP Management (ESP) 5, Index Management (IndexMGMT) 6, Lock Management (Locks) 7, Memory Management (Memory) 8, Metadata Cache Management (MDCache) 9, Task Management (TaskMGMT) 10, Monitor Access SQL Execution (Monaccess 11, Network I / O Management (Netio) 12, Parallel Query Management (Parallel) 13, process cache Management (PCACHE) 14, Recovery 15, Transaction Management (XACTMGMT) 16, Transaction Summary (XACTSUM) 17, Disk I / O Management (DISKIO) 18, Work Process Management (WPM)
English short words after parentheses are the module parameters.
Environment: 1. Data Tables used in user databases Authts and Article
2, the data table has 100,000 line data
3, the user has basic database table operation permissions such as query, modification, deletion, etc.
Steps: Perform sp_sysmon "00:10:00" (Server level system storage process, do not need to open a database), or execute the procedure of the following format, view the specific operation batch command corresponding system performance:
SP_SYSMON Begin_Sample
SQL statement or storage process
sp_sysmon commit_sample
This experiment adopts sp_sysmon "hh: mm: ss", performance module name.
Conclusion: Through this practice, you can learn about the current system's operating conditions in all aspects, performance and unbalanced uncoordinated, learn to use the corresponding parameters and measures to solve and tun, constantly compare the performance before and after comparison Status, ultimately improve system performance.
Description: 1. The command execution result set is the same as follows, each block exercise is no longer one by one:
============================================================================================================================================================================================================= ====================
Sybase Adaptive Server Enterprise System Performance Report
============================================================================================================================================================================================================= ==================== Server Version: Adaptive Server Enterprise / 11.9.2 / 1031 / P / NT (IX86) / OS 3.
Server Name: Server is unnamed
Run Date: May 28, 2001
Statistics Cleared At: 15:57:27
Statistics Sampled at: 16:07:28
Sample interval: 00:10:00
2. Perform each column information of the result set:
Per Sec: Average per second during sampling
Per XACT: The average of each transaction during the sampling period
Count: Total value per second during sampling
% of total: percentage of total, different cases
3. Result set corresponds to give performance information description, analysis, and adjustability
4, this practice only gives the monitoring result of the partial module (possibly by applying), using sp_sysmon "hh: mm: SS" to see all details.
Disk I / O Management
Command line: sp_sysmon "00:10:00", DISKIO
result:
Disk I / O Management (Disk I / O Management)
------------------ Reports the overall disk I / O behavior of the Server, including the Semaphore for reading, writing, and logical devices.
Max OutStanding I / OS Per Secy XACT Count% of Total
Maximum significant I / O number: The maximum I / O number of server overall overhead, is expressed by Server and the engine, respectively.
------------------------ ------------ --------------------
Server N / A N / A 10 N / A
ENGINE 0 N / A N / A 10 N / A
I / OS Delayed by
The system encounters I / O latency, similar to I / O is blocked by Server or operating system restrictions. Most operating systems have a parameter limit for asynchronous I / O number. You can view parameters "Allow SQL Server Async I / O" with sp_configure.
Disk I / O Structures N / A N / A 0 N / A
A number of I / Os that reach the disk I / O structure limit thereby being delayed. When the server exceeds the number of control blocks of the available disk I / O, I / O will be delayed because Server needs to obtain a disk I / O control block by task when starting an I / O request. If its value is not zero, add the number of disk I / O control blocks by setting the parameter value "Disk I / O Structures" (default 256), if the operating system allows us to set it as much as possible to use optical disk I / O The chance of the structure is minimized. Server Config Limit N / A N / A 0 N / A
Adjust the number of asynchronous disk I / O requested by parameters "Max async I / OS Per Server" (default 2147483647).
ENGINE CONFIG LIMIT N / A N / A 0 N / A
The engine configuration maximum asynchronous disk I / O request number is limited, with the parameter "max async I / OS per engine" to see and adjust.
OPERATING SYSTEM LIMIT N / A N / A 0 N / A
The limit number of the operating system views the operating system documentation.
Device Activity Detail
----------------------
Device:
Master.dat
Master Per Secy XACT% of Total
------------------------ ------------ --------------------
Reads
APF 0.0 0.0 0 0.0%
NON-APF 0.2 0.0 102 78.5%
Writes 0.0 0.0 28 21.5%
------------------------ ------------ --------------------
Total I / OS 0.2 0.0 130 1.5%
Device Semaphore Grand 0.2 0.0 130 100.0%
Device Semaphore Waited 0.0 0.0 0 0.0%
-------------------------------------------------- ---------------------------
