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

《SQLServer性能监视和故障诊断.ppt》由会员分享，可在线阅读，更多相关《SQLServer性能监视和故障诊断.ppt（19页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第 14 章 SQL Server 性能监视和故障诊断n监视监视 SQL Server n使用使用“SQL Server Profiler”n使用使用“系统监视器系统监视器”n查看当前活动查看当前活动 监视监视 SQL Server：管理任务：管理任务：小王所在的学校建立一个完整的数据库系统，该系统用于学校的办小王所在的学校建立一个完整的数据库系统，该系统用于学校的办公和教学管理。小王负责维护数据库，他根据数据库特点制定了日常的公和教学管理。小王负责维护数据库，他根据数据库特点制定了日常的监视计划，并使用各种工具对监视计划，并使用各种工具对 SQL Server 运行进行监视。运行进行监视。任

2、务演示：任务演示：小王使用一台计算机通过小王使用一台计算机通过“系统监视器系统监视器”管理控制台远程连接到管理控制台远程连接到 SQL 服务器。监视默认的磁盘活动、处理器使用率等；还监视服务器。监视默认的磁盘活动、处理器使用率等；还监视 SQL Server 对象对象 General Statistics 和和 Buffer Manager 下的计数器（用户下的计数器（用户的连接情况和数据页的使用情况）。的连接情况和数据页的使用情况）。通过使用，发觉网站查询教室安排时慢，检查发现教室安排查询使通过使用，发觉网站查询教室安排时慢，检查发现教室安排查询使用了用了spClassroomQ 存储过程。

3、用存储过程。用 SQL Server Profiler 建立了该存储过建立了该存储过程跟踪。程跟踪。跟踪后发现问题的源头，在跟踪后发现问题的源头，在SSMS中打开活动监视器，查看数据中打开活动监视器，查看数据库的锁的信息，并对数据库对象相互锁定的问题进行解决。库的锁的信息，并对数据库对象相互锁定的问题进行解决。最后检查该表属性。通过维护计划重建了更新频繁的索引。问题最后检查该表属性。通过维护计划重建了更新频繁的索引。问题全部解决。全部解决。重要概念重要概念：在日常工作中，必须不断的对数据库进行维护。维护的依据就是对在日常工作中，必须不断的对数据库进行维护。维护的依据就是对系统状态的监视情况。通

4、过监视及跟踪，找到产生问题的对象，对系统系统状态的监视情况。通过监视及跟踪，找到产生问题的对象，对系统进行维护。这就是数据库生命周期的一个重要组成部分。进行维护。这就是数据库生命周期的一个重要组成部分。SQL Server Profiler：可以跟踪：可以跟踪 SQL Server 的各种对象，进一步查的各种对象，进一步查找故障来源；找故障来源；系统监视器：可以实时监控系统的运行状态并提供警报信息；系统监视器：可以实时监控系统的运行状态并提供警报信息；活动监视器：可以监视当前的活动监视器：可以监视当前的 SQL Server 中的进程和锁的情况。中的进程和锁的情况。使用使用“SQL Serve

5、r Profiler”：SQL Server Profiler 是一种事件探查器，它具备跟踪服务器和数是一种事件探查器，它具备跟踪服务器和数据库活动的功能。可用来捕捉表、文件或据库活动的功能。可用来捕捉表、文件或 T-SQL 脚本中的数据，以供脚本中的数据，以供稍后分析。稍后分析。n监视服务器和数据库活动的图形化界面工具监视服务器和数据库活动的图形化界面工具q根据可重用模板创建跟踪根据可重用模板创建跟踪 q在跟踪运行时，观察跟踪结果在跟踪运行时，观察跟踪结果 q将跟踪结果存储在文件表中，以供进一步分析将跟踪结果存储在文件表中，以供进一步分析 q按需要启动、停止、暂停和修改跟踪结果按需要启动、停

