数据库性能监控分析

1、数据库性能监控分析系统的设计与实现 刘志泉 (北京理工大学计算机科学与工程系，北京 100081)摘 要：在讨论 Oracle 体系结构和性能优化的基础上介绍了一个基于 J2EE 的数据库性能监控和分析系统(DMI)的总体设计思想及其部分 实现。关键词：性能优化；Sql Server；实时监控；JMS；RMIDesign and Realization of Database Performance Monitoring andAnalyzing SystemWANG Na, SU Hongyi, BAI Lin, WANG Xin, HAO Zizhao(Dept. of Computer

2、Science and Engineering, Beijing Institute of Technology, Beijing 100081)【Abstract】This paper presents the design and part of implementation of a database performance monitoring and analyzing system (DMI) based on J2EE with discussing the architecture and performance optimizing of Oracle.【Key words】

3、Performance optimizing; Oracle; Real-time monitoring; JMS; RMI0引言随着数据库应用的不断深入和扩大，数据库中的数据量 迅速增长，数据操作也越来越复杂，数据库工作效率逐渐下 降。因此，实施对数据库的管理维护、性能调优越来越受到 广大数据库管理员(DBA)的关注和重视。虽然目前各种数据 库产品本身也提供了大量功能强大的性能监控和调试工具， 如 Oracle 的 OEM、Performance Manager、Capacity Planer， SQL Sserverd的SQL Server Profiler 等， 来帮助数据库管理人员对数

4、据库性能进行调整、优化，但遗 憾的是，精通掌握这些工具并能通过它们来有效地分析数据 库性能状态，进而合理配置数据库以调整其性能也十分困难。 因此开发一个简单高效的数据库性能监控管理工具来辅助 DBA 对数据库进行性能分析调优成为数据库应用不断扩展的需要。针对这种情况，本文结合业界先进的数据库管理经验， 开发了一个简单、实 用、方便、安全的数据库监控管理平台。它可以有效地辅助 数据库管理人员对数据库进行性能优化，确保数据库正常、 平滑、高效地运转。可以监控 Sql server、Oracle、Sybase、DB2 等1数据库性能和优化2.总体设计2.1数据库的结构和性能优化数据库优化的目的是更改

5、系统的一个或多个组件，使其满足一个或多个目标的过程。对关系型数据库来说，优化是进行合理的资源配置，达到组件之间的均衡以改善其性能， 即增加吞吐量、提高响应时间。数据库性能优化要考虑到系统的各个组成部分。 (1)用户进程和服务器进程用户进程是 SQL 语句的提出者，服务器进程则负责执行由用户进程传递过来的SQL语句。用户进程和服务器进程是数据库性能调整的一个重要方面，尤其是当用户的数量随着时间的推移而不断增大时，建立与数据库的重复性临时连接的 Web 应用系统会导致性能下降。(2)数据库实例： 一个数据库实例是存储结构和后台进程的组合体。其中，用来存放所有数据库进程共享的数据和控制信息的存储区域

6、，当数据库一启动，就立即占有服务器的内存空间，库中高速缓存、字典高速缓存、 数据高速缓存、日志缓冲区以及大缓冲池和Java 池等组件的 大小对系统性能有极大的影响，它们直接影响磁盘 I/O 的频率 ，从而影响数据库效率。实例的另一个组成部分是后台进程，主要有系统监视进程(SMON)、进程监视进程(PMON)、数据库写进程(DBW0)、日志写进程(LGWR)和检查点进程(CKPT)。后台进程负责进行执行磁盘 I/O 操作和监视管理其它进程进行可能 多的并行操作以获取更好的性能和效率。(3)数据库由数据文件(Data files)和 Redo 日志文件(Redo Log files)组成，与其相

7、关的还有参数文件(Parameter files)、密码文件(Password files)和存档的日志文件(Archive Log files)等，它们的物理和逻辑 结构影响磁盘 I/O 效率，与系统的整体性能密切相关。总体来说，对数据库的调整优化，应使用综合全面的调整方法，从检查外部环境入手，然后逐渐深入细节。整个过程使用自顶向下的概念，首先从整体环境考虑，仔细检查可能出现问题的部分，包括服务器、磁盘、网络等。在完成了对服务器环境的调整之后，检查影响数据库的全局参数，特别注意控制 SGA 配置的初始化参数以及数据库的整体行为。然后是数据库中单个表和索引，查看能够控制表行为的存储设置，并且查

