Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
Thanks for the advices. I will invest time reading the links you provide. –  hbrls Dec 7 '11 at 9:15

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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