通過(guò)SQL Server數(shù)據(jù)倉(cāng)庫(kù)查看數(shù)據(jù)收集組.doc

通過(guò)SQL Server數(shù)據(jù)倉(cāng)庫(kù)查看數(shù)據(jù)收集組發(fā)布日期：10-01-11 10:53:46 點(diǎn)擊次數(shù)：4 在此實(shí)驗(yàn)中，您將查看系統(tǒng)支持的數(shù)據(jù)收集類型和收集計(jì)劃，并配置其屬性。 1.通過(guò)管理數(shù)據(jù)倉(cāng)庫(kù)向?qū)Р榭聪到y(tǒng)收集組及其狀態(tài)管理數(shù)據(jù)倉(cāng)庫(kù)配置完成后，向?qū)Р粌H僅會(huì)創(chuàng)建數(shù)據(jù)庫(kù)(即ManagementDW)，同時(shí)還將自動(dòng)啟動(dòng)SQL Server Agent 作業(yè)來(lái)收集并更新系統(tǒng)數(shù)據(jù)收集組(1)在CHICAGOSQLDEV01 實(shí)例中返回Object Explorer 并依次展開(kāi)Management, Data Collection, System Data Collection Sets. 此時(shí)可以看到三種系統(tǒng)數(shù)據(jù)收集組：Disk Usage, Query Statistics, 以及 Server Activity.(2)右鍵點(diǎn)擊Disk Usage然后點(diǎn)擊Properties. Disk Usage統(tǒng)計(jì)數(shù)據(jù)的完整屬性將通過(guò)三個(gè)選項(xiàng)卡顯示，即General, Uploads 以及Description.點(diǎn)擊Description選項(xiàng)卡可以看到: Collects data about disk and log usage for all databases. 此描述信息并不是很詳細(xì)，但可以讓我們快速了解到磁盤(pán)使用的統(tǒng)計(jì)信息;這些信息針對(duì)實(shí)例中的所有數(shù)據(jù)庫(kù)的數(shù)據(jù)文件和日志文件。(3)點(diǎn)擊Uploads 選項(xiàng)卡可以看到所有信息均為灰色，因?yàn)檫@些特殊的數(shù)據(jù)集都采用非緩存模式收集。在非緩存模式中數(shù)據(jù)的收集和上傳通過(guò)相同的代理作業(yè)進(jìn)行處理。因此Uploads選項(xiàng)卡中的信息對(duì)于此收集組來(lái)說(shuō)沒(méi)有實(shí)際意義。非緩存模式對(duì)于相對(duì)負(fù)載較輕或數(shù)據(jù)集收集操作不是很頻繁的情況下很適用(4)最后，點(diǎn)擊General 選項(xiàng)卡，可以看到該數(shù)據(jù)收集的特定屬性：Data collection and upload在這里可以將Data collection and upload 更改為緩存模式。但由于我們實(shí)驗(yàn)中的數(shù)據(jù)收集操作并不是很頻繁，因此不需要進(jìn)行緩存。如果數(shù)據(jù)收集操作很復(fù)雜且很頻繁，則建議選擇緩存模式。理想狀態(tài)下，緩存將只對(duì)進(jìn)行收集的本地服務(wù)器性能產(chǎn)生影響，然后再將收集上來(lái)的數(shù)據(jù)慢慢上傳到管理數(shù)據(jù)倉(cāng)庫(kù)。例如，針對(duì)特定數(shù)值的收集頻率可能為30-60秒，但上傳頻率可以設(shè)置為30或60分鐘。Schedule:點(diǎn)擊 Pick 可以看到可用的計(jì)劃。在這里可以看到我們選擇的計(jì)劃是occur every day every 6 hour(s) between 12:00:00 AM and 11:59:59 PM 并且使用該計(jì)劃的作業(yè)數(shù)為1.如果設(shè)置多臺(tái)服務(wù)器采用相同的計(jì)劃進(jìn)行數(shù)據(jù)收集并將數(shù)據(jù)上傳到相同的管理數(shù)據(jù)倉(cāng)庫(kù)，則建議將作業(yè)的啟動(dòng)時(shí)間岔開(kāi)，這樣可以更好的平衡管理數(shù)據(jù)倉(cāng)庫(kù)的負(fù)載。Collection items這里可以看到兩個(gè)項(xiàng)目：Disk Usage Data Files 和Disk Usage Log Files. 每一個(gè)收集器的類型均為 “Generic T-SQL Query Collector Type” ，因此在Input parameters 中的T-SQL腳本將用于執(zhí)行這些收集項(xiàng)目Disk Usage Data Files選擇Disk Usage Data Files 行，然后在Input parameters 文本框中將顯示下列代碼：-disk_usageDECLARE dbsize bigintDECLARE logsize bigintDECLARE ftsize bigintDECLARE 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.database_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 JOIN 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ù)庫(kù)中執(zhí)行這段代碼查詢結(jié)果如下圖所示：請(qǐng)注意上述查詢結(jié)果只針對(duì)一個(gè)數(shù)據(jù)庫(kù)，而通過(guò)SQL Server Agent 作業(yè)執(zhí)行時(shí)將收集并上傳所有數(shù)據(jù)庫(kù)的數(shù)據(jù)集Disk Usage Log Files選中Disk Usage Log Files 然后查看Input parameters 文本框中的相關(guān)代碼注意：在此版本中，針對(duì)Disk Usage Log Files 的數(shù)據(jù)收集操作是通過(guò)執(zhí)行DBCC SQLPERF (logspace)語(yǔ)句而實(shí)現(xiàn)的Specify the account to run the collection set保留默認(rèn)設(shè)置SQL Server Agent Service Account 即可Specify how long to retain data in the management data warehouseDisk Usage 的數(shù)據(jù)收集并不會(huì)產(chǎn)生大量的數(shù)據(jù)，默認(rèn)的保留期限是730 天。(假設(shè)系統(tǒng)有10個(gè)數(shù)據(jù)庫(kù)，則在730天中將會(huì)有29200行的數(shù)據(jù)產(chǎn)生。即每天收集4次，則730天中共插入4 x 10 x 730 = 29200 行)如果數(shù)據(jù)庫(kù)數(shù)量很多且收集頻率很高，則可以將保留期限適當(dāng)減少(5)連接到CHICAGOCONFIGSERVER 實(shí)例中，新建查詢窗口并直接查詢ManagementDW.snapshots.disk_usage 和ManagementDW.snapshots.log_usage 這兩個(gè)表：SELECT * FROM ManagementDW.snapshots.disk_usageSELECT * FROM ManagementDW.snapshots.log_usage(6)除了直接查詢數(shù)據(jù)集，還可以在Object Explorer 中通過(guò)報(bào)表來(lái)進(jìn)行查看，下一個(gè)練習(xí)當(dāng)中我們將使用該功能。出于實(shí)驗(yàn)?zāi)康?，在此我們還需要修改一些收集屬性，加大進(jìn)行數(shù)據(jù)收集的頻率，以便更清晰的看到結(jié)果。在這里我們將收集頻率調(diào)整為15分鐘。在真正的生產(chǎn)環(huán)境中，不建議這樣設(shè)置，否則將影響msdb 數(shù)據(jù)庫(kù)以及管理數(shù)據(jù)倉(cāng)庫(kù)的性能。(7)修改System Data Collection Sets 收集屬性的方法有很多。根據(jù)緩存模式的區(qū)別，每個(gè)收集組都有1個(gè)或2個(gè)作業(yè)，例如Server Activity采用緩存模式，有2個(gè)作業(yè);而Disk Usage 和Query Statistics 則采用非緩存模式，因此只有1個(gè)作業(yè)。為了提高收集和上傳的頻率，我們可以更改作業(yè)計(jì)劃。重要提示：在進(jìn)行充分測(cè)試以前，不要在生產(chǎn)環(huán)境中更改收集頻率2.為查詢統(tǒng)計(jì)信息以及磁盤(pán)使用更改作業(yè)計(jì)劃，每分鐘收集數(shù)值(1)在CHICAGOSQLDEV01 實(shí)例和 CHICAGOCONFIGSERVER 實(shí)例的Object Explorer 中，依次展開(kāi)SQL Server Agent 節(jié)點(diǎn)和Jobs 節(jié)點(diǎn)，查看當(dāng)前的作業(yè)(2)每個(gè)SQL Server 實(shí)例中至少會(huì)有一個(gè)作業(yè)，用于進(jìn)行系統(tǒng)健康的監(jiān)控。詳細(xì)信息，請(qǐng)參考下面鏈接中的文章：/psssql/archive/2008/07/15/supporting-sql-server-2008-the-system-health-session.aspx(3)其它作業(yè)是我們手動(dòng)創(chuàng)建的(目前在CHICAGOSQLDEV01 實(shí)例中有5個(gè)用于收集數(shù)據(jù)的實(shí)例，在CHICAGOCONFIGSERVER 實(shí)例中有一個(gè)作業(yè)用于清除歷史記錄，如下表所示：Data Collection Set Type Cached v. Non-cached Job Collection or Upload (or both) Current 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_uploadServer Activity Cached Collection collection_set_3_collection ServerActivity_collectionServer Activity Cached Upload collection_set_3_ upload ServiceActivity_upload根據(jù)上述表格，將每個(gè)作業(yè)的名稱改為名稱，即上表的最后一列 (4)在CHICAGOSQLDEV01 實(shí)例中，右鍵點(diǎn)擊SQL Server Agent 然后依次點(diǎn)擊New 和Schedule(5)在New Job Schedule 對(duì)話框，輸入JobSchedule_Every_1min 作為作業(yè)名稱，然后如下圖所示進(jìn)行設(shè)置：(6)確定作業(yè)將在午夜每隔1分鐘執(zhí)行一次，然后點(diǎn)擊 OK(7)將這個(gè)計(jì)劃添加到下面兩個(gè)作業(yè)中：DiskUsage_noncached 和QueryStats_upload. 右鍵點(diǎn)擊作業(yè)名稱，然后點(diǎn)擊Properties, 在Schedules 選項(xiàng)卡中，點(diǎn)擊Pick 并選擇JobSchedule_Every_1min 計(jì)劃。為ServerActivity_Upload 選擇CollectorSchedule_Every_5min 計(jì)劃。重要提示：在生產(chǎn)環(huán)境中設(shè)置收集頻率過(guò)高的時(shí)候要特別小心3.為數(shù)據(jù)收集和分析操作而生成相關(guān)信息此任務(wù)并不復(fù)雜，但第一個(gè)批處理文件需要執(zhí)行將近5分鐘的時(shí)間，第二個(gè)批處理文件將打開(kāi)多個(gè)SQLCMD 命令行窗口，直到手動(dòng)停止。這些腳本在進(jìn)行分析前都需要時(shí)間。因此我們先將它們啟動(dòng)，直到后面的步驟再將其停止。另外，我們不需要關(guān)注這些腳本如何運(yùn)行也不需要等待它們執(zhí)行完畢。(1)運(yùn)行批處理文件。打開(kāi)我的電腦，然后打開(kāi)下面的文件夾：C:Manageability LabsPerformance Data Collection(2)雙擊GenerateData.cmd 文件。該腳本將執(zhí)行將近5分鐘的時(shí)間直至完成(3)雙擊GenerateActivity.cmd 文件。該腳本將循環(huán)執(zhí)行，直到我們?cè)诤竺娴木毩?xí)中關(guān)閉該窗口查看Server Activity 和 Query Statistics 這兩個(gè)系統(tǒng)數(shù)據(jù)收集組的代碼、設(shè)計(jì)、以及參數(shù)(4)返回到CHICAGOSQLDEV01 實(shí)例的Object Explorer 并依次展開(kāi)Management, Data Collection, 以及System Data Collection Sets. 在此可以看到三個(gè)系統(tǒng)數(shù)據(jù)收集組：Disk Usage, Query Statistics, and Server Activity.(5)右鍵點(diǎn)擊Query Statistics 然后點(diǎn)擊Properties. 注意該收集組和Disk Usage 數(shù)據(jù)收集組屬性的區(qū)別：相同點(diǎn) 兩者均為非緩存模式 兩者在Uploads 選項(xiàng)卡中均為灰色 兩者均通過(guò)SQL Server Agent 運(yùn)行不同點(diǎn) Disk Usage 數(shù)據(jù)收集組通過(guò)T-SQL 語(yǔ)句實(shí)現(xiàn)，在Input Parameters 中可以看到相關(guān)語(yǔ)句。而Query Statistics 則通過(guò) 內(nèi)部的 “Query Activity Collector Type” 來(lái)處理，且無(wú)法直接查看。該數(shù)據(jù)收集組所收集的內(nèi)容在Description 選項(xiàng)卡中可以看到：Collects query statistics, sql text and plans of most performance-affecting querie