沒有叢集索引的資料表,以 Delete 刪除記錄後不會釋放空間

–故意塞 1 千萬筆記錄到 Northwind DB 的資料表,但該資料表沒有建立 Clustered Index
DELETE t

雖然刪除所有記錄,之後查詢空間使用
exec sp_spaceused

image

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)

image
Database 那 11G 的空間是因為沒有縮 Log(Delete 另一個不好的地方),但資料頁已經縮下來了。

若可以的話,清空資料表用 truncate 是比較好的

碰到一個國內的流程產品,其部分系統資料表有建 PK,但沒有 Clustered Index。使用一陣後,查詢 sysindexes 系統資料表/檢視,可以發現保留空間是實際資料量的數十倍,但 DBCC Shrinkdatabase 無法解決問題,這造成維護、備份的困擾,建了Clustered Index 就解決問題了。

另外,SQL 2005 後可以透過 sys.dm_exec_requests 查詢執行語法進度,例如 DBCC Shrinkdatabase

2 Comments

  1. kang
    Posted 2009 年 12 月 29 日 at 01:22:35 | Permalink | 回覆

    ..在一些 join update 的场合,用heap table 有风险..明明很普通的SQL ,在heap table的情况下就hang住不动了~

  2. 瑞宏
    Posted 2010 年 01 月 08 日 at 06:44:31 | Permalink | 回覆

    可以參考以下KBServer significantly increases the unused space for some tableshttp://support.microsoft.com/default.aspx?scid=kb;EN-US;924947 Ray Yen

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: