• Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
  • Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
home/Knowledge Base/MS SQL Server/Important Counters (perfmon.exe)
Popular Search:Oracle, SQL Server, MongoDB

Important Counters (perfmon.exe)

143 views 0 September 7, 2019 admin

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.

Was this helpful?

Yes  No
Related Articles
  • MS SQL Server Notes
  • Restoring Backup via Scripts
  • Restore Backup in SQL Server
  • SQL Server JOBS
  • Backup using T-SQL
  • Steps to Take Backup from GUI in SQL Server
Leave A Comment Cancel reply

MS SQL Server
  • Important Counters (perfmon.exe)
  • MS SQL Server Notes
  • Restoring Backup via Scripts
  • Restore Backup in SQL Server
  • SQL Server JOBS
  • Backup using T-SQL
View All 20  
Popular Articles
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrading MariaDB on Windows
  • Oracle DBA Basic Interview Questions Part A
  • Purging Oracle Sysaux Tablespace
  • Client – Server Architecture
KB Categories
  • Oracle Database
    • Oracle RAC
    • Oracle ASM
    • Oracle GoldenGate
    • Oracle Tuning
    • Oracle 11g Database
    • Oracle Database Upgrade
    • Oracle 12c Database
    • ALL KB Oracle
    • Oracle 18c Database
    • Oracle Standby Database
  • MongoDB
  • MS SQL Server
  • MySQL
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • MariaDB
Database Organization

Database Organization (DB ORG) is knowledge base for DBA to learn and execute the fundamental of different databases under one website. DB ORG is a non-profit initiative. ORACLE, MS SQL Server, MongoDB, MariaDB, Couchbase

Join Our Community
  • KnowledgeBase
  • Documentation
  • Troubleshoot
  • FAQ
Information Links
  • About DBOrg
  • Licenses
  • Terms
  • Privacy Policy
Contact Us
    DB ORG - Database Administration,
    Knowledge Base for DBA
    Mail: support@databaseorg.com
    WhatsApp: (+91) 9306440957
    Monday to Friday: EST - 11:30 AM to 06:30 PM (IST - 9:00 PM to 4:00 AM)
  • © 2023 Database Organization - DB ORG. All Rights Reserved.

Popular Search:Oracle, SQL Server, MongoDB

WhatsApp DB Org