2

I referred to this article: sp_trace_setevent (Transact-SQL).

And trace my SQL Server on production machine for months.

EventId of most of the traces were: 10,12,14,17

The MSDN says:

  • 10 RPC:Completed
  • 12 SQL:BatchCompleted
  • 14 Audit Login
  • 17 ExistingConnection

Please tell me what do they mean in the performance perspective. Which ones should I keep an eye on and examine in detail. Or am I missing something. Thanks!

2 Answers 2

4

You appear to have setup a trace with no understanding of why you've done so. There is little point in running a trace for 10 minutes, let alone months, without a reason.

Scenarios for using SQL Server Profiler

The first step in using SQL Server Profiler is to identify your reasons for monitoring an instance of SQL Server.

The most common scenarios (taken from the above link) which may be relevant to you are:

  • Find the worst-performing queries.
  • Monitor stored procedure performance.

A trace that captures RPC:Completed and SQL:BatchCompleted events (Stored Procedure and TSQL classes) with Duration, Reads and CPU data columns will provide you with a diagnostic log of how your queries are performing. You can import this trace file to a table and run queries against it to identify the slowest, highest IO or highest CPU consumers for example.

If you have direct access to the server and want to analyse performance, I'm inclined to suggest that you ignore profiler traces and start with the SQL 2005 Performance Dashboard Reports. When you've grasped the basic concepts of Troubleshooting Performance Problems, invest some time understanding Dynamic Management Views (DMVs) and how they can help with performance analysis.

1
  • Thanks for the advices. I will invest time reading the links you provide.
    – hbrls
    Dec 7, 2011 at 9:15
2

The link you provided has very explicit descriptions, if you are trying to optimize some code in your database you probably need to look for some SQL:BatchStarting/SQL:BatchCompleted that is taking too long, and see if there is any way to optimize it

Note: i'm new here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.