SQL 跟踪SQL Trace

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

在 SQL 跟踪中,如果事件是在跟踪定义中列出的事件类的实例,则收集这些事件。In SQL Trace, events are gathered if they are instances of event classes listed in the trace definition. 可以将这些事件从跟踪中筛选出来或为其目标进行排队。These events can be filtered out of the trace or queued for their destination. 目标可以是文件或 SQL ServerSQL Server 管理对象 (SMO),它可以使用管理 SQL ServerSQL Server的应用程序中的跟踪信息。The destination can be a file or SQL ServerSQL Server Management Objects (SMO), which can use the trace information in applications that manage SQL ServerSQL Server.

重要

已弃用 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 is in maintenance mode and may 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.

SQL 跟踪的优点Benefits of SQL Trace

Microsoft SQL ServerSQL Server 提供 Transact-SQLTransact-SQL 系统存储过程来创建对 SQL Server 数据库引擎SQL Server Database Engine实例的跟踪。Microsoft SQL ServerSQL Server provides Transact-SQLTransact-SQL system stored procedures to create traces on an instance of the SQL Server 数据库引擎SQL Server Database Engine. 可以不使用 SQL Server ProfilerSQL Server Profiler,而使用这些系统存储过程从您自己的应用程序中手动创建跟踪。These system stored procedures can be used from within your own applications to create traces manually, instead of using SQL Server ProfilerSQL Server Profiler. 这样,您就可以针对企业的特定需求编写自定义应用程序。This allows you to write custom applications specific to the needs of your enterprise.

SQL 跟踪结构SQL Trace Architecture

事件源可以是生成跟踪事件(例如 Transact-SQLTransact-SQL 批处理)或 SQL ServerSQL Server 事件(例如死锁)的任何源。Event Sources can be any source that produces the trace event, such as Transact-SQLTransact-SQL batches or SQL ServerSQL Server events, such as deadlocks. 有关事件的详细信息,请参阅 SQL Server Event Class ReferenceFor more information about events, see SQL Server Event Class Reference. 事件发生后,如果该事件类已经包含在跟踪定义中,则跟踪将收集该事件信息。After an event occurs, if the event class has been included in a trace definition, the event information is gathered by the trace. 如果已经在跟踪定义中为该事件类定义筛选器,则将应用这些筛选器并将跟踪事件信息传递到队列。If filters have been defined for the event class in the trace definition, the filters are applied and the trace event information is passed to a queue. 从队列中,跟踪信息或者被写入文件,或者由应用程序(例如 SQL Server ProfilerSQL Server Profiler)中的 SMO 使用。From the queue, the trace information is either written to a file or can be used by SMO in applications, such as SQL Server ProfilerSQL Server Profiler. 以下关系图显示了在跟踪期间 SQL 跟踪如何收集事件。The following diagram shows how SQL Trace gathers events during a tracing.

数据库引擎事件跟踪进程Database Engine event tracing process

SQL 跟踪的术语SQL Trace Terminology

下列词汇介绍了 SQL 跟踪的重要概念。The following terms describe the key concepts of SQL Trace.

事件Event
MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine实例内发生的操作。The occurrence of an action within an instance of the MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine.

数据列Data column
事件的属性。An attribute of an event.

事件类Event class
可以被跟踪的事件类型。A type of event that can be traced. 该事件类包含可以由事件报告的所有数据列。The event class contains all of the data columns that can be reported by an event.

事件类别Event category
一组相关的事件类。A group of related event classes.

跟踪 (名词)Trace (noun)
数据库引擎Database Engine返回的事件和数据的集合。A collection of events and data returned by the 数据库引擎Database Engine.

跟踪 (动词)Trace (verb)
收集并监视 SQL ServerSQL Server实例中的事件。To collect and monitor events in an instance of SQL ServerSQL Server.

跟踪定义Tracedefinition
事件类、数据列和筛选器的集合,用于标识跟踪过程中收集的事件的类型。A collection of event classes, data columns and filters that identify the types of events to be collected during a trace.

ActualRebindsFilter
限制跟踪中收集的事件的条件。Criteria that limit the events that are collected in a trace.

跟踪文件Trace file
保存跟踪时创建的文件。A file created when a trace is saved.

模板Template
SQL Server ProfilerSQL Server Profiler中,用来定义要在跟踪中收集的事件类和数据列的文件。In SQL Server ProfilerSQL Server Profiler, a file that defines the event classes and data columns to be collected in a trace.

