简介SQL Server Profiler是数据库管理员常用的图形化性能监控工具这份配套文档系统讲解了其在SqlServer2000下的使用方法与优化技巧。文档面向需要诊断数据库性能瓶颈、排查死锁和慢查询的DBA与开发人员内容按打开工具、创建跟踪、事件与数据列选择、过滤条件设置、查看分析跟踪及模板定制等章节逐步展开并专门介绍了MSDN相关分析方法、Read80trace工具的Normalization功能以及通过usp_GetAccessPattern存储过程分析Normalize后数据并定位HOT数据库的高级技巧可直接用于生产环境性能调优。资源为单个doc格式文档压缩包仅308KB内容精炼但覆盖了从基础监视到深层分析的全流程。目前已有597人学习使用适合作为SQL Server性能监控的速查与进阶参考资料尤其对需要维护历史版本SQL Server的运维人员和开发人员有实用价值。1. 一份《SqlServer2000性能工具Profiler.doc》旧文档为什么今天还值得照着做一份名为《SqlServer2000性能工具Profiler.doc》的旧文档放到现在第一眼确实劝退SqlServer2000 是二十多年前的产品Profiler 也早被新版本里的 DMV、查询存储和扩展事件挤到了角落。但如果你手里正好有一台老库或者要从一套历史遗留系统里揪出那个跑了好几年都说不清道不明的慢查询这份文档描述的技术依然能直接落地。SqlServer2000 的 Profiler 本质上是一个事件捕获器能把服务端正在执行的 SQL、存储过程调用、锁等待与死锁信息原样抓出来并给出耗时、CPU、Reads/Writes 这些关键指标。下面按我做老库维护时实际用下来的方案把这套工具从客户端追踪到服务端脚本、从筛选参数到结果分析完整拆开讲。2. 为什么 SqlServer2000 的性能调优绕不开 Profiler工具定位与选型理由2.1 没有 DMV 也没有扩展事件Profiler 是那个年代唯一能看见 SQL 的探针SQL Server 2000 时代的性能诊断和现在完全是两回事。现在遇到慢查询第一反应是查 sys.dm_exec_query_stats、看等待统计、翻查询存储而 2000 里这些一概没有管理员手边能用的工具屈指可数Windows 性能监视器看 CPU、内存、磁盘队列DBCC 命令看碎片和缓冲查询分析器看执行计划剩下的就只有 Profiler。性能监视器能把压力说清楚却说不清是哪条 SQL 在制造压力DBCC 能看结构问题也看不到“某一个时刻到底谁在跑”。恰恰是 Profiler 能在不中断业务的前提下把服务端正在执行的语句一条条列出来带 CPU、Reads、Writes、Duration 这些资源消耗字段。这就是它被放进“性能工具”里的根本原因。我见过不少刚接手老项目的同事面对一台 SQL Server 2000 实例一脸茫然第一反应是去装新版本客户端来连结果连不上。其实 2000 的 Profiler 不是独立安装包它随 SQL Server 2000 客户端工具一起安装。中文版一般在开始菜单 Microsoft SQL Server 组里叫“事件探查器”英文版叫 SQL Server Profiler。如果服务器上找不到这个入口多半是只装了数据库服务器组件没装客户端工具需要补装客户端组件才能看到。这个入口问题看起来基础但实际排查时卡住半小时的案例我见过不止一次。2.2 客户端追踪与服务端追踪线上抓取直接选后者Profiler 的“追踪”有两种完全不同的运行方式文档里如果只字不提容易在第一步就埋下隐患。第一种是客户端追踪也就是启动 Profiler 图形界面连上实例后新建一个跟踪事件流实时汇入界面。这种方式直观、门槛低适合开发环境或者临时盯一屏输出缺点同样明显事件要先经过客户端进程过滤和渲染网络抖动、笔记本休眠、内存不足都会丢事件而且图形界面本身对服务器也有额外压力。第二种是服务端追踪用系统存储过程在服务端定义追踪事件由 SQL Server 进程直接写入文件客户端断开也不影响抓取这也叫“脚本化追踪”。两种方式的核心差异可以看下面这张表。对比项客户端追踪服务端追踪追踪运行位置Profiler 客户端进程SQL Server 服务进程断线表现客户端断线即停事件丢失客户端断开追踪继续文件滚动界面勾选容易漏设Options2 自动切换新文件对线上库额外压力较高界面渲染也占资源较低事件直接写文件适用场景开发环境、临时盯一屏线上库、长时间抓取我的习惯是只要目标是生产库或者打算抓超过十分钟的数据一律走服务端追踪。早期我在笔记本上开客户端追踪抓线上问题中午去吃饭笔记本休眠回来发现追踪早就静默停了一下午白干从那以后就长了记性。2.3 用 SqlServer2000 Profiler 前先对齐三个旧版认知在动手之前有三个容易被新版本经验误导的差异点值得先说清楚。第一个是 Duration 的单位。SQL Server 2000 Profiler 里 Duration 数据列的单位是毫秒而 SQL Server 2005 之后改成了微秒。同样的“Duration 1000”在 2000 里表示超过 1 秒在 2005 里则表示超过 1 毫秒套错单位会让筛选条件形同虚设。第二个是事件命名风格。2000 的事件类形如 SQL:BatchCompleted、RPC:Completed、Lock:Deadlock Chain带冒号分层新版本扩展事件的命名和组织方式完全不同不能用新思路直接找。第三个是版本兼容性。2000 的 Profiler 客户端只能追踪 SQL Server 2000 实例抓 2000 的库就得用装着 2000 客户端工具的机器别的版本连不进去。这三个认知对齐了后面的配置才不会来回返工。3. 用 Profiler 建立线上追踪模板、事件类、数据列与筛选器参数3.1 新建追踪的四个关键选项模板、保存位置、文件上限与滚动更新客户端追踪的入口路径基本固定打开 Profiler文件菜单里选择新建跟踪连接到目标实例随后弹出“跟踪属性”窗口。第一次用的人往往盯着这个窗口发呆因为里面的选项并不少。按优先级排真正需要先定下来的只有四件事模板、保存位置、单文件上限、是否滚动更新。模板建议选 Standard如果你主要查存储过程相关性能可选 TSQL_SPs。2000 自带的模板数量很少和后来版本里几十个预置模板没法比选哪个都只是起点最终事件类都要手动调整。保存位置方面客户端追踪时文件默认保存在运行 Profiler 的这台机器上服务端追踪时文件路径则要写在数据库服务器上。很多人第一次就把路径写错抓了半天发现文件根本没生成原因就在这层没分清。单文件上限我一般设 20MB太小会频繁滚动生成一堆碎片文件太大又不好收尾。最关键的是勾选“启用文件滚动更新”不勾的话文件写满 20MB 后追踪会自己停下来界面没有任何提示这是 2000 里最常见的静默翻车点。设置完保存选项后进入“事件类”标签页左边是事件分类树右边是数据列选择底部还能打开筛选窗口。全部配好后点运行追踪就开始工作了。需要提醒的是2000 的追踪一旦启动配置就不允许再改想调整筛选条件只能停止后重新建一个追踪。所以在点“运行”之前把模板、事件、数据列和筛选都检查一遍比什么都重要。3.2 事件类与数据列的取舍Completed 系列是主力Showplan 慎开在“事件类”标签页里最直观的诱惑是把“显示所有事件”勾上这样能看到所有分类和事件名但千万别全选。全选的结果是噪音淹没信号抓出来的文件满是连接、断开、审计和错误信息真正的慢 SQL 反而被淹没。实际使用中我通常只勾下面这几类。事件类所在分类作用建议SQL:BatchCompletedSQL一条批处理执行完成后的耗时与资源默认勾选RPC:Completed存储过程一次 RPC 调用主要是存储过程调用完成信息默认勾选SQL:StmtCompletedSQL批处理内部单条语句完成信息分析单条语句时勾Showplan XML性能输出执行计划文本量巨大默认不勾Lock:Deadlock / Lock:Deadlock Chain锁分类死锁相关的进程与资源信息排查锁问题时勾为什么要选带 Completed 的事件而不选 Starting因为 Starting 事件只有开始时刻没有耗时、CPU、Reads 这些结果数据。性能定位要看的是“一条 SQL 跑完花了多少资源”而不是“它开始了”。至于 Showplan XML它会把执行计划完整写进 TextData一条复杂查询的计划能膨胀到几百上千字符抓一小时文件轻松超过几百 MB除非你专门在调执行计划否则不要勾。数据列方面TextData、EventClass、Duration、CPU、Reads、Writes、SPID、StartTime、DatabaseName、ApplicationName 这十列基本够用。TextData 是 SQL 文本EventClass 用来区分事件类型Duration/CPU/Reads 是排序和聚合的主要依据DatabaseName 和 ApplicationName 用来过滤业务范围。数据列不是越多越好每多一列追踪输出和文件体积都会成比例上涨。3.3 筛选器设置排除 Profiler 自身把 Duration 阈值卡在毫秒量级筛选器是整个追踪配置里最值得花时间的地方。在跟踪属性窗口的事件类标签页底部有一个“筛选”按钮点开后左侧列出数据列右侧是运算符和值。2000 的筛选器虽然简陋但基础的等于、大于、小于、包含都支持。我固定会加三组条件第一ApplicationName 不等于“SQL Server Profiler”排除追踪工具自身的查询第二DatabaseName 等于目标业务库名避免多个库共用一个实例时抓到无关语句第三Duration 大于 1000也就是只抓超过 1 秒的语句。如果目标是排查逻辑读压力也可以再加一个 Reads 大于 500 的条件。这里有三个 2000 特有的限制。筛选器是全局生效的对所有已选事件统一过滤你没法单独给 Lock:Deadlock 设一个阈值、再给 SQL:BatchCompleted 设另一个阈值。其次筛选器不能实现“某个事件不参与过滤”这种细粒度控制。最后追踪启动后筛选器不可修改想调整只能停掉重建。所以在点运行前把这三组条件反复核对一次尤其是单位问题2000 的 Duration 是毫秒“大于 1000”就是大于 1 秒不要拿着 2005 后的微秒习惯来设值。4. 把 Profiler 追踪脚本化sp_trace_create 建立服务端追踪与文件读取4.1 为什么服务端追踪更可靠断线、丢事件与文件滚动的差别如果你已经按第 3 章的步骤配置了一个客户端追踪并且成功抓到了数据那下一步值得做的事情是把这个追踪“搬到服务端”。客户端追踪最大的问题在于它依赖图形界面进程的稳定性。我经历过最典型的一次下午两点开始抓三点去看发现界面还在但文件已经四个小时没写了原因是当时连接会话被网络策略断开Profiler 客户端进入重连状态事件流全部丢失。服务端追踪不存在这个问题它由 SQL Server 进程写文件客户端只是下发了一个定义之后哪怕关闭 Profiler追踪照样在跑。把当前客户端追踪配置转换成服务端脚本操作上也有现成入口。在 Profiler 的文件菜单里找“导出”或“另存为”相关选项选择生成 SQL 脚本工具就会把当前事件类、数据列、筛选器的定义翻译成一组系统存储过程调用。2000 的菜单在不同语言版本里位置略有差异但核心是“把跟踪定义保存为脚本”。生成出来的脚本一般很长因为 sp_trace_setevent 会把每个事件与每个数据列的组合逐行展开一个中等配置生成几百行非常正常这是正常的不要手工精简。执行这份脚本需要 sysadmin 权限并且脚本里的文件路径是数据库服务器本机的路径。执行前先把目录建好确认 SQL Server 服务账户对该目录有写权限否则追踪创建成功后一启动就报错错误信息还藏在系统日志里不容易发现。4.2 用 sp_trace_create 定义追踪的最小脚本参数说明与启动停止从 Profiler 导出的脚本很长不方便在文章里完整贴出来但核心骨架就是下面这几段。第一次接触的人看这个最小示例就能理解服务端追踪的运行逻辑。事件编号和数据列编号不要靠记忆写以你自己机器上导出的脚本为准下面代码只是演示。-- 创建服务端追踪输出到 D:\Trace\app_trace单文件上限 20MB启用滚动 DECLARE TraceID int EXEC sp_trace_create TraceID TraceID OUTPUT, Options 2, -- 2 表示文件滚动写满自动生成新文件 TraceFile ND:\Trace\app_trace, -- 不写扩展名SQL Server 自动加 .trc MaxFileSize 20, -- 单文件上限单位 MB StopTime NULL, -- 不设自动停止时间 FileCount 5 -- 最多保留 5 个滚动文件 GO -- 绑定事件与数据列。事件编号和数据列编号由 Profiler 导出脚本自动生成这里只列常用组合 EXEC sp_trace_setevent TraceID, 10, 1, 1 -- RPC:Completed - TextData EXEC sp_trace_setevent TraceID, 10, 11, 1 -- RPC:Completed - Duration EXEC sp_trace_setevent TraceID, 10, 13, 1 -- RPC:Completed - CPU EXEC sp_trace_setevent TraceID, 10, 16, 1 -- RPC:Completed - Reads EXEC sp_trace_setevent TraceID, 12, 1, 1 -- SQL:BatchCompleted - TextData EXEC sp_trace_setevent TraceID, 12, 11, 1 -- SQL:BatchCompleted - Duration EXEC sp_trace_setevent TraceID, 12, 13, 1 -- SQL:BatchCompleted - CPU EXEC sp_trace_setevent TraceID, 12, 16, 1 -- SQL:BatchCompleted - Reads GO -- 启动追踪状态 1启动0停止2关闭并删除定义 EXEC sp_trace_setstatus TraceID, 1 GO这段脚本的逻辑分成三步先创建追踪得到一个追踪 ID然后把需要的事件和数据列绑定到这个 ID 上最后启动它。Options 参数是滚动更新的开关设成 2 时文件写满会自动切换新文件这也是线上长时间抓取必须设置的参数。TraceFile 参数注意不要带扩展名SQL Server 会在第一个文件上自动加 .trc滚动后的文件会变成 app_trace_1.trc、app_trace_2.trc 这种命名。MaxFileSize 的单位是 MB最小可以设 1实际建议 20 到 50 之间。FileCount 表示滚动文件数量上限超过后最早的滚动文件会被覆盖所以磁盘规划要按“单文件上限 × 文件数”再加余量来留。停止服务端追踪时正确的顺序是先停止再关闭定义。用 sp_trace_setstatus 传 0 停止追踪追踪定义还留在服务端再传 2 才能关闭并释放资源。如果抓完数据只停在停止状态不清理定义长时间挂机还是会占用服务端资源。养成抓完就三步走——停止、关闭、确认文件生成——的习惯比什么都强。-- 停止追踪并清理定义 EXEC sp_trace_setstatus TraceID, 0 EXEC sp_trace_setstatus TraceID, 2 GO4.3 用 ::fn_trace_gettable 回读追踪文件下一步分析的入口服务端追踪产生的 .trc 文件除了可以在 Profiler 里直接打开更实用的读取方式是使用系统函数 ::fn_trace_gettable。这个函数可以把追踪文件当表来查询方便按事件编号、耗时、CPU 排序也可以导出成 CSV 做进一步分析。下面是一段最常用的读取查询。-- 读取追踪文件中的慢语句按耗时倒序 SELECT TOP 100 EventClass, TextData, Duration, CPU, Reads, SPID, StartTime FROM ::fn_trace_gettable(ND:\Trace\app_trace.trc, default) WHERE EventClass IN (10, 12) -- 10RPC:Completed12SQL:BatchCompleted ORDER BY Duration DESC GO这里几个点需要解释一下fn_trace_gettable 是 SQL Server 2000 的系统表值函数调用时必须带双冒号前缀这个写法在新版本里已经不常见了。第二个参数 default 表示读取文件本身如果开了滚动更新文件不止一个这个函数在 2000 里对多文件的支持有限常见做法是把主文件和滚动文件复制到同一个目录后按顺序改名读取或者直接用 Profiler 图形界面去打开主文件它会自动加载关联的滚动文件。EventClass 数字与事件名的对应关系可以通过服务端目录视图或早期文档查但更简单的办法是先用 Profiler 打开文件看一眼确认。TextData 列是 ntext 类型排序和导出时如果需要完整文本建议在 SELECT 里显式转成 nvarchar否则不同工具处理起来容易出截断和乱码。5. Profiler 避坑指南5 个高频翻车点与排查方法5.1 TextData 被截断只看到 SQL 前半句整段语句拼不齐现象抓回来的追踪里很多 TextData 内容停在两三百个字符就断了一条很长的 UPDATE 只能看到前半段复制出来根本没法还原完整语句。原因客户端追踪在界面展示和保存时为了控制内存与显示开销对长文本列做了截断处理。这不是服务端数据本身的问题而是 Profiler 客户端为了交互体验加的限制。解决改用第 4 章的服务端追踪事件由服务端直接写文件TextData 保存的是完整文本。已经用客户端抓出来的半截数据没有后悔药只能重新抓。判断 TextData 是否完整有一个简单办法看末尾有没有正常结束符或者直接把 SQL 文本长度和 StmtCompleted 之类的配套信息对照一下。5.2 死锁图在 SqlServer2000 里显示不出来勾了事件仍一片空白现象为了排查死锁跟踪里明明勾选了 Lock:Deadlock死锁也确实发生了但界面上看不到任何图形只有一堆看不懂的进程编号和对象文本。原因SQL Server 2000 的死锁信息需要同时勾选 Lock:Deadlock 和 Lock:Deadlock Chain 两个事件类只勾前者拿不到完整的锁等待链而且 2000 的死锁展示机制很弱不像 2005 之后有独立的死锁图页签。解决两个事件类一起勾上。抓到死锁后在结果行上右键选择“提取事件数据”把进程和资源信息转存为文本或 HTML 文件再慢慢看。分析时把两个 SPID、各自占用的对象、等待的资源对应起来基本就能还原死锁环。5.3 追踪文件写满自动停止高峰时段数据断档原因在滚动参数现象早上 8 点启动追踪中午去看发现文件最后写入时间停在 8 点 10 分之后没有任何数据但数据库明显一直在忙。原因文件达到 20MB 上限后追踪自动停止而且 2000 的界面不会弹提示。客户端追踪没勾“启用文件滚动更新”服务端追踪的 Options 不是 2都会触发这个行为。解决客户端追踪在保存设置里勾上文件滚动更新服务端追踪把 sp_trace_create 的 Options 参数设成 2。同时磁盘空间要按预期抓取时长预留20MB 的上限在忙碌系统里可能十分钟就写满留足余量才能覆盖完整高峰期。5.4 抓进大量 Profiler 自会话筛选条件没生效噪音刷屏现象追踪结果里混进大量来源为 SQL Server Profiler 的短小命令比如一些系统维护语句把业务 SQL 淹没了根本没法看。原因筛选器里没有排除 ApplicationName 为 SQL Server Profiler 的会话也没用 DatabaseName 或 LoginName 限定业务范围导致工具自身的活动也被记录下来。解决在筛选器里加一条 ApplicationName 不等于“SQL Server Profiler”的条件再按实际业务加 DatabaseName 等于目标库名。如果老应用的 ApplicationName 没有固定值就改用 LoginName 或 HostName 来圈定业务范围。注意 2000 的筛选器对某些系统内部活动是挡不住的尽量通过缩小事件类范围来减少噪音。5.5 重放结果把线上数据写坏回放只能指向一次性测试环境现象为了验证某个 UPDATE 是否会导致锁等待有人把 Profiler 抓到的文件直接拿到测试库上点“重放”结果测试库里的真实数据全被改了更严重的是有人拿错了文件重放到生产环境。原因Profiler 的 Replay 功能是真实执行捕获的语句不是只模拟执行计划它会把 INSERT、UPDATE、DELETE 再原样跑一遍。解决重放只允许在一次性还原出来的专用测试副本上做并且执行前确认服务器名、数据库名都和捕获环境完全不同。生产环境抓取的文件只做分析不点重放。我现在的习惯是抓到需要验证的语句后把单条 SQL 手工抽取出来在事务里跑并回滚而不是整文件重放。6. 从 Profiler 文件到结论SQL 模板归一化与性能基线对比追踪文件拿到手最值钱的不是一条条看 SQL而是把几百条、上千条语句汇总成少数几个“SQL 模板”找到真正消耗资源的模式。这里我一般会把 fn_trace_gettable 的结果导出成 CSV再用一段小脚本做归一化聚合逻辑很简单把语句里的数字字面量和字符串字面量统一替换成占位符然后按归一化后的文本分组累计次数、总耗时和总 CPU。import re import csv from collections import defaultdict def normalize(sql): if not sql: return # 字符串字面量替换为占位符 s re.sub(r\\.*?, ?, sql) # 数字字面量替换为占位符 s re.sub(r\\b\\d\\b, ? , s) # 压缩空白只保留前 200 字符用于分组 s re.sub(r\\s, , s) return s[:200] agg defaultdict(lambda: [0, 0, 0]) # [次数, 总耗时, 总CPU] with open(trace.csv, newline, encodinggbk) as f: reader csv.DictReader(f) for row in reader: key normalize(row[TextData]) if not key: continue agg[key][0] 1 agg[key][1] int(row[Duration] or 0) agg[key][2] int(row[CPU] or 0) for sql, (cnt, dur, cpu) in sorted(agg.items(), keylambda x: -x[1][1])[:20]: print(cnt, dur, cpu, sql)这段脚本做的事情就是把相同模板的语句归到一起。比如“SELECT * FROM orders WHERE order_id 10001”和“SELECT * FROM orders WHERE order_id 20002”归一化后都是“SELECT * FROM orders WHERE order_id ?”会归到同一组。分组后按总耗时排序排在最前面的那几条才是真正要优化的对象。CSV 导出时注意编码SQL Server 2000 中文环境导出的文件用 GBK 读取比较稳这一点在 Python 里已经通过 encoding 参数处理了。归一化之后下一步是性能基线对比。做法很简单优化前在业务高峰期抓 15 分钟记录前几个模板的累计 Duration、平均 Duration 和总 Reads做完索引调整或 SQL 改写后同样条件下再抓 15 分钟对比同一组模板的指标变化。数量级差距一目了然是能给业务方和开发看的硬数据。这套归一化加基线对比的流程我在老库上用了很多次。以前排查一个订单系统的慢查询第一次抓到一千二百行归一化后只剩 14 个模板前三个模板占了 80% 的 Duration改完索引再抓一次同样 15 分钟前三个模板的总耗时从 87000 毫秒降到 13000 毫秒。如果你也接手了这样的老库记得先建服务端追踪再去吃饭。开着客户端追踪在办公室里过夜这种事断一次线一下午就白抓了。希望帮到你。本文还有配套的精品资源点击获取