[SQL][效能調校]設定管理資料倉儲收集 SQL Server 運作狀況

[SQL][效能調校]設定管理資料倉儲收集 SQL Server 運作狀況

早期在 SQL Server 2005 和之前的版本,為了要查看 SQL Server 運作的狀況,多半都是使用 SQL Trace 和效能監視器,一來設定較為複雜一點,二來這兩個要合併在一起查看也不是那麼方便,所幸在 SQL Server 2008 開始,除了 SQL Server Express 版本沒有辦法使用,其他版本都可以 SQL Server 可以設定「管理資料倉儲」,配合定時的資料收集,就可以搭配報表來查看 SQL Server 運作的狀況了。

 

在我所遇到的不少資料庫效能案例當中,有透過這樣的方式可以查到一些異常 Lock 的時間和指令,也有因此查到造成主機異常緩慢的原因,算是一個非常好上手的檢測工具。因此個人所要管理的資料庫主機上,大部分都被都會啟用資料收集,這樣一有狀況的時候就可以取得一些當下的運作狀況,讓問題處理可以更加容易。

 

基本上 SQL Server 2008 到 2014 的設定都大同小異,只有 SQL Server 2014 上多了一點功能,因此下面就用 SQL Server 2014 來當作安裝範例。


設定管理資料倉儲

首先我們在 SSMS 上面,在所要設定主機的節點下,選擇「管理」→「資料收集」,按下滑鼠右鍵選擇「工作」→「設定管理資料倉儲

image

image

 

進入導引式的安裝畫面,可以直接選擇「下一步」

MDW003

 

選擇「新增」按鈕,去建立一個專門用來存放資料收集的資料倉儲的資料庫。

image

 

這裡我就直接設定資料庫名稱為 MDW,這個名稱可以按照你自己的喜好任意決定

MDW005

MDW006

 

接下來就看有沒有要特別指定某些帳號具有讀、寫或管理這個管理資料倉儲的權限,這裡可以先忽略,以後有需要在去設定也可以。

MDW007

 

確定無誤就可以按下「完成」,就可以很容易完成建立「管理資料倉儲」的部分了

MDW008

MDW009


設定資料收集

在完成前面的步驟,基本上只有建立好一個空的資料庫來存放資料收集的資訊,接下來我們要設定「資料收集」。在完成這些設定之後,SQL Server 會在 SQL Agent 裡面增加一些工作,透過排程去收集相關資訊並且將他們存放在指定的磁碟目錄下,再透過另外的工作將這些檔案,定時給匯入到「管理資料倉儲」內,後續我們才可以來使用。因此這裡我們一樣在資料收集的節點上,按下滑鼠右鍵選擇「工作」→「設定資料收集

image

MDW011

 

接下來的這個部分,在圖上 1 的地方是選擇之前所建立的那個管理資料倉儲的資料庫名稱,圖上 2 的部分則是設定資料收集所存放資料的快取目錄,這兩個部分和之前的版本都相同。較大的差異在於圖上 3 的部分,在 SQL Server 2008 & 2012 的時候,只有「系統資料收集組」,因此沒有選項可以選擇;而在 SQL Server 2014,則多增加了「交易效能收集組」,可以讓你查看資料庫中那些資料表和預存程序使用量較高,後續則可以考慮將這些改規畫成為 In-memory Table 或 Native Stored Procedure。

image

 

確認沒有問題,就可以按下「完成」按鈕,讓 SQL Server 去建立相關的 JOB 在 SQL Agent 內。

MDW013

 

如果這個時候 SQL Agent 是沒有啟動的,則會看到下圖一般的錯誤訊息。

MDW014

 

如果沒有異常的話,則可以順利完成,資料收集也就開始運作了。

MDW015

 


查看統計資料

當啟用資料收集之後, 在「系統資料收集組」內主要有三個資料收集,分別是「磁碟使用量」、「伺服器活動」和「查詢統計資料」,其中磁碟使用量預設是保存兩年( 730 天)的資料,而伺服器活動和查詢統計資料預設都是保留最近 14 天的資料,如果覺得這個預計值太低的話,則可以在該收集項目上,按下滑鼠右鍵選擇屬「屬性」,下方有個資料保留期限,則可以按照你所需要的放大這個時間,但要注意的是,如果 SQL Server 平常是非常忙碌的,放大這個值可能會造成管理資料倉儲的資料庫會非常的大,這點需要特別注意一下。

MDW020

 

完成相關設定之後,我們就可以來查看這些相關報表了,基本上可以有兩個進入點

 

第一個是在 SSMS 下選擇「管理」→「資料收集」按下滑鼠右鍵選擇「報表」→「管理資料倉儲」,就可以看到三張可以查詢的報表

image

 

另外一個進入點,則是選擇管理資料倉儲的資料庫( MDW ),在該資料庫上按下滑鼠右鍵,選擇「報表」→「管理資料倉儲」,在這裡要稍微注意一下,如果是 SQL Server 2014 的版本,則會多出現有「交易效能分析概觀」,在這裡我們可以選擇「管理資料倉儲概觀

image

 

出現畫面後,則可以點選連結進入查看相關系統收集的資料

image

 


 

基本上整個設定和使用都非常的方便,如果能夠善加這些報表,相對對您在管理資料庫上面,會有許多的幫助。