Share This Post

T-SQL Tuesday #66: Monitoring

T-SQL Tuesday

Topic today is monitoring. Catherine Wilhelmsen (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.

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

http://1drv.ms/1EATsIU

Save it with the following name: SQLPerformanceV3.xml

Start perfmon.exe

From Data Collector Sets > User Defined, Right click Create a new data collector set, and give it a name”

image

Click Next, then Browse (for a custom template), select the SQLPerformanceV3.xml file saved in the previous step. Click OK

image

Then, click Next

Change the root directory to a drive with available free space

image

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

image

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:

image

Click Performance Monitor, then click second icon from the left

image

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.

Filed under: SQL, SQL Server, SQL Server 2012, SQL Server 2014, SQLTuesday

Share This Post

2 Comments

  1. TSQL Monitoring

  2. I am leaving another comment

Leave a Reply