SQL优化过程(SQL Server)

1、2-SQL优化过程(SQL Server)分析优化工具分析优化工具l动态管理视图 (DMV) l事件跟踪器(SQL SERVER PROFILER)lSET STATISTICS IO/TIME/PROFILE ONl执行计划l数据库引擎优化顾问lWINDOWS性能监视器监视和优化性能监视和优化性能 监视数据库的目的是评估服务器的性能。 有效监视包括定期拍摄当前性能的快照来隔离导致问题的进程，以及连续收集数据来跟踪性能趋势。 日常数据库性能评估有助于使响应时间最小化并使吞吐量最大化，从而实现最佳性能。 有效网络流量、磁盘 I/O 和 CPU 使用率是实现最佳性能的关键。 您需要透彻地分析应用程

2、序要求，了解数据的逻辑结构和物理结构，评估数据库使用情况，并协商使用 如联机事务处理 (OLTP) 与决策支持 冲突之间的平衡措施。 监视和优化数据库性能的好处监视和优化数据库性能的好处 Microsoft SQL Server 和 Microsoft Windows 操作系统提供实用工具，允许您查看数据库的当前状态并跟踪条件变化时的性能。 可使用多种工具和技术来监视 Microsoft SQL Server。 了解如何监视 SQL Server 有助于： 确定是否可以提高性能。 例如，通过监视常用查询的响应时间，可以确定是否需要更改表的查询或索引。 评估用户活动。 例如，通过监视尝试连接到

3、SQL Server 实例的用户，可以确定安全设置是否充分以及是否需要测试应用程序或开发系统。 例如，通过在执行 SQL 查询时对其进行监视，可以确定这些查询是否编写正确并生成预期的结果。 解决任何问题或调试应用程序组件（如存储过程）。 动态环境中的监视动态环境中的监视 监视操作非常重要，因为 SQL Server 在动态环境中提供服务。 更改条件会导致性能发生变化。 在评估中，您可以看到性能会随着用户数量增加、用户访问和连接方法改变、数据库内容增加、客户端应用程序改变、应用程序中的数据变化、查询变得更加复杂以及网络流量上升而变化。 通过使用 SQL Server 工具来监视性能，可以将性能的

4、某些变化与条件和复杂查询的变化相关联。 下列方案提供了此方面的示例： 通过监视常用查询的响应时间，可以确定是否需要更改查询或执行查询的表上的索引。 通过在执行 Transact-SQL 查询时对其进行监视，可以确定这些查询是否编写正确并生成预期的结果。 通过监视试图连接到 SQL Server 实例的用户，可以确定安全设置是否得当并测试应用程序或开发系统。 响应时间是指以可视确认信息（指出正在处理查询）的形式将结果集的首行返回给用户所需的时间。 吞吐量是指在一段给定时间内，服务器处理的查询总数。 随着用户数量的增加，对服务器资源的竞争也会更激烈，进而导致响应时间增加和总体吞吐量减少。 监视监视

5、 SQL Server SQL Server 组件组件 确定监视目标。 选择相应工具。 标识要监视的组件。 选择那些组件的度量。 监视服务器。 分析数据。 确定监视目标确定监视目标 建立性能基线。 标识一段时间内的性能变化。 诊断特定性能问题。 标识要优化的组件或进程。 比较对不同客户端应用程序性能的影响。 审核用户活动。 在不同负荷下测试服务器。 测试数据库体系结构。 测试维护计划。 测试备份和还原计划。 确定何时修改硬件配置选择相应工具选择相应工具 确定监视原因后，应该为该监视类型选择相应的工具。 Windows 操作系统和 SQL Server 提供了一整套用于在大型事务环境中监视服务器

6、的工具。 这些工具清楚地显示 SQL Server 数据库引擎实例或 SQL Server Analysis Services 实例的状态。 Windows 提供下列工具来监视在服务器上运行的应用程序： 系统监视器，使您可以收集和查看有关活动（如内存、磁盘和处理器使用）的实时数据 性能日志和警报 任务管理器 SQL Server 提供下列工具来监视 SQL Server 的组件： SQL 跟踪 SQL Server Profiler 分布式重播实用工具 SQL Server Management Studio 活动监视器 SQL Server Management Studio 图形显示计划