跟踪表Trace table
SQL Server ProfilerSQL Server Profiler中,将跟踪保存到表时创建的表。In SQL Server ProfilerSQL Server Profiler, a table that is created when a trace is saved to a table.

使用数据列描述返回的事件Use Data Columns to Describe Returned Events

跟踪运行时,SQL 跟踪使用跟踪输出中的数据列来描述返回的事件。SQL Trace uses data columns in the trace output to describe events that are returned when the trace runs. 下表说明了 SQL Server ProfilerSQL Server Profiler 数据列(这些数据列与 SQL 跟踪使用的数据列相同),并指示默认情况下选择的列。The following table describes the SQL Server ProfilerSQL Server Profiler data columns, which are the same data columns as those used by SQL Trace, and indicates the columns that are selected by default.

数据列Data column 列号Column number 描述Description
ApplicationNameApplicationName 1010 客户端应用程序的名称,该客户端应用程序创建了指向 SQL ServerSQL Server实例的连接。The name of the client application that created the connection to an instance of SQL ServerSQL Server. 此列由该应用程序传递的值填充,而不是由程序名填充的。This column is populated with the values passed by the application and not the name of the program.
BigintData1BigintData1 5252 值(bigint 数据类型),取决于跟踪中指定的事件类。Value (bigint data type), which depends on the event class specified in the trace.
BigintData2BigintData2 5353 值(bigint 数据类型),取决于跟踪中指定的事件类。Value (bigint data type), which depends on the event class specified in the trace.
Binary DataBinary Data 22 依赖于跟踪中捕获的事件类的二进制值。The binary value dependent on the event class that is captured in the trace.
ClientProcessIDClientProcessID 99 由主机分配给正在运行客户端应用程序的进程的 ID。The ID assigned by the host computer to the process where the client application is running. 如果客户端提供了客户端进程 ID,则填充此数据列。This data column is populated if the client process ID is provided by the client.
ColumnPermissionsColumnPermissions 4444 表示是否已设置了列权限。Indicates whether a column permission was set. 可以分析语句文本来确定各列所应用权限的情况。You can parse the statement text to determine which permissions were applied to which columns.
CPUCPU 1818 事件使用的 CPU 时间(毫秒)。The amount of CPU time (in milliseconds) that is used by the event.
数据库 IDDatabase ID 33 由 USE database_name 语句指定的数据库的 ID;如果未对给定实例发出 USE database_name语句,则为默认数据库的 ID。The ID of the database specified by the USE database_name statement, or the ID of the default database if no USE database_namestatement has been issued for a given instance. SQL Server ProfilerSQL Server Profiler 数据列而且服务器可用,则 ServerName 将显示数据库名。displays the name of the database if the ServerName data column is captured in the trace and the server is available. 可使用 DB_ID 函数来确定数据库的值。Determine the value for a database by using the DB_ID function.
DatabaseNameDatabaseName 3535 正在运行用户语句的数据库的名称。The name of the database in which the user statement is running.
DBUserNameDBUserName 4040 客户端的 SQL ServerSQL Server 用户名。The SQL ServerSQL Server user name of the client.
DurationDuration 1313 事件的持续时间(微秒)。The duration (in microseconds) of the event.

服务器以微秒(百万分之一秒或 10-6 秒)为单位报告事件的持续时间,以毫秒(千分之一秒或 10-3 秒)为单位报告事件使用的 CPU 时间。The server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). SQL Server ProfilerSQL Server Profiler 图形用户界面默认以毫秒为单位显示 “持续时间” 列,但是当跟踪保存到文件或数据库表中时,将以微秒为单位写入“持续时间” 列值。The SQL Server ProfilerSQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.
EndTimeEndTime 1515 事件的结束时间。The time at which the event ended. 对指示事件开始的事件类(例如 SQL:BatchStartingSP:Starting)将不填充此列。This column is not populated for event classes that refer to an event that is starting, such as SQL:BatchStarting or SP:Starting.
错误Error 3131 给定事件的错误号。The error number of a given event. 通常是 sysmessages中存储的错误号。Often this is the error number stored in sysmessages.
EventClassEventClass 2727 捕获的事件类的类型。The type of event class that is captured.
EventSequenceEventSequence 5151 此事件的序列号。Sequence number for this event.
EventSubClassEventSubClass 2121 事件子类的类型,提供有关每个事件类的详细信息。The type of event subclass, which provides further information about each event class. 例如, Execution Warning 事件类的事件子类值代表执行警告的类型:For example, event subclass values for the Execution Warning event class represent the type of execution warning:

