What is the use of trace captured via Profiler?

Question

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?

Answer 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
• ...