Practical Solution to SQL Server Performance Monitoring

In order to prepare and execute the monitoring processes you should perform the following steps on the server you will monitor:

1. Create a C:PerfLogs folder and copy SQLperfmon.htm to this folder.
2. Start the Windows Performance Monitor console by clicking the Start button on your desktop, selecting the Run option, and typing in a “perfmon” command.
3. On the Tree tab, expand Performance Logs and Alerts. Right-click on Counter Logs and choose New Log Settings From… (Figure 1).

   
   Figure 1

4. Select the SQLperfmon.htm file from the C:PerfLogs folder (Figure 2).

   
   Figure 2

5. The Counter Log properties window appears (Figure 3).

   
   Figure 3

   On the General tab, you can see the performance log file name, a list of all the predefined counters, and the monitoring interval. You can change the log file name and the monitoring interval without any impact on the described monitoring process. The counters list assumes that the monitored server has two physical disks: “0 C:” and “1 D:”. If the disk configuration is different from the counter list, you can modify the counter list along with the corresponding PerfmonDataCustomer01 table and import scripts.

   On the Schedule tab, you can define when to start and stop monitoring. For example, you may want to monitor the server only during business hours. In order to change the location of the file or the file name pattern, use the Log Files tab. When editing, make sure that the file type remains “Text File – CSV.”

6. Click OK, and the new SQLperfmon counter log will be added to the log list. You can start the monitoring process immediately, or wait for it to be started by the scheduler, if set (Figure 4).

   
   Figure 4

   Continues…