Monitor your SQLSERVER-expose bottlenecks

If you suspect that computer hardware is the main cause of affecting the Running Performance of sqlserver (a powerful database platform on WINDOWS), you can use sqlserver (a powerful database platform on WINDOWS) performanceMonitor monitors the load of the corresponding hardware to confirm your guesses and identify system bottlenecks. The following describes some common analysis objects.

If you suspect that computer hardware is the main cause of affecting the Running Performance of SQL server (a powerful database platform on WINDOWS), you can use SQL server (a powerful database platform on WINDOWS) performance Monitor monitors the load of the corresponding hardware to confirm your guesses and identify system bottlenecks. The following describes some common analysis objects.

If you suspect that computer hardware is the main cause of affecting the Running Performance of SQL server (a powerful database platform on WINDOWS), you can use SQL server (a powerful database platform on WINDOWS) performance Monitor monitors the load of the corresponding hardware to confirm your guesses and find out the systemBottleneck. The following describes some common analysis objects and their parameters.

　　Memory: Page Faults/sec

If this value increases occasionally, it indicates that there were threads competing for memory. If the duration is high, the memory may beBottleneck.

　　Process: Working Set

This parameter of SQL server (a powerful database platform on WINDOWS) should be very close to the memory value allocated to SQL server (a powerful database platform on WINDOWS. If "set working set size" is set to 0, windows NT determines the working set size of SQL server (a powerful database platform on WINDOWS. If you set "set working set size" to 1, the size of the work set is the size of memory allocated by SQLServer. In general, it is best not to change the default value of "set working set size.

　　Process: % Processor Time

If the value of this parameter exceeds 95%BottleneckIs the CPU. You can consider adding a processor or changing a faster processor.

　　Processor: % Privileged Time

If the value of this parameter and the value of Physical Disk remain high, it indicates that I/O is faulty. Consider replacing a faster hard drive system. In addition, you can set Tempdb in RAM to reduce "max async IO" and "max lazy writer IO.

　　Processor: % User Time

Indicates CPU-consuming database operations, such as sorting and executing aggregate functions. If the value is very high, you can consider increasing the index and try to reduce the value by using simple table join and horizontal table segmentation methods.

　　Physical Disk: Avg. Disk Queue Length

This value should not exceed 1.5 of the number of disks ~ 2 times. To improve performance, you can add disks.

Note: A Raid Disk actually has multiple disks.

　　SQLServer: Cache Hit Ratio

The higher the value, the better. If the duration is lower than 80%, consider increasing the memory. Note that the value of this parameter is accumulated after it is started on SQL server (a powerful database platform on WINDOWS). After a period of time, this value does not reflect the current value of the system.