《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.name
from sys.sysobjects o 
inner join sys.schemas s
on o.uid=s.schema_id
where o.id=133575514
go
select id,indid,name
from sys.sysindexes
where id=133575514 and  indid=1
go
exec 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 rowCounts
from sys.dm_db_partition_stats p inner join sys.objects o
on p.object_id=o.object_id
group 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個虛擬日志單元的物理日志文件。當創建數據庫時，邏輯日志文件從物理日志文件的始端開始。新日志記錄被添加到邏輯日志的末端，然后向物理日志的末端擴展。

當邏輯日志的末端到達物理日志文件的末端時，新的日志記錄將回繞到物理日志文件的始端，繼續向后寫