8、看这些设置适应单个对象处理需求的情况。最后是SQL语句，它们的执行效率极大地影响着数据库的性能，通过对其进行正确的调整可以使数据库的性能获得一个数量级程度的改善。应辨识哪些 SQL 语句使用最频繁，然后使用必要的工具进行调整以获得最优的执行计划。3 详细设计与实现数据库监控系统的重要组成部分数据库实时监控模块的实现。在该模块，因为监控数据要不断的反应给客户，并且反应方式大部分是通过各种图形(柱状 图、折线图等)、表格的方式表现，刷新频率较高，所以采用 APPLET 作为客户端，以保证较快的显示速度。下面以实时监控主页面的实现为例来进行阐述。3.1性能参数确定根据数据库监控系统主要通过以下几个方

9、面进行数据库整体优化(1)系统资源情况包括用户 CPU 、内存使用率、I/O系统资源调整，基于任务管理器设计。(2)数据库结构分类包括表的主键、外键、索引、锁、分割表或储存冗余数据、存储衍生数据、数据库对象的放置策略，临时表、阻塞等信息体系数据库基础信息和结构合理性；(3)数据用户情况参数分类包括用户信息 总用户数、活动用户数以及活动回话情况和事务日志信息，反应数据用户的基本信息；(4)进程参数分包括提供服务器进程总个数、dedicated 服务器 进程个数、shared 服务器进程个数、dispatcher 服务器进程个数、并行查询服务器进程个数、job queue 服务器进程个数这些信息；

10、(5)磁盘存储信息 Database Files、Redo Log Files 和 Archive Log Files 的使用情况；3.2详细分析调整优化系统资源调整优化系统资源主要针对CPU，内存和I/O系统资源及这些瓶颈对数据库性能影响分析。为每个资源瓶颈提供快速识别功能，按照SQL Server当前的配置不能支持这种负载需要对内存或者增加I/O的宽或网络通道情况调整。以最佳应用配比进行系统资源调整和分配。 CPU调整优化1）性能配比当没有额外负载，突然发生的CPU瓶颈通常由于没有查询计划，不良的配置或设计因素和不足的硬件资源所引起。在购买更快或更多处理器前，需要首先确定CPU的最大处理能

11、力带宽并查看是否他们都在使用中。数据库监控器提供一整套CPU运行情况监控功能。包括查看Processor:% Processor Time计数器是否偏高；该计数器值超过80%一般被认为是瓶颈。也可以使用sys.dm_os_schedulers视图来监视是否正在运行的任务不是0。非0的值预示有任务需要等待时间切片来运行；这个数值高表明一个CPU瓶颈的征兆。同时数据库监控器提供查看当前缓存中最耗费CPU的批或过程高级别视图。查询根据具有相同plan handle（意味着属于同一个批或过程）的语句聚合CPU的调用。如果给出的plan handle对应多个语句，将不得不继续找到在整个CPU使用中最占用

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

13、译存储过程，只有导致重编译的语句被编译-而不是整个存储过程。这减少了CPU的负载并减少了对例如COMPILE锁的资源争用。重编译可以有多种原因出发，例如：架构变化统计变化延期编译Set属性改变临时表改变存储过程创建时使用了RECOMPLIE查询提示或使用了OPTION （RECOMPILE）。通过系统监视器（PerfMon）或SQL Trace(SQL Server Profiler)来检测过多的编译和重编译。系统监视器(Perfmon)SQL Statistics对象提供了监视重编译的计数器和发送到SQL Server实例的请求类型。监视查询编译和重编译的数量以及相关联的接受的批的数量来找出

14、是否这个编译是最耗费CPU资源。理想环境下，SQL Recompilations/sec和Batch Request/sec的比值应该很低，除非用户提交了大量的单独查询。下列显示了关键的数据计数器。SQL Server: SQL Statistics: Batch Requests/secSQL Server: SQL Statistics: SQL Compilations/secSQL Server: SQL Statistics: SQL Recompilations/secSQL 跟踪如果PerfMon计数器显出了很高的重编译数量，编译将在SQL Server中占用很多的CPU资源。将

15、需要查看Profiler 跟踪并从中找到找到被重编译的存储过程。SQL Server Profiler跟踪给出重编译原因的信息。可以使用下列事件。SP:Recompile和SQL:StmtRecompile事件类指出了哪个存储过程和语句被重编译。当编译一个存储过程，一个事件为这个存储过程生成，其中每条语句将被编译。然而，当存储过程重编译时，只有导致重编译的语句被重编译（在SQL Server 2000中将是整个存储过程）。下面列出了SP:Recompile事件类更多重要的数据列。特别是EventSubClass数据列决定重编译的原因。SP:Recompile当存储过程或触发器被重编译被触发一次

16、，但不会被独立查询引发。在SQL Server 2005中，监视SQL:StmtRecompiles该事件类在所有类型的重编译中都会被触发，包括批，独立查询，存储过程和触发器。如下是我们关系的事件中关键的数据列：EventClassEventSubClassObjectID (represents stored procedure that contains this statement)SPIDStartTimeSqlHandleTextData可以通过SqlHandle和ObjectID列或其他列将这个查询的结果分组，也可以查看是否最多的重编译类型是存储过程或其他原因（例如SET选项改变等

17、）。Showplan XML For Query Compile. Showplan XML For Query Compile事件发生于Microsoft SQL Server编译或重编译一段SQL语句时。该事件有关于语句编译或重编译的信息。信息包括查询计划和过程的对象ID。捕获这些事件是有性能开销的，因为它捕获了每次编译或重编译。系统监视其中看到很高的SQL Compilations/sec计数器值，应该监视这个事件。通过这些信息，可以看到那条语句被频繁的重编译。可以使用这些信息改变这些语句的参数。这将影响重编译的数量。DMVs. 使用sys.dm_exec_query_optimizer

18、_info DMV，可以得到SQL Server花费在优化的时间。如果获取了这个DMV的2个快照，可以得到在给定的时间段内花费在查询优化的时间。特别是查看elapsed time，该时间由于优化而产生。因为优化过程的时间基本上就是用户优化操作的CPU时间（因为优化处理是CPU时间的主要部分），可以得到一个好的度量，找到那段编译时间占用了大量的CPU时间。其他包含重要信息的DMV有：sys.dm_exec_query_stats.针对查看的数据列有：Sql_handleTotal worker timePlan generation numberStatement Start Offsetsys

19、.dm_exec_query_stats.特别是plan_generation_num预示了查询编译时的次数。通过下面sql语句展示被重编译次数最多的25个存储过程进行分析处理。通过各类监测方式发现过多的编译/重编译，提供针对化调整方案。如果重编译因为SET选项改变而发生，使用SQL Server Profiler确定哪个SET选项被改编。避免在存储过程中改变SET选项。如果改变最好在连接级别设置。确认在该连接的生存周期内不要改变SET选项。在临时表上重编译的阀值比在普通表上的低。在临时表上的重编译时由于统计改变而引起，可以降临时表改为使用表变量。表变量的改变不会引起重编译。这种方法的确定是查

20、询优化器不识别表变量，因为统计不会被创建或维护表变量。这将导致没有查询计划。可以测试不同的选项，并选择最好的方法。另外使用KEEP PLAN查询提示。设置临时表的这个阀值与使用永久表相同。为避免由于改变统计而产生的重编译（例如，因为数据统计导致计划不理想），特别是KEEPFIXED PLAN查询提示。根据设置的影响，重编译可以仅因为相关正确的原因（例如，当底层表结构改变导致计划不再适用），而不根据统计的变化。如果语句引用的表的架构改变时或者表是被标记为sp_recompile的存储过程，重编译将发生。 关闭自动更新索引统计和表或视图的统计，防止由于对象的统计改变而产生的重编译。注意，通过使用这

21、种方法关闭”auto-stats”特性不是一个好的想法。这是因为查询优化器不再为在这些对象上的数据改变而敏感，将导致不良的查询计划。使用这种方法仅在尝试了所有其他选择之后，做为最后的手段。 使用对象全名模式（例如，dbo.Table1）避免重编译并避免不明确的对象。 为避免由于延期编译导致的重编译，不要混杂DML和DDL或从条件结构创建DDL，例如IF语句。 运行Database Engine Tuning Advisor(DTA)查看改变索引是否可以改善编译时间和查询的执行时间。 检查是否存储过程通过WITH RECOMPILE选项创建或使用了RECOMPILE查询提示。如果过程通过WITH

22、 RECOMPILE选项创建，在SQL Server 2005中，如果在过程中特殊的语句需要被重编译，可以利用语句级的RECOMPILE提示。这将避免在每次执行的时候对整个过程重编译，而同时允许个别语句被编译。3）效率低的查询计划调整当为一个查询生成查询计划时，SQL Server查询优化器尝试选择一个计划为查询提供最快的响应时间。注意最快的查询时间并不意味最小的I/O开销，也不意味使用最少的CPU资源-它会在各种资源中平衡。某些操作类型比其他操作对CPU更敏感。Hash操作和Sort操作扫描他们各自的输入数据。使用扫描向前读取（prefetch）时，在需要操作页面前，页面几乎都在缓存中。因此

23、可以减少或消除物理I/O操作。这使这些操作的类型将不被物理I/O所限制。与之相比，嵌套循环连接有很多索引查找，如果索引查找使用很多不同的表以至于页面不适合缓存的大小，将导致生成I/O负载。输入优化用于评估为每中操作生成不同查询计划开销的评估，可以在Showplan（EstimateRows和EstimateExecution属性）中看到结果。没有精确的评估，用于优化的主输入是有缺陷的。低效率的查询计划可以导致增加CPU的消耗。查询sys.dm_exec_query_stats是确定哪个查询累计使用CPU时间最多的有效方法。还可以选择，也可以查询sys.dm_exec_cached_plans并

