SQL Server Profiler is a powerful tool that is available with SQL Server since a long time; however, it has mostly been underutilized by DBAs. SQL Server Profiler can perform various significant functions such as tracing what is running under the SQL Server Engine’s hood, and finding out how queries are resolved internally and what scripts are running to accomplish any T-SQL command. The major functions this tool can perform have been listed below:
- Creating trace
- Watching trace
- Storing trace
- Replaying trace
Trace includes all the T-SQL scripts that run simultaneously on SQL Server. As trace contains all the T-SQL scripts running on SQL Server, it often tends to become considerably huge. Hence, it is always a good practice to capture only those data that are actually required for analysis.
Dilemma to use on Production Server
SQL Server Profiler has a lot of potential as a tool and offers several advantages. Unfortunately, developers have been keeping their hands off from this tool and usually give two major excuses.
- Reason 1: Profiler adds too much overhead to production server.
- Reason 2: Profiler accumulates plethora of data making it extremely intricate to analyze them afterwards.
Reference : Pinal Dave (http://blog.SQLAuthority.com)