《SQL Server企業級平台管理實踐》讀書筆記——SQL Server資料庫檔案分配方式

標籤：style   blog   http   io   color   ar   os   使用   for   

原文:《SQL Server企業級平台管理實踐》讀書筆記——SQL Server資料庫檔案分配方式

1、檔案分配方式以及檔案空間檢查方法

最常用的檢查資料檔案和表大小的命令就是：sp_spaceused

此命令有三個缺陷：1、無法直觀的看出每個資料檔案和記錄檔的使用方式。2、這個預存程序依賴SQL Server儲存在一些系統檢視表裡的空間使用統計資訊計算出的結果，如果沒有更新空間統計資訊，比如剛剛發生大資料插入，sp_spaceused的結果就不準確。3、這個命令主要是針對普通使用者的資料庫，對於tempdb資料庫裡儲存的一些系統臨時資料對象，是無法用這個預存程序來統計的。

可以通過“sp_helptext sp_spaceused”命令查看該預存程序明細。

SQL Server的檔案分為資料檔案（.mdf,.ndf)和記錄檔（.ldf)兩種。不同的檔案查看方式不同。

資料內容以頁形式儲存，可以通過DBCC PAGE命令查看頁內容

DBCC PAGE(<db_id>,<file_id>,<page_id>,<forma_id>)

比如我們要看AdventureWorks這個資料庫裡面的AdventureWorks_Data資料檔案裡的第3230頁。我們先從db_id可以從sp_helpdb的結果中得到；file_id可以從sp_helpfile的結果中得到。

Format_id是指定的輸出格式，有1、2、3三個值。一般來講，3這個輸出格式比較直觀。在運行DBCC PAGE之前，還需要開啟跟蹤標識（trace flag）3604.

我們來查看明細：

 這裡有幾個資訊比較重要，比如ObjectID=133575514、IndexId=1

它告訴我們，這個頁面屬於133575514這個對象，ID為1的索引（index)。通過下列語句可以得到它屬於什麼對象。

select s.name,o.namefrom sys.sysobjects o inner join sys.schemas son o.uid=s.schema_idwhere o.id=133575514goselect id,indid,namefrom sys.sysindexeswhere id=133575514 and  indid=1
goexec sp_helpindex N‘DimProduct‘go

我們可以看到此頁面屬於DimProduct表、然後這個表下面的索引：PK_DimProduct_ProductKey，這個索引建立在ProductKey列上

可以通過下面的命令來查看資料庫中某張表有那些區，該區中第一個頁面的ID是多少：

上面的這張表用到了這麼多區的內容，並且每個區中大部分儲存為8個的連續頁，只有第一個為1個頁，頁面的ID為217。

大型行的支援

