適用於大小為 867666.25 MB 的數據庫的推薦自動增長設置

我需要為我們的生產數據庫設置/修改自動增長選項的建議。

FUNDSDB 大小 867666.25 MB

另外，我附上了目前的自動增長統計資訊，看起來它設置為 200 MB，考慮到數據庫大小，我認為這要小得多，而且它應該修改為一些實際值，否則這些自動增長事件可能會導致我們的性能下降目前數據庫事務。

任何人都可以建議最適合這個重型 OLTP 生產數據庫的自動增長設置嗎？我們目前使用的是 SQL Server 2012 標準 RTM 版本。我們計劃更改自動增長因子的主要原因是我們觀察到，我們每天頻繁地經歷多達 11 次的自動增長，並且頻繁出現 CPU 峰值。

您可能需要考慮分析過去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 Technet）

或者使用以下腳本：

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

參考： 確定 SQL Server 數據庫增長率(mssqltips.com)

根據這些腳本提供的資訊，您可以將數據庫的增長設置設置為接近GrowthMB（Microsoft Technet 腳本）值或接近Max Size Diff From Previous (MB)值（Mssqltips.com 腳本）。

如果您插入到未來，您可以將數據庫文件的最大大小(*mdf) 設置為返回值的倍數（12 個月的價值？）加上目前數據庫大小。

引用自：https://dba.stackexchange.com/questions/206736