MVA學習筆記-SQL Server 管理入門 (四)：自動化、維護與監控

MVA連結

SQL SERVER AGENT

• 負責自動化的SQL SERVER元件
• 以WINDOWS服務的方式執行
• 支援T-SQL、POWERSHEEL、作業系統指令或執行應用程式
• 週期性的執行
• 可將作業直接產生指令碼或文件來方便移轉或保存
• 在AGENT的屬性來設定歷程記錄的保留方式，避免留存資料量過大
• AGENT的設定和歷程保留在msdb

警示與通知

透過DATABASE MAIL實作SMTP，讓DB可以發送EMAIL
AGENT會觀察WINDWOS裡有多少SQLSERVER錯誤訊息
嚴重層級17以上就真的嚴重了
自訂錯誤訊息從50000後開始，要搭配WITHLOG讓記錄寫入WINDOWS錯誤訊息

定義警示

DATABASE MAIL

可設定多個帳號以增加可靠度

管理\DATABASEMAIL利用UI來設定

新增操作員來接受錯誤訊息

資料庫維護

• 確定資料庫的完整性
• DBCC CHECKDB('DBNAME') 
• 只毀索引PAGE的話，如果DBCC CHECKDB是NOINDEX的話是無錯的
• SELECT TABLE的話也會是正常，但如果是WITH INDEX的話也會異常。

索引片段

• SQL SERVER重組索引資料頁當修改資料造成資料頁分割

內部不連續

• FILLFACTOR(僅有子頁層)
• PAD_INDEX(包含根與中介層)

維護索引

REBUILD

• 重建整個索引
• 資料庫需要未使用的空間
• 以單一交易執行，可能需要大量的交易記錄空間

REORGANIZE

• 線上重新排序資料頁
• 交易記錄使用量較少
• 可以被中斷，但仍保有執行到當時的工作成果

線上索引作業

• 可以線上建立、重建和刪除
• 較離線慢，但需許使用者讀取資料
• 需要額外的硬碟空間
• 查詢資料片段語法
• SELECT * FROM sys.dm_db_index_physical_stats(db_id('dbname'),object_id('t'),null,null,'DETAILED')
• 在TABLE右鍵屬性查片段狀況也可以
• 可以參考保哥的文章

追蹤與監控工作

動態管理檢視與函數概觀

• sys.dm_exec_%-連接和執行
• sys.dm_os_%-SQLOS相關資訊
• sys.dm_tran_%-交易管理
• sys.dm_io_%-IO相關資訊
• sys.dm_db_%-資料庫範圍的資訊
• 使用時必須指定sys結構描述，分別為當下狀態資訊與累積的歷史資訊
• 累積的部份在db重開機之後就會歸零

使用效能監視器

• 提供即時監控健康與效能的計數器(perfmon)
• 監控重點：

• CPU

• Memory

• Disk System

• Network

• SQL Server Counters

資料收集概觀

• 收集多台機器的內容並保留在一個DB上面
• 利用SSIS和SQL AGENT作業傳送資料到集中的資料庫
• 用SSMS來存取報表

設定資料收集

• 在一群的SQL SERVER主機中選一台來當資料收集主機，其它主機則個別設定好上傳資料的頻率以及要上傳的資料與保留期限。
• 管理\資料收集\設定管理資料倉儲
• 新增一個資料庫用來管理收集資料並授權
• 在各機器上面都要設定資料收集，並連到資料倉儲主機