–故意塞 1 千萬筆記錄到 Northwind DB 的資料表,但該資料表沒有建立 Clustered Index
DELETE t
雖然刪除所有記錄,之後查詢空間使用
exec sp_spaceused
dbcc showcontig(t)
結果如下:
DBCC SHOWCONTIG scanning ‘t’ table…
Table: ‘t’ (373576369); index ID: 0, database ID: 6
TABLE level scan performed.
– Pages Scanned…………………………..: 6248
– Extents Scanned…………………………: 783
– Extent Switches…………………………: 782
– Avg. Pages per Extent……………………: 8.0
– Scan Density [Best Count:Actual Count]…….: 99.74% [781:783]
– Extent Scan Fragmentation ……………….: 0.26%
– Avg. Bytes Free per Page…………………: 7954.6
– Avg. Page Density (full)…………………: 1.72%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
可以看到資料表 t 依然占用非常多的資料頁,但每一頁都幾近空的,可是仍有管理資訊。
能透過 dbcc shrinkfile 釋放頁面
dbcc shrinkfile(Northwind_Data,10)
Database 那 11G 的空間是因為沒有縮 Log(Delete 另一個不好的地方),但資料頁已經縮下來了。
若可以的話,清空資料表用 truncate 是比較好的
碰到一個國內的流程產品,其部分系統資料表有建 PK,但沒有 Clustered Index。使用一陣後,查詢 sysindexes 系統資料表/檢視,可以發現保留空間是實際資料量的數十倍,但 DBCC Shrinkdatabase 無法解決問題,這造成維護、備份的困擾,建了Clustered Index 就解決問題了。
另外,SQL 2005 後可以透過 sys.dm_exec_requests 查詢執行語法進度,例如 DBCC Shrinkdatabase
2 Comments
..在一些 join update 的场合,用heap table 有风险..明明很普通的SQL ,在heap table的情况下就hang住不动了~
可以參考以下KBServer significantly increases the unused space for some tableshttp://support.microsoft.com/default.aspx?scid=kb;EN-US;924947 Ray Yen