Many people get confused, thinking that SQL Server Profiler is the actual tracing feature of SQL Server, while in fact it is only an application, a great .NET application but an application. The actual tracing feature of SQL Server is called SQL Trace and it is a powerful feature exposed by the database engine.
SQL Trace provides a real-time view into what’s going on inside the database engine and we can take a real advantage of that. Mastering this feature opens up a whole lot of new possibilities when it comes to troubleshooting, performance tuning and more.
SQL Trace uses trace I/O providers to send the captured data to consumers. The 2 available providers are the file provider and the rowset provider. SQL Server Profiler makes use of the rowset provider and while we run a server side trace (from a script) we make use of the file provider.
I will not get into all the differences between the two providers but I do want to point out one key difference between the two and that is the important fact that the file provider guarantees to capture every event (SQL Server 2005 and latter) defined in the trace you are running while the rowset provider under a certain load starts dropping events. This can become very important when capturing data for later analysis or when trying to reproduce a problem.
Want to read more about SQL Server traces and see advanced usage from real world scenarios? Check out my latest articles at the bellow links….
Create a Performance Baseline Repository This process creates a Baseline repository that holds summarized data of all SQL Executions executed on an instance of SQL Server.
TraceErrors Process This process generates a report of all exceptions that took place on an instance of SQL Server along with other information such as the login, host, application and the statements that raised the exceptions.
To download a presentation on SQL Trace
Click to Download