1 = 查询等候。1 = Query wait. 查询执行之前必须等待资源;例如,内存。The query must wait for resources before it can execute; for example, memory.

2 = 查询超时。查询等待执行所需资源时超时。2 = Query time-out. The query timed out while waiting for required resources to execute. 所有事件类都不填充此数据列。This data column is not populated for all event classes.
GUIDGUID 5454 依赖于跟踪中指定的事件类的 GUID 值。GUID value which depends on the event class specified in the trace.
FileNameFileName 3636 所修改的文件的逻辑名称。The logical name of the file that is modified.
HandleHandle 3333 一个整数,ODBC、OLE DB 或 DB-Library 使用它来协调服务器的执行情况。The integer used by ODBC, OLE DB, or DB-Library to coordinate server execution.
HostNameHostName 88 正在运行客户端程序的计算机的名称。The name of the computer on which the client is running. 如果客户端提供了主机名,则填充此数据列。This data column is populated if the host name is provided by the client. 若要确定主机名,请使用 HOST_NAME 函数。To determine the host name, use the HOST_NAME function.
IndexIDIndexID 2424 受事件影响的对象的索引 ID。The ID for the index on the object affected by the event. 若要确定对象的索引的 ID,请使用 sysindexes 系统表的 indid 列。To determine the index ID for an object, use the indid column of the sysindexes system table.
IntegerDataIntegerData 2525 跟踪中捕获的与事件类对应的整数值。The integer value dependent on the event class captured in the trace.
IntegerData2IntegerData2 5555 跟踪中捕获的与事件类对应的整数值。The integer value dependent on the event class captured in the trace.
IsSystemIsSystem 6060 指示该事件发生在系统进程还是用户进程上:Indicates whether the event occurred on a system process or a user process:

1 = 系统1 = system

0 = 用户0 = user
LineNumberLineNumber 55 包含存在错误的行的行号。Contains the number of the line that contains the error. 对于涉及 Transact-SQLTransact-SQL 语句的事件(如 SP:StmtStarting), LineNumber 包含存储过程或批查询中语句的行号。For events that involve Transact-SQLTransact-SQL statements, like SP:StmtStarting, the LineNumber contains the line number of the statement in the stored procedure or batch.
LinkedServerNameLinkedServerName 4545 链接服务器的名称。Name of the linked server.
LoginNameLoginName 1111 用户的登录名(SQL Server 安全登录名或 Windows 登录凭据,格式为“域/用户名”)。The name of the login of the user (either SQL Server security login or the Windows login credentials in the form of DOMAIN\Username).
LoginSidLoginSid 4141 已登录的用户的安全标识符 (SID)。The security identifier (SID) of the logged-in user. 可以在 master 数据库的 sys.server_principals 视图中找到此信息。You can find this information in the sys.server_principals view of the master database. 服务器中的每个登录名都具有唯一的 ID。Each login to the server has a unique ID.
MethodNameMethodName 4747 OLEDB 方法的名称。Name of the OLEDB method.
模式Mode 3232 一个整数,各种事件都使用它来描述事件要请求或已接收的状态。The integer used by various events to describe a state the event is requesting or has received.
NestLevelNestLevel 2929 一个整数,表示 @@NESTLEVEL 返回的数据。The integer that represents the data returned by @@NESTLEVEL.
NTDomainNameNTDomainName 77 用户所属的 Microsoft Windows 域。The Microsoft Windows domain to which the user belongs.
NTUserNameNTUserName 66 Windows 用户名。The Windows user name.
Exchange SpillObjectID 2222 系统分配的对象 ID。The system-assigned ID of the object.
ObjectID2ObjectID2 5656 相关对象或实体(如果存在)的 ID。The ID of the related object or entity, if available.
ObjectNameObjectName 3434 被引用对象的名称。The name of the object that is referenced.
ObjectTypeObjectType 2828 表示事件中涉及的对象类型的值。The value representing the type of the object involved in the event. 该值对应于 sysobjects 中的 type列。This value corresponds to the type column in sysobjects.
OffsetOffset 6161 语句在存储过程或批处理中的起始偏移量。The starting offset of the statement within the stored procedure or batch.
OwnerIDOwnerID 5858 仅限于锁事件。For lock events only. 拥有锁的对象的类型。The type of the object that owns a lock.
OwnerNameOwnerName 3737 对象所有者的数据库用户名。The database user name of the object owner.
ParentNameParentName 5959 该对象所在的架构的名称。The name of the schema in which the object resides.
权限Permissions 1919 表示所检查的权限类型的整型值。The integer value that represents the type of permissions checked. 值为:Values are:

