推荐的自动增长设置适合于大小为867666.25 MB的数据库

Question

我需要为我们的生产数据库设置/修改自动增长选项的建议。

消防处867666.25 MB大小

此外，我还附加了当前的自动增长统计数据，看起来它被设置为200 MB，考虑到数据库的大小，我认为它要少得多，而且它应该修改为一些实际的值，否则这些自动增长事件会导致我们当前的db事务的性能下降。

有人能推荐适合这个大型OLTP生产数据库的自动增长设置吗？我们目前正在使用Server 2012标准RTM版本。我们计划改变自动增长因子的主要原因是，我们观察到，我们每天会频繁地经历11次自动增长，还有很多频繁的CPU峰值。

Answer 1

您可能需要考虑分析数据库在过去n天中的增长情况。这可以通过分析msdb数据库中的备份信息来实现。以下脚本是如何实现这一目标的两个变体：

-- Transact-SQL script to analyse the database size growth using backup history. 
DECLARE @endDate DATETIME,
        @months SMALLINT; 
SET @endDate = GETDATE();  -- Include in the statistic all backups from today 
SET @months = 6;           -- back to the last 6 months. 
;
WITH HIST AS 
     (
         SELECT BS.database_name AS DatabaseName,
                YEAR(BS.backup_start_date) * 100 
                + MONTH(BS.backup_start_date) AS YearMonth,
                CONVERT(NUMERIC(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB,
                CONVERT(NUMERIC(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB,
                CONVERT(NUMERIC(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
         FROM   msdb.dbo.backupset AS BS
                INNER JOIN msdb.dbo.backupfile AS BF
                     ON  BS.backup_set_id = BF.backup_set_id
         WHERE  NOT BS.database_name IN ('master', 'msdb', 'model', 'tempdb')
                AND BF.file_type = 'D'
                AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
         GROUP BY
                BS.database_name,
                YEAR(BS.backup_start_date),
                MONTH(BS.backup_start_date)
     )

SELECT MAIN.DatabaseName,
       MAIN.YearMonth,
       MAIN.MinSizeMB,
       MAIN.MaxSizeMB,
       MAIN.AvgSizeMB,
       MAIN.AvgSizeMB 
       -(
           SELECT TOP 1 SUB.AvgSizeMB
           FROM   HIST AS SUB
           WHERE  SUB.DatabaseName = MAIN.DatabaseName
                  AND SUB.YearMonth < MAIN.YearMonth
           ORDER BY
                  SUB.YearMonth DESC
       ) AS GrowthMB
FROM   HIST AS MAIN
ORDER BY
       MAIN.DatabaseName,
       MAIN.YearMonth

参考资料：数据库大小增长为列表 (Microsoft )

或者使用以下脚本：

SELECT DISTINCT
    A.[database_name]
,   AVG( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Avg Size Diff From Previous (MB)]
,   MAX( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Max Size Diff From Previous (MB)]
,   MIN( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Min Size Diff From Previous (MB)]
,   A.[Sample Size]
FROM 
(
    SELECT
        s.[database_name]
    --, s.[backup_start_date]
    ,   COUNT(*) OVER ( PARTITION BY s.[database_name] ) AS [Sample Size]
    ,   CAST ( ( s.[backup_size] / 1024 / 1024 ) AS INT ) AS [Backup Size (MB)]
    ,   CAST ( ( LAG(s.[backup_size] ) 
            OVER ( PARTITION BY s.[database_name] ORDER BY s.[backup_start_date] ) / 1024 / 1024 ) AS INT ) AS [Previous Backup Size (MB)]
    FROM 
        [msdb]..[backupset] s
    WHERE
        s.[type] = 'D' --full backup
    --ORDER BY
    --  s.[database_name]
    --, s.[backup_start_date]
) AS A
ORDER BY
    [Avg Size Diff From Previous (MB)] DESC;
GO

参考资料：确定Server数据库增长率 (mssqltips.com)

根据这些脚本提供的信息，您可以将数据库的增长设置设置为接近GrowthMB (Microsoft )值或接近Max Size Diff From Previous (MB)值(Mssqltips.com脚本)。

如果插入到将来，可以将数据库文件(*mdf)的最大大小设置为返回值的倍数(12个月值？)加上当前的DB大小。

Answer 2

将其设置为1024 it。如果这种情况仍然发生得太频繁，那么4096 If。在你衡量你的实际增长需求之前，这是一种猜测。

或者，手动将其增长到950 1TB，甚至是1TB，让Autogrow不是一个因素，这是您应该做的事情。

即时文件初始化打开了吗？您没有提到Server的版本。

并将日志文件从%更改为MB。

为了奖金--请把它们放到单独的驱动器上。

Kevin3NF