TempDB最佳化03

為了減少TempDB的IO，容量設定是一個關鍵因素，設定重點為：

1.      檔案初始大小：如果TempDB檔案一開始設定2MB，如果同時間許多使用者存取資料庫時會產生10MB暫存的資料，此時檔案因為太小就會自動成長導致產生IO降低效能。

2.      檔案成長量：如果TempDB檔案一開始設定2MB，假設檔案會增加到10MB，此時如果成長量設定一次成長1MB，與設定一次增加2MB比起來IO多了許多。

為了掌握正確的設定適當的初始大小與成長量資訊，必須透過監控TempDB的空間容量變化。至於如何監控TempDB檔案請參考【Day2_監控TempDB的空間容量】。

最後TempDB有二個(含)資料檔案以上時，每個檔案的大小與成長量都要設定一致，原因是SQL SERVER會依資料檔案大小按比例分散IO。

以下是實作設定檔案容量與成長量：

--查詢TempDB檔案的大小

SELECT    

name AS FileName, size*1.0/128 AS FileSizeinMB,

CASE max_size  WHEN 0 THEN 'Autogrowth is off.'

         WHEN -1 THEN 'Autogrowth is on.'

               ELSE 'Log file will grow to a maximum size of 2 TB.'        END Autogrowth ,

growth AS GrowthValue,

CASE WHEN growth = 0 THEN 'Size is fixed and will not grow.'

     WHEN growth > 0 AND is_percent_growth = 0  THEN 'Growth value is in 8-KB pages.'

        ELSE 'Growth value is a percentage.' END GrowthIncrement

FROM tempdb.sys.database_files;

查詢結果：

--修改檔案容量與成長量

USE [master]

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB, FILEGROWTH = 10%)

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev2', SIZE = 102400KB, FILEGROWTH = 10%)

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILEGROWTH = 10%)

GO

查詢結果：

參考網址