IMPORTANT COUNTERS (perfmon.exe)
SQL Server Access Methods object: Page Splits/sec
To monitor number of page splits w.r.t SQL Server. If the number of page splits is high, we need to increase the fill factor of our indexes there by providing more room in data pages and subsequently reducing the chances for Page Splits.
SQL Server Buffer Manager Object: Cache Size (pages)
To monitor the amount of physical RAM used by SQL Server’s data cache. This number is presented in pages. We should see that no more than 60 %
SQLServer: SQL Statistics: Batch Requests/Sec counter
This counter measures the number of batch requests that SQL Server receives per second. Value greater than 1000 batch requests per second indicate a busy Server, and could lead to CPU bottleneck.
SQLServer: SQL Statistics: SQL Compilations/Sec counters
To monitor compilations on SQL Server requests per second. This counter should not exceed 150.
SQLServer: Databases: Log Flushes/sec
To monitor the number of log flushes per second. This can be measured on a per database level, or for all databases on a SQL Server. If the counter is more, we need to increase RAM allocated for SQL Server.
SQL Server General Statistics Object: User Connections
To monitor the number of user connections, not the number of users, those are currently connected to SQL Server.
Server Locks Object: Number of Deadlocks/sec
To minotor the number of deadlocks per second. Since the monitoring is done on per second basis, we may always see some number for this counter. For average, we need to average this value to seconds.
SQL Server Locks Object: Average Wait Time (ms)
To monitor the average wait time for locks. This counter is very useful if the users experience waits for their transactions to complete.
SQL Server Access Methods Object: Full Scans/sec
To monitor the number of table scans SQL Server instance is performing. This counter is for an entire server, not pertaining to a specific database.
SQL Server Buffer Manager Object: Buffer Cache Hit Ratio
To monitor how often SQL Server access buffer and not the hard disk, to retreive data. The higher this ratio, the less often SQL Server has to go to the hard disk to fetch data, and performance overall is boosted.
SQLServer:Memory Manager: Total Server Memory (KB)
To monitor how much total RAM the instance is using.
SQLServer:Memory Manager: Target Server Memory (KB)
Specifies how much memory SQL Server would like to have in order to operate efficiently. This is actually based on the number of buffers reserved by SQL Server when it is first started up.
IO Monitoring:
PhysicalDisk Object: Avg. Disk Queue Length – The average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If the I/O system is overloaded, more read/write operations will be waiting. If the disk queue length exceeds a specified value too frequently during peak usage of SQL Server, there might be an I/O bottleneck.
Avg. Disk Sec/Read: The average time, in seconds, of a read of data from the disk. Use the following to analyze numbers in the output.
- Less than 10 milliseconds (ms) = very good
- Between 10-20 ms = okay
- Between 20-50 ms = slow, needs attention
- Greater than 50 ms = serious IO bottleneck
Avg. Disk Sec/Write: The average time, in seconds, of a write of data to the disk. See the guidelines for the previous item, Avg. Disk Sec/Read.
Physical Disk: %Disk Time: The percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value > 50%, there is an I/O bottleneck.
Avg. Disk Reads/Sec: The rate of read operations on the disk. Make sure that this number is less than 85% of disk capacity. Disk access time increases exponentially beyond 85% capacity.
Avg. Disk Writes/Sec: The rate of write operations on the disk. Make sure that this number is less than 85% of the disk capacity. Disk access time increases exponentially beyond 85% capacity.
Database: Log Bytes Flushed/sec: The total number of log bytes flushed. A large value indicates heavy log activity in tempdb.
Database:Log Flush Waits/sec: The number of commits that are waiting on log flush. Although transactions do not wait for the log to be flushed in tempdb, a high number in this performance counter indicates and I/O bottleneck in the disk(s) associated with the log.
ADVANCED COUNTERS:
There are some SQL Server counters that you can watch to help determine if your SQL Server is experiencing any problems.
* SQL Server Buffer Mgr: Page Life Expectancy: To specify how long data pages are staying in the buffer. If this counter gets below 300 seconds, this is an indication that SQL Server could use more memory in order to boost performance.
* SQL Server Buffer Mgr: Lazy Writes/Sec: This counter tells us how many times a second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space. Ideally, it should be close to zero which indicates that your SQL Server’s buffer cache is plenty big and SQL Server doesn’t have to free up dirty pages, instead waiting for this to occur during regular checkpoints. If this value is high, then a need for more memory is indicated.
* SQL Server Buffer Mgr: Checkpoint Pages/Sec: Higher Value of this counter indicates that the checkpoint process is running more often than it should, which more server resources. In those cases, we need to increase RAM to reduce how often the checkpoint occurs or decrease the “recovery interval” SQL Server.
* SQL Server Buffer Mgr: Page Life Expectancy: To monitor how long data pages are staying in the buffer. If the value is less than 300 seconds means, an indication that the SQL Server could use more memory to boost overall performance.