Profiling in SQL Server

SQL Profiler captures SQL Server events from a server. The events are saved in a trace file that can be used to analyze and diagnose problem.

The different purposes of using SQL Profiler are:
It is used to find the cause of the problem by stepping through problem queries.
It is very useful to analyze the cause of slow running queries.
It can be used to tune workload of the SQL server.
It also stores security-related actions that can be reviewed by a security administrator.
SQL Profiler also supports auditing the actions performed on instances of SQL Server.