SQLServer优化建议---数据库设计

一、SQLServer实例尽量不要与其他应用在一台服务器中。

 

二、如果条件允许可以采用数据库集群、数据库的镜像、发布订阅等方式进行负载均衡。

 

三、对于32位Windows 2003 Server服务器，需要开启PAE（物理地址扩展），这样操作系统才能提供超过4GB的物理内存访问，达到64GB。然后开启SQLServer的AWE（地址窗口化扩展插件--以后将取消该功能），使SQLServer能够访问更多的内存。

1、开启PAE的过程：

在显示受保护的操作系统文件后，找到C:/Boot.ini文件并编辑。将“/PAE”加到操作系统的启动路径后，例如：multi(0)disk(0)rdisk(0)partition(1)/WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE

然后，重启服务器，PAE生效。

2、开启AWE的过程：

在SQLServer数据库的查询工具中输入：

sp_configure 'show advanced options', 1

RECONFIGURE

GO

 

sp_configure 'awe enabled', 1

RECONFIGURE

GO

 

四、留给操作系统1~2GB内存，其余全部留给SQLServer。例如24GB的服务器，可以将SQLServer的最小和最大内存设置为20GB和22GB。

 

五、tempdb的设置

tempdb是临时数据库，主要存放临时数据对象或者内部对象等内容，对系统性能影响较大。

1、将 tempdb 的恢复模式设置为 SIMPLE。

2、初始大小设置为尽量大，以满足日常使用；同时允许自动增长，并且不限制文件增长，自动增长大小设置为较大值。这可以避免 tempdb 因文件增长得过于频繁而影响性能。这种设置方式对于OLAP系统尤为重要。

3、数据文件数目应该设置与服务器的CPU核数相同，并且每个数据文件的大小应该相同，以提高处理效率。

4、将 tempdb 数据库放在高效磁盘阵列，建议存放在raid0阵列中。

5、不要与用户数据库存放在一个磁盘上。

 

六、用户数据库设计

1、建立几个文件组，用户数据表分类存放到新建的各文件组，默认文件组不要设置成Primary文件组。每个文件组的数据文件均存放到各数据磁盘中，例如有3个数据磁盘（H、I、J），则每个文件组应包含3个文件，文件1存放到H盘，文件2存放到I盘，文件3存放到J盘。一般建议数据文件存放在raid5阵列中。但是文件组不要太多，否则sp_attachdb无法正常运行，而只能用 CREATE DATABASE … FOR ATTACH。

2、数据库日志文件建议存放到Raid1阵列上，数据文件建议存放在raid5阵列。当然了，如果磁盘富裕，可以做成raid0+1，速度更快。

3、数据文件和日志文件的初始大小和增长也最好设置较大。

4、对于OLAP系统建议将恢复模式设置为SIMPLE。

 

七、数据表设计

1、数据表设计尽量满足第三范式要求。但是必要情况下，可以增加重复属性。例如帖子数据表可以除了用户ID外，增加用户名字段冗余，以避免帖子数据表和用户数据表频繁表关联查询造成的性能损耗；常用的计算字段（如几个费用的总和）可以考虑存储到数据表中。

2、对于数据表字段类型在满足业务需求的情况下，尽量使用小数据类型。例如，尽量用数字而不是字符类型，能用smallint就用smallint类型存放，而不用int。

3、对于text和image类型（SQLServer2008以后改为varchar(max)、varbinary(max)类型）尽量少用，建议使用sp_tableoption，将large value types out of row设置为on，允许该类型字段存储在记录行实际存储空间外。

4、对于大容量数据表，可以考虑进行表分区。

5、如果服务器存在IO性能瓶颈，而非CPU瓶颈，可以考虑数据表压缩，在同样的存储（页/区等）下保存更多记录。每次读取一页可以获取更多记录，提高效率。但是压缩、解压缩需要消耗CPU。

CREATE TABLE T1

(c1 int, c2 nvarchar(50) )

WITH (DATA_COMPRESSION = ROW);

GO

ALTER TABLE T1

REBUILD WITH (DATA_COMPRESSION = PAGE);

GO

 

八、索引设计

1、对于OLAP系统，建立较多的索引，而对于OLTP系统，索引尽可能精简。因为OLTP系统，数据频繁增、删、改，相应的索引也要进行频繁的维护，会影响系统效率。

2、选择正确的填充因子选项。当创建或重新生成索引时，填充因子的值可确定每个叶级页上要填充数据的空间百分比，以便在每一页上保留一些剩余空间作为以后扩展索引的可用空间。例如，指定填充因子的值为 80 表示每个叶级页上将有 20% 的空间保留为空，以便随着向基础表中添加数据而为扩展索引提供空间。在索引行之间保留可用空间，而不是在索引的末尾保留。对于OLAP系统，填充因子可以减小，对于OLTP系统，可适当提高填充因子，以减少页拆分的次数，提高系统效率。但是当填充因子过大时，索引占用空间相应增多，也会降低读取性能。

3、如果一个索引的值对应的行数小于表中所有行的5%（有人说1%），则应该建立索引，否则可能在查询时不会使用该索引。例如在几百万条记录的数据表中，在只有10个值的字段上建立索引，这个索引占用空间、影响增删改下效率不说，还根本就用不到。

4、当业务操作对应的查询相对较固定时，可以使用包含列索引。例如我们发现当按照用户查询帖子时，一般会列出帖子ID、用户、标题、发帖时间等内容，则我们建立如下索引：

CREATE INDEX IX_User ON BBS(UserID) INCLUDE (Title，InputTime);

实现性能提升，因为查询优化器可以在索引中找到所有列值，而不必访问表或聚集索引数据，从而减少磁盘 I/O 操作。

5、大容量数据插入时，应该先删除索引，再插入数据，再重建索引，这样速度比直接插入要快。

6、对数据执行插入、更新或删除操作，都会自动维护索引，随着时间的推移，这些修改可能会导致碎片，碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。可以使用sys.dm_db_index_physical_stats函数，检测特定索引、表或索引视图的所有索引、数据库中所有索引或所有数据库中所有索引中的碎片。查看该函数返回列avg_fragmentation_in_percent（逻辑碎片），当>5%且<=30%，可以使用ALTER INDEX REORGANIZE语句，当>30%时，可以使用ALTER INDEX REBUILD WITH (ONLINE = ON)。通过上述方式重新组织索引，减少索引碎片。

7、如果索引的值是唯一，尽量指定为唯一索引。

8、经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引字段。

9、把经常一起出现的字段组合在一起，组成组合索引，把最常用的字段和重复率低的字段放在前面。在查询时，必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。