SQL Profiler is a powerful tool that allows you to capture and analyse events, such as the execution of a stored procedure, occurring within SQL Server. This information can be used to identify and troubleshoot many SQL Server-related problems.
The inner working of Profiler
The SQL Server Profiler is only a GUI designed to work with another feature of SQL Server call SQL Trace. It is SQL Trace that actually doing most of the work when it comes to capturing SQL Sever events and storing them for later use. SQL Trace is a feature of SQL Server that can be accessed indirectly with the Profiler GUI, system stored procedures, or programmatically using Server Management Objects (SMO).
In essence, SQL Trace is a very simple tool. Its job is just to capture SQL Server-related communication between a client and SQL Server. It acts similarly to a specialized network sniffer that captures traffic on the network related to SQL Server and allows you to see exactly which events are being sent from the client to SQL Server.
Unlike a network sniffer, which allows you to see every byte transversing the network, SQL Trace only captures and process SQL Server-specific events.
Figure : The flow of Profiler data from SQL Server events to final output
Profiler terminology
1. Events
SQL Server Profiler allows you to capture over 170 different SQL Server-related events. The execution of a stored procedure is one example of event. An Event Category is a group of related events. The term Event Class refers to an event, and all of the data columns associated with it.
2. Data columns
Every event that can be captured includes a group of related data that describes that event and is stored in what are called data columns.
3. Filters
Filter allow user to tell profiler not to collect the events (rows) that you don’t want to save or view.
4. Trace
A trace includes the events and data columns you collect and is usually stored in a physical file for later examination.
