查詢微調建議

有些查詢會比其他查詢耗用更多的資源。例如，會傳回大量結果集的查詢，以及包含非唯一之 WHERE 子句的查詢，總是會耗用大量的資源。與較不複雜的查詢相比，即使查詢最佳化工具再聰明，也無法除去這些建構的資源成本。SQL Server 會使用最佳的存取計畫，但查詢的最佳化受限於可能的情況。

然而，若要增進查詢效能，您可以：

• 增加更多記憶體。若伺服器執行許多複雜的查詢，而其中有幾項查詢執行得很慢，這個解決方法會特別有用。

• 使用多個處理器。多個處理器可以讓 Database Engine 利用平行查詢。如需詳細資訊，請參閱＜平行查詢處理＞。

• 重寫查詢。請考量下列問題：

  • 如果查詢使用資料指標，請判斷是否可使用較有效率的資料指標類型 (例如：僅限向前快轉) 或單一查詢，來撰寫資料指標查詢。單一查詢的執行速度一般會快於資料指標作業。因為一組資料指標陳述式多半是外部迴圈作業，在這種情況下，外部迴圈中的每一列都會使用內部陳述式執行一次，所以請考慮改用 GROUP BY 或 CASE 陳述式，或是子查詢。如需詳細資訊，請參閱＜資料指標類型 (Database Engine)＞與＜查詢基本原理＞。

  • 如果應用程式使用迴圈的話，可考慮將迴圈放在查詢內部。應用程式往往會包含迴圈，迴圈中包含參數型查詢，這會執行好幾次，並且需要在執行應用程式的電腦與 SQL Server 之間做一次網路往返。請改用暫存資料表建立單一且比較複雜的查詢。這樣就只需要一次網路往返，而且查詢最佳化工具能夠更為最佳化單一查詢。如需詳細資訊，請參閱＜程序 Transact-SQL＞與＜Transact-SQL 變數＞。

  • 不要在一個查詢中為單一資料表使用多個別名，來模擬索引交集。現在已經不需要這麼做了，因為 SQL Server 會自動考量索引交集，而且可以在同一個查詢中利用同一個資料表的多個索引。請考量下列範例查詢：

    SELECT * FROM lineitem 
    WHERE partkey BETWEEN 17000 AND 17100 AND
        shipdate BETWEEN '1/1/1994' AND '1/31/1994'
    

    SQL Server 可以同時利用 partkey 與 shipdate 資料行上的索引，然後在兩個子集間執行雜湊比對，以取得索引交集。

  • 使用查詢參數化來允許重複使用快取的查詢執行計畫。如果有一組查詢具有相同的查詢雜湊和查詢計畫雜湊，您可能會透過建立一個參數化查詢，改善效能。呼叫含有參數的單一查詢而非含有常值的多個查詢可以重複使用快取的查詢執行計畫。如需詳細資訊，請參閱＜使用查詢和查詢計畫雜湊來尋找並微調類似的查詢＞和＜執行計畫快取與重複使用＞。

    如果您無法修改應用程式，可以使用具有強制參數化的範本計畫指南來達成相同的結果。如需詳細資訊，請參閱＜使用計畫指南指定查詢參數化行為＞。

  • 只在必要時才使用查詢提示。若對舊版 SQL Server 執行的查詢使用提示，應測試不指定提示會有何差別。提示可能會使查詢最佳化工具無法選擇比較好的執行計畫。如需詳細資訊，請參閱＜SELECT (Transact-SQL)＞。

• 使用 query_plan_hash 來擷取、儲存和比較經過一段時間之查詢的查詢執行計畫。例如，變更系統組態之後，您可以比較關鍵任務查詢的查詢計畫雜湊值與其原始的查詢計畫雜湊值。查詢計畫雜湊值的差異可以告訴您系統組態變更是否針對重要的查詢產生了更新的查詢執行計畫。如果 sys.dm_exec_requests 中的查詢計畫雜湊與其基準查詢計畫雜湊不同 (表示提升效能)，您可能也會決定停止執行目前長時間執行的查詢。如需詳細資訊，請參閱＜使用查詢和查詢計畫雜湊來尋找並微調類似的查詢＞。

• 運用 query governor 組態選項。query governor 組態選項可用來防止長時間執行的查詢耗費系統資源。根據預設，該選項的設定是無論查詢會花多少時間，都會執行所有查詢。然而，您可以設定 query governor 來限制所有連接的所有查詢可執行的最大秒數，也可以只限制特定連接的查詢。因為 query governor 是以估計的查詢成本為基礎，而不是根據實際的經過時間，所以它沒有執行時期額外負擔。它也會在長時間執行的查詢開始前，就加以阻止，而不是等它們執行到預先定義的限制時才將它們中斷。如需詳細資訊，請參閱＜查詢管理員成本限制選項＞和＜SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL)＞。

• 從計畫快取中讓查詢計畫的重複使用最佳化。Database Engine 會快取查詢計畫，盡可能地加以重複使用。如果沒有快取某個查詢計畫，就不能重複使用該查詢計畫。而且，每次執行未快取的查詢計畫時，都必須加以編譯，因此導致效能降低。下列 Transact-SQL SET 陳述式選項會防止重複使用快取的查詢計畫。將這些 SET 選項設為 ON 的 Transact-SQL 批次，其查詢計畫都不能分享給將這些 SET 選項設為 OFF 並進行編譯的相同批次。

  SET ANSI_NULL_DFLT_OFF	SET ANSI_NULL_DFLT_ON
  SET ANSI_NULLS	SET ANSI_PADDING
  SET ANSI_WARNINGS	SET ARITHABORT
  SET CONCAT_NULL_YIELDS_NULL	SET DATEFIRST
  SET DATEFORMAT	SET FORCEPLAN
  SET LANGUAGE	SET NO_BROWSETABLE
  SET NUMERIC_ROUNDABORT	SET QUOTED_IDENTIFIER
  SET TEXTSIZE

  此外，SET ANSI_DEFAULTS 選項會影響快取查詢計畫的重複使用，因為它可以用來變更 ANSI_NULLS、ANSI_NULL_DFLT_ON、ANSI_PADDING、ANSI_WARNINGS、CURSOR_CLOSE_ON_COMMIT、IMPLICIT_TRANSACTIONS 及 QUOTED_IDENTIFIER SET 選項。附註：大部分可以用 SET ANSI_DEFAULTS 來變更的 SET 選項，都被視為會影響查詢計畫重複使用的 SET 選項。

  您可以使用下列方式來變更其中某些 SET 選項：

  • 使用 sp_configure 預存程序來進行伺服器面的變更。如需詳細資訊，請參閱＜sp_configure (Transact-SQL)＞。

  • 使用 ALTER DATABASE 陳述式的 SET 子句。如需詳細資訊，請參閱＜ALTER DATABASE (Transact-SQL)＞

  • 變更 OLE DB 與 ODBC 連接設定。如需詳細資訊，請參閱＜用戶端網路組態＞。

[!附註]

為了避免 SET 選項導致重複編譯查詢計畫，請在連接時建立 SET 選項，並確定在連接期間，SET 選項沒有變更。有些 SET 選項必須設為特定值，才能使用索引檢視，或是在計算的資料行上編製索引。如需詳細資訊，請參閱＜影響結果的 SET 選項＞。

請參閱

參考

CASE (Transact-SQL)

概念

子查詢基本原則