通過SQL Server數(shù)據(jù)倉庫查看數(shù)據(jù)收集組

1、通過SQL Server數(shù)據(jù)倉庫查看數(shù)據(jù)收集組發(fā)布日期：10-01-11 10:53:46 點擊次數(shù)：4 在此實驗中，您將查看系統(tǒng)支持的數(shù)據(jù)收集類型和收集計劃，并配置其屬性。 1.通過管理數(shù)據(jù)倉庫向?qū)Р榭聪到y(tǒng)收集組及其狀態(tài)管理數(shù)據(jù)倉庫配置完成后，向?qū)Р粌H僅會創(chuàng)建數(shù)據(jù)庫(即ManagementDW)，同時還將自動啟動SQL Server Agent 作業(yè)來收集并更新系統(tǒng)數(shù)據(jù)收集組(1)在CHICAGOSQLDEV01 實例中返回Object Explorer 并依次展開Management, Data Collection, System Data Collection Sets. 此時可以看到

2、三種系統(tǒng)數(shù)據(jù)收集組：Disk Usage, Query Statistics, 以及 Server Activity.(2)右鍵點擊Disk Usage然后點擊Properties. Disk Usage統(tǒng)計數(shù)據(jù)的完整屬性將通過三個選項卡顯示，即General, Uploads 以及Description.點擊Description選項卡可以看到: Collects data about disk and log usage for all databases. 此描述信息并不是很詳細，但可以讓我們快速了解到磁盤使用的統(tǒng)計信息;這些信息針對實例中的所有數(shù)據(jù)庫的數(shù)據(jù)文件和日志文件。(3)點擊Up

3、loads 選項卡可以看到所有信息均為灰色，因為這些特殊的數(shù)據(jù)集都采用非緩存模式收集。在非緩存模式中數(shù)據(jù)的收集和上傳通過相同的代理作業(yè)進行處理。因此Uploads選項卡中的信息對于此收集組來說沒有實際意義。非緩存模式對于相對負載較輕或數(shù)據(jù)集收集操作不是很頻繁的情況下很適用(4)最后，點擊General 選項卡，可以看到該數(shù)據(jù)收集的特定屬性：Data collection and upload在這里可以將Data collection and upload 更改為緩存模式。但由于我們實驗中的數(shù)據(jù)收集操作并不是很頻繁，因此不需要進行緩存。如果數(shù)據(jù)收集操作很復(fù)雜且很頻繁，則建議選擇緩存模式。理想狀態(tài)

4、下，緩存將只對進行收集的本地服務(wù)器性能產(chǎn)生影響，然后再將收集上來的數(shù)據(jù)慢慢上傳到管理數(shù)據(jù)倉庫。例如，針對特定數(shù)值的收集頻率可能為30-60秒，但上傳頻率可以設(shè)置為30或60分鐘。Schedule:點擊 Pick 可以看到可用的計劃。在這里可以看到我們選擇的計劃是occur every day every 6 hour(s) between 12:00:00 AM and 11:59:59 PM 并且使用該計劃的作業(yè)數(shù)為1.如果設(shè)置多臺服務(wù)器采用相同的計劃進行數(shù)據(jù)收集并將數(shù)據(jù)上傳到相同的管理數(shù)據(jù)倉庫，則建議將作業(yè)的啟動時間岔開，這樣可以更好的平衡管理數(shù)據(jù)倉庫的負載。Collection item

5、s這里可以看到兩個項目：Disk Usage Data Files 和Disk Usage Log Files. 每一個收集器的類型均為 “Generic T-SQL Query Collector Type” ，因此在Input parameters 中的T-SQL腳本將用于執(zhí)行這些收集項目Disk Usage Data Files選擇Disk Usage Data Files 行，然后在Input parameters 文本框中將顯示下列代碼：-disk_usageDECLARE dbsize bigintDECLARE logsize bigintDECLARE ftsize bigin

