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

2014-12-16

9823
0
SQL Performance Tuning
2020-02-20

早期在 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 上面，在所要設定主機的節點下，選擇「管理」→「資料收集」，按下滑鼠右鍵選擇「工作」→「設定管理資料倉儲」

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

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

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

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

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

設定資料收集

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

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

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

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

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

查看統計資料

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