24、通过使用过滤器查找可疑的类似于%Hash Match%, %Sort%这样CPU敏感的各种操作。监测到效率低的查询计划，数据库监控器提供下一优化建议。使用Database Engine Tuning Advisor调节查询，查看是否生成对修改索引的建议。 检查有问题的评估。编写的查询中使用的更有限制性的WHERE从句是否合适？无限制条件的查询是资源敏感的。在查询中涉及的表上运行UPDATE STATISTICS，检查是否还有这种问题。是否查询使用的构造导致优化器不能精确的评估？考虑是否可以将查询修改为其他的方法，避免这种问题。如果不能修改架构或查询，SQL Server 2005有一个新的查询

25、计划特性，允许将指定查询提示添加到满足某种文本的查询中。这可以用在独立查询中，也可以用在存储过程内。例如OPTION(OPTIMIZE FOR)这样的提示允许你影响评估而忘记所有列出的潜在计划。其他的提示，类似OPTION(FORCE ORDER)或 OPITON(USE PLAN)允许你改变控制查询计划的程度。4）内部查询的并行当为一个查询生成执行计划时，SQL Server优化器尝试为该查询选择最快的相应计划。如果查询的开销超过了在cost threshold for parallelism选项中指定的值，并行不会被禁用，优化器尝试生成一个可以用于并行的计划。并行查询计划使用多线程处理查询

26、，每个线程分布在可用的CPU上并同时利用每个CPU的时间资源。最大的并行度可以通过服务器上的max degree of parallelism选项或每个查询使用OPTION(MAXDOP)提示限制。用于执行实际并行度（DOP）的结果度量有多少线程将在给定的操作上并行是知道执行时才能确定。在执行查询前，SQL Server 2005决定有多少个调度器未充分利用并为查询选择DOP来充分利用剩余的调度器。一旦一个DOP被选择了，直到完成，查询将使用这个选择的并行度来运行。并行查询的使用时CPU有一些偏高，但是它在elapsed time上的时间很短。如果没有其他瓶颈，类似于物理I/O等待，并行计划将

27、会使用所有处理器的100%资源。查询开始执行后，一个关键的因素（系统有多空闲）可以导致运行并行计划的改变。例如，如果查询运行在空闲时间，服务器可以选择使用并行计划并使用DOP为4，在4个不同的处理器上产生线程。一旦这种线程开始执行，现存的连接可以提交其他需要大量CPU的查询。在这种情况，所有不同的线程将共享可用的CPU的时间切片，导致更高的查询持续时间。并行可以为查询提供最快的响应时间。然而，给定查询的响应时间必须与整体的吞吐量和系统其他查询的响应进行衡量。并行查询一般最适合批处理和决策支持系统，而不适合一个事务处理环境。内部查询的并行问题通过SQL Server:SQL Statistics

28、 Batch Requests/sec 计数器检测。因为在考虑使用并行计划前，查询必须评估开销超过为并行配置设置的开销阀值（默认被设置为5），服务器每秒处理的批小于运行在并行计划中的批。运行很多并行查询的服务器一般配置为较小的每秒批请求数（例如，小于100的值）。DMVs在运行的服务器上，可以使用SQL查询确认在给定会话中是否可以并行运行任何活动的请求。可以搜索符合运行在并行的计划。这可以通过搜索缓存的计划来查看如果关系操作符有Parrallel属性为非零的值。这些计划也许可以不运行在平行中，但是他们如果系统不忙，他们也适合这样做。一般来说，查询的持续时间长于CPU时间总量，因为一些时间花费在

29、等待资源上例如锁或物理I/O。查询使用CPU时间长于持续时间的唯一场景是当查询运行在并行计划例如多线程并发使用CPU。注意并不是所有并行查询将证明这种行为（CPU时间大于持续时间）。任何运行在并行计划的查询被查询优化器认为是成本昂贵的，并会超过并行阀值，默认为5（粗略的是在涉及的机器上5秒执行一次）。任何通过上述方法确认的查询都是以后要调节的候选者。使用Database Engine Tuning Advisor查看是否任何索引改变，改变索引视图或分区改变能减少查询的开销 检查实际值和评估集的重要不同因为评估集在评估查询开销中是重要因素。如果找到重要的不同：如果auto create stat

30、istics数据库设置被禁用，确认在Showplan输出的Warnings列中没有MISSING STATS项。尝试在关闭评估的表上运行UPDATE STATISTICS。验证查询没有使用优化器无法精确评估的查询构造，例如多语句表值函数或CLR函数，表值或Transact-SQL变量比较（参数比较是可以的）。评估是否可以使用不同的Transact-SQL语句或表达式将查询写的更有效率5）拙劣游标使用调整SQL Server 2005之前的SQL Server 版本仅支持在每个连接上有单个活动的操作。一个查询正在执行或有了结果等待发送到客户端时将被认为是活动的。在一些情形中，客户端应用程序也许需

