优化 SQL Server运行的配置

□刘 湛

( 运城农业职业技术学院，山西 运城 044000)

SQL Server 2000提高了动态自我管理的能力，在安装完成SQL Server后，一般都不再需要做任何设置，SQL Server会自动按照最优化的配置运行。然而，SQL Server的最优化配置是针对SQL Server自身的，复杂多变的系统环境和实际业务需求，也导致SQL Server不一定能很好地运行，所以，有必要对这些需要根据实际情况调整的配置拿出来单独研究。

一、 内存配置

有时我们可能会发现这样的问题，并发访问的用户不多，但SQL Server 占用内存的比率却很高。检查服务器却一切正常，没有感染病毒，最新的SQL Server Service Pack也装上了，通过网络监控，也没有发现恶意访问。SQL Server内存占用随用户的增加而增加，但用户断开连接后并没有释放内存。这个问题其实既不是软件问题，也不是系统故障，可以算是SQL Server内在管理上的BUG，它是由于SQL Server数据库引擎在Windows 2000或Windows NT上运行时，默认的内存管理行为而导致的，如果不做特殊配置的话，其默认内存管理行为就是在不产生多余换页I/O的情况下，尽可能多的获取内存。SQL Server 实例在启动时通常获取 8 到 12 MB 的内存来完成初始化过程。当实例完成初始化后，就不会再获取更多的内存，直到用户连接到该实例并开始产生工作负荷，这时，该实例才会根据需要不停地获取内存以支持工作负荷。这样，当有更多的用户连接并运行查询时，SQL Server 将自动获取内存直到达到自身的内存分配目标而满足需求所需，并且直到达到该目标的下限才会释放任何内存。如果想解决这个问题，就要进行设置，影响SQL Server实例内存分配行为的是SQL Server实例的min server memory和max server memory配置选项。min server memory配置项不影响SQL Server实例初始化过程的内存处理，仅在释放内存时，保障SQL Server实例分配的内存不会低于此值。如果这两个设置项相同，则意味着SQL Server实例使用固定的内存大小。

如果是SQL Server数据库专用服务器，则可以考虑设置SQL Server内存分配为“使用固定大小”，并设置一个较大的值，如果不是专用服务器，则应该考虑设置“最大服务内存”配置项，用来为其他应用程序预留内存，防止正在运行的实例占用过多的内存，而导致运行其他程序受到影响。

二、数据存储

SQL Server是数据存储和管理的工具，数据是以文件形式存储在磁盘分区中的，所以磁盘分区格式将影响数据的存储，磁盘I/O速度会直接影响SQL Server的数据处理效率。

1.磁盘分区格式。SQL Server 2000支持的最大数据文件和日志文件为32TB，由于数据文件和日志文件皆为普通的文件，所以除了SQL Server自身的支持外，还要受磁盘分区格式的限制，很多用户在使用SQL Server时，往往忽略了这一点。

2.使用文件组。SQL Server将数据库映射到一组操作系统文件上，这样可以避免数据库的总大小受限于磁盘分区格式。一个SQL Server可以包含0～255个用户定义文件组。日志文件不属于任何文件组，一个文件组可以包含多个数据文件，而一个数据文件只能属于一个文件组，未明确指定文件组的数据文件均属于默认文件组，默认文件组是主文件组，也可以通过ALTER DATABASE语句将用户定义文件组指定为默认文件组。将SQL Server数据文件分成不同文件组，是为了管理和分配的需要。另一方面，将数据文件分成不同的文件组，使得用户可以轻易地在新磁盘上添加数据文件，并把它添加到文件组中，使数据库的增长不受磁盘大小和磁盘分区格式的影响。以下的代码是在SQL Server 2000上创建数据库Market。这个数据库Market包括1个主数据文件、1个事务日志文件和3个用户定义的文件组。

USE master

GO -- 创建数据库

CREATE DATABASE Market

ON PRIMARY --主文件组和主要数据文件

( NAME='Market_Primary',

FILENAME= 'd:Market_Prm.mdf'),

FILEGROUP Market_FG1 --用户定义文件组1

( NAME = 'Market_FG1_Dat1',

FILENAME = 'd:Market_FG1_1.ndf'), --次要数据文件1

( NAME = 'Market_FG1_Dat2',

FILENAME = 'e:Market_FG1_2.ndf'), --次要数据文件2

FILEGROUP Market_FG2 --用户定义文件组2

( NAME = 'Market_FG1_Dat',

FILENAME = 'f:Market_FG2.ndf') --次要数据文件

LOG ON --日志文件

( NAME='Market_log', FILENAME ='e:Market.ldf')

GO -- 修改默认数据文件组

ALTER DATABASE Market MODIFY FILEGROUP Market_FG1 DEFAULT

GO

USE Market

-- 在默认文件组Market_FG1创建表,并且指定图像数据保存在用户定义文件组Market_FG2

CREATE TABLE lx

( column1 har(6) PRIMARY KEY,

column2 char(10),

column3 image )

TEXTIMAGE_ON Market_FG2

有时因为特殊处理需要(比如磁盘有问题需要检修等)，需要把一个文件组中的某些数据文件删除，可以用参考下面的代码进行处理。

USE Market

-- 将要删除数据文件Market_FG1_Dat1上的数据转移到其他数据文件中，并且清空数据文件

DBCC SHRINKFILE (Market_FG1_Dat1,EMPTYFILE)

--删除数据文件Market_FG1_Dat1

ALTER DATABASE Market REMOVE FILE Market_FG1_Dat1

3.tempdb数据库。关于文件组，另一个有必要涉及到的就是系统数据库tempdb，该数据库用于存放一些临时存储过程和临时表。在SQL Server每次启动时，tempdb数据库都根据系统数据库model的结构重新创建。

在使用数据库的过程中，或多或少地都会用到临时表和临时存储过程。因此，tempdb数据库的性能对数据库的影响是全局的，它的性能可能会决定整个SQL Server实例的处理效率。鉴于此，对tempdb数据库的性能调优也显得非常重要。SQL Server系统每次启动时tempdb数据库都被重置为其初始大小(8.0MB)。如果为tempdb数据库定义的大小较小，在以后的工作中，当tempdb数据库空间不够时，系统都将自动扩展tempdb数据库的大小，这样就可能会成为系统处理负荷的一部分，无形之中就增加了系统开销。

下面的代码将tempdb数据库的主数据文件大小设置为100MB。

ALTER DATABASE tempdb

MODIFY FILE(name=tempdev, size=100MB)

除了避免启动SQL Server时tempdb数据库自动增加的工作负荷外，把tempdb数据库的数据文件分散到多个高性能的磁盘上，并且避免和用户数据库的数据文件放在同一磁盘，也可以获得更好的性能。下面的代码可以将tempdb数据库的主数据文件移动到指定的磁盘分区上，并且为其添加一个数据文件。

--移动数据文件

ALTER DATABASE tempdb

MODIFY FILE(name=tempdev, filename=’d: empdb.mdf’)

--添加次要数据文件

ALTER DATABASE tempdb

ADD FILE(name=‘tempdb_1’, filename=’d: empdb_data_1.ndf’)

参考文献：

[1]刘志成.数据库应用技术SQL Server 2000[M].北京:科学出版社,2003.

[2]邹建.SQL Server 2000开发与管理应用实例[M]. 北京:人民邮电出版社,2005.

[3]余金山.SQL Server 2000/2005 数据库开发实例入门与提高[M].北京: 电子工业出版社, 2005.