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