7、存储过程 数据库控制台命令 (DBCC) 内置函数 跟踪标志 哪些因素影响性能？ 等待系统资源等待系统资源 内存，内存，CPU，IO 错误的配置错误的配置 硬件硬件 & 软件软件 不优化的查询不优化的查询 & 设计设计 写法不好写法不好, 设计不周设计不周 索引问题索引问题 和工作量与资源配置没有关系和工作量与资源配置没有关系 不好的执行计划不好的执行计划 和客户端网络交互太多和客户端网络交互太多监视监视 SQL Server ：管理任务：管理任务： 小王所在的学校建立一个完整的数据库系统，该系统用于学校的办小王所在的学校建立一个完整的数据库系统，该系统用于学校的办公和教学管理

8、。小王负责维护数据库，他根据数据库特点制定了日常的公和教学管理。小王负责维护数据库，他根据数据库特点制定了日常的监视计划，并使用各种工具对监视计划，并使用各种工具对 SQL Server 运行进行监视。运行进行监视。 任务演示：任务演示： 小王使用一台计算机通过小王使用一台计算机通过“系统监视器系统监视器”管理控制台远程连接到管理控制台远程连接到 SQL 服务器。监视默认的磁盘活动、处理器使用率等；还监视服务器。监视默认的磁盘活动、处理器使用率等；还监视 SQL Server 对对象象 General Statistics 和和 Buffer Manager 下的计数器（用户的连接情况和数下的

9、计数器（用户的连接情况和数据页的使用情况）。据页的使用情况）。 通过使用，发觉网站查询教室安排时慢，检查发现教室安排查询使用通过使用，发觉网站查询教室安排时慢，检查发现教室安排查询使用了了spClassroomQ 存储过程。用存储过程。用 SQL Server Profiler 建立了该存储过程跟踪。建立了该存储过程跟踪。 跟踪后发现问题的源头，在跟踪后发现问题的源头，在SSMS中打开活动监视器，查看数据库中打开活动监视器，查看数据库的锁的信息，并对数据库对象相互锁定的问题进行解决。的锁的信息，并对数据库对象相互锁定的问题进行解决。 最后检查该表属性。通过维护计划重建了更新频繁的索引。问题最后

10、检查该表属性。通过维护计划重建了更新频繁的索引。问题全部解决。全部解决。重要概念重要概念： 在日常工作中，必须不断的对数据库进行维护。维护的依据就是对在日常工作中，必须不断的对数据库进行维护。维护的依据就是对系统状态的监视情况。通过监视及跟踪，找到产生问题的对象，对系统系统状态的监视情况。通过监视及跟踪，找到产生问题的对象，对系统进行维护。这就是数据库生命周期的一个重要组成部分。进行维护。这就是数据库生命周期的一个重要组成部分。 SQL Server Profiler ：可以跟踪：可以跟踪 SQL Server 的各种对象，进一步查找故的各种对象，进一步查找故障来源；障来源； 系统监视器：可以

11、实时监控系统的运行状态并提供警报信息；系统监视器：可以实时监控系统的运行状态并提供警报信息； 活动监视器：可以监视当前的活动监视器：可以监视当前的 SQL Server 中的进程和锁的情况。中的进程和锁的情况。使用使用 “SQL Server Profiler ” ： SQL Server Profiler 是一种事件探查器，它具备跟踪服务器和数据库是一种事件探查器，它具备跟踪服务器和数据库活动的功能。可用来捕捉表、文件或活动的功能。可用来捕捉表、文件或 T-SQL 脚本中的数据，以供稍后分脚本中的数据，以供稍后分析。析。n监视服务器和数据库活动的图形化界面工具监视服务器和数据库活动的图形化界

12、面工具q根据可重用模板创建跟踪根据可重用模板创建跟踪 q在跟踪运行时，观察跟踪结果在跟踪运行时，观察跟踪结果 q将跟踪结果存储在文件表中，以供进一步分析将跟踪结果存储在文件表中，以供进一步分析 q按需要启动、停止、暂停和修改跟踪结果按需要启动、停止、暂停和修改跟踪结果 q重现跟踪结果重现跟踪结果 n通过使用通过使用 SQL Server Profiler 跟踪跟踪 SQL Server 活动活动 使用使用 SQL Server Profiler 先确定跟踪对象，再选择标准，可以监视以先确定跟踪对象，再选择标准，可以监视以下活动：下活动：q 执行性能较差的查询；执行性能较差的查询； q 登录尝试

