SQL Profiler: Features, functions and setup in SQL Server 2005

Trying to find the proverbial needle in the haystack of your SQL Server transactions is no small task.
SQL Server Profiler not only helps you find that needle, it gives you details on all the other needles in a single interface.
features
A single interface with all options to configure the Profiler session:
   -Trace Events, Columns, Filtering, etc.
New profiling events:
   -Service Broker, CLR, Full Text, etc.
The ability to profile SQL Server 2005 Analysis Services, which has been a black box with earlier versions of SQL Server.
Integration between SQL Server Profiler data and Performance Monitor data into a single interface to correlate the macro- and micro-level data
Note: Many SQL Server 2000 Profiler features are back by popular demand -- keeping the learning curve to a minimum -- and new features are available to quickly leverage the technology.
-- Authentication: Remains the first interface when the application is started, but now has options for connecting to Analysis Server.
--Data capture: Enables you to write to a table, record on the screen or save to a text file.
--Graphical user interface and T-SQL commands: Enable you to run Profiler interactively on your desktop or on a predefined schedule with SQL Server Agent.
--T-SQL interface: Allows you to schedule Profiler to execute on a regular basis and write the data for future analysis.
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
sample T-SQL queries to analyze the SQL Server Profiler results.(SQL Server 2005 Profiler session setup)
1-Start SQL Server 2005 Profiler via the GUI interface by navigating to Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler.
 
2-Start a new Trace session by selecting the 'File' menu and the 'New Trace' option. Once the 'Connect to Server' interface loads, select the 'Type' as either 'Database Engine' or 'Analysis Services'. For this tip we will use the 'Database Engine' option. Then select the 'Server Name' followed by the 'Authentication' type. Finally, press the 'Connect' button to start the configuration.

3-Configure SQL Server Profiler Trace Properties – General Tab:
 Trace Name: Specify a meaningful name for the session
 Use the template: A total of eight templates are available with predefined events selected
----For this tip we will use the 'Standard (default)' template
 Save to file or Save to table: To retain a copy of the data save the results to either a database table or Windows file.
----For this tip we will save the results to the dbo.TraceResults table in my user defined database.

4-Configure SQL Server Profiler Trace Properties – Events Selection Tab:
 Review the specific events and select the needed check boxes to capture the desired data.  Show All Events: Select this check box to see all events that Profiler will be able to capture.
 Show All Columns: Select this check box to see all columns that Profiler will be able to capture.

5-Additional Configuration – Column Filters
 Specify filters based on the columns that are selected for the session to limit the data.

6-Additional Configuration – Organize Columns
 Specify the column order as well grouping settings for the final data.
7-To start the session, press the 'Run' button on the Trace Properties interface.
--Review the results that are captured in the Profiler interface.
  
8-Sample T-SQL queries to analyze the SQL Server Profiler results.
-- Total transactions per database in the last one hour
SELECT COUNT(*) AS 'Total Records',
DB_NAME(DatabaseID) AS 'Database Name'
FROM dbo.TraceResults (NOLOCK)
WHERE StartTime BETWEEN DATEADD(Hour, -1,
GETDATE()) AND GETDATE()

GROUP BY DatabaseID
ORDER BY DatabaseID
GO
-- High CPU usage in the last one hour
USE AdventureWorks
GO
SELECT *
FROM dbo.TraceResults (NOLOCK)
WHERE CPU > 5000
AND StartTime BETWEEN DATEADD(Hour, -1,
GETDATE()) AND GETDATE()
GO
-- Long duration in the last one hour
USE AdventureWorks
GO
SELECT *
FROM dbo.TraceResults (NOLOCK)
WHERE Duration > 10000000
AND StartTime BETWEEN DATEADD(Hour, -1,
GETDATE()) AND GETDATE()
GO
------------------------------------------------
I hope this article is useful to help you