6、tDECLARE reservedpages bigintDECLARE pages bigintDECLARE usedpages bigintSELECT dbsize = SUM(convert(bigint,casewhen type = 0 then size else 0 end),logsize = SUM(convert(bigint,casewhen type = 1 then size else 0 end),ftsize = SUM(convert(bigint,casewhen type = 4 then size else 0 end)FROM sys.databas

7、e_filesSELECT reservedpages = SUM(a.total_pages),usedpages = SUM(a.used_pages),pages = SUM(CASEWHEN ernal_type IN (202,204)THEN 0WHEN a.type != 1THEN a.used_pagesWHEN p.index_id 2THEN a.data_pagesELSE 0END)FROM sys.partitions pJOIN sys.allocation_units aON p.partition_id = a.container_idLEFT J

8、OIN ernal_tables itON p.object_id = it.object_idSELECT dbsize as dbsize,logsize as logsize,ftsize as ftsize,reservedpages as reservedpages,usedpages as usedpages,pages as pages選中這些代碼并將其復(fù)制到新建的查詢窗口中，首先注釋掉第一行(disk_usage)，然后在 master 數(shù)據(jù)庫中執(zhí)行這段代碼查詢結(jié)果如下圖所示：請注意上述查詢結(jié)果只針對一個數(shù)據(jù)庫，而通過SQL Server Agent 作業(yè)執(zhí)行時將

9、收集并上傳所有數(shù)據(jù)庫的數(shù)據(jù)集Disk Usage Log Files選中Disk Usage Log Files 然后查看Input parameters 文本框中的相關(guān)代碼注意：在此版本中，針對Disk Usage Log Files 的數(shù)據(jù)收集操作是通過執(zhí)行DBCC SQLPERF (logspace)語句而實現(xiàn)的Specify the account to run the collection set保留默認設(shè)置SQL Server Agent Service Account 即可Specify how long to retain data in the management data

10、 warehouseDisk Usage 的數(shù)據(jù)收集并不會產(chǎn)生大量的數(shù)據(jù)，默認的保留期限是730 天。(假設(shè)系統(tǒng)有10個數(shù)據(jù)庫，則在730天中將會有29200行的數(shù)據(jù)產(chǎn)生。即每天收集4次，則730天中共插入4 x 10 x 730 = 29200 行)如果數(shù)據(jù)庫數(shù)量很多且收集頻率很高，則可以將保留期限適當減少(5)連接到CHICAGOCONFIGSERVER 實例中，新建查詢窗口并直接查詢ManagementDW.snapshots.disk_usage 和ManagementDW.snapshots.log_usage 這兩個表：SELECT * FROM ManagementDW.snap

11、shots.disk_usageSELECT * FROM ManagementDW.snapshots.log_usage(6)除了直接查詢數(shù)據(jù)集，還可以在Object Explorer 中通過報表來進行查看，下一個練習當中我們將使用該功能。出于實驗?zāi)康?，在此我們還需要修改一些收集屬性，加大進行數(shù)據(jù)收集的頻率，以便更清晰的看到結(jié)果。在這里我們將收集頻率調(diào)整為15分鐘。在真正的生產(chǎn)環(huán)境中，不建議這樣設(shè)置，否則將影響msdb 數(shù)據(jù)庫以及管理數(shù)據(jù)倉庫的性能。(7)修改System Data Collection Sets 收集屬性的方法有很多。根據(jù)緩存模式的區(qū)別，每個收集組都有1個或2個作業(yè)，例如

12、Server Activity采用緩存模式，有2個作業(yè);而Disk Usage 和Query Statistics 則采用非緩存模式，因此只有1個作業(yè)。為了提高收集和上傳的頻率，我們可以更改作業(yè)計劃。重要提示：在進行充分測試以前，不要在生產(chǎn)環(huán)境中更改收集頻率2.為查詢統(tǒng)計信息以及磁盤使用更改作業(yè)計劃，每分鐘收集數(shù)值(1)在CHICAGOSQLDEV01 實例和 CHICAGOCONFIGSERVER 實例的Object Explorer 中，依次展開SQL Server Agent 節(jié)點和Jobs 節(jié)點，查看當前的作業(yè)(2)每個SQL Server 實例中至少會有一個作業(yè)，用于進行系統(tǒng)健康的監(jiān)

13、控。詳細信息，請參考下面鏈接中的文章：/psssql/archive/2008/07/15/supporting-sql-server-2008-the-system-health-session.aspx(3)其它作業(yè)是我們手動創(chuàng)建的(目前在CHICAGOSQLDEV01 實例中有5個用于收集數(shù)據(jù)的實例，在CHICAGOCONFIGSERVER 實例中有一個作業(yè)用于清除歷史記錄，如下表所示：Data Collection Set Type Cached v. Non-cached Job Collection or Upload (or both) C

14、urrent Job Name Descriptive Job Name(change to)Disk Usage Non-cached Both collection_set_1_noncached_collect_and_upload DiskUsage_noncachedQuery Statistics Cached Collection collection_set_2_collection QueryStats_collectionQuery Statistics Cached Upload collection_set_2_upload QueryStats_uploadServe

15、r Activity Cached Collection collection_set_3_collection ServerActivity_collectionServer Activity Cached Upload collection_set_3_ upload ServiceActivity_upload根據(jù)上述表格，將每個作業(yè)的名稱改為名稱，即上表的最后一列 (4)在CHICAGOSQLDEV01 實例中，右鍵點擊SQL Server Agent 然后依次點擊New 和Schedule(5)在New Job Schedule 對話框，輸入JobSchedule_Every_1mi

16、n 作為作業(yè)名稱，然后如下圖所示進行設(shè)置：(6)確定作業(yè)將在午夜每隔1分鐘執(zhí)行一次，然后點擊 OK(7)將這個計劃添加到下面兩個作業(yè)中：DiskUsage_noncached 和QueryStats_upload. 右鍵點擊作業(yè)名稱，然后點擊Properties, 在Schedules 選項卡中，點擊Pick 并選擇JobSchedule_Every_1min 計劃。為ServerActivity_Upload 選擇CollectorSchedule_Every_5min 計劃。重要提示：在生產(chǎn)環(huán)境中設(shè)置收集頻率過高的時候要特別小心3.為數(shù)據(jù)收集和分析操作而生成相關(guān)信息此任務(wù)并不復(fù)雜，但第一個

17、批處理文件需要執(zhí)行將近5分鐘的時間，第二個批處理文件將打開多個SQLCMD 命令行窗口，直到手動停止。這些腳本在進行分析前都需要時間。因此我們先將它們啟動，直到后面的步驟再將其停止。另外，我們不需要關(guān)注這些腳本如何運行也不需要等待它們執(zhí)行完畢。(1)運行批處理文件。打開我的電腦，然后打開下面的文件夾：C:Manageability LabsPerformance Data Collection(2)雙擊GenerateData.cmd 文件。該腳本將執(zhí)行將近5分鐘的時間直至完成(3)雙擊GenerateActivity.cmd 文件。該腳本將循環(huán)執(zhí)行，直到我們在后面的練習中關(guān)閉該窗口查看Ser

18、ver Activity 和 Query Statistics 這兩個系統(tǒng)數(shù)據(jù)收集組的代碼、設(shè)計、以及參數(shù)(4)返回到CHICAGOSQLDEV01 實例的Object Explorer 并依次展開Management, Data Collection, 以及System Data Collection Sets. 在此可以看到三個系統(tǒng)數(shù)據(jù)收集組：Disk Usage, Query Statistics, and Server Activity.(5)右鍵點擊Query Statistics 然后點擊Properties. 注意該收集組和Disk Usage 數(shù)據(jù)收集組屬性的區(qū)別：相同點 兩者均為非緩存模式 兩者在Uploads 選項卡中均為灰色 兩者均通過SQL Server Agent 運行不同點 Disk Usage 數(shù)據(jù)收集組通過T-SQL 語句實現(xiàn)，在Input Parameters 中可以看到相關(guān)語句。而Query Statistics 則通過 內(nèi)部的 “Query Activity Collector Type” 來處理，且無法直接查看。該數(shù)據(jù)收集組所收集的內(nèi)容在Description 選項卡中可以看到：Collects query statistics, sql text and plans of most performance-affecting querie