優(yōu)化 SQL Server運(yùn)行的配置

□劉 湛

( 運(yùn)城農(nóng)業(yè)職業(yè)技術(shù)學(xué)院，山西 運(yùn)城 044000)

SQL Server 2000提高了動(dòng)態(tài)自我管理的能力，在安裝完成SQL Server后，一般都不再需要做任何設(shè)置，SQL Server會(huì)自動(dòng)按照最優(yōu)化的配置運(yùn)行。然而，SQL Server的最優(yōu)化配置是針對(duì)SQL Server自身的，復(fù)雜多變的系統(tǒng)環(huán)境和實(shí)際業(yè)務(wù)需求，也導(dǎo)致SQL Server不一定能很好地運(yùn)行，所以，有必要對(duì)這些需要根據(jù)實(shí)際情況調(diào)整的配置拿出來(lái)單獨(dú)研究。

一、 內(nèi)存配置

有時(shí)我們可能會(huì)發(fā)現(xiàn)這樣的問(wèn)題，并發(fā)訪問(wèn)的用戶不多，但SQL Server 占用內(nèi)存的比率卻很高。檢查服務(wù)器卻一切正常，沒(méi)有感染病毒，最新的SQL Server Service Pack也裝上了，通過(guò)網(wǎng)絡(luò)監(jiān)控，也沒(méi)有發(fā)現(xiàn)惡意訪問(wèn)。SQL Server內(nèi)存占用隨用戶的增加而增加，但用戶斷開(kāi)連接后并沒(méi)有釋放內(nèi)存。這個(gè)問(wèn)題其實(shí)既不是軟件問(wèn)題，也不是系統(tǒng)故障，可以算是SQL Server內(nèi)在管理上的BUG，它是由于SQL Server數(shù)據(jù)庫(kù)引擎在Windows 2000或Windows NT上運(yùn)行時(shí)，默認(rèn)的內(nèi)存管理行為而導(dǎo)致的，如果不做特殊配置的話，其默認(rèn)內(nèi)存管理行為就是在不產(chǎn)生多余換頁(yè)I/O的情況下，盡可能多的獲取內(nèi)存。SQL Server 實(shí)例在啟動(dòng)時(shí)通常獲取 8 到 12 MB 的內(nèi)存來(lái)完成初始化過(guò)程。當(dāng)實(shí)例完成初始化后，就不會(huì)再獲取更多的內(nèi)存，直到用戶連接到該實(shí)例并開(kāi)始產(chǎn)生工作負(fù)荷，這時(shí)，該實(shí)例才會(huì)根據(jù)需要不停地獲取內(nèi)存以支持工作負(fù)荷。這樣，當(dāng)有更多的用戶連接并運(yùn)行查詢時(shí)，SQL Server 將自動(dòng)獲取內(nèi)存直到達(dá)到自身的內(nèi)存分配目標(biāo)而滿足需求所需，并且直到達(dá)到該目標(biāo)的下限才會(huì)釋放任何內(nèi)存。如果想解決這個(gè)問(wèn)題，就要進(jìn)行設(shè)置，影響SQL Server實(shí)例內(nèi)存分配行為的是SQL Server實(shí)例的min server memory和max server memory配置選項(xiàng)。min server memory配置項(xiàng)不影響SQL Server實(shí)例初始化過(guò)程的內(nèi)存處理，僅在釋放內(nèi)存時(shí)，保障SQL Server實(shí)例分配的內(nèi)存不會(huì)低于此值。如果這兩個(gè)設(shè)置項(xiàng)相同，則意味著SQL Server實(shí)例使用固定的內(nèi)存大小。

如果是SQL Server數(shù)據(jù)庫(kù)專用服務(wù)器，則可以考慮設(shè)置SQL Server內(nèi)存分配為“使用固定大小”，并設(shè)置一個(gè)較大的值，如果不是專用服務(wù)器，則應(yīng)該考慮設(shè)置“最大服務(wù)內(nèi)存”配置項(xiàng)，用來(lái)為其他應(yīng)用程序預(yù)留內(nèi)存，防止正在運(yùn)行的實(shí)例占用過(guò)多的內(nèi)存，而導(dǎo)致運(yùn)行其他程序受到影響。

