MSSQL 監控數據/日志文件增長實現方法

2020-07-25 13:03:52

字體：大中小

來源：轉載

供稿：網友

前幾天,在所有數據庫服務器部署了監控磁盤空間的存儲過程和作業后（MS SQL 監控磁盤空間告警），今天突然收到了兩封告警郵件，好吧，存儲規劃是一方面，但是，是不是要分析一下是什么原因造成磁盤空間不足的呢？會不會是因為突然暴增的日志文件，抑或是系統業務猛增導致數據量暴增，還是歷史數據累計原因....分析總得有數據來支撐吧，但是現在只有那些數據文件的當前大小信息，沒有數據文件的歷史增長變化信息，所以，今天就想實現這么一個功能，每天（頻率可以調整）去收集一下數據文件的信息，放到一個表里面，這樣方便我們分析數據文件的增長演變例程，甚至你可以將數據文件的增長幅度和業務變化關聯起來分析....

那么接下來就是我的設計思路和實現代碼，目前只是簡單實現，以后將繼續優化,豐富一些功能。

首先我們創建一個表DiskCapacityHistory，用來保存數據庫文件的歷史增長變化信息：

復制代碼代碼如下:

USE msdb;
GO

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'') AND xtype='U')
DROP TABLE DiskCapacityHistory;
GO

CREATE TABLE dbo.DiskCapacityHistory
(
    [Date_CD]            INT                     ,
    [DataBaseID]         INT                     ,
    [FileID]             INT                     ,
    [DataBaseName]       sysname                 ,
    [LogicalName]        VARCHAR(32)             ,
    [FileTypeDesc]       NVARCHAR(60)            ,
    [PhysicalName]       NVARCHAR(260)         　,
    [StateDesc]          NVARCHAR(60)         　 ,
    [MaxSize]            NVARCHAR(32)            ,
    [GrowthType]         NVARCHAR(8)             ,
    [IsReadOnly]         INT                     ,
    [IsPercentGrowth]    SMALLINT                ,
    [Size]               FLOAT                   ,
    [Growth_MOM_RAT]     FLOAT                   ,
    [Growth_YOY_RAT]     FLOAT                   ,
    CONSTRAINT PK_DiskCapacityHistory PRIMARY KEY(Date_CD, DataBaseID, FileID)
);

復制代碼代碼如下:

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '日期編碼'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'Date_CD';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '數據庫標識'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'DataBaseID';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '文件標識'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'FileID';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '數據庫名稱'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'DataBaseName';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '數據庫邏輯名稱'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'LogicalName';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '文件類型描述'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'FileTypeDesc';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '物理數據庫文件'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'PhysicalName';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '文件最大大小'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'MaxSize';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '文件增長類型'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'GrowthType';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '是否只讀類型'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'IsReadOnly';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '是否按百分比增長'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'IsPercentGrowth';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '數據文件大小(GB)'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'Size';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '文件增長環比(%)'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'Growth_MOM_RAT';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
    , @value = '文件增長同比(%)'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N'DiskCapacityHistory'
    , @level2type = N'COLUMN'
    , @level2name = N'Growth_YOY_RAT';

GO

IF OBJECT_ID(N'sp_diskcapacity_cal') IS NOT NULL
DROP PROCEDURE sp_diskcapacity_cal;
GO

接下來，我們創建存儲過程，負責來收集、統計這些數據庫的文件的相關信息。關于環比/同比,正常情況一般是:
環比: (指標當前值 - 指標值(上個月同一天))/ 指標值(上個月同一天) 。
同比: (指標當前值 - 指標值(去年月同一天))/ 指標值(去年月同一天) 。
其實如果關注每天的數據文件變化情況，這個代碼里面的環比、同比其實意義不大，其實我們可以這樣定義環比、同比：
環比：（指標當前值 - 指標值（昨天）)/指標值（昨天）。
同比：（指標當前值 - 指標值（上個月））/指標值（上個月）
當然，你也可以把這四個指標都加上，對比參考，側重點不同而已。

復制代碼代碼如下:

IF  OBJECT_ID(N'sp_diskcapacity_cal')IS NOT NULL
    DROP PROCEDURE sp_diskcapacity_cal;
GO

