SQL优化过程(SQL Server)

TSQL。
事件
事件是指 SQL Server 数据库引擎实例中操作的一次执行。事件由事件 属性进一步定义。这些属性在数据列中列出。
注意：
若要查看所有事件，选中『跟踪属性』对话框中『事件选择』选项 卡上的『所有事件』复选框。
类别
安全审核
安全审核 会话 TSQL
事件
Audit Login
Audit Logout ExistingConnection SQL:BatchStarting
• 通过在执行 Transact-SQL 查询时对其进行监视，可以确定这些查 询是否编写正确并生成预期的结果。 • 通过监视试图连接到 SQL Server 实例的用户，可以确定安全设置 是否得当并测试应用程序或开发系统。 • 响应时间是指以可视确认信息（指出正在处理查询）的形式将结 果集的首行返回给用户所需的时间。 吞吐量是指在一段给定时间 内，服务器处理的查询总数。 • 随着用户数量的增加，对服务器资源的竞争也会更激烈，进而导 致响应时间增加和总体吞吐量减少。

SQL Server 性能对象
SQL Server 为系统监视器提供了大量的对象和计数器。对象可 以是任何一个 SQL Server 资源。每个对象包含一个或多个计数器， 这些计数器可确定要监视的对象的多个方面。 • SQL Server-specific 对象允许你监视 SQL Server 的每个实例 • SQL Server-specific 对象包括:
监视 SQL Server 组件
• 确定监视目标。 • 选择相应工具。 • 标识要监视的组件。 • 选择那些组件的度量。 • 监视服务器。 • 分析数据。
确定监视目标
• • • • • • • • • • • 建立性能基线。 标识一段时间内的性能变化。 诊断特定性能问题。 标识要优化的组件或进程。 比较对不同客户端应用程序性能的影响。 审核用户活动。 在不同负荷下测试服务器。 测试数据库体系结构。 测试维护计划。 测试备份和还原计划。 确定何时修改硬件配置
SQL Server 提供下列工具来监视 SQL Server 的组件：
• SQL 跟踪 • SQL Server Profiler • 分布式重播实用工具 • SQL Server Management Studio 活动监视器 • SQL Server Management Studio 图形显示计划 • 存储过程 • 数据库控制台命令 (DBCC) • 内置函数 • 跟踪标志
性能日志和警报
在『系统监视器』中，使用该管理单元将性能信息捕捉到日志文 件中，稍后查看。 计数器日志将记录关于硬件资源和基于性能的系统服务的历史信 息；跟踪日志收集事件跟踪信息，根据跟踪信息测量与事件相关的性 能统计。 日志文件将提供用于排除故障和进行归纳的信息。在为当前活动 的图表、警报和报告提供即时反馈时，还可长时间跟踪计数器。 不可能时时监视某个特定计数器，又希望再超出或低于指定值得 到通知，可配置一个在计数器达到特定极限值时就被激活的警报。

监视服务器和数据库活动的图形化界面工具

根据可重用模板创建跟踪 在跟踪运行时，观察跟踪结果 将跟踪结果存储在文件表中，以供进一步分析 按需要启动、停止、暂停和修改跟踪结果 重现跟踪结果
通过使用
SQL Server Profiler 跟踪 SQL Server 活动
使用 SQL Server Profiler 先确定跟踪对象，再选择标准，可以监视以 下活动： 执行性能较差的查询； 登录尝试、故障、连接或连接断开； 语句级别使用的CPU； 死锁的问题； Tempdb 数据库的性能等。
预定义模板：容易配置指定活动类型所需的事件。
用户定义模板
保存跟踪数据
若稍后分析或重现捕捉到的数据，可先将捕捉到的事件数据保存到 一个文件或 SQL Server 表中。 保存到表，可以设置的选项：表的位置和名称；表中最多可存储的 行数。 保存到文件，可以设置的选项：文件的位置和名称；最大文件的大 小；文件满时的跟踪行为；服务器或“SQL Server Profiler ”进行的跟 踪处理。
选择设置一个跟踪的时间，允许自动跟踪并在达到指定日期和时间 之前保持跟踪运行。
• 跟踪类别、事件和列：
跟踪类别 在 SQL Server Profiler 中，每个类别都是一组相关的事件类。事件类 由可以跟踪的事件的类型组成，事件类包含所有可以被事件报告的数据 列。 默认的跟踪类别有：
安全审核；
会话； 存储过程；