6、止、暂停和修改跟踪结果 q重现跟踪结果重现跟踪结果 n通过使用通过使用 SQL Server Profiler 跟踪跟踪 SQL Server 活动活动 使用使用 SQL Server Profiler 先确定跟踪对象，再选择标准，可以监视先确定跟踪对象，再选择标准，可以监视以下活动：以下活动：q 执行性能较差的查询；执行性能较差的查询；q 登录尝试、故障、连接或连接断开；登录尝试、故障、连接或连接断开；q 语句级别使用的语句级别使用的CPU；q 死锁的问题；死锁的问题；q Tempdb 数据库的性能等。数据库的性能等。“SQL Server Profiler”跟踪选项跟踪选项：使用使用 SQ

7、L Server Profiler 创建一个跟踪时，有很多选项用于定义创建一个跟踪时，有很多选项用于定义将记录的活动和将存储的登录跟踪活动。将记录的活动和将存储的登录跟踪活动。指定跟踪模板指定跟踪模板 跟踪事件是通过指定要监视的事件类和要记录的个别数据值（列）跟踪事件是通过指定要监视的事件类和要记录的个别数据值（列）来确定的。通过选择跟踪基于的模板，添加或删除个别的事件类或列，来确定的。通过选择跟踪基于的模板，添加或删除个别的事件类或列，并应用筛选器限制基于指定标准收集的数据来确定跟踪。并应用筛选器限制基于指定标准收集的数据来确定跟踪。预定义模板：容易配置指定活动类型所需的事件。预定义模板：容

8、易配置指定活动类型所需的事件。用户定义模板用户定义模板保存跟踪数据保存跟踪数据 若稍后分析或重现捕捉到的数据，可先将捕捉到的事件数据保存到若稍后分析或重现捕捉到的数据，可先将捕捉到的事件数据保存到一个文件或一个文件或 SQL Server 表中。表中。保存到表，可以设置的选项：表的位置和名称；表中最多可存储的保存到表，可以设置的选项：表的位置和名称；表中最多可存储的行数。行数。保存到文件，可以设置的选项：文件的位置和名称；最大文件的大保存到文件，可以设置的选项：文件的位置和名称；最大文件的大小；文件满时的跟踪行为；服务器或小；文件满时的跟踪行为；服务器或“SQL Server Profiler

9、”进行的进行的跟踪处理。跟踪处理。通过保存跟踪，可以：通过保存跟踪，可以：使用一个跟踪文件或跟踪表来创建一个工作负载，它被用作数据库使用一个跟踪文件或跟踪表来创建一个工作负载，它被用作数据库引擎优化顾问的输入；引擎优化顾问的输入；使用一个跟踪文件捕捉事件，并将跟踪文件发送到提供支持的程序使用一个跟踪文件捕捉事件，并将跟踪文件发送到提供支持的程序中进行分析；中进行分析；指定跟踪停止时间指定跟踪停止时间 选择设置一个跟踪的时间，允许自动跟踪并在达到指定日期和时间选择设置一个跟踪的时间，允许自动跟踪并在达到指定日期和时间之前保持跟踪运行。之前保持跟踪运行。使用使用 SQL Server 中的查询处理

10、工具，访问数据或查看中的查询处理工具，访问数据或查看 SQL Server Profiler 中的数据。中的数据。跟踪类别、事件和列：跟踪类别、事件和列：跟踪类别跟踪类别 在在 SQL Server Profiler 中，每个类别都是一组相关的事件类。事中，每个类别都是一组相关的事件类。事件类由可以跟踪的事件的类型组成，事件类包含所有可以被事件报告的件类由可以跟踪的事件的类型组成，事件类包含所有可以被事件报告的数据列。数据列。默认的跟踪类别有：默认的跟踪类别有：安全审核；安全审核；会话；会话；存储过程；存储过程；TSQL。事件事件 事件是指事件是指 SQL Server 数据库引擎实例中操作的

