SQL Server Monitoring Pt. 2 – Run a SQL Profiler trace during high usage hours

As my research continues I should point out that these tools are pretty sharp. SQL Profiler while not the sharpest ax in the tool box, it’s still a very powerful tool. There are numerous traces you can run to iron out the most simple problems. There are some drawbacks. The more parameters you put into your trace, the more load you put on your server. This begs the question “If I’m trying to identify a performance issue, why would I put more load on my server” I guess it’s like the old adage “You need to spend money to make money”. However, in the case we are going to look at today SQL Profiler is not that expensive.

In my situation I support many vendor developed databases, as I have talked about in previous blogs. I have been working with vendor applications my entire carrier. Vendors love to point the finger at our server, our desktops, or anything to put us in our place. That’s fine point your finger, I will point back with a different finger. My finger will get the point across. This SQL Profiler will identify the front end queries that are tacking the server. We will find out what query’s take over 60 seconds to run. In the end we can save the results and send them back to the vendor. The vendor can then see how their query is effecting the server performance.

So lets get started! Be sure to kick this off in the morning or create a scheduled job.

  1. Launch SSMS
  2. Tools > SQL Server Profiler
  3. Connect to your server
  4. In the Trace Properties Window > General Tab
    1. Trace Name: “SQL Lumberjack is Awesome” (or whatever you want)
    2. Use the template
      1. TSQL_Duration
    3. Check Save to File
    4. This will open a Save As window
    5. Save your .trc file
    6. Check Enable Trace stop time
      1. Note: Run this trace during high usage hours. I love to run this trace during the first or last business day of the month.
    7. Go to the Events Select Tab
      1. Make the following selections (You may have to check Show all columns)
      2. Click Column Filters
      3. Click Duration
      4. Expand ‘Greater than or equal’
      5. Type 60000 (60 milliseconds)
      6. Click Ok
    8. Buckle your seat belt
    9. Click Run
  5. Now envision the vendor shaking in their boots 🙂

I like to keep the trace window open to monitor the results. Once it’s done review your saved file. This trace can go either way. You may only get one or two hits, which is fine and I have experienced that result. You can always adjust the duration time but you will want to analyze why your adjusting it. If you have several hits send that trace to your vendor. 

I have adjusted my research plan. Instead of looking at SQL Monitor (RedGate Tool) I will be looking at the Database Engine Tuning Adviser to tune indexes. SQL Monitor is a very sharpe ax. I’m the SQL Lumberjack… I will continue to look at those options that are not as sharpe.

Lets be honest in most cases these will save your department money. You can take that to your review.


About Erik Disparti

I started my IT career in 1999 and in January 2011 I became a SQL DBA. So far I'm loving every minute of it and learning more and more everyday.
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to SQL Server Monitoring Pt. 2 – Run a SQL Profiler trace during high usage hours

  1. Hey nice post. I hope it’s alright that I shared
    this on my Twitter, if not, no worries just let me know and I’ll remove it.
    Regardless keep up the good work.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s