HOWTO: Monitor SQL Server Performance

Summary

For large or very busy installations of RMTrack it can be very helpful to do regular performance monitoring. The statistics gathered can help users' capacity planning and ensure the application continues to run smoothly.

Background

Many factors can affect SQL Server's performance. Generally speaking CPU, Memory and Disk I/O all play their parts, and often improving one area will highlight that another area also needs addressing.

In general the best approach is to regularly monitor the performance of both the webserver and the SQL server. This specific KB article only deals with SQL Server.

Resources

The following downloads are PerfMon templates that can be used to collect performance counters for the specified SQL Server edition. Follow the directions in STEPS for how to configure PerfMon data collection using one of these templates.

Note that these templates only take measurements from SQL Servers default instance. If you need to measure a specific instance please contact your support representative for assistance.

• For SQL 2016: Sql2016PerfMonSettings.xml
• For SQL 2014: Sql2014PerfMonSettings.xml
• For SQL 2012 Sql2012PerfMonSettings.xml
• For SQL 2008 on Windows 2008 R2 Sql2008R2PerfMonSettings.xml
• For SQL 2008 (non-R2) Sql2008PerfMonSettings.xml
• For SQL 2005 Sql2005PerfMonSettings.xml

Steps to Start Monitoring

1. Start PerfMon.exe, expand "Data Collector Sets":
   
2. Right click on "User Defined" and select "New->Data Collector Set":
   
3. Name the new data collector set "SQL Peformance" and select "Create from template" and click "Next"
   
4. Select "Browse" and select the file sent to you by RMTrack support:
   
5. Select "PAL_Microsoft_SQL_Server_2014" template and click next:
   
6. Pick a location to store the performance data:
   
7. Select the user id to run as when collecting the data (<Default> is fine), click "finish" to save the new data collector set:
   
8. Right click on the new data collector set and select "Start":
   
9. Let the data collection run for at least 1 day:
   

Steps to Complete Monitoring

After the performance monitoring has run for at least 1 day, you can stop the data collection and send the raw data to your support representative for analysis.

To stop the data collection:

1. Start PerfMon.exe, expand "Data Collector Sets", expand "User Defined":
   
2. Right click on your data collection set and select :
   
3. Zip the contents of the data folder where the measurements are stored (step 6 above) and send the .zip file to your support representative. Note that if the file is large you will need to arrange a method other than email to transfer the file to your support representative.

Applies to

RMTrack Issue Tracking - All versions

Keywords: SQL Server, Performance