Using SQL Server Profiler
SQL Server Profiler Tutorial Video
In this ten minute video, Brent Ozar explains how to get started tracing your slow queries with Profiler:
For more tutorial videos like this, check out the SQL Server Tutorials page.
How to Use SQL Server Profiler
You can start SQL Profiler by choosing Start --> Programs --> Microsoft SQL Server --> Profiler. Note that you must be a member of the SYSADMIN fixed server role to be able to setup traces with Profiler.SQL Profiler does not immediately start a trace. Instead you must make a selection from one of the following menus:
When setting up a trace, you must first specify the server that you wish to trace and logon credentials. Once you supply a valid login that is a member of the SYSADMIN server role (if you fail to do so you will get an error) you'll get a dialog that helps with configuring trace properties. The trace properties dialog has four tabs. The General tab lets you specify the trace name, which is helpful if you wish to save the trace for future analysis, as well as the destination where the trace should be saved. If you choose to save the trace to a file you, have an option of setting the maximum file size before the file is rolled-over - that is, a new file is created to continue saving trace data. If you allow traces to grow large, opening such trace files may take dozens of minutes or even several hours. You also have an option to save the file as a SQL Server table; if you choose this option you can specify the maximum number of rows to be saved and the trace table's destination. Note that you don't have to save the trace table on the server where the trace is taken. In fact, it is often useful to trace the production server and save the trace table on a monitoring or testing server for later investigation.
The same tab allows you to specify the template to be used as the starting point for your trace. You can also create and make changes to your own templates. You are allowed to change the server you wish to trace, as long as you can provide a valid administrative login for that server. Finally, you can a define trace stop time if you wish to stop recording a trace at some point in the future.
The Events tab lets you select the events and event categories to be recorded. The Data Columns tab allows you to select particular data columns that you wish to see in your trace output. The Filters tab is where you can set filters to limit trace output to certain database, connection or by other criteria.
SQLServerPedia Articles on Profiler