二、數(shù)據(jù)存儲(chǔ)

SQL Server是數(shù)據(jù)存儲(chǔ)和管理的工具，數(shù)據(jù)是以文件形式存儲(chǔ)在磁盤分區(qū)中的，所以磁盤分區(qū)格式將影響數(shù)據(jù)的存儲(chǔ)，磁盤I/O速度會(huì)直接影響SQL Server的數(shù)據(jù)處理效率。

1.磁盤分區(qū)格式。SQL Server 2000支持的最大數(shù)據(jù)文件和日志文件為32TB，由于數(shù)據(jù)文件和日志文件皆為普通的文件，所以除了SQL Server自身的支持外，還要受磁盤分區(qū)格式的限制，很多用戶在使用SQL Server時(shí)，往往忽略了這一點(diǎn)。

2.使用文件組。SQL Server將數(shù)據(jù)庫(kù)映射到一組操作系統(tǒng)文件上，這樣可以避免數(shù)據(jù)庫(kù)的總大小受限于磁盤分區(qū)格式。一個(gè)SQL Server可以包含0～255個(gè)用戶定義文件組。日志文件不屬于任何文件組，一個(gè)文件組可以包含多個(gè)數(shù)據(jù)文件，而一個(gè)數(shù)據(jù)文件只能屬于一個(gè)文件組，未明確指定文件組的數(shù)據(jù)文件均屬于默認(rèn)文件組，默認(rèn)文件組是主文件組，也可以通過(guò)ALTER DATABASE語(yǔ)句將用戶定義文件組指定為默認(rèn)文件組。將SQL Server數(shù)據(jù)文件分成不同文件組，是為了管理和分配的需要。另一方面，將數(shù)據(jù)文件分成不同的文件組，使得用戶可以輕易地在新磁盤上添加數(shù)據(jù)文件，并把它添加到文件組中，使數(shù)據(jù)庫(kù)的增長(zhǎng)不受磁盤大小和磁盤分區(qū)格式的影響。以下的代碼是在SQL Server 2000上創(chuàng)建數(shù)據(jù)庫(kù)Market。這個(gè)數(shù)據(jù)庫(kù)Market包括1個(gè)主數(shù)據(jù)文件、1個(gè)事務(wù)日志文件和3個(gè)用戶定義的文件組。

USE master

GO -- 創(chuàng)建數(shù)據(jù)庫(kù)

CREATE DATABASE Market

ON PRIMARY --主文件組和主要數(shù)據(jù)文件

( NAME='Market_Primary',

FILENAME= 'd:Market_Prm.mdf'),

FILEGROUP Market_FG1 --用戶定義文件組1

( NAME = 'Market_FG1_Dat1',

FILENAME = 'd:Market_FG1_1.ndf'), --次要數(shù)據(jù)文件1

( NAME = 'Market_FG1_Dat2',

FILENAME = 'e:Market_FG1_2.ndf'), --次要數(shù)據(jù)文件2

FILEGROUP Market_FG2 --用戶定義文件組2

( NAME = 'Market_FG1_Dat',

FILENAME = 'f:Market_FG2.ndf') --次要數(shù)據(jù)文件

LOG ON --日志文件

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

GO -- 修改默認(rèn)數(shù)據(jù)文件組

ALTER DATABASE Market MODIFY FILEGROUP Market_FG1 DEFAULT

GO

USE Market

-- 在默認(rèn)文件組Market_FG1創(chuàng)建表,并且指定圖像數(shù)據(jù)保存在用戶定義文件組Market_FG2

CREATE TABLE lx

( column1 har(6) PRIMARY KEY,

column2 char(10),

column3 image )

TEXTIMAGE_ON Market_FG2

