> 数据库 > SQL Server 2005 >

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

时间:2015-10-11 10:26  来源:linux.it.net.cn   作者:IT

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

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


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

首先我们创建一个表DiskCapacityHistory，用来保存数据库文件的历史增长变化信息：
 
  1. USE  msdb;  
  2. GO  
  3.   
  4.   
  5. IF  EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'') AND xtype='U')  
  6.     DROP TABLE DiskCapacityHistory;  
  7. GO  
  8.   
  9.   
  10. CREATE TABLE dbo.DiskCapacityHistory  
  11. (  
  12.     [Date_CD]            INT                     ,  
  13.     [DataBaseID]         INT                     ,  
  14.     [FileID]             INT                     ,  
  15.     [DataBaseName]       sysname                 ,  
  16.     [LogicalName]        VARCHAR(32)             ,  
  17.     [FileTypeDesc]       NVARCHAR(60)            ,  
  18.     [PhysicalName]       NVARCHAR(260)         　,  
  19.     [StateDesc]          NVARCHAR(60)         　 ,  
  20.     [MaxSize]            NVARCHAR(32)            ,  
  21.     [GrowthType]         NVARCHAR(8)             ,  
  22.     [IsReadOnly]         INT                     ,  
  23.     [IsPercentGrowth]    SMALLINT                ,  
  24.     [Size]               FLOAT                   ,  
  25.     [Growth_MOM_RAT]     FLOAT                   ,  
  26.     [Growth_YOY_RAT]     FLOAT                   ,  
  27.     CONSTRAINT PK_DiskCapacityHistory PRIMARY KEY(Date_CD, DataBaseID, FileID)      
  28. );  
  29.   
  30.   
  31. 复制代码 代码如下:  
  32.   
  33.   
  34. EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  35.     , @value = '日期编码'  
  36.     , @level0type = N'SCHEMA'  
  37.     , @level0name = N'dbo'  
  38.     , @level1type = N'TABLE'  
  39.     , @level1name = N'DiskCapacityHistory'  
  40.     , @level2type = N'COLUMN'  
  41.     , @level2name = N'Date_CD';  
  42.   
  43.   
  44. EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  45.     , @value = '数据库标识'  
  46.     , @level0type = N'SCHEMA'  
  47.     , @level0name = N'dbo'  
  48.     , @level1type = N'TABLE'  
  49.     , @level1name = N'DiskCapacityHistory'  
  50.     , @level2type = N'COLUMN'  
  51.     , @level2name = N'DataBaseID';  
  52.   
  53.   
  54. EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  55.     , @value = '文件标识'  
  56.     , @level0type = N'SCHEMA'  
  57.     , @level0name = N'dbo'  
  58.     , @level1type = N'TABLE'  
  59.     , @level1name = N'DiskCapacityHistory'  
  60.     , @level2type = N'COLUMN'  
  61.     , @level2name = N'FileID';  
  62.   
  63.   
  64. EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  65.     , @value = '数据库名称'  
  66.     , @level0type = N'SCHEMA'  
  67.     , @level0name = N'dbo'  
  68.     , @level1type = N'TABLE'  
  69.     , @level1name = N'DiskCapacityHistory'  
  70.     , @level2type = N'COLUMN'  
  71.     , @level2name = N'DataBaseName';  
  72.   
  73.   
  74.    
  75.  EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  76.     , @value = '数据库逻辑名称'  
  77.     , @level0type = N'SCHEMA'  
  78.     , @level0name = N'dbo'  
  79.     , @level1type = N'TABLE'  
  80.     , @level1name = N'DiskCapacityHistory'  
  81.     , @level2type = N'COLUMN'  
  82.     , @level2name = N'LogicalName';  
  83.   
  84.   
  85.    
  86. EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  87.     , @value = '文件类型描述'  
  88.     , @level0type = N'SCHEMA'  
  89.     , @level0name = N'dbo'  
  90.     , @level1type = N'TABLE'  
  91.     , @level1name = N'DiskCapacityHistory'  
  92.     , @level2type = N'COLUMN'  
  93.     , @level2name = N'FileTypeDesc';  
  94.      
  95.   
  96.   
  97.      
  98. EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  99.     , @value = '物理数据库文件'  
  100.     , @level0type = N'SCHEMA'  
  101.     , @level0name = N'dbo'  
  102.     , @level1type = N'TABLE'  
  103.     , @level1name = N'DiskCapacityHistory'  
  104.     , @level2type = N'COLUMN'  
  105.     , @level2name = N'PhysicalName';  
  106.   
  107.   
  108. EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  109.     , @value = '文件最大大小'  
  110.     , @level0type = N'SCHEMA'  
  111.     , @level0name = N'dbo'  
  112.     , @level1type = N'TABLE'  
  113.     , @level1name = N'DiskCapacityHistory'  
  114.     , @level2type = N'COLUMN'  
  115.     , @level2name = N'MaxSize';  
  116.   
  117.   
  118. EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  119.     , @value = '文件增长类型'  
  120.     , @level0type = N'SCHEMA'  
  121.     , @level0name = N'dbo'  
  122.     , @level1type = N'TABLE'  
  123.     , @level1name = N'DiskCapacityHistory'  
  124.     , @level2type = N'COLUMN'  
  125.     , @level2name = N'GrowthType';  
  126.   
  127.   
  128. EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  129.     , @value = '是否只读类型'  
  130.     , @level0type = N'SCHEMA'  
  131.     , @level0name = N'dbo'  
  132.     , @level1type = N'TABLE'  
  133.     , @level1name = N'DiskCapacityHistory'  
  134.     , @level2type = N'COLUMN'  
  135.     , @level2name = N'IsReadOnly';  
  136.   
  137.   
  138. EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  139.     , @value = '是否按百分比增长'  
  140.     , @level0type = N'SCHEMA'  
  141.     , @level0name = N'dbo'  
  142.     , @level1type = N'TABLE'  
  143.     , @level1name = N'DiskCapacityHistory'  
  144.     , @level2type = N'COLUMN'  
  145.     , @level2name = N'IsPercentGrowth';  
  146.   
  147.   
  148. EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  149.     , @value = '数据文件大小(GB)'  
  150.     , @level0type = N'SCHEMA'  
  151.     , @level0name = N'dbo'  
  152.     , @level1type = N'TABLE'  
  153.     , @level1name = N'DiskCapacityHistory'  
  154.     , @level2type = N'COLUMN'  
  155.     , @level2name = N'Size';  
  156.   
  157.   
  158. EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  159.     , @value = '文件增长环比(%)'  
  160.     , @level0type = N'SCHEMA'  
  161.     , @level0name = N'dbo'  
  162.     , @level1type = N'TABLE'  
  163.     , @level1name = N'DiskCapacityHistory'  
  164.     , @level2type = N'COLUMN'  
  165.     , @level2name = N'Growth_MOM_RAT';  
  166.   
  167.   
  168. EXEC sys.sp_addextendedproperty @name = N'MS_Description'  
  169.     , @value = '文件增长同比(%)'  
  170.     , @level0type = N'SCHEMA'  
  171.     , @level0name = N'dbo'  
  172.     , @level1type = N'TABLE'  
  173.     , @level1name = N'DiskCapacityHistory'  
  174.     , @level2type = N'COLUMN'  
  175.     , @level2name = N'Growth_YOY_RAT';  
  176.   
  177.   
  178. GO  
  179.   
  180.   
  181. IF  OBJECT_ID(N'sp_diskcapacity_cal')  IS NOT NULL  
  182.     DROP PROCEDURE sp_diskcapacity_cal;  
  183. GO  

