SQL Server Profiler is an interface to create and manage traces and analyze and replay trace results. Events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when diagnosing a problem.
Tábhachtach
SQL Trace and SQL Server Profiler are deprecated. The Microsoft.SqlServer.Management.Trace namespace that contains the Microsoft SQL Server Trace and Replay objects is also deprecated. However, Analysis Services workloads are supported.
This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
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 affect 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 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.
SQL Server Profiler concepts
To use SQL Server Profiler, you need to understand the terms that describe the way the tool functions.
Nóta
Understanding SQL Trace helps when working with SQL Server Profiler. For more information, see SQL Trace.
Event
An event is an action generated within an instance of SQL Server Database Engine. Examples of these are:
Login connections, failures, and disconnections.
Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.
Remote procedure call (RPC) batch status.
The start or end of a stored procedure.
The start or end of statements within stored procedures.
The start or end of a SQL batch.
An error written to the SQL Server error log.
A lock acquired or released on a database object.
An opened cursor.
Security permission checks.
The trace displays all of the data generated by an event in a single row. This row is intersected by data columns that describe the event in detail.
EventClass
An event class is a type of event that can be traced. The event class contains all of the data that an event can report. The following are examples of event classes:
SQL:BatchCompleted
Audit Login
Audit Logout
Lock: Acquired
Lock: Released
EventCategory
An event category defines the way events are grouped within SQL Server Profiler. For example, all lock event classes are grouped within the Locks event category. However, event categories only exist within SQL Server Profiler. This term doesn't reflect the way Engine events are grouped.
DataColumn
A data column is an attribute of an event class captured in the trace. Because the event class determines the type of data that can be collected, not all data columns apply to all event classes. For example, in a trace that captures the Lock: Acquired event class, the BinaryData data column contains the value of the locked page ID or row, but the Integer Data data column doesn't contain any value because it doesn't apply to the event class being captured.
Template
A template defines the default configuration for a trace. Specifically, it includes the event classes you want to monitor with SQL Server Profiler. For example, you can create a template specifying the events, data columns, and filters. A template isn't executed but saved as a file with a .tdf extension. Once saved, the template controls the trace data captured when a trace based on the template is launched.
Trace
A trace captures data based on selected event classes, data columns, and filters. For example, you can create a trace to monitor exception errors. You select the Exception event class and the Error, State, and Severity data columns to do this. Data from these three columns must be collected for the trace results to provide meaningful data. You can then run a trace, configured in such a manner, and collect data on any Exception events in the server. Trace data can be saved or used immediately for analysis. Traces can be replayed later, although certain events, such as Exception events, are never replayed. You can also save the trace as a template to build similar traces.
SQL Server provides two ways to trace an instance of SQL Server: you can trace with SQL Server Profiler, or you can trace using system stored procedures.
Filter
When you create a trace or template, you can define criteria to filter the data collected by the event. To keep traces from becoming too large, you can filter them so that only a subset of the event data is collected. For example, limiting the Microsoft Windows user names in the trace to specific users reduces the output data.
If a filter isn't set, the trace output returns all events of the selected event classes.
SQL Server Profiler tasks
Task description
Article
Lists the predefined templates that SQL Server provides for monitoring certain events and the permissions required to use replay traces.
Extended Events and SQL Server Profiler are tools for monitoring and troubleshooting SQL Server performance. SQL Server Profiler is deprecated and should only be used with Analysis Services. Extended Events is the replacement for SQL Server Profiler and provides advanced troubleshooting capabilities not available elsewhere. The key differences are noted here to help with the migration from SQL Server Profiler to Extended Events.
Extended Events tool
Extended Events is a lightweight, highly scalable, and flexible event-handling system built into SQL Server.
Extended Events sessions typically consume fewer resources than SQL Trace and SQL Server Profiler, making them more suitable for production environments. Extended Events supports capturing events that are available in modern versions of SQL.
In contrast, the events available in SQL Trace/SQL Server Profiler are limited to features available in SQL Server 2008R2 and earlier.
Extended Events provides superior filtering capabilities, a smaller default payload, and features not offered in Profiler, such as in-memory and aggregate targets and multi-target support.
For more information about Extended Events, see Extended Events.
SQL Server Profiler tool
SQL Server Profiler is a graphical user interface that uses SQL Trace to capture activity for an instance of SQL Server or Analysis Services.
SQL Server Profiler can be resource-intensive if improperly configured, impacting server performance, especially when used on production servers. It has built-in templates to support quick tracing.
In summary, though SQL Server Profiler is an older tool that may be familiar to many users, Extended Events is a modern alternative that offers better performance, more detailed event information, and capabilities for troubleshooting and monitoring SQL Server instances not available elsewhere. Due to its advantages over Profiler, Extended Events is recommended for new tracing and monitoring work.
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.