计数器：收集对象各方面的数据。计数器内置于操作系统中，并
持续读取性能数据。若一个对象类型有多个实例，计数器会跟踪
每个实例或所有实例的统计信息。

实例：同一个对象类型可以有多个实例。查看『系统监视器』性
能信息时，可以查看某一实例值或该对象所有实例的综合值。
在『系统监视器』中，可指定要显示的计数器，并可以直线图、直 方图或报告的形式显现。 当性能数据保存为 HTML 文件时，可在浏览器中查看并打印直线图、 直方图或报告。报告还可导入电子表格中，进行更详细分析。

系统监视器简介
它是一个MMC（Microsoft Management Console）管理单元， 可使用该管理单元查看系统性能度量指标。 你可以启动控制面板中『管理工具』程序组下的『性能』应用程 序，来查看『系统监视器』。

对象：是计算机系统的主要组件或子系统。对象可以是硬件、软
件或应用程序。
任务演示：
小王使用一台计算机通过“系统监视器”管理控制台远程连接到 SQL 服务器。监视默认的磁盘活动、处理器使用率等；还监视 SQL Server 对 象 General Statistics 和 Buffer Manager 下的计数器（用户的连接情况和数 据页的使用情况）。
通过使用，发觉网站查询教室安排时慢，检查发现教室安排查询使 用了spClassroomQ 存储过程。用 SQL Server Profiler 建立了该存储过程跟踪。
动态环境中的监视
• 监视操作非常重要，因为 SQL Server 在动态环境中提供服务。 更 改条件会导致性能发生变化。 在评估中，您可以看到性能会随着 用户数量增加、用户访问和连接方法改变、数据库内容增加、客 户端应用程序改变、应用程序中的数据变化、查询变得更加复杂 以及网络流量上升而变化。 通过使用 SQL Server 工具来监视性能 ，可以将性能的某些变化与条件和复杂查询的变化相关联。 下列 方案提供了此方面的示例： • 通过监视常用查询的响应时间，可以确定是否需要更改查询或执 行查询的表上的索引。
跟踪后发现问题的源头，在SSMS中打开活动监视器，查看数据库 的锁的信息，并对数据库对象相互锁定的问题进行解决。 最后检查该表属性。通过维护计划重建了更新频繁的索引。问题 全部解决。
重要概念：
在日常工作中，必须不断的对数据库进行维护。维护的依据就是对 系统状态的监视情况。通过监视及跟踪，找到产生问题的对象，对系统 进行维护。这就是数据库生命周期的一个重要组成部分。 • SQL Server Profiler ：可以跟踪 SQL Server 的各种对象，进一步查找故 障来源；
通过保存跟踪，可以：
使用一个跟踪文件或跟踪表来创建一个工作负载，它被用作数据库 引擎优化顾问的输入；
使用一个跟踪文件捕捉事件，并将跟踪文件发送到提供支持的程序 中进行分析；
使用 SQL Server 中的查询处理工具，访问数据或查看 SQL Server Profiler 中的数据。
指定跟踪停止时间
选择相应工具
• 确定监视原因后，应该为该监视类型选择相应的工具。 Windows 操作系统和 SQL Server 提供了一整套用于在大型事务环境中监视 服务器的工具。 这些工具清楚地显示 SQL Server 数据库引擎实例 或 SQL Server Analysis Services 实例的状态。 • Windows 提供下列工具来监视在服务器上运行的应用程序： • 系统监视器，使您可以收集和查看有关活动（如内存、磁盘和处 理器使用）的实时数据 • 性能日志和警报 • 任务管理器

