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