SQL Server 2005性能排错（1）

　　简介

　　很多客户偶尔会遇到sql server 数据库性能下降。原因可能涉及从不良好的数据库设计到不正确的负载配置。作为一个管理员，你应该预先阻止或最小化问题，并当问题发生时，诊断原因并尽可能的做出正确的操作来解决问题。这片白皮书所述的问题通常来源于microsoft® corporation 的customer support service（css or pss）部门所遇到的，因为将所有可能的问题都详尽的分析是不合实际的。我们提供了按部就班的指导，通过使用可用的工具例如sql server profiler，system monitor和在sql server 2005中新的dynamic management view来为一般的性能问题诊断和排错。

　　目标

　　这篇文章的主要目标是提供一套常规的方法通过使用公开的工具在一般的客户场景中诊断和排错sql server性能问题。

　　sql server 2005在用户支持上有了很大的提高。内核层（sql-os）被重新架构过，内部结构和统计数据通过动态管理视图(dmvs)暴露为关系型行集。sql server 2000通过像sysprocesses这样的系统表暴露一些信息，但是有时你需要将sql server进程内存映射为物理文件并从中抽取内部结构的相关信息。这里有2个主要的问题。第一，客户不能总是提供物理映射文件，因为文件的尺寸原因，并且这个过程很耗时。第二，这将花费更长的时间诊断问题，因为文件必须传回microsoft corporation来分析。

　　这带给我们本文的第二个目标，展示dmvs的优点。dmvs通过除去大多数情况下需要的生成和分析物理映射步骤可以加速调试的过程。本文尽可能的提供了和sql server 2000中同样问题的比较。dmvs提供为获取关键系统信息的简单而熟悉的界面。这些信息也可以用于监视目的，警告管理员潜在的问题。或者也可以被周期性的收集为以后的分析所用。

　　方法

　　这里有很多降低sql server速度的原因。我们使用下列3个主要症状来诊断问题。

　　◆资源瓶颈: cpu，内存，和i/o瓶颈是在本文中主要涉及的。这里我们不考虑网络因素。对每种资源瓶颈，我们会描述如何识别问题并阐述可能的原因。例如，内存瓶颈可以导致过多的分页，最后影响性能。

　　◆tempdb 瓶颈:因为每个sql server 实例只有一个tempdb，这可以产生性能和磁盘空间的瓶颈。不好的应用程序在过多的ddl和dml操作会使tempdb过载。这导致其他在这台服务器上运行的不相关的应用程序运行缓慢或失败。

　　◆缓慢运行的用户查询:现有的查询性能下降或新的查询显示比预期时间更长。这可能有很多原因。例如：

　　◆改变统计信息可以导致现有查询的较差的查询计划。

　　◆制表扫描，降低查询性能。

　　◆即使资源利用正常由于阻塞也可以导致应用程序运行缓慢。

　　过多的阻塞可能是由于不良的应用程序设计或架构设计或者是选择了错误的事务隔离级别的原因。

　　这些症状的原因不需要每个都独立出来。不良的查询计划选择可以使系统资源加重并导致整体性能的下降。所以，如果大表缺失的有用的索引，或查询优化器没有选择它，这样不仅导致查询缓慢，也会导致将不需要数据页读取到内存（buffer pool）中在缓存中存储，这样会加重i/o子系统的压力。同样的，频繁运行查询的重编译可以导致cpu的压力。

　　资源瓶颈

　　接下来的部分讨论了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瓶颈的征兆。你可以用下列查询列出所有的调度器并产看等待运行的任务数量。

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

　　本节剩下的部分将讨论一些其他的在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”

　　sql 跟踪

　　如果perfmon计数器显出了很高的重编译数量，编译将在sql server中占用很多的cpu资源。我们将需要查看profiler 跟踪并从中找到找到被重编译的存储过程。sql server profiler跟踪给出我们重编译原因的信息。你可以使用下列事件。

　　sp:recompile和sql:stmtrecompile事件类指出了哪个存储过程和语句被重编译。当你编译一个存储过程，一个事件为这个存储过程生成，其中每条语句将被编译。然而，当存储过程重编译时，只有导致重编译的语句被重编译（在sql server 2000中将是整个存储过程）。下面列出了sp:recompile事件类更多重要的数据列。特别是eventsubclass数据列决定重编译的原因。sp:recompile当存储过程或触发器被重编译被触发一次，但不会被独立查询引发。在sql server 2005中，监视sql:stmtrecompiles也非常有用，该事件类在所有类型的重编译中都会被触发，包括批，独立查询，存储过程和触发器。如下是我们关系的事件中关键的数据列：

　　◆eventclass

　　◆eventsubclass

