SQL Server 2008 查詢優化

某些查詢佔用的資源比其他查詢佔用的資源多。例如，返回大型結果集的查詢和那些包含 WHERE 子句（並非唯一子句）的查詢總是佔用大量資源。與不太複雜的查詢相比，查詢優化器的智慧水平無法消除這些構造的資源開銷。SQL Server 使用最佳訪問計劃，但查詢優化會受到可訪問內容的限制。

儘管如此，您可以執行下列操作來提高查詢效能：

新增更多記憶體。當伺服器執行許多複雜查詢且其中幾個查詢執行很慢時，此解決方案尤其有用。

使用多個處理器。多個處理器允許資料庫引擎使用並行查詢。有關詳細資訊，請參閱並行查詢處理。

重寫查詢。請注意下列事項：

如果查詢使用遊標，則確定是否可以使用效率更高的遊標型別（如快速只進遊標）或單個查詢編寫遊標查詢。單個查詢的效能通常優於遊標操作。因為一組遊標語句通常是一個外迴圈操作，在此操作中，一旦使用內部語句便開始處理外迴圈中的每一行，所以可考慮使用 GROUP BY 或 CASE 語句，或者使用子查詢來替代。有關詳細資訊，請參閱遊標型別（資料庫引擎）和查詢基礎知識。

如果應用程式使用迴圈，可考慮將迴圈放入查詢內。應用程式經常包含帶引數化查詢的迴圈，該迴圈執行許多次並要求執行應用程式的計算機與 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 cost limit 選項和 SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL)。

通過計劃快取優化查詢計劃的重新使用。資料庫引擎對查詢計劃進行快取以備重新使用。如果查詢計劃不進行快取，則永遠不能重新使用。然而，每次執行未快取的查詢計劃時，必須對其進行編譯，這就導致效能降低。下列 Transact-SQL SET 語句選項可阻止重新使用已快取的查詢計劃。包含這些處於 ON 狀態的 SET 選項的 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 選項。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-520793/，如需轉載，請註明出處，否則將追究法律責任。