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.

Advertisements
This entry was posted in SQL, SQL Server, SQL Server 2012, SQL Server 2014, SQLTuesday. Bookmark the permalink.

One Response to T-SQL Tuesday #66: Monitoring

  1. Pingback: Roundup of T-SQL Tuesday #66: Monitoring - Cathrine Wilhelmsen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s