接下来，我们创建存储过程，负责来收集、统计这些数据库的文件的相关信息。关于环比/同比,正常情况一般是:
环比:  (指标当前值 - 指标值(上个月同一天))/ 指标值(上个月同一天) 。
同比:  (指标当前值 - 指标值(去年月同一天))/ 指标值(去年月同一天) 。
其实如果关注每天的数据文件变化情况，这个代码里面的环比、同比其实意义不大，其实我们可以这样定义环比、同比：
环比： （指标当前值 - 指标值（昨天）)/指标值（昨天）。
同比： （指标当前值 - 指标值 （上个月））/指标值（上个月）
当然，你也可以把这四个指标都加上，对比参考，侧重点不同而已。
 
  1. IF  OBJECT_ID(N'sp_diskcapacity_cal')IS NOT NULL  
  2.     DROP PROCEDURE sp_diskcapacity_cal;  
  3. GO  
  4.   
  5.   
  6. CREATE PROCEDURE dbo.sp_diskcapacity_cal  
  7. AS  
  8. BEGIN  
  9.   
  10.   
  11.    INSERT INTO dbo.DiskCapacityHistory  
  12.    (  
  13.         [Date_CD]           ,  
  14.         [DataBaseID]        ,  
  15.         [FileID]            ,  
  16.         [DataBaseName]      ,  
  17.         [LogicalName]       ,  
  18.         [FileTypeDesc]      ,  
  19.         [PhysicalName]      ,  
  20.         [StateDesc]         ,  
  21.         [MaxSize]           ,  
  22.         [GrowthType]        ,  
  23.         [IsReadOnly]        ,  
  24.         [IsPercentGrowth]   ,  
  25.         [Size]                 
  26.    )  
  27.      SELECT CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT)  
  28.                                                                             AS DateCD        ,  
  29.             database_id                                                     AS DataBaseId    ,  
  30.             file_id                                                         AS FileID        ,  
  31.             DB_NAME(database_id)                                            AS DataBaseName  ,  
  32.             name                                                            AS LogicalName   ,  
  33.             type_desc                                                       AS FileTypeDesc  ,  
  34.             physical_name                                                   AS PhysicalName  ,  
  35.             state_desc                                                      AS StateDesc     ,  
  36.             CASE WHEN max_size = 0 THEN N'不允许增长'  
  37.                  WHEN max_size = -1 THEN N'自动增长'  
  38.                  ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'G'  
  39.             END                                                             AS MaxSize       ,  
  40.             CASE WHEN is_percent_growth = 1  
  41.                  THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'  
  42.                  ELSE RTRIM(CAST(Growth AS CHAR(10))) + 'M'  
  43.             END                                                             AS Growth        ,  
  44.             Is_Read_Only AS IsReadOnly ,  
  45.             Is_Percent_Growth AS IsPercentGrowth ,  
  46.             CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4))                 AS Size  
  47.      FROM   sys.master_files;  
  48.   
  49.   
  50.       
  51.      MERGE INTO dbo.DiskCapacityHistory DM USING  
  52.      (  
  53.      SELECT M.Date_CD        ,  
  54.             M.DataBaseID     ,  
  55.             M.FileID         ,  
  56.             CASE WHEN N.SIZE IS NULL OR N.SIZE = 0 THEN 0 ELSE  
  57.                 (M.SIZE - N.SIZE)/N.SIZE END AS Growth_MOM_RAT  
  58.      FROM dbo.DiskCapacityHistory M  
  59.       LEFT JOIN dbo.DiskCapacityHistory  N ON  
  60.               CAST(CAST(M.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH, 1, CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))  
  61.           AND M.DataBaseID = N.DataBaseID AND M.FileID = N.FileID  
  62.      WHERE M.Date_CD =  CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT)  
  63.      ) TMP  
  64.      ON  
  65.      (  
  66.             DM.Date_CD       = TMP.Date_CD     AND  
  67.             DM.DatabaseId    = TMP.DataBaseId  AND  
  68.             DM.FileId        = TMP.FileId  
  69.      )  
  70.      WHEN MATCHED THEN UPDATE SET  
  71.         DM.Growth_MOM_RAT = TMP.Growth_MOM_RAT;  
  72. END     
  73. 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语句功能，上面的脚本得改写成
 
  1. UPDATEdbo.DiskCapacityHistory  
  2.  SET     GROWTH_MOM_RAT =( SELECTCASE WHEN N.SIZE IS NULL  
  3.                                             OR N.SIZE = 0 THEN 0  
  4.                                        ELSE ( dbo.DiskCapacityHistory.SIZE  
  5.                                               - N.SIZE ) / N.SIZE  
  6.                                   END AS Growth_MOM_RAT  
  7.                          FROM     dbo.DiskCapacityHistory N  
  8.                          WHERE    CAST(CAST(dbo.DiskCapacityHistory.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH,  
  9.                                                             1,  
  10.                                                             CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))  
  11.                                   AND dbo.DiskCapacityHistory.DataBaseID = N.DataBaseID  
  12.                                   AND dbo.DiskCapacityHistory.FileID = N.FileID  
  13.                        )  
  14.  WHEREdbo.DiskCapacityHistory.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120),  
  15.                                                      '-', '') AS INT)  
  16.   
  17.   
  18. UPDATEdbo.DiskCapacityHistory  
  19.  SET     GROWTH_YOY_RAT =( SELECTCASE WHEN N.SIZE IS NULL  
  20.                                             OR N.SIZE = 0 THEN 0  
  21.                                        ELSE ( dbo.DiskCapacityHistory.SIZE  
  22.                                               - N.SIZE ) / N.SIZE  
  23.                                   END AS Growth_YOY_RAT  
  24.                          FROM     dbo.DiskCapacityHistory N  
  25.                          WHERE    CAST(CAST(dbo.DiskCapacityHistory.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH,  
  26.                                                             12,  
  27.                                                             CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))  
  28.                                   AND dbo.DiskCapacityHistory.DataBaseID = N.DataBaseID  
  29.                                   AND dbo.DiskCapacityHistory.FileID = N.FileID  
  30.                        )  
  31.  WHEREdbo.DiskCapacityHistory.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120),  
  32.                                                      '-', '') AS INT)  