31、要从结果中读取并向SQL Server提交其他基于刚刚从结果集中读取的行的查询。这在默认的结果集中是不能实现的，因为还有其他等待的结果。一般的解决方法是改变连接属性是用服务器端游标。当使用服务器端游标，数据库客户端软件(OLE DB提供者或ODBC驱动)显然会封装客户端请求在特殊的扩展存储过程中，例如sp_cursoropen，sp_cursorfetch等等。这提到了API游标（而不是TSQL游标）。当用户执行查询，查询文本通过sp_cursoropen被发送到服务器，请求读取从sp_cursorfetch指示服务器进发送某些数量的行。通过控制获取行的数量，可以为ODBC驱动或OLE DB提

32、供者缓存行。这阻止发生服务器等待客户端都区所有发送的行的情形。因此，服务器可以在这个连接上接受新的请求。一次性打开游标并获取1行（或少量行）的应用程序能被网络延时的网络瓶颈影响，特别是在广域网（WAN）。在有快速网络并有不同用户连接时，处理很多游标请求的开销变得更重要。因为开销来自于游标位置的变化来适应在结果集上的位置改变，预请求的处理开销，类似的处理，服务器处理1个请求返回100行必处理100不同请求相同的100行但是每次1行更有效率。通过考虑SQL Server:Cursor Manager By Type Cursor Requests/Sec计数器，可以通过这个性能计数器知道有多少游标

33、在系统中使用。系统还有很高的CPU利用率，因为小量的读取通常会有每秒数百个游标请求。DMVs可以用于测定使用API游标（不是TSQL游标）连接获取一行使用的缓存大小。它对于大的获取缓存更有效。使用包括RPC：Completed事件类的跟踪用于搜索sp_cursorfetch语句。第4个参数的值是通过获取返回的行数。请求返回的最大行数是被指定为与RPC:Starting事件类关联的参数。针对拙劣游标使用调整提供一下方案确定游标是完成操作的最佳方法或是否基于集合这种更有效的操作是可行的。 当连接到SQL Server 2005，考虑使用多活动结果集(MARS) 参考使用的API文档决定如何指定游标

34、的获取缓存大小：ODBC - SQL_ATTR_ROW_ARRAY_SIZEOLE DB IRowset:GetNextRows or IRowsetLocate:GetRowsAt3.2.1.2内存调整优化内存压力表示当可用内存数量受到限制。识别SQL Server何时运行在内存压力下将帮助排除内存相关的问题。SQL Server依赖于不同类型的内存压力特征也不一样。下表汇总了内存压力类型，和他们潜在的原因。在所有的情况下，可以更多的会见到超时或显示的内存不足错误消息。Windows有通知的机制 如果物理内存运行在过高或过低的情况下。SQL Server在他的内存管理决策中使用这种机制。一般

35、排错的步骤显示在表。内存压力自身不会预示问题。内存压力是需要的，但时不是为服务器以后遇到内存错误的充分条件。在内存压力下工作将被任务是服务器的正常操作。然而内存压力的征兆可以于是服务器运行已经接近设计容量并且潜在存在内存不足的错误。在正常运行情况下，这些信息将作为基线决定以后内存不足的原因。1）外部物理内存压力检查Physical Memory节的Available项的值。如果可用内存总数很低，这表现了有外部内存压力。这个准确值依赖于很多因素，可以在当这个值降低到50-100MB开始查找问题。当这个总数小于10MB时，外部内存压力将表现得很明显。相同信息也可以通过Memory: Availab

36、le Bytes计数器获取。如果存在外部内存压力并且看到了内存相关的错误，需要确认在系统中主要的内存消耗者。为了这个，考虑Process: Working Set性能计数器或在任务管理器中Process栏中的Mem Usage列，找到最大的内存消耗者。系统中所有使用的物理内存可以通过汇总下列计数器获取。Process 对象，每个进程的 Working Set计数器Memory 对象系统的Cache Bytes计数器未分页池的Pool Nonpaged Bytes 计数器Available Bytes (等于任务管理其中的Available 值)如果没有外部压力，Process: Private

37、 Bytes计数器或在任务管理器中虚拟内存将接近工作集的大小（Process: Working Set或任务管理器中的Mem Usage），意味着我们没有内存用于分页了。注意任务管理器中的Mem Usage列和相应的性能计数器不能计算通过AWE分配的内存。这样如果使用AWE，信息将是不完整的。这种情况下，你需要考虑在SQL Server内分配的内存来获取完整的信息。可以使用sys.dm_os_memory_clerks DMV找到SQL Server通过AWE机制分配了多少内存。只有当前缓存池（MEMORYCLERK_SQLBUFFERPOOL类型）使用这种机制并且只能是在使用AWE功能时。通

