The meaning of performance monitor parameters, what are the commonly used key parameters?

Explanation of parameter information of SQL Server performance monitor

SQL Server performance counters:

Access methods are used to monitor the methods of accessing logical pages in the database.

The number of complete scans per second (the number of complete scans per second) is unlimited. It can be a basic table scan or a full index scan. If the value displayed by this counter is higher than 1 or 2, you should analyze your query to determine whether full table scanning is really needed and whether SQL query can be optimized.

Page splits/sec (page splits/sec) The number of page splits per second due to data update operations.

Buffer Manager: Monitoring Microsoft? SQL Server？ How to use it: memory stores data pages, internal data structures and process cache; When SQL Server reads database pages from disk and writes database pages to disk, counters monitor physical I/O.. Monitoring the memory and counters used by SQL Server helps to determine whether there is a bottleneck, because there is a lack of available physical memory in the cache to store frequently accessed data. If so, SQL Server must retrieve data from disk. Whether you can improve query performance by adding more memory or making more memory available for data caching or SQL Server internal structure.

How often SQL Server needs to read data from disk. Compared with other operations, such as memory access, physical I/O takes a lot of time. Minimizing physical I/O can improve query performance.

. Page Reads/sec: The number of physical database page reads issued per second. This statistic shows the total number of physical page reads between all databases. Due to the high overhead of physical I/O, overhead can be minimized by using larger data cache, intelligent index, more efficient query or changing database design.

. Page writes per second (. Pages written per second) The number of pages written per second by the physical database.

. Buffer cache hit rate. The ratio of unread pages in Buffer Cache/Buffer Pool to all pages in the whole buffer pool. The percentage of pages that can be found in the cache without reading from disk. This ratio is the total number of cache hits divided by the total number of cache lookups since the SQL Server instance was started. After a long time, this ratio has not changed much. Because the cost of reading data from cache is much lower than that of reading data from disk, a higher value is usually needed. In general, you can improve the cache hit rate by increasing the amount of available memory in SQL Server. The counter value depends on the application, but the ratio is preferably 90% or higher. Increase the memory until the value continues to be higher than 90%, which means that more than 90% of data requests can get the required data from the data buffer.

Lazy Writes/sec (lazy writes/sec) The number of buffers written by lazy writers per second. The value is preferably 0.

The cache manager object is to monitor Microsoft? SQL Server？ How to use memory to store objects, such as stored procedures, special and prepared Transact-SQL statements and triggers.

Cache hit rate (cache hit rate, all caches). In SQL Server, cache can include log cache, buffer cache and procedure cache, which is an overall ratio. The ratio of cache hits to lookups. This is a very good counter to see how effective the SQL Server cache is for your system. If the value is very low and below 80%, you need to add more memory.

Latches are used to monitor internal SQL Server resource locks called latches. Monitoring latches to identify user activity and resource usage helps identify performance bottlenecks.

Average Latch Wait Ti m e (m s) (average latch wait time (milliseconds)) The average time in milliseconds that a SQL Server thread must wait for a latch. If this value is high, you may encounter serious competition problems.

Latch Waits/sec (Latch Waits/sec) Number of times to wait for latches per second. If this value is high, it means that you are experiencing a lot of resource competition.

Locks provides information about SQL Server locks on a single resource type. Locks are placed on SQL Server resources (such as reading or modifying rows in a transaction) to prevent multiple transactions from using resources concurrently. For example, if a transaction adds a exclusive locks (X lock) to a row of a table, no other transaction can modify the row until the lock is released. Using locks as little as possible can improve concurrency and thus improve performance. You can monitor multiple instances of the Locks object at the same time, and each instance represents a lock on a resource type.

Deadlocks per second Number of lock requests that resulted in deadlocks.

Average wait time (milliseconds) (average wait time (milliseconds)) The average wait time for a thread to wait for a particular type of lock.

Lock Requests/sec (Lock Requests/sec) Number of lock requests of a specific type per second.

Memory manager: used to monitor the overall server memory usage, estimate user activities and resource usage, and help identify performance bottlenecks. Monitoring the memory used by an instance of SQL Server helps to determine:

Is the bottleneck due to the lack of available physical memory to store frequently accessed data in the cache? If so, SQL Server must retrieve data from disk.

Whether you can improve query performance by adding more memory or making more memory available for data caching or SQL Server internal structure.

Blocks Locked: The number of blocks locked on the server. Locked on a resource such as a page, row, or table. I don't want to see value added.

Total server memory: the total dynamic memory currently used by the sql server server.

Some counters needed to monitor IIS:

Internet information services worldwide:

File cache hit percentage, file cache refresh, file cache hit

File Cache Hits% is the proportion of cache hits in all cache requests, which reflects the working situation of file cache settings in IIS. For a website that is mostly composed of static pages, this value should be kept at around 80%. File Cache Hits is a specific value of file cache hits, and File CacheFlushes is the number of file cache flushes since the server was started. If the refresh is too slow, it will waste memory. If the refresh is too fast, the objects in the cache will be discarded and generated too frequently to play the role of cache. By comparing file cache hits and file cache refreshes, the ratio of cache hit rate to cache emptying rate can be obtained. By observing its two values, you can get a suitable refresh value (refer to IIS settings, such as ObjectTTL, MemCacheSize, MaxCacheFileSize).

Web service:

Bytes Total/sec: displays the total number of bytes sent and accepted by the Web server. A low value indicates that IIS is transmitting data at a low speed.

Connection Denied: The lower the value, the better. A high number indicates a bottleneck in the network adapter or processor.

Error not found: displays the number of requests that the server cannot satisfy because the requested file cannot be found (HTTP status code 404).