Sql trace and profiler

SQL Trace is a technology built into SQL Server engine, that allows to provide a customer the details of number of events occurring in different parts of SQL Server.

SQL Trace can be compared to a debugger. Before a complex program organized in multiple classes and modules , a developer who must identify the cause of a problem from a bug report has only an effective method Facilities: Reproduce the problem by following step by step the behavior of the code using the debugging tool , observing the values assigned to variables, state changes of classes , iteration loops . Without this ability to enter in code operations, finely observe what happens , the work of debugging would be reduced to a tedious research done of testing, of PRINT instructions, of careful reading of code. SQL Server adds additional levels of complexity , that make experimental method nightmarish: We can rarely guess just by reading a simple request what is a query execution plan , that is to say the strategy that SQL Server will apply to ensure optimal performance.To find out, you should do it yourself all the work of optimizer: Inspect the tables, list the indexes and statistics ,estimate the number of impacted lines , try several strategies , estimate cost ... It is almost impossible to guess what will happen exactly . In addition, the competitive performance of queries makes the performances dependent of context :workload processors waiting on locks , temporary tables performance ...

Without SQL Trace, SQL Server would be a black box, we would have no way to identify accurately the reasons for a sudden downturn, or excessive system charge.In every instance of SQL Server, a subsystem named trace controller is responsible for the collection of events produced by providers that are specific part of the engine , and to the sending of these events to registered customers. These providers are enabled only if at least one client is actively listening one of the events they produce. The trace controller distributes the results to I / O providers trace) suppliers /, that allows to a trace to be directly saved in a binary file by the server, or sent to a client, as the profiler.

The profiler, incorrectly translated in some versions of SQL Server by "Profile Manager" is actually a client program that displays the information of a SQL trace. It is the essential tool for optimization. You used to monitor the behavior of your server, by intercepting all the events sent by SQL Trace, which of course SQL queries, along with valuable information on their impact and implementation. It is important to know it.