场景：
你是一名企业数据库主管，你的团队要维护该企业的数据库24小时 不间断正常运行。 为了达到这个目的，需要对 SQL Server 进行全面监控以保证服务 器的所有子系统都处于协调工作和未超载的状态之下。 你觉得使用 Windows 系统监视器可以实现这个目标；通过它还可 设置警报，在系统超载时，通知管理员团队。
2--SQL优化过程(SQL Server)
分析优化工具
动态管理视图 (DMV) 事件跟踪器(SQL SERVER PROFILER) SET STATISTICS IO/TIME/PROFILE ON 执行计划 数据库引擎优化顾问 WINDOWS性能监视器
监视和优化性能
• 监视数据库的目的是评估服务器的性能。 有效监视包括定期拍摄 当前性能的快照来隔离导致问题的进程，以及连续收集数据来跟 踪性能趋势。 • 日常数据库性能评估有助于使响应时间最小化并使吞吐量最大化 ，从而实现最佳性能。 有效网络流量、磁盘 I/O 和 CPU 使用率是 实现最佳性能的关键。 您需要透彻地分析应用程序要求，了解数 据的逻辑结构和物理结构，评估数据库使用情况，并协商使用 [ 如联机事务处理 (OLTP) 与决策支持] 冲突之间的平衡措施。
哪些因素影响性能？
• 等待系统资源
• 内存，CPU，IO
• 错误配置
• 硬件 & 软件
• 不优化的查询 & 设计
• 写法不好, 设计不周
• 索引问题
• 和工作量与资源配置没有关系
• 不好的执行计划 • 和客户端网络交互太多
监视 SQL Server ： 管理任务：
小王所在的学校建立一个完整的数据库系统，该系统用于学校的办 公和教学管理。小王负责维护数据库，他根据数据库特点制定了日常的 监视计划，并使用各种工具对 SQL Server 运行进行监视。

• “SQL Server Profiler” 跟踪选项 ：
使用 SQL Server Profiler 创建一个跟踪时，有很多选项用于定义将记 录的活动和将存储的登录跟踪活动。 指定跟踪模板 跟踪事件是通过指定要监视的事件类和要记录的个别数据值（列 ）来确定的。通过选择跟踪基于的模板，添加或删除个别的事件类或 列，并应用筛选器限制基于指定标准收集的数据来确定跟踪。
描述
用户成功登陆到SQL Server
用户已退出SQL Server 启动跟踪，现有用户连接属 性 指出已启动 T-SQL 批处理
列
数据列包含事件的属性。 SQL Server Profiler 使用跟踪输出的数据列 ，描述跟踪运行时捕捉到的事件。
可以通过使用列筛选器管理列来控制收集数据的类型。
使用“系统监视器” ：
监视和优化数据库性能的好处
• Microsoft SQL Server 和 Microsoft Windows 操作系统提供实用工具 ，允许您查看数据库的当前状态并跟踪条件变化时的性能。 可使 用多种工具和技术来监视 Microsoft SQL Server。 了解如何监视 SQL Server 有助于： • 确定是否可以提高性能。 例如，通过监视常用查询的响应时间， 可以确定是否需要更改表的查询或索引。 • 评估用户活动。 例如，通过监视尝试连接到 SQL Server 实例的用 户，可以确定安全设置是否充分以及是否需要测试应用程序或开 发系统。 例如，通过在执行 SQL 查询时对其进行监视，可以确定 这些查询是否编写正确并生成预期的结果。 • 解决任何问题或调试应用程序组件（如存储过程）。
• 系统监视器：可以实时监控系统的运行状态并提供警报信息；
• 活动监视器：可以监视当前的 SQL Server 中的进程和锁的情况。
使用 “SQL Server Profiler ” ：
SQL Server Profiler 是一种事件探查器，它具备跟踪服务器和数据库 活动的功能。可用来捕捉表、文件或 T-SQL 脚本中的数据，以供稍后分 析。