11、一次执行。事件由事数据库引擎实例中操作的一次执行。事件由事件属性进一步定义。这些属性在数据列中列出。件属性进一步定义。这些属性在数据列中列出。注意：注意：若要查看所有事件，选中若要查看所有事件，选中跟踪属性跟踪属性对话框中对话框中事件选择事件选择选项选项卡上的卡上的所有事件所有事件复选框。复选框。类别事件描述安全审核Audit Login用户成功登陆到SQL Server安全审核Audit Logout用户已退出SQL Server会话ExistingConnection启动跟踪，现有用户连接属性TSQLSQL:BatchStarting指出已启动 T-SQL 批处理列列 数据列包含事件的属性

12、。数据列包含事件的属性。SQL Server Profiler 使用跟踪输出的数使用跟踪输出的数据列，描述跟踪运行时捕捉到的事件。据列，描述跟踪运行时捕捉到的事件。可以通过使用列筛选器管理列来控制收集数据的类型。可以通过使用列筛选器管理列来控制收集数据的类型。使用使用“系统监视器系统监视器”：n 场景：场景：你是一名企业数据库主管，你的团队要维护该企业的数据库你是一名企业数据库主管，你的团队要维护该企业的数据库24小时小时不间断正常运行。不间断正常运行。为了达到这个目的，需要对为了达到这个目的，需要对 SQL Server 进行全面监控以保证服务进行全面监控以保证服务器的所有子系统都处于协调工

13、作和未超载的状态之下。器的所有子系统都处于协调工作和未超载的状态之下。你觉得使用你觉得使用 Windows 系统监视器可以实现这个目标；通过它还可系统监视器可以实现这个目标；通过它还可设置警报，在系统超载时，通知管理员团队。设置警报，在系统超载时，通知管理员团队。n系统监视器简介系统监视器简介 它是一个它是一个MMC（Microsoft Management Console）管理单元，）管理单元，可使用该管理单元查看系统性能度量指标。可使用该管理单元查看系统性能度量指标。你可以启动控制面板中你可以启动控制面板中管理工具管理工具程序组下的程序组下的性能性能应用程应用程序，来查看序，来查看系统监视

14、器系统监视器。q对象：对象：是计算机系统的主要组件或子系统。对象可以是硬件、软是计算机系统的主要组件或子系统。对象可以是硬件、软件或应用程序。件或应用程序。q计数器：计数器：收集对象各方面的数据。计数器内置于操作系统中，并收集对象各方面的数据。计数器内置于操作系统中，并持续读取性能数据。若一个对象类型有多个实例，计数器会跟踪每持续读取性能数据。若一个对象类型有多个实例，计数器会跟踪每个实例或所有实例的统计信息。个实例或所有实例的统计信息。q实例：实例：同一个对象类型可以有多个实例。查看同一个对象类型可以有多个实例。查看系统监视器系统监视器性性能信息时，可以查看某一实例值或该对象所有实例的综合值

15、。能信息时，可以查看某一实例值或该对象所有实例的综合值。在系统监视器中，可指定要显示的计数器，并可以直线图、直在系统监视器中，可指定要显示的计数器，并可以直线图、直方图或报告的形式显现。方图或报告的形式显现。当性能数据保存为当性能数据保存为 HTML 文件时，可在浏览器中查看并打印直线文件时，可在浏览器中查看并打印直线图、直方图或报告。报告还可导入电子表格中，进行更详细分析。图、直方图或报告。报告还可导入电子表格中，进行更详细分析。性能日志和警报性能日志和警报 在在系统监视器系统监视器中，使用该管理单元将性能信息捕捉到日志文中，使用该管理单元将性能信息捕捉到日志文件中，稍后查看。件中，稍后查看

