One Community, One World
Windows Commnication Foundation Addressing
by [lnkAuthor]  on 
  PageViews   |    Add to Fav   |     Send to Friend   |    Download Code
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.
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.

By Mohamed Atia on 7/21/2011