Monitoring Performance Counters via PERFMON | SQL Server Performance Troubleshooting -3

I will continue to explain how to Monitoring Performance Counters via PERFMON | SQL Server Performance Troubleshooting in this Article Series.


SQL Server Performance Troubleshooting


Read previous article before this.

SQL Server Performance Troubleshooting -2 Monitor Databases and Instances via Activity Monitor



Monitoring Performance Counters via PERFMON

Bottleneck that occurs in the Database and Server can be identified by examining the use of Server resources. The most important bottlenecks affecting SQL Server are as follows.

  • Memory Bottleneck
  • Disk I/O Bottleneck
  • CPU Bottleneck



Memory Bottleneck in SQL Server

SQL Server Instance allocates memory space from Operating system ( OS ) according to the Max and Min memory parameters set during startup. By default, SQL Server Instance Memory usage grows continuously to avoid paging. If there is no more memory space for a process on the OS, Operating system warns SQL Server and SQL Server releases extra memory that it allocated.


Maximum and minimum memory parameters can be set from the following screen. ( SQL Server Management Studio–> Right Click SQL Instance and Choose Server Properties )


The general cause of bottlenecks in SQL Server Instances is that Memory is insufficient. In case of insufficient memory, it is called hard page fault if the database reads data from the Physical disk instead of memory.


The following Performance Counters should be reviewed to see if there is Memory Bottleneck in the Database and OS.



Memory: Pages/sec in SQL Server

Indicates the number of memory pages that are not exist in the memory ve and must be read from the disk and the number of memory pages that must be written to the disk.

This value should be followed for a certain period while the database is running normally and an average value should be determined. If this value is constantly high in case of problems, it is useful to increase the size of the OS’s physical memory.



Memory: Committed Bytes in SQL Server

Indicates the total memory space used by Instance from pagefile.sys on RAM and Disk. This counter will not exceed the total RAM size if the amount of Memory supplied is sufficient. If There is no more sufficient memory, pagefile.sys space is used, and this means related performance counter value will exceed the RAM value. In such a scenario, adding RAM to the server can ease the system.




Memory: Available Bytes in SQL Server

Indicates the total amount of available memory for Instance. This value usually appears low. If the value of this counter is consistently below 4 MB, too many paging operations occur.



Disk I/O Bottleneck in SQL Server

For Disk I/O Bottleneck, which occurs on the disk or Storage, the Performance Counter values under the Physical and Logical Disk in the Perfmon tool should be checked.


  • If a Counter’s value, such as Disk seconds / read counter, is greater than 15-20 ms, it is usually a disk performance issue.
  • Continuous high values for average Disk seconds / write counter indicate that there is a performance problem on the disks.


% Disk Time in SQL Server

Displays Read / Write density of the disks. If this value is 80%, it is understood that disks are used extensively. A value of 50% or less indicates no Disk Bottleneck.


Avg. Disk Queue Length in SQL Server

It shows the ones waiting in the I/O made to the disk. If this value is consistently high, it means that the Disk or Storage Bottleneck started.




Avg. Disk Bytes/Transfer in SQL Server

Shows the average amount of bytes processed during Read and Write to Disk. A higher value means that Disk is operating efficiently.




CPU Bottleneck

It is easier to see if the system slows down from the CPU, as compared to Memory and Disk I/O. Because, after making sure of Memory and Disk, If there is still more trouble and if the CPU side needs to be examined, the System CPU can be checked immediately. If the system CPU is 80%, it means that there is a problem, but in 50% it can be determined that the problem is not caused by the CPU.


Processor: % Processor Time in SQL Server

Displays the active runtime of the CPU.



If the OS can handle requests without increasing the Server Queue Length value, then the processes are serviced as quickly as possible.


Processor: % Privileged Time

Shows the CPU time allocated to the OS on which SQL Server is running.


Processor: % User Time

Shows the CPU time spent on other activities running on the operating system.


I will continue to explain SQL Server Performance Troubleshooting in the next article.

SQL Server Performance Troubleshooting -4 Using DMV ( Dynamic Management View ) and DMF ( Dynamic Management Function )




Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then Click and read the following articles.

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *