SQL Server数据库自身优化

　　1.1：增加次数据文件

　　从SQL SERVER 2005开始，数据库不默认生成NDF数据文件，一般情况下有一个主数据文件(MDF)就够了，但是有些大型的数据库，由于信息很多，而且查询频繁，所以为了提高查询速度，可以把一些表或者一些表中的部分记录分开存储在不同的数据文件里

　　由于CPU和内存的速度远大于硬盘的读写速度，所以可以把不同的数据文件放在不同的物理硬盘里，这样执行查询的时候，就可以让多个硬盘同时进行查询，以充分利用CPU和内存的性能，提高查询速度。 在这里详细介绍一下其写入的原理，数据文件(MDF、NDF)和日志文件(LDF)的写入方式是不一样的：

　　数据文件：SQL Server按照同一个文件组里面的所有文件现有空闲空间的大小，按这个比例把新的数据分布到所有有空间的数据文件里，如果有三个数据文件A.MDF，B.NDF，C.NDF，空闲大小分别为200mb，100mb，和50mb，那么写入一个70mb的东西，他就会向ABC三个文件中一次写入40、20、10的数据，如果某个日志文件已满，就不会向其写入

　　日志文件：日志文件是按照顺序写入的，一个写满，才会写入另外一个

　　由上可见，如果能增加其数据文件NDF,有利于大数据量的查询速度，但是增加日志文件却没什么用处。

　　1.2：设置文件自动增长(大数据量，小数据量无需设置)

　　在SQL Server 2005中，默认MDF文件初始大小为5MB，自增为1MB，不限增长，LDF初始为1MB，增长为10%，限制文件增长到一定的数目，一般设计中，使用SQL自带的设计即可，但是大型数据库设计中，最好亲自去设计其增长和初始大小，如果初始值太小，那么很快数据库就会写满，如果写满，在进行插入会是什么情况呢?当数据文件写满，进行某些操作时，SQL Server会让操作等待，直到文件自动增长结束了，原先的那个操作才能继续进行。如果自增长用了很长时间，原先的操作会等不及就超时取消了(一般默认的阈值是15秒)，不但这个操作会回滚，文件自动增长也会被取消。也就是说，这一次文件没有得到任何增大，增长的时间根据自动增长的大小确定的，如果太小，可能一次操作需要连续几次增长才能满足，如果太大，就需要等待很长时间，所以设置自动增长要注意一下几点：

　　1)要设置成按固定大小增长，而不能按比例。这样就能避免一次增长太多或者太少所带来的不必要的麻烦。建议对比较小的数据库，设置一次增长50 MB到100 MB。对大的数据库，设置一次增长100 MB到200 MB。

　　2)要定期监测各个数据文件的使用情况，尽量保证每个文件剩余的空间一样大，或者是期望的比例。

　　3)设置文件最大值，以免SQL Server文件自增长用尽磁盘空间，影响操作系统。

　　4)发生自增长后，要及时检查新的数据文件空间分配情况。避免SQL Server总是往个别文件写数据。

　　因此，对于一个比较繁忙的数据库，推荐的设置是开启数据库自动增长选项，以防数据库空间用尽导致应用程序失败，但是要严格避免自动增长的发生。同时，尽量不要使用自动收缩功能。

　　1.3 数据和日志文件分开存放在不同磁盘上

　　数据文件和日志文件的操作会产生大量的I/O。在可能的条件下，日志文件应该存放在一个与数据和索引所在的数据文件不同的硬盘上以分散I/O，同时还有利于数据库的灾难恢复。

　　优化②：表分区，索引分区 (优化①粗略的进行了表分区，优化②为精确数据分区)

　　为什么要表分区?

　　当一个表的数据量太大的时候，我们最想做的一件事是什么?将这个表一分为二或者更多分，但是表还是这个表，只是将其内容存储分开，这样读取就快了N倍了

　　原理：表数据是无法放在文件中的，但是文件组可以放在文件中，表可以放在文件组中，这样就间接实现了表数据存放在不同的文件中。能分区存储的还有：表、索引和大型对象数据 。

　　SQL SERVER 2005中，引入了表分区的概念， 当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区,当一个表里的数据很多时，可以将其分拆到多个的表里,因为要扫描的数据变得更少 ,查询可以更快地运行,这样操作大大提高了性能,表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表

　　2.1什么时候使用分区表：

　　1、表的大小超过2GB。

　　2、表中包含历史数据，新的数据被增加到新的分区中。

　　2.2表分区的优缺点

　　表分区有以下优点：

　　1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

　　2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用;

　　3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可;

　　4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

　　缺点：

　　分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能.

　　2.3表分区的操作三步走

　　2.31 创建分区函数

　　CREATE PARTITION FUNCTION xx1(int)

　　AS RANGE LEFT FOR VALUES (10000, 20000);

　　注释：创建分区函数：myRangePF2，以INT类型分区，分三个区间，10000以内在A 区，1W-2W在B区，2W以上在C区.

　　2.3.2创建分区架构

　　CREATE PARTITION SCHEME myRangePS2

　　AS PARTITION xx1

　　TO (a, b, c);

　　注释：在分区函数XX1上创建分区架构：myRangePS2，分别为A,B,C三个区间

　　A,B,C分别为三个文件组的名称，而且必须三个NDF隶属于这三个组，文件所属文件组一旦创建就不能修改

　　2.3.3 对表进行分区

　　常用数据规范–数据空间类型修改为：分区方案，然后选择分区方案名称和分区列列表，结果如图所示：

　　也可以用sql语句生成

　　CREATE TABLE [dbo].[AvCache](

原文转自：http://www.ltesting.net