iT邦幫忙

2019 iT 邦幫忙鐵人賽

DAY 28
1
自我挑戰組

IT人員面面觀系列 第 26

檢查資料庫中每張資料表的使用狀況

通常在系統上線後,隨著User日積月累地去操作系統的次數,
你會發現有些資料其實不需要經常被User存取到,
或者是根本User也不需要這些資料了,
此時為了增進系統的效能,應該要著手進行資料搬移或刪除等動作,
俗稱HouseKepping的作業。

舉例來說:
1.User有可能不需要經常的存取到上一季的資料,
那麼可以寫個排程去定期將上一季的資料,
搬至另一個表格存檔封存,類似E-Mail封存的概念,
而原本這張表格只需要保留本季的資料即可。
2.User可能再也不需要存取一年前的舊資料,
3.那麼即可寫個排程去定期刪除超過一年的舊資料。

因此定期查看每張資料表的使用情況就變的重要了,此類查詢SQL指令如下:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    t.modify_date AS ModifyDate,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB    
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 NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, t.modify_date, p.Rows
ORDER BY 
    t.Name

https://ithelp.ithome.com.tw/upload/images/20181028/20107408cRefqE7flB.jpg

由上圖可看出那些表格擁有比較多筆資料和所佔的容量,
以及他的表格中資料最後的修改日期,
那麼就可以先針對最近修改日期而且有數萬筆以上資料的表格,
進行舊資料的搬移或刪除。


上一篇
log檔和tempdb檔案大小一直增加時,該如何縮小?
下一篇
主動通知SQL Server發生錯誤的機制
系列文
IT人員面面觀28

尚未有邦友留言

立即登入留言