MSSQL 监控数据/日志文件增长实现方法

来源：互联网发布：sql like用法编辑：程序博客网时间：2024/05/09 20:40

今天就想实现这么一个功能，每天（频率可以调整）去收集一下数据文件的信息，放到一个表里面，这样方便我们分析数据文件的增长演变例程，甚至你可以将数据文件的增长幅度和业务变化关联起来分析

前几天,在所有数据库服务器部署了监控磁盘空间的存储过程和作业后（MS SQL 监控磁盘空间告警），今天突然收到了两封告警邮件，好吧，存储规划是一方面，但是，是不是要分析一下是什么原因造成磁盘空间不足的呢？会不会是因为突然暴增的日志文件，抑或是系统业务猛增导致数据量暴增，还是历史数据累计原因....分析总得有数据来支撑吧，但是现在只有那些数据文件的当前大小信息，没有数据文件的历史增长变化信息，所以，今天就想实现这么一个功能，每天（频率可以调整）去收集一下数据文件的信息，放到一个表里面，这样方便我们分析数据文件的增长演变例程，甚至你可以将数据文件的增长幅度和业务变化关联起来分析....

那么接下来就是我的设计思路和实现代码，目前只是简单实现，以后将继续优化,丰富一些功能。

首先我们创建一个表DiskCapacityHistory，用来保存数据库文件的历史增长变化信息：

USE  msdb;GOIF  EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'') AND xtype='U')    DROP TABLE DiskCapacityHistory;GOCREATE 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';GOIF  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;GOCREATE PROCEDURE dbo.sp_diskcapacity_calASBEGIN   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 DMUSING    (             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;