SQL Server Performance Troubleshooting -3 Monitoring Performance Counters via PERFMON

Hi,

I will continue to explain how to Troubleshoot Performance problems of SQL Server in this Article Series.

 

Read previous article before this.

 

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

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

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

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

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

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

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

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

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

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.

 

Mehmet Salih Deveci

I am Founder of 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 mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.