When you suspect that computer hardware is the main factor affecting the performance of SQL SERVER, you can use the SQL SERVER Performance Monitor to monitor the load on the corresponding hardware to confirm your suspicions and identify system bottlenecks. Below, we will introduce some commonly used analysis objects and their parameters.
Memory: Page Faults / sec
If this value occasionally spikes, it indicates that there was thread competition for memory at that time. If it remains consistently high, then memory may be a bottleneck.
Process: Working Set
This parameter for SQL SERVER should be very close to the amount of memory allocated to SQL SERVER. In SQL SERVER settings, if "set working set size" is set to 0, Windows NT will determine the size of the SQL SERVER working set. If "set working set size" is set to 1, it forces the working set size to be equal to the amount of memory allocated to SQLServer. In general, it is best not to change the default value of "set working set size."
Process: %Processor Time