当前位置:首页 >> 信息与通信 >>

数据库性能监控分析


数据库性能监控分析系统的设计与实现
刘志泉 (北京理工大学计算机科学与工程系,北京 100081)

摘 要:在讨论 Oracle 体系结构和性能优化的基础上介绍了一个基于 J2EE 的 数据库性能监控和分析系统(DMI)的总体设计思想及其部分 实现。

关键词:性能优化;Sql Server;实时监控;JMS;RMI


Design and Realization of Database Performance Monitoring and

Analyzing System WANG Na, SU Hongyi, BAI Lin, WANG Xin, HAO Zizhao

(Dept. of Computer Science and Engineering, Beijing Institute of Technology, Beijing 100081)

【Abstract Abstract】This paper presents the design and part of implementation Abstract of a database performance monitoring and analyzing system (DMI) based on J2EE with discussing the architecture and performance optimizing of Oracle. 【Key words Key words】Performance optimizing; Oracle; Real-time monitoring; JMS; RMI

0 引言
随着数据库应用的不断深入和扩大, 数据库中的数据量 迅速增长, 数据操作

也越来越复杂,数据库工作效率逐渐下 降。因此,实施对数据库的管理维护、 性能调优越来越受到 广大数据库管理员(DBA)的关注和重视。 虽然目前各种数据 库产品本身也提供了大量功能强大的性能监控和调试工具, 如 Oracle 的 OEM、 Performance Manager、Capacity Planer, SQL Sserverd的SQL Server Profiler 等, 来帮助数据库管理人员对数据库性能进行调整、优化,但遗 憾的是,精通 掌握这些工具并能通过它们来有效地分析数据 库性能状态,进而合理配置数据 库以调整其性能也十分困难。 因此开发一个简单高效的数据库性能监控管理工 具来辅助 DBA 对数据库进行性能分析调优成为数据库应用不断扩展的需要。 针对这种情况,本文结合业界先进的数据库管理经验, 开发了一个简单、 实 用、方便、安全的数据库监控管理平台。它可以有效地辅助 数据库管理人 员对数据库进行性能优化, 确保数据库正常、 平滑、 高效地运转。 可以监控 Sql server、Oracle、Sybase、DB2 等

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

用来存放所有数据库进程共享的数据和控制信息的存储区域,当数据库一启动, 就立即占有服务器的内存空间,库中高速缓存、字典高速缓存、 数据高速缓存、 日志缓冲区以及大缓冲池和Java 池等组件的 大小对系统性能有极大的影响, 它们直接影响磁盘 I/O 的频率 ,从而影响数据库效率。 实例的另一个组成部分是后台进程,主要有系统监视进程(SMON)、进程监视 进程(PMON)、数据库写进程(DBW0)、日志写进程(LGWR)和检查点进程(CKPT)。 后台进程负责进行执行磁盘 I/O 操作和监视管理其它进程进行可能 多的并行 操作以获取更好的性能和效率。 (3)数据库由数据文件(Data files)和 Redo 日志文件(Redo Log files)组 成,与其相 关的还有参数文件(Parameter files)、密码文件(Password files) 和存档的日志文件(Archive Log files)等,它们的物理和逻辑 结构影响磁盘 I/O 效率,与系统的整体性能密切相关。 总体来说,对数据库的调整优化,应使用综合全面的调整方法,从检查外部 环境入手,然后逐渐深入细节。整个过程使用自顶向下的概念,首先从整体环境 考虑,仔细检查可能出现问题的部分,包括服务器、磁盘、网络等。在完成了对 服务器环境的调整之后,检查影响数据库的全局参数,特别注意控制 SGA 配置 的初始化参数以及数据库的整体行为。然后是数据库中单个表和索引,查看能够 控制表行为的存储设置,并且查看这些设置适应单个对象处理需求的情况。最后 是SQL语句,它们的执行效率极大地影响着数据库的性能,通过对其进行正确的 调整可以使数据库的性能获得一个数量级程度的改善。应辨识哪些 SQL 语句使 用最频繁,然后使用必要的工具进行调整以获得最优的执行计划。

3 详细设计与实现
数据库监控系统的重要组成部分——数据库实时监控模块的实现。在该模 块,因为监控数据要不断的反应给客户,并且反应方式大部分是通过各种图形 (柱状 图、折线图等)、表格的方式表现,刷新频率较高,所以采用 APPLET 作 为客户端,以保证较快的显示速度。下面以实时监控主页面的实现为例来进行 阐述。

3.1 性能参数确定
根据数据库监控系统主要通过以下几个方面进行数据库整体优化 (1)系统资源情况包括用户 CPU 、内存使用率、I/O系统资源调整,基于任 务管理器设计。 (2)数据库结构分类包括表的主键、外键、索引、锁、分割表或储存冗余数据、存储
衍生数据、 数据库对象的放置策略, 临时表、 阻塞等信息体系数据库基础信息和结构合理性;

(3)数据用户情况参数分类包括用户信息 总用户数、 活动用户数以及活动回 话情况和事务日志信息,反应数据用户的基本信息; (4)进程参数分包括提供服务器进程总个数、dedicated 服务器 进程个数、 shared 服务器进程个数、dispatcher 服务器进程个数、并行查询服务器进程 个数、job queue 服务器进程个数这些信息; (5)磁盘存储信息 Database Files、Redo Log Files 和 Archive Log Files 的使用情况;

3.2 详细分析调整优化 3.2.1 系统资源调整优化 系统资源调整优化 资源调整
系统资源主要针对 CPU,内存和 I/O 系统资源及这些瓶颈对数据库性能影响 分析。为每个资源瓶颈提供快速识别功能,按照 SQL Server 当前的配置不能支 持这种负载需要对内存或者增加 I/O 的宽或网络通道情况调整。 以最佳应用配比 进行系统资源调整和分配。

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

数据库监控器提供一整套 CPU 运行情况监控功能。包括查看 Processor:% Processor Time 计数器是否偏高;该计数器值超过 80%一般被认为是瓶颈。也可 以使用 sys.dm_os_schedulers 视图来监视是否正在运行的任务不是 0。非 0 的 值预示有任务需要等待时间切片来运行;这个数值高表明一个 CPU 瓶颈的征兆。 同时数据库监控器提供查看当前缓存中最耗费 CPU 的批或过程高级别视图。 查询根据具有相同 plan handle(意味着属于同一个批或过程)的语句聚合 CPU 的调用。如果给出的 plan handle 对应多个语句,将不得不继续找到在整个 CPU 使用中最占用资源的查询。 2)过多的编译和重编译 当批或远端过程调用(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) 系统监视器(Perfmon) SQL Statistics 对象提供了监视重编译的计数器和发送到 SQL Server 实例 的请求类型。 监视查询编译和重编译的数量以及相关联的接受的批的数量来找出 是否这个编译是最耗费 CPU 资源。 理想环境下, Recompilations/sec 和 Batch SQL Request/sec 的比值应该很低,除非用户提交了大量的单独查询。 下列显示了关键的数据计数器。 ◆SQL Server: SQL Statistics: Batch Requests/sec ◆SQL Server: SQL Statistics: SQL Compilations/sec ◆SQL Server: SQL Statistics: SQL Recompilations/sec 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 可以通过 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 sys.dm_exec_query_stats.