CREATE PROCEDURE dbo.sp_diskcapacity_cal
AS
BEGIN

   INSERT INTO dbo.DiskCapacityHistory
   (
        [Date_CD]           ,
        [DataBaseID]        ,
        [FileID]            ,
        [DataBaseName]      ,
        [LogicalName]       ,
        [FileTypeDesc]      ,
        [PhysicalName]      ,
        [StateDesc]         ,
        [MaxSize]           ,
        [GrowthType]        ,
        [IsReadOnly]        ,
        [IsPercentGrowth]   ,
        [Size]                
   )
     SELECT CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT)
                                                                            AS DateCD        ,
            database_id                                                     AS DataBaseId    ,
            file_id                                                         AS FileID        ,
            DB_NAME(database_id)                                            AS DataBaseName  ,
            name                                                            AS LogicalName   ,
            type_desc                                                       AS FileTypeDesc  ,
            physical_name                                                   AS PhysicalName  ,
            state_desc                                                      AS StateDesc     ,
            CASE WHEN max_size = 0 THEN N'不允許增長'
                 WHEN max_size = -1 THEN N'自動增長'
                 ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'G'
            END                                                             AS MaxSize       ,
            CASE WHEN is_percent_growth = 1
                 THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'
                 ELSE RTRIM(CAST(Growth AS CHAR(10))) + 'M'
            END                                                             AS Growth        ,
            Is_Read_Only AS IsReadOnly ,
            Is_Percent_Growth AS IsPercentGrowth ,
            CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4))                 AS Size
     FROM   sys.master_files;

     
     MERGE INTO dbo.DiskCapacityHistory DM USING
     (
     SELECT M.Date_CD        ,
            M.DataBaseID     ,
            M.FileID         ,
            CASE WHEN N.SIZE IS NULL OR N.SIZE = 0 THEN 0 ELSE
                (M.SIZE - N.SIZE)/N.SIZE END AS Growth_MOM_RAT
     FROM dbo.DiskCapacityHistory M
      LEFT JOIN dbo.DiskCapacityHistory  N ON
              CAST(CAST(M.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH, 1, CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
          AND M.DataBaseID = N.DataBaseID AND M.FileID = N.FileID
     WHERE M.Date_CD =  CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT)
     ) TMP
     ON
     (
            DM.Date_CD       = TMP.Date_CD     AND
            DM.DatabaseId    = TMP.DataBaseId  AND
            DM.FileId        = TMP.FileId
     )
     WHEN MATCHED THEN UPDATE SET
        DM.Growth_MOM_RAT = TMP.Growth_MOM_RAT;
END    
GO

順便吐槽一下：由于前兩年一直使用ORACLE數據庫，很少接觸SQL SERVER，在實現上面功能的時候，我深深的體會到了ORACLE和SQL SERVER的巨大差距，如果用PL/SQL實現，那非常方便快捷，但是用T-SQL讓我遇到了幾個相當痛苦地方，下面順便記錄對比一下吧：

一：由于我采用INT來保存日期數據，那么需要在DATE類型和INT類型之間轉換，我們來對比一下兩者的差別吧：

1.1 DATE類型轉換為整型:

T-SQL：

SELECT CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT);

PL/SQL：

SELECT TO_CHAR(Date_CD, 'YYYYMMDD') FROM DUAL;

1.2 整型轉換為DATE類型（字段DATE_CD）

T-SQL：

SELECT CAST(CAST(DATE_CD AS CHAR(8)) AS DATE) FROM TEST;

PL/SQL：

SELECT TO_DATE(DATE_CD, 'YYYY-MM-DD') FROM TEST;

結論: 純屬個人感受,從上面的腳本的簡單性,方便性上,感覺ORACLE完勝SQL SERVER

二：計算數據文件增長同比、環比值

1：SQL SERVER 2005 沒有MERGE語句功能，上面的腳本得改寫成

復制代碼代碼如下:

UPDATEdbo.DiskCapacityHistory
 SET     GROWTH_MOM_RAT =( SELECTCASE WHEN N.SIZE IS NULL
                                            OR N.SIZE = 0 THEN 0
                                       ELSE ( dbo.DiskCapacityHistory.SIZE
                                              - N.SIZE ) / N.SIZE
                                  END AS Growth_MOM_RAT
                         FROM     dbo.DiskCapacityHistory N
                         WHERE    CAST(CAST(dbo.DiskCapacityHistory.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH,
                                                            1,
                                                            CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
                                  AND dbo.DiskCapacityHistory.DataBaseID = N.DataBaseID
                                  AND dbo.DiskCapacityHistory.FileID = N.FileID
                       )
 WHEREdbo.DiskCapacityHistory.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120),
                                                     '-', '') AS INT)

