What is SQL Server Profiler?

SQL Server Profiler is a tool that captures SQL Server 2005 events from a database server. The events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem. SQL Server Profiler is used for activities such as:

• Stepping through problem queries to find the cause of the problem.
• Finding and diagnosing slow-running queries.
• Capturing the series of Transact-SQL statements that lead to a problem. The saved trace can then be used to replicate the problem on a test server where the problem can be diagnosed.
• Monitoring the performance of SQL Server to tune workloads.
• Correlating performance counters to diagnose problems

SQL Server Profiler also supports auditing the actions performed on instances of SQL Server. Audits record security-related actions for later review by a security administrator.

Typical uses of SQL Server Profiler

Your use of SQL Server Profiler depends on the reason you are monitoring an instance of the SQL Server Database Engine, however, following are some of what you can use SQL Server profiler for:

• Monitor the performance of an instance of the SQL Server Database Engine, Analysis Server, or Integration Services (after they have occurred).
• Debug Transact-SQL statements and SQL Server stored procedures.
• Analyze SQL Server performance by identifying slowly executing queries.
• Perform stress testing and quality assurance by replaying traces.
• Replay traces of one or more users.
• Perform query analysis by saving Showplan results.
• Test Transact-SQL statements and SQL Server stored procedures in the development phase of a project by single-stepping through statements to confirm that the code works as expected.
• Troubleshoot problems in SQL Server by capturing events on a production system and replaying them on a test system. This is useful for testing or debugging purposes and allows users to continue using the production system without interference.
• Audit and review activity that occurred on an instance of SQL Server allowing a security administrator to review any of the auditing events, including the success and failure of a login attempt and the success and failure of permissions in accessing statements and objects.
• Save trace results in XML to provide a standardized hierarchical structure to trace results allowing you to modify existing traces or manually create traces and then replay them.
• Aggregate trace results to allow similar event classes to be grouped and analyzed. These results provide counts based on a single column grouping.
• Allow users who are not administrators to create traces.
• Correlate performance counters with a trace to diagnose performance problems.
• Configure trace templates that can be used for tracing later.

Space Requirements for SQL Server Profiler

SQL Server Profiler uses space in the temp directory to improve access to trace data. SQL Server Profiler requires at least 10 megabytes (MB) of free space. If free space drops below 10 MB while you are using SQL Server Profiler, all SQL Server Profiler functions stop.

When SQL Server Profiler uses space in the temp directory, this space usage may cause the temp directory to grow rapidly. To prevent problems, you can place the temp directory on a drive that is not a system drive by changing the value for the TEMP environment variable.

Click here to go back to the SQL Server content index page.