38、过识别和除去主要物理内存使用者（如果有可能）和/或 通过添加更多的内存的方法解除外部内存压力一般可以解决与此相关的内存问题。2）外部虚拟内存压力需要确定是否页面文件为当前内存的分配能提供足够的空间。为了检查，可以打开任务管理器中的性能视图，并检查Commit Charge节。如果Total接近于Limit则说明可以被提交的最大数量内存没有扩展页面的空间。注意任务管理器中的Commit Charge Total指出潜在使用的页面文件，而不是实际使用值。实际使用的页面文件将增加物理内存压力。同样可以通过下列技术起获取相关信息：Memory: Commit Limit, Paging File: %

39、Usage, Paging File: %Usage Peak。可以通过每个进程的Process: Working Set减去Process Private Bytes计数器来评估内存总数。如果Paging File: %Usage Peak（或Peak Commit Charge）很高，检查系统日志中是否有指出页面文件增长或通知“running low on virtual memory”的信息。可能需要增加页面文件大小。High Paging File: %Usage指出物理内存超过要提交的值并也要考虑外部物理内存压力（大量的内存需求，没有足够的RAM）。3）内部物理内存压力内部内存压力来

40、自于SQL Server自身，应首先通过检查在缓存分布中的异常来考虑在SQL Server内存分布。通常在SQL Server中缓存会占用最多提交的内存。为了确定在缓存池中的内存总数，我们可以使用DBCC MEMROYSTATUS命令。在Buffer Counts节，可以找到Target值。下列输出显示了在服务器达到正常负载时DBCC MEMORYSTATUS的结果。Target是被定期的重新计算的来反映内存的低或高。在常规服务负载下target页面过低可能预示出现了外部内存压力。如果SQL Server占用了大量的内存(通过Process: Private Bytes或 任务管理器中Mem

41、Usage 列显示)，请查看是否Target的数值。注意，如果启用AWE，还要从sys.dm_of_memory_clerks或DBCC MEMORYSTATUS输出计算AWE分配的总量。考虑上面的示例（没有启用AWE），Target*8KB=1.53GB，而服务器的Process: Private Bytes大约是1.62GB或缓存池用SQL Server占用了94%的内存。注意，如果服务器没有过载，Target是应该超过Process: Private Bytes性能计数器报告的数量。如果Target过低，但是服务器的Process: Private Bytes或 任务管理器中Mem Us

42、age 值很高，从缓存池外使用内存的组件带来的内部内存压力。被加载到SQL Server进程中的组件，例如COM对象，连接服务器，扩展存储过程，SQLCLR或其他会从缓存池外占用内存。如果不使用SQL Server内存接口，将没有方法跟踪组件在缓存池外占用的内存。适用于SQL Server内存管理机制的组件使用在缓存池中分配很少的内存。如果分配的大于8KB，这些组将将通过多页分配器借口使用缓存池外的内存。如果通过多页分配器分发了过大的内存( 100-200MB或更多)，看到了通过多页分配器 分发的大量内存，检查服务器的配置并尝试使用之前或后续的查询确定哪个组件占用的最多的内存。Target值低

43、，但是在百分比上它占用了最多的内存，请在前面部分中查找描述外部内存压力的部分(External Physical Memory Pressure)，或查看服务器内存配置参数。设置了max server memory 和/或min server memory，应该用这些值和Target值进行比较。max server memory选项限制了在缓存池中占用内存的最大值，而服务器还可以占用其他的部分。min server memory选项告诉服务器当小于该值时不能释放缓存池的内存。如果Target小于min server memory设置并且服务器没有过载，这可能预示服务器遇到了外部内存压力并且不能

44、获得这个设置大小的内存。它也可能预示着从内部组件的内存压力，就像上面描述的那样。Target 数值不能超过max server memory选项的设置。相对于Stolen和Target页面的高百分比(>75-80%)预示着内部内存压力。更多关于服务器组件内存分配的信息可以使用sys.dm_of_memory_clerks DMV获取。查询将Buffer Pool与通过单页分配器提供给组件的内存视为不同的部分。通过下列查询可以确定在缓存池中耗费内存最多的10个组件（通过单页分配器）。4）高速缓存和内存压力SQL Server 2005与SQL Server 2000的高速缓存设计上有一些细

