IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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.
https://ittutorial.org/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.
https://ittutorial.org/sql-server-performance-troubleshooting-4-using-dmv-dynamic-management-view-and-dmf-dynamic-management-function/
