SQL Server Profiler
SQL Server Profiler is a rich interface to create and manage traces and analyze and replay trace results. 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.
Important
|
|---|
|
We are announcing the deprecation of SQL Server Profiler for Database Engine Trace Capture and Trace Replay. These features will be supported in the next version of SQL Server, but will be removed in a later version. The specific version of SQL Server has not been determined. The Microsoft.SqlServer.Management.Trace namespace that contains the Microsoft SQL Server Trace and Replay objects will also be deprecated. Note that SQL Server Profiler for the Analysis Services workloads is not being deprecated, and will continue to be supported. The following table shows the features we recommend using in SQL Server 2012 to capture and replay your trace data: |
|
Feature\Target Workload |
Relational Engine |
Analysis Services |
|
Trace Capture |
Extended Events graphical user interface in SQL Server Management Studio |
SQL Server Profiler |
|
Trace Replay |
Distributed Replay |
SQL Server Profiler |
Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly. 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. For information about tuning the physical database design for database workloads, see Database Engine Tuning Advisor.
-
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.
To use SQL Server Profiler, you need to understand the terms that describe the way the tool functions.
Note
|
|---|
|
When working with SQL Server Profiler, it is helpful to understand SQL Trace. For more information, see SQL Trace. |
|
Task Description |
Topic |
|---|---|
|
Lists the predefined templates that SQL Server provides for monitoring certain types of events, and the permissions required to use to replay traces. |
|
|
Describes how to run SQL Server Profiler. |
|
|
Describes how to create a trace. |
|
|
Describes how to specify events and data columns for a trace file. |
Specify Events and Data Columns for a Trace File (SQL Server Profiler) |
|
Describes how to save trace results to a file. |
|
|
Describes how to save trace results to a table. |
|
|
Describes how to filter events in a trace. |
|
|
Describes how to view filter information. |
|
|
Describes how to Modify a Filter. |
|
|
Describes how to Set a Maximum File Size for a Trace File (SQL Server Profiler). |
Set a Maximum File Size for a Trace File (SQL Server Profiler) |
|
Describes how to set a maximum table size for a trace table. |
Set a Maximum Table Size for a Trace Table (SQL Server Profiler) |
|
Describes how to start a trace. |
|
|
Describes how to start a trace automatically after connecting to a server. |
Start a Trace Automatically after Connecting to a Server (SQL Server Profiler) |
|
Describes how to filter events based on the event start time. |
Filter Events Based on the Event Start Time (SQL Server Profiler) |
|
Describes how to filter events based on the event end time. |
Filter Events Based on the Event End Time (SQL Server Profiler) |
|
Describes how to filter server process IDs (SPIDs) in a trace. |
Filter Server Process IDs (SPIDs) in a Trace (SQL Server Profiler) |
|
Describes how to pause a trace. |
|
|
Describes how to stop a trace. |
|
|
Describes how to run a trace after it has been paused or stopped. |
Run a Trace After It Has Been Paused or Stopped (SQL Server Profiler) |
|
Describes how to clear a trace window. |
|
|
Describes how to close a trace window. |
|
|
Describes how to set trace definition defaults. |
|
|
Describes how to set trace display defaults. |
|
|
Describes how to open a trace file. |
|
|
Describes how to open a trace table. |
|
|
Describes how to replay a trace table. |
|
|
Describes how to replay a trace file. |
|
|
Describes how to replay a single event at a time. |
|
|
Describes how to replay to a breakpoint. |
|
|
Describes how to replay to a cursor. |
|
|
Describes how to replay a Transact-SQL script. |
|
|
Describes how to create a trace template. |
|
|
Describes how to modify a trace template. |
|
|
Describes how to set global trace options. |
|
|
Describes how to find a value or data column while tracing. |
Find a Value or Data Column While Tracing (SQL Server Profiler) |
|
Describes how to derive a template from a running trace. |
Derive a Template from a Running Trace (SQL Server Profiler) |
|
Describes how to derive a template from a trace file or trace table. |
Derive a Template from a Trace File or Trace Table (SQL Server Profiler) |
|
Describes how to create a Transact-SQL script for running a trace. |
Create a Transact-SQL Script for Running a Trace (SQL Server Profiler) |
|
Describes how to export a trace template. |
|
|
Describes how to import a trace template. |
|
|
Describes how to extract a script from a trace. |
|
|
Describes how to correlate a trace with Windows performance log data. |
Correlate a Trace with Windows Performance Log Data (SQL Server Profiler) |
|
Describes how to organize columns displayed in a trace. |
|
|
Describes how to start SQL Server Profiler. |
|
|
Describes how to save traces and trace templates. |
|
|
Describes how to modify trace templates. |
|
|
Describes how to correlate a trace with Windows performance log data. |
|
|
Describes how to view and analyze traces with SQL Server Profiler. |
|
|
Describes how to analyze deadlocks with SQL Server Profiler. |
|
|
Describes how to analyze queries with SHOWPLAN results in SQL Server Profiler. |
Analyze Queries with SHOWPLAN Results in SQL Server Profiler |
|
Describes how to filter traces with SQL Server Profiler. |
|
|
Describes how to use the replay features of SQL Server Profiler. |
|
|
Lists the context-sensitive help topics for SQL Server Profiler. |
|
|
Lists the system stored procedures that are used by SQL Server Profiler to monitor performance and activity. |
Important