Performance Monitor (Perfmon) Counters Useful for SQL Server Troubleshooting

How to gather performance data when SQL server performance is causing problems for Symantec Endpoint Protection manager (SEPM).

Solution

The following steps will produce a Perfmon log that can be used to demonstrate performance trends on a SQL server over time.

1. To open Perfmon click Start->Run, type "perfmon" into the Run dialog box, then click OK.
2. Expand Performance Logs and Alerts on the left-hand panel in the Perfomance window.
3. Select Counter Logs from the list under Performance Logs and Alerts.
4. Click Action->New Log Settings...
5. Provide an appropriate name for the new log settings (i.e. ____).
6. On the General tab:
   1. Modify the Polling Interval to 1 second.
   2. Click Add Counters on the log settings creation dialog.
      1. To add Counters:
         1. choose the Performance object from the Performance Object drop-down list
         2. Select the appropriate Counter(s) from the counters list. (you can use Shift-click to select more than one Counter at a time)
         3. Once you have selected all of the Counters for a particular Performance Object, verify you have selected the proper instance, then click Add.
      2. Add the following Performance Objects/Counters:
         1. Processor/%Processor Time
         2. Processor/%User Time
            
            The following counters are prefixed by "SQLServer" in a default instance, and MSSQL$[Instance_Name] for a named instance
         3. SQLServer:Databases/Bulk Copy Throughput/sec for _TOTAL
         4. SQLServer:Databases/Bulk Copy Throughput/sec for the SEPM database
         5. SQLServer:Locks/Average Wait Time (ms) for _TOTAL
         6. SQLServer:Locks/Lock Wait Time (ms) for _TOTAL
         7. SQLServer:Locks/Number of Deadlocks/sec for _TOTAL
         8. SQLServer:SQL Statistics/Batch Requests/sec
         9. SQLServer:Transactions/Longest Transaction Running Time
7. On the Schedule tab:
   1. Schedule Perfmon to start and stop logging at specified times or choose to manually stop and start the trace.
   2. Whichever method is chosen, ensure that the performance data is gathered during a time period when performance issues are present.
   3. If the issue is intermittent, it is best to gather data from both "good" and "bad" times in a contiguous log.