SQL Server ProfilerSQL Server Profiler

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance

SQL Server ProfilerSQL 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.

重要

已弃用 SQL 跟踪和 SQL Server ProfilerSQL Server ProfilerSQL Trace and SQL Server ProfilerSQL Server Profiler are deprecated. 包含 Microsoft SQL Server 跟踪和重播对象的“Microsoft.SqlServer.Management.Trace”命名空间也已遭弃用。The Microsoft.SqlServer.Management.Trace namespace that contains the Microsoft SQL Server Trace and Replay objects are also deprecated.

后续版本的 Microsoft SQL Server 将删除该功能。This feature will be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

请改用扩展事件。Use Extended Events instead. 有关扩展事件的详细信息,请参阅快速入门:SQL Server 中的扩展事件SSMS XEvent 探查器For more information on Extended Events, see Quick Start: Extended events in SQL Server and SSMS XEvent Profiler.

备注

不支持针对 Analysis Services 工作负载的 SQL Server ProfilerSQL Server ProfilerSQL Server ProfilerSQL Server Profiler for Analysis Services workloads are supported.

备注

尝试从 SQL Server Profiler 连接到 Azure SQL 数据库时,会错误地引发误导性错误消息,如下所示:When you try to connect to a Azure SQL Database from SQL server profiler, it incorrectly throws a misleading error message as follows:

  • 若要对 SQL Server 运行跟踪,你必须是 sysadmin 固定服务器角色的成员或拥有 ALTER TRACE 权限。In order to run a trace against SQL Server, you must be a member of sysadmin fixed server role or have the ALTER TRACE permission.

该消息应说明了 SQL Server Profiler 不支持 Azure SQL 数据库。The message should have explained that Azure SQL Database is not supported by SQL Server profiler.

在哪里可以启动探查器?Where is the Profiler?

可以在 SQL Server Management StudioSQL Server Management Studio 中通过多种方式启动 Profiler。You can start the Profiler in a number of ways from within SQL Server Management StudioSQL Server Management Studio. 下面的主题列出了启动探查器的方式。Here is a topic that lists the ways to start the Profiler.

捕获和重播跟踪数据Capture and replay trace data

下表显示我们在 SQL ServerSQL Server 中推荐使用的用于捕获和重播跟踪数据的功能。The following table shows the features we recommend using in SQL ServerSQL Server to capture and replay your trace data.

功能\目标工作负荷Feature\Target Workload 关系引擎Relational Engine Analysis ServicesAnalysis Services
跟踪捕获Trace Capture 扩展事件 SQL Server Management StudioSQL Server Management Studio 中的图形用户界面Extended Events graphical user interface in SQL Server Management StudioSQL Server Management Studio SQL Server ProfilerSQL Server Profiler
跟踪重播Trace Replay Distributed ReplayDistributed Replay SQL Server ProfilerSQL Server Profiler

使用 SQL Server ProfilerUse SQL Server Profiler

Microsoft SQL Server ProfilerSQL Server Profiler 是 SQL 跟踪的图形用户界面,用于监视 数据库引擎Database Engine 或 Analysis Services 的实例。Microsoft SQL Server ProfilerSQL 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 ProfilerSQL 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.

  • 捕获导致某个问题的一系列 Transact-SQLTransact-SQL 语句。Capturing the series of Transact-SQLTransact-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.

  • 监视 SQL ServerSQL Server 的性能以优化工作负荷。Monitoring the performance of SQL ServerSQL Server to tune workloads. 有关为数据库工作负荷而优化物理数据库设计的信息,请参阅 Database Engine Tuning AdvisorFor information about tuning the physical database design for database workloads, see Database Engine Tuning Advisor.

  • 使性能计数器与诊断问题关联。Correlating performance counters to diagnose problems.

SQL Server ProfilerSQL Server Profiler 还支持对 SQL ServerSQL Server 实例上执行的操作进行审核。also supports auditing the actions performed on instances of SQL ServerSQL Server. 审核将记录与安全相关的操作,供安全管理员以后复查。Audits record security-related actions for later review by a security administrator.

SQL Server Profiler 概念SQL Server Profiler concepts

