使用查詢存放區監視效能
適用範圍：
適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器
適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器中的 查詢存放區 功能可讓您追蹤一段時間的查詢效能。 查詢存放區 可協助您快速找出執行時間最長且耗用最多資源的查詢，藉此簡化效能疑難解答。 查詢存放區 會自動擷取查詢和運行時間統計數據的歷程記錄，並保留它們以供檢閱。 它會依時間分割數據，讓您可以查看時態使用模式。 所有使用者、資料庫和查詢的數據會儲存在 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器實例中名為 azure_sys 的資料庫。
重要
請勿修改 azure_sys 資料庫或其架構。 這麼做可防止 查詢存放區和相關效能功能正常運作。
啟用 查詢存放區
查詢存放區 適用於所有區域，不需額外費用。 這是加入功能，因此它預設不會在伺服器上啟用。 針對指定伺服器上的所有資料庫，可以全域啟用或停用查詢存放區，而且無法針對每個資料庫開啟或關閉。
重要
請勿在高載定價層上啟用 查詢存放區，因為這會造成效能影響。
在 Azure 入口網站 中啟用 查詢存放區
- 登入 Azure 入口網站，然後選取您的 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器實例。
- 在功能表的 [設定] 區段中選取 [伺服器參數]。
- 搜尋
pg_qs.query_capture_mode參數。 - 將值設定為
TOP或ALL，視您是否要追蹤最上層查詢或巢狀查詢（在函式或程式內執行的查詢），然後按兩下 [ 儲存]。 允許最多 20 分鐘，讓第一批數據保存在azure_sys資料庫中。
啟用 查詢存放區 等候取樣
- 搜尋
pgms_wait_sampling.query_capture_mode參數。 - 將值設定為
ALL，並將 [儲存]。
查詢存放區 中的資訊
查詢存放區 由兩家商店組成：
- 用來保存查詢執行統計數據資訊的運行時間統計數據存放區。
- 保存等候統計數據資訊的等候統計數據存放區。
使用 查詢存放區 的常見案例包括：
- 判斷在指定時間範圍中執行查詢的次數。
- 比較跨時間時段查詢的平均運行時間，以查看大型差異。
- 識別過去幾個小時內執行時間最長的查詢。
- 識別正在等候資源的前 N 個查詢。
- 瞭解特定查詢的等候本質。
為了將空間使用量降到最低，運行時間統計數據存放區中的運行時間執行統計數據會透過固定且可設定的時間範圍匯總。 您可以使用檢視來查詢這些存放區中的資訊。
存取 查詢存放區 資訊
查詢存放區 資料會儲存在 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器實例azure_sys資料庫中。 下列查詢會傳回 查詢存放區 中查詢的相關信息：
SELECT * FROM query_store.qs_view;
或此查詢等候統計資料：
SELECT * FROM query_store.pgms_wait_sampling_view;
尋找等候查詢
Wait 事件類型會以相似度將不同的等候事件合併到貯體中。 查詢存放區 提供等候事件類型、特定等候事件名稱和有問題的查詢。 能夠將此等候資訊與查詢運行時間統計數據相互關聯，表示您可以更深入瞭解對查詢效能特性的貢獻。
以下是一些範例，說明如何使用 查詢存放區 中的等候統計數據，深入瞭解工作負載：
| 觀察 | 動作 |
|---|---|
| 高鎖定等候 | 查看受影響查詢的查詢文字，並找出目標實體。 查看 查詢存放區 以修改相同實體的其他查詢，這會經常執行且/或持續時間很高。 找出這些查詢之後，請考慮變更應用程式邏輯，改善並行存取，或使用限制較少的隔離等級。 |
| 高緩衝區 IO 等候 | 在查詢存放區中尋找實體讀取次數高的查詢。 如果它們符合高 IO 等候的查詢，請考慮在基礎實體上引進索引，以便進行搜尋，而不是掃描。 這會將查詢的 IO 額外負荷降到最低。 檢查入口網站中伺服器的效能 建議，以查看此伺服器是否有索引建議可優化查詢。 |
| 高記憶體等候 | 找出查詢存放區中記憶體耗用量名列前茅的查詢。 這些查詢可能會進一步延遲受影響查詢的進度。 檢查入口網站中伺服器的效能 建議，以查看是否有索引建議可優化這些查詢。 |
設定選項
啟用 查詢存放區 時，它會以伺服器參數決定pg_qs.interval_length_minutes的匯總時段儲存資料（預設值為 15 分鐘）。 針對每個視窗，它會為每個視窗儲存500個不同的查詢。
下列選項可用於設定 查詢存放區 參數：
| 參數 | 說明 | Default | 範圍 |
|---|---|---|---|
| pg_qs.query_capture_mode | 設定追蹤的語句。 | none | none、top、all |
| pg_qs.interval_length_minutes ≦ | 以分鐘為單位設定pg_qs的query_store擷取間隔 - 這是數據持續性的頻率。 | 15 | 1 - 30 |
| pg_qs.store_query_plans | 開啟或關閉pg_qs的查詢計劃。 | 關閉 | 開啟、關閉 |
| pg_qs.max_plan_size | 設定要針對查詢計劃文字儲存的位元組數目上限，pg_qs;較長的計劃將會被截斷。 | 7500 | 100 - 10k |
| pg_qs.max_query_text_length | 設定可以儲存的最大查詢長度;將會截斷較長的查詢。 | 6000 | 100 - 10K |
| pg_qs.retention_period_in_days | 設定 pg_qs 的保留期間期間- 在此時間之後，將會刪除數據。 | 7 | 1 - 30 |
| pg_qs.index_generation_interval ≦ | 以分鐘為單位設定query_store索引自動產生間隔，以分鐘為單位pg_qs。 | 720 | 15 - 10080 |
| pg_qs.index_recommendations | 啟用或停用索引建議。 pg_qs.query_capture_mode 也必須是 'TOP' 或 'ALL'。 | 關閉 | off，建議 |
| pg_qs.track_utility | 設定公用程式命令是否由pg_qs追蹤。 | on | 開啟、關閉 |
(*)靜態伺服器參數，需要伺服器重新啟動，才能讓其值變更生效。
下列選項特別適用於等候統計資料：
| 參數 | 說明 | Default | 範圍 |
|---|---|---|---|
| pgms_wait_sampling.query_capture_mode | 選取pgms_wait_sampling延伸模組所追蹤的語句。 | none | 無，全部 |
| Pgms_wait_sampling.history_period | 設定取樣等候事件的頻率，以毫秒為單位。 | 100 | 1-600000 |
注意
pg_qs.query_capture_mode 會取代 pgms_wait_sampling.query_capture_mode。 如果 pg_qs.query_capture_mode 為 NONE，pgms_wait_sampling.query_capture_mode 設定沒有任何作用。
使用 Azure 入口網站 來取得或設定參數的不同值。
檢視和函式
使用下列檢視和函式檢視和管理 查詢存放區。 PostgreSQL 公用角色中的任何人都可以使用這些檢視來查看 查詢存放區 中的數據。 這些檢視僅適用於 azure_sys 資料庫中。
查詢的正規化方式是查看其結構，並忽略任何語意上不重要的專案，例如常值、常數、別名或大小寫的差異。
如果兩個查詢在語意上相同，即使它們針對相同的參考數據行和數據表使用不同的別名，它們也會以相同的query_id來識別。 如果兩個查詢只與它們中使用的常值不同，它們也會以相同的query_id來識別。 針對以相同query_id識別的所有查詢，其sql_query_text會是自 查詢存放區 開始錄製活動之後先執行的查詢，或因為執行函式query_store.qs_reset 而最後一次捨棄保存的數據。
查詢正規化的運作方式
以下是一些示範此正規化運作方式的範例：
假設您使用下列語句建立資料表：
create table tableOne (columnOne int, columnTwo int);
您可以開啟 查詢存放區 資料收集，而單一或多個使用者會依此確切順序執行下列查詢：
select * from tableOne;
select columnOne, columnTwo from tableOne;
select columnOne as c1, columnTwo as c2 from tableOne as t1;
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one";
所有先前的查詢都會共用相同的query_id。 而 查詢存放區 保留的文字，就是啟用數據收集之後執行的第一個查詢。 因此，它會是 select * from tableOne;。
下列一組查詢一旦正規化，就不符合先前的一組查詢，因為 WHERE 子句會使其語意上不同：
select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
select * from tableOne where columnOne = -3 and columnTwo = -3;
select columnOne, columnTwo from tableOne where columnOne = '5' and columnTwo = '5';
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = 7 and columnTwo = 7;
不過，這個最後一個集合中的所有查詢都會共用相同的query_id，而用來識別這些查詢的文字全都是批次 select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;中的第一個查詢。
最後，在以下尋找一些查詢不符合上一個批次中這些查詢的query_id，以及它們不相符的原因：
查詢：
select columnTwo as c2, columnOne as c1 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
不相符的原因：數據行清單是指相同的兩個數據行（columnOne 和 ColumnTwo），但參考的順序會反轉，從 columnOne, ColumnTwo 上一個批次 ColumnTwo, columnOne 到此查詢中的順序。
查詢：
select * from tableOne where columnTwo = 25 and columnOne = 25;
不相符的原因：參考 WHERE 子句中評估的運算式順序會從 columnOne = ? and ColumnTwo = ? 上一個批次中的 反轉為 ColumnTwo = ? and columnOne = ? 此查詢中的 。
查詢：
select abs(columnOne), columnTwo from tableOne where columnOne = 12 and columnTwo = 21;
不相符的原因：數據行清單中的第一個表達式已不再columnOne，而是abs透過 （abs(columnOne)） 評估函columnOne式，這在語意上不相等。
查詢：
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = ceiling(16) and columnTwo = 16;
不相符的原因：WHERE 子句中的第一個表達式不會再評估與常值相等 columnOne ，但函式的結果 ceiling 在常值上評估，這在語意上並不相等。
檢視
query_store.qs_view
此檢視會傳回已在支持資料表中保存的所有數據，查詢存放區。 目前使用時間範圍在記憶體中記錄的數據，在時間範圍結束之前不會顯示，而且其記憶體內部動態數據會收集並保存至儲存在磁碟上的數據表。 此檢視會針對每個不同的資料庫 （db_id）、使用者 （user_id） 和查詢 （query_id） 各傳回不同的數據列。
| 名稱 | 類型 | 參考 | 說明 |
|---|---|---|---|
| runtime_stats_entry_id | bigint | runtime_stats_entries數據表中的標識碼。 | |
| user_id | oid | pg_authid.oid | 執行語句的使用者 OID。 |
| db_id | oid | pg_database.oid | 執行語句的資料庫 OID。 |
| query_id | bigint | 從語句剖析樹狀結構計算的內部哈希程序代碼。 | |
| query_sql_text | varchar（10000） | 代表聲明的文字。 具有相同結構的不同查詢會叢集在一起;此文字是叢集中第一個查詢的文字。 查詢文字長度上限的預設值為 6000，而且可以使用查詢存放區參數 pg_qs.max_query_text_length進行修改。 如果查詢的文字超過這個最大值，則會截斷為第一 pg_qs.max_query_text_length 個字元。 |
|
| plan_id | bigint | 對應至此查詢之計劃的識別碼。 | |
| start_time | timestamp | 查詢會依時間範圍匯總，其時間範圍是由伺服器參數 pg_qs.interval_length_minutes 定義（預設值為15分鐘）。 這是與這個項目的時間範圍對應的開始時間。 |
|
| end_time | timestamp | 對應至這個專案之時間範圍的結束時間。 | |
| 通話 | bigint | 在此時間範圍中執行的查詢次數。 請注意，針對平行查詢，每個執行的呼叫數目會對應至 1，用於驅動查詢執行的後端進程，再加上每個後端背景工作進程的其他許多單位，都會啟動以共同作業執行執行樹狀結構的平行分支。 | |
| total_time | double precision | 查詢運行時間總計，以毫秒為單位。 | |
| min_time | double precision | 最小查詢運行時間，以毫秒為單位。 | |
| max_time | double precision | 查詢運行時間上限，以毫秒為單位。 | |
| mean_time | double precision | 平均查詢運行時間，以毫秒為單位。 | |
| stddev_time | double precision | 查詢運行時間的標準偏差，以毫秒為單位。 | |
| rows | BIGINT | 擷取或受 語句影響的數據列總數。 請注意，對於平行查詢，每個執行的數據列數目會對應至後端進程驅動查詢執行的後端進程傳回給客戶端的數據列數目，加上每個後端背景工作進程啟動以共同作業執行執行樹狀結構的平行分支，並傳回至驅動後端進程。 | |
| shared_blks_hit | bigint | 語句所叫用的共享區塊快取總數。 | |
| shared_blks_read | bigint | 語句所讀取的共享區塊總數。 | |
| shared_blks_dirtied | bigint | 語句所擷取的共享區塊總數。 | |
| shared_blks_written | bigint | 語句所寫入的共享區塊總數。 | |
| local_blks_hit | bigint | 語句叫用的本機區塊快取總數。 | |
| local_blks_read | bigint | 語句所讀取的本機區塊總數。 | |
| local_blks_dirtied | bigint | 語句所擷取的本機區塊總數。 | |
| local_blks_written | bigint | 語句所寫入的本機區塊總數。 | |
| temp_blks_read | bigint | 語句所讀取的暫存區塊總數。 | |
| temp_blks_written | bigint | 語句所寫入的暫存區塊總數。 | |
| blk_read_time | double precision | 語句所花費讀取區塊的總時間，以毫秒為單位（如果已啟用track_io_timing，則為零）。 | |
| blk_write_time | double precision | 語句所花費寫入區塊的總時間，以毫秒為單位（如果已啟用track_io_timing，則為零）。 | |
| is_system_query | boolean | 判斷查詢是否由具有user_id = 10 的角色執行，其具有超級使用者許可權，並且用來執行控制窗格作業。 由於此服務是受控 PaaS 服務，因此只有 Microsoft 是該超級使用者角色的一部分。 | |
| query_type | text | 查詢所代表的作業類型。 可能的值為 unknown、、select、、update、deleteinsert、utilitymerge、、 nothingundefined。 |
query_store.query_texts_view
此檢視會傳回 查詢存放區 中的查詢文字數據。 每個相異query_sql_text各有一個數據列。
| 名稱 | 類型 | 說明 |
|---|---|---|
| query_text_id | bigint | query_texts數據表的標識碼 |
| query_sql_text | varchar（10000） | 代表聲明的文字。 具有相同結構的不同查詢會叢集在一起;此文字是叢集中第一個查詢的文字。 |
| query_type | smallint | 查詢所代表的作業類型。 在 PostgreSQL <= 14 版本中，可能的值為 0 （未知）、 1 （選取）、 2 （更新）、（插入）、 43 （刪除）、（公用程式）、 56 （無）。 在 PostgreSQL >= 15 版本中，可能的值為 0 （未知）、 1 （選取）、 2 （更新）、（插入 4 ）、 3 （刪除 5 ）、（合併）、（公用程式）、 67 （無）。 |
query_store.pgms_wait_sampling_view
此檢視會傳回 查詢存放區 中的等候事件數據。 此檢視會針對每個不同資料庫 （db_id）、使用者 （user_id）、查詢 （query_id） 和事件 （event） 各傳回不同的數據列。
| 名稱 | 類型 | 參考 | 說明 |
|---|---|---|---|
| start_time | timestamp | 查詢會依時間範圍匯總，其時間範圍是由伺服器參數 pg_qs.interval_length_minutes 定義（預設值為15分鐘）。 這是與這個項目的時間範圍對應的開始時間。 |
|
| end_time | timestamp | 對應至這個專案之時間範圍的結束時間。 | |
| user_id | oid | pg_authid.oid | 執行語句的使用者 OID。 |
| db_id | oid | pg_database.oid | 執行語句的資料庫 OID。 |
| query_id | bigint | 從語句剖析樹狀結構計算的內部哈希程序代碼。 | |
| event_type | text | 後端正在等候的事件類型。 | |
| event | text | 如果後端目前正在等候，則為等候事件名稱。 | |
| 通話 | 整數 | 擷取相同事件的次數。 |
注意
如需 query_store.pgms_wait_sampling_view 檢視event_type和事件數據行中可能值的清單，請參閱pg_stat_activity的官方檔，並尋找參考相同名稱之數據行的資訊。
query_store.query_plans_view
此檢視會傳回用來執行查詢的查詢計劃。 每個相異資料庫標識碼和查詢標識元各有一個數據列。 這隻會儲存非使用量查詢的查詢計劃。
| plan_id | db_id | query_id | plan_text |
|---|---|---|---|
| plan_id | bigint | EXPLAIN 所產生的標準化查詢計劃哈希值。 它被視為正規化，因為它會排除計劃節點的估計成本，以及緩衝區的使用方式。 | |
| db_id | oid | pg_database.oid | 執行語句的資料庫 OID。 |
| query_id | bigint | 從語句剖析樹狀結構計算的內部哈希程序代碼。 | |
| plan_text | varchar（10000） | 指定 costs=false、buffers=false 和 format=text 的語句執行計劃。 這是 EXPLAIN 所提供的相同輸出。 |
函式
query_store.qs_reset
此函式會捨棄到目前為止收集的所有統計數據，查詢存放區。 它會針對已經關閉的時間範圍捨棄統計數據，這些統計數據已保存在磁碟數據表上，以及目前時間範圍中仍保留在記憶體中的統計數據。 此函式只能由伺服器管理員角色執行（azure_pg_admin）。
query_store.staging_data_reset
此函式會捨棄記憶體中 查詢存放區 收集的所有統計數據（也就是說，記憶體中的數據尚未排清到磁碟數據表上，以支援 查詢存放區 所收集數據的持續性）。 此函式只能由伺服器管理員角色執行（azure_pg_admin）。
限制和已知問題
Azure 儲存體和 查詢存放區 相容性
由於相容性問題，您無法同時啟用 Azure 儲存體和 查詢存放區 擴充功能。 若要確保正常運作並避免潛在的衝突，請一次只啟用其中一個擴充功能。
若要使用 Azure 儲存體：
- 將 參數
pg_qs.query_capture_mode設定為NONE，以停用 查詢存放區。 此參數是動態的，因此您不需要重新啟動。
若要使用 查詢存放區：
- 發出
DROP EXTENSION azure_storage;來停用 Azure 儲存體 延伸模組。 - 從
shared_preload_libraries移除 Azure 儲存體。 - 重新啟動資料庫伺服器。
這些步驟是防止衝突的必要步驟，並確保系統正常運作。 我們正努力解決這些相容性問題，並讓您隨時掌握任何更新。
唯讀模式
當 適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器實例處於唯讀模式時，例如default_transaction_read_only當 參數設定on為 時，或因為達到儲存容量而自動啟用只讀模式，查詢存放區 不會擷取任何數據。