0

I am learning about capturing trace via profiler.

I understand that the purpose of capturing trace via profiler is to log events happening on the sql server without impacting performance, because logging via the profiler GUI impacts performance.

Once I have the trace I can open it in profiler and view the events without having impact on sql server. What else can I do with the trace?

1 Answer 1

1

Don't mess around with the old trace engine. It has been deprecated for a decade now. Use Extended Events (XE) instead.

The old trace engine consists of the support in the database engine called "SQL trace"; and the GUI called "Profiler". The reason not to use Profiler for the old trace engine is because of the overhead. So, back in the days when we used the old trace engine, we used "server-side traces", where SQL Server would write to a trace file. Regardless of whether a trace file was produced by the database engine or Profiler, we can open that trace file in both Profiler, and read it from the database engine doing select from fn_trace_gettable().

There are myriad use-cases for tracing. Capturing SQL statement along with costs is only one of them.

Considering that we have Extended Events now, and the old trace engine is deprecated and haven't been worked on for a decade now should make the decision between the two pretty easy. Also, XE is so much more powerful and rich compared to the old trace engine.

XE might be somewhat more complex to get started with, which is why I wrote this blog post.

Edit: adding some examples. As for use-cases of tracing, it is essentially limitless. Tracing is IMO a troubleshooting tool allowing you to capture information when things happens in the engine. To mention just a few examples:

  • Capture when a SQL statement was completed along with cost for performance reasons.
  • When a deadlock occurs along with details info what deal to the deadlock.
  • See if weird things happens in the engine, stuff you then want to narrow in on (like in my "Looking for strange" trace).
  • Capture execution plans for performance analysis.
  • See how frequently checkpoint happens.
  • Wait stats
  • Capturing when big memory grants happens.
  • Long waits for client apps to consume data from SQL Server
  • Tracking recompilations
  • ...
4
  • Just to get an idea suppose I configure an extended event for sql_statement_completed then does this impact performance of sql server?
    – variable
    May 5 at 7:26
  • Good point about XE. You don't mention any uses for trace, though, which is the question. May 5 at 8:51
  • Yes, adding a trace will add load to the server, how much depends on lots of stuff. As for use cases of tracing, it is like asking "what can I use a computer for?". Mentioning 1, 2, 5 20 or 100 use cases might restrict the willingness to explore the feature, meaning you limit yourself. I'll add a few examples, anyhow... May 5 at 9:35
  • Where can I replay the trace and does replaying the trace give an illusion of load or does it actually fire the crud operations recorded inside the trace?
    – variable
    May 6 at 7:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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