若要使用 SQL Server ProfilerSQL Server Profiler,您需要了解描述该工具工作方式的术语。To use SQL Server ProfilerSQL Server Profiler, you need to understand the terms that describe the way the tool functions.

备注

使用 SQL Server ProfilerSQL Server Profiler 时,了解 SQL 跟踪非常有帮助。Understanding SQL Trace really helps when working with SQL Server ProfilerSQL Server Profiler. 有关详细信息,请参阅 SQL TraceFor more information, see SQL Trace.

事件Event

事件是在 SQL Server 数据库引擎SQL Server Database Engine实例中生成的操作。An event is an action generated within an instance of SQL Server 数据库引擎SQL Server Database Engine. 示例包括:Examples of these are:

  • 登录连接、失败和断开。Login connections, failures, and disconnections.
  • Transact-SQLTransact-SQLSELECTINSERTUPDATEDELETE 语句。SELECT, INSERT, UPDATE, and DELETE statements.
  • 远程过程调用 (RPC) 批处理状态。Remote procedure call (RPC) batch status.
  • 存储过程的开始或结束。The start or end of a stored procedure.
  • 存储过程中的语句的开始或结束。The start or end of statements within stored procedures.
  • SQL 批处理的开始或结束。The start or end of an SQL batch.
  • 写入到 SQL ServerSQL Server 错误日志的错误。An error written to the SQL ServerSQL Server error log.
  • 在数据库对象上获取或释放的锁。A lock acquired or released on a database object.
  • 打开的游标。An opened cursor.
  • 安全权限检查。Security permission checks.

由事件生成的所有数据显示在单个行中的跟踪内。All of the data generated by an event is displayed in the trace in a single row. 该行与详细说明事件的数据列相交。This row is intersected by data columns that describe the event in detail.

EventClassEventClass

事件类是可跟踪的事件类型。An event class is a type of event that can be traced. 事件类包含所有可由事件报告的数据。The event class contains all of the data that can be reported by an event. 下面是事件类的示例:The following are examples of event classes:

  • SQL:BatchCompletedSQL:BatchCompleted
  • 审核登录Audit Login
  • 审核注销Audit Logout
  • Lock: AcquiredLock: Acquired
  • Lock: ReleasedLock: Released

EventCategoryEventCategory

事件类别定义 SQL Server ProfilerSQL Server Profiler中的事件的分组方法。An event category defines the way events are grouped within SQL Server ProfilerSQL Server Profiler. 例如,所有锁事件类都分组在 Locks 事件类别中。For example, all lock events classes are grouped within the Locks event category. 但是,事件类别仅存在于 SQL Server ProfilerSQL Server Profiler中。However, event categories only exist within SQL Server ProfilerSQL Server Profiler. 该术语不反映引擎事件的分组方法。This term doesn't reflect the way Engine events are grouped.

DataColumnDataColumn

数据列是在跟踪中捕获的事件类的属性。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 are applicable to all event classes. 例如,在捕获 Lock: Acquired 事件类的跟踪中,BinaryData 数据列包含锁定的页 ID 或行的值,但 Integer Data 数据列不包含任何值,因为该数据列不适用于被捕获的事件类。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 isn't applicable to the event class being captured.

模板Template

模板定义跟踪的默认配置。A template defines the default configuration for a trace. 具体地说,它包括您要使用 SQL Server ProfilerSQL Server Profiler监视的事件类。Specifically, it includes the event classes you want to monitor with SQL Server ProfilerSQL Server Profiler. 例如,可以创建一个指定了要使用的事件、数据列和筛选器的模板。For example, you can create a template that specifies the events, data columns, and filters to use. 模板不会被执行,而是用 .tdf 扩展名保存为文件。A template isn't executed, but rather is saved as a file with a .tdf extension. 保存后,模板就可以在启动基于此模板的跟踪时控制捕获的跟踪数据。Once saved, the template controls the trace data that is 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. 为此,请选择 Exception 事件类以及 ErrorStateSeverity 数据列。To do this, you select the Exception event class and the Error, State, and Severity data columns. 需要收集这三列的数据,以使跟踪结果可提供有意义的数据。Data from these three columns needs to be collected in order for the trace results to provide meaningful data. 然后,可运行以此方式配置的跟踪,并可收集有关服务器中发生的任何 Exception 事件的数据。You can then run a trace, configured in such a manner, and collect data on any Exception events that occur in the server. 可以保存跟踪数据,也可以立刻将其用于分析。Trace data can be saved, or used immediately for analysis. 尽管某些事件(如 Exception 事件)永远不会被重播,但跟踪以后可以被重播。Traces can be replayed at a later date, although certain events, such as Exception events, are never replayed. 还可以将跟踪保存为模板,以便在将来生成类似的跟踪。You can also save the trace as a template to build similar traces in the future.