特别是 plan_generation_num 预示了查询编译时的次数。通过下面 sql 语句 展示被重编译次数最多的 25 个存储过程进行分析处理。 通过各类监测方式发现过多的编译/重编译,提供针对化调整方案。 ◆如果重编译因为 SET 选项改变而发生,使用 SQL Server Profiler 确定哪 个 SET 选项被改编。避免在存储过程中改变 SET 选项。如果改变最好在连接级别 设置。确认在该连接的生存周期内不要改变 SET 选项。 在临时表上重编译的阀值比在普通表上的低。在临时表上的重编译时由于统 计改变而引起,可以降临时表改为使用表变量。表变量的改变不会引起重编译。 这种方法的确定是查询优化器不识别表变量, 因为统计不会被创建或维护表变量。 这将导致没有查询计划。可以测试不同的选项,并选择最好的方法。另外使用 KEEP PLAN 查询提示。设置临时表的这个阀值与使用永久表相同。 ◆为避免由于改变统计而产生的重编译(例如,因为数据统计导致计划不理 想),特别是 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 提示。这将避 免在每次执行的时候对整个过程重编译,而同时允许个别语句被编译。 3)效率低的查询计划调整 效率低的查询计划调整 当为一个查询生成查询计划时,SQL Server 查询优化器尝试选择一个计划为 查询提供最快的响应时间。注意最快的查询时间并不意味最小的 I/O 开销,也不 意味使用最少的 CPU 资源-它会在各种资源中平衡。 某些操作类型比其他操作对 CPU 更敏感。Hash 操作和 Sort 操作扫描他们各 自的输入数据。使用扫描向前读取(prefetch)时,在需要操作页面前,页面几 乎都在缓存中。因此可以减少或消除物理 I/O 操作。这使这些操作的类型将不被 物理 I/O 所限制。与之相比,嵌套循环连接有很多索引查找,如果索引查找使用 很多不同的表以至于页面不适合缓存的大小,将导致生成 I/O 负载。 输入优化用于评估为每中操作生成不同查询计划开销的评估,可以在 Showplan(EstimateRows 和 EstimateExecution 属性)中看到结果。没有精确 的评估,用于优化的主输入是有缺陷的。低效率的查询计划可以导致增加 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)允许你改变控制查询计划的程度。 4)内部查询的并行 当为一个查询生成执行计划时,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 的时间切片,导致更高的查询持续时间。 并行可以为查询提供最快的响应时间。然而,给定查询的响应时间必须与整 体的吞吐量和系统其他查询的响应进行衡量。 并行查询一般最适合批处理和决策 支持系统,而不适合一个事务处理环境。 内部查询的并行问题通过 SQL Server:SQL Statistics – Batch Requests/sec 计数器检测。

因为在考虑使用并行计划前,查询必须评估开销超过为并行配置设置的开销 阀值(默认被设置为 5),服务器每秒处理的批小于运行在并行计划中的批。运 行很多并行查询的服务器一般配置为较小的每秒批请求数 (例如, 小于 100 的值) 。 DMVs 在运行的服务器上,可以使用 SQL 查询确认在给定会话中是否可以并行运行 任何活动的请求。 可以搜索符合运行在并行的计划。这可以通过搜索缓存的计划来查看如果关 系操作符有 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 语句或表达式将查询写的更有效 率 5)拙劣游标使用调整 拙劣游标使用调整 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 行更有效率。 通过考虑 SQL Server:Cursor Manager By Type – Cursor Requests/Sec 计数器,可以通过这个性能计数器知道有多少游标在系统中使用。系统还有很高 的 CPU 利用率,因为小量的读取通常会有每秒数百个游标请求。 DMVs 可以用于测定使用 API 游标(不是 TSQL 游标)连接获取一行使用的缓存大 小。它对于大的获取缓存更有效。

使用包括 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

3.2.1.2 内存调整优化 内存调整 调整优化
内存压力表示当可用内存数量受到限制。识别 SQL Server 何时运行在内存 压力下将帮助排除内存相关的问题。SQL Server 依赖于不同类型的内存压力特 征也不一样。下表汇总了内存压力类型,和他们潜在的原因。在所有的情况下, 可以更多的会见到超时或显示的内存不足错误消息。 Windows 有通知的机制 如果物理内存运行在过高或过低的情况下。SQL Server 在他的内存管理决策中使用这种机制。 一般排错的步骤显示在表。 内存压力自身不会预示问题。内存压力是需要的,但时不是为服务器以后遇 到内存错误的充分条件。在内存压力下工作将被任务是服务器的正常操作。然而 内存压力的征兆可以于是服务器运行已经接近设计容量并且潜在存在内存不足 的错误。在正常运行情况下,这些信息将作为基线决定以后内存不足的原因。 1)外部物理内存压力 检查 Physical Memory 节的 Available 项的值。如果可用内存总数很低,这 表现了有外部内存压力。这个准确值依赖于很多因素,可以在当这个值降低到

50-100MB 开始查找问题。当这个总数小于 10MB 时,外部内存压力将表现得很明 显。 相同信息也可以通过 Memory: Available Bytes 计数器获取。 如果存在外部内存压力并且看到了内存相关的错误,需要确认在系统中主要 的内存消耗者。为了这个,考虑 Process: Working Set 性能计数器或在任务管 理器中 Process 栏中的 Mem Usage 列,找到最大的内存消耗者。 系统中所有使用的物理内存可以通过汇总下列计数器获取。 ◆Process 对象,每个进程的 Working Set 计数器 ◆Memory 对象 ◆系统的 Cache Bytes 计数器 ◆未分页池的 Pool Nonpaged Bytes 计数器 ◆Available Bytes (等于任务管理其中的 Available 值) 如果没有外部压力,Process: Private Bytes 计数器或在任务管理器中虚拟 内存将接近工作集的大小 (Process: Working Set 或任务管理器中的 Mem Usage) , 意味着我们没有内存用于分页了。 注意任务管理器中的 Mem Usage 列和相应的性能计数器不能计算通过 AWE 分 配的内存。这样如果使用 AWE,信息将是不完整的。这种情况下,你需要考虑在 SQL Server 内分配的内存来获取完整的信息。 可以使用 sys.dm_os_memory_clerks DMV 找到 SQL Server 通过 AWE 机制分 配了多少内存。 只有当前缓存池(’MEMORYCLERK_SQLBUFFERPOOL’类型)使用这种机制并 且只能是在使用 AWE 功能时。 通过识别和除去主要物理内存使用者 (如果有可能) 和/或 通过添加更多的内存的方法解除外部内存压力一般可以解决与此相关的 内存问题。 2)外部虚拟内存压力

