sql server数据库优化的10多种方法

1、SQL Server数据库优化的10多种方法巧妙优化SQL Server数据库的几种方法，在实际操作中导致查询速度慢的原因有很多，其中最为常见有以下的几种：没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)。I/O吞吐量小，形成了瓶颈效应。没有创建计算列导致查询不优化SQL Server数据库。内存不足。网络速度慢。查询出的数据量过大(可以采用多次查询，其他的方法降低数据量)。锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)。sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。返回了不必要的行和列。查询语句不好，没有优化。可以通过如下方法来优化查询 :1

2、、把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL2000不在支持。数据量(尺寸)越大，提高I/O越重要。2、纵向、横向分割表，减少表的尺寸(sp_spaceuse)。3、升级硬件。4、根据查询条件,建立索引,优化索引、优化SQL Server数据库访问方式，限制结果集的数据量。注意填充因子要适当(最好是使用默认值0)。索引应该尽量小，使用字节数小的列建索引好(参照索引的创建),不要对有限的几个值的字段建单一索引如性别字段。5、提高网速。6、扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。配

3、置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server? 2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍(虚拟内存大小设置的一半)。7、增加服务器 CPU个数;但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择

4、的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作Update,Insert， Delete还不能并行处理。8、如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like a% 使用索引 like %a 不使用索引用 like %a% 查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。9、DB Server 和APPLi

5、cation Server 分离;OLTP和OLAP分离。10、分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。(参照SQL帮助文件分区视图)在实现分区视图之前，必须先水平分区表。在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个

6、服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。在T-sql的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理查询计划的过程是这样的：查询语句的词法、语法检查。将语句提交给DBMS的查询优化器。优化器做代数优化和存取路径的优化SQL Server数据库。由预编译模块生成查询规划。然后在合适的时间提交给系统处理执行。最后将执行结果

7、返回给用户其次，看一下SQL SERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。12、Commit和rollback的区别 Rollback:回滚所有的事物。 Commit:提交当前的事物. 没有必要在动态SQL里写事物，如果要写请写在外面如： begin tran exec(s) commit trans 或者将动态SQL 写成函数或者存储过程。SPAN13、在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其

8、他的联接访问表,后果严重。 HYPERLINK /gohands/article/details/2243300 SQL Server调优的五个步骤分类： HYPERLINK /gohands/article/category/73805 数据库2008-04-02 09:391175人阅读 HYPERLINK /gohands/article/details/2243300 l comments 评论(0) HYPERLINK javascript:void(0); o 收藏 收藏 HYPERLINK /gohands/article/details/2243300 l report o 举

9、报 举报 HYPERLINK /tag/sql%20server t _blank sql server HYPERLINK /tag/%e6%95%b0%e6%8d%ae%e5%ba%93 t _blank 数据库 HYPERLINK /tag/%e7%a3%81%e7%9b%98 t _blank 磁盘 HYPERLINK /tag/%e4%bc%98%e5%8c%96 t _blank 优化 HYPERLINK /tag/%e6%80%a7%e8%83%bd%e4%bc%98%e5%8c%96 t _blank 性能优化 HYPERLINK /tag/%e5%b7%a5%e4%bd%9c

10、 t _blank 工作步骤1 优化应用工作量优化应用性能的第一步是优化工作量。在该部分调优方法论中列出的优化步骤能够解决很多常见的性能和可延展性问题。这些优化可以帮助降低由于特殊的设计或低效的实施导致的性能瓶颈影响，并且可以保证系统资源能够充分和有效利用。例如，解决低效率的查询计划或低效率的缓存等问题将会更加有效率地发挥SQL服务器缓存机制，从而整体上降低I/O操作。 编译/重新编译- 数据库,CPU确定是否存在显著的CPU竞争，如果存在，请关注重新编译次数多的那些T-SQL语句，它们占用大量CPU资源。如果应用中SQL代码重新编译次数很多，可以考虑下面优化方法：评估有关的语句的作用，将数据

11、修改代码和数据定义命令相分离。解决过时的索引统计。使用变量或其他逻辑替代临时表。微软忠告：频繁地编译/重新编译会消耗很高的CPU和磁盘I/O资源，会增加整体的工作量竞争。 低效率的查询计划-数据库,CPU确定是否存在明显的CPU竞争，如果有，请确定无效率查询计划是如何占用过多的cpu资源。是否存在数据库模式，应用需求，用户使用的报表工具，或其它条件促使在生产环境下执行无效率的查询，使用Hash连接和排序操作的查询，结果会消耗很高的CPU和I/O。步骤2 减少读/写活动一旦你的应用代码被调优，接下来达到最佳性能就是减少应用运行时读写活动量或I/O，一个最常见的应用代码错误是编写低效率的数据查询操

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

