问题场景

在日常运维时，我们经常需要跟踪一下K/3出现的问题是否是数据库导致。例如不合理的索引、性能问题和错误的字段填充等等。这时候学习使用SQL Server profiler跟踪工具就至关重要了。本文以SQL SERVER 2016为例进行讲解。

学习目标

掌握SQL Server profiler工具的功能
能使用SQL Server profiler工具

工具介绍

1、工具的打开

1.1、可以在开始菜单找到SQL Server profiler，双击打开

1.2、打开SQL管理工具 SQL Server Management Studio。点击工具--SQL Server profiler。

1.3、打开的SQL Server profiler工具界面如图所示。

2、工具的说明

事件的说明

事件号	事件名称	说明
10	RPC:Completed	在完成了远程过程调用 (RPC) 时发生。
11	RPC:Starting	在启动了 RPC 时发生。
12	SQL:BatchCompleted	在完成了 Transact-SQL 批处理时发生。
13	SQL:BatchStarting	在启动了 Transact-SQL 批处理时发生。
14	Audit Login	在用户成功登录到 SQL Server 时发生。
15	Audit Logout	在用户从 SQL Server 注销时发生。
16	Attention	在发生需要关注的事件（如客户端中断请求或客户端连接中断）时发生。
17	ExistingConnection	检测在启动跟踪前连接到 SQL Server 的用户的所有活动。
18	Audit Server Starts and Stops	在修改 SQL Server 服务状态时发生。
20	Audit Login Failed	指示试图从客户端登录到 SQL Server 失败。
21	EventLog	指示已将事件记录到 Windows 应用程序日志中。
22	ErrorLog	指示已将错误事件记录到 SQL Server 错误日志中。
23	Lock:Released	指示已释放某个资源（如页）的锁。
24	Lock:Acquired	指示获取了某个资源（如数据页）的锁。
25	Lock:Deadlock	指示两个并发事务由于试图获得对方事务拥有的资源的不兼容锁而发生了相互死锁。
26	Lock:Cancel	指示已取消获取资源锁（例如，由于死锁）。
27	Lock:Timeout	指示由于其他事务持有所需资源的阻塞锁而使对资源（例如页）锁的请求超时。超时由 @@LOCK_TIMEOUT 函数确定，并可用 SET LOCK_TIMEOUT 语句设置。
28	Degree of Parallelism Event（7.0 插入）	在执行 SELECT、INSERT 或 UPDATE 语句之前发生。
33	Exception	指示 SQL Server 中出现了异常。
34	SP:CacheMiss	指示未在过程缓存中找到某个存储过程。
35	SP:CacheInsert	指示某个项被插入到过程缓存中。
36	SP:CacheRemove	指示从过程缓存中删除了某个项。
37	SP:Recompile	指示已重新编译存储过程。
38	SP:CacheHit	指示在过程缓存中找到了存储过程。
40	SQL:StmtStarting	在启动了 Transact-SQL 语句时发生。
41	SQL:StmtCompleted	在完成了 Transact-SQL 语句时发生。
42	SP:Starting	指示启动了存储过程。
43	SP:Completed	指示完成了存储过程。
44	SP:StmtStarting	指示已开始执行存储过程中的 Transact-SQL 语句。
45	SP:StmtCompleted	指示存储过程中的 Transact-SQL 语句已执行完毕。
46	Object:Created	指示 CREATE INDEX、CREATE TABLE 和 CREATE DATABASE 这样的语句已创建了一个对象。
47	Object:Deleted	指示已在 DROP INDEX 和 DROP TABLE 这样的语句中删除了对象。
50	SQL Transaction	跟踪 Transact-SQL BEGIN、COMMIT、SAVE 和 ROLLBACK TRANSACTION 语句。
51	Scan:Started	指示启动了表或索引扫描
52	Scan:Stopped	指示停止了表或索引扫描。
53	CursorOpen	指示 ODBC、OLE DB 或 DB-Library 在 Transact-SQL 语句中打开了一个游标。
54	TransactionLog	将事务写入事务日志时进行跟踪。
55	Hash Warning	指示未在缓冲分区进行的某一哈希操作（例如，哈希联接、哈希聚合、哈希 union 运算、哈希非重复）已恢复为替换计划。发生此事件的原因可能是递归深度、数据扭曲、跟踪标记或位计数。
58	Auto Stats	指示发生了自动更新索引统计信息。
59	Lock:Deadlock Chain	为导致死锁的每个事件而生成。
60	Lock:Escalation	指示较细粒度的锁转换成了较粗粒度的锁（例如，页锁升级或转换为 TABLE 或 HoBT 锁）。
61	OLE DB Errors	指示发生了 OLE DB 错误。
67	Execution Warnings	指示在执行 SQL Server 语句或存储过程期间发生的任何警告。
68	Showplan Text (Unencoded)	显示所执行 Transact-SQL 语句的计划树。
69	Sort Warnings	指示不适合内存的排序操作。不包括与创建索引有关的排序操作；只包括某查询内的排序操作（如 SELECT 语句中使用的 ORDER BY 子句）。
70	CursorPrepare	指示已准备了 ODBC、OLE DB 或 DB-Library 用于 Transact-SQL 语句的游标。
71	Prepare SQL	ODBC、OLE DB 或 DB-Library 已准备好了一个或多个要使用的 Transact-SQL 语句。
72	Exec Prepared SQL	ODBC、OLE DB 或 DB-Library 已执行了一个或多个准备好的 Transact-SQL 语句。
73	Unprepare SQL	ODBC、OLE DB 或 DB-Library 已撤消（删除）了一个或多个准备好的 Transact-SQL 语句。
74	CursorExecute	执行了先前由 ODBC、OLE DB 或 DB-Library 为 Transact-SQL 语句准备的游标。
75	CursorRecompile	由 ODBC 或 DB-Library 为 Transact-SQL 语句打开的游标已直接重新编译或由于架构更改而重新编译。为 ANSI 和非 ANSI 游标触发。
76	CursorImplicitConversion	SQL Server 将 Transact-SQL 语句的游标从一种类型转换为另一种类型。为 ANSI 和非 ANSI 游标触发。
77	CursorUnprepare	ODBC、OLE DB 或 DB-Library 撤消（删除）了准备好的 Transact-SQL 语句的游标。
78	CursorClose	关闭了先前由 ODBC、OLE DB 或 DB-Library 为 Transact-SQL 语句打开的游标。
79	Missing Column Statistics	可能曾经对优化器有用的列统计信息不可用。
80	Missing Join Predicate	正在执行没有联接谓词的查询。这可能导致长时间运行查询。
81	Server Memory Change	SQL Server 内存的使用量已增加或减少了 1 MB 或最大服务器内存的 5%（两者中较大者）。
82-91	User Configurable (0-9)	用户定义的事件数据。
92	Data File Auto Grow	指示服务器已自动扩展了数据文件。
93	Log File Auto Grow	指示服务器已自动扩展了日志文件。
94	Data File Auto Shrink	指示服务器已自动收缩了数据文件。
95	Log File Auto Shrink	指示服务器已自动收缩了日志文件。
96	Showplan Text	显示来自查询优化器的 SQL 语句的查询计划树。请注意，TextData 列不包含此事件的显示计划。
97	Showplan All	显示查询计划，并显示已执行的 SQL 语句的完整编译时详细信息。请注意，TextData 列不包含此事件的显示计划。
98	Showplan Statistics Profile	显示查询计划，并显示已执行的 SQL 语句的完整运行时详细信息。请注意，TextData 列不包含此事件的显示计划。
100	RPC Output Parameter	生成每个 RPC 的参数的输出值。
108	Audit Add Login to Server Role Event	在从固定服务器角色添加或删除登录时发生；针对 sp_addsrvrolemember 和 sp_dropsrvrolemember。
112	Audit App Role Change Password Event	在更改应用程序角色的密码时发生。
113	Audit Statement Permission Event	在使用语句权限（如 CREATE TABLE）时发生。
114	Audit Schema Object Access Event	在成功或未成功使用了对象权限（如 SELECT）时发生。
115	Audit Backup/Restore Event	在发出 BACKUP 或 RESTORE 命令时发生。
116	Audit DBCC Event	在发出 DBCC 命令时发生。
117	Audit Change Audit Event	在修改审核跟踪时发生。
118	Audit Object Derived Permission Event	在发出 CREATE、ALTER 和 DROP 对象命令时发生。
119	OLEDB Call Event	为分布式查询和远程存储过程调用 OLE DB 访问接口时发生。
120	OLEDB QueryInterface Event	为分布式查询和远程存储过程调用 OLE DB QueryInterface 时发生。
121	OLEDB DataRead Event	对 OLE DB 访问接口调用数据请求时发生。
122	Showplan XML	在执行 SQL 语句时发生。包括该事件可以标识 Showplan 运算符。每个事件都存储在格式正确的 XML 文档中。请注意，此事件的 Binary 列包含已编码的显示计划。使用 SQL Server Profiler 可打开跟踪并查看显示计划。
123	SQL:FullTextQuery	执行全文查询时发生。
124	Broker:Conversation	报告 Service Broker 会话的进度。
125	Deprecation Announcement	使用将从 SQL Server 的未来版本中删除的功能时发生。
126	Deprecation Final Support	使用将从 SQL Server 的下一个主版本中删除的功能时发生。
127	Exchange Spill Event	在 tempdb 数据库临时写入并行查询计划中的通信缓冲区时发生。
128	Audit Database Management Event	创建、更改或删除数据库时发生。
129	Audit Database Object Management Event	对数据库对象（如架构）执行 CREATE、ALTER 或 DROP 语句时发生。
130	Audit Database Principal Management Event	创建、更改或删除数据库的主体（如用户）时发生。
131	Audit Schema Object Management Event	创建、更改或删除服务器对象时发生。
132	Audit Server Principal Impersonation Event	服务器范围中发生模拟（如 EXECUTE AS LOGIN）时发生。
133	Audit Database Principal Impersonation Event	数据库范围中发生模拟（如 EXECUTE AS USER 或 SETUSER）时发生。
134	Audit Server Object Take Ownership Event	服务器范围中的对象的所有者发生更改时发生。
135	Audit Database Object Take Ownership Event	数据库范围中的对象的所有者发生更改时发生。
136	Broker:Conversation Group	Service Broker 创建新的会话组或删除现有会话组时发生。
137	Blocked Process Report	进程被阻塞的时间超过了指定的时间时发生。不包括系统进程或正在等待未发现死锁的资源的进程。请使用 sp_configure 来配置生成报表时的阈值和频率。
138	Broker:Connection	报告 Service Broker 管理的传输连接的状态。
139	Broker:Forwarded Message Sent	Service Broker 转发消息时发生。
140	Broker:Forwarded Message Dropped	Service Broker 删除用于转发的消息时发生。
141	Broker:Message Classify	Service Broker 确定消息的路由时发生。
142	Broker:Transmission	指示在 Service Broker 传输层中发生了错误。错误号和状态值指示了错误源。
143	Broker:Queue Disabled	指示检测到有害消息，这是由于在 Service Broker 队列中有五个连续的事务回滚。该事件包含数据库 ID 和包含有害消息的队列的队列 ID。
146	Showplan XML Statistics Profile	在执行 SQL 语句时发生。标识 Showplan 运算符，并显示完整的编译时数据。请注意，此事件的 Binary 列包含已编码的显示计划。使用 SQL Server Profiler 可打开跟踪并查看显示计划。
148	Deadlock Graph	取消获取锁的尝试时发生，这是因为该尝试是死锁的一部分，并且被选为死锁牺牲品。提供死锁的 XML 说明。
149	Broker:Remote Message Acknowledgement	Service Broker 发送或收到消息确认时发生。
150	Trace File Close	跟踪文件在回滚期间关闭时发生。
152	Audit Change Database Owner	使用 ALTER AUTHORIZATION 更改数据库的所有者，并且检查执行该操作的权限时发生。
153	Audit Schema Object Take Ownership Event	使用 ALTER AUTHORIZATION 来将所有者分配给对象，并且检查执行该操作的权限时发生。
155	FT:Crawl Started	全文爬网（填充）开始时发生。用于检查工作线程任务是否拾取了爬网请求。
156	FT:Crawl Stopped	全文爬网（填充）停止时发生。爬网成功完成或发生错误时停止。
157	FT:Crawl Aborted	在全文爬网过程中遇到异常时发生。通常导致全文爬网停止。
158	Audit Broker Conversation	报告与 Service Broker 对话安全性相关的审核消息。
159	Audit Broker Login	报告与 Service Broker 传输安全性相关的审核消息。
160	Broker:Message Undeliverable	Service Broker 无法保留收到的消息时发生，该消息应当已传递给某个服务。
161	Broker:Corrupted Message	Service Broker 收到损坏的消息时发生。
162	User Error Message	显示出现错误或异常时用户看到的错误消息。
163	Broker:Activation	队列监视器启动激活存储过程时，发送 QUEUE_ACTIVATION 通知时，或者队列监视器启动的激活存储过程退出时发生。
164	Object:Altered	数据库对象更改时发生。
165	Performance statistics	将经过编译的查询计划第一次缓存、重新编译或从计划缓存中删除时发生。
166	SQL:StmtRecompile	发生语句级别的重新编译时发生。
167	Database Mirroring State Change	镜像数据库的状态更改时发生。
168	Showplan XML For Query Compile	编译 SQL 语句时发生。显示完整的编译时数据。请注意，此事件的 Binary 列包含已编码的显示计划。使用 SQL Server Profiler 可打开跟踪并查看显示计划。
169	Showplan All For Query Compile	编译 SQL 语句时发生。显示完整的编译时数据。用于标识 Showplan 运算符。
170	Audit Server Scope GDR Event	指示在服务器范围中发生了权限的授予、拒绝或撤消事件（如创建登录）。
171	Audit Server Object GDR Event	指示发生了对架构对象（如表或函数）的授予、拒绝或撤消事件。
172	Audit Database Object GDR Event	指示发生了对数据库对象（如程序集和架构）的授予、拒绝或撤消事件。
173	Audit Server Operation Event	使用了安全审核操作（如使用了更改设置、资源、外部访问或授权）时发生。
175	Audit Server Alter Trace Event	检查语句的 ALTER TRACE 权限时发生。
176	Audit Server Object Management Event	创建、更改或删除服务器对象时发生。
177	Audit Server Principal Management Event	创建、更改或删除了服务器主体时发生。
178	Audit Database Operation Event	发生数据库操作（如检查或订阅查询通知）时发生。
180	Audit Database Object Access Event	访问数据库对象（如架构）时发生。
182	TM: Begin Tran completed	BEGIN TRANSACTION 请求完成时发生。
181	TM: Begin Tran starting	BEGIN TRANSACTION 请求开始时发生。
183	TM: Promote Tran starting	PROMOTE TRANSACTION 请求开始时发生。
184	TM: Promote Tran completed	PROMOTE TRANSACTION 请求完成时发生。
185	TM: Commit Tran starting	COMMIT TRANSACTION 请求开始时发生。
186	TM: Commit Tran completed	COMMIT TRANSACTION 请求完成时发生。
187	TM: Rollback Tran starting	ROLLBACK TRANSACTION 请求开始时发生。
188	TM: Rollback Tran completed	ROLLBACK TRANSACTION 请求完成时发生。
189	Lock:Timeout (timeout > 0)	对资源（如页）的锁请求超时时发生。
190	Progress Report: Online Index Operation	报告生成进程正在运行时，联机索引生成操作的进度。
191	TM: Save Tran starting	SAVE TRANSACTION 请求开始时发生。
192	TM: Save Tran completed	SAVE TRANSACTION 请求完成时发生。
193	Background Job Error	后台作业不正常终止时发生。
194	OLEDB Provider Information	分布式查询运行并收集对应于提供程序连接的信息时发生。
195	Mount Tape	收到磁带装入请求时发生。
196	Assembly Load	发生加载 CLR 程序集的请求时发生。
198	XQuery Static Type	执行 XQuery 表达式时发生。此事件类提供静态类型的 XQuery 表达式。
199	QN: subscription	无法订阅查询注册时发生。 TextData 列包含事件的有关信息。
200	QN: parameter table	有关活动订阅的信息存储在内部参数表中。在创建或删除参数表时发生该事件类。通常，重新启动数据库时将创建或删除这些表。 TextData 列包含事件的有关信息。
201	QN: template	查询模板代表订阅查询的类。通常，除参数值以外，相同类中的查询是相同的。当新的订阅请求针对已存在的类 (Match)、新类 (Create) 或 Drop 类（指示清除没有活动订阅的查询类的模板）时，发生此事件类。 TextData 列包含事件的有关信息。
202	QN: dynamics	跟踪查询通知的内部活动。 TextData 列包含事件的有关信息。
213	Database Suspect Data Page	指示何时将某页添加到 msdb 的 suspect_pages 表。
214	CPU threshold exceeded	指示资源调控器检测到查询超过 CPU 阈值 (REQUEST_MAX_CPU_TIME_SEC) 的时间。
215	指示 LOGON 触发器或资源调控器分类器函数开始执行的时间。	指示 LOGON 触发器或资源调控器分类器函数开始执行的时间。
216	PreConnect:Completed	指示 LOGON 触发器或资源调控器分类器函数完成执行的时间。
217	Plan Guide Successful	指示 SQL Server 已成功为计划指南中包含的查询或批处理生成执行计划。
218	Plan Guide Unsuccessful	指示 SQL Server 无法为包含计划指南的查询或批处理生成执行计划。 SQL Server 尝试在不应用计划指南的情况下为此查询或批处理生成执行计划。无效的计划指南可能是导致此问题的原因。您可以通过使用 sys.fn_validate_plan_guide 系统函数验证该计划指南。