　　◆objectid (represents stored procedure that contains this statement)

　　◆spid

　　◆starttime

　　◆sqlhandle

　　◆textdata

　　更多信息，请见sql server 联机丛书中“sql:stmtrecompile event class”。

　　如果你有保存的跟踪文件，你可以使用下列查询查看所有捕捉的重编译事件。

　　事件类37是 sp:recompile， 75 是 cursorrecompile， 166是sql:stmtrecompile

　　你可以通过sqlhandle和objectid列或其他列将这个查询的结果分组，也可以查看是否最多的重编译类型是存储过程或其他原因（例如set选项改变等）。

　　showplan xml for query compile.

　　showplan xml for query compile事件发生于microsoft sql server编译或重编译一段sql语句时。该事件有关于语句编译或重编译的信息。信息包括查询计划和过程的对象id。捕获这些事件是有性能开销的，因为它捕获了每次编译或重编译。如果你在系统监视其中看到很高的sql compilations/sec计数器值，你应该监视这个事件。通过这些信息，你可以看到那条语句被频繁的重编译。你可以使用这些信息改变这些语句的参数。这将影响重编译的数量。

　　dmvs.

　　当你使用sys.dm_exec_query_optimizer_info dmv，你可以得到sql server花费在优化的时间。如果获取了这个dmv的2个快照，你可以得到在给定的时间段内花费在查询优化的时间。

　　特别是查看elapsed time，该时间由于优化而产生。因为优化过程的时间基本上就是用户优化操作的cpu时间（因为优化处理是cpu时间的主要部分），你可以得到一个好的度量，找到那段编译时间占用了大量的cpu时间。

　　其他包含有用信息的dmv有：

　　sys.dm_exec_query_stats.

　　你希望查看的数据列有：

　　◆sql_handle

　　◆total worker time

　　◆plan generation number

　　◆statement start offset

　　更多信息请查看sql server 联机丛书相关主题

　　sys.dm_exec_query_stats.

　　特别是plan_generation_num预示了查询编译时的次数。下面是示例给你展示了被重编译次数最多的25个存储过程。

　　更多信息请见batch compilation, recompilation, and plan caching issues in sql 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时间最多的有效方法。

　　还可以选择，也可以查询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的时间切片，导致更高的查询持续时间。

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

　　检测

　　内部查询的并行问题可以通过下列方法检测。

　　系统监视器(perfmon)

　　考虑sql server:sql statistics – batch requests/sec 计数器，并查看sql server联机丛书中的“sql statistics object”获取更多信息。

　　因为在考虑使用并行计划前，查询必须评估开销超过为并行配置设置的开销阀值（默认被设置为5），服务器每秒处理的批小于运行在并行计划中的批。运行很多并行查询的服务器一般配置为较小的每秒批请求数（例如，小于100的值）。

　　dmvs

　　在运行的服务器上，你可以使用下列查询确认在给定会话中是否可以并行运行任何活动的请求。

　　通过这些信息，查询的文本可以通过使用sys.dm_exec_sql_text轻松获取，而查询计划可以使用sys.dm_exec_cached_plan获取。

　　你也可以搜索符合运行在并行的计划。这可以通过搜索缓存的计划来查看如果关系操作符有parrallel属性为非零的值。这些计划也许可以不运行在平行中，但是他们如果系统不忙，他们也适合这样做。

　　一般来说，查询的持续时间长于cpu时间总量，因为一些时间花费在等待资源上例如锁或物理i/o。查询使用cpu时间长于持续时间的唯一场景是当查询运行在并行计划例如多线程并发使用cpu。注意并不是所有并行查询将证明这种行为（cpu时间大于持续时间）。

　　解决

　　任何运行在并行计划的查询被查询优化器认为是成本昂贵的，并会超过并行阀值，默认为5（粗略的是在涉及的机器上5秒执行一次）。任何通过上述方法确认的查询都是以后要调节的候选者。

　　◆使用database engine tuning advisor查看是否任何索引改变，改变索引视图或分区改变能减少查询的开销

　　◆检查实际值和评估集的重要不同因为评估集在评估查询开销中是重要因素。如果找到重要的不同：

　　如果auto create statistics数据库设置被禁用，确认在showplan输出的warnings列中没有missing stats项。

　　尝试在关闭评估的表上运行update statistics。

　　验证查询没有使用优化器无法精确评估的查询构造，例如多语句表值函数或clr函数，表值或transact-sql变量比较（参数比较是可以的）。

　　◆评估是否可以使用不同的transact-sql语句或表达式将查询写的更有效率

　　拙劣游标使用

