SQL Server 2008 R2 Profiler

 

 

This article explores the SQL Server Profiler, one of SQL Server's most powerful auditing and analysis tools. The SQL Server Profiler gives you a basic understanding of DB access and helps you answer questions such as these:

SQL Server Profiler records activity that occurs on a SQL Server. The tool has a great deal of flexibility and can be customized for your needs. You can direct SQL Server Profiler to record output to a window, file, or table. You can stipulate which events to trace, the info to comprise in the trace, how you want that info grouped, and what filters you can want to apply. SQL Server 2008 has both a server and a client-side component for tracing activity on a server. The SQL trace facility is the server-side component that manages queues of events initiated by event producers on the server. Extended stored procedures can be used to define the server-side events that are to be captured. These procedures, which define a SQL trace, are discussed later in this , in the section, "Defining Server-Side Traces." The SQL Profiler is the client-side tracing facility. It comes with a fully functional GUI that allows for real-time auditing of SQL Server events. When it is used to trace server activity, events that are part of a trace definition are gathered at the server. Any filters defined as part of the trace definition are applied, and the event data is queued for its final destination. The SQL Profiler app is the final destination when client-side tracing is used.

The subsequent four steps in the process when tracing from the SQL Server Profiler:

  1. Event producers, such as the Query Processor, Lock Manager, ODS, and so on, raise events for the SQL Server Profiler.

  2. The filters define the info to submit to SQL Server Profiler. A producer will not send events if the event is not included in the filter.

  3. SQL Server Profiler queues all the events.

  4. 4. SQL Server Profiler writes the events to each defined consumer, such as a flat file, a table, the Profiler client window, and so on.

 

In addition to obtaining its trace data from the event producers listed in step 1, you can also configure SQL Profiler so that it obtains its data from a previously saved location. This includes trace data saved in a file or table.

Because SQL Server Profiler can trace numerous events, it is easy to get lost when reading the trace output. You need to roughly determine the info you require and how you want the info grouped. As an example, if you can want to see the SQL statements that each user is submitting throughout an app, you could trace incoming SQL statements and group them by user and by app. When you have an idea about what you can want to trace, you should open the SQL Server Profiler by selecting Start, then SQL Server 2008, then Performance Tools, and Lastly SQL Server Profiler. You also can open it from within SSMS from the Tools menu. When you open the Profiler, you are presented with an app window that is basically empty. To start a new trace, you choose the File menu and choose New Trace. In the connection dialog that is displayed, you can enter the connectivity info for the server you can want to trace. After the connection is established, the General tab of the Trace Properties window is displayed.

The first place you should look when creating a new trace is at the trace templates. These templates contain predefined trace settings that address some common auditing needs. They have preset events, data columns, and filters targeted at specific profiling scenarios. The available trace templates, found in the template drop-down on the General tab of the Trace Properties window.



SQL Profiler Templates