在SQL Server中，行不能跨頁，屬於同一行的所有欄位的資料都要放在同一個頁面裡，頁的最大資料量是8064B(8KB)。所以一般資料類型欄位所組成的一行，最長加起來不能超過8KB，但這個限制不包括varchar(max)、nvarchar(max)、varbinary(max)這樣的資料類型，在2005以後版本中這種大類型資料欄位突破了8KB的限制，方便了使用者的使用，如果不超過8KB,資料還是會一起存放在普通資料頁裡面。如果總長度超過了8KB,SQL Server就會把這些欄位分開，單獨存放在一種叫做Row-Overflow(行溢出）的頁面裡。

資料檔案空間使用的計算方法

 查看資料庫使用的最簡單的方法，就是在Management Studio中，右鍵點擊資料庫名稱，選擇報表專案中的標準報表進行查看：

這裡的統計結果和sp_spaceused統計的結果稍有不同，原因是這裡是按照區統計的，由於每個區裡面就有8個頁，而這8個頁不一定全部被使用到了，但是如果按照區統計預設的就是全部被使用了，而按照頁統計的結果就不一樣了。

兩種統計方法不同，使用情境也不一樣，按照區統計結果速度快

按照區統計：

如果按照區統計我們使用一個命令就可以實現

DBCC SHOWFILESTATS

由於SQL Server在絕大多數時間都是按照區為單位進行分配新空間的，而系統分配頁上的資訊永遠是即時更新的，所以按照這種統計方法比較準確可靠。在伺服器負載很高的情況下也能安全執行，不會增加額外的系統負擔。所以要考察資料庫資料檔案級的使用方式，它是個比較好的選擇。

按照頁面統計

如果想要知道某個具體的表格或者索引使用了多少空間，就要從頁面層級進行分析。這裡我們就可以選擇sp_spaceused或者DBCC SHOWCONTIG

當然在SQL Server2005以後新加了一個sys.dm_db_index_physical_stats來查看索引的儲存明細，包括頁多少，鎖片百分比等

sp_spaceused是根據sys.allocation_units和sys.partitions這兩張管理檢視來計算儲存空間的。當刪除索引後、表的空間資訊不實當前資訊時，這兩張表可能不能及時反映出資料庫的準確資訊。

可以加入Undateusage這個參數，要求SQL Server為這句指令更新管理檢視裡的統計資訊。這樣做會件消耗資源的工作。在生產壞境下建議不要輕易使用。

sp_spaceused的另一個缺點一次只能查詢一個對象，不能對所有表一次性尋找，我們可以通過sys.dm_db_partition_stats視圖來實現這件事情：

select o.name,       SUM(p.reserved_page_count) as reserved_page_count,       SUM(p.used_page_count) as used_page_count,       SUM(           case when (p.index_id<2) then (p.in_row_data_page_count+           p.lob_used_page_count+p.row_overflow_used_page_count)           else p.lob_used_page_count+p.row_overflow_used_page_count           end            )as DataPages,           SUM(               case when (p.index_id<2) then row_count               else 0               end                ) as rowCountsfrom sys.dm_db_partition_stats p inner join sys.objects oon p.object_id=o.object_idgroup by o.name

sp_spaceused方法簡單，但功能比較脆弱，也不是最準確的方法。不精選使用。

sys.dm_db_partition_stats會來的更直接，對系統效能也沒有多少影響。

DBCC SHOWCONTIG(或者sys.dm_db_index_physical_stats)是檢查資料庫空間分配最準確的方法，它可以展現用了多少頁面、多少區、甚至頁面上的平均資料量。但是也會付出效能上的代價，SQL Server從整體效能角度出發，不可能一直維護這樣的底層統計資訊。為了完成這個命令，就必須對庫進行掃描。總之得到的結果越精確，掃描的範圍就越大。

總之：如果管理者只要看資料檔案的整體使用方式，DBCC SHOWFILESTATS是比較好的選擇。如果要看每個對象的空間使用方式，可以使用動態管理檢視sys.dm_db_partition_stats。如果想要瞭解每個頁、每個區的使用方式、片段程度，那DBCC SHOWCONTIG是比較好的選擇。

記錄檔

資料庫的記錄檔是包含用於恢複資料庫的所有日誌資訊。每個資料庫必須至少有一個記錄檔，當然也可以有多個。記錄檔的推薦副檔名是.ldf。與資料庫檔案按8KB組織不同，記錄檔不是按照頁、區來組織的。

SQL Server資料庫引擎在內部將每一物理記錄檔分成多個虛擬日誌單元。虛擬日誌單元沒有固定大小，且一個物理記錄檔所包含的虛擬日誌單元數不固定。管理員不能配置或設定虛擬日誌單元的大小或數量。但SQL Server會嘗試控制虛擬日誌單元的數目，把它限制在一個合理的範圍內，但是有種情況特殊，記錄檔每自動成長一次，會至少增加一個虛擬日誌單元。所以，如果一個記錄檔經曆了多次小的自動成長，裡面的虛擬日誌單元數目會比正常的記錄檔多很多。這種情況會影響到記錄檔管理的效率，甚至造成資料庫啟動要花很長時間。

交易記錄是一種迴繞的檔案。例如，假設有一個資料庫，它包含一個分成5個虛擬日誌單元的物理記錄檔。當建立資料庫時，邏輯記錄檔從物理記錄檔的始端開始。新日誌記錄被添加到邏輯日誌的末端，然後向物理日誌的末端擴充。

當邏輯日誌的末端到達物理記錄檔的末端時，新的日誌記錄將迴繞到物理記錄檔的始端，繼續向後寫

查看記錄檔的使用方式非常簡單。我們通過下面這個語句查看：

DBCC SQLPERF(LOGSPACE)

顯示了SQL Server上所有資料庫的日誌大小，以及使用比率。語句的執行不會對SQL Server帶來負擔，這個語句返回的結果總是正確的。可以隨時在SQL Server上運行這個命令。 

《SQL Server企業級平台管理實踐》讀書筆記——SQL Server資料庫檔案分配方式