需要确定是否页面文件为当前内存的分配能提供足够的空间。为了检查,可 以打开任务管理器中的性能视图,并检查 Commit Charge 节。如果 Total 接近于 Limit 则说明可以被提交的最大数量内存没有扩展页面的空间。注意任务管理器 中的 Commit Charge Total 指出潜在使用的页面文件,而不是实际使用值。实际 使用的页面文件将增加物理内存压力。 同样可以通过下列技术起获取相关信息:Memory: Commit Limit, Paging File: %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)内部物理内存压力 内部内存压力来自于 SQL Server 自身,应首先通过检查在缓存分布中的异 常来考虑在 SQL Server 内存分布。通常在 SQL Server 中缓存会占用最多提交的 内存。 为了确定在缓存池中的内存总数, 我们可以使用 DBCC MEMROYSTATUS 命令。 在 Buffer Counts 节,可以找到 Target 值。下列输出显示了在服务器达到正常 负载时 DBCC MEMORYSTATUS 的结果。 Target 是被定期的重新计算的来反映内存的低或高。在常规服务负载下 target 页面过低可能预示出现了外部内存压力。 如果 SQL Server 占用了大量的内存(通过 Process: Private Bytes 或 任务 管理器中 Mem 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 Usage 值很高,从缓存池外使用内存的组件带来的内部内存压力。被加 载到 SQL Server 进程中的组件,例如 COM 对象,连接服务器,扩展存储过程, SQLCLR 或其他会从缓存池外占用内存。如果不使用 SQL Server 内存接口,将没 有方法跟踪组件在缓存池外占用的内存。 适用于 SQL Server 内存管理机制的组件使用在缓存池中分配很少的内存。 如果分配的大于 8KB,这些组将将通过多页分配器借口使用缓存池外的内存。 如果通过多页分配器分发了过大的内存( 100-200MB 或更多),看到了通过多 页分配器 分发的大量内存,检查服务器的配置并尝试使用之前或后续的查询确 定哪个组件占用的最多的内存。 Target 值低,但是在百分比上它占用了最多的内存,请在前面部分中查找描 述外部内存压力的部分(External Physical Memory Pressure),或查看服务器 内存配置参数。 设置了 max server memory 和/或 min server memory, 应该用这些值和 Target 值进行比较。max server memory 选项限制了在缓存池中占用内存的最大值,而 服务器还可以占用其他的部分。min server memory 选项告诉服务器当小于该值 时不能释放缓存池的内存。如果 Target 小于 min server memory 设置并且服务 器没有过载, 这可能预示服务器遇到了外部内存压力并且不能获得这个设置大小 的内存。它也可能预示着从内部组件的内存压力,就像上面描述的那样。Target 数值不能超过 max server memory 选项的设置。 相对于 Stolen 和 Target 页面的高百分比(>75-80%)预示着内部内存压力。 更多关于服务器组件内存分配的信息可以使用 sys.dm_of_memory_clerks DMV 获取。

查询将 Buffer Pool 与通过单页分配器提供给组件的内存视为不同的部分。 通过下列查询可以确定在缓存池中耗费内存最多的 10 个组件 (通过单页分配器) 。 4)高速缓存和内存压力 SQL Server 2005 与 SQL Server 2000 的高速缓存设计上有一些细微的不同, 其中之一就是统一了高速缓存的框架。 为了从高速缓存中删除最近很少使用的项, 该框架实现了一套时钟算法。现在它使用 2 支不同的时钟指针,一个是内部时钟 指针,一个是外部时钟指针。 内部时钟指针控制与其他高速缓存相关的缓存大小。当框架预测到高速缓存 要使用到尽头是它开始移动。 当 SQL Server 总体上陷入内存压力时,外部时钟指针开始移动。外部时钟 指针的移动可以导致外部和内部的内存压力。 在内部和外部内存压力时不会混乱 的移动外部时钟和内部时钟。 关于时钟移动的信息可以通过 sys.dm_os_memory_cache_clock_hands DMV 显示。每个高速缓存项在内部和外部时钟指针都有不同的行。看到 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 来暴露,如下列代码所示。 可以减少服务器收到的低物理内存的通知。查看内存总量(KB 为单位)。使 用 SQL Server 的 XML 能力来查询这些信息。 上面收到了低内存的通知,缓存池重新计算 target。。如果缓存池中心的提 交比当前的提交缓存还小,缓存池将开始收缩直到外部内存压力被移除。 当监测到内部内存压力时,为组件在缓存池分配内存的低内存通知将被打开。 打开低内存通知允许从使用缓存池的高速缓存和其他组件中回收页面。

内部内存压力可以通过调整 max server memory 选项或当 stolen 页面与缓 存池的比例超过 80%时触发。 内部内存压力通知(‘Shrink’)能通过使用查询 ring buffer 的调用来发现。 5)内部虚拟内存压力 VAS 的占用可以使用 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), 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 系统。 除非数据库适合物理内存, Server SQL

经常地会有数据库页面进出缓存池。这样就发生了实质的 I/O 流量。同样,在事 务被明确的提交前,日志记录需要写入磁盘。SQL Server 为各种目的可以使用 tempdb,例如存储中间结果,排序,保持行的版本或其他。所以好的 I/O 系统对 于 SQL Server 性能非常重要。 除了当事务需要回滚时, 访问日志文件是非常频繁的, 而包括访问数据文件, 包括 tempdb,是随机访问的。所以作为一个通常的规则,为获取更好的性能,

你最好将日志文分布不到不同的物理磁盘。本文重点于不是在如何配置你的 I/O 设备,而是描述识别 I/O 瓶颈的方法。一旦识别了 I/O 瓶颈,需要重新配置你的 I/O 系统。 如果有一个慢速的 I/O 子统,用户会遇到性能问题,例如很长的响应时间, 任务由于超时而中断。 数据库监控系统提供使用下列性能参数来识别 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. Disk Sec/Write 是平均每次从磁盘读取数据的时间。请引用之前的 指导数据。 ◆Physical Disk: %Disk Time 是所选磁盘驱动器用于服务于读或写请求的 总共时间的百分比。一般推荐是如果该值大于 50%,则表现为 I/O 瓶颈。 ◆Avg. Disk Reads/Sec 表现磁盘上读操作的速度。你需要确认该值小于 85%的磁盘设计能力。磁盘访问时间指数高于能力的 85%。 ◆Avg. Disk Writes/Sec 表现在磁盘上写操作的速度。确认该值小于 85% 的磁盘设计能力。磁盘访问时间指数高于能力的 85%。 也可以通过考察锁等待来识别 I/O 瓶颈。当数据页通过读或写访问并且在缓 存池中页不可用时,这些锁等待占用了大量的物理 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_types