13、、故障、连接或连接断开；登录尝试、故障、连接或连接断开； q 语句级别使用的语句级别使用的CPU；q 死锁的问题；死锁的问题；q Tempdb 数据库的性能等。数据库的性能等。 “SQL Server Profiler” 跟踪选项跟踪选项 ： 使用使用 SQL Server Profiler 创建一个跟踪时，有很多选项用于定义将记创建一个跟踪时，有很多选项用于定义将记录的活动和将存储的登录跟踪活动。录的活动和将存储的登录跟踪活动。 指定跟踪模板指定跟踪模板 跟踪事件是通过指定要监视的事件类和要记录的个别数据值（列跟踪事件是通过指定要监视的事件类和要记录的个别数据值（列）来确定的。通过选择跟踪基

14、于的模板，添加或删除个别的事件类或）来确定的。通过选择跟踪基于的模板，添加或删除个别的事件类或列，并应用筛选器限制基于指定标准收集的数据来确定跟踪。列，并应用筛选器限制基于指定标准收集的数据来确定跟踪。 预定义模板：容易配置指定活动类型所需的事件。预定义模板：容易配置指定活动类型所需的事件。 用户定义模板用户定义模板保存跟踪数据保存跟踪数据 若稍后分析或重现捕捉到的数据，可先将捕捉到的事件数据保存到若稍后分析或重现捕捉到的数据，可先将捕捉到的事件数据保存到一个文件或一个文件或 SQL Server 表中。表中。 保存到表，可以设置的选项：表的位置和名称；表中最多可存储的保存到表，可以设置的选项

15、：表的位置和名称；表中最多可存储的行数。行数。 保存到文件，可以设置的选项：文件的位置和名称；最大文件的大保存到文件，可以设置的选项：文件的位置和名称；最大文件的大小；文件满时的跟踪行为；服务器或小；文件满时的跟踪行为；服务器或“SQL Server Profiler ”进行的跟进行的跟踪处理。踪处理。 通过保存跟踪，可以：通过保存跟踪，可以： 使用一个跟踪文件或跟踪表来创建一个工作负载，它被用作数据库使用一个跟踪文件或跟踪表来创建一个工作负载，它被用作数据库引擎优化顾问的输入；引擎优化顾问的输入； 使用一个跟踪文件捕捉事件，并将跟踪文件发送到提供支持的程序使用一个跟踪文件捕捉事件，并将跟踪文

16、件发送到提供支持的程序中进行分析；中进行分析； 指定跟踪停止时间指定跟踪停止时间 选择设置一个跟踪的时间，允许自动跟踪并在达到指定日期和时间选择设置一个跟踪的时间，允许自动跟踪并在达到指定日期和时间之前保持跟踪运行。之前保持跟踪运行。 使用使用 SQL Server 中的查询处理工具，访问数据或查看中的查询处理工具，访问数据或查看 SQL Server Profiler 中的数据。中的数据。 跟踪类别、事件和列：跟踪类别、事件和列： 跟踪类别跟踪类别 在在 SQL Server Profiler 中，每个类别都是一组相关的事件类。事件类中，每个类别都是一组相关的事件类。事件类由可以跟踪的事件的

17、类型组成，事件类包含所有可以被事件报告的数据由可以跟踪的事件的类型组成，事件类包含所有可以被事件报告的数据列。列。默认的跟踪类别有：默认的跟踪类别有：安全审核；安全审核； 会话；会话； 存储过程；存储过程； TSQL。 事件事件 事件是指事件是指 SQL Server 数据库引擎实例中操作的一次执行。事件由事件数据库引擎实例中操作的一次执行。事件由事件属性进一步定义。这些属性在数据列中列出。属性进一步定义。这些属性在数据列中列出。注意：注意： 若要查看所有事件，选中若要查看所有事件，选中跟踪属性跟踪属性对话框中对话框中事件选择事件选择选项选项卡上的卡上的所有事件所有事件复选框。复选框。类别事件

