I’ve been looking a lot of SQL Server installations over the past year. If there are reports of performance issues, the challenge is finding them.
One way is instrumenting the Server and SQL Server to help identify potential bottlenecks. I really like a new utility called Performance Monitor. OK, I jest. Its been around since NT 4. The nice thing, it is readily available on every server.
Here my approach, capture the key performance counters to a log file, every 5 minutes for a 24 hour period. Once the sampling period is complete, review.
To start, download the following XML file locally
Save it with the following name: SQLPerformanceV3.xml
From Data Collector Sets > User Defined, Right click Create a new data collector set, and give it a name”
Click Next, then Browse (for a custom template), select the SQLPerformanceV3.xml file saved in the previous step. Click OK
Then, click Next
Change the root directory to a drive with available free space
Click Next, then Finish
Back to Performance Monitor, there should be a User defined data collector set named SQLPerformance. Right click that set, and select properties. Select the Stop Condition tab
Change the overall duration to 1 day. Click OK.
Now, starting it (Right Click > Start ) will create a folder for the performance log file with the Computer name, and a date time entry.
From Performance Monitor, once you are done collecting data and have stopped the trace. Import the log file:
Click Performance Monitor, then click second icon from the left
Choose, log file, then Add… select the log file that was captured. Then click OK.
Now you can analyze your trace, adding and removing counters as needed. The full extent would be enough for another blog post! I’ve found this to be a very effective method to help identify performance issues.
Lastly, as you make changes to help improve performance, run another 24 hour perfmon capture and compare the results.