13、缺失或或无效率的索引是如何导致磁盘I/O瓶颈的。DBA们必须评估应用的 SQL代码保证语句尽可能有效率地执行；这项任务通常必需创建索引来最有效地提取数据。如果应用的SQL代码发生变化，访问不同的表或从目的表选择更多的/不同的列，当前的索引可能会不起作用。需要分析说明SQL 代码无效率使用存在的索引或语句正在用表扫描搜集数据的地方。 磁盘I/O-数据库文件的增长-DB I/O确定是否存在明显的磁盘I/O竞争，如果存在，需要关注频繁使用扩展段的数据库。DBA们应关注在一定的时间窗口内频繁使用扩展段的数据库。当SQL Server增大数据库文件时，文件倾向于破碎，操作将非常消耗CPU和I/O。 磁盘

14、I/O-数据库文件配置-DB I/O确定是否存在明显的磁盘I/O竞争，如果存在，请关注配置糟糕的数据库文件是如何导致数据库内锁竞争的增加,进而形成资源瓶颈，减少应用之间的竞争。DBA应考察可能导致闩竞争的一些数据库文件的配置问题，包括：数据文件和日志文件配置在同一磁盘设备上。数据库文件数量少于可用的CPU数量，特别是TempDB数据库。数据库文件数量少于可用的磁盘I/O设备数量。步骤3 减少竞争现在，已经优化应用的I/O访问，下一步要完成的性能优化就是确保高度的并发不会导致对象竞争情况的增加。即使数据访问被优化了，使用锁和闩锁的SQL Server引擎，会同步和保护数据访问，在高负载下也会出现

15、阻塞问题。智能的事务控制逻辑，可保证事务不会执行过长时间，或者只在适当得数据上加锁，因而其是达到高并发的关键。使用适当的事务隔离层可保证减少不必要的读操作阻塞，评估锁提示的需要可保证锁的不必要的保持，这些都可以极大提高应用的性能。为了减少或消除闩锁问题，保证应用不要将DDL和DML的操作混在一起。一旦解决这些问题，你就应该分析你的应用时如何访问数据的，以便确定是否可以通过数据分区的方式提高应用性能。 阻塞锁-对象竞争-数据库锁确定是否存在明显的锁竞争，如果存在，看看经常出现锁竞争的数据库表，帮助识别故障点和缺失的索引，应用倾向于访问数据库中的某些特定的表多一些。当隔离层设置不正确时，事务会执行

16、很长时间，由于涉及到的索引导致不能访问数据，处理发生冲突或发生阻塞等。许多应用管理员没有意识到数据库遭受阻塞的程度；我们需要分析和发现由频繁的短期锁大量累积而导致的明显竞争。 阻塞锁-锁类型-数据库锁确定是否存在明显的锁竞争，如果存在，按照数据库分析锁的类型。某些应用以不同的方式访问不同的特定数据库。其原因可能是不同的开发人员开发的代码不同，或需求不断变化等等。按照数据库显示不同的SQL Server锁类型的分析结果，显示锁的行为与整体活动时间的比较分析的重要程度，这些将有助于应用程序开发人员正确地修改他们的应用代码。 内存缓冲区闩锁-数据库闩锁确定是否存在明显的内存缓冲区闩锁竞争，如果存在，

17、很多的内存缓冲区闩锁等待是I/O瓶颈和热页的迹象。因为内存缓冲区闩锁与I/O竞争没有直接关系，因而这对SQL Server的可用内存数量是很关键的。 内部高速缓存闩锁竞争- 数据库闩锁确定是否存在明显的内部高速缓存闩锁竞争，如果存在，识别出哪里存在大部分竞争。内部高速缓存闩锁可用在多种不同的情况；可能最常见的例子是内部高速缓存的竞争（不是缓冲池页），尤其当使用堆,text或两者同时使用的时候。如果解决LOG和PAGELATCH_UP的竞争后没有作用，通常将数据分区可以很好缓解内部高速缓存闩锁的竞争。步骤4 解决资源瓶颈到目前为止，你已经确保你的查询正确地使用了底层的系统资源，并且尽可能有效地访

18、问数据。现在你应该确定是否有资源瓶颈使你的应用慢下来。在应用上你可以做许多调优工作，在某些情况下外部因素仍是性能优化的最后障碍。这部分调优方法描述了特定资源的瓶颈。例如，SQL Server有足够的内存来支持良好的性能吗？有窃取SQL Server内存的外部应用程序吗？你的硬盘性能能足够支持你的工作量吗？你的应用能有效率地记录日志吗，记录日志的时间是否需要提高？最后，并行可以帮助你的查询执行更快，还是SQL Server花费更多的时间协调并发线程，从而使得并发带来更多的阻碍？应该考虑到应用性能的这些方面，可以保证充分利用底层系统资源，并且可以帮助确定哪些硬件需要扩容。 内存压力-系统内存确定是否存在明显的内存压力，如果存在，请分析： 外部的内存压力可以影响SQL Server的性能。许多DBA和DBA的经理们不明白病毒检测软件的配置不当和在一个exchange server上安装SQL Server所带来的影响。 SQL Server没有足够的内存达到理想的功能。如果SQL Server不能分配给缓存足够的内存，页的平均寿命将减少，系统范围内存分页交换就会增加。 日志等待确定是否有明显的日志等待，如果有，分析有多少因素减慢SQL Server记录日志。步骤5 基线偏离分析毫无疑