16、。计数器日志将记录关于硬件资源和基于性能的系统服务的历史信计数器日志将记录关于硬件资源和基于性能的系统服务的历史信息；跟踪日志收集事件跟踪信息，根据跟踪信息测量与事件相关的性能息；跟踪日志收集事件跟踪信息，根据跟踪信息测量与事件相关的性能统计。统计。日志文件将提供用于排除故障和进行归纳的信息。在为当前活动日志文件将提供用于排除故障和进行归纳的信息。在为当前活动的图表、警报和报告提供即时反馈时，还可长时间跟踪计数器。的图表、警报和报告提供即时反馈时，还可长时间跟踪计数器。不可能时时监视某个特定计数器，又希望再超出或低于指定值得不可能时时监视某个特定计数器，又希望再超出或低于指定值得到通知，可配置

17、一个在计数器达到特定极限值时就被激活的警报。到通知，可配置一个在计数器达到特定极限值时就被激活的警报。对象描述SQLServer:Buffer Manager提供关于 SQL Server 使用的内存缓冲区的信息 SQLServer:Databases提供关于 SQL Server 数据库的信息 SQLServer:Locks提供关于 SQL Server 发出的单个锁请求的信息 SQLServer:Memory Manager提供关于 SQL Server 内存利用率的信息 SQL Server-specific 对象允许你监视对象允许你监视 SQL Server 的每个实例的每个实例 SQ

18、L Server-specific 对象包括对象包括:nSQL Server 性能对象性能对象 SQL Server 为系统监视器提供了大量的对象和计数器。对象可以为系统监视器提供了大量的对象和计数器。对象可以是任何一个是任何一个 SQL Server 资源。每个对象包含一个或多个计数器，这资源。每个对象包含一个或多个计数器，这些计数器可确定要监视的对象的多个方面。些计数器可确定要监视的对象的多个方面。n 监视监视SQL Server 的注意事项的注意事项 通过监视通过监视 SQL Server 实例，需要对系统主要方面进行分析。通实例，需要对系统主要方面进行分析。通过监视，可找到并消除性能瓶

19、颈，进一步确定数据库、过监视，可找到并消除性能瓶颈，进一步确定数据库、T-SQL 查询或查询或客户端应用程序的设计问题。客户端应用程序的设计问题。n 监视器的关键区域监视器的关键区域q磁盘系统：磁盘系统：SQL Server 使用使用 Windows 操作系统输入操作系统输入/输出（输出（I/O）调用来实现对）调用来实现对磁盘子系统的读写操作。磁盘子系统的读写操作。SQL Server 管理执行磁盘管理执行磁盘I/O的时机和方式；的时机和方式；Windows 操作系统将操作系统将执行底层执行底层 I/O 操作。在系统中，磁盘操作。在系统中，磁盘 I/O 频繁将导致瓶颈发生。频繁将导致瓶颈发生。

20、q内存：内存：对对 SQL Server 进行周期性的监视，可确保内存的使用情况不出现异常。进行周期性的监视，可确保内存的使用情况不出现异常。首先要确定不存在消耗过多内存的进程，同时也不存在内存不够的情况。首先要确定不存在消耗过多内存的进程，同时也不存在内存不够的情况。qCPU：对对 SQL Server 进行周期性的监视，可确定进行周期性的监视，可确定CPU 使用率是否在正常范围使用率是否在正常范围内。持续的高内。持续的高CPU 使用率可能表示需要升级使用率可能表示需要升级CPU 或添加更多的处理器。或添加更多的处理器。查看当前活动查看当前活动：n 提问：提问：作为作为DBA，希望获得，希望

21、获得 SQL Server 的实时状态的实时状态。有什么好的办法。有什么好的办法可以查看可以查看 SQL Server 中当前的所有进程以及进程的内容呢？中当前的所有进程以及进程的内容呢？可通过使用可通过使用SSMS 的的活动监视器活动监视器组件或动态管理视图来监视组件或动态管理视图来监视SQL Server 中当前活动，以获取有关到数据库引擎的用户连接及其所中当前活动，以获取有关到数据库引擎的用户连接及其所保存的锁的信息。保存的锁的信息。监视当前活动可帮助排除数据库锁定的问题，终止死锁或其他没响监视当前活动可帮助排除数据库锁定的问题，终止死锁或其他没响应的进程。应的进程。n 活动监视器活动监

22、视器 活动监视器是活动监视器是SSMS 中的图形化工具，显示有关当前用户进程和中的图形化工具，显示有关当前用户进程和锁的信息。（在锁的信息。（在管理管理文件夹中可看到）文件夹中可看到）在在活动监视器活动监视器的选择页上可查看到三个信息：进程信息、按的选择页上可查看到三个信息：进程信息、按进程分类的锁和按对象分类的锁。进程分类的锁和按对象分类的锁。进程信息：进程信息：在在进程信息进程信息页面上。页面上。活动监视器活动监视器将显示一个列表，它列将显示一个列表，它列出了系统中所有的连接进程。出了系统中所有的连接进程。数据库管理员可根据该页面来确定哪些进程正处于活动状态，若数据库管理员可根据该页面来确

23、定哪些进程正处于活动状态，若需要还可终止某一进程。需要还可终止某一进程。按进程分类的锁：按进程分类的锁：在在按进程分类的锁按进程分类的锁页面上，页面上，活动监视器活动监视器显示所选进程保显示所选进程保存的所有锁和锁定请求。存的所有锁和锁定请求。数据库管理员可根据该页面确定哪些进程由于过多的锁定和死锁而数据库管理员可根据该页面确定哪些进程由于过多的锁定和死锁而产生了相互阻止。产生了相互阻止。按对象分类的锁：按对象分类的锁：在在按对象分类的锁按对象分类的锁页面上，页面上，活动监视器活动监视器显示出所选数据显示出所选数据库对象上保存的所有锁和锁请求。库对象上保存的所有锁和锁请求。数据库管理员可使用该

24、页面排除与特定表或其他数据库对象产生数据库管理员可使用该页面排除与特定表或其他数据库对象产生冲突的锁定问题。冲突的锁定问题。n 动态管理对象动态管理对象 可以使用动态管理视图和动态管理函数去查询可以使用动态管理视图和动态管理函数去查询 SQL Server 2005中中的动态元数据。这些元数据提供了有关的动态元数据。这些元数据提供了有关 SQL Server 当前状态的信息。当前状态的信息。进程信息：进程信息：动态管理视图和目录视图都位于动态管理视图和目录视图都位于SSMS 的的对象资源管理器对象资源管理器中中System View 文件夹下。文件夹下。动态管理视图与静态视图一样，都在动态管理

25、视图与静态视图一样，都在sys 架构中定义。动态管理视架构中定义。动态管理视图的名称通常包含图的名称通常包含dm前缀，便于静态视图区分。前缀，便于静态视图区分。动态管理视图动态管理视图描述描述Sys.dm_db_partition_stats返回数据库中每个分区的页面返回数据库中每个分区的页面和行计数信息和行计数信息Sys.dm_exec_sessions返回所有连接到服务器的当前返回所有连接到服务器的当前会话的信息会话的信息Sys.dm_broker_queue_monitors返回系统中每个返回系统中每个Service Broker 队列监视器中的信息队列监视器中的信息 查询动态管理视图：查询动态管理视图：可使用标准可使用标准SELECT语句查询动态管理视图。语句查询动态管理视图。举例：用代码返回系统中当前锁定状态的详细信息。举例：用代码返回系统中当前锁定状态的详细信息。SELECT*FROM 多媒体：使用活动监视器处理未提交事务多媒体：使用活动监视器处理未提交事务 在这个交互式多媒体中，你通过活动在这个交互式多媒体中，你通过活动监视器找到未提交的事务的相关进程。通监视器找到未提交的事务的相关进程。通过取消该进程，消除了未提交事务对数据过取消该进程，消除了未提交事务对数据库产生的影响。库产生的影响。