18、描述安全审核Audit Login用户成功登陆到SQL Server安全审核Audit Logout用户已退出SQL Server会话ExistingConnection启动跟踪，现有用户连接属性TSQLSQL:BatchStarting指出已启动 T-SQL 批处理列列 数据列包含事件的属性。数据列包含事件的属性。 SQL Server Profiler 使用跟踪输出的数据列使用跟踪输出的数据列，描述跟踪运行时捕捉到的事件。，描述跟踪运行时捕捉到的事件。 可以通过使用列筛选器管理列来控制收集数据的类型。可以通过使用列筛选器管理列来控制收集数据的类型。使用使用“系统监视器系统监视器” ：n 场

19、景：场景： 你是一名企业数据库主管，你的团队要维护该企业的数据库你是一名企业数据库主管，你的团队要维护该企业的数据库24小时小时不间断正常运行。不间断正常运行。 为了达到这个目的，需要对为了达到这个目的，需要对 SQL Server 进行全面监控以保证服务进行全面监控以保证服务器的所有子系统都处于协调工作和未超载的状态之下。器的所有子系统都处于协调工作和未超载的状态之下。 你觉得使用你觉得使用 Windows 系统监视器可以实现这个目标；通过它还可系统监视器可以实现这个目标；通过它还可设置警报，在系统超载时，通知管理员团队。设置警报，在系统超载时，通知管理员团队。 n系统监视器简介系统监视器简

20、介 它是一个它是一个MMC（Microsoft Management Console）管理单元，）管理单元，可使用该管理单元查看系统性能度量指标。可使用该管理单元查看系统性能度量指标。 你可以启动控制面板中你可以启动控制面板中管理工具管理工具程序组下的程序组下的性能性能应用程应用程序，来查看序，来查看系统监视器系统监视器。 q对象：对象：是计算机系统的主要组件或子系统。对象可以是硬件、软是计算机系统的主要组件或子系统。对象可以是硬件、软件或应用程序。件或应用程序。q计数器：计数器：收集对象各方面的数据。计数器内置于操作系统中，并收集对象各方面的数据。计数器内置于操作系统中，并持续读取性能数据。

21、若一个对象类型有多个实例，计数器会跟踪持续读取性能数据。若一个对象类型有多个实例，计数器会跟踪每个实例或所有实例的统计信息。每个实例或所有实例的统计信息。q实例：实例：同一个对象类型可以有多个实例。查看同一个对象类型可以有多个实例。查看系统监视器系统监视器性性能信息时，可以查看某一实例值或该对象所有实例的综合值。能信息时，可以查看某一实例值或该对象所有实例的综合值。 在在系统监视器系统监视器中，可指定要显示的计数器，并可以直线图、直中，可指定要显示的计数器，并可以直线图、直方图或报告的形式显现。方图或报告的形式显现。 当性能数据保存为当性能数据保存为 HTML 文件时，可在浏览器中查看并打印直

22、线图、文件时，可在浏览器中查看并打印直线图、直方图或报告。报告还可导入电子表格中，进行更详细分析。直方图或报告。报告还可导入电子表格中，进行更详细分析。 性能日志和警报性能日志和警报 在在系统监视器系统监视器中，使用该管理单元将性能信息捕捉到日志文中，使用该管理单元将性能信息捕捉到日志文件中，稍后查看。件中，稍后查看。 计数器日志将记录关于硬件资源和基于性能的系统服务的历史信计数器日志将记录关于硬件资源和基于性能的系统服务的历史信息；跟踪日志收集事件跟踪信息，根据跟踪信息测量与事件相关的性息；跟踪日志收集事件跟踪信息，根据跟踪信息测量与事件相关的性能统计。能统计。 日志文件将提供用于排除故障和

23、进行归纳的信息。在为当前活动日志文件将提供用于排除故障和进行归纳的信息。在为当前活动的图表、警报和报告提供即时反馈时，还可长时间跟踪计数器。的图表、警报和报告提供即时反馈时，还可长时间跟踪计数器。 不可能时时监视某个特定计数器，又希望再超出或低于指定值得不可能时时监视某个特定计数器，又希望再超出或低于指定值得到通知，可配置一个在计数器达到特定极限值时就被激活的警报。到通知，可配置一个在计数器达到特定极限值时就被激活的警报。对象描述SQLServer:Buffer Manager提供关于 SQL Server 使用的内存缓冲区的信息 SQLServer:Databases提供关于 SQL Ser