可以识别是否 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 78 0x0227A040 6 1 10804 78 0x0227A040 6 2 101451 31 0x02720040 在通过数据库监控系统识别到 I/O 瓶颈后提供优化方案: ◆检查 SQL Server 的内存配置。如果 SQL Server 配置的内存不足,将导致 更多的 I/O 开销。 ◆为当前的磁盘阵列添加更多的物理驱动器和/或使用更快的磁盘代替当前 的磁盘。这帮助提升读写访问时间。但是添加的磁盘数量不能比当前 I/O 控制器 所支持的数量大。 ◆添加快速或额外的 I/O 控制器。考虑为当前的控制器添加更多的缓存(如 果有可能) ◆考察执行计划并查看那个计划占用了更多的 I/O。这样可以找到更好的查 询进化 (例如, 索引) 可以减少 I/O。 如果缺少索引, 你可以运行 Database Engine Tuning Advisor 找到缺失的索引。

3.2.2 数据结构调整优化 数据结构调整 调整优化
数据库结构调整优化的也是实现避免磁盘 I/O 瓶颈、减少 CPU 利用率和减少 资源竞争重要作用。从基本表设计、扩展设计和数据库表对象放置等角度进行分 析。在基本表设计中,表的主键、外键、索引设计占有非常重要的地位,但系统 设计人员往往只注重于满足用户要求, 而没有从系统优化的高度来认识和重视它

们。实际上,它们与系统的运行性能密切相关。从系统数据库优化角度必须要进 行合理调整和优化。

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

① 聚族索引(Clustered Index):聚族索引的数据页按物理有序储存,占 用空间小。选择策略是,被用于 Where 子句的列:包括范围查询、模糊查询或高 度重复的列(连续磁盘扫描);被用于连接 Join*作的列;被用于 Order by 和 Group by 子句的列。 聚族索引不利于插入*作, 另外没有必要用主键建聚族索引。 ② 非聚族索引(Nonclustered Index):与聚族索引相比,占用空间大, 而且效率低。 选择策略是, 被用于 Where 子句的列: 包括范围查询、 模糊查询 (在 没有聚族索引时)、主键或外键列、点(指针类)或小范围(返回的结果域小于 整表数据的 20%)查询;被用于连接 Join*作的列、主键列(范围查询);被用 于 Order by 和 Group by 子句的列;需要被覆盖的列。对只读表建多个非聚族索 引有利。索引也有其弊端,一是创建索引要耗费时间,二是索引要占有大量磁盘 空间,三是增加了维护代价(在修改带索引的数据列时索引会减缓修改速度)。 那么,在哪种情况下不建索引呢?对于小表(数据小于 5 页)、小到中表(不直 接访问单行数据或结果集不用排序) 单值域 、 (返回值密集) 索引列值太长 、 (大 于 20bitys)、容易变化的列、高度重复的列、Null 值列,对没有被用于 Where 子语句和 Join 查询的列都不能建索引。另外,对主要用于数据录入的,尽可能 少建索引。当然,也要防止建立无效索引,当 Where 语句中多于 5 个条件时,维 护索引的开销大于索引的效益,这时,建立临时表存储有关数据更有效。 数据库监控系统通过 SQL 脚本提取应用系统数据库的主外键设置情况和索引 使用情况,按照数据库规范进行分析,及时发现不合理键值情况,给出合理调整 方案。

3.2.2.2 分割表和储存冗余数据应用 分割表和储存冗余数据 储存冗余数据应用
分割表分为水平分割表和垂直分割表两种。分割表增加了维护数据完整性的 代价。 水平分割表:一种是当多个过程频繁访问数据表的不同行时,水平分割表, 并消除新表中的冗余数据列;若个别过程要访问整个数据,则要用连接,这也无 妨分割表;典型案例是电信话单按月分割存放。另一种是当主要过程要重复访问 部分行时,最好将被重复访问的这些行单独形成子集表(冗余储存),这在不考

虑磁盘空间开销时显得十分重要;但在分割表以后,增加了维护难度,要用触发 器立即更新、 或存储过程或应用代码批量更新, 这也会增加额外的磁盘 I/O 开销。 垂直分割表 (不破坏第三范式) 一种是当多个过程频繁访问表的不同列时, , 可将表垂直分成几个表, 减少磁盘 I/O (每行的数据列少, 每页存的数据行就多, 相应占用的页就少),更新时不必考虑锁,没有冗余数据。缺点是要在插入或删 除数据时要考虑数据的完整性,用存储过程维护。另一种是当主要过程反复访问 部分列时, 最好将这部分被频繁访问的列数据单独存为一个子集表 (冗余储存) , 这在不考虑磁盘空间开销时显得十分重要;但这增加了重叠列的维护难度,要用 触发器立即更新、或存储过程或应用代码批量更新,这也会增加额外的磁盘 I/O 开销。垂直分割表可以达到最大化利用 Cache 的目的。 过程分割表的方法适用于:各个过程需要表的不联结的子集,各个过程需要 表的子集,访问频率高的主要过程不需要整表。在主要的、频繁访问的主表需要 表的子集而其它主要频繁访问的过程需要整表时则产生冗余子集表。 存储衍生数据对一些要做大量重复性计算的过程而言,若重复计算过程得到 的结果相同,或计算牵扯多行数据需额外的磁盘 I/O 开销,或计算复杂需要大量 的 CPU 时间,就考虑存储计算结果(冗余储存)。现予以分类说明: 若在一行内重复计算,就在表内增加列存储结果。但若参与计算的列被更新 时,必须要用触发器更新这个新列。 若对表按类进行重复计算,就增加新表存储相关结果。但若参与计算的列被 更新时, 就必须要用触发器立即更新、 或存储过程或应用代码批量更新这个新表。 若对多行进行重复性计算,就在表内增加列存储结果。但若参与计算的列被 更新时,必须要用触发器或存储过程更新这个新列。 存储冗余数据有利于加快访问速度;但违反了第三范式,这会增加维护数据 完整性的代价,必须用触发器立即更新、或存储过程或应用代码批量更新,以维 护数据的完整性。对于频繁同时访问多表的一些主要过程,考虑在主表内存储冗 余数据,即存储冗余列或衍生列。

