優化 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.