数据库优化方法 (一)

我经常碰到有人会问一些数据库优化方面的问题，我觉得这是一最基本技能要求，特别是使用sql server 2005，对于sql server的优化，使用的专职dba或兼职dba们工作更轻松，效率更高了，但是还是有很多新人可能不大了解，我在这儿写一下最基本的一些介绍。

    一、首先介绍一下sql server 2005中优化数据时使用到的两个工具    
    1.第一个工具sql server profile，这个工具在sql server 2000中也有，公司在使用sql 2000数据库时，我经常使用这个工具，来捕捉那些有性能问题的sql语句。
       sql server profiler 是用于服务器捕获sql server 2005事件的工具。事件可以保存在一个跟踪文件中，可在以后对该文件进行分析，也可以在试图诊断某个问题时，用它来重播某一系列的步骤。
      sql server profiler 可用于以下活动：
      1）逐步分析有问题的查询以找到出现问题的原因。
      2）查找并诊断运行慢的查询
      3）捕获导致某个问题的一系列T-SQL语句，然后用所保存的跟踪在某台测试服务器上复制此问题，接着在测试服务器上诊断问题。
      4）监视SQL SERVER的性能以优化工作负荷
      5）使性能计数器与诊断问题关联

       使用 SQL Server Profiler时，您可以按“持续时间”、CPU、“读”或“写”数据列将跟踪或跟踪文件分组来排除数据故障。例如，您可以对性能差的查询或逻辑读取操作数特别高的查询进行数据故障排除。
       通过将跟踪保存至表和使用 Transact-SQL 查询事件数据，可以找到其他信息。
       工具界面如图：
      
       2. 第二个工具是sql server 2005中新增加的工具——数据库引擎优化顾问
       
         

          数据库引擎优化顾问用于分析在一个或多个数据库中运行的工作负荷的性能效果。分析数据库的工作负荷效果后，数据库引擎优化顾问会提供在 Microsoft SQL Server 数据库中添加、删除或修改物理设计结构的建议。这些物理性能结构包括聚集索引、非聚集索引、索引视图和分区。实现这些结构之后，数据库引擎优化顾问使查询处理器能够用最短的时间执行工作负荷任务。

       数据库管理员可以使用数据库引擎优化顾问进行探索性分析。探索性分析需要综合使用手动优化和工具辅助优化。若要使用数据库引擎优化顾问进行探索性分析，需使用用户指定的配置功能。使用用户指定的配置功能可以指定优化现有和假设的物理设计结构（如索引、索引视图和分区）的配置。指定假设结构的好处就是，可以在不用首先实现这些结构的情况下评估它们对数据库的影响。

     尽管数据库引擎优化顾问图形用户界面 (GUI) 和 dta 命令行实用工具都支持探索性分析，但是 dta 的灵活性更大，因为它可以使用 XML 输入文件。XML 输入文件使用数据库引擎优化顾问 XML 架构，这是一种已发布的架构，可以通过访问数据库引擎优化顾问架构进行下载。

        探索性分析的两种模式
       可以使用数据库引擎优化顾问以下列两种模式之一执行探索性分析：
     1) 评估模式
         在评估模式中，数据库引擎优化顾问将相同工作负荷下当前配置的成本 (C) 和用户指定的配置的成本 (U) 进行比较。因为 C 由数据库中当前存在的物理设计结构组成，所以 C 始终是实际配置。相比较而言，U 是由实际和假设的物理设计结构组成的配置。如果数据库引擎优化顾问报告 U 的成本低于 C 的成本，则 U 的物理设计性能可能优于 C。

        例如，对于下列情况，评估模式是有用的：
         数据库管理员要确定向表中添加非聚集索引对性能的影响。
         数据库管理员刚刚完成了使用数据库引擎优化顾问优化数据库并接受了建议 (R)。查看 R 后，管理员可能会通过修改 R 对其进行微调。
        例如，
        数据库管理员想要添加两个非聚集索引并删除 R 中的一个非聚集索引。修改 R 后，该管理员将修改的 R 作为输入发送给数据库引擎优化顾问，并再次优化以衡量修改后的 R 对性能的影响。


       2) 优化模式
           在优化模式中，数据库管理员已经知道应该对数据库物理设计的一部分进行修改，但是希望数据库引擎优化顾问能够为其余配置提供最佳物理设计结构方面的建议。

          例如，优化模式在以下情况下非常有用：
         数据库管理员了解由于事实数据表过大，因此必须对其进行分区。管理员必须选择是按月还是按季度分区。可以使用其中任意一种方式对表进行分区，但管理员希望选择在给定的工作负荷下能提供最佳性能的分区方法。若要确定最佳分区方法，管理员可以使用数据库引擎优化顾问两次化工作负荷。
       首先，管理员通过用户指定的配置和按月假设分区的表来优化工作负荷。
       然后，使用按季度假设分区的表来优化工作负荷。
       使用两种假设配置优化工作负荷后，管理员可以通过比较提高的百分比来确定能提供最佳性能的分区方法。
      例如：
       Orders 表必须包含 ship_date 列的聚集索引。数据库管理员想要确定 Orders 表的一组最佳非聚集索引。通过指定用户指定的配置（该配置包含 Orders 表中 ship_date 列的聚集索引），数据库管理员可以部分修改物理数据库设计。然后可以在优化模式下使用数据库引擎优化顾问确定用户指定的配置对性能的影响。


     数据库引擎优化顾问未优化事件的最常见原因包括：
       1）工作负荷引用了用户未选择优化的表。
       2）工作负荷引用的表过小，例如包含的数据页少于 10 页的表。
      3）数据库引擎优化顾问无法在指定时间范围内优化工作负荷。


说明：工作负荷是数据库引擎优化顾问的分析对象，它由要优化的一个或多个数据库执行的一组T-SQL语句构成。