有時(shí)因?yàn)樘厥馓幚硇枰?比如磁盤有問(wèn)題需要檢修等)，需要把一個(gè)文件組中的某些數(shù)據(jù)文件刪除，可以用參考下面的代碼進(jìn)行處理。

USE Market

-- 將要?jiǎng)h除數(shù)據(jù)文件Market_FG1_Dat1上的數(shù)據(jù)轉(zhuǎn)移到其他數(shù)據(jù)文件中，并且清空數(shù)據(jù)文件

DBCC SHRINKFILE (Market_FG1_Dat1,EMPTYFILE)

--刪除數(shù)據(jù)文件Market_FG1_Dat1

ALTER DATABASE Market REMOVE FILE Market_FG1_Dat1

3.tempdb數(shù)據(jù)庫(kù)。關(guān)于文件組，另一個(gè)有必要涉及到的就是系統(tǒng)數(shù)據(jù)庫(kù)tempdb，該數(shù)據(jù)庫(kù)用于存放一些臨時(shí)存儲(chǔ)過(guò)程和臨時(shí)表。在SQL Server每次啟動(dòng)時(shí)，tempdb數(shù)據(jù)庫(kù)都根據(jù)系統(tǒng)數(shù)據(jù)庫(kù)model的結(jié)構(gòu)重新創(chuàng)建。

在使用數(shù)據(jù)庫(kù)的過(guò)程中，或多或少地都會(huì)用到臨時(shí)表和臨時(shí)存儲(chǔ)過(guò)程。因此，tempdb數(shù)據(jù)庫(kù)的性能對(duì)數(shù)據(jù)庫(kù)的影響是全局的，它的性能可能會(huì)決定整個(gè)SQL Server實(shí)例的處理效率。鑒于此，對(duì)tempdb數(shù)據(jù)庫(kù)的性能調(diào)優(yōu)也顯得非常重要。SQL Server系統(tǒng)每次啟動(dòng)時(shí)tempdb數(shù)據(jù)庫(kù)都被重置為其初始大小(8.0MB)。如果為tempdb數(shù)據(jù)庫(kù)定義的大小較小，在以后的工作中，當(dāng)tempdb數(shù)據(jù)庫(kù)空間不夠時(shí)，系統(tǒng)都將自動(dòng)擴(kuò)展tempdb數(shù)據(jù)庫(kù)的大小，這樣就可能會(huì)成為系統(tǒng)處理負(fù)荷的一部分，無(wú)形之中就增加了系統(tǒng)開(kāi)銷。

下面的代碼將tempdb數(shù)據(jù)庫(kù)的主數(shù)據(jù)文件大小設(shè)置為100MB。

ALTER DATABASE tempdb

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

除了避免啟動(dòng)SQL Server時(shí)tempdb數(shù)據(jù)庫(kù)自動(dòng)增加的工作負(fù)荷外，把tempdb數(shù)據(jù)庫(kù)的數(shù)據(jù)文件分散到多個(gè)高性能的磁盤上，并且避免和用戶數(shù)據(jù)庫(kù)的數(shù)據(jù)文件放在同一磁盤，也可以獲得更好的性能。下面的代碼可以將tempdb數(shù)據(jù)庫(kù)的主數(shù)據(jù)文件移動(dòng)到指定的磁盤分區(qū)上，并且為其添加一個(gè)數(shù)據(jù)文件。

--移動(dòng)數(shù)據(jù)文件

ALTER DATABASE tempdb

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

--添加次要數(shù)據(jù)文件

ALTER DATABASE tempdb

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

參考文獻(xiàn)：

[1]劉志成.數(shù)據(jù)庫(kù)應(yīng)用技術(shù)SQL Server 2000[M].北京:科學(xué)出版社,2003.

[2]鄒建.SQL Server 2000開(kāi)發(fā)與管理應(yīng)用實(shí)例[M]. 北京:人民郵電出版社,2005.

[3]余金山.SQL Server 2000/2005 數(shù)據(jù)庫(kù)開(kāi)發(fā)實(shí)例入門與提高[M].北京: 電子工業(yè)出版社, 2005.