SQL Server Profiler 性能分析实战：从 Trace 抓取到热点 SQL 定位

简介这份文档面向 SQL Server 数据库管理员与开发人员聚焦 SQL Server 2000 环境下性能监视工具 Profiler 的实战应用帮助排查死锁、致命错误、慢查询与存储过程效率等典型问题。资源包内含 1 个 doc 文件压缩包约 308KB篇幅紧凑适合作为随查随用的工具手册。内容从打开 Profiler、新建跟踪、设置事件与数据列、配置过滤条件讲起逐步延伸到跟踪结果的查看与分析并覆盖跟踪模板的创建与复用。文档还整理了 MSDN 相关介绍包括 Profiler trace 文件性能分析的传统方法及其局限、Read80trace 工具的 Normalization 功能、利用存储过程分析规范化数据、usp_GetAccessPattern 的使用技巧以及如何判断 HOT 数据库。读者可据此掌握实时监视、事件跟踪、数据存储与性能瓶颈定位的完整思路并借助模板与工具链提升 SQL 语句和存储过程的执行效率。目前已有 597 人学习下载。1. 从一次 CPU 打满的故障说起这份 Profiler 文档能帮你做什么接手一套跑了七八年的业务系统应用端反馈“页面转圈”登录服务器一看8 个 CPU 全在 90% 以上波动内存和磁盘反倒很闲。这种时候最怕凭感觉猜猜错方向改半天索引问题还在。我当时的做法是挂上 SQL Server Profiler 采样两小时再用文档里提到的 Read80trace 做标准化最后跑一遍usp_GetAccessPattern报表出来一眼就看到某个存储过程两小时内只跑了 75 次却吃掉了 90.8% 的 CPU。定位到这一条优化方向就清楚了。这份《SqlServer2000性能工具Profiler.doc》讲的正是这套从抓取到分析的完整链路。它不是单纯教你怎么点开 Profiler 菜单而是把“抓 Trace → 标准化 → 按访问模式归类统计 → 找热点语句和热点库”串成了一条可复现的路径。适合两类人一是还在维护 SQL Server 2000/2005 老系统的 DBA二是需要从海量 Trace 里快速找出关键 SQL 的后端工程师。如果你手上正好有性能问题要查这份文档里的存储过程和命令行参数可以直接抄来用。2. Profiler 抓取与跟踪配置事件、数据列和过滤条件怎么定2.1 打开 Profiler 并建立第一条跟踪文档第 1 章给的入口路径是企业管理器里tool → SQLServer Profiler → New Trace连上目标服务器后直接 Run。这个流程在 SQL Server 2000 的 Enterprise Manager 里是通的如果你用的是 SSMS入口变成Tools → SQL Server Profiler逻辑一样。新建跟踪时会弹出一个 Trace Properties 对话框里面几个关键选项直接决定后面分析顺不顺手。跟踪名称和服务器不用多说重点是输出方式。文档里列了Capture to file和Capture to table两种。我的血泪经验是生产环境一律选 Capture to file把.trc落到本地磁盘。Capture to table 会把每一行事件写进数据库表跟踪本身就在给数据库加负载很容易把一次性能排查变成二次故障。文件方式内存开销小后面用 Read80trace 处理也更方便。-- 如果你不想开图形界面也可以用扩展存储过程建跟踪 -- 下面这段是常见做法先建一个输出到文件的跟踪 DECLARE rc int DECLARE TraceID int DECLARE maxfilesize bigint SET maxfilesize 50 -- 单位 MB单个文件到 50MB 就滚动 EXEC rc sp_trace_create TraceID OUTPUT, 0, ND:\trace\perf_trace, maxfilesize, NULL SELECT rc AS rc, TraceID AS TraceID这段代码里sp_trace_create的第二个参数 0 表示不启用文件滚动之外的特殊选项第三个参数是文件路径前缀SQL Server 会自动加.trc后缀。maxfilesize设 50MB 是为了防止单个文件太大不好处理Read80trace 能顺序读取perf_trace.trc、perf_trace_1.trc这一系列文件。建完跟踪后还需要用sp_trace_setevent逐个添加事件和列再用sp_trace_setstatus启动步骤比图形界面繁琐但适合做成脚本反复用。2.2 事件选择只抓你真正要看的文档 2.1 节列了一串值得监视的事件登录连接的成功失败断开、DELETE/INSERT/UPDATE、RPC 状态、存储过程开始结束、错误日志写入、游标打开、锁的添加释放。同时明确说了一句“不监视过多的事件”因为每多一个事件类跟踪文件就大一圈系统负担也重。我一般分两轮抓。第一轮用 Standard 模板跑拿到整体轮廓第二轮针对可疑模块只留SQL:BatchCompleted、RPC:Completed、SP:StmtCompleted这几个把噪音压下去。文档第 3 章的模板表里TSQL_Duration模板专门按执行时间分组适合找慢查询TSQL_SPs模板把存储过程的每一步都抓出来怀疑过程重编译时再加SP:Recompile事件。模板不是拿来就用得根据你当前要回答的问题选。2.3 数据列与过滤条件把 Reads、CPU、Duration 留下来Data Columns 标签页决定每一行事件记录哪些字段。文档说“通常选择那些我们比较关心的列”这句话背后有个硬性要求如果你后面要用usp_GetAccessPattern做统计reads、cpu、duration、writes这四列必须勾上否则存储过程里sum(cpu)、sum(reads)全是空。另外TextData列也要留标准化后的语句文本就靠它。Filters 标签页是控制跟踪体积的关键。常见做法是按Duration过滤比如只抓大于 1000 毫秒的语句或者按DatabaseName过滤只盯某个库。但要注意过滤条件设得太狠可能把最频繁的那类短查询全滤掉了。文档 4.5 节专门提醒过运行最多最密的语句未必有超过 1000 毫秒的执行时间带参数运行的结果有可能不包括最频繁语句。所以第一轮抓取时我建议不加 Duration 过滤先把全量拿到手分析阶段再用duration_filter参数去切。3. Read80trace 标准化与 usp_GetAccessPattern 统计从 GB 级 Trace 到访问模式报表3.1 为什么传统分析方法在 GB 级 Trace 面前会翻车文档 4.2 节把传统方法说得很透一种是用 Profiler 自带的 Filter 和排序另一种是把 Trace 导入数据库用 T-SQL 统计。Trace 小的时候这两种都行但文档举了个例子——4 个 500MB 以上的 trace 文件这两种方法就非常吃力。原因有两个一是文件太大你很容易被某几条耗时语句吸引把精力耗在不是真正关键的地方二是大量语句模式类似、只是参数不同没有简单办法把它们归到一起统计。这个“模式类似、参数不同”的问题就是标准化的切入点。比如下面三条语句select * from authors where au_lname white select * from authors where au_lname green select * from authors where au_lname carson在访问模式层面它们是同一类查询但直接 group by 文本会得到三行。Read80trace 的 Normalization 功能把它们统一成select * from authors where au_lname {str}这样统计才有意义。3.2 Read80trace 命令行参数与执行文档给的命令是Read80trace –f –dmydb –Imytrace.trc三个开关的含义-f表示不生成 RML 文件因为不需要重放功能RML 文件体积很大不需要就关掉-d指定处理结果写入的数据库名后面创建的存储过程就是访问这个库里的表-I指定要分析的 trace 文件。Read80trace 会自动识别同目录下的mytrace.trc、mytrace1.trc、mytrace2.trc并顺序处理。另外-i开关可以直接从 zip 或 CAB 文件里读 trace不用手动解压。执行完之后mydb库里会生成两张关键表tblBatches存放所有语句tblUniqueBatches存放标准化后的语句两张表通过HashId关联。文档提到在 512MB 内存的老机器上分析几个 GB 的 trace 文件不到一小时这个性能在当年算相当能打。我自己的习惯是先把 trace 文件按时间段分目录每个目录单独跑一次 Read80trace避免一次处理太多文件时中途出错要重来。3.3 usp_GetAccessPattern 存储过程拆解文档 4.4 节给出了完整的存储过程定义。它的核心逻辑分两步先算出所有语句的 CPU、Reads、Writes、Duration 总和再按标准化文本 group by算出每一类语句的占比。Create procedure usp_GetAccessPattern duration_filter int-1 as begin declare sum_total float,sum_cpu float,sum_reads float, sum_duration float,sum_writes float select sum_totalcount(*)*0.01, sum_cpusum(cpu)*0.01, sum_readssum(reads)*0.01, sum_writessum(writes)*0.01, sum_durationsum(duration)*0.01 from tblBatches where durationduration_filter Select ltrim(str(count(*))) exec_stats, str(count(*)/sum_total,4,1)% ExecRatio, ltrim(str(sum(cpu))) : ltrim(str(avg(cpu))) cpu_stats, str(sum(cpu)/sum_cpu,4,1)% CpuRatio, ltrim(str(sum(reads))) : ltrim(str(avg(reads))) reads_stats, str(sum(reads)/sum_reads,4,1)% ReadsRatio, ltrim(str(sum(duration))) : ltrim(str(avg(duration))) duration_stats, str(sum(duration)/sum_duration,4,1)% DurRatio, textdata, count(*)/sum_total tp, sum(cpu)/sum_cpu cp, sum(reads)/sum_reads rp, sum(duration)/sum_duration dp into #queries_staticstics from (select reads,cpu,duration,writes, convert(varchar(2000),NormText)textdata from tblBatches inner join tblUniqueBatches on tblBatches.HashIdtblUniqueBatches.hashid where durationduration_filter) B group by textdata print Top 10 order by cpureadsduration select top 10 * from #queries_staticstics order by cprpdp desc print Top 10 order by cpu select top 10 * from #queries_staticstics order by cp desc print Top 10 order by reads select top 10 * from #queries_staticstics order by rp desc print Top 10 order by duration select top 10 * from #queries_staticstics order by dp desc print Top 10 order by batches select top 10 * from #queries_staticstics order by tp desc End几个参数和字段要说明白。duration_filter以毫秒为单位默认 -1 表示不过滤传入 1000 就只看执行时间超过 1 秒的语句。sum_total等变量乘 0.01 是为了后面算百分比时直接除省一次乘法。#queries_staticstics临时表里tp、cp、rp、dp分别是执行次数、CPU、Reads、Duration 的占比排序时用它们比用原始 sum 值更直观。存储过程把 writes 去掉了因为大部分系统以 Reads 为主需要的话把注释掉的那行放开就行。调用方式Use mydb Exec usp_GetAccessPattern -- 或者带过滤参数 Exec usp_GetAccessPattern 30003.4 从报表里读出关键语句文档给了一个真实采样某大型公司业务系统8 个 CPU 都在 90%100% 波动。跑完usp_GetAccessPattern后DBO.x_DEDUP_PROC两小时内运行 75 次占用 90.8% CPU、94.6% Reads。平均 CPU 时间 681961 毫秒约 11 分钟。8 个 CPU 两小时内最多跑 80 条左右实际跑了 75 条说明这个存储过程一直在连续运行。优化它问题基本就解决了。另一个采样里关键语句是SELECT COUNT(*) FROM GTBL7MS和SELECT CaseNO FROM PATIENTDATA_sum WHERE MRN P1后续检查发现相关表没有有效索引加上索引后性能整体提升。这两个例子说明报表的价值不在于数字多漂亮而在于帮你把“哪类语句最耗资源”这件事从猜测变成可量化的事实。4. 避坑与排查Profiler 分析里最容易翻车的五个地方4.1 跟踪文件暴涨把磁盘写满现象挂上 Profiler 跑了一晚上第二天发现磁盘红了.trc文件几十个 GB。原因事件类选太多或者没设文件滚动大小跟踪一直往一个文件里追加。解决建跟踪时设maxfilesize比如 50MB 或 100MB让文件自动滚动事件类只留必要的几个生产环境尽量用Capture to file而不是Capture to table。4.2 int 溢出导致存储过程报错现象执行usp_GetAccessPattern时报“将 expression 转换为数据类型 int 时出现算术溢出错误”。原因tblBatches里的reads、cpu、writes字段是 int 类型大 trace 文件里这些值的 sum 超过 int 上限。解决文档 4.5 节给了修正语句alter table tblBatches alter column reads bigint alter table tblBatches alter column cpu bigint alter table tblBatches alter column writes bigint改完再跑存储过程就不会溢出了。这个坑在 trace 文件超过 1GB 时几乎必现。4.3 过滤参数设太小漏掉最频繁的语句现象用usp_GetAccessPattern 5000跑出来的结果里找不到应用端反馈的那条高频查询。原因那条查询单次执行时间不到 5 秒被duration_filter滤掉了。解决文档 4.5 节建议参数不要小于 1000用 1000 的倍数如 3000、5000。同时交叉分析四个结果不带参数的、1000、3000、5000 各跑一次重叠出现的语句通常正是需要关注的。运行最多最密的语句未必有超过 1000 毫秒的执行时间所以不带参数的那次不能省。4.4 拿压力测试的 Trace 当生产数据用现象分析压力测试产生的 trace优化了一堆语句上线后性能问题依旧。原因很多压力测试工具不能真正模拟现实环境甚至是循环执行自己写的语句访问模式和真实生产差很远。解决文档 4.3 节明确建议分析从生产环境捕获的 Trace 文件压力测试的 trace 只作参考。如果非要用至少确认测试脚本覆盖了真实业务的主要路径。4.5 只盯最频繁语句忽略高资源占比的“少数派”现象报表里最频繁的语句是一条环境配置语句没有参考价值真正耗资源的存储过程只跑了 75 次差点被忽略。原因执行次数多不等于资源占用高。一条执行 11 分钟的存储过程跑 75 次就能吃满 8 个 CPU。解决看报表时同时关注ExecRatio、CpuRatio、ReadsRatio、DurRatio四个占比按cprpdp排序的那张表往往最能暴露问题。文档 4.4 节的例子就是靠这个排序抓到x_DEDUP_PROC的。5. 进阶技巧用 dbid 分组找出 HOT 数据库与交叉分析习惯5.1 按 dbid 分组定位最耗资源的库一台 SQL Server 上跑多个库时光知道哪类语句耗资源还不够还得知道哪个库最“热”。文档 4.6 节给了一段按dbid分组的统计语句思路和usp_GetAccessPattern一样只是 group by 的维度从textdata换成dbid。print group by dbid declare sum_total float,sum_cpu float,sum_reads float, sum_duration float,sum_writes float select sum_totalcount(*)*0.01, sum_cpusum(cpu)*0.01, sum_readssum(reads)*0.01, sum_writessum(writes)*0.01, sum_durationsum(duration)*0.01 from tblBatches select dbid, ltrim(str(count(*))) exec_stats, str(count(*)/sum_total,4,1)% ExecRatio, ltrim(str(sum(cpu))) : ltrim(str(avg(cpu))) cpu_stats, str(sum(cpu)/sum_cpu,4,1)% CpuRatio, ltrim(str(sum(reads))) : ltrim(str(avg(reads))) reads_stats, str(sum(reads)/sum_reads,4,1)% ReadsRatio, ltrim(str(sum(duration))) : ltrim(str(avg(duration))) duration_stats, str(sum(duration)/sum_duration,4,1)% DurRatio, count(*)/sum_total tp, sum(cpu)/sum_cpu cp, sum(reads)/sum_reads rp, sum(duration)/sum_duration dp into #queries_staticstics_groupbydb from (select reads,cpu,duration,writes, convert(varchar(2000),NormText)textdata,dbid from tblBatches inner join tblUniqueBatches on tblBatches.HashIdtblUniqueBatches.hashid) b group by dbid order by sum(reads) desc select dbid,ExecRatio batches,CPURatio CPU, ReadsRatio Reads,DurRatio Duration from #queries_staticstics_groupbydb这段代码和usp_GetAccessPattern的区别在于子查询里多取了dbidgroup by 换成dbid最后按sum(reads)降序排列。输出结果里dbid对应sysdatabases里的数据库 ID用select name from sysdatabases where dbid ?就能查出库名。文档给了一个输出样例dbid为 37 的库在 batches、CPU、Reads、Duration 四个维度上都排第一那它就是当前最需要关注的 HOT 库。5.2 交叉分析四个结果建立访问模式直觉文档 4.5 节提到的交叉分析方法是我自己用得最多的一条技巧。具体做法是同一份 trace 数据分别跑不带参数、1000、3000、5000 四次usp_GetAccessPattern把四张报表并排看。不带参数的那次告诉你“谁跑得最多”带参数的那几次告诉你“谁跑得最重”。重叠出现的语句就是既频繁又耗资源的关键语句优先处理。我一般还会把order by cprpdp desc的那张 Top 10 单独存下来和上一次优化的结果对比。如果某类语句的占比从 40% 降到 5%说明优化生效了如果没降反升可能是索引改了但统计信息没更新或者应用端调用模式变了。性能调优是个长期过程文档里那句话我印象很深一开始可以解决 80% 的问题但后面 20% 的问题却需要另外 80% 的时间。从那以后我每次做 Profiler 分析都强制走一遍“抓全量 → Read80trace 标准化 → 四个参数交叉跑 → 按 dbid 看热点库”的流程不再凭感觉猜哪条 SQL 有问题。这套文档里的存储过程和命令改改库名和文件路径就能直接用希望帮到你。本文还有配套的精品资源点击获取