示例工程數(shù)據(jù)庫(kù)教程后端【免費(fèi)下載鏈接】sql-server-samplesAzure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for SQL Server, Azure SQL, Azure Synapse, and Azure SQL Edge項(xiàng)目地址https://gitcode.com/gh_mirrors/sq/sql-server-samples點(diǎn)擊查看免費(fèi)下載SQL Assessment API 提供了一套可擴(kuò)展的機(jī)制來評(píng)估 SQL Server 實(shí)例配置是否符合最佳實(shí)踐。Performance probe性能探針是其中用于采集 Windows 性能計(jì)數(shù)器Performance Counter數(shù)據(jù)的核心探針類型其返回的多條樣本數(shù)據(jù)需要配合performance 數(shù)據(jù)變換Data Transformation計(jì)算平均值、比率、速率等派生值最終由檢查規(guī)則Rule的條件表達(dá)式判定合規(guī)性。讀完本文你將掌握 Performance probe 的完整 JSON 配置語(yǔ)法、計(jì)數(shù)器別名與實(shí)例過濾技巧、五種派生值函數(shù)的數(shù)學(xué)含義與適用場(chǎng)景以及在同一檢查中正確使用多個(gè)計(jì)數(shù)器并規(guī)避多實(shí)例結(jié)果錯(cuò)位陷阱的實(shí)戰(zhàn)方案。Performance probe 是什么Performance probe 的類型代碼Type code為Performance用于讀取目標(biāo)機(jī)器上的性能計(jì)數(shù)器值如Buffer Manager、Latches、Databases、Memory Node、Access Methods等性能對(duì)象下的計(jì)數(shù)器。它并不是一個(gè)獨(dú)立的檢查而是作為數(shù)據(jù)源供規(guī)則引用探針負(fù)責(zé)取數(shù)performance 數(shù)據(jù)變換負(fù)責(zé)把多份樣本加工成單個(gè)派生值規(guī)則條件再基于該值做出合規(guī)性判斷。從 SQL Assessment API 的探針體系看Performance是眾多探針類型中的一種。在 Probe 文檔 中列出的可用探針類型還包括 AzGraph、AzMetadata、CMD、External、PowerShell、Registry、SQLT-SQL與 WMI。其中 T-SQL 探針從動(dòng)態(tài)管理視圖DMV取數(shù)而 Performance 探針與 WMI 探針一樣數(shù)據(jù)來自操作系統(tǒng)層因此它適合捕捉 T-SQL 查詢無法直接暴露的運(yùn)行時(shí)指標(biāo)例如緩沖區(qū)命中率、閂鎖等待時(shí)間、每秒事務(wù)數(shù)等。samples/manage/sql-assessment-api/docs/Reference/Probes/Performance.mdImplementation 屬性采樣頻率與計(jì)數(shù)器清單Performance probe 的implementation部分包含三個(gè)參數(shù)參數(shù)是否必填類型默認(rèn)值說明numberOfSamples可選number2采集的樣本數(shù)量interval可選number1兩次采樣之間的時(shí)間間隔秒counters必填object—需要采集的性能計(jì)數(shù)器清單numberOfSamples探針會(huì)對(duì)每個(gè)計(jì)數(shù)器連續(xù)采樣 N 次。默認(rèn) 2 次意味著拿到兩個(gè)樣本足以支撐差值型派生值如 rate、delta_ratio、ratio的計(jì)算某些場(chǎng)景如求平均值可提高采樣次數(shù)以獲得更穩(wěn)定的統(tǒng)計(jì)結(jié)果。interval相鄰樣本的采集間隔秒。默認(rèn) 1 秒。對(duì)于瞬態(tài)波動(dòng)明顯的計(jì)數(shù)器如Pages Allocated/sec合理調(diào)整采樣間隔能反映更真實(shí)的負(fù)載特征。counters必填。這是一個(gè) JSON 對(duì)象其每個(gè)屬性名對(duì)應(yīng)一個(gè)性能對(duì)象Performance Object例如Latches、Databases、Buffer Manager每個(gè)性能對(duì)象的值又是一個(gè) JSON 對(duì)象其中的每個(gè)屬性代表該對(duì)象下的一個(gè)性能計(jì)數(shù)器。Performance probe 的整體結(jié)構(gòu)可參考倉(cāng)庫(kù)中的結(jié)構(gòu)圖 BasicPerformanceProbeStructure.svg計(jì)數(shù)器別名與實(shí)例過濾計(jì)數(shù)器名稱常常包含空格和其他非字母數(shù)字字符例如Pages Allocated/sec無法直接在條件表達(dá)式和消息模板中引用。因此每個(gè)計(jì)數(shù)器都必須指定一個(gè)別名alias——?jiǎng)e名將作為該計(jì)數(shù)器在檢查條件與消息中使用的變量名。此外計(jì)數(shù)器可選地聲明一個(gè)感興趣的實(shí)例列表instances。例如 SQL Server 的Databases性能對(duì)象下Transactions/sec計(jì)數(shù)器會(huì)為每個(gè)數(shù)據(jù)庫(kù)實(shí)例分別報(bào)告值其中_Total代表匯總實(shí)例而Memory Node對(duì)象下的計(jì)數(shù)器則按 NUMA 內(nèi)存節(jié)點(diǎn)實(shí)例報(bào)告。示例一最簡(jiǎn)單的單對(duì)象單計(jì)數(shù)器下面的配置為Access Methods對(duì)象下的Pages Allocated/sec計(jì)數(shù)器采樣并把結(jié)果以pages_allocated變量提供給檢查使用{ type: Performance, implementation: { Counters: { Access Methods: { Pages Allocated/sec: pages_allocated } } } }示例二調(diào)整采樣次數(shù)為Memory Node對(duì)象下的Free Node Memory (KB)計(jì)數(shù)器只采集 1 個(gè)樣本此時(shí)該計(jì)數(shù)器只適合使用average、min、max這類不依賴多個(gè)樣本的派生函數(shù){ type: Performance, implementation: { NumberOfSamples: 1, Counters: { Memory Node: { Free Node Memory (KB): free_node_memory } } } }示例三多對(duì)象、別名與實(shí)例過濾的組合同時(shí)采集Memory Node與Access Methods兩個(gè)性能對(duì)象其中Free Node Memory (KB)通過對(duì)象式寫法指定alias為free_node_memory且instances限定為[000]僅取編號(hào)為000的 NUMA 節(jié)點(diǎn)實(shí)例而Target Node Memory (KB)則使用字符串簡(jiǎn)寫形式{ type: Performance, implementation: { NumberOfSamples: 1, Counters: { Memory Node: { Free Node Memory (KB): { alias: free_node_memory, instances: [000] }, Target Node Memory (KB): target_node_memory }, Access Methods: { Pages Allocated/sec: pages_allocated } } } }可見計(jì)數(shù)器值的兩種寫法等價(jià)字符串簡(jiǎn)寫計(jì)數(shù)器名: 別名與對(duì)象寫法計(jì)數(shù)器名: { alias: ..., instances: [...] }。需要過濾實(shí)例時(shí)使用對(duì)象寫法。完整 ruleset 示例五個(gè)基于性能計(jì)數(shù)器的檢查下面是一份完整可用的規(guī)則集 JSON與文檔保持一致它同時(shí)定義了 5 個(gè)針對(duì)Server目標(biāo)的規(guī)則以及一個(gè)名為PerformanceProbe的探針定義。通過這個(gè)例子可以完整看到規(guī)則rules 探針probes 變換transform三者如何協(xié)作{ schemaVersion: 1.0, name: Performance Checks Example, version: 1.0, rules:[ { target: { type: Server }, id: TotalPages, itemType: definition, displayName: Buffer Manager Total pages, description: Use \average\, \min\, or \max\ for counter type PERF_COUNTER_LARGE_RAWCOUNT(65792)., message: Total pages ({total_pages}) is greater than 0, condition: { lt: [total_pages, 0] }, probes: [ { id: PerformanceProbe, transform: { type: performance, counters: { total_pages: average } } } ] }, { target: { type: Server }, id: CacheHitRatio, itemType: definition, displayName: Buffer Manager cache hit ratio, description: Use \ratio\ for counter type PERF_LARGE_RAW_FRACTION(537003264) and specify base PERF_LARGE_RAW_BASE(1073939712)., message: Cache hit ratio ({cache_hit_ratio:P0}) is greater than 0, condition: { lt: [cache_hit_ratio, 0] }, probes: [ { id: PerformanceProbe, transform: { type: performance, counters: { cache_hit_ratio: { type: ratio, base: cache_hit_ratio_base } } } } ] }, { target: { type: Server }, id: LatchWaitTime, itemType: definition, displayName: Average Latch Wait Time, description: Use \delta_ratio\ for counter type PERF_AVERAGE_BULK(1073874176) and base PERF_LARGE_RAW_BASE(1073939712)., message: Average Latch Wait Time ({latch_wait_time}ms.) is greater than 0, condition: { lt: [latch_wait_time, 0] }, probes: [ { id: PerformanceProbe, transform: { type: performance, counters: { latch_wait_time: { type: delta_ratio, base: latch_wait_time_base } } } } ] }, { target: { type: Server }, id: TransactionsPerSec, itemType: definition, displayName: Database Transactions per sec, description: Use \rate\ for counter type PERF_COUNTER_BULK_COUNT(272696576)., message: Transactions per sec ({transactions_sec:0.##}sec.) for database {instance_name} is greater than 0, condition: { or: [{eq: [instance_name, _Total]}, {le: [transactions_sec, 0]}] }, probes: [ { id: PerformanceProbe, transform: { type: performance, counters: { transactions_sec: rate } } } ] }, { target: { type: Server }, id: TotalTransactionsPerSec, itemType: definition, displayName: Total Database Transactions per sec, description: Use \rate\ for counter type PERF_COUNTER_BULK_COUNT(272696576)., message: Total Transactions per sec ({transactions_sec:0.##}sec.) is greater than 0, condition: { lt: [transactions_sec, 0] }, probes: [ { id: PerformanceProbe, transform: { type: performance, counters: { transactions_sec: { type: rate, instance: _Total } } } } ] } ], probes:{ PerformanceProbe: [{ type: Performance, implementation: { Counters: { Buffer Manager: { Buffer cache hit ratio: cache_hit_ratio, Buffer cache hit ratio base: cache_hit_ratio_base, Target pages: total_pages }, Latches: { Average Latch Wait Time (ms): latch_wait_time, Average Latch Wait Time Base: latch_wait_time_base }, Databases: { Transactions/sec: transactions_sec } } } }] } }逐規(guī)則要點(diǎn)解讀TotalPagesTarget pages屬于PERF_COUNTER_LARGE_RAWCOUNT(65792)類型的原始計(jì)數(shù)值適合用average或min/max派生條件lt: [total_pages, 0]即總頁(yè)數(shù)小于 0 則告警。CacheHitRatioBuffer cache hit ratio屬于PERF_LARGE_RAW_FRACTION(537003264)比例型計(jì)數(shù)器必須配套其 base 計(jì)數(shù)器Buffer cache hit ratio base類型PERF_LARGE_RAW_BASE(1073939712)在變換中使用ratio類型并指定base。消息模板{cache_hit_ratio:P0}中的P0表示按百分比格式顯示。LatchWaitTimeAverage Latch Wait Time (ms)屬于PERF_AVERAGE_BULK(1073874176)平均型計(jì)數(shù)器同樣需要 base 計(jì)數(shù)器使用delta_ratio類型派生。TransactionsPerSecTransactions/sec屬于PERF_COUNTER_BULK_COUNT(272696576)速率型計(jì)數(shù)器使用rate派生。由于它按數(shù)據(jù)庫(kù)實(shí)例分別報(bào)告條件用or組合instance_name等于_Total時(shí)跳過或transactions_sec 0時(shí)告警。{transactions_sec:0.##}表示保留兩位小數(shù)的數(shù)值格式。TotalTransactionsPerSec在變換層用instance: _Total直接過濾出匯總實(shí)例條件只需比較單一值。關(guān)于condition中使用的運(yùn)算符lt、or、eq、le等可參考 Operators 參考文檔其中還包含match正則匹配、interval區(qū)間取值等更復(fù)雜的判定手段。規(guī)則的其他屬性message模板、displayName、description、itemType的完整語(yǔ)義見 Rule 文檔。performance 數(shù)據(jù)變換五種派生值函數(shù)Performance probe 返回的是包含多次樣本的表格而檢查需要一個(gè)派生值。performance變換詳見 DataTransformation/performance.md為每個(gè)計(jì)數(shù)器計(jì)算指定的派生值。相關(guān)符號(hào)約定n— 該計(jì)數(shù)器的樣本數(shù)量c?、c?— 該計(jì)數(shù)器的樣本值t?、t?— 樣本的時(shí)間戳b?、b?— base 計(jì)數(shù)器的樣本值類型參數(shù)公式適用計(jì)數(shù)器類型average—(1/n) · Σc?PERF_COUNTER_LARGE_RAWCOUNT(65792)等原始計(jì)數(shù)值delta_ratiobase_counter(c? ? c?) / (b? ? b?)PERF_AVERAGE_BULK(1073874176)如平均閂鎖等待時(shí)間需配 basemin/max—所有樣本的最小值 / 最大值原始計(jì)數(shù)值用于極值監(jiān)控rate—(c? ? c?) / (t? ? t?)PERF_COUNTER_BULK_COUNT(272696576)每秒計(jì)數(shù)類ratiobase_counter(c?/b? c?/b?) / 2PERF_LARGE_RAW_FRACTION(537003264)如緩存命中率需配 base要點(diǎn)rate 與 average 的區(qū)別rate計(jì)算的是兩個(gè)樣本的差值 ÷ 時(shí)間戳差值即真正的每秒速率適用于Transactions/sec這類本身就是每秒次數(shù)的計(jì)數(shù)器average則是多個(gè)樣本的算術(shù)平均適用于靜態(tài)量如總頁(yè)數(shù)。ratio 與 delta_ratio 的區(qū)別ratio是兩次采樣的比值取平均delta_ratio是兩次采樣差值的比值兩者都要求指定base計(jì)數(shù)器且 base 計(jì)數(shù)器必須與主計(jì)數(shù)器同時(shí)出現(xiàn)在探針的Counters配置中如示例中的Buffer cache hit ratio base。在performance變換中指定派生類型有兩種寫法字符串簡(jiǎn)寫total_pages: average或?qū)ο髮懛╟ache_hit_ratio: { type: ratio, base: cache_hit_ratio_base }后者還可附加instance參數(shù)如{ type: rate, instance: _Total }在變換層過濾實(shí)例。變換的整體結(jié)構(gòu)見 BasicPerformanceTransformStructure.svg一個(gè)檢查使用多個(gè)計(jì)數(shù)器多實(shí)例結(jié)果的錯(cuò)位陷阱在實(shí)際使用中同一檢查往往需要同時(shí)觀察多個(gè)計(jì)數(shù)器例如批量請(qǐng)求速率batch_request_sec與鎖請(qǐng)求速率lock_requests_sec。但當(dāng)這些計(jì)數(shù)器來自不同的實(shí)例時(shí)直接合并會(huì)得到意外結(jié)果?？聪旅娴姆蠢褍蓚€(gè)計(jì)數(shù)器放進(jìn)同一個(gè) probe referencebatch_request_sec不指定實(shí)例lock_requests_sec指定_Totalprobes: [ { id: PerformanceProbe, alias: b, transform: { type: performance, counters: { batch_request_sec: rate, lock_requests_sec: { type: rate, instance: _Total } } } } ]變換結(jié)果返回兩行數(shù)據(jù)每行只填充了其中一個(gè)計(jì)數(shù)器的值batch_request_seclock_requests_secinstance_name123snull未選擇實(shí)例Null456s_Total條件表達(dá)式是按行逐一求值的第一行對(duì)batch_request_sec求值并可能觸發(fā)一條消息第二行對(duì)lock_requests_sec求值并觸發(fā)另一個(gè)錯(cuò)誤——這顯然不符合兩個(gè)指標(biāo)共同判定的預(yù)期。解決方案用別名alias拆分引用正確做法是將同一 Performance probe 引用兩次各自通過變換只取一個(gè)計(jì)數(shù)器并用alias區(qū)分結(jié)果。alias是探針引用的任意別名其機(jī)制詳見 ProbeReference 文檔中的 alias 一節(jié)probes: [ { id: PerformanceProbe, alias: b, transform: { type: performance, counters: { batch_request_sec: rate } } }, { id: PerformanceProbe, alias: l, transform: { type: performance, counters: { lock_requests_sec: { type: rate, instance: _Total } } } } ]此時(shí)傳給檢查的數(shù)據(jù)結(jié)構(gòu)變?yōu)閎::batch_request_secb::instance_namel::lock_requests_secl::instance_name123s無實(shí)例456s_Total兩個(gè)計(jì)數(shù)器各自獨(dú)立成行條件可以同時(shí)、無歧義地引用它們。變量名有三種等價(jià)寫法在無歧義場(chǎng)景下PerformanceProbe::batch_request_sec b::batch_request_sec batch_request_sec其中b::batch_request_sec形式通過別名 雙冒號(hào)精確定位到指定引用產(chǎn)生的數(shù)據(jù)是處理同一探針被多次調(diào)用時(shí)的標(biāo)準(zhǔn)做法ProbeReference 文檔 中還有把探針 A 的數(shù)據(jù)作為參數(shù)傳給探針 B 的進(jìn)階用法。引擎視角Performance probe 的執(zhí)行語(yǔ)義了解探針引擎的執(zhí)行語(yǔ)義有助于寫出高效的規(guī)則。根據(jù) Probe 文檔 的說明探針是 JSON 屬性值為實(shí)現(xiàn)數(shù)組屬性名即探針 ID值是一個(gè)探針實(shí)現(xiàn)probe implementation數(shù)組。引擎會(huì)選擇第一個(gè)與目標(biāo)模式target pattern匹配的實(shí)現(xiàn)因此實(shí)現(xiàn)順序很重要。Performance probe 也可以與其他類型的實(shí)現(xiàn)如 T-SQL、WMI混合構(gòu)成一個(gè)探針。探針應(yīng)設(shè)計(jì)為無副作用side-effect free的函數(shù)引擎不保證探針的調(diào)用順序甚至可能為降低對(duì)目標(biāo) SQL Server 的負(fù)載而重排調(diào)用當(dāng)沒有任何檢查需要某個(gè)探針的數(shù)據(jù)時(shí)該探針根本不會(huì)被調(diào)用。默認(rèn)規(guī)則集中的探針只讀取元數(shù)據(jù)不讀取用戶表數(shù)據(jù)也不向?qū)嵗龑懭肴魏蝺?nèi)容。性能計(jì)數(shù)器的采樣開銷Performance probe 需要按numberOfSamples × interval的時(shí)間窗口在目標(biāo)機(jī)器上持續(xù)采樣這與其他一次性取數(shù)的探針如單條 T-SQL 查詢不同。在自定義規(guī)則時(shí)應(yīng)合理控制采樣次數(shù)與間隔避免在高負(fù)載實(shí)例上疊加額外的采樣開銷。與默認(rèn)規(guī)則集的銜接倉(cāng)庫(kù)中的 DefaultRuleset.csv 提供了默認(rèn)規(guī)則集的可讀版本455 行規(guī)則可直接在倉(cāng)庫(kù)中瀏覽與篩選其中包含大量與性能相關(guān)的檢查例如HighCPUUsage評(píng)估服務(wù)器整體 CPU 與 SQL Server 進(jìn)程 CPU 占用閾值參數(shù)默認(rèn) 70PlansUseRatio評(píng)估計(jì)劃緩存中單次使用計(jì)劃的比例用于判斷是否應(yīng)啟用 Optimize for ad hoc workloads一系列 TempDB 文件配置檢查TempDBFilesNotLess8、TempDBFiles1PerCPU等。這類運(yùn)行時(shí)指標(biāo)類檢查正是 Performance probe 與 performance 變換的典型應(yīng)用場(chǎng)景。讀者可以結(jié)合 MakingCustomChecks_sample.json自定義規(guī)則集示例與 DisablingBuiltInChecks_sample.json按 ID / 標(biāo)簽禁用內(nèi)置規(guī)則示例來編寫自己的性能監(jiān)控規(guī)則。更完整的概念體系探針、規(guī)則、變換、消息模板、局部變量可繼續(xù)閱讀 Customization 目錄 下的文檔。小結(jié)Performance probe 是 SQL Assessment API 中連接操作系統(tǒng)性能計(jì)數(shù)器與配置合規(guī)性判定的橋梁。本文覆蓋了其三個(gè) implementation 參數(shù)numberOfSamples、interval、counters、計(jì)數(shù)器別名與實(shí)例過濾的兩種 JSON 寫法、五種派生值函數(shù)average、min/max、rate、ratio、delta_ratio的公式與計(jì)數(shù)器類型對(duì)應(yīng)關(guān)系以及多計(jì)數(shù)器多實(shí)例場(chǎng)景下按行求值的陷阱與基于別名拆分的標(biāo)準(zhǔn)解法。在動(dòng)手自定義性能規(guī)則時(shí)建議對(duì)照 performance 數(shù)據(jù)變換文檔 與 ProbeReference 文檔 協(xié)同閱讀并結(jié)合默認(rèn)規(guī)則集觀察真實(shí)生產(chǎn)級(jí)規(guī)則的寫法。贊分享示例工程數(shù)據(jù)庫(kù)教程后端【免費(fèi)下載鏈接】sql-server-samplesAzure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for SQL Server, Azure SQL, Azure Synapse, and Azure SQL Edge項(xiàng)目地址https://gitcode.com/gh_mirrors/sq/sql-server-samples點(diǎn)擊查看免費(fèi)下載相關(guān)推薦SQL Assessment API 自定義探測(cè)指南用 T-SQL Probe 驅(qū)動(dòng) SQL Server 配置評(píng)估SQL Assessment API 自定義探測(cè)指南用 T SQL Probe 驅(qū)動(dòng) SQL Server 配置評(píng)估 SQL Assessment API 通示例工程數(shù)據(jù)庫(kù)教程后端SQL Assessment API 深度指南用規(guī)則集評(píng)估 SQL Server 與 Azure SQL 托管實(shí)例配置sql-server-samplesSQL Assessment API 深度指南用規(guī)則集評(píng)估 SQL Server 與 Azure SQL 托管實(shí)例配置sql server samples示例工程數(shù)據(jù)庫(kù)教程后端sql-server-samples 實(shí)戰(zhàn)指南使用 SQL Assessment API 客戶端評(píng)估 SQL Server 配置最佳實(shí)踐sql server samples 實(shí)戰(zhàn)指南使用 SQL Assessment API 客戶端評(píng)估 SQL Server 配置最佳實(shí)踐 SQL Assess示例工程數(shù)據(jù)庫(kù)教程后端上一篇Rust跨平臺(tái)編譯終極指南使用Cross實(shí)現(xiàn)零配置多架構(gòu)支持下一篇MCP代理內(nèi)存使用分析終極指南Valgrind實(shí)戰(zhàn)教程創(chuàng)作聲明：本文部分內(nèi)容由AI輔助生成（AIGC），僅供參考