數(shù)據(jù)文件的碎片
影響磁盤讀取性能的兩個主要因素：錄道時間和輪詢延遲。
我們在查詢數(shù)據(jù)時，有兩種磁盤的讀取方式：順序讀和隨機讀。隨機讀發(fā)生在對表或索引的掃描時，順序讀發(fā)生在使用索引查找數(shù)據(jù)時。當數(shù)據(jù)文件有大量碎片時，隨機讀不會受到太大的影響，因為SQLSERVER會根據(jù)表所占用到的數(shù)據(jù)頁面，不管記錄的邏輯順序隨機的讀取出來，所謂的預(yù)讀正是這種方式。而順序讀時，因為要按記錄的邏輯順序讀取相應(yīng)的記錄，如果邏輯上相鄰的數(shù)據(jù)頁在物理分布上不連續(xù)，則會因為磁頭的來回移動使性能大打折扣。這也就是為什么有時我們看到表掃描比索引查找效率更高的原因。
我們在創(chuàng)建數(shù)據(jù)庫時，會為數(shù)據(jù)文件和日志文件分別指定一個初始大小和增量大小。如果這些文件都在獨自的邏輯分區(qū)中，那么不會有磁盤碎片的產(chǎn)生。但是，如果每個文件所在的分區(qū)中還有其它的數(shù)據(jù)庫文件。則因為這些文件的自增長就會產(chǎn)生磁盤碎片了，如下圖所示：
為了防止這些碎片的產(chǎn)生，我們應(yīng)該每次把文件自增長的大小設(shè)置的更大些，以防止產(chǎn)生這么多小的碎片。但是，如果每次文件增長的過大，特別是在系統(tǒng)繁忙的時候，勢必會影響數(shù)據(jù)庫的性能。為了能快速的完全文件增長的工作，SQLSERVER借助WINDOWS的即時文件初始化功能來快速的完成此項任務(wù)。若要使用即時文件初始化，必須在 Windows 帳戶下運行 MSSQLSERVER 服務(wù)帳戶并為該 Windows 帳戶分配 Windows SE_MANAGE_VOLUME_NAME 特權(quán)。此權(quán)限默認情況下分配給 Windows 管理員組。如果擁有系統(tǒng)管理員權(quán)限，您可以通過將 Windows 帳戶添加到“執(zhí)行卷維護任務(wù)”安全策略來分配此權(quán)限。默認MSSQLSERVER是在LocalSystem帳號啟動的，但此帳號的SE_MANAGE_VOLUME_NAME 特權(quán)是被禁用的。詳見http://msdn.microsoft.com/en-us/library/ms684190(VS.85).aspx
結(jié)論：定期執(zhí)行磁盤碎片整理并為數(shù)據(jù)文件分配合適的初始大小。并制定任務(wù)計劃，在系統(tǒng)空閑時根據(jù)現(xiàn)在數(shù)據(jù)的實際大小調(diào)整數(shù)據(jù)文件的大小，減少對系統(tǒng)繁忙時因為文件增長帶來的開銷。
日志文件的碎片
不同于數(shù)據(jù)文件，日志文件不能使用即時文件初始化進行自增長。因此，在分配一個很大自增長量時就會很耗時。在這個操作期間，所有的inset、delete、update操作都會被阻塞。那么隨后一斷時間數(shù)據(jù)庫的整體性能也會受到很大的影響。就像高速公路突然塞車被疏導之后一樣。在系統(tǒng)內(nèi)部，會把這些日志文件分成好多個虛擬的日志文件(VLF)，你可以使用DBCC LOGINFO來查看你當前的日志文件中有多少個VLF。如果返回的結(jié)果數(shù)很多，證明你應(yīng)該對日志進行維護了。這就和數(shù)據(jù)文件的磁盤碎片一樣，會對性能造成嚴重影響。這個數(shù)量是由日志文件的整體大小和擴張日志使用的增量在內(nèi)部決定的，我們無法控制。
但是，因為日志是順序?qū)懭氲模嬲拇疟P碎片對性能影響其實不是很大。如果你的增量設(shè)置過小，會因為頻繁的調(diào)整日志文件而影響到VLF。如果你設(shè)置的增量過大，又會占用過長的文件分配時間。因此，最好的辦法就是你控制你的事務(wù)盡可能的短。同時，定期的備份你的日志，以使日志可以截斷。從而防止日志文件進行自增長而帶來的性能開銷。一直以來有種誤解就是認為完整恢復模式的數(shù)據(jù)庫不會自動截斷事務(wù)日志。如果你從來沒有對這個數(shù)據(jù)庫做過完整備份，其實它也是可以對事務(wù)日志自動截斷的。
結(jié)論：VLF越少越好，建議的數(shù)值是不超過5個。定時對事務(wù)日志進行備份，以最快截斷以供后續(xù)使用。
索引的內(nèi)部和外部碎片
這些碎片都是邏輯上的碎片。整天都在討論索引碎片，相信這個大家應(yīng)該都很清楚了。不再多羅嗦，概括如下：內(nèi)部碎片受頁面填充度的影響，如果碎片過多使表所占的實際頁面數(shù)比無碎片時多出很多。因此在表掃描時會發(fā)生更多的I/O操作，但是索引查找時不會受到很大影響。外部碎片是因為頁面的邏輯順序和硬盤上的物理順序不一致或是分區(qū)的不連續(xù)所造成的。這時，如果使用索引進行范圍查找的話，因為要按照記錄的邏輯順序進行記取，會引起磁頭來回移動。關(guān)于索引碎片的維護，請參見聯(lián)機文檔。
文件的目錄存儲及文件名要求
在目錄中新建、訪問、刪除文件時，都會在目錄的元數(shù)據(jù)中進行相應(yīng)的搜索或執(zhí)行Chkdsk.exe命令完成相應(yīng)的任務(wù)。因此，如果文件過多或是目錄層次太多，會花費更長的時間完成。建議文件數(shù)目不超過100,000，當然我們很多時候永遠達不到這個數(shù)目。同時，Windwos NT之后的版本，為了提供向后兼容性，在你對目錄中的任何文件修改之后，不符合8.3文件格式的長文件名都會生成一個8.3格式文件名。如果你的目錄中有上百個長文件名的文件，這會帶來一定的性能損失。因此，如果機器上沒有運行16位的程序，可通過注冊表把NtfsDisable8dot3NameCreation設(shè)置為1，禁止生成8.3文件名。注冊表位置如下：HKEY_LOCAL_MACHINE"SYSTEM"CurrentControlSet"Control"FileSystem"NtfsDisable8dot3NameCreation。那么日志文件和數(shù)據(jù)文件是在什么時候才會被修改呢？如果你不怕葬你的硬盤，運行每個腳本之前創(chuàng) 建一個新的Northwind數(shù)據(jù)庫。你可以運行一下下面的腳本，此例也正好演示一下insert into和select into的效率問題。
USE Northwind;
GO
select * into my_customers
from dbo.Customers where 1=0
GO
insert into my_customers
select c1.*
from dbo.Customers c1,dbo.Customers c2,dbo.Customers c3
--觀察運行前后的數(shù)據(jù)文件和日志文件的增長
--insert into被完整記錄于日志中，我們發(fā)現(xiàn)
--日志文件增長了很大，我的長到了500M多
--在新建Northwind數(shù)據(jù)庫后，運行下面的腳本
--select into作為一個大批量操作，只記錄了部分事務(wù)
--因此日志增長不是很大，我的長到了4M
--因此從性能上來說select into效率高于insert into
select c1.*
into my_customers
from dbo.Customers c1,dbo.Customers c2,dbo.Customers c3
硬盤格式化的簇大小設(shè)置
客戶給我們一臺新的服務(wù)器，我們可以最大調(diào)整的就是硬盤。CPU、內(nèi)存就擺在那了，客戶說沒有更好的機器了。同時，硬盤的I/O效率也是影響查詢性能的關(guān)鍵因素。SQL2005對tempdb的要求越來越高，如果條件允許，一般把tempdb、數(shù)據(jù)文件、索引文件、全文目錄都分別存放在獨立的RAID5陣列中（有時MSFTESQL服務(wù)會因為磁盤I/O過高而暫停服務(wù)），日志文件則存放在RAID1+0或RAID1中，操作系統(tǒng)和SQLSERVER存放于RAID1中。硬盤的扇區(qū)大小默認是512個字節(jié)，那么我們在對新的硬盤進行格式化時，選擇的簇的大小多少才是最合適的？陣列的條帶容量大小應(yīng)該設(shè)置為多少？
因為一個數(shù)據(jù)頁面是8K，數(shù)據(jù)頁面在內(nèi)部由擴展分區(qū)進行管理。一個擴展分區(qū)包含了8個邏輯連續(xù)的頁面。分區(qū)的管理是通過全局分配映射頁面（GAM，只保存超過8個頁面的表，統(tǒng)一分區(qū)）和共享全局分配映射頁面（SGAM，保存小于8個數(shù)據(jù)頁面的表，混合分區(qū)）來進行管理的，一個數(shù)據(jù)文件的第2個頁面是GAM，第3個頁面是SGAM。每個GAM和SGAM能管理的頁面范圍是4G，每4G都會增加一個GAM和SGAM。在你創(chuàng)建一個新的數(shù)據(jù)庫是，使用DBCC PAGE命令來觀察這兩個頁面，可以看到數(shù)據(jù)庫已經(jīng)分配了很多擴展分區(qū)，還保留了一些分區(qū)。在創(chuàng)建表時，新加記錄后，如果表總共占用不到8個數(shù)據(jù)頁面的話會被分配到SGAM中，超過8個頁面時才會被分配到GAM分區(qū)中。前面我們提到過索引的外部碎片是因為頁面的邏輯順序和硬盤上的物理順序不一致或是分區(qū)的不連續(xù)所造成的。因此，如果我們把簇的大小設(shè)置為64K時，正好和一個分區(qū)大小一樣，那么這個分區(qū)一旦被某個表所使用后，就不能被另外的表所使用了。從而減少了數(shù)據(jù)頁面的外部碎片，但是分區(qū)的不連續(xù)還是不能避免。那么把簇大小設(shè)為128K呢？因為讀取數(shù)據(jù)時，磁盤是按簇的大小進行讀取的。設(shè)置簇過大，會一次讀取出很多無用的內(nèi)容。即便你只讀取一條記錄，SQLSERVER還是會把記錄所在的整個頁讀取出來。這時，實際的磁盤是讀取出了64K。但是因為簇是連續(xù)的扇區(qū)，因此多讀取的這一部分，對性能的影響基本是可以忽略的。因為磁盤主要受尋道和輪詢延遲影響。
對于RAID中的條帶容量設(shè)置，內(nèi)部的工作機制我現(xiàn)在還不是很清楚。只是通過下面的文檔得出的結(jié)論256K。但是網(wǎng)上很多介紹的都是說作為數(shù)據(jù)庫應(yīng)用時應(yīng)該小于簇的大小，這和下面微軟的文檔描述不一致。更多內(nèi)容參見：http://www.microsoft.com/whdc/archive/subsys_perf.mspx
為你的硬盤啟動寫入緩存
在沒有專門緩存控制器時，這會提高磁盤的I/O效率，但是會增加數(shù)據(jù)丟失的風險。但是并不會造成數(shù)據(jù)的不一致。我們來看一下事務(wù)操作的過程，它采用預(yù)寫事務(wù)日志(WAL)的方式來保證ACID。如圖所示：
事務(wù)提交后，修改先反應(yīng)到事務(wù)日志中，這時可能會還存在于磁盤緩存中。如果這時突然斷電，檢查點操作還沒有來得急把提交的事務(wù)寫入數(shù)據(jù)文件。重啟服務(wù)后日志文件中的并沒有真正包含所提交的事務(wù)，redo操作失敗了，你提交的事務(wù)丟失了。但是如果事務(wù)日志從緩存中寫入了磁盤后斷電，是不會丟失數(shù)據(jù)的。如果是日志文件保存在緩存中，而數(shù)據(jù)文件已從緩存中寫入了磁盤。這時數(shù)據(jù)不會丟失，只是日志中看不到你提交的事務(wù)記錄了。因為寫入磁盤時是以8K寫入的，也就是16個扇區(qū)的操作。如果只完成了部分扇區(qū)的寫入后，斷電了。這時我們就會收到824錯誤了，因為頁面的校驗和發(fā)生錯誤致使無法讀取出此頁了。數(shù)據(jù)庫校驗和設(shè)置在page_verity選項中，有三個選項可以設(shè)置：checksum、torn_page_detection、none。開銷依次減少，安全性依次減弱。每次發(fā)生校驗和錯誤時，都會在msdb.dbo.suspect_pages中得到一條記錄。如果出現(xiàn)這樣的錯誤，而你沒有備份，你只能冒著丟失數(shù)據(jù)的風險執(zhí)行DBCC命令來忽略掉這一頁了。
以上各人見解，如有異議請指正！