或

 
  1. CREATE TABLE #DiskCapacityHistory  
  2.     (  
  3.       DATE_CD INT ,  
  4.       DataBaseID INT ,  
  5.       FileID INT ,  
  6.       Growth_MOM_RAT FLOAT  
  7.     ) ;  
  8.   
  9.   
  10.   INSERTINTO #DiskCapacityHistory  
  11.         SELECT  M.DATE_CD ,  
  12.                 M.DataBaseID ,  
  13.                 M.FileID ,  
  14.                 CASE WHEN N.SIZE IS NULL  
  15.                           OR N.SIZE = 0 THEN 0  
  16.                      ELSE ( M.SIZE - N.SIZE ) / N.SIZE  
  17.                 END AS Growth_MOM_RAT  
  18.         FROM    dbo.DiskCapacityHistory M ,  
  19.                 dbo.DiskCapacityHistory N  
  20.         WHERE   CAST(CAST(M.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH, 1,  
  21.                                                               CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))  
  22.                 AND M.DataBaseID = N.DataBaseID  
  23.                 AND M.FileID = N.FileID  
  24.                 AND M.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE()  
  25.                                              - 1, 120), '-', '') AS INT)  
  26.   
  27.   
  28.   UPDATE dbo.DiskCapacityHistory  
  29.      SET Growth_MOM_RAT = M.Growth_MOM_RAT  
  30.     FROM #DiskCapacityHistory M  
  31.    WHERE dbo.DiskCapacityHistory.DATE_CD = M.DATE_CD  
  32.         AND dbo.DiskCapacityHistory.DataBaseID = M.DataBaseID  
  33.         AND dbo.DiskCapacityHistory.FileID = M.FileID ;  

