SQLServer日常維護事項

1.      目的

1.1.1.  監控資料庫的當前健全狀態，確保資料庫穩定運行

1.1.2.  監控資料庫的備份/HA狀況，減少問題發生時的風險和責任

1.1.3.  監控資料庫的整體健全狀態，對資料庫的效能進行調整，確保資料庫高效運行

1.1.4.  儘可能減少緊急故障發生頻率

1.1.5.  儘早發現系統存在的潛在問題，使可能的故障消除在萌芽狀態

2.      作業系統環境監控

2.1.       檢查系統日誌

2.1.1.  使用“事件檢視器”，查看系統日誌，關注錯誤資訊排查錯誤。

2.1.2.  重點關注與SQLServer相關的日誌資訊。

2.1.3.  及時備份與清理到期的日誌資訊。

2.2.       檢查磁碟空間

2.2.1.  檢查資料檔案所在的磁碟空間使用方式

2.2.2.  檢查記錄檔所在的磁碟空間使用方式

2.2.3.  檢查備份的磁碟空間使用方式

3.      資料庫效能監控

3.1.       記憶體

3.1.1.  dbcc memorystatus

 

dbcc memorystatus   /*   重點查看buffer counts部分   其中committed memory和target memory最重要。   Committed memory表示sql server已經得到的記憶體數量   target memory表示有效運行所需的記憶體數量。   當兩個存在差別過大，說明可能存在記憶體壓力   */

3.1.2.  SQLServer:Buffer Managerà       Page Life Expectancy顯示資料頁在緩衝池中駐留的時間長度（單位是秒）。值越大，系統越健康。如果存在記憶體瓶頸，這個值會低於300s或者更少。

3.1.3.  SQLServer:Buffer Managerà       Buffer cache hit ratio顯示資料庫記憶體命中率，所請求的資料或者說頁面在緩衝池被找到的次數，如果很低說明記憶體不足，此值一般大於98%

3.1.4.  SQLServer:Buffer Managerà       Stolen pages當這個指標與目標記憶體頁面數比例較大時可能存在問題。

3.1.5.  SQLServer:Buffer Managerà       Memory Grants Pending顯示等待記憶體授權的進程隊列。這個指標值為0時理想狀態。

3.1.6.  SQLServer:Buffer Managerà       Checkpoint pages/sec顯示檢查點操作每秒寫入磁碟的髒頁數目。如果這個值很高，說明缺少記憶體。

3.1.7.  SQLServer:Buffer Managerà       Lazy writes/sec顯示每秒將髒頁從記憶體寫到磁碟的次數。這個值應該儘可能接近0，當大於20或者更多，確信緩衝池不夠。

3.2.       磁碟

3.2.1.  PhysicalDiskàAVG.Disk Aueue Length顯示每一個磁碟的隊列長度。不要選擇_TOTAL對象，而是查看某驅動盤符的隊列長度，如果參數值持續大於2，則可能影響效能。

3.2.2.  PhysicalDiskAVG.Disk sec/Read顯示每次讀取的平均磁碟時間，理想情況下值小於10毫秒。

3.2.3.  PhysicalDiskAVG.Disk sec/Write顯示每次寫入的平均磁碟時間，理想情況下值小於10毫秒。

3.2.4.  SQL ServerAccess MethodsFullscans/sec顯示每秒請求完全索引掃描或者全表掃描的數目，如果掃描頻率每秒大於1，那麼說明索引缺少或者索引比較差。

3.2.5.  SQL ServerAccess MethodsPageSplits/sec顯示每秒頁面拆分的次數。可以通過適當的索引維護或者好的填滿因數來避免發生。

3.3.       CPU

3.3.1.  SystemProcessor Queue Length顯示系統隊列長度，如果平均值大於3，那麼說明cpu存在瓶頸

3.3.2.  Processorà%Privilege Time顯示作業系統內部操作所花費的時間。

3.3.3.  SQL ServeràSQL StatisticsàSQL Compilations/sec顯示查詢計劃的編譯次數。

3.3.4.  SQL ServeràSQL StatisticsàSQL Re-Compilations/sec顯示查詢計劃的重編譯次數。

3.3.5.  當SQL Compilations/sec和SQL Re-Compilations/sec次數過高的話，說明可能未使用綁定變數導致計劃重新編譯，或者說重新編譯次數比編譯次數過高的話，那麼說明存在應用上的瓶頸。

4.      資料庫維護

4.1.       主要資料庫（業務資料庫）

4.1.1.  查看資料庫的增長情況，每周增長量應該處平穩狀態，如果出現異常增長，則需要注意查看原因。

4.1.2.  注意資料檔案所在磁碟的空間，保證至少有一周的增長量空間。

4.1.3.  查看每個記錄檔的增長情況，每周增長量應該處平穩狀態，如果出現異常增長，則需要注意查看原因

4.1.4.  注意記錄檔所在磁碟的空間，保證至少有一周的增長量空間。

4.1.5.  記錄檔超大後，可考慮是否進行清理,如果清理使用備份日誌然後收縮記錄檔。

4.2.       鏡像資料庫

4.2.1.  除日誌清除外步驟同主要資料庫

4.2.2.  鏡像資料庫記錄檔超大後，可以在主要資料庫上進行日誌清理操作。

4.3.       次要資料庫(Log Shipping)

4.3.1.  除日誌清除外步驟同主要資料庫

4.3.2.  備用資料庫記錄檔超大後，可以在主要資料庫上進行日誌清理操作。

4.4.       查看作業健全狀態

可以通過以下指令碼查看作業狀況或者通過圖形介面“作業活動監視器”查看

 

--0:Fail 1:Succ 3:Cancel 5:First run and msdb.dbo.syscategories.category_id not between 10 and 20   select        category = jc.name, category_id = jc.category_id, job_name = j.name,        job_enabled = j.enabled,        last_run_time = cast(js.last_run_date as varchar(10)) + '-' + cast(js.last_run_time as varchar(10)),        last_run_duration = js.last_run_duration,        last_run_status = js.last_run_outcome,        last_run_msg = js.last_outcome_message + cast(nullif(js.last_run_outcome,1) as varchar(2)),        job_created = j.date_created,        job_modified = j.date_modified    from msdb.dbo.sysjobs j    inner join msdb.dbo.sysjobservers js on j.job_id = js.job_id    inner join msdb.dbo.syscategories jc on j.category_id = jc.category_id    where j.enabled = 1 and js.last_run_outcome in (0,1,3,5)

4.5.       查看鏡像狀態

可以通過以下指令碼查看鏡像狀態或者通過“鏡像監視器”查看

 

select        dm.mirroring_partner_instance,   dm.mirroring_role,   dm.mirroring_state,   dm.mirroring_witness_state,       dm.mirroring_safety_level,   dm.mirroring_witness_name    from   sys.database_mirroring dm   join sys.databases d on (dm.database_id=d.database_id)   where (d.name=N'dbname') and mirroring_guid is not null

4.6.       查看Log Shipping狀態

可以通過以下指令碼在監視伺服器上執行查看記錄傳送狀態

 

use master   go   exec sp_help_log_shipping_monitor   go   exec sp_help_log_shipping_monitor_primary 'TestServer1','Northwind'   go   exec sp_help_log_shipping_monitor_secondary 'TestServer2','Northwind'   go

4.7.       查看備份狀況

4.7.1.  查看備份是否成功

4.7.2.  驗證備份組(RESTORE VERIFYONLY )

4.7.3.  定期做資料庫災難恢複的演練(其中包括備份還原，HA)