数据库监控系统提供数量查询量和表关系分析,提供针对不同应用数据库使 用给出合理的使用分割表和存储冗余数据的方案, 最大程度优化数据在访问和统 计是占用 CPU 和 I/O 系统的效率。

3.2.2.3 数据库对象的放置策略应用 数据库对象的放置策略应用
数据库对象的放置策略是均匀地把数据分布在系统的磁盘中, 平衡 I/O 访问, 避免 I/O 瓶颈。 ⑴ 访问分散到不同的磁盘,即使用户数据尽可能跨越多个设备,多个 I/O 运转,避免 I/O 竞争,克服访问瓶颈;分别放置随机访问和连续访问数据。 ⑵ 分离系统数据库 I/O 和应用数据库 I/O。 把系统审计表和临时库表放在不 忙的磁盘上。 ⑶ 把事务日志放在单独的磁盘上,减少磁盘 I/O 开销,这还有利于在障碍 后恢复,提高了系统的安全性。 ⑷ 把频繁访问的 “活性” 表放在不同的磁盘上; 把频繁用的表、 频繁做 Join* 作的表分别放在单独的磁盘上, 甚至把把频繁访问的表的字段放在不同的磁盘上, 把访问分散到不同的磁盘上,避免 I/O 争夺; ⑸ 利用段分离频繁访问的表及其索引 (非聚族的) 分离文本和图像数据。 、 段的目的是平衡 I/O,避免瓶颈,增加吞吐量,实现并行扫描,提高并发度,最 大化磁盘的吞吐量。利用逻辑段功能,分别放置“活性”表及其非聚族索引以平 衡 I/O。当然最好利用系统的默认段。另外,利用段可以使备份和恢复数据更加 灵活,使系统授权更加灵活。

3.2.2.4 临时表 Tempdb 调整优化
Tempdb 用于全局存储内部或用户对象,临时表,对象和在 SQL Server 操作 是创建的存储过程。每个 SQL Server 实例只有 1 个单一的 tempdb。它可能是一 个性能和磁盘空间的瓶颈。有限可用空间和过多的 DDL/DML 会使 Tempdb 超过负 载。这能导致运行在同一个服务器中的其他无关应用变得运行缓慢或失败。

数据库监控系统提供监视器来监视使用中的 tempdb 空间。Free Space in tempdb (KB).这个计数器以 KB 为单位跟踪空闲空间的数量。管理员可以使用这 个计数器确定是否 tempdb 运行在较低的磁盘空间上。 内部对象在每条语句中被创建和销毁,除非想在前面所描述的。注意到有大 量的 tempdb 空间分配,将需要了解那个会话或任务占用了空间,然后进肯能做 一些矫正的操作。

3.2.2.5 阻塞调整优化
阻塞主要是等待逻辑锁,例如等待在资源上获取排他锁或等待从更低级别的 同步结果。 当做出一个在已经锁定的资源上获得一个不兼容的锁的请求产生时,逻辑锁 等待发生。在特殊的 Transact-SQL 语句运行时,通过使用锁可以基于事务隔离 级别提供数据一致性的功能,这样给最终用户的感觉是 SQL Server 运行缓慢。 当查询被阻塞时,它不占用任何系统资源,所以你将发现它运行很长时间但是资 源占用却很少。更多关于并发控制和阻塞的信息请查看 SQL Server 联机丛书。 如果系统没有被配置为处理这种负载就会导致等待底层的原始同步。 一般阻塞和等待的场景是: ◆识别阻塞者 ◆识别长的阻塞 ◆阻塞每个对象 ◆页面闭锁问题 ◆阻塞影响整体性能 如果系统资源(或锁)当前不能服务于请求,这个 SQL Server 会话将被置 于等待状态。换句话说,资源有一个等待请求的队列。DMV 能提供任何等待资源 的会话的信息。 SQL Server 2005 提供了更详细和一致的等待信息,有大约 125 种等待类型 而 SQL Server 2000 只有 76 种可用的等待类型。DMV 提供的信息范围从

sys.dm_os_wait_statistics 中表现 SQL Server 全面和积累的等待信息,到 sys.dm_os_waiting_tasks 中与会话相关分解的等待信息。下列 DMV 提供了详细 的等待某些资源的任务等待队列。它同样表现了在系统中所有的等待队列。例如 这个结果显示了会话 56 被会话 54 阻塞了。 为了找到准许的锁或等待锁的会话,可以使用 sys.dm_tran_locks DMV。每 行数据展现了发送到锁管理器的当前活动的请求。为了有序的锁,准许请求指出 了锁已经在资源上被准许给请求者。一个等待的请求指出了请求没有被准许。 在 SQL Server 中阻塞是很正常的,使用逻辑锁来维护事务一致性的。然而 当等待的锁超过了阀值,它会影响响应时间。为了识别长时间运行的阻塞,使用 BlockedProcessThreshold 配置参数来建立一个用户配置的服务端阻塞阀值。阀 值定义一个秒级的间隔。任何超过阀值的阻塞将出发事件并被 SQL Trace 捕获。 一旦阻塞处理阀值被建立,下一步是捕获跟踪的事件。跟踪阻塞超过用户配 置的阀值事件可以通过 SQL Trace 或 Profiler 捕获。 新的 SQL Server 2005 DMV Sys.dm_db_index_operational_stats 提供了全 面的索引使用统计,包括阻塞。根据阻塞,它提供了每个表,索引,分区的锁统 计的详细信息。 例如, 在给定索引和表上的访问历史, 锁数量 (row_lock_count) , 阻塞数量(row_lock_wait_count)和等待时间(row_lock_wait_in_ms)等信息。 这个 DMV 包括的类型信息有: ◆占有的锁的数量,例如行或页。 ◆阻塞或等待的数量,例如,行或页。 ◆阻塞或等待持续的时间,例如行或页。 ◆页面上闩的等待。 ◆page_latch_wait 持续时间:这包括特殊页上的争用,升序键的插入。在 这种情况,热点是最后的页面,所以多个写入这到最后的页面每次尝试获取 高级的页面闩在同样的时间。这将作为 Pagelatch waits 暴露。 ◆page_io_latch_wait 持续的时间: 当用户请求一个不在缓存池的页面时发 生的 I/O 闩。一个慢速的 I/O 子系统或过多工作的 I/O 子系统将遇到很高的 PageIOlatch 等待,这实际上是 I/O 问题。这个问题被混在于缓存清除和缺

