SQL Server 2005性能排错(2)

您所在的位置：首页>>开发>>微软平台和开发技术专区>>SQL Server>>

SQL Server 2005性能排错(2)

http://developer.51cto.com 2007-02-28 17:25 Sunil Agarwal/Boris Baryshnikov/Tom Davidson/Keit. msdn 我要评论(0)

摘要：很少会有偶然的性能下降。设计不良的数据库或工作负载配置不正确的系统会经常导致性能问题。管理员需要能预先阻止或最小化问题的影响，当管理员遇到问题时，应该诊断问题并采取正确操作来修复问题。本文提供了按部就班的指导，通过使用可用的工具例如SQL Server Profiler，System Monitor和在SQL Server 2005中新的Dynamic Management View来为一般的性能问题诊断和排错。
标签：SQL Server 2005 性能排错

资源瓶颈

接下来的部分讨论了CPU，内存和I/O子系统资源及这些瓶颈如何产生。（网络问题不在本文的讨论范围）每个资源瓶颈，我们描述了如何识别问题并找到原因。例如，一个内存瓶颈可以导致过渡分页，将会影响性能。

在决定你遇到资源瓶颈前，你需要了解在普通环境下资源是如何使用的。你可以使用在这片白皮书中所列出的方法收集有关资源使用的基线信息（即使你没有遇到性能问题）。

你也许找到问题是一个资源运行到设计容量并且SQL Server当前的配置不能支持这种负载。为了解决这问题，你也许需要添加处理能力，内存或增加I/O的带宽或网络通道。但是，在你进行下一步之前，理解这种资源瓶颈的通常原因是非常重要的。有一些解决方案不需要添加额外资源，例如重新配置。

解决资源瓶颈的工具

下列工具是在解决特殊性能瓶颈时经常使用的工具。

◆系统监视器(PerfMon)：这是Windows所带的一个工具。更多信息，请见系统监视器文档。
◆SQL Server Profiler：在SQL Server 2005程序组中的Performance Tools组中可以看到SQL Server Profiler。
◆DBCC 命令：详细信息请见SQL Server联机丛书中和附录A。
◆DMVs: 详细信息请见SQL Server联机丛书。

CPU 瓶颈

当没有额外负载，突然发生的CPU瓶颈通常由于没有查询计划，不良的配置或设计因素和不足的硬件资源所引起。在购买更快或更多处理器前，你需要首先确定CPU的最大处理能力带宽并查看是否他们都在使用中。

系统监视器一般是确定CPU资源的最好工具。你应该查看Processor:% Processor Time计数器是否偏高；该计数器值超过80%一般被认为是瓶颈。你也可以使用sys.dm_os_schedulers视图来监视是否正在运行的任务不是0。非0的值预示有任务需要等待时间切片来运行；这个数值高表明一个CPU瓶颈的征兆。你可以用下列查询列出所有的调度器并产看等待运行的任务数量。

select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255

下列查询给你一个查看当前缓存中最耗费CPU的批或过程高级别视图。查询根据具有相同plan handle（意味着属于同一个批或过程）的语句聚合CPU的调用。如果给出的plan handle对应多个语句，你将不得不继续找到在整个CPU使用中最占用资源的查询。

select top 50 
sum(qs.total_worker_time) as total_cpu_time, 
sum(qs.execution_count) as total_execution_count,
count(*) as  number_of_statements, 
qs.plan_handle 
from 
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

本节剩下的部分将讨论一些其他的在SQL Server 中CPU敏感的操作，也有有效的检查和解决这些问题的方法。

过多的编译和重编译

当批或远端过程调用(RPC)被提交到SQL Server，在开始执行前，服务器检查查询计划的有效性和正确性。如果这些检查中的一个失败时，批将被再次编译声称不同的查询计划。这种编译就是所说的重编译。当服务器确认当底层数据改变时有更优化的查询计划时，重编译确认正确性再完成编译。编译是CPU敏感的，因此过多的重编译将导致在系统中的CPU性能问题。

在SQL Server 2000中，当SQL Server重编译一个存储过程，整个存储过程都被重编译，不仅是触发重编译的语句。SQL Server 2005引入了语句级的存储过程的重编译。当SQL Server 2005重编译存储过程，只有导致重编译的语句被编译-而不是整个存储过程。这减少了CPU的负载并减少了对例如COMPILE锁的资源争用。重编译可以有多种原因出发，例如：

◆架构变化
◆统计变化
◆延期编译
◆Set属性改变
◆临时表改变
◆存储过程创建时使用了RECOMPLIE查询提示或使用了OPTION （RECOMPILE）。

检测

你可以使用系统监视器（PerfMon）或SQL Trace(SQL Server Profiler)来检测过多的编译和重编译。

系统监视器(Perfmon)

SQL Statistics对象提供了监视重编译的计数器和发送到SQL Server实例的请求类型。你必须监视查询编译和重编译的数量以及相关联的接受的批的数量来找出是否这个编译是最耗费CPU资源。理想环境下，SQL Recompilations/sec和Batch Request/sec的比值应该很低，除非用户提交了大量的单独查询。

下列显示了关键的数据计数器。

◆SQL Server: SQL Statistics: Batch Requests/sec
◆SQL Server: SQL Statistics: SQL Compilations/sec
◆SQL Server: SQL Statistics: SQL Recompilations/sec

更多信息请看SQL Server联机丛书中的 “SQL Statistics Object”