Server 2005性能排错

解决

如果你监测到过多的编译/重编译，考虑下列选项。

◆如果重编译因为SET选项改变而发生，使用SQL Server Profiler确定哪个SET选项被改编。避免在存储过程中改变SET选项。如果改变最好在连接级别设置。确认在该连接的生存周期内不要改变SET选项。

在临时表上重编译的阀值比在普通表上的低。在临时表上的重编译时由于统计改变而引起，你可以降临时表改为使用表变量。表变量的改变不会引起重编译。这种方法的确定是查询优化器不识别表变量，因为统计不会被创建或维护表变量。这将导致没有查询计划。你可以测试不同的选项，并选择最好的方法。另外一个选项时使用KEEP PLAN查询提示。设置临时表的这个阀值与使用永久表相同。

EventSubclass 列预示了在临时表上的”Statistics Changed”的操作。

◆为避免由于改变统计而产生的重编译（例如，因为数据统计导致计划不理想），特别是KEEPFIXED PLAN查询提示。根据设置的影响，重编译可以仅因为相关正确的原因（例如，当底层表结构改变导致计划不再适用），而不根据统计的变化。如果语句引用的表的架构改变时或者表是被标记为sp_recompile的存储过程，重编译将发生。

◆关闭自动更新索引统计和表或视图的统计，防止由于对象的统计改变而产生的重编译。注意，通过使用这种方法关闭”auto-stats”特性不是一个好的想法。这是因为查询优化器不再为在这些对象上的数据改变而敏感，将导致不良的查询计划。使用这种方法仅在尝试了所有其他选择之后，做为最后的手段。

◆批应该尽量使用对象全名（例如，dbo.Table1）避免重编译并避免不明确的对象。

◆为避免由于延期编译导致的重编译，不要混杂DML和DDL或从条件结构创建DDL，例如IF语句。

◆运行Database Engine Tuning Advisor(DTA)查看改变索引是否可以改善编译时间和查询的执行时间。

◆检查是否存储过程通过WITH RECOMPILE选项创建或使用了RECOMPILE查询提示。如果过程通过WITH RECOMPILE选项创建，在SQL Server 2005中，如果在过程中特殊的语句需要被重编译，我们可以利用语句级的RECOMPILE提示。这将避免在每次执行的时候对整个过程重编译，而同时允许个别语句被编译。更多有关RECOMPILE提示的信息，请查看SQL Server联机丛书。

效率低的查询计划

当为一个查询生成查询计划时，SQL Server查询优化器尝试选择一个计划为查询提供最快的响应时间。注意最快的查询时间并不意味最小的I/O开销，也不意味使用最少的CPU资源-它会在各种资源中平衡。

某些操作类型比其他操作对CPU更敏感。Hash操作和Sort操作扫描他们各自的输入数据。使用扫描向前读取（prefetch）时，在需要操作页面前，页面几乎都在缓存中。因此可以减少或消除物理I/O操作。这使这些操作的类型将不被物理I/O所限制。与之相比，嵌套循环连接有很多索引查找，如果索引查找使用很多不同的表以至于页面不适合缓存的大小，将导致生成I/O负载。

最有意义的输入优化用于评估为每中操作生成不同查询计划开销的评估，你可以在Showplan（EstimateRows和EstimateExecution属性）中看到结果。没有精确的评估，用于优化的主输入是有缺陷的。

为获取SQL Server优化器如何使用统计的详细信息，请查看 Statistics Used by the Query Optimizer in Microsoft SQL Server 2005 。该白皮书讨论了优化器如何使用统计，维护和更新统计的最佳实践，以及一些常见的查询设计问题。



检测

低效率的查询计划通常可以被检测出来。低效率的查询计划可以导致增加CPU的消耗。

查询sys.dm_exec_query_stats是确定哪个查询累计使用CPU时间最多的有效方法。

select 

highest_cpu_queries.plan_handle, 

highest_cpu_queries.total_worker_time,

q.dbid,

q.objectid,

q.number,

q.encrypted,

q.[text]

from 

(select top 50 

qs.plan_handle, 

qs.total_worker_time

from 

sys.dm_exec_query_stats qs

order by qs.total_worker_time desc) as highest_cpu_queries

cross apply sys.dm_exec_sql_text(plan_handle) as q

order by highest_cpu_queries.total_worker_time desc

还可以选择，也可以查询sys.dm_exec_cached_plans并通过使用过滤器查找可疑的类似于‘%Hash Match%’, ‘%Sort%’这样CPU敏感的各种操作。

解决

如果你监测到效率低的查询计划，考虑下列选项。

◆使用Database Engine Tuning Advisor调节查询，查看是否生成对修改索引的建议

◆检查有问题的评估。

编写的查询中使用的更有限制性的WHERE从句是否合适？无限制条件的查询是资源敏感的。

在查询中涉及的表上运行UPDATE STATISTICS，检查是否还有这种问题。

是否查询使用的构造导致优化器不能精确的评估？

考虑是否可以将查询修改为其他的方法，避免这种问题。

◆如果不能修改架构或查询，SQL Server 2005有一个新的查询计划特性，允许你将指定查询提示添加到满足某种文本的查询中。这可以用在独立查询中，也可以用在存储过程内。例如OPTION(OPTIMIZE FOR)这样的提示允许你影响评估而忘记所有列出的潜在计划。其他的提示，类似OPTION(FORCE ORDER)或 OPITON(USE PLAN)允许你改变控制查询计划的程度。

内部查询的并行

当为一个查询生成执行计划时，SQL Server优化器尝试为该查询选择最快的相应计划。如果查询的开销超过了在cost threshold for parallelism选项中指定的值，并行不会被禁用，优化器尝试生成一个可以用于并行的计划。并行查询计划使用多线程处理查询，每个线程分布在可用的CPU上并同时利用每个CPU的时间资源。最大的并行度可以通过服务器上的max degree of parallelism选项或每个查询使用OPTION(MAXDOP)提示限制。

用于执行实际并行度（DOP）的结果——度量有多少线程将在给定的操作上并行——是知道执行时才能确定。在执行查询前，SQL Server 2005决定有多少个调度器未充分利用并为查询选择DOP来充分利用剩余的调度器。一旦一个DOP被选择了，直到完成，查询将使用这个选择的并行度来运行。并行查询的使用时CPU有一些偏高，但是它在elapsed time上的时间很短。如果没有其他瓶颈，类似于物理I/O等待，并行计划将会使用所有处理器的100%资源。

查询开始执行后，一个关键的因素（系统有多空闲）可以导致运行并行计划的改变。例如，如果查询运行在空闲时间，服务器可以选择使用并行计划并使用DOP为4，在4个不同的处理器上产生线程。一旦这种线程开始执行，现存的连接可以提交其他需要大量CPU的查询。在这种情况，所有不同的线程将共享可用的CPU的时间切片，导致更高的查询持续时间。

通过并行计划运行不是一定是不好的，并行可以为查询提供最快的响应时间。然而，给定查询的响应时间必须与整体的吞吐量和系统其他查询的响应进行衡量。并行查询一般最适合批处理和决策支持系统，而不适合一个事务处理环境。

[1] [2] [3] [4] [5] [6] [7]