失索引中。 ◆页面闩等待持续的时间。 除了阻塞相关信息外,这还有额外的信息。 ◆访问类型, 包括 range, singleton lookups. ◆在页级的插入,更新,删除。 ◆在页级之上插入,更新或删除。在叶上的活动是索引维护。在每个叶级页 面中的第一行有这个级别之上的条目。如果新的页面被分配到叶级别上,这 级别之上将为新的叶级页面的第一行创建新的项。 ◆在叶级别的页面合并将表现为重新分配的空页,因为行已经删除了。 ◆索引维护。在叶级上页面合并就是将空白页重分配,这导致在叶上行被删 除,因此留下的中间级别页面是空白的。在叶级页面的第一行有一个条目在 该层上。如果在叶级别删除了足够的行,原来包含第一行叶级页面条目的中 间层索引页面也会是空白的。这导致在叶结点上的合并发生。 这些信息积累了从实例启动以来的信息。信息不会一直保留直到实例被重新 启动,并且没有其他的方法可以重置它。这个 DMV 返回的数据仅在元数据缓存对 象表现的堆或索引可用的情况下存在。 只要堆和索引的元数据被加载到了元数据 缓存,每个列的这个值将被设置为 0。统计是被累加的直到缓存对象被从元数据 缓存中删除。然而,你可以周期性的查询这个表来收集在表中的信息,用于更进 一步的分析。 SQL Server 2000 提供了 76 种等待类型来提供等待报告。SQL Server 2005 提供了多余 100 个等待类型来跟踪应用程序性能。 任何时间 1 个用户连接在等待 时,SQL Server 会累加等待时间。例如应用程序请求资源例如 I/O,锁或内存, 可以等待资源直到可用。这些等待信息可以跨所有连接将被汇总和分类,所以性 能配置可以从给定的负载获得。因此,SQL 等待类型从应用程序负载或用户观点 识别和分类用户(或线程)等待。 ◆一些等待是正常的例如后台线程的等待,例如 lazy writer 组件。 ◆一些会话为获取共享锁等待很长时间

◆信号等待是在一个工作线程获取对资源访问到它被拿到 CPU 上调度执行这 段时间。长时间的信号等待也许意味着很高的 CPU 争用。 ◆Track_waitstats.收集数据渴望的采样数量和采样的时间间隔。◆ Get_waitstats.分析前面步骤收集到的数据。这有一个调用的示例。 exec [dbo].[get_waitstats_2005] ◆Spid 运行,需要当前不可用的资源。因为资源不可用,在 T0 时,它移动 到资源等待列表。 ◆信号指出资源是可用的,所以 spid 在 T1 时间移动到可运行队列。 ◆Spid 等候运行状态直到 T2,同样的 CPU 通过可运行队列处理按顺序到达 等待。 其他方面的查询性能与 DML 查询,查询删除,插入和修改数据相关。在指定 表上定义更多索引, 在需要数据修改时就需要更多的资源。 由于锁结合持续事务, 时间长的修改操作可以损害并发性。 因此在应用程序中使用那个索引就变得非常 重要。计算出是否在数据库架构上有大量从未使用过的索引存在。 SQL Server 2005 提供了新的 sys.dm_db_index_usage_stats 动态管理视图 显示哪些索引是使用的,和是否他们被用于用户查询或仅用于系统操作。每次执 行查询,在这个视图中的列将根据用于执行查询的查询计划将会增加。当 SQL Server 启动并运行, 数据就被收集了。 这个 DMV 中的数据只是保存在内存中的, 没有持久化。所以当 SQL Server 实例关闭,数据将会丢失。你可以周期性的获 取这个表,并将数据保存用于以后分析。 在索引操作被分为用户类型和系统类型。用户类型引用 SELECT 和 INSERT/DELETE/UPDATE 操作。系统类型操作是类似于 DBCC 这样的命令或 DDL 命 令或是 update statistics。每种类别的语句列被区分为: ◆依靠索引的 SEEK 操作 (user_seeks or system_seeks) ◆依靠索引的 LOOKUP 操作(user_lookups or system_lookups) ◆依靠索引的 SCAN 操作(user_scans or system_scans) ◆依靠索引的 UPDATE 操作(user_updates or system_updates)

每种访问索引都会记路最后一次访问的时间戳。 一个索引本身通过 3 列识别, database_id,object_id 和 index_id。然而,index_id=0 代表是一个堆表, index_id=1 代表时集束索引,反之 index_id>1 但表是非集束索引。 一个整天运行的数据库应用程序,从 sys.dm_db_index_usage_stats 中得到 的索引访问信息列表将增长。 下列是在 SQL Server 2005 使规则和任务的定义: ◆SEEK: 识别用于访问数据的 B 树结构数量。不论 B 树结构只是访问每级只 有少量页面来获取一个数据行, 还是是表中使用半索引页面读取如几个 G 数据或 百万行的数据。所以我们希望在这个类别有更多的累计。 ◆SCAN: 识别不使用 B 树索引获取数据的数据表数量。没有任何索引定义的 表属于这种情况。 有索引定义但是在执行语句查询时并没有使用这些说印的表也 属于这种情况。 ◆LOOKUP: 识别在一个集束索引通过’seeking’在一个非集束索引上查询 数据,2 个索引都定义在同一张表上。这种场景描述在 SQL Server 2000 中的书 签查询。它表现了这样一个场景,非集束索引被用于访问表,并且非集束索引没 有覆盖查询的列和索引列没有在 WHERE 子句定义,SQL Server 将使用非集束索 引列的 user_seeks 值加上使用集束索引列的 user_lookups 值。 这个值能变得很 高如果多个非集束索引在这个表上定义。 如果依靠集束索引的 user_seeks 值高, user_lookups 的数量也会很高,加上一部分 user_seeks 也是很高,应该通过将 非集束索引大量的高于集束索引。 动态管理视图的真正目的是在长时间运行时观察索引的使用情况。它可以提 供视图的快照或查询结果集,将其存储,然后每天比较相应的改变。识别特殊的 索引数月没有使用或者在很长时间没有使用,可以最终从数据库中删除他们。

3.2.3 数据用户查询管理 数据用户查询管理
3.2.3.1 数据库用户管理 数据库用户管理
授权登录到 SQL Server 的每个用户都有一个登录名,该登录名将授予他们