1 = SELECT ALL1 = SELECT ALL

2 = UPDATE ALL2 = UPDATE ALL

4 = REFERENCES ALL4 = REFERENCES ALL

8 = INSERT8 = INSERT

16 = DELETE16 = DELETE

32 = EXECUTE (只针对过程)32 = EXECUTE (procedures only)

4096 = SELECT ANY (至少选择一列)4096 = SELECT ANY (at least one column)

8192 = UPDATE ANY8192 = UPDATE ANY

16384 = REFERENCES ANY16384 = REFERENCES ANY
ProviderNameProviderName 4646 OLEDB 访问接口的名称。Name of the OLEDB provider.
ReadsReads 1616 由服务器代表事件读取逻辑磁盘的次数。The number of read operations on the logical disk that are performed by the server on behalf of the event. 这些读取操作数包含在语句执行期间读取表和缓冲区的次数。These read operations include all reads from tables and buffers during the statement's execution.
RequestIDRequestID 4949 包含该语句的请求的 ID。ID of the request that contains the statement.
RoleNameRoleName 3838 正在启用的应用程序角色名。The name of the application role that is being enabled.
RowCountsRowCounts 4848 批处理中的行数。The number of rows in the batch.
ServerNameServerName 2626 正在跟踪的 SQL ServerSQL Server 实例的名称。The name of the instance of SQL ServerSQL Server that is being traced.
SessionLoginNameSessionLoginName 6464 发起会话的用户的登录名。The login name of the user who originated the session. 例如,如果您使用 SQL ServerSQL Server Login1 连接到 并以 Login2身份执行语句,则 SessionLoginName 将显示 Login1,而 LoginName 将显示 Login2For example, if you connect to SQL ServerSQL Server using Login1 and execute a statement as Login2, SessionLoginName displays Login1, while LoginName displays Login2. 此数据列将同时显示 SQL ServerSQL Server 登录名和 Windows 登录名。This data column displays both SQL ServerSQL Server and Windows logins.
SeveritySeverity 2020 异常错误事件的严重级别。The severity level of the exception event.
SourceDatabaseIDSourceDatabaseID 6262 存在该对象的源的数据库 ID。The ID of the database in which the source of the object exists.
SPIDSPID 1212 SQL ServerSQL Server 为客户端的相关进程分配的服务器进程 ID (SPID)。The server process ID (SPID) that is assigned by SQL ServerSQL Server to the process that is associated with the client.
SqlHandleSqlHandle 6363 基于即席查询文本或 SQL 对象的数据库和对象 ID 的 64 位哈希运算。64-bit hash based on the text of an ad hoc query or the database and object ID of an SQL object. 可以将该值传递到 sys.dm_exec_sql_text() 以检索关联的 SQL 文本。This value can be passed to sys.dm_exec_sql_text() to retrieve the associated SQL text.
StartTimeStartTime 1414 事件(如果有)的开始时间。The time at which the event started, when available.
StateState 3030 错误状态代码。Error state code.
成功Success 2323 表示事件是否成功。Represents whether the event was successful. 值包括:Values include:

1 = Success.1 = Success.

0 = Failure0 = Failure

