SQL_Server_性能监视和故障诊断

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