[SQL][問題處理]資料表在每個資料檔案各占用多少空間
臨時遇到一個狀況，原本在某台 SQL Server 主機上因為有一些 I/O 瓶頸，因此協助設定了 Instance 參數，調整「平行處理原則的成本臨界值」和「平行處理原則的最大程度」，並且將大量 I/O 的資料庫增加三個 NDF，並且重建 Cluster Index，讓資料可以平均存放。看起來是個很單純的處理，因此花了一些時間監控和測試，取得較適合的參數值之後，就協助客戶做好設定了。
這樣的處理經過一兩週的測試之後，效果看起來還算不錯，都有達到預期改善的目標，就要在結案收款的時候，客戶忽然出了一個難題，要我們證明資料表內的資料都有平均散放在這些資料表內。這就有點頭痛了，就以往所知道的，可以透過一些系統檢視，像是 sys.indexes 去取得在每個 Partition 下面的 rows 和 pages，似乎沒有辦法知道在每個檔案上各占用多少，要怎麼要來知道呢 ?
就在百思不得其解的時候，百敬老師提醒了一段「要能呈現 page id 的分布」，讓我忽然被點醒了，想到可以利用 「DBCC IND」來處理，因此就寫了以下的程式碼
-- 建立暫存資料表
CREATE TABLE #DBCCIND
(
PageFID INT,
PagePID INT,
IAMFID INT,
IAMPDF INT,
ObjectID INT,
IndexID INT,
PartitionNumber INT,
PartitionID BIGINT,
iam_chain_type SYSNAME,
PageType INT,
IndexLevel INT,
NextPageFID INT,
NextPagePID INT,
PrevPageFID INT,
PrevPagePID INT
)
GO
-- 利用 DBCC IND 將範例資料庫和資料表的資訊存放到暫存資料表內
INSERT INTO #DBCCIND
EXEC( 'DBCC IND("SampleDB",SampleTable, 1) WITH TABLERESULTS ')
GO
-- 取出資料頁的資訊做統計
SELECT PageFID,m.physical_name, COUNT(*)*8/1024.0 "Size"
FROM #DBCCIND i
JOIN sys.master_files m ON m.database_id = DB_ID("SampleDB") AND m.file_id = i.PageFID
WHERE IndexID = 1 AND PageType = 1
GROUP BY PageFID,m.physical_name
ORDER BY PageFID
執行完之後就可以得到結果了
而在上述的語法中，大家比較陌生的應該是 DBCC IND，他前面兩個參數分別是資料庫和物件，第三個參數就有 –2,–1, 0 , 1 四種值可以使用，其中：
1 Cluster Index
0 所有的資料頁和 IAM ( Heap )
-1 所有的頁面資訊，包含 LOB ( Large Object Binary )
-2 所有的 IAM 資訊
由於我的範例資料表是有建立 Cluster Index，因此在上述的範例中，我在 DBCC IND 第三個參數填入參數 1 。而 DBCC IND 的結果中，我就取出所有的資料頁 ( PageType = 1 ) 的資訊，因為他是計算出有多少的 Page，因此我把結果 *8 / 1024.0，將他換算成為 MB。
由於這個狀況是在 SQL Server 2008，因此處理起來會比較麻煩一點。然而如果您的環境是 SQL Server 2012 以上，就不用那麼麻煩了，可以用另外一個新的 DMV 「sys.dm_db_database_page_allocations」來取代，因此可以更為簡化寫成如下的方式
-- 利用 DMV 來處理
select allocated_page_file_id,count(*)*8/1024.0 'Size' , m.physical_name
from sys.dm_db_database_page_allocations( DB_ID('SampleDB'), OBJECT_ID('SampleTable'),1, NULL,NULL) a
join sys.master_files m ON a.database_id = m.database_id AND a.allocated_page_file_id = m.file_id
group by allocated_page_file_id,m.physical_name
order by allocated_page_file_id
出來的結果是一樣的，但使用起來方便多了。如果大家對這些指令想了解的話，可以參考以下的網址，會有更詳細的說明。
參考資料
1. More undocumented fun: DBCC IND, DBCC PAGE, and off-row columns