您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 實作每日統計資料表新增列數與儲存空間變化

編輯：關於SqlServer

前言

在資料庫維運管理中，掌握資料表每日的成長趨勢（新增列數、資料空間、索引空間變化）對於容量規劃、效能調校與異常偵測至關重要。本文提供一套完整的 T-SQL 解決方案，透過排程作業自動記錄每日空間使用情況，並示範如何查詢增量變化。

實作步驟

1. 建立統計資料儲存表

首先建立一張表，用於保存每次掃描的資料表空間資訊，包含列數、保留空間、資料大小、索引大小與未使用空間（單位皆為 KB）。

CREATE TABLE [dbo].[t_rpt_table_spaceinfo](
    [table_name] [sysname] NOT NULL,
    [record_date] [date] NOT NULL,
    [record_time] [time](7) NOT NULL,
    [rows_count] [bigint] NULL,
    [reserved] [bigint] NULL,
    [data_size] [bigint] NULL,
    [index_size] [bigint] NULL,
    [unused] [bigint] NULL,
    CONSTRAINT [PK_t_rpt_table_spaceinfo] PRIMARY KEY CLUSTERED 
    (
        [table_name] ASC,
        [record_date] ASC,
        [record_time] ASC
    )
)

2. 建立 SQL Server Agent 作業，每日自動收集

新增一個作業，排程於每天凌晨執行一次。作業步驟使用游標遍歷所有使用者資料表（sys.tables），呼叫 sp_spaceused 取得空間資訊，寫入暫存表後再批次轉入正式統計表。

SET NOCOUNT ON;

/* 建立暫存表存放 sp_spaceused 回傳結果 */
CREATE TABLE #tablespaceinfo (
    nameinfo VARCHAR(500),
    rowsinfo BIGINT,
    reserved VARCHAR(20),
    datainfo VARCHAR(20),
    index_size VARCHAR(20),
    unused VARCHAR(20)
);

DECLARE @tablename VARCHAR(255);

/* 使用游標逐一處理每張使用者資料表 */
DECLARE Info_cursor CURSOR FOR
    SELECT '[' + [name] + ']' FROM sys.tables WHERE type = 'U';

OPEN Info_cursor;
FETCH NEXT FROM Info_cursor INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #tablespaceinfo EXEC sp_spaceused @tablename;
    FETCH NEXT FROM Info_cursor INTO @tablename;
END

/* 將暫存資料轉型並寫入正式統計表 */
INSERT INTO t_rpt_table_spaceinfo (
    record_date, record_time, [table_name], [rows_count],
    reserved, [data_size], index_size, unused
)
SELECT 
    CONVERT(date, GETDATE()),
    CONVERT(varchar(8), GETDATE(), 114),
    nameinfo,
    rowsinfo,
    CAST(REPLACE(reserved, 'KB', '') AS BIGINT),
    CAST(REPLACE(datainfo, 'KB', '') AS BIGINT),
    CAST(REPLACE(index_size, 'KB', '') AS BIGINT),
    CAST(REPLACE(unused, 'KB', '') AS BIGINT)
FROM #tablespaceinfo;

CLOSE Info_cursor;
DEALLOCATE Info_cursor;
DROP TABLE #tablespaceinfo;

3. 查詢每日增量變化

利用 ROW_NUMBER() 視窗函數為同一資料表的記錄依時間排序編號，再自我聯結比較相鄰兩筆記錄，即可得到該時段內的新增列數與空間增長量。

;WITH table_spaceinfo AS (
    SELECT 
        record_date, record_time, table_name, rows_count,
        reserved, data_size, index_size, unused,
        ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY record_date, record_time) AS list_no
    FROM t_rpt_table_spaceinfo
)
SELECT 
    _a.table_name AS 表名,
    CONVERT(varchar(20), _a.record_date) + ' ' + CONVERT(varchar(8), _a.record_time) + '~~' +
    CONVERT(varchar(20), _b.record_date) + ' ' + CONVERT(varchar(8), _b.record_time) AS [時間段範圍],
    _b.rows_count - _a.rows_count AS [新增列數],
    _b.data_size - _a.data_size AS [新增資料空間(KB)]
FROM table_spaceinfo _a
JOIN table_spaceinfo _b
    ON _a.table_name = _b.table_name
    AND _a.list_no = _b.list_no - 1
ORDER BY [時間段範圍];