24、ver 数据库的信息 SQLServer:Locks提供关于 SQL Server 发出的单个锁请求的信息 SQLServer:Memory Manager提供关于 SQL Server 内存利用率的信息 SQL Server-specific 对象允许你监视对象允许你监视 SQL Server 的每个实例的每个实例 SQL Server-specific 对象包括对象包括:nSQL Server 性能对象性能对象 SQL Server 为系统监视器提供了大量的对象和计数器。对象可为系统监视器提供了大量的对象和计数器。对象可以是任何一个以是任何一个 SQL Server 资源。每个对象包含一个

25、或多个计数器，资源。每个对象包含一个或多个计数器，这些计数器可确定要监视的对象的多个方面。这些计数器可确定要监视的对象的多个方面。n 监视监视SQL Server 的注意事项的注意事项 通过监视通过监视 SQL Server 实例，需要对系统主要方面进行分析。通实例，需要对系统主要方面进行分析。通过监视，可找到并消除性能瓶颈，进一步确定数据库、过监视，可找到并消除性能瓶颈，进一步确定数据库、T-SQL 查询或查询或客户端应用程序的设计问题。客户端应用程序的设计问题。n 监视器的关键区域监视器的关键区域q磁盘系统：磁盘系统： SQL Server 使用使用 Windows 操作系统输入操作系统输

26、入/输出（输出（I/O）调用来实现对）调用来实现对磁盘子系统的读写操作。磁盘子系统的读写操作。 SQL Server 管理执行磁盘管理执行磁盘I/O的时机和方式；的时机和方式； Windows 操作系统将操作系统将执行底层执行底层 I/O 操作。在系统中，磁盘操作。在系统中，磁盘 I/O 频繁将导致瓶颈发生。频繁将导致瓶颈发生。q内存：内存： 对对 SQL Server 进行周期性的监视，可确保内存的使用情况不出现异常。首进行周期性的监视，可确保内存的使用情况不出现异常。首先要确定不存在消耗过多内存的进程，同时也不存在内存不够的情况。先要确定不存在消耗过多内存的进程，同时也不存在内存不够的情况

27、。qCPU： 对对 SQL Server 进行周期性的监视，可确定进行周期性的监视，可确定CPU 使用率是否在正常范围内。使用率是否在正常范围内。持续的高持续的高CPU 使用率可能表示需要升级使用率可能表示需要升级CPU 或添加更多的处理器。或添加更多的处理器。查看当前活动查看当前活动 ：n 提问：提问： 作为作为DBA，希望获得，希望获得 SQL Server 的实时状态的实时状态。有什么好的办法。有什么好的办法可以查看可以查看 SQL Server 中当前的所有进程以及进程的内容呢？中当前的所有进程以及进程的内容呢？ 可通过使用可通过使用SSMS 的的活动监视器活动监视器组件或动态管理视图

28、来监视组件或动态管理视图来监视SQL Server 中当前活动，以获取有关到数据库引擎的用户连接及其中当前活动，以获取有关到数据库引擎的用户连接及其所保存的锁的信息。所保存的锁的信息。 监视当前活动可帮助排除数据库锁定的问题，终止死锁或其他没响监视当前活动可帮助排除数据库锁定的问题，终止死锁或其他没响应的进程。应的进程。n 活动监视器活动监视器 活动监视器是活动监视器是SSMS 中的图形化工具，显示有关当前用户进程和中的图形化工具，显示有关当前用户进程和锁的信息。（在锁的信息。（在管理管理文件夹中可看到）文件夹中可看到） 在在活动监视器活动监视器的选择页上可查看到三个信息：进程信息、按的选择页上可查看到三个信息：进程信息、按进程分类的锁和按对象分类的锁。进程分类的锁和按对象分类的锁。 进程信息：进程信息： 在在进程信息进程信息页面上。页面上。活动监视器活动监视器将显示一个列表，它列将显示一个列表，它列出了系统中所有的连接进程。出了系统中所有的连接进程。 数据库管理员可根据该页面来确定哪些进程正处于活动状态，若数据库管理员可根据该页面来确定哪些进程正处于活动状态，若需要还可终止某一进程。需要还可终止某一进程。 按进程分类的锁：按进程分类的锁： 在在按进程分类的锁按进程分类的锁页面上，页面上，活动监视器活动监