　　sql server 2005之前的sql server 版本仅支持在每个连接上有单个活动的操作。一个查询正在执行或有了结果等待发送到客户端时将被认为是活动的。在一些情形中，客户端应用程序也许需要从结果中读取并向sql server提交其他基于刚刚从结果集中读取的行的查询。这在默认的结果集中是不能实现的，因为还有其他等待的结果。一般的解决方法是改变连接属性是用服务器端游标。

　　当使用服务器端游标，数据库客户端软件(ole db提供者或odbc驱动)显然会封装客户端请求在特殊的扩展存储过程中，例如sp_cursoropen，sp_cursorfetch等等。这提到了api游标（而不是tsql游标）。当用户执行查询，查询文本通过sp_cursoropen被发送到服务器，请求读取从sp_cursorfetch指示服务器进发送某些数量的行。通过控制获取行的数量，可以为odbc驱动或ole db提供者缓存行。这阻止发生服务器等待客户端都区所有发送的行的情形。因此，服务器可以在这个连接上接受新的请求。

　　一次性打开游标并获取1行（或少量行）的应用程序能被网络延时的网络瓶颈影响，特别是在广域网（wan）。在有快速网络并有不同用户连接时，处理很多游标请求的开销变得更重要。因为开销来自于游标位置的变化来适应在结果集上的位置改变，预请求的处理开销，类似的处理，服务器处理1个请求返回100行必处理100不同请求相同的100行但是每次1行更有效率。

　　检测

　　你可以使用下列方法为拙劣游标使用排错。

　　系统监视器(perfmon)

　　通过考虑sql server:cursor manager by type – cursor requests/sec计数器，你可以通过这个性能计数器知道有多少游标在系统中使用。系统还有很高的cpu利用率，因为小量的读取通常会有每秒数百个游标请求。这里没有特殊的计数器告诉你关于获取的缓存大小。

　　dmvs

　　接下来的查询可以用于测定使用api游标（不是tsql游标）连接获取一行使用的缓存大小。它对于大的获取缓存更有效，例如100行。

　　sql 跟踪

　　使用包括rpc：completed事件类的跟踪用于搜索sp_cursorfetch语句。第4个参数的值是通过获取返回的行数。请求返回的最大行数是被指定为与rpc:starting事件类关联的参数。

　　解决

　　◆确定游标是完成操作的最佳方法或是否基于集合这种更有效的操作是可行的。

　　◆当连接到sql server 2005，考虑使用多活动结果集(mars)

　　◆参考你使用的api文档决定如何指定游标的获取缓存大小：

　　odbc - sql_attr_row_array_size

　　ole db – irowset::getnextrows or irowsetlocate::getrowsat

　　内存瓶颈

　　这部分给出了低缓存的条件和对不同内存错误诊断方法，可能的原因和排错方法。

　　背景

　　引用不同的内存资源通过使用简单的术语内存。但是却有一些内存资源类型，对于理解和区分特殊的内存资源这是很重要的。

　　虚拟地址空间和物理内存

　　在microsoft windows®，每个进程都有自己的虚拟地址空间(vas)。进程可用所有虚拟地址有vas的大小决定。vas的大小依赖于架构（32位或64位）和操作系统。在排错的上下文中，理解虚拟地址空间使用内存资源，了解应用程序可以超出vas即使在64位平台只要物理地址可能一直可用，这些很重要。

　　更多有关于虚拟地址空间，请查看sql server联机丛书中“process address space”和msdn中的virtual address space。

　　windows地址扩展和sql server

　　windows地址扩展(awe)是允许32位应用程序跨越32位地址限制操作内存的api。awe机制技术上不需要64位平台。然而它出现了。内存页通过awe机制涉及在64位平台上的锁定页。

　　在32位和64位平台上，内存通过awe机制分配不能分页出界。这可以有益于应用程序（这是在64位平台上使用awe机制的原因）。这也影响了系统和其他应用程序可用ram总数，这可能是有害的影响。因为这个原因为了使用awe，lock pages in memory权利必须分配该运行sql server的账号。

　　从排错的角度来看，要点是sql server缓存池使用awe映射内存；然而，只有数据库（hash过的）页面可以利用awe分配内存。通过awe机制内存分配将不会在任务管理器或在process: private bytes性能计数器中看到。你需要使用sql server特殊的计数器或动态管理视图来获取这些信息。

　　更多关于awe映射内存的信息，请在sql server联机丛书中查看“managing memory for large databases” 和 “memory architecture”以及msdn中的large memory support

　　下列表汇总了不同sql server 2005支持的最大内存（注意特殊的sql server版本或windows可以有不同支持内存的限制）

　　表1

　　[1]更多有关于启动参数，请参考sql server联机丛书中“using awe” 文章。

最大的网站源码资源下载站，