各个数据列的具体含义列举如下表，以供参考查阅。

数据列	列号	描述
Application Name1	10	创建与 SQL Server 实例的连接的客户端应用程序名。该列由应用程序传递的值填充，而不是由所显示的程序名填充。
Binary Data	2	与在跟踪中捕获的事件类相关的二进制值。
ClientProcessID1	9	由主机计算机分配给进程的 ID，在该进程中客户应用程序正在运行。如果客户端提供客户端进程 ID，则填充此数据列。
Column Permissions	44	表明是否已设置了列权限。分析语句文本，以确定将哪些权限应用到了哪些列。
CPU	18	事件所使用的 CPU 时间总计（以毫秒为单位）。
Database ID1	3	USE database 语句所指定的数据库 ID，如果没有对给定实例发出过 USE database 语句，则是默认数据库。如果在跟踪内捕获 Server Name数据列且服务器可用，则 SQL 事件探查器将显示数据库名。通过使用 DB_ID 函数确定数据库的值。
DatabaseName	35	正在运行用户语句的数据库的名称。
DBUserName1	40	客户端的 SQL Server 用户名。
Duration	13	事件所花费的时间总计（以毫秒为单位）。
End Time	15	事件结束时的时间。启动事件的事件类（如 SQL:BatchStarting 或 SP:Starting）的该列不填充。
Error	31	给定事件的错误号。通常是存储在 sysmessages 中的错误号。
EventClass1	27	捕获的事件类类型。
EventSubClass1	21	事件子类的类型，提供有关每个事件类的进一步信息。例如，Execution Warning 事件类的事件子类值代表执行警告的类型：
1 = 查询等待。查询必须等待资源（如内存）才能执行。
2 = 查询超时。查询在等待执行所需的资源时超时。所有事件类的该数据列均不填充。
FileName	36	所修改的文件的逻辑名称。
Handle	33	ODBC、OLE DB 或 DB-Library 所用的整数，用以协调服务器的执行。
Host Name1	8	正运行客户端的计算机名。如果客户端提供主机名，则填充此数据列。若要确定主机名，请使用 HOST_NAME 函数。
Index ID	24	受事件影响的对象上的索引 ID。若要确定对象的索引 ID，请使用 sysindexes 系统表的 indid 列。
Integer Data	25	与在跟踪中捕获的事件类相关的整型值。
LoginName	11	用户的登录名（SQL Server 安全登录或 Microsoft Windows® 登录凭据，格式为 DOMAIN\Username）。
LoginSid1	41	登录用户的安全标识号 (SID)。可以在 master 数据库的 sysxlogins 表中找到该信息。对于服务器中的每个登录，SID 是唯一的。
Mode	32	不同事件所用的整数，用于描述事件已接收或要请求的状态。
NestLevel	29	表示 @@NESTLEVEL 所返回的数据的整数。
NT Domain Name1	7	用户所属的 Microsoft Windows NT® 4.0 或 Windows 2000 域。
NT User Name1	6	Windows NT 4.0 或 Windows 2000 用户名。
Object ID	22	系统分配的对象 ID。
ObjectName	34	引用的对象名。
ObjectType	28	表示事件中涉及的对象类型的值。该值对应于 sysobjects 中的 type 列。
Owner Name	37	对象所有者的数据库用户名称。
Permissions	19	表示所检查的权限类型的整型值。取值为：
1 = SELECT ALL
2 = UPDATE ALL
4 = REFERENCES ALL
8 = INSERT
16 = DELETE
32 = EXECUTE（仅限于过程）
4096 = SELECT ANY （至少一列）
8192 = UPDATE ANY
16384 = REFERENCES ANY
Reads	16	服务器代表事件执行的逻辑磁盘读取数。
RoleName	38	要启用的应用程序角色名。
Server Name1	26	跟踪的 SQL Server 实例名。
Severity	20	异常错误的严重级别。
SPID1	12	SQL Server 指派的与客户端相关的服务器进程 ID。
Start Time1	14	启动事件的时间（可用时）。
State	30	等同于错误状态代码。
Success	23	表示事件是否成功。取值包括：
1 = 成功。
0 = 失败
例如，1 表示权限检查成功，0 表示该检查失败。
TargetLoginName	42	对于以登录为目标的操作（例如，添加新登录），是目标登录的名称。
TargetLoginSid	43	对于以目标为登录的操作（例如，添加新登录），是目标登录的 SID。
TargetUserName	39	对于以数据库用户为目标的操作（例如授予用户权限），是该用户的名称。
TextData	1	与跟踪内捕获的事件类相关的文本值。但是，如果正在跟踪参数化查询，则不以 TextData 列中的数据值显示变量。
Transaction ID	4	系统分配的事务 ID。
Writes	17	服务器代表事件执行的物理磁盘写入数。