例如, 1 表示权限检查成功,而 0 表示权限检查失败。For example, a 1 means a successful permissions check, and a 0 means a failed check.
TargetLoginNameTargetLoginName 4242 如果是针对登录的操作(例如,添加新的登录),这是所针对登录的名称。For actions that target a login, the name of the targeted login; for example, to add a new login.
TargetLoginSidTargetLoginSid 4343 如果是针对登录的操作(例如,添加新的登录),这是所针对登录的 SID。For actions that target a login, the SID of the targeted login; for example, to add a new login.
TargetUserNameTargetUserName 3939 如果是针对某个数据库用户的操作(例如,授予用户权限),这是该用户的名称。For actions that target a database user, the name of that user; for example, to grant permission to a user.
TextDataTextData 11 依赖于跟踪中捕获的事件类的文本值。The text value dependent on the event class that is captured in the trace. 但是,如果跟踪参数化查询,则不以 TextData 列中的数据值显示变量。However, if you trace a parameterized query, the variables are not displayed with data values in the TextData column.
事务 IDTransaction ID 44 系统为事务分配的 ID。The system-assigned ID of the transaction.
类型Type 5757 跟踪中捕获的与事件类对应的整数值。The integer value dependent on the event class captured in the trace.
WritesWrites 1717 由服务器代表事件写入物理磁盘的次数。The number of physical disk write operations that are performed by the server on behalf of the event.
XactSequenceXactSequence 5050 用于说明当前事务的标记。A token to describe the current transaction.

*默认情况下,对于所有事件,均填充这些数据列。*These data columns are populated by default for all events.

**有关 ObjectType 数据列的详细信息,请参阅 ObjectType 跟踪事件列**For more information about the ObjectType data column, see ObjectType Trace Event Column.

SQL 跟踪任务SQL Trace Tasks

任务说明Task Description 主题Topic
介绍如何使用 TRANSACT-SQL 存储过程创建和运行跟踪。Describes how to create and run traces using Transact-SQL stored procedures. 使用 Transact-SQL 存储过程创建和运行跟踪Create and Run Traces Using Transact-SQL Stored Procedures
介绍如何在 SQL Server 数据库引擎SQL Server Database Engine实例上使用存储过程创建手动跟踪。Describes how to create manual traces using stored procedures on an instance of the SQL Server 数据库引擎SQL Server Database Engine. 使用存储过程创建手动跟踪Create Manual Traces using Stored Procedures
介绍如何将跟踪结果保存到跟踪结果所写入的文件。Describes how to save trace results to the file where the trace results are written. 将跟踪结果保存到文件Save Trace Results to a File
介绍如何使用 temp 目录中的空间来提高访问跟踪数据的性能。Describes how to improve access to trace data by using space in the temp directory. 改进对跟踪数据的访问Improve Access to Trace Data
介绍如何使用存储过程创建跟踪。Describes how to use stored procedures to create a trace. 创建跟踪 (Transact-SQL)Create a Trace (Transact-SQL)
介绍如何使用存储过程创建只检索有关所需跟踪事件信息的筛选器。Describes how to use stored procedures to create a filter that retrieves only the information you need on an event being traced. 设置跟踪筛选器 (Transact-SQL)Set a Trace Filter (Transact-SQL)
介绍如何使用存储过程修改现有跟踪。Describes how to use stored procedures to modify an existing trace. 修改现有跟踪 (Transact-SQL)Modify an Existing Trace (Transact-SQL)
介绍如何使用内置函数查看保存的跟踪。Describes how to use built-in functions to view a saved trace. 查看保存的跟踪 (Transact-SQL)View a Saved Trace (Transact-SQL)
介绍如何使用内置函数查看跟踪筛选器信息。Describes how to use built-in functions to view trace filter information. 查看筛选器信息 (Transact-SQL)View Filter Information (Transact-SQL)
介绍如何使用存储过程删除跟踪。Describes how to use stored procedures to delete a trace. 删除跟踪 (Transact-SQL)Delete a Trace (Transact-SQL)
介绍如何将跟踪引起的性能损失降到最低。Describes how to minimize the performance cost incurred by a trace. 优化 SQL 跟踪Optimize SQL Trace
介绍如何筛选跟踪以将跟踪过程中的开销降到最低。Describes how to filter a trace to minimize the overhead that is incurred during a trace. 筛选跟踪Filter a Trace
介绍如何将跟踪收集的数据量降至最低。Describes how to minimize the amount of data that the trace collects. 限制跟踪文件和表的大小Limit Trace File and Table Sizes
介绍在 Microsoft SQL ServerSQL Server中计划跟踪的两种方法。Describes the two ways to schedule tracing in Microsoft SQL ServerSQL Server. 安排跟踪Schedule Traces

另请参阅See Also

SQL Server Profiler 模板和权限 SQL Server Profiler Templates and Permissions
SQL Server 管理对象 (SMO) 编程指南SQL Server Management Objects (SMO) Programming Guide