45、微的不同，其中之一就是统一了高速缓存的框架。为了从高速缓存中删除最近很少使用的项，该框架实现了一套时钟算法。现在它使用2支不同的时钟指针，一个是内部时钟指针，一个是外部时钟指针。内部时钟指针控制与其他高速缓存相关的缓存大小。当框架预测到高速缓存要使用到尽头是它开始移动。当SQL Server总体上陷入内存压力时，外部时钟指针开始移动。外部时钟指针的移动可以导致外部和内部的内存压力。在内部和外部内存压力时不会混乱的移动外部时钟和内部时钟。关于时钟移动的信息可以通过sys.dm_os_memory_cache_clock_hands DMV显示。每个高速缓存项在内部和外部时钟指针都有不同的行。看到

46、rounds count和removed all rounds count增加，说明服务器遇到内部/外部内存压力。通过sys.dm_os_ring_buffers的ring buffers DMV获取。每个ring buffer保留了之前几次某种类型的通知。指定ring buffer的详细信息将在下面描述。使用从资源监视器的通知识别内存改变的状态。在内部，SQL Server有一个监视不同内存压力的架构。当内存状态改变，资源监视器任务生成一个通知。这个通知用于内部组件根据内存状态调整它们内存使用并通过sys.dm_os_ring_buffers DMV来暴露，如下列代码所示。可以减少服务器收到

47、的低物理内存的通知。查看内存总量（KB为单位）。使用SQL Server的XML能力来查询这些信息。上面收到了低内存的通知，缓存池重新计算target。如果缓存池中心的提交比当前的提交缓存还小，缓存池将开始收缩直到外部内存压力被移除。当监测到内部内存压力时，为组件在缓存池分配内存的低内存通知将被打开。打开低内存通知允许从使用缓存池的高速缓存和其他组件中回收页面。内部内存压力可以通过调整max server memory选项或当stolen页面与缓存池的比例超过80%时触发。内部内存压力通知(Shrink)能通过使用查询ring buffer的调用来发现。5）内部虚拟内存压力VAS的占用可以使用

48、sys.dm_os_virtual_address_dump DMV来跟踪。VAS汇总可以使用下列视图来查询。如果最大可用区域小于4MB，可能遇到了VAS压力。SQL Server 2005监视和响应VAS压力。SQL Server 2000不会监视从VAS带来的压力，但是当出现虚拟内存不足错误是，它会清理高速缓存。数据库监控器提供内存压力调整方案和优化配置方案1.验证是否服务器运行在外部内存压力。如果出现外部内存压力，开始收集性能计数器：SQL Server: Buffer Manager, SQL Server: Memory Manager 2.确认内存配置参数(sp_configure

49、)， min memory per query，min/max server memory，awe enabled和 Lock Pages in Memory 权利。观察不正常的值。纠正配置。提供为SQL Server 2005增加内存的理由。 4.检查所有可能影响服务器的非默认sp_configure参数。 5.检查内部内存压力。 6.当你见到内存错误消息时，观察DBCC MEMORYSTATUS输出和改变的方法。 7.检查负载（并发会话数量，并发执行查询数量）。3.2.1.3 I/O调整优化SQL Server性能非常依赖于I/O系统。除非数据库适合物理内存，SQL Server经常地会有

50、数据库页面进出缓存池。这样就发生了实质的I/O流量。同样，在事务被明确的提交前，日志记录需要写入磁盘。SQL Server为各种目的可以使用tempdb，例如存储中间结果，排序，保持行的版本或其他。所以好的I/O系统对于SQL Server性能非常重要。除了当事务需要回滚时，访问日志文件是非常频繁的，而包括访问数据文件，包括tempdb，是随机访问的。所以作为一个通常的规则，为获取更好的性能，你最好将日志文分布不到不同的物理磁盘。本文重点于不是在如何配置你的I/O设备，而是描述识别I/O瓶颈的方法。一旦识别了I/O瓶颈，需要重新配置你的I/O系统。如果有一个慢速的I/O子统，用户会遇到性能问题

51、，例如很长的响应时间，任务由于超时而中断。数据库监控系统提供使用下列性能参数来识别I/O瓶颈。 PhysicalDisk Object: Avg. Disk Queue Length表现在采样周期中所选择的物理磁盘队列中的物理读和写平均请求数量。如果你的I/O系统过载，更多的读/写操作将被等待。如果在很少使用SQL Server时，磁盘队列长度经常超过2，这样你可能遇到了I/O瓶颈 Avg. Disk Sec/Read 是平均每次从磁盘读取数据的时间小于10 ms 很好在 10 - 20 ms 之间- 正常在20 - 50 ms 之间- 缓慢，需要注意大于 50 ms 严重的I/O 瓶颈Avg

