SQL_Server_性能监视和故障诊断.ppt

第 14 章 SQL Server 性能监视和故障诊断 n监视 SQL Server n使用 “SQL Server Profiler ” n使用“系统监视器” n查看当前活动 监视 SQL Server ： 管理任务： 小王所在的学校建立一个完整的数据库系统，该系统用于学校的办 公和教学管理。小王负责维护数据库，他根据数据库特点制定了日常的 监视计划，并使用各种工具对 SQL Server 运行进行监视。 任务演示： 小王使用一台计算机通过“系统监视器”管理控制台远程连接到 SQL 服务器。监视默认的磁盘活动、处理器使用率等；还监视 SQL Server 对象 General Statistics 和 Buffer Manager 下的计数器（用户的连接情 况和数据页的使用情况）。 通过使用，发觉网站查询教室安排时慢，检查发现教室安排查询使 用了spClassroomQ 存储过程。用 SQL Server Profiler 建立了该存储过 程跟踪。 跟踪后发现问题的源头，在SSMS中打开活动监视器，查看数据 库的锁的信息，并对数据库对象相互锁定的问题进行解决。 最后检查该表属性。通过维护计划重建了更新频繁的索引。问题 全部解决。 重要概念： 在日常工作中，必须不断的对数据库进行维护。维护的依据就是对 系统状态的监视情况。通过监视及跟踪，找到产生问题的对象，对系统 进行维护。这就是数据库生命周期的一个重要组成部分。 SQL Server Profiler ：可以跟踪 SQL Server 的各种对象，进一步查 找故障来源； 系统监视器：可以实时监控系统的运行状态并提供警报信息； 活动监视器：可以监视当前的 SQL Server 中的进程和锁的情况。 使用 “SQL Server Profiler ” ： SQL Server Profiler 是一种事件探查器，它具备跟踪服务器和数 据库活动的功能。可用来捕捉表、文件或 T-SQL 脚本中的数据，以供 稍后分析。 n监视服务器和数据库活动的图形化界面工具 q根据可重用模板创建跟踪 q在跟踪运行时，观察跟踪结果 q将跟踪结果存储在文件表中，以供进一步分析 q按需要启动、停止、暂停和修改跟踪结果 q重现跟踪结果 n通过使用 SQL Server Profiler 跟踪 SQL Server 活动 使用 SQL Server Profiler 先确定跟踪对象，再选择标准，可以监 视以下活动： q 执行性能较差的查询； q 登录尝试、故障、连接或连接断开； q 语句级别使用的CPU； q 死锁的问题； q Tempdb 数据库的性能等。 “SQL Server Profiler” 跟踪选项 ： 使用 SQL Server Profiler 创建一个跟踪时，有很多选项用于定义 将记录的活动和将存储的登录跟踪活动。 指定跟踪模板 跟踪事件是通过指定要监视的事件类和要记录的个别数据值（列） 来确定的。通过选择跟踪基于的模板，添加或删除个别的事件类或列， 并应用筛选器限制基于指定标准收集的数据来确定跟踪。 预定义模板：容易配置指定活动类型所需的事件。 用户定义模板 保存跟踪数据 若稍后分析或重现捕捉到的数据，可先将捕捉到的事件数据保存到 一个文件或 SQL Server 表中。 保存到表，可以设置的选项：表的位置和名称；表中最多可存储的 行数。 保存到文件，可以设置的选项：文件的位置和名称；最大文件的大 小；文件满时的跟踪行为；服务器或“SQL Server Profiler ”进行的跟 踪处理。 通过保存跟踪，可以： 使用一个跟踪文件或跟踪表来创建一个工作负载，它被用作数据库 引擎优化顾问的输入； 使用一个跟踪文件捕捉事件，并将跟踪文件发送到提供支持的程序 中进行分析； 指定跟踪停止时间 选择设置一个跟踪的时间，允许自动跟踪并在达到指定日期和时间 之前保持跟踪运行。 使用 SQL Server 中的查询处理工具，访问数据或查看 SQL Server Profiler 中的数据。 跟踪类别、事件和列： 跟踪类别 在 SQL Server Profiler 中，每个类别都是一组相关的事件类。事 件类由可以跟踪的事件的类型组成，事件类包含所有可以被事件报告的 数据列。 默认的跟踪类别有： 安全审核； 会话； 存储过程； TSQL。 事件 事件是指 SQL Server 数据库引擎实例中操作的一次执行。事件由事 件属性进一步定义。这些属性在数据列中列出。 注意： 若要查看所有事件，选中跟踪属性对话框中事件选择选项 卡上的所有事件复选框。 类别事件描述 安全审核Audit Login用户成功登陆到SQL Server 安全审核Audit Logout用户已退出SQL Server 会话ExistingConnection启动跟踪，现有用户连 接 属性 TSQLSQL:BatchStarting指出已启动 T-SQL 批处理 列 数据列包含事件的属性。 SQL Server Profiler 使用跟踪输出的数据 列，描述跟踪运行时捕捉到的事件。 可以通过使用列筛选器管理列来控制收集数据的类型。 使用“系统监视器” ： n 场景： 你是一名企业数据库主管，你的团队要维护该企业的数据库24小时 不间断正常运行。 为了达到这个目的，需要对 SQL Server 进行全面监控以保证服务 器的所有子系统都处于协调工作和未超载的状态之下。 你觉得使用 Windows 系统监视器可以实现这个目标；通过它还可 设置警报，在系统超载时，通知管理员团队。 n系统监视器简介 它是一个MMC（Microsoft Management Console）管理单元， 可使用该管理单元查看系统性能度量指标。 你可以启动控制面板中管理工具程序组下的性能应用程 序，来查看系统监视器。 q对象：是计算机系统的主要组件或子系统。对象可以是硬件、软 件或应用程序。 q计数器：收集对象各方面的数据。计数器内置于操作系统中，并 持续读取性能数据。若一个对象类型有多个实例，计数器会跟踪每 个实例或所有实例的统计信息。 q实例：同一个对象类型可以有多个实例。查看系统监视器性 能信息时，可以查看某一实例值或该对象所有实例的综合值。 在系统监视器中，可指定要显示的计数器，并可以直线图、直 方图或报告的形式显现。 当性能数据保存为 HTML 文件时，可在浏览器中查看并打印直线 图、直方图或报告。报告还可导入电子表格中，进行更详细分析。 性能日志和警报 在系统监视器中，使用该管理单元将性能信息捕捉到日志文 件中，稍后查看。 计数器日志将记录关于硬件资源和基于性能的系统服务的历史信 息；跟踪日志收集事件跟踪信息，根据跟踪信息测量与事件相关的性能 统计。 日志文件将提供用于排除故障和进行归纳的信息。在为当前活动 的图表、警报和报告提供即时反馈时，还可长时间跟踪计数器。 不可能时时监视某个特定计数器，又希望再超出或低于指定值得 到通知，可配置一个在计数器达到特定极限值时就被激活的警报。 对象描述 SQLServer:Buffer Manager 提供关于 SQL Server 使用的内存缓 冲区的信息 SQLServer:Databas es 提供关于 SQL Server 数据库的信息 SQLServer:Locks 提供关于 SQL Server 发出的单个锁 请求的信息 SQLServer:Memory Manager 提供关于 SQL Server 内存利用率的 信息 SQL Server-specific 对象允许你监视 SQL Server 的每个实例 SQL Server-specific 对象包括: nSQL Server 性能对象 SQL Server 为系统监视器提供了大量的对象和计数器。对象可以 是任何一个 SQL Server 资源。每个对象包含一个或多个计数器，这 些计数器可确定要监视的对象的多个方面。 n 监视SQL Server 的注意事项 通过监视 SQL Server 实例，需要对系统主要方面进行分析。通 过监视，可找到并消除性能瓶颈，进一步确定数据库、T-SQL 查询或 客户端应用程序的设计问题。 n 监视器的关键区域 q磁盘系统： SQL Server 使用 Windows 操作系统输入/输出（I/O）调用来实现对 磁盘子系统的读写操作。 SQL Server 管理执行磁盘I/O的时机和方式； Windows 操作系统将 执行底层 I/O 操作。在系统中，磁盘 I/O 频繁将导致瓶颈发生。 q内存： 对 SQL Server 进行周期性的监视，可确保内存的使用情况不出现异常 。首先要确定不存在消耗过多内存的进程，同时也不存在内存不够的情况。 qCPU： 对 SQL Server 进行周期性的监视，可确定CPU 使用率是否在正常范围 内。持续的高CPU 使用率可能表示需要升级CPU 或添加更多的处理器。 查看当前活动 ： n 提问： 作为DBA，希望获得 SQL Server 的实时状态。有什么好的办法 可以查看 SQL Server 中当前的所有进程以及进程的内容呢？ 可通过使用SSMS 的活动监视器组件或动态管理视图来监视 SQL Server 中当前活动，以获取有关到数据库引擎的用户连接及其所 保存的锁的信息。 监视当前活动可帮助排除数据库锁定的问题，终止死锁或其他没响 应的进程。 n 活动监视器 活动监视器是SSMS 中的图形化工具，显示有关当前用户进程和 锁的信息。（在管理文件夹中可看到） 在活动监视器的选择页上可查看到三个信息：进程信息、按 进程分类的锁和按对象分类的锁。 进程信息： 在进程信息页面上。活动监视器将显示一个列表，它列 出了系统中所有的连接进程。 数据库管理员可根据该页面来确定哪些进程正处于活动状态，若 需要还可终止某一进程。 按进程分类的锁： 在按进程分类的锁页面上，活动监视器显示所选进程保 存的所有锁和锁定请求。 数据库管理员可根据该页面确定哪些进程由于过多的锁定和死锁而 产生了相互阻止。 按对象分类的锁： 在按对象分类的锁页面上，活动监视器显示出所选数据 库对象上保存的所有锁和锁请求。 数据库管理员可使用该页面排除与特定表或其他数据库对象产生 冲突的锁定问题。 n 动态管理对象 可以使用动态管理视图和动态管理函数去查询 SQL Server 2005中 的动态元数据。这些元数据提供了有关 SQL Server 当前状态的信息。 进程信息： 动态管理视图和目录视图都位于SSMS 的对象资源管理器中 System View 文件夹下。 动态管理视图与静态视图一样，都在sys 架构中定义。动态管理视 图的名称通常包含dm前缀，便于静态视图区分。 动态动态 管理视图视图描述 Sys.dm_db_partition_stats返回数据库库中每个分区的页页 面和行计计数信息 Sys.dm_exec_sessions返回所有连连接到服务务器的当 前会话话的