查詢存放區的使用案例Usage scenarios for Query Store

適用範圍: Azure Database for PostgreSQL-9.6] 和 [10 的單一伺服器Applies to: Azure Database for PostgreSQL - Single Server 9.6 and 10

您可以在各種不同的案例中使用查詢存放區,在這些案例中追蹤和維護可預測的工作負載效能至關重要。You can use Query Store in a wide variety of scenarios in which tracking and maintaining predictable workload performance is critical. 請考量下列範例:Consider the following examples:

  • 識別並調整前幾個成本昂貴的查詢Identifying and tuning top expensive queries
  • A/B 測試A/B testing
  • 在升級期間保持效能穩定Keeping performance stable during upgrades
  • 識別並改善臨機操作工作負載Identifying and improving ad hoc workloads

識別並調整成本昂貴的查詢Identify and tune expensive queries

識別長時間執行的查詢Identify longest running queries

使用 Azure 入口網站中的查詢效能見解檢視,即可快速識別執行時間最長的查詢。Use the Query Performance Insight view in the Azure portal to quickly identify the longest running queries. 這些查詢通常會消耗大量資源。These queries typically tend to consume a significant amount resources. 最佳化執行時間最長的問題可提高效能,方法是釋放資源以供系統上執行的其他查詢使用。Optimizing your longest running questions can improve performance by freeing up resources for use by other queries running on your system.

使用效能差異鎖定查詢Target queries with performance deltas

查詢存放區會將效能資料分割成時間範圍,讓您能夠追蹤一段時間內的查詢效能。Query Store slices the performance data into time windows, so you can track a query's performance over time. 這可協助您確切識別哪些查詢會增加所花費的整體時間。This helps you identify exactly which queries are contributing to an increase in overall time spent. 如此一來,您就可以對工作負載進行具有針對性的疑難排解。As a result you can do targeted troubleshooting of your workload.

調整成本昂貴的查詢Tuning expensive queries

當您識別效能欠佳的查詢時,您要採取的動作取決於問題的本質:When you identify a query with suboptimal performance, the action you take depends on the nature of the problem:

  • 使用效能建議來判斷是否有任何建議的索引。Use Performance Recommendations to determine if there are any suggested indexes. 若有,請建立索引,然後在建立索引之後使用查詢存放區來評估查詢效能。If yes, create the index, and then use Query Store to evaluate query performance after creating the index.
  • 針對查詢所使用的基礎資料表,確定其中的統計資料為最新。Make sure that the statistics are up-to-date for the underlying tables used by the query.
  • 請考慮重新撰寫成本昂貴的查詢。Consider rewriting expensive queries. 例如,利用查詢參數化並減少使用動態 SQL。For example, take advantage of query parameterization and reduce use of dynamic SQL. 在讀取資料時實作最佳邏輯,例如在資料庫端上套用資料篩選,而不是在應用程式端上套用。Implement optimal logic when reading data like applying data filtering on database side, not on application side.

A/B 測試A/B testing

使用查詢存放區,可比較您計劃引進應用程式變更之前和之後的工作負載效能。Use Query Store to compare workload performance before and after an application change you plan to introduce. 使用查詢存放區來評估環境或應用程式變更對工作負載效能所造成影響的案例範例如下:Examples of scenarios for using Query Store to assess the impact of the environment or application change to workload performance:

  • 推出新版本的應用程式。Rolling out a new version of an application.
  • 在伺服器上新增額外的資源。Adding additional resources to the server.
  • 在成本昂貴查詢所參考資料表上建立遺漏的索引。Creating missing indexes on tables referenced by expensive queries.

在上述任一案例中,套用下列工作流程:In any of these scenarios, apply the following workflow:

  1. 在計劃性變更之前使用查詢存放區執行您的工作負載,以產生效能基準。Run your workload with Query Store before the planned change to generate a performance baseline.
  2. 在受控制的時間點套用應用程式變更。Apply application change(s) at the controlled moment in time.
  3. 在變更之後繼續執行工作負載足夠長的時間,以產生系統的效能影像。Continue running the workload long enough to generate performance image of the system after the change.
  4. 比較變更之前和之後的結果。Compare results from before and after the change.
  5. 決定是要保留變更還是復原。Decide whether to keep the change or rollback.

識別並改善臨機操作工作負載Identify and improve ad hoc workloads

某些工作負載沒有可調整的主控查詢,無法提高整體的應用程式效能。Some workloads do not have dominant queries that you can tune to improve overall application performance. 這些工作負載通常具有相對大量的唯一查詢,各個查詢都會耗用一部分的系統資源。Those workloads are typically characterized with a relatively large number of unique queries, each of them consuming a portion of system resources. 每個唯一查詢都不會經常執行,因此其個別執行階段耗用量並不重要。Each unique query is executed infrequently, so individually their runtime consumption is not critical. 另一方面,假設應用程式一直產生新的查詢,則絕大部分的系統資源會花費在查詢編譯,這不是最佳狀況。On the other hand, given that the application is generating new queries all the time, a significant portion of system resources is spent on query compilation, which is not optimal. 通常,如果您的應用程式會產生查詢 (而不是使用預存程序或參數化查詢),或者它依賴於預設會產生查詢的物件關聯式對應架構,就會發生這種情況。Usually, this situation happens if your application generates queries (instead of using stored procedures or parameterized queries) or if it relies on object-relational mapping frameworks that generate queries by default.

如果您能夠掌控應用程式程式碼,則可以考慮重新撰寫資料存取層來使用預存程序或參數化查詢。If you are in control of the application code, you may consider rewriting the data access layer to use stored procedures or parameterized queries. 不過,您也可以改善這種情況而無需變更應用程式,只要使用相同的查詢雜湊,針對整個資料庫 (所有查詢) 或個別查詢範本強制執行查詢參數化即可。However, this situation can be also improved without application changes by forcing query parameterization for the entire database (all queries) or for the individual query templates with the same query hash.

後續步驟Next steps