基于服务端的SQL Trace使用进阶第六篇：如何导入跟踪数据

基于服务端的基于服务端的基于服务端的基于服务端的SQLSQLSQLSQL TraceTraceTraceTrace使用进阶第六篇：如何导入跟踪数据使用进阶第六篇：如何导入跟踪数据使用进阶第六篇：如何导入跟踪数据使用进阶第六篇：如何导入跟踪数据 相信对于 SQLTrace，朋友们已经不陌生了，不管是在应用程序调优，还是故障排除与性能优化中，用的都是比较多的。尽管如此，如何合 理的使用 SQL Trace，依然是个很大的问题，因为稍不注意，就是的结果适得其反，严重的消耗数据库服务器的资源，加大服务器的压力。 为了使得大家更好的理解和使用 SQL Trace，我们本系列文章来自 Stairway toServer-side Tracing，然后经过我们团队的理解和整理发布 在 agilesharp，希望对广大的技术朋友在如何使用 SQL Trace上有所帮助。 前面的文章中我们介绍了创建和收集跟踪数据，在收集了大量的跟踪数据后，首选的 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 是使用 T-SQL语句来分析这些跟踪数据。通过写 查询来进行筛选、聚集，分析跟踪数据中我们感兴趣的各种事件。本篇文章为大家介绍如何将基于服务端的跟踪文件或者使用 Profiler客户 端工具收集的跟踪数据导入到 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 中，为后续分析使用。 将服务端的跟踪文件导入表中 基于服务端的跟踪不允许直接向表里写入跟踪数据。这项限制确保在收集数据时不需要访问数据库引擎，就不会带来额外的开销。将正在跟 踪或跟踪结束后生成的跟踪文件导入到临时表中是非常容易的。 下面让我们回顾一下表值函数 fn_trace_gettable。这个函数有2个参数，跟踪文件的路径和滚动更新文件对应的值，来确定需要读取的跟踪 文件。函数会返回包含所有跟踪事件列的记录集。在结果集中没有在跟踪文件中收集的数据列会显示 NULL。可以使用 fn_trace_gettable 函数做为源，使用 SELECT…INTO语句创建一个新表并导入跟踪数据。下面的语句将一个单独的跟踪文件导入到表中，可以将第2个参数的 数值修改为滚动更新文件对应的值来导入多个跟踪文件，也可以通过指定参数值为 DEFAULT来实现多个文件的导入。 1 SELECT * 2 INTO dbo.ImportedTraceData 3 FROM FN_TRACE_GETTABLE(N'C:\TraceFiles\MyTrace\MyTrace.trc', 1); 复制代码 fn_trace_gettable函数可以被指定为 INSERT…SELECT语句的源向一个存在的表中新增数据。下面的语句创建一个新表，并将这个表的数 据列定义为 fn_trace_gettable函数返回的所有数据列，使用 INSERT…SELECT…FROMfn_trace_gettable语句将跟踪数据导入到这个通 用的表中。函数的第2个参数指定为 DEFAULT，可以将所有的滚动跟踪文件一次性导入。使用 INSERT…SELECT语句也可以将其他跟踪文 件导入到同一个表中。 4 CREATE TABLE [dbo].[ServerSideTraceData] 5 ( 6 [TextData] [nvarchar](MAX) NULL , 7 [BinaryData] [varbinary](MAX) NULL , 8 [DatabaseID] [int] NULL , 9 [TransactionID] [bigint] NULL , 10 [LineNumber] [int] NULL , 11 [NTUserName] [nvarchar](256) NULL , 12 [NTDomainName] [nvarchar](256) NULL , 13 [HostName] [nvarchar](256) NULL , 14 [ClientProcessID] [int] NULL , 15 [ApplicationName] [nvarchar](256) NULL , 16 [LoginName] [nvarchar](256) NULL , 17 [SPID] [int] NULL , 18 [Duration] [bigint] NULL , 19 [StartTime] [datetime] NULL , 20 [EndTime] [datetime] NULL , 21 [Reads] [bigint] NULL , 22 [Writes] [bigint] NULL , 23 [CPU] [int] NULL , 24 [Permissions] [bigint] NULL , 25 [Severity] [int] NULL , 26 [EventSubClass] [int] NULL , 27 [ObjectID] [int] NULL , 28 [Success] [int] NULL , 29 [IndexID] [int] NULL , 30 [IntegerData] [int] NULL , 31 [ServerName] [nvarchar](256) NULL , 32 [EventClass] [int] NULL , 33 [ObjectType] [int] NULL , 34 [NestLevel] [int] NULL , 35 [State] [int] NULL , 36 [Error] [int] NULL , 37 [Mode] [int] NULL , 38 [Handle] [int] NULL , 39 [ObjectName] [nvarchar](256) NULL , 40 [DatabaseName] [nvarchar](256) NULL , 41 [FileName] [nvarchar](256) NULL , 42 [OwnerName] [nvarchar](256) NULL , 43 [RoleName] [nvarchar](256) NULL , 44 [TargetUserName] [nvarchar](256) NULL , 45 [DBUserName] [nvarchar](256) NULL , 46 [LoginSid] [varbinary](MAX) NULL , 47 [TargetLoginName] [nvarchar](256) NULL , 48 [TargetLoginSid] [varbinary](MAX) NULL , 49 [ColumnPermissions] [int] NULL , 50 [LinkedServerName] [nvarchar](256) NULL , 51 [ProviderName] [nvarchar](256) NULL , 52 [MethodName] [nvarchar](256) NULL , 53 [RowCounts] [bigint] NULL , 54 [RequestID] [int] NULL , 55 [XactSequence] [bigint] NULL , 56 [EventSequence] [bigint] NULL , 57 [BigintData1] [bigint] NULL , 58 [BigintData2] [bigint] NULL , 59 [GUID] [uniqueidentifier] NULL , 60 [IntegerData2] [int] NULL , 61 [ObjectID2] [bigint] NULL , 62 [Type] [int] NULL , 63 [OwnerID] [int] NULL , 64 [ParentName] [nvarchar](256) NULL , 65 [IsSystem] [int] NULL , 66 [Offset] [int] NULL , 67 [SourceDatabaseID] [int] NULL , 68 [SqlHandle] [varbinary](MAX) NULL , 69 [SessionLoginName] [nvarchar](256) NULL , 70 [PlanHandle] [varbinary](MAX) NULL , 71 [GroupID] [int] NULL 72 ) ; 73 74 INSERT INTO [dbo].[ServerSideTraceData] 75 SELECT * 76 FROM FN_TRACE_GETTABLE(' C:\TraceFiles\MyTrace\ MyTrace.trc ', 77 DEFAULT) ; 复制代码 fn_trace_gettable函数返回的数据列有些是image或ntext的数据类型，像TextData,BinaryData, LoginSid, TargetLoginSid, SqlHandle， PlanHandle 这些字段。上面的 CREATE TABLE 的定义中为了更方面查询，将这些字段的数据列修改为 nvarchar(MAX)和 varbinary(MAX)。 本篇文章中引用的前2个脚本是按照 SQL 2008中 fn_trace_gettable 函数返回的表结构，表定义了66个字段。SQL Server 2005中有65个 可选的跟踪数据列，GroupID是 SQLServer 2008中为了资源管理器新增的一个列。66个字段看起来非常多，为了简化，可以同时在 INSERT 和 SELECT 语句中仅指定需要的数据列，也可以按需要将定义表中的其他数据列删掉。不过做之前考虑到以后可能需要将其他的跟踪数据导 入到相同表中，并且可能使用同一段定义好的脚本来查看和管理跟踪数据。这些情况下尽管表中有一些多余的字段，但是定义一个 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 化的 跟踪数据表结构更方便维护管理。 fn_trace_gettable 函数可以读取所有的 SQL Trace 文件，需要 SQL Server 服务账号权限。读取的源文件可以是之前 Profiler 客户端工具 保存的跟踪文件，当前 SQL Server 实例上正在运行的跟踪或者是当前实例、其他实例上生成的跟踪文件。远程的跟踪文件可以通过UNC 名来访问（例如：“\\SqlServerName\ShareName\TraceFileName.trc”），这样不需要将文件复制到本机后再导入。 将将将将ProfilerProfilerProfilerProfiler客户端的跟踪数据导入表中客户端的跟踪数据导入表中客户端的跟踪数据导入表中客户端的跟踪数据导入表中 我们知道在使用 Profiler 客户端工具时，“跟踪属性”页选择“保存到表”，可以直接将跟踪数据写入到一个表中。初步看来，这是一个很 方便的方法，节省了再导入表的步骤。但是实际上当收集大量事件数据时，通过 Profiler 客户端直接将跟踪数据写入表中会带来性能影响。 Profiler 需要从生成的 SQL Trace 记录集中一行一行读取，然后几乎实时的将数据显示在客户端界面上并写入目标表。Profiler 是一行一行 插入数据而不是基于集合或批量的操作，这样处理会带来大量性能的开销，所以建议不要通过Profiler 直接将数据写入表，除非在不需要考 虑性能的场景下（比如在活动进程很少的测试环境）。 如果不使用 Profiler 直接保存到表的功能，还可以使用另外一种方法，在跟踪结束后将跟踪数据写入到表中。通过Profiler 的菜单选项，“文 件——>另存为——>跟踪表”。接下来 Profiler 会提示我们选择跟踪数据存储的服务器，数据库，架构及表。注意，如果选取一个已经存 在的表，Profiler 会先删除这个表然后再创建一个同名的新表，并不会直接向已存在的表中新增数据。这种类似“保存到表”的方法，Profiler 每次插入一行数据到表中，所以当将一个大的跟踪文件导入表时就会花费很多时间。从性能和时间上考虑，大的跟踪文件更适合使用服务端 的跟踪数据导入方法。 Profiler 创建的表的结构只包括了那些在跟踪中定义的列，并不包括 fn_trace_gettable 函数返回的所有数据列。如果我们想将不同跟踪的 数据合并到一个表中，当表的结构不同时，就需要为 Profiler 创建的不同表结构的不同列，写对应的查询来匹配。 除了上面说到的使用 Profiler 客户端将跟踪数据直接写到表中，我们还可以先保存为跟踪文件，然后使用 fn_trace_gettable 函数读取跟踪 文件再导入表中，就像当做一个基于服务端的跟踪来导入。这种方法的优点是可以向一个存在的表中新增数据，而不用删除之前收集的数据。 由于 fn_trace_gettable 函数与 Profiler 客户端不同，必须运行在 SQL Server 服务器上，所以需要先将 Profiler 生成的跟踪文件复制到服 务器上，或是设置一个 SQL Server 服务账号有权限访问的共享 目录 工贸企业有限空间作业目录特种设备作业人员作业种类与目录特种设备作业人员目录1类医疗器械目录高值医用耗材参考目录 ，然后为 fn_trace_gettable 函数的参数指定UNC路径来访问。 使用 fn_trace_gettable 函数的一个明显的好处是，通过它来导入Profiler 的跟踪数据到同一个标准化结构的表中，不用为了导入数据而去 研究每个不同跟踪对应的不同数据列。 通过定制的查询语句来分析导入的跟踪数据通过定制的查询语句来分析导入的跟踪数据通过定制的查询语句来分析导入的跟踪数据通过定制的查询语句来分析导入的跟踪数据 针对导入跟踪数据定制的查询语句是一个很好的技术，它让分析大数据量的跟踪数据变得非常容易。通过它可以进行故障排除、容量规划以 及负载的分析。下面用一个实际的 例子 48个音标大全附带例子子程序调用编程序例子方差分析的例子空间拓扑关系例子方差不存在的例子 来演示在生产环境下，通过对一个典型工作日的跟踪，来分析应用程序是如何使用数据库的。这种开 放型的分析是一种很好的方法，让我们熟悉应用程序对服务器的使用情况。 下面是一个存储过程，创建了基于服务端的跟踪，只定义了 RPC:Completed 和 SQL:BatchCompleted 两个事件，并没有定义筛选器。我 们在第2篇文章中也讨论过基于服务端跟踪的存储过程。大家可以参见文章（http://www.agilesharp.com/showtopic-180.aspx） 不建议大家在生产环境下运行一个像这样没有筛选器的跟踪，尤其是当服务器容量紧张或是高并发请求的情况下（比如每秒几千的请求量）。 除非是服务器有足够的剩余容量来承担额外的负载。我的测试环境是在一台OLTP 服务器上，通过性能监视器可以看到每秒有几百的批处理 请求，峰值大约在一千。开启上面定义好的跟踪，并没有太明显的性能影响。 1 CREATE PROCEDURE dbo.StartBatchAndRpcCompletedTrace 2 AS 3 declare @rc int 4 declare @TraceID int 5 declare @maxfilesize bigint 6 set @maxfilesize = 100 7 declare @stoptime datetime 8 --stop trace 9 hours from now 9 set @stoptime = DATEADD(hour, 9, GETDATE()) 10 exec @rc = sp_trace_create @TraceID output, 2, N'C:\TraceFiles\BatchAndRpcCompleted', @maxfilesize, @stoptime, 100 11 if (@rc != 0) goto finish 12 declare @on bit 13 set @on = 1 14 --RPC:Completed columns 15 exec sp_trace_setevent @TraceID, 10, 15, @on -- EndTime 16 exec sp_trace_setevent @TraceID, 10, 31, @on -- Error 17 exec sp_trace_setevent @TraceID, 10, 8, @on -- HostName 18 exec sp_trace_setevent @TraceID, 10, 16, @on -- Reads 19 exec sp_trace_setevent @TraceID, 10, 48, @on -- RowCounts 20 exec sp_trace_setevent @TraceID, 10, 1, @on -- TextData 21 exec sp_trace_setevent @TraceID, 10, 9, @on -- ClientProcessID 22 exec sp_trace_setevent @TraceID, 10, 10, @on -- ApplicationName 23 exec sp_trace_setevent @TraceID, 10, 18, @on -- CPU 24 exec sp_trace_setevent @TraceID, 10, 11, @on -- LoginName 25 exec sp_trace_setevent @TraceID, 10, 12, @on -- SPID 26 exec sp_trace_setevent @TraceID, 10, 13, @on -- Duration 27 exec sp_trace_setevent @TraceID, 10, 14, @on -- StartTime 28 --SQL:BatchCompleted columns 29 exec sp_trace_setevent @TraceID, 12, 15, @on -- EndTime 30 exec sp_trace_setevent @TraceID, 12, 31, @on -- Error 31 exec sp_trace_setevent @TraceID, 12, 8, @on -- HostName 32 exec sp_trace_setevent @TraceID, 12, 16, @on -- Reads 33 exec sp_trace_setevent @TraceID, 12, 48, @on -- RowCounts 34 exec sp_trace_setevent @TraceID, 12, 1, @on -- TextData 35 exec sp_trace_setevent @TraceID, 12, 9, @on -- ClientProcessID 36 exec sp_trace_setevent @TraceID, 12, 10, @on -- ApplicationName 37 exec sp_trace_setevent @TraceID, 12, 18, @on -- CPU 38 exec sp_trace_setevent @TraceID, 12, 11, @on -- LoginName 39 exec sp_trace_setevent @TraceID, 12, 12, @on -- SPID 40 exec sp_trace_setevent @TraceID, 12, 13, @on -- Duration 41 exec sp_trace_setevent @TraceID, 12, 14, @on -- StartTime 42 -- Set the trace status to start 43 exec @rc = sp_trace_setstatus @TraceID, 1 44 finish: 45 return @rc 46 go 复制代码 为了实现自动化，使用 SQL Server 代理作业8:00点自动运行这个存储过程。跟踪的结束时间参数@stoptime定义为开始运行的9小时后， 可以计算出上午8:00点开启的跟踪，会在下午5:00点自动结束。上面存储过程中定义了单个滚动更新文件的最大空间为100MB，滚动的文 件数量最多100个。通过这2个参数计算出定义的跟踪会占用10GB 的存储空间。在目前服务器的负载下10GB 大概可以容纳700万的查询。 实际的空间需求取决于 TextDate 和 ApplicationName等数据列的长度以及收集的语句数量。 跟踪结束后，先使用文章最上面的脚本在测试环境下创建一个标准化的表。然后用下面的脚本导入数据。 1 DECLARE 2 @RolloverFileNumber int = 1 3 ,@TotalRolloverFiles int = 99 --specify actual number of rollover files 4 ,@TraceFileName varchar(255); 5 --import initial file 6 INSERT INTO dbo.ServerSideTraceData 7 SELECT * 8 FROM FN_TRACE_GETTABLE('C:\TraceFiles\BatchAndRpcCompleted.trc',1); 9 RAISERROR('%d rows imported from initial trace file', 0, 1, @@ROWCOUNT) WITH NOWAIT; 10 --import each rollover file 11 WHILE @RolloverFileNumber <= @TotalRolloverFiles 12 BEGIN 13 SET @TraceFileName = 'C:\TraceFiles\BatchAndRpcCompleted_' 14 + CAST(@RolloverFileNumber AS varchar(10)) + '.trc'; 15 INSERT INTO dbo.ServerSideTraceData 16 SELECT * 17 FROM FN_TRACE_GETTABLE(@TraceFileName, 1); 18 RAISERROR('%d rows imported from rollover file %d', 0, 1, @@ROWCOUNT, @RolloverFileNumber) WITH NOWAIT; 19 SET @RolloverFileNumber = @RolloverFileNumber + 1; 20 END 复制代码 为了避免导入几百万跟踪记录数据时，对生产环境产生性能影响，建议使用测试环境。由于测试环境 SQL Server 服务账号没有访问生产环 境服务器的权限，所以先将原始的跟踪文件复制到测试环境的 C盘下。脚本中实现的方法没有使用一个 INSERT…SELECT 语句导入所有的 跟踪文件，而是使用循环分别导入每个文件。这样可以避免填充测试环境的事务日志。下一篇文章中我们会讨论自动化导入多个跟踪文件的 其他方法。 接下来我们介绍一个简单的查询语句来收集有用的信息。 21 SELECT 22 HostName 23 ,ApplicationName 24 ,COUNT(*) AS QueryCount 25 ,AVG(RowCounts) AS AvgRowCount 26 ,AVG(CPU) AS AvgCPU 27 ,SUM(CPU) AS TotalCPU 28 ,AVG(Reads) AS AvgReads 29 ,SUM(Reads) AS TotalReads 30 ,AVG(Duration)/1000 AS AvgDurationMs 31 ,SUM(Duration)/1000 AS TotalDurationMs 32 FROM dbo.ServerSideTraceData 33 WHERE 34 EventClass IN(10,12) --Batch and RPC completed 35 GROUP BY 36 HostName 37 ,ApplicationName 38 ORDER BY 39 QueryCount DESC; 复制代码 这个查询按照HostName和 ApplicationName分组进行统计。运行结果见下图，列出了查询次数最多的HostName 和 ApplicationName 以及一些其他信息。 注意：在上面例子中 ApplicationName 显示为“.Net SqlClient Data Provider”，是由于程序中没有明确指定应用名称。建议让开发人员 指定应用名称（在链接字符串中指定 Application Name的值）。特别是当同一台服务有多个应用访问时，明确应用名称更容易区分是哪个 应用或组件的查询语句。 上面的查询结果，可以看出服务器执行最多的查询是来自公共的web 服务器，平均查询时间很短，在1毫秒以下。仅仅通过这些指标还不够， 并不能看出所有的问题。下面再运行一个查询。 1 --sample queries from specific host and application 2 SELECT TOP 1000 3 HostName 4 ,ApplicationName 5 ,TextData 6 ,RowCounts 7 ,CPU 8 ,Reads 9 ,Duration/1000 DurationMs 10 FROM dbo.ServerSideTraceData 11 WHERE 12 EventClass IN(10,12) --Batch and RPC completed 13 AND HostName = 'WEB03' 14 AND ApplicationName = '.Net SqlClient Data Provider' 15 AND StartTime > '20110414 10:00:00' 16 ORDER BY StartTime; 复制代码 通过这个查询和查询的结果，我们可以看出WEB程序多次反复调用GetProductList 的一个存储过程，并且在这台WEB 服务器上一半以上 的查询都是这个存储过程，这说明它的执行频繁太高。 我将分析的结果发给开发小组，之后他们在代码中发现了 bug，即使在产品数据不需要的时候，每次页面点击也都会调用这个存储过程。而 且这个产品列表几乎是静态的，最后通过增加缓存避免其他重复的访问调用这个存储过程。这个改进减少了每天百万次的查询。 使用这个方法，我发现了很多问题并深入细节去分析。大多数问题都不严重而且容易修复，从中我学到了很多关于应用程序如何正确访问数 据库的方法，积累了宝贵的经验。 总结总结总结总结 跟踪数据是非常有价值的资源，我们可以将它保存到 SQL Server 的表中做各种分析。通过分析跟踪数据不仅可以发现和解决 SQL Server 的性能问题，而且可以让我们深入了解到应用程序是如何访问数据库的。当数据量很大的时候，这些数据也可以存储在数据仓库中，做汇总 分析并生成报表展示。 本篇内容就到这里，下篇文章我们会继续讨论用不同的方法自动化导入跟踪数据。