Introduction SQL Server Performance is affected by many factors like the Processor, Memory, Hard Disks, Network Interface Cards…etc. To be able to detect the source of problems with a SQL Server machine, we need to monitor some counters and indicators, which indicate the source of the problem that the server is facing. Once the Problem Source has been identified, counter measures can be applied to resolve those problems (ex: increasing ram capacity of the server, optimizing the application that uses the database).
Below is a guided Exercise on how to monitor the SQL Server machine Performance, point out sources of problems, and the recommended values for the examined counters.
Steps 01. Open Administrative Tools and then click "Performance".
02. In the Performance console tree, select the Counter Logs node.
03. Right-click Counter Logs and then choose "New Log Settings" from the shortcut menu.
04. In the Name text box, type SQL Server Performance Counters and then click OK.
05. On the General tab, click Add Counters. The Add Counters dialog box opens.
06. Select Use Local Computer Counters.
07. By using the Add Counters dialog box, Add the counters specified in the table below:
| Counter |
Value |
| Processor:% Processor Time |
Should average below 75% |
| System: Processor Queue Length |
Should average below 2 per processor |
| Memory-Pages/sec |
Should average below 20 |
| Memory-Available Bytes |
Should remain above 50 MB |
| Physical Disk-% Disk Time |
Should average below 50% |
| Physical Disk-Avg. Disk Queue Length |
Should average below 2 per disk, even individual disks in a raid array |
| Network Interface-Bytes Total/sec |
Used to size the network bandwidth per NIC |
| SQL Server: Buffer Manager- Buffer Cache Hit Ratio |
Should exceed 90% |
| SQL Server: Buffer Manager-Page Life Expectancy |
Should remain above 300 seconds. |
08. In the Add Counters dialog box, click Close.
09. In the SQL Server Performance Counters dialog box, click the Schedule tab.
10. In the Start Log area, select Manually.
11. Click OK. The log file will be stored by default in the C:\PerfLogs folder.
12. In the Performance console, a new log named SQL Server Performance Counters appears in the details pane when you select Counters Logs.
13. Right-click the SQL Server Performance Counters log and then choose Start.
14. After a few minutes, right-click the SQL Server Performance Counters log and select "Save Settings As".
15. Click Save.
16. In the Performance console, right-click the SQL Server Performance Counters log and then choose "Stop".
17. Open the SQL Server Performance Counters.HTM file saved in step 15.
18. View the data stored in the HTM file.
19. Note the averages, highs, and lows given for all of the counters selected.
20. Compare the Results to the Values Mentioned in the Counters table above.