52、. Disk Sec/Write 是平均每次从磁盘读取数据的时间。请引用之前的指导数据。 Physical Disk: %Disk Time 是所选磁盘驱动器用于服务于读或写请求的总共时间的百分比。一般推荐是如果该值大于50%，则表现为I/O瓶颈。 Avg. Disk Reads/Sec 表现磁盘上读操作的速度。你需要确认该值小于85%的磁盘设计能力。磁盘访问时间指数高于能力的85%。 Avg. Disk Writes/Sec表现在磁盘上写操作的速度。确认该值小于85%的磁盘设计能力。磁盘访问时间指数高于能力的85%。也可以通过考察锁等待来识别I/O瓶颈。当数据页通过读或写访问并且在缓存池中页

53、不可用时，这些锁等待占用了大量的物理I/O等待。当页面没有在缓存池中找到时，一个异步I/O请求被发出，I/O的状态是被选中的。如果I/O已经完成，工作进程处理正常。否则，依赖于请求的类型，它会等待PAGEIOLATCH_EX 或 PAGEIOLATCH_SH。当I/O完成时，工作者被放置到可用队列中。从I/O完成到工作者被实际的调度的时间在signal_wait_time_ms列说明。如果你得waiting_task_counts and wait_time_ms偏离正常值太多，可以识别为I/O问题。为了这样，通过使用性能计数器和关键DMV建立正常时运行时的性能基线就十分重要。wait_typ

54、es可以识别是否I/O系统处在瓶颈状态，但是他们不能提供任何关于物理磁盘遇到问题的信息。可以使用下列DMV查询找到当前等待的I/O请求。可以周期性的执行这些请求检查I/O系统的健康情况，并找到涉及I/O瓶颈的物理磁盘。下面是示例的输出。它展示当时在给定的数据库上有3个未决定的I/O 。可以使用database_id 和 file_id 来找到映射的物理磁盘文件。io_pending_ms_ticks 表现了所有等待在未决定队列中的个别的I/O。Database_id File_Id io_stall io_pending_ms_ticks scheduler_address-6 1 10804

55、 78 0x0227A0406 1 10804 78 0x0227A0406 2 101451 31 0x02720040在通过数据库监控系统识别到I/O瓶颈后提供优化方案：检查SQL Server的内存配置。如果SQL Server配置的内存不足，将导致更多的I/O开销。 为当前的磁盘阵列添加更多的物理驱动器和/或使用更快的磁盘代替当前的磁盘。这帮助提升读写访问时间。但是添加的磁盘数量不能比当前I/O控制器所支持的数量大。 添加快速或额外的I/O控制器。考虑为当前的控制器添加更多的缓存（如果有可能） 考察执行计划并查看那个计划占用了更多的I/O。这样可以找到更好的查询进化（例如，索引）可以减

56、少I/O。如果缺少索引，你可以运行Database Engine Tuning Advisor找到缺失的索引。3.2.2数据结构调整优化数据库结构调整优化的也是实现避免磁盘I/O瓶颈、减少CPU利用率和减少资源竞争重要作用。从基本表设计、扩展设计和数据库表对象放置等角度进行分析。在基本表设计中，表的主键、外键、索引设计占有非常重要的地位，但系统设计人员往往只注重于满足用户要求，而没有从系统优化的高度来认识和重视它们。实际上，它们与系统的运行性能密切相关。从系统数据库优化角度必须要进行合理调整和优化。 3.2.2.1主外键调整优化主键(Primary Key)：主键被用于复杂的SQL语句时，频繁

57、地在数据访问中被用到。一个表只有一个主键。主键应该有固定值（不能为Null或缺省值，要有相对稳定性），不含代码信息，易访问。把常用的列作为主键才有意义。短主键最佳（小于25bytes），主键的长短影响索引的大小，索引的大小影响索引页的大小，从而影响磁盘I/O。主键分为自然主键和人为主键。自然主键由实体的属性构成，自然主键可以是复合性的，在形成复合主键时，主键列不能太多，复合主键使得Join作复杂化、也增加了外键表的大小。人为主键是，在没有合适的自然属性键、或自然属性复杂或灵敏度高时，人为形成的。人为主键一般是整型值，没有实际意义，也略微增加了表的大小；但减少了把它作为外键的表的大小。 外键（Foreign Key）：外键的作用是建立关系型数据库中表之间的关系（参照完整性），主键只能从独立的实体迁移到非独立的实体，成为后者的一个属性，被称为外键。 索引(Index)：利用索引优化系统性能是显而易见的，对所有常用于查询中的Where子句的列和所有用于排序的列创建索引，可以避免整表扫描或访问，在不改变表的物理结构的情况下，直接访问特定的数据列，这样减少数据存取时间；利用索引可以优化或排除耗时的分类*作；把数据分散到不同的页面上，就分散了插入的数据；主键自动建立了唯一索引，因此唯一索引也能确保数据的唯一性（即实体完整性）