SQL ServerSQL Server 提供了以下两种用于跟踪 SQL ServerSQL Server 实例的方法:使用 SQL Server ProfilerSQL Server Profiler 进行跟踪,或使用系统存储过程进行跟踪。provides two ways to trace an instance of SQL ServerSQL Server: you can trace with SQL Server ProfilerSQL 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. 例如,可以在跟踪中将 Microsoft Windows 用户名限制为特定的用户,从而减少输出的数据。For example, you can limit the Microsoft Windows user names in the trace to specific users, thereby reducing the output data.

如果没有设置筛选器,则跟踪输出中将返回选定事件类的所有事件。If a filter isn't set, all events of the selected event classes are returned in the trace output.

SQL Server Profiler 任务SQL Server Profiler tasks

任务说明Task description 主题Topic
列出 SQL Server 提供的用于监视特定类型事件的预定义模板,以及重播跟踪所需使用的权限。Lists the predefined templates that SQL Server provides for monitoring certain types of events, and the permissions required to use to replay traces. SQL Server Profiler 模板和权限SQL Server Profiler Templates and Permissions
介绍了如何运行 SQL ServerSQL Server Profiler。Describes how to run SQL ServerSQL Server Profiler. 运行 SQL Server Profiler 所需的权限Permissions Required to Run SQL Server Profiler
介绍如何创建跟踪。Describes how to create a trace. 创建跟踪 (SQL Server Profiler)Create a Trace (SQL Server Profiler)
说明如何指定跟踪文件的事件和数据列。Describes how to specify events and data columns for a trace file. 指定跟踪文件的事件和数据列 (SQL Server Profiler)Specify Events and Data Columns for a Trace File (SQL Server Profiler)
说明如何将跟踪结果保存到文件。Describes how to save trace results to a file. 将跟踪结果保存到文件 (SQL Server Profiler)Save Trace Results to a File (SQL Server Profiler)
说明如何将跟踪结果保存到表。Describes how to save trace results to a table. 将跟踪结果保存到表 (SQL Server Profiler)Save Trace Results to a Table (SQL Server Profiler)
说明如何筛选跟踪中的事件。Describes how to filter events in a trace. 在跟踪中筛选事件 (SQL Server Profiler)Filter Events in a Trace (SQL Server Profiler)
说明如何查看筛选信息。Describes how to view filter information. 查看筛选器信息 (SQL Server Profiler)View Filter Information (SQL Server Profiler)
说明如何修改筛选器。Describes how to Modify a Filter. 修改筛选器 (SQL Server Profiler)Modify a Filter (SQL Server Profiler)
说明如何设置跟踪文件的文件大小上限 (SQL Server ProfilerSQL Server Profiler)。Describes how to Set a Maximum File Size for a Trace File (SQL Server ProfilerSQL Server Profiler). 设置跟踪文件的最大文件大小 (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. 设置跟踪表的最大表大小 (SQL Server Profiler)Set a Maximum Table Size for a Trace Table (SQL Server Profiler)
说明如何启动跟踪。Describes how to start a trace. 启动跟踪Start a Trace
说明如何在连接到服务器后自动启动跟踪。Describes how to start a trace automatically after connecting to a server. 连接到服务器后自动启动跟踪 (SQL Server Profiler)Start a Trace Automatically after Connecting to a Server (SQL Server Profiler)
说明如何基于事件开始时间筛选事件。Describes how to filter events based on the event start time. 基于事件开始时间筛选事件 (SQL Server Profiler)Filter Events Based on the Event Start Time (SQL Server Profiler)
说明如何基于事件结束时间筛选事件。Describes how to filter events based on the event end time. 基于事件结束时间筛选事件 (SQL Server Profiler)Filter Events Based on the Event End Time (SQL Server Profiler)
说明如何筛选跟踪中的服务器进程 ID (SPID)。Describes how to filter server process IDs (SPIDs) in a trace. 在跟踪中筛选服务器进程 ID (SPID) (SQL Server Profiler)Filter Server Process IDs (SPIDs) in a Trace (SQL Server Profiler)
介绍如何暂停跟踪。Describes how to pause a trace. 暂停跟踪 (SQL Server Profiler)Pause a Trace (SQL Server Profiler)
介绍如何停止跟踪。Describes how to stop a trace. 停止跟踪 (SQL Server Profiler)Stop a Trace (SQL Server Profiler)
说明如何在跟踪暂停或停止之后运行跟踪。Describes how to run a trace after it has been paused or stopped. 在跟踪暂停或停止之后运行跟踪 (SQL Server Profiler)Run a Trace After It Has Been Paused or Stopped (SQL Server Profiler)
说明如何清除跟踪窗口。Describes how to clear a trace window. 清除跟踪窗口 (SQL Server Profiler)Clear a Trace Window (SQL Server Profiler)
说明如何关闭跟踪窗口。Describes how to close a trace window. 关闭跟踪窗口 (SQL Server Profiler)Close a Trace Window (SQL Server Profiler)
说明如何设置跟踪定义默认值。Describes how to set trace definition defaults. 设置跟踪定义默认值 (SQL Server Profiler)Set Trace Definition Defaults (SQL Server Profiler)
说明如何设置跟踪显示默认值。Describes how to set trace display defaults. 设置跟踪显示默认值 (SQL Server Profiler)Set Trace Display Defaults (SQL Server Profiler)
说明如何打开跟踪文件。Describes how to open a trace file. 打开跟踪文件 (SQL Server Profiler)Open a Trace File (SQL Server Profiler)
说明如何打开跟踪表。Describes how to open a trace table. 打开跟踪表 (SQL Server Profiler)Open a Trace Table (SQL Server Profiler)
说明如何重播跟踪表。Describes how to replay a trace table. 重播跟踪表 (SQL Server Profiler)Replay a Trace Table (SQL Server Profiler)
说明如何重播跟踪文件。Describes how to replay a trace file. 重播跟踪文件 (SQL Server Profiler)Replay a Trace File (SQL Server Profiler)
说明如何每次重播一个事件。Describes how to replay a single event at a time. 每次重播一个事件 (SQL Server Profiler)Replay a Single Event at a Time (SQL Server Profiler)
说明如何重播到断点。Describes how to replay to a breakpoint. 重播到断点 (SQL Server Profiler)Replay to a Breakpoint (SQL Server Profiler)
介绍如何重播至光标处。Describes how to replay to a cursor. 重播至光标处 (SQL Server Profiler)Replay to a Cursor (SQL Server Profiler)
介绍了如何重播 Transact-SQLTransact-SQL 脚本。Describes how to replay a Transact-SQLTransact-SQL script. 重播 Transact-SQL 脚本 (SQL Server Profiler)Replay a Transact-SQL Script (SQL Server Profiler)
说明如何创建跟踪模板。Describes how to create a trace template. 创建跟踪模板 (SQL Server Profiler)Create a Trace Template (SQL Server Profiler)
说明如何修改跟踪模板。Describes how to modify a trace template. 修改跟踪模板 (SQL Server Profiler)Modify a Trace Template (SQL Server Profiler)
说明如何设置全局跟踪选项。Describes how to set global trace options. 设置全局跟踪选项 (SQL Server Profiler)Set Global Trace Options (SQL Server Profiler)
说明如何在跟踪时查找值或数据列。Describes how to find a value or data column while tracing. 在跟踪时查找值或数据列 (SQL Server Profiler)Find a Value or Data Column While Tracing (SQL Server Profiler)
说明如何从正在运行的跟踪派生模板。Describes how to derive a template from a running trace. 从正在运行的跟踪中派生模板 (SQL Server Profiler)Derive a Template from a Running Trace (SQL Server Profiler)
说明如何从跟踪文件或跟踪表派生模板。Describes how to derive a template from a trace file or trace table. 从跟踪文件或跟踪表派生模板 (SQL Server Profiler)Derive a Template from a Trace File or Trace Table (SQL Server Profiler)
介绍了如何创建 Transact-SQLTransact-SQL 脚本来运行跟踪。Describes how to create a Transact-SQLTransact-SQL script for running a trace. 创建 Transact-SQL 脚本来运行跟踪 (SQL Server Profiler)Create a Transact-SQL Script for Running a Trace (SQL Server Profiler)
说明如何导出跟踪模板。Describes how to export a trace template. 导出跟踪模板 (SQL Server Profiler)Export a Trace Template (SQL Server Profiler)
说明如何导入跟踪模板。Describes how to import a trace template. 导入跟踪模板 (SQL Server Profiler)Import a Trace Template (SQL Server Profiler)
说明如何从跟踪提取脚本。Describes how to extract a script from a trace. 从跟踪提取脚本 (SQL Server Profiler)Extract a Script from a Trace (SQL Server Profiler)
说明如何将跟踪与 Windows 性能日志数据关联。Describes how to correlate a trace with Windows performance log data. 将跟踪与 Windows 性能日志数据关联 (SQL Server Profiler)Correlate a Trace with Windows Performance Log Data (SQL Server Profiler)
说明如何组织跟踪中显示的列。Describes how to organize columns displayed in a trace. 组织跟踪中显示的列 (SQL Server Profiler)Organize Columns Displayed in a Trace (SQL Server Profiler)
介绍了如何启动 SQL Server ProfilerSQL Server ProfilerDescribes how to start SQL Server ProfilerSQL Server Profiler. 启动 SQL Server ProfilerStart SQL Server Profiler
说明如何保存跟踪和跟踪模板。Describes how to save traces and trace templates. 保存跟踪和跟踪模板Save Traces and Trace Templates
说明如何修改跟踪模板。Describes how to modify trace templates. 修改跟踪模板Modify Trace Templates
说明如何将跟踪与 Windows 性能日志数据关联。Describes how to correlate a trace with Windows performance log data. 将跟踪与 Windows 性能日志数据关联Correlate a Trace with Windows Performance Log Data
介绍了如何使用 SQL Server ProfilerSQL Server Profiler 查看和分析跟踪。Describes how to view and analyze traces with SQL Server ProfilerSQL Server Profiler. 使用 SQL Server Profiler 查看和分析跟踪View and Analyze Traces with SQL Server Profiler
介绍了如何使用 SQL Server ProfilerSQL Server Profiler 分析死锁。Describes how to analyze deadlocks with SQL Server ProfilerSQL Server Profiler. 使用 SQL Server Profiler 分析死锁Analyze Deadlocks with SQL Server Profiler
说明如何在 SQL Server Profiler 中使用 SHOWPLAN 结果来分析查询。Describes how to analyze queries with SHOWPLAN results in SQL Server Profiler. 在 SQL Server Profiler 中使用 SHOWPLAN 结果来分析查询Analyze Queries with SHOWPLAN Results in SQL Server Profiler
介绍了如何使用 SQL Server ProfilerSQL Server Profiler 筛选跟踪。Describes how to filter traces with SQL Server ProfilerSQL Server Profiler. 使用 SQL Server Profiler 筛选跟踪Filter Traces with SQL Server Profiler
说明如何使用 SQL Server ProfilerSQL Server Profiler 的重播功能。Describes how to use the replay features of SQL Server ProfilerSQL Server Profiler. 重播跟踪Replay Traces
列出了 SQL Server ProfilerSQL Server Profiler 的上下文相关帮助主题。Lists the context-sensitive help topics for SQL Server ProfilerSQL Server Profiler. SQL Server Profiler 的 F1 帮助SQL Server Profiler F1 Help
列出由 SQL Server ProfilerSQL Server Profiler 用于监视性能和活动的系统存储过程。Lists the system stored procedures that are used by SQL Server ProfilerSQL Server Profiler to monitor performance and activity. SQL Server Profiler 存储过程 (Transact-SQL)SQL Server Profiler Stored Procedures (Transact-SQL)

另请参阅See also