[SQL Server] Query Store Setting | 當Eva搭上DBA航班之後 - 點部落

2022-12-01

[SQL Server] Query Store Setting

啟用Query Store前需要研究的各種設定。

General

Operation Mode:

READ_WRITE 模式，查詢存放區會收集並保存查詢計劃和執行階段執行統計資料資訊。
READ_ONLY 模式，可以從查詢存放區讀取資訊，但不會加入新資訊。

Monitoring

Data Flush Interval(Min):

將寫入查詢存放區的資料保存到磁碟的頻率。以非同步寫入
在 Transact-SQL 中則是以秒表示。預設值為 900 秒，在圖形化使用者介面即 15 分鐘。
如果您的工作負載不會產生大量不同的查詢與計劃，或您可以在資料庫關閉之前承受較長的資料保存時間
考慮使用較高的值
- 若將此選項設定過低，會太頻繁寫入磁碟，影響SQL效能
- 若設定太高，會保留過多Plan在Memory，有可能讓Memory崩潰

ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

Statistics Collection interval:

定義所收集執行階段統計資料的資料粒度層級 (以分鐘表示)。預設值是 60 分鐘。如果您需要更精細的資料粒度或使用較短時間來偵測與解決問題，請考慮使用較低的值。
它會直接影響查詢存放區資料的大小。

ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

Query Store Retention

Max Plans Per Query:

設置每個查詢的最大執行計劃數量。
預設為每個查詢有 200 個執行計劃。

Max Size (MB): 預設1GB

指定查詢存放區可在您資料庫內使用的資料空間限制。這是最重要的設定，它會直接影響查詢存放區的作業模式。
如果已用到了查詢存放區的配置空間上限，查詢存放區就會將其作業模式變更為 READ_ONLY，並會停止收集新的Plan and Statistics
啟用後，查看目前使用size大小

select current_storage_size_mb, max_storage_size_mb from sys.database_query_store_options

Query Store Capture Mode

ALL: 全部都搜
Auto: SQL自動判別那些要搜，會忽略不常執行的Query
None: 不會蒐集新的Query訊息，只會繼續收集之前記錄過的Query
Custom: 自訂

Size Based Cleanup Mode

控制當總資料量接近上限時是否會自動清除
Auto:當Query store的資料達到Max Size的90%就會自動清理最舊且成本最低的資料，清到容量的80%就會停止清理
Off: 當Query store的資料達到Max Size的90%，就會轉去ReadOnly Mode

Stale Query Threshold(Days)

Query Store裡的資料保留多長時間，預設是30天
如果不須30天建議調降

Query Store Capture Mode

Execution Count:

某個查詢在分配的Stale Threshold中必須達到的執行次數。
默認值為 30次以上才會抓plan

Stale Threshold: 在這個時間內....

Total Compile CPU Time (ms): 在分配的Stale Threshold中編譯查詢所花費的總時間。

Total Execution CPU Time (ms): 在分配的Stale Threshold中執行查詢所花費的總 CPU 時間。

Reference:

SQL Server

回首頁