Can diagnose SP

This article describes the adaptive server system through the Sp_sysmon of the operation of a comprehensive system to understand, is conducive to better understanding of system performance, more effective system management, rational use and allocation of system resources, to achieve system performance tuning purposes.
Learn from 18 aspects of the performance of the system and use environment parameters to tune performance 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 to the implementation of SQL (monaccess) 11, network I/O management (netio)
12. Parallel query Management (parallel) 13, process cache Management (Pcache) 14, Recovery Management (recovery)
15, transaction Management (XACTMGMT) 16, transaction profile (Xactsum) 17, disk I/O management (Diskio)
18. Work Process Management (WPM)

The English short word after parentheses is the module parameter.

Environment: 1, the user database has the data table auths and article for practice

2, the data table has 100,000 rows of data

3, the user has the query, the modification, the deletion and so on basic database table Operation Authority

Step: Perform Sp_sysmon "00:10:00" (server-level system storage process, no need to open a database), or perform the following format process to view the specific operation batch command corresponding to the system performance:

Sp_sysmon Begin_sample

SQL statement or storage process

Sp_sysmon Commit_sample

This experiment uses Sp_sysmon "HH:MM:SS", the performance module name.

Conclusion: Through this exercise, we can understand the current system in various aspects of the system operating conditions, performance problems and imbalance imbalance, learn to use the corresponding parameters and measures to solve and tune, and constantly compare the performance before and after adjustment, and ultimately improve system performance.

Note: 1, the command execution result set at the beginning of the same as the following, the block exercise is no longer one by one listed:

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

Sybase Adaptive Server Enterprise System Performance

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

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, the implementation of the result set of each column information tip:

Per sec: average of each second during sampling

Per XACT: average of each transaction submitted during sampling

Count: Total value per second during sampling

% of total: percentages, depending on different circumstances

3, the result set corresponding to the performance of the description, analysis and tunable description

4, this exercise only gives some of the module monitoring results (may have deletions), with Sp_sysmon "HH:MM:SS" can see all the details.

Disk I/O management

Command line: Sp_sysmon "00:10:00", Diskio

Results:
Disk I/O Management (diskette I/O management)
-------------------Report Server overall disk I/O behavior, including semaphore contention on read, write, and logical devices.

Max Outstanding I/Os per sec/XACT count% of total

Maximum significant I/O: The maximum number of I/O to the server's overall cost, represented by the server and engine, respectively.

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

Server N/A, n/a

Engine 0 N/a N/A

I/Os delayed by

The system is experiencing I/O latency problems, similar to I/O being blocked by server or operating system restrictions. Most operating systems have a parameter that restricts the number of asynchronous I/O. You can use sp_configure to view the parameters "Allow SQL Server async I/O."

Disk I/O structures N/a n/a 0 N/A

The number of I/O that has been delayed by reaching the disk I/O structure limit. When the server exceeds the number of control blocks available for disk I/O, I/O is delayed because the server requires a task to get a disk I/O control block when it starts an I/O request. If its value is Non-zero, increase the number of disk I/O control blocks by setting an increase in the parameter value "disk I/O structures" (default 256), if the operating system allows as large a size as possible, to minimize the chance of using the disk I/O structure.

Server Config Limit N/a N/a 0 N/A

Adjust the number of asynchronous disk I/O requests used by the server once using the parameter "max async I/Os Per Server" (default 2147483647).

Engine Config Limit N/a N/a 0 N/A

The engine configures the maximum number of asynchronous disk I/O requests, viewed and adjusted with the parameter "max async I/Os per engine".

Operating System Limit N/a N/a 0 N/A

Limit number of operating systems view operating system documentation.

Device Activity Detail

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

Device:

Master.dat

Master per sec/XACT count% 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 granted 0.2 0.0 130 100.0%

Device Semaphore waited 0.0 0.0 0 0.0%

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