访问 SQL Server 实例的权限。有两种类型的登录名: 1)Microsoft Windows 帐户名 通过使用 sp_grantlogin,sysadmin 或 securityadmin 固定服务器角色的 成员可以授权各个用户或 Windows 组的 Windows 帐户登录到 SQL Server 实 例。然后,Windows 帐户标识的用户或 Windows 组中的任何用户可以使用 Windows 身份验证连接到 SQL Server 实例。每个 Windows 帐户或组名称都存 储在 sys.server_principals 中。 Windows 帐户或组的 Windows 安全标识符存 储在 sys.server_principals.sid 中。 2)SQL Server 登录名 这些名称将在用户使用 SQL Server 身份验证登录时使用。SQL Server 登 录名由 sysadmin 或 securityadmin 固定服务器角色的成员使用 sp_addlogin 来定义的。每个 SQL Server 登录名都存储在 master.dbo.syslogins.loginname 中。SQL Server 将生成一个用作安全标识符 的 GUID 并将其存储在 sys.server_principals.sid 中。SQL Server 使用 sys.server_principals.sid 作为登录名的 security_identifier。 3)数据库用户名 每个 Windows 帐户或 SQL Server 登录都必须与已授权用户对其进行访问 的每个数据库中的用户名相关联,或者该数据库必须已启用 guest 访问。数据 库用户名由 db_owner 或 db_accessadmin 固定数据库角色的成员来定义, 并存 储在每个数据库的 sys.database_principals 表中。每个数据库用户名都与存 储在 sys.database_principals.uid 中的一个数据库用户 ID 相关联。每个用 户的 security identifier 都存储在 sys.database_principals.sid 中,因此 可将用户映射回其关联登录名。如果使用同一数据库用户名作为 SQL Server 登 录名或 Windows 帐户名,则可以减少混淆;但是,不要求一定这样做。 4)获得登录帐户或 ID 连接到 SQL Server 实例时,可以使用下列函数获取登录帐户或 ID:

SUSER_SNAME,以获取与 security identifier 关联的 SQL Server 登录名 SUSER_SNAME 或 Windows 帐户。 SUSER_SID,以获取与 SQL Server 登录名或 Windows 帐户关联的 security SUSER_SID

identifier。
SUSER_SID()(指定 SUSER_SID 时不带 login_account 参数),以获取当 SUSER_SID() 前连接的 security identifier,不管使用的是 SQL Server 身份验证还是 Windows 身份验证。 SYSTEM_USER,用来获取用于 Windows 身份验证连接的 Windows 帐户或用 SYSTEM_USER 于 SQL Server 身份验证连接的 SQL Server 登录名。在 Transact-SQL 中, SYSTEM_USER 作为 SUSER_SNAME()(指定 SUSER_SNAME 时不带 security_identifier 参数)的同义词来实现。 SUSER_SNAME 可以使用以下参数: 用于 Windows 帐户或组的 security_identifier,在这种情况下,该函 数将返回 Windows 帐户名或组名。 为 SQL Server 登录名生成的伪 security_identifier,在这种情况下, 该函数将返回 SQL Server 登录名。 如果没有为使用 Windows 身份验证建立的连接指定 security_identifier,SUSER_SNAME 将返回与该连接关联的 Windows 帐户名。 如果是使用 SQL Server 身份验证进行的连接, SUSER_SNAME 将 返回与该连接关联的 SQL Server 登录。 SYSTEM_USER:在 ISO 中,此函数作为 SUSER_SNAME() 的同义词实现。(指定 SUSER_SNAME 时不带 security_identifier 参数。) USER_ID(),以获取与当前连接关联的数据库用户 ID。 USER_NAME,以获取与数据库用户 ID 关联的数据库用户名。

ISO CURRENT_USER 或 SESSION_USER 函数,用来获取与当前连接关联的数据库 用户名。在 Transact-SQL 中,这些函数实施为 USER_NAME() 的同义词。(指 定 USER_NAME 时不带 database_user_ID 参数。)Transact-SQL 函数 USER 也 实现为 USER_NAME() 的同义词 ISO 允许在符合如下条件的 SQL 模块中对 SQL 语句进行编码: 模块的授权 标识符独立于已连接到 SQL 数据库的用户的授权标识符。ISO 指定 SESSION_USER 始终返回建立连接的用户的授权标识符。 对于从 SQL 模块中执行 的任何语句,CURRENT_USER 都将返回 SQL 模块的授权标识符;如果 SQL 语句 不是从 SQL 模块中执行的,则返回进行连接的用户的授权标识符。如果 SQL 模 块没有独立的授权标识符, ISO 将指定 CURRENT_USER 返回与 SESSION_USER 相 同的值。 Server 没有用于 SQL 模块的独立授权标识符; SQL 因此 CURRENT_USER 和 SESSION_USER 始终相同。 ISO 将 USER 函数定义为向后兼容按照早期版本的 标准编写的应用程序的函数。USER 被指定为与 CURRENT_USER 返回相同的值。 USER_ID('database_user_name') ' ' USER_ID 将返回与指定数据库用户名关联的数据库用户 ID。如果未指定 database_user_name,USER_ID 将返回与当前连接关联的数据库用户 ID。 USER_NAME(database_user_ID) USER_NAME 返回与指定的数据库用户 ID 关联的数据库用户名。如果未指定 database_user_ID,USER_NAME 将返回与当前连接关联的数据库用户名。 CURRENT_USER、SESSION_USER、USER 这些函数是 USER_NAME() 的同义词。(指 定 USER NAME 时不带 database_user_ID 参数。)

3.2.3.2 事务日志管理 事务日志 日志管理
SQL Server 的 每 一 个 数 据 库 , 无 论 是 系 统 数 据 库 ( master,model, sybsystemprocs, tempdb),还是用户数据库,都有自己的 transaction log, 每个库都有 syslogs 表。Log 记录用户对数据库修改的操作,所以如果不用命令 清除, log 会一直增长直至占满空间。清除 log 可用 dump transaction 命令;

或者开放数据库选项 trunc log on chkpt 数据库会每隔一段间隔自动清除 log。 chkpt, 管理好数据库 log 是用户操作数据库必须考虑的一面 是用户操作数据库必须考虑的一面。 1)SQL 记录及读取日志信息 1)SQL Server 记录及读取日志信息 SQL Server 是先记 log 的机制。Server Cache Memory 中日志页总是先写于 数据页:

时写入硬盘。 Log pages 在 commit ,checkpoint,space needed 时写入硬盘 Data pages 在 checkpoint,space needed 时写入硬盘。 系统在 recovery 时读每个 database 的 syslogs 表的信息, ,回退未完成的 事务(transaction) 数据改变到事务前状态 ; (数据改变到事务前状态) 完成已提交的事务(transaction) (transaction) (数据改变为事务提交后的状态 数据改变为事务提交后的状态)。在 Log 中记下 checkpoint 点 点。这样保证整 个数据库系统的一致性和完整性。 个数据库系统的一致性和完整性 2)Transaction logs 和 checkpoint 进程 checkpoint 命令的功能是强制所有 命令的功能是强制所有“脏”页(自上次写入数据库设备后被 自上次写入数据库设备后被 更新过的页)写入数据库设备 写入数据库设备。自动的 checkpoint 间隔是由 SQL Server 根据 系统活动和系统表 sysconfigures 中的恢复间隔(recovery interval recovery interval)值计算 出的。 通过指定系统恢复所需的时间总量, 通过指定系统恢复所需的时间总量 恢复间隔决定了 checkpoint 的频率。 如果数据库开放 trunc log on chkpt 选项,则 SQL Server 在数据库系统执 行 checkpoint 时自动清除 log。但用户自己写入执行的 checkpoint 命令并不清 除 log,即使 trunc log on chkpt 选项开放。只有在 trunc log on chkpt 选项 开放时,SQL Server 自动执行 checkpoint 动作,才能自动清除 log 。这个自动 的 checkpoint 动作在 SQL Server 中的进程叫做 checkpoint 进程。 trunc log 。当

on chkpt 选项开放时, checkpoint 进程每隔 0 秒左右清除 log, 而不考虑 recovery interval 设置时间的间隔。 3)Transaction 3)Transaction log 的大小 没有一个十分严格的和确切的方法来确定一个数据库的 log 应该给多大空 间。 对一个新建的数据库来说, log 大小为整个数据库大小的 20%左右。 因为 log 记录对数据库的修改,如果修改的动作频繁,则 log 的增长十分迅速。所以说 log 空间大小依赖于用户是如何使用数据库的。 还有其它因素影响 log 大小,我们应该根据操作估计 log 大小,并间隔一个 周期就对 log 进行备份和清除。 4)检测 4)检测 log 的大小 若 log 在自己的设备上,dbcc checktable (syslogs) 有如下信息: 例:***NOTICE:space used on the log segment is 12.87Mbytes,64.35% ***NOTICE:space free on the log segment is 7.13Mbytes,35.65% 根据 log 剩余空间比例来决定是否使用 dump transaction 命令来备份和清 除 log。 用快速方法来判断 transaction log 满的程度。 1>use database_name 2>go 1>select data_pgs (8,doa mpg) 2>from sysindexes where id=8 3>go Note:this query may be off by as many as 16 pages. 在 syslogs 表用 sp_spaceused 命令。 5)log 5)log 设备

一般来说,应该将一个数据库的 data 和 log 存放在不同的数据库设备上。 这样做的好处: 可以单独地备份(back up)transaction log,防止数据库溢满 , 可以看到 log 空间的使用情况。[dbcc checktable (syslogs)] 设备 6)log 6)log 的清除 数据库的 log 是不断增长的, 必须在它占满空间之前清除。 前面已经讨论过, 清除 log 可以开放数据库选项 trunc log on chkpt,使数据库系统每隔一段时 间间隔自动清除 log,还可以执行命令 dump transaction 来清除 log.trunc log on chkpt 选项同 dump transaction with truncate_only 命令一样,只是清除 log 而不保留 log 到备份设备上。所以如果只想清除 log 而不做备份,可以使用 trunc log on chkpt 选项及 dump transaction with truncate_only,dump transaction with no_log 命令。若想备份,应做 dump transaction database_name to dumpdevice。 7)管理大的 7)管理大的 transactions 有些操作是大批量地修改数据,log 增长速度十分快,如:大量数据修改, 删除一个表的所有记录 ,基于子查询的数据插入 ,批量数据拷贝 . 使用 transaction 使 log 不至溢满 若这个表很大,则此 update 动作在未完成之前就可能使 log 满,引起 1105 错误 (log full) 而且执行这种大的 transaction 所产生的 exclusive table loc, 阻止其他用户在 update 期间修改这个表,这可能引起死锁。为避免这些情况, 可以把这个大的 transaction 分成几个小的 transactions,并执行 dump transaction 动作。 若这个 transaction 需要备份到介质上, 则不用 with truncate_only 选项。 若执 行 dump transaction with truncate_only,应该先做 dump database 命 令。 ,可以镜像 log

同样,把整个 table 的记录都删除,要记很多 log,可以用 truncate table 命令代替上述语句完成相同功能。这样,表中记录都删除了,而使用 truncate table 命令, log 只记录空间回收情况, 而不是记录删除表中每一行的操作。 。


相关文章:
Oracle数据库性能监控语句汇总
Oracle数据库性能监控语句汇总_计算机软件及应用_IT/计算机_专业资料。oracle数据.../*会话的唯一标识,通常要对某个会话进行分析前,首先就需要获 得该会话的 SID...
数据库性能问题处理及监控
比如占用 CPU 高的 无法监控 分析性能问题 处理监控 通知数据库厂家处理 确定问题原因,如果暂时不能确定原因, 则尽量收集各种统计数据(awr,alert log 等) ,需要...
SQLServer性能监控指标说明
SQLServer的性能计数器 指标说明,供性能分析和做监控产品参考。性能监控指标说明 ...指标说明 数据库空间: 指标名称数据库空间名称 空间编号 空间类型 数据类型 ...
mysql 性能监控
mysql 性能监控_计算机软件及应用_IT/计算机_专业资料。一、 Mysql 的日志记录对...‘passwd’ 将分析结果保存到数据库: pt-index-usage /data/dbdata/localhost-...
ORACLE数据库性能监控手册 精品资料
oracle数据库性能监控的... 2页 1下载券 Oracle监控数据库性能的... 4页 1...二、如何获取并分析 AWR 1、使用 TOAD 工具登录 ORACLE 登录界面 2、 生成 ...
Oracle性能监控
所以, DBA 的一个重要工作就是定期监控, 分析评价 oracle 数据库的性能是否依然 满足客户的应用需求. 分析评价 oracle 数据库性能主要有数据库吞吐量, 数据库用户...
监控数据库性能的SQL语句
监控数据库性能的SQL语句_IT/计算机_专业资料。Oracle性能优化的必备,如果喜欢使用...对可疑/性能不好的 Server Process 来进行 Trace.,可以用 tkprof 来分析 Trace...
ORACLE性能监控SQL语句
ORACLE 性能监控 SQL 语句 监控当前数据库谁在运行什么 SQL 语句 SELECT osuser...分析表 analyze table tablename compute statistics for all indexes; analyze tab...
使用SpotLight监控数据库性能
使用SpotLight 监控数据库性能 8.1.4 使用 SpotLight 监控数据库性能(1) SpotLight On Oracle 是由 Quest 公司出品的一款针对 Oracle 进行监控的软件。 SpotLight ...
oracle数据库性能监控
oracle 数据库性能监控的 SQL 1. 监控事例的等待 SQL> SUM(DECODE(WAIT_TIME,0,0,1)) SQL SELECT EVENT,SUM SUM "PREV",SUM SUM(DECODE(WAIT_TIME,0,1...
更多相关标签:
oracle数据库性能监控 | 数据库性能监控 | mysql数据库性能监控 | 数据库性能监控工具 | 数据库性能监控软件 | 如何监控数据库性能 | oracle数据库性能分析 | 数据库性能分析报告 |