SQL Server 檔案和檔案組

標籤：uname   shrink   體驗   選項設定   base   儲存   strong   default   添加   

資料庫是資料的倉庫，用於儲存資料，而儲存資料需要媒介，現在的儲存媒介，最常用的是硬碟，土豪一點的伺服器使用固態硬碟（SSD），特殊用途的伺服器使用記憶體。資料庫最常用的隱藏檔是資料檔案和記錄檔，資料檔案用於儲存資料，由一個主要資料檔案（.mdf）和若干個輔助資料檔案（.ndf）構成；記錄檔用於儲存事物日誌，由.ldf檔案構成。不同的檔案可以存分布到不同的物理硬碟上，這樣便於分散硬碟IO，提高資料的讀取速度。

資料檔案的組合，稱作檔案組（File Group），資料庫不能直接設定儲存資料的資料檔案，而是通過檔案組來指定。

一，檔案組和檔案的作用

SQL Server的資料存放區在檔案中，檔案是實際儲存資料的物理實體，檔案組是邏輯對象，SQL Server通過檔案組來管理檔案。

 

一個資料庫有一個或多個檔案組，主檔案組（Primary File Group）是系統自動建立的，使用者可以根據需要添加檔案組。每一個檔案組管理一個或多個檔案，其中主檔案組中包含主要資料檔案（master data file），副檔名是.mdf，這個檔案是系統預設產生的，並且在資料庫中是唯一的；輔助檔案的副檔名是.ndf，是使用者根據需要添加的。主檔案組中也可以包含輔助檔案，除了主檔案組之外，其他檔案組只能包含輔助檔案。

例如，查看樣本資料庫的檔案組，Primary 是住檔案組，勾選Default表示住檔案組是預設的檔案組，這意味著，如果在create table和create index中沒有指定FileGroup選項，那麼SQL Server將使用預設的檔案組來儲存資料。

檔案組是一個邏輯實體，實際上，資料存放區在檔案中（.mdf和.ndf）中，每一個檔案組中都包含檔案，因此，在create table和create index命令中指定檔案組，那麼資料就會儲存到檔案組包含的檔案中。

從中可以看到，資料庫檔案的中繼資料：

• Logical Name是資料檔案的邏輯名稱，用於資料壓縮 DBCC ShrinkFile等；
• 資料庫檔案有兩種類型：Rows Data（儲存資料）和Log（儲存日誌），
• Initial Size是檔案的初始大小，
• 而Autogrowth表示檔案自動增加的大小，Maxsize是檔案大小的最大值，
• Path參數表示檔案的路徑，
• File Name是檔案的實體名稱，邏輯名稱和實體名稱可以是不同的。

使用者也可以使用sys.database_files 和 sys.filegroups 查看資料的檔案和檔案組的中繼資料。

二，使用檔案組的優勢

在實際開發資料庫的過程中，通常情況下，使用者需要關注檔案組，而不用關心檔案的實體儲存體，即使DBA改變檔案的實體儲存體，使用者也不會察覺到，也不會影響資料庫去執行查詢。除了邏輯檔案和物理檔案的分離之外，SQL Server使用檔案組還有一個優勢，那就是分散IO負載，其實現的原理是：

• 對於單分區表，資料只能存到一個檔案組中。如果把檔案組內的資料檔案分布在不同的物理硬碟上，那麼SQL Server能同時從不同的物理硬碟上讀寫資料，把IO負載分散到不同的硬碟上。
• 對於多分區表，每個分區使用一個檔案組，把不同的資料子集儲存在不同的磁碟上，SQL Server在讀寫某一個分組的資料時，能夠調用不同的硬碟IO。

這兩種方式，其本質上，都是使每個硬碟均攤系統負載，提高IO效能。

建立分區表時，不同的分區可以使用相同的檔案組，也可以使用不同的檔案組。因此，在設計檔案組時，應盡量把包含的檔案包含在不同的硬碟上，以實現物理IO的最大分散化。

在建立檔案時，伺服器CPU核的數量，決定最大的並發IO度，應該根據CPU 核的數量建立多個檔案。通常情況下，檔案的數量和CPU核的數量一致，是最佳化的設計。

還有，應該根據硬碟的效能來建立檔案組，記錄檔儲存到效能最好的硬碟上，而查詢延遲要求高的資料，也需要儲存到效能最好的硬碟上。

不是所有的資料都是同等重要的，應該根據業務需求和查詢延遲，對資料分級，因此，在設計檔案組時，應該把層級高的資料分散，而把那些基本用不到的資料存放區到效能差的，用於儲存歸檔資料的硬碟上，以實現伺服器效能的合理配置。

三，指定檔案組

在建立表時，在on子句中指定檔案組，那麼資料將儲存在該檔案組包含的檔案中：

CREATE TABLE [dbo].[student](...) ON [PRIMARY]

在建立索引時，在on子句指定檔案組選項，那麼該表的索引結構將儲存在檔案組包含的檔案中：

CREATE NONCLUSTERED INDEX [idx_student_stuname] ON [dbo].[student]( [stuname] ASC ) ON [PRIMARY]

在建立分區時，在TO自置中指定檔案組，每一個分區將儲存到檔案組中：

REATE PARTITION SCHEME scheme_nameAS PARTITION function_name TO ([fg_name1], <....>, [fp_nameN])

四，資料檔案大小增長導致的問題

當資料檔案爆滿，沒有空間儲存資料時，此時執行insert命令，這會導致資料檔案的增長。如果filegrowth選項設定的過大，會導致SQL Server耗費較長時間來實現檔案的增長，在資料檔案增長時，該檔案是不能訪問的，因此，即使使用者僅插入一條資料，也要等待很長時間才能完成查詢，對使用者來說，體驗不友好。

資料檔案增長是非常耗費系統資源和影響效能，如果設定SQL Server 自動成長，可能會導致系統效能不夠穩定，所以，應該預測可能的空間使用需求，並提前做好規劃。盡量避免空間用盡而使得SQL Server不得不自動成長的現象發生。同時也要確保每一次自動成長都能夠在可接受的時間內完成，及時滿足用戶端應用的需求。

 

推薦文檔：

檔案自動成長和自動收縮sql server

SQL Server 檔案和檔案組