Typical Uses of SQL Server Profiler
SQL Server Profiler is a rich interface to create and manage traces and analyze and replay trace results. Your use of SQL Server Profiler depends on the reason you are monitoring an instance of the SQL Server Database Engine. For example, if you are in the development phase of the production cycle, you are not as concerned about the overhead incurred by tracing many events as you are concerned about obtaining all the performance details you can gather. If, by contrast, you are monitoring a production server, you want your traces to be more focused and limited in time so tracing incurs the least load on your server.
Use SQL Server Profiler to:
- 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 stored procedures.
- Analyze 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 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. This allows 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. This allows 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.
See Also
Other Resources
Locks Event Category
Sessions Event Category
Stored Procedures Event Category
TSQL Event Category
Introducing SQL Server Profiler
Server Performance and Activity Monitoring How-to Topics