SQL Server调优的五个步骤

ehxz

步骤1 优化应用工作量

      优化应用性能的第一步是优化工作量。在该部分调优方法论中列出的优化步骤能够解决很多常见的性能和可延展性问题。这些优化可以帮助降低由于特殊的设计或低效的实施导致的性能瓶颈影响，并且可以保证系统资源能够充分和有效利用。例如，解决低效率的查询计划或低效率的缓存等问题将会更加有效率地发挥SQL服务器缓存机制，从而整体上降低I/O操作。


      ■ 编译/重新编译- 数据库,CPU

      确定是否存在显著的CPU竞争，如果存在，请关注重新编译次数多的那些T-SQL语句，它们占用大量CPU资源。如果应用中SQL代码重新编译次数很多，可以考虑下面优化方法：
      ●评估有关的语句的作用，将数据修改代码和数据定义命令相分离。

      ●解决过时的索引统计。

      ●使用变量或其他逻辑替代临时表。微软忠告：频繁地编译/重新编译会消耗很高的CPU和磁盘I/O资源，会增加整体的工作量竞争。



      ■ 低效率的查询计划-数据库,CPU

      确定是否存在明显的CPU竞争，如果有，请确定无效率查询计划是如何占用过多的cpu资源。是否存在数据库模式，应用需求，用户使用的报表工具，或其它条件促使在生产环境下执行无效率的查询，使用Hash连接和排序操作的查询，结果会消耗很高的CPU和I/O。





      步骤2 减少读/写活动

      一旦你的应用代码被调优，接下来达到最佳性能就是减少应用运行时读写活动量或I/O，一个最常见的应用代码错误是编写低效率的数据查询操作；查询返回很多的数据-太多的列或行-SQLServer会负载很大。无论是应用设计允许用户创建自己的（通常无效率的），不限定每页结果的查询，还是后端代码使用嵌套查询，这些查询会返回很多的数据（包括用视图或表值函数写的查询），你的应用做为一个整体可能会访问更多的远超过需要的数据。在一些情况下，检查完你的应用代码后，你可能会认识到你的代码将会返回底层表中的所有数据，来满足查询需要!分析存在的索引和它们维护模式，确定添加索引是否合适，分析数据库文件的增长情况会帮你极大减少应用的读写活动量，可以释放宝贵的磁盘资源。


      ■ 无效率的或缺失的索引-DB I/O

      确定是否存在明显的磁盘I/O竞争，如果存在，需要分析缺失或或无效率的索引是如何导致磁盘I/O瓶颈的。DBA们必须评估应用的 SQL代码保证语句尽可能有效率地执行；这项任务通常必需创建索引来最有效地提取数据。如果应用的SQL代码发生变化，访问不同的表或从目的表选择更多的/不同的列，当前的索引可能会不起作用。需要分析说明SQL 代码无效率使用存在的索引或语句正在用表扫描搜集数据的地方。

      ■ 磁盘I/O-数据库文件的增长-DB I/O

      确定是否存在明显的磁盘I/O竞争，如果存在，需要关注频繁使用扩展段的数据库。DBA们应关注在一定的时间窗口内频繁使用扩展段的数据库。当SQL Server增大数据库文件时，文件倾向于破碎，操作将非常消耗CPU和I/O。


      ■ 磁盘I/O-数据库文件配置-DB I/O

      确定是否存在明显的磁盘I/O竞争，如果存在，请关注配置糟糕的数据库文件是如何导致数据库内锁竞争的增加,进而形成资源瓶颈，减少应用之间的竞争。DBA应考察可能导致闩竞争的一些数据库文件的配置问题，包括：
      ●数据文件和日志文件配置在同一磁盘设备上。

      ●数据库文件数量少于可用的CPU数量，特别是TempDB数据库。

      ●数据库文件数量少于可用的磁盘I/O设备数量。




      步骤3 减少竞争

      现在，已经优化应用的I/O访问，下一步要完成的性能优化就是确保高度的并发不会导致对象竞争情况的增加。即使数据访问被优化了，使用锁和闩锁的SQL Server引擎，会同步和保护数据访问，在高负载下也会出现阻塞问题。智能的事务控制逻辑，可保证事务不会执行过长时间，或者只在适当得数据上加锁，因而其是达到高并发的关键。使用适当的事务隔离层可保证减少不必要的读操作阻塞，评估锁提示的需要可保证锁的不必要的保持，这些都可以极大提高应用的性能。为了减少或消除闩锁问题，保证应用不要将DDL和DML的操作混在一起。一旦解决这些问题，你就应该分析你的应用时如何访问数据的，以便确定是否可以通过数据分区的方式提高应用性能。


      ■ 阻塞锁-对象竞争-数据库锁

      确定是否存在明显的锁竞争，如果存在，看看经常出现锁竞争的数据库表，帮助识别故障点和缺失的索引，应用倾向于访问数据库中的某些特定的表多一些。当隔离层设置不正确时，事务会执行很长时间，由于涉及到的索引导致不能访问数据，处理发生冲突或发生阻塞等。许多应用管理员没有意识到数据库遭受阻塞的程度；我们需要分析和发现由频繁的短期锁大量累积而导致的明显竞争。


      ■ 阻塞锁-锁类型-数据库锁

      确定是否存在明显的锁竞争，如果存在，按照数据库分析锁的类型。某些应用以不同的方式访问不同的特定数据库。其原因可能是不同的开发人员开发的代码不同，或需求不断变化等等。按照数据库显示不同的SQL Server锁类型的分析结果，显示锁的行为与整体活动时间的比较分析的重要程度，这些将有助于应用程序开发人员正确地修改他们的应用代码。


      ■ 内存缓冲区闩锁-数据库闩锁

      确定是否存在明显的内存缓冲区闩锁竞争，如果存在，很多的内存缓冲区闩锁等待是I/O瓶颈和热页的迹象。因为内存缓冲区闩锁与I/O竞争没有直接关系，因而这对SQL Server的可用内存数量是很关键的。


      ■ 内部高速缓存闩锁竞争- 数据库闩锁

      确定是否存在明显的内部高速缓存闩锁竞争，如果存在，识别出哪里存在大部分竞争。内部高速缓存闩锁可用在多种不同的情况；可能最常见的例子是内部高速缓存的竞争（不是缓冲池页），尤其当使用堆,text或两者同时使用的时候。如果解决LOG和PAGELATCH_UP的竞争后没有作用，通常将数据分区可以很好缓解内部高速缓存闩锁的竞争。






      步骤4 解决资源瓶颈

      到目前为止，你已经确保你的查询正确地使用了底层的系统资源，并且尽可能有效地访问数据。现在你应该确定是否有资源瓶颈使你的应用慢下来。在应用上你可以做许多调优工作，在某些情况下外部因素仍是性能优化的最后障碍。这部分调优方法描述了特定资源的瓶颈。例如，SQL Server有足够的内存来支持良好的性能吗？有窃取SQL Server内存的外部应用程序吗？你的硬盘性能能足够支持你的工作量吗？你的应用能有效率地记录日志吗，记录日志的时间是否需要提高？最后，并行可以帮助你的查询执行更快，还是SQL Server花费更多的时间协调并发线程，从而使得并发带来更多的阻碍？应该考虑到应用性能的这些方面，可以保证充分利用底层系统资源，并且可以帮助确定哪些硬件需要扩容。


      ■ 内存压力-系统内存

      确定是否存在明显的内存压力，如果存在，请分析：

      ● 外部的内存压力可以影响SQL Server的性能。许多DBA和DBA的经理们不明白病毒检测软件的配置不当和在一个exchange server上安装SQL Server所带来的影响。

      ● SQL Server没有足够的内存达到理想的功能。如果SQL Server不能分配给缓存足够的内存，页的平均寿命将减少，系统范围内存分页交换就会增加。

      ■ 日志等待

      确定是否有明显的日志等待，如果有，分析有多少因素减慢SQL Server记录日志。



      步骤5 基线偏离分析

      毫无疑问，对应用性能的最好的比较衡量是它自己过去的性能。需要分析应用性能是在哪里与过去的所观测的行为发生了偏离。这意味着你能快速，容易地看出你的应用的伸缩状况，以及确定应用的构建和系统变化等因素对企业性能的影响。我们可以分析多种主要资源的使用的偏离情况。我们需要分析出哪种主要的资源分类会受到影响，根据分析所提供的逻辑和分析度量值与其它支持信息，可以更快地理解如何获得应用的最佳性能。


      ■ CPU使用率偏离，CPU等待时间偏离，I/O等待时间偏离，闩锁等待时间偏离, 锁等待时间偏离，工作量偏离

      ● 确定这些指标是否与过去典型的使用情况发生了重要变化。

      ● 这些变化应该再核实确认，确保它们不会成为有问题的变化。




Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=2243300