• MS SQL 监控数据/日志文件增长

        前几天,在所有数据库服务器部署了监控磁盘空间的存储过程和作业后（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)     
);
    View Code 
    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

    接下来，我们创建存储过程，负责来收集、统计这些数据库的文件的相关信息。关于环比/同比,正常情况一般是:

    环比:  (指标当前值 - 指标值(上个月同一天))/ 指标值(上个月同一天) 。

    同比:  (指标当前值 - 指标值(去年月同一天))/ 指标值(去年月同一天) 。

    其实如果关注每天的数据文件变化情况，这个代码里面的环比、同比其实意义不大，其实我们可以这样定义环比、同比：

    环比： （指标当前值 - 指标值（昨天）)/指标值（昨天）。

    同比： （指标当前值 - 指标值 （上个月））/指标值（上个月）

    当然，你也可以把这四个指标都加上，对比参考，侧重点不同而已。

    存储过程
    1.   IF  OBJECT_ID(N'sp_diskcapacity_cal')IS NOT NULL
    2.     DROP PROCEDURE sp_diskcapacity_cal;
    3. GO
    4.  
    5. CREATE PROCEDURE dbo.sp_diskcapacity_cal
    6. AS
    7. BEGIN
    8.    
    9.    INSERT INTO dbo.DiskCapacityHistory
    10.    (
    11.         [Date_CD]           ,
    12.         [DataBaseID]        ,
    13.         [FileID]            ,
    14.         [DataBaseName]      ,
    15.         [LogicalName]       ,
    16.         [FileTypeDesc]      ,
    17.         [PhysicalName]      ,
    18.         [StateDesc]         ,
    19.         [MaxSize]           ,
    20.         [GrowthType]        ,
    21.         [IsReadOnly]        ,
    22.         [IsPercentGrowth]   ,
    23.         [Size]                
    24.    )
    25.      SELECT CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT)
    26.                                                                             AS DateCD        ,
    27.             database_id                                                     AS DataBaseId    ,
    28.             file_id                                                         AS FileID        ,
    29.             DB_NAME(database_id)                                            AS DataBaseName  ,
    30.             name                                                            AS LogicalName   ,
    31.             type_desc                                                       AS FileTypeDesc  ,
    32.             physical_name                                                   AS PhysicalName  ,
    33.             state_desc                                                      AS StateDesc     ,
    34.             CASE WHEN max_size = 0 THEN N'不允许增长'
    35.                  WHEN max_size = -1 THEN N'自动增长'
    36.                  ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'G'
    37.             END                                                             AS MaxSize       ,
    38.             CASE WHEN is_percent_growth = 1
    39.                  THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'
    40.                  ELSE RTRIM(CAST(Growth AS CHAR(10))) + 'M'
    41.             END                                                             AS Growth        ,
    42.             Is_Read_Only AS IsReadOnly ,
    43.             Is_Percent_Growth AS IsPercentGrowth ,
    44.             CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4))                 AS Size
    45.      FROM   sys.master_files;
    46.      
    47.      
    48.      MERGE INTO dbo.DiskCapacityHistory DM USING
    49.      (
    50.      SELECT M.Date_CD        ,
    51.             M.DataBaseID     ,
    52.             M.FileID         ,
    53.             CASE WHEN N.SIZE IS NULL OR N.SIZE = 0 THEN 0 ELSE
    54.                 (M.SIZE - N.SIZE)/N.SIZE END AS Growth_MOM_RAT
    55.      FROM dbo.DiskCapacityHistory M
    56.       LEFT JOIN dbo.DiskCapacityHistory  N ON
    57.               CAST(CAST(M.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH, 1, CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
    58.           AND M.DataBaseID = N.DataBaseID AND M.FileID = N.FileID
    59.      WHERE M.Date_CD =  CAST(REPLACE(CONVERT(varchar(10),GETDATE(),120),'-','') AS INT)
    60.      ) TMP
    61.      ON
    62.      (
    63.             DM.Date_CD       = TMP.Date_CD     AND
    64.             DM.DatabaseId    = TMP.DataBaseId  AND
    65.             DM.FileId        = TMP.FileId
    66.      )
    67.      WHEN MATCHED THEN UPDATE SET
    68.         DM.Growth_MOM_RAT = TMP.Growth_MOM_RAT;
    69. END    
    70. 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语句功能，上面的脚本得改写成
     
    Code Snippet
    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. UPDATEdbo.DiskCapacityHistory
    18.  SET     GROWTH_YOY_RAT =( SELECTCASE WHEN N.SIZE IS NULL
    19.                                             OR N.SIZE = 0 THEN 0
    20.                                        ELSE ( dbo.DiskCapacityHistory.SIZE
    21.                                               - N.SIZE ) / N.SIZE
    22.                                   END AS Growth_YOY_RAT
    23.                          FROM     dbo.DiskCapacityHistory N
    24.                          WHERE    CAST(CAST(dbo.DiskCapacityHistory.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH,
    25.                                                             12,
    26.                                                             CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
    27.                                   AND dbo.DiskCapacityHistory.DataBaseID = N.DataBaseID
    28.                                   AND dbo.DiskCapacityHistory.FileID = N.FileID
    29.                        )
    30.  WHEREdbo.DiskCapacityHistory.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120),
    31.                                                      '-', '') AS INT)
    或
    Code Snippet
    1. CREATE TABLE #DiskCapacityHistory
    2.     (
    3.       DATE_CD INT ,
    4.       DataBaseID INT ,
    5.       FileID INT ,
    6.       Growth_MOM_RAT FLOAT
    7.     ) ;
    8.  
    9.   INSERTINTO #DiskCapacityHistory
    10.         SELECT  M.DATE_CD ,
    11.                 M.DataBaseID ,
    12.                 M.FileID ,
    13.                 CASE WHEN N.SIZE IS NULL
    14.                           OR N.SIZE = 0 THEN 0
    15.                      ELSE ( M.SIZE - N.SIZE ) / N.SIZE
    16.                 END AS Growth_MOM_RAT
    17.         FROM    dbo.DiskCapacityHistory M ,
    18.                 dbo.DiskCapacityHistory N
    19.         WHERE   CAST(CAST(M.Date_CD AS CHAR(8)) AS DATE) = DATEADD(MONTH, 1,
    20.                                                               CAST(CAST(N.Date_CD AS CHAR(8)) AS DATE))
    21.                 AND M.DataBaseID = N.DataBaseID
    22.                 AND M.FileID = N.FileID
    23.                 AND M.Date_CD = CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE()
    24.                                              - 1, 120), '-', '') AS INT)
    25.  
    26.   UPDATE dbo.DiskCapacityHistory
    27.      SET Growth_MOM_RAT = M.Growth_MOM_RAT
    28.     FROM #DiskCapacityHistory M
    29.    WHERE dbo.DiskCapacityHistory.DATE_CD = M.DATE_CD
    30.         AND dbo.DiskCapacityHistory.DataBaseID = M.DataBaseID
    31.         AND dbo.DiskCapacityHistory.FileID = M.FileID ;

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

    Oracle Sample
    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.                          FROM DM.TM_WGGBO_IDCTOBUSVOLDTL_DAY
    13.                        ) T
    14.                  WHERE EXISTS(SELECT 1 FROM ETL.T_IDCVOL_DAY_${ssid} WHERE DATE_CD = T.DATE_CD)
    15.           ) TEMP
    16.                         ON (
    17.                                 DM.DATE_CD     = TEMP.DATE_CD     AND
    18.                                 DM.CITY_ID     = TEMP.CITY_ID     AND
    19.                                 DM.IDC_NODE    = TEMP.IDC_NODE    AND
    20.                                 DM.VOL_TYPE    = TEMP.VOL_TYPE
    21.                                 )
    22. WHEN MATCHED THEN
    23.   UPDATE
    24.        SET DM.IDC_MOM_RAT    =       TEMP.IDC_MOM_RAT                    ,
    25.            DM.IDC_YOY_RAT    =       TEMP.IDC_YOY_RAT                     
    26. ;
    27. COMMIT;
    扫描上面二维码关注我
    如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！
    本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
  • 相关阅读:
    读<<关于程序员的技能>> 惭愧,希望有人能进来解惑! 人工智能
    我也来说说多核 人工智能
    有感于.net社区挺唐骏的作假的文章，诚实是美德，你们可以做假发财成功，但请不要批评，嘲笑他人诚实贫穷 人工智能
    几个程序员也难已回答的问题 人工智能
    Flash已死，有事烧纸！ 人工智能
    关于Windows7的用户体验,我们是属于跟不上时代的古董,还是那个敢说出皇帝新衣的男孩? 人工智能
    企鹅帝国的疯狂反扑！ 人工智能
    微软IE9预览版全面(100%)支持HTML5和CSS3，到学习使用Html５的时候了 人工智能
    Flash已死终成现实，Adobe亲手埋葬Flash，并公开支持Html５ 人工智能
    4个月，你就是企业需要的程序员人才，听蒋涛(CSDN)在线说“黑马” 人工智能
  • 原文地址：https://www.cnblogs.com/kerrycode/p/3071443.html
Copyright © 2020-2023  润新知