2: 幸好SQL 2008还把ORACLE的MERGE的功能给模仿了过来，但是T-SQL缺少ORACLE数据库强大的分析函数LAG，如果有这个，我计算环比，同比就非常方便了，一个SQL就搞定了，下面是个例子，本想把ORACLE的SQL也做个例子展现，但是又要建表、造数，折腾起来比较麻烦。

 
  1. MERGE INTO DM.TM_WGGBO_IDCTOBUSVOLDTL_DAY DM  
  2. USING    (  
  3.              SELECT *  
  4.                FROM (  
  5.                        SELECT    DATE_CD,  
  6.                                  CITY_ID,  
  7.                                  IDC_NODE,  
  8.                                  VOL_TYPE,  
  9.                                  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                ,  
  10.                                  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                 ,  
  11.   
  12.   
  13.                          FROM DM.TM_WGGBO_IDCTOBUSVOLDTL_DAY  
  14.                        ) T  
  15.                  WHERE EXISTS(SELECT 1 FROM ETL.T_IDCVOL_DAY_${ssid} WHERE DATE_CD = T.DATE_CD)  
  16.           ) TEMP  
  17.                         ON (  
  18.                                 DM.DATE_CD     = TEMP.DATE_CD     AND  
  19.                                 DM.CITY_ID     = TEMP.CITY_ID     AND  
  20.                                 DM.IDC_NODE    = TEMP.IDC_NODE    AND  
  21.                                 DM.VOL_TYPE    = TEMP.VOL_TYPE  
  22.                                 )  
  23. WHEN MATCHED THEN  
  24.   UPDATE  
  25.        SET DM.IDC_MOM_RAT    =       TEMP.IDC_MOM_RAT                    ,  
  26.            DM.IDC_YOY_RAT    =       TEMP.IDC_YOY_RAT                      
  27.   
  28.   
  29. COMMIT;  
(责任编辑：IT)
    最近关注
    热点内容