UPDATEdbo.DiskCapacityHistory
 SET     GROWTH_YOY_RAT =( SELECTCASE WHEN N.SIZE IS NULL
                                            OR N.SIZE = 0 THEN 0
                                       ELSE ( dbo.DiskCapacityHistory.SIZE
                                              - N.SIZE ) / N.SIZE
                                  END AS Growth_YOY_RAT
                         FROM     dbo.DiskCapacityHistory N
                         WHERE    CAST(CAST(dbo.DiskCapacityHistory.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH,
                                                            12,
                                                            CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
                                  AND dbo.DiskCapacityHistory.DataBaseID = N.DataBaseID
                                  AND dbo.DiskCapacityHistory.FileID = N.FileID
                       )
 WHEREdbo.DiskCapacityHistory.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120),
                                                     '-', '') AS INT)

或

復制代碼代碼如下:

CREATE TABLE #DiskCapacityHistory
    (
      DATE_CD INT ,
      DataBaseID INT ,
      FileID INT ,
      Growth_MOM_RAT FLOAT
    ) ;

  INSERTINTO #DiskCapacityHistory
        SELECT  M.DATE_CD ,
                M.DataBaseID ,
                M.FileID ,
                CASE WHEN N.SIZE IS NULL
                          OR N.SIZE = 0 THEN 0
                     ELSE ( M.SIZE - N.SIZE ) / N.SIZE
                END AS Growth_MOM_RAT
        FROM    dbo.DiskCapacityHistory M ,
                dbo.DiskCapacityHistory N
        WHERE   CAST(CAST(M.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH, 1,
                                                              CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
                AND M.DataBaseID = N.DataBaseID
                AND M.FileID = N.FileID
                AND M.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE()
                                             - 1, 120), '-', '') AS INT)

  UPDATE dbo.DiskCapacityHistory
     SET Growth_MOM_RAT = M.Growth_MOM_RAT
    FROM #DiskCapacityHistory M
   WHERE dbo.DiskCapacityHistory.DATE_CD = M.DATE_CD
        AND dbo.DiskCapacityHistory.DataBaseID = M.DataBaseID
        AND dbo.DiskCapacityHistory.FileID = M.FileID ;

2: 幸好SQL 2008還把ORACLE的MERGE的功能給模仿了過來，但是T-SQL缺少ORACLE數據庫強大的分析函數LAG，如果有這個，我計算環比，同比就非常方便了，一個SQL就搞定了，下面是個例子，本想把ORACLE的SQL也做個例子展現，但是又要建表、造數，折騰起來比較麻煩。

復制代碼代碼如下:

MERGE INTO DM.TM_WGGBO_IDCTOBUSVOLDTL_DAY DM
USING    (
             SELECT *
               FROM (
                       SELECT    DATE_CD,
                                 CITY_ID,
                                 IDC_NODE,
                                 VOL_TYPE,
                                 LAG(IDC_VOL_RAT   ) OVER(PARTITION BY CITY_ID,IDC_NODE,VOL_TYPE,SUBSTR(DATE_CD,7,2) ORDER BY SUBSTR(DATE_CD,0,6)) AS IDC_MOM_RAT                ,
                                 LAG(IDC_VOL_RAT   ) OVER(PARTITION BY CITY_ID,IDC_NODE,VOL_TYPE,SUBSTR(DATE_CD,5,4) ORDER BY SUBSTR(DATE_CD,0,4)) AS IDC_YOY_RAT                 ,

                         FROM DM.TM_WGGBO_IDCTOBUSVOLDTL_DAY
                       ) T
                 WHERE EXISTS(SELECT 1 FROM ETL.T_IDCVOL_DAY_${ssid} WHERE DATE_CD = T.DATE_CD)
          ) TEMP
                        ON (
                                DM.DATE_CD     = TEMP.DATE_CD     AND
                                DM.CITY_ID     = TEMP.CITY_ID     AND
                                DM.IDC_NODE    = TEMP.IDC_NODE    AND
                                DM.VOL_TYPE    = TEMP.VOL_TYPE
                                )
WHEN MATCHED THEN
  UPDATE
       SET DM.IDC_MOM_RAT    =       TEMP.IDC_MOM_RAT                    ,
           DM.IDC_YOY_RAT    =       TEMP.IDC_YOY_RAT                     

COMMIT;