2.
SQLServer
Related performance counters:
Process: Working Set
Working
Set
The counter represents the amount of memory occupied by a process. When monitoring, you can choose the process you need, such as:
SQL Server
Process
Working set
(
Process SQLSERVR
)
SQL Server: Buffer Manager: Buffer Cache Hit Ratio
Cache hits hit rate,
Best ratio is
90%
Or higher. Increase the memory until this value continues to be higher than
90%
Mean
90%
The above data requests can be obtained from the data buffer.
If this value is low, it continues to be lower than
80%
It is necessary to add more memory.
SQL Server: Buffer Manager: Total Pages
This counter is targeted
SQL Server
Physical reading and writing, not physical reading and writing of the entire system. When the physical read and write is time consuming, the above two values should not be too large.
SQL Server: Memory Manager: Total Server Memory (KB)
If
Total Server Memory (KB)
The counter value has been high with the physical memory size of the computer, and more memory may be required.
Full Scans / Sec
(
SQL Server
:
Access Methods
)
(
Full table scan
/
second
)
The number of complete scans per second. If this counter is displayed
1
or
2
high.
[
Full table scan is very much affected by performance, generally optimized
SQL
Query statements, try to avoid full table scans
]
Page Splits / Sec
(
SQL Server
:
Access Methods
)
(
Page segmentation
/
second
)
The number of pages divided per second due to data update operations.
[
The page split is to move half of the data on the full page to two new pages in order to make space inserted in this page. Page demolition reduces performance.
]
Lazy Writes / Sec (SEC
Inert
/
second
)
The number of buffers written per second. The value is best
0
.
[
It is simple to say that the inert write process is:
If the cache empty list becomes too small, inert write will scan the entire cache, recover the unused page, which has been kept with free space. If the value of the counter is small, it means that the free space of the cache is enough.
]
Average Latch Wait Ti M E (M s)
Average latch wait time
(
millisecond
))
One
SQL Server
The thread must wait for the average time of a latch, in milliseconds. If this value is high, you may have experienced serious competition.
Latch Waits / Sec (SEC (
Latch
/
second
)
Wait quantity per second. If this value is high, you are experiencing a lot of competition for resources.
Number of Deadlocks / Sec
(
SQL Server
:
LOCKS
)
(
Number of deadlocks
/
second
)
Quantity of lock requests that lead to deadlocks
Average Wait Time
(
SQL Server
:
LOCKS
)
(ms)
(
Average waiting time
(
millisecond
))
The average wait time of thread waits for some type of lock
Lock Blocks
(
SQL Server: Memory Manager
)
:
The number of lock blocks on the server is on the page, row, or resource like this. Don't want to see a growth value.
Use Connections
(SQL Server
):
Current user connection
It can be seen from the above counter:
SQL Server
Current user connection number, memory usage, physical
I / O
, Resource competition situation, whether there is a deadlock, put these as upgrade hardware
or
Adjustment parameters
Or optimize the reference.