3、工具的使用

常规窗口：

设置跟踪的名称和，勾上保持到文件，选择好文件存放的路径，注意设置最大文件大小根据需要进行设置，也可以设置跟踪停止的时间。

事件选择窗口：

在该图中设置需要跟踪的SQL Server事件类。主要用来跟踪SQL语句和存储过程的事件，通常情况下只要设置TSQL事件类的SQL:BatchCompleted，SQL:StmtCompleted事件和存储过程事件类RPC:Completed、SP:Completed，SP:stmtCompleted事件即可。

系统默认如下：

我们要将我们需要的事件勾上，把不需要的事件不勾。如图所示：将“显示所有事件”勾上，将显示我们需要的事件。

选择完成，取消勾选“显示所有事件”：

对选定的数据库服务器进行一定事件的跟踪，然后另存为跟踪文件，如下图：
可以对数据列：CPU（事件所使用的CPU事件，毫秒为单位），Reads（服务器代表事件执行的逻辑磁盘读取数），Writes（服务器代表事件执行的物理磁盘写入数），Duration（事件所花费的事件总计，毫秒为单位）进行查看，查找读取或写入物理磁盘次数多的操作，耗时比较多的操作。为查找性能问题提供有力的证据，对性能优化也具有参考的价值。

右键点击列名，可以取消选择列，该列将取消。

先选择要跟踪的账套对应的数据库实体。

选择Duration的编辑筛选器，将其设置为大于等于200。为了不使跟踪文件过大，在筛选条件上选择Duration>=200的事件，因为执行周期很短的SQL不是我们在性能分析中关注的重点对象，同时全部SQL都跟踪会很多。

最后选择的列如下：

设置完上面的信息后，点击“运行”按钮。如果不设置Duration>=200，将开始的界面如下：

总结

如果发现某一段时间有规律的发生性能问题，需要事先跟踪，跟踪一段时间，而不是问题已经发生了再去跟踪。跟踪一段时间后，约1-2小时保存一次，否则跟踪文件太大，耗用太多的内存会影响服务器的内存。在保存时要选择保存为跟踪文件（默认是这样），保存为其他格式会丢失分析性能的数据，以前有分支机构的人员就是保存为SQL脚本发给研发，这对分析问题毫无价值。

SQL Profile跟踪工具的使用

恭喜您！