SQL 語法空間省空間魔術1

2020-11-11

SQL

今天又面臨了雲端AZURE上的DB快要緊繃爆炸了!!!!

然後我就把"幾個"沒有在用的TABLE裡面過期的資料用DELETE下了
結果很精彩我的空間一點都沒少(感謝AZURE平台)

好然後就借用了網路上的語法


SELECT
--1 as 'T', 
--t.*,
--1 as 'I',
--i.*,
--1 as 'P', 
--p.*,
--1 as 'A',
--a.*,
--1 as 'S',
--s.*
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 /1024 AS TotalSpaceMB, 
    SUM(a.total_pages)  AS TotalPage, 
    SUM(a.used_pages) * 8 /1024 AS UsedSpaceMB, 
    SUM(a.used_pages)  AS UsedPage, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 /1024 AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
--t.Name  in( N'VarMaxsize',N'Maxsize')
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB

檢查一下每個表的長相

意外發現有0筆資料但是有空間的資料表(?)

然後再仔細研究以後
資料庫很貼心delete 其實不一定真的會刪除而是會幫你標註
在表裡面也會有分三種儲存體來儲存
1. IN_ROW_DATA (char int 等等固定長度的)
2. LOB_DATA (大量資料的 nvarchar(max) 或是text)
3.ROW_OVERFLOW_DATA (nvarchar 固定長度的)
根據資料型態不同資料庫會存放在三種不同的TYPE(CREATE的時候就會相對應的產生)

delete後並不會刪除
解決方法有三
1.delete (tablock) <<實測目前沒用網路上說要刪除到一定的量觸發了TABLELOCK等級
2.truncate <<這個絕對有用連HEADER的7 byte都清掉了
3.alter table rebuild<<這個確定有用但是如果你的TABLE沒啥殘碎空間也沒用

檢查方法也很簡單
就直接開表新增然後一直刪除配合AZURE DB平台或是上面的語法就可以算大小

如果有人的程式會一直DELETE 明明沒有多少資料量但是維運整天在哀號要加硬體
希望這篇有幫助

參考:https://www.cnblogs.com/woodytu/p/4488930.html
參考:http://sqlworker.blogspot.com/2019/01/delete-heap-table.html

回首頁