The SQL Server Profiler is one of the most powerful tools in the SQL Server package. It allows you to audit and analyze virtually all of the events which that occur during a defined SQL Server time horizon. It records all activity activities in SQL Server, and can send to it as an output to a file, table, or to the display window. In our opinion, the best output device is a sql SQL database table, where one can manipulate and query all pertinent activities in a highly organized fashion.

The profiler has a client side and a server side component. The server side trace facility component is accessed and defined through system stored system-stored procedure. The client side is the Profiler proper which is in the form of a GUI interface which allows auditing of SQL events on a real time basis.

Event producers notify the Profiler for things such as queries and locks. The profiler then passes these event notifications through a set of user-set filters. The events are queued and written to the specified output device. Because of the large amount of information available during a SQL Server session, the user has to define the information recorded in the profiler. One can do this by event type, application, user, etc.

There is a built-in set of templates which that will help the user define the universe of events. These templates are similar to the ones suitable for creating user defined functions and stored procedures. The packaged templates in SQL 2000 are as follows:

SQLProfiler SP_Counts

Tracks start of sprocs

SQLProfilerStandard

Tracks completion of SQL statements and RPCs

SQLProfilerTSQL

Tracks start of TSQL statements and RPCs

SQLProfilerTSQL_Duration

Tracks execution time pf TSQL statements and RPCs

SQLProfilerTSQL_Grouped

Tracks start of SQLs and RPCs grouped by App, User

SQLProfilerTSQL_Replay

Captures profiling data

August 16, 2005 width="379" valign="top">

Traces details of sprocs

SQLProfilerTuning

Shows completion of SQL and RPCs for performance

Start SQL Profiler from the tools menu in Enterprise Manager. Choose the events to capture, and the data columns needed to differentiate the things you want to trace, such as application, user, login. Duration, duration, object, etc. Then set the filters to eliminate noise you don't want. Server side traces are accomplished using the system stored procedures sp_trace_create, sp_trace_setevent, sp_trace_setfilter, and sp_trace_setstatus.

Traces can be exported to the Index Tuning Wizard, or saved to disk to be read back into the profiler at a later time.