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