Sqlserver推薦參數配置及日誌收縮問題

標籤：

      最近不定期有項目反饋周期性的系統整體效能下降情況，經分析存在因資料庫環境、參數配置不佳造成的。比如，sqlserver記錄檔預設按百分比增長，當記錄檔已經比較大時，每次擴充時耗時較長，系統整體卡頓；另外，如果沒有專門做記錄備份，收縮日誌和資料庫時不會顯著的降低日誌大小，造成每次完整備份很大、備份時間很長，等等。 推薦配置

簡單整理一些比較基礎、通用的配置如下：

1. 建議的sqlserver版本(x64)：sqlserver 2008 及更高版本（最明顯的應該是參數嗅探特性）

2. 最小記憶體和最大記憶體統一設定為實體記憶體的80%

3. 資料和記錄檔的初始大小分別設定為10G和2G，均設定為按照固定200M大小增長，不限制最大值；

4. Tempdb資料庫的復原模式設定為簡單，資料和記錄檔的初始大小分別設定為2G和1G，均設定為按照固定200M大小增長，不限制最大值；

5. Tempdb的資料檔案個數 = 資料庫伺服器的CPU數，所有資料檔案的初始大小和增量必須一致，資料檔案個數不要超過4個；

6. 最大並行度設定為1，或並行的開銷閥值設定為100（酌情設定）

7. 資料庫的完整備份後，應該再做一個記錄備份，然後再做日誌收縮。

 

日誌收縮

正常情況下，完成完整備份後，應該執行記錄備份，然後再做記錄檔的收縮。只有做記錄備份後記錄才會被截斷，僅做完整備份或差異備份，做日誌收縮是沒有效果的。

操作步驟如下：

USE [master]GOBACKUP DATABASE [DbName] TO DISK=‘xxx‘GOBACKUP LOG [DbName] TO DISK=‘xxx‘GOUSE [DbName]GO-- 確定資料庫記錄檔的邏輯名稱，收縮記錄檔DECLARE @logName NVARCHAR(100);SELECT @logName = name FROM sys.database_files WHERE type_desc = ‘LOG‘;DBCC SHRINKFILE (@logName, 1024);GO

 

如果不備份日誌，直接截斷日誌（不推薦使用），有以下兩種變通方式：

1. 將日誌寫入nul虛擬檔案（對 SQL Server而言，nul 與其他真實存在的檔案一樣， SQL SERVER會掃描所有活動紀錄，將該日誌格式化後寫入 nul檔案）

2. 將資料庫改為簡單復原模式後又改為完整復原模式

    SQL2005 的WITH TRUNCATE_ONLY選項，起到相同的效果。運行在簡單復原模式下，所有活動紀錄在 checkpoint後會被丟棄；

-- 備份資料庫日誌到nul虛擬檔案BACKUP LOG [DbName] TO DISK=‘nul‘-- 備份資料庫日誌，截斷日誌（sqlserver2005支援）BACKUP LOG [DbName] WITH TRUNCATE_ONLY// 2008以後

-- 將資料庫復原模式改為簡單（即截斷日誌），然後再恢複為完整模式USE [master]GOALTER DATABASE [DbName] SET RECOVERY SIMPLE WITH NO_WAITGOALTER DATABASE [DbName] SET RECOVERY SIMPLE --簡單模式GOUSE [DbName]GO-- 確定資料庫記錄檔的邏輯名稱DBCC SHRINKFILE (N‘DbName_log‘ , 1024)GOUSE [master]GOALTER DATABASE [DbName] SET RECOVERY FULL WITH NO_WAITGOALTER DATABASE [DbName] SET RECOVERY FULL --還原為完全模式GO

Sqlserver推薦參數配置及日誌收縮問題