SQLServer 參數化查詢經驗分享

什麼是參數化查詢?
　　一個簡單理解參數化查詢的方式是把它看做只是一個T-SQL查詢，它接受控制這個查詢返回什麼的參數。通過使用不同的參數，一個參數化查詢返回不同的結果。要獲得一個參數化查詢，你需要以一種特定的方式來編寫你的代碼，或它需要滿足一組特定的標準。

　　有兩種不同的方式來建立參數化查詢。第一個方式是讓查詢最佳化工具自動地參數化你的查詢。另一個方式是通過以一個特定方式來編寫你的T-SQL代碼，並將它傳遞給sp_executesql系統預存程序，從而編程一個參數化查詢。這篇文章的後面部分將介紹這個方法。

　　參數化查詢的關鍵是查詢最佳化工具將建立一個可以重用的緩衝計劃。通過自動地或編程使用參數化查詢，SQL Server可以最佳化類似T-SQL語句的處理。這個最佳化消除了對使用高貴資源為這些類似T-SQL語句的每一次執行建立一個緩衝計劃的需求。而且通過建立一個可重用計劃，SQL Server還減少了存放過程緩衝中類似的執行計畫所需的記憶體使用量。

　　現在讓我們看看使得SQL Server建立參數化查詢的不同方式。

　　參數化查詢是怎樣自動建立的?

　　微軟編寫查詢最佳化工具代碼的人竭盡全力地最佳化SQL Server處理你的T-SQL命令的方式。我想這是查詢最佳化工具名稱的由來。這些盡量減少資源和最大限度地提高查詢最佳化工具執行效能的方法之一是查看一個T-SQL語句並確定它們是否可以被參數化。要瞭解這是如何工作的，讓我們看看下面的T-SQL語句：

SELECT * 　　FROM AdventureWorks.Sales.SalesOrderHeader 　　WHERE SalesOrderID = 56000; 　　GO

在這裡，你可以看到這個命令有兩個特點。第一它簡單，第二它在WHERE謂詞中包含一個用於SalesOrderID值的指定值。查詢最佳化工具可以識別這個查詢比較簡單以及SalesOrderID有一個參數(“56000”)。因此，查詢最佳化工具可以自動地參數化這個查詢。

　　如果你使用下面的SELECT語句來查看一個只包含用於上面語句的緩衝計劃的、乾淨的緩衝池，那麼你會看到查詢最佳化工具將T-SQL查詢重寫為一個參數化T-SQL語句：

SELECT stats.execution_count AS cnt, 　　p.size_in_bytes AS [size], 　　[sql].[text] AS [plan_text] 　　FROM sys.dm_exec_cached_plans p 　　OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 　　JOIN sys.dm_exec_query_stats stats 　　ON stats.plan_handle = p.plan_handle; 　　GO

當我在一個SQL Server 2008執行個體上運行這個命令時，我得到下面的輸出，(注意，輸出被重新格式化了，以便它更易讀)：

　　cnt size plan_text

　　--- ------- --------------------------------------------------------------

　　1 49152 (@1 int)SELECT * FROM [AdventureWorks].[Sales].[SalesOrderHeader]

　　WHERE [SalesOrderID]=@1

　　如果你看看上面輸出中的plan_text欄位，你會看到它不像原來的T-SQL文本。如前所述，查詢最佳化工具將這個查詢重新編寫為一個參數化T-SQL語句。在這裡，你可以看到它現在有一個資料類型為(int)的變數(@1)，它在之前的SELECT語句中被定義的。另外在plan_text的末尾， 值“56000”被替換為變數@1。既然這個T-SQL語句被重寫了，而且被儲存為一個緩衝計劃，那麼如果未來一個T-SQL命令和它大致相同，只有SalesOrderID欄位被賦的值不同的話，它就可以被用於重用。讓我們在動作中看看它。

如果我在我的機器上運行下面的命令：

DBCC FREEPROCCACHE; 　　GO 　　SELECT * 　　FROM AdventureWorks.Sales.SalesOrderHeader 　　WHERE SalesOrderID = 56000; 　　GO 　　SELECT * 　　FROM AdventureWorks.Sales.SalesOrderHeader 　　WHERE SalesOrderID = 56001; 　　GO 　　SELECT stats.execution_count AS cnt, 　　p.size_in_bytes AS [size], 　　[sql].[text] AS [plan_text] 　　FROM sys.dm_exec_cached_plans p 　　OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 　　JOIN sys.dm_exec_query_stats stats 　　ON stats.plan_handle = p.plan_handle; 　　GO 　　我從最後的SELECT語句得到下面的輸出，(注意，輸出被重新格式化以便它更易讀)： 　　cnt size plan_text 　　--- -------- -------------------------------------------------------------- 　　2 49152 (@1 int)SELECT * FROM AdventureWorks].[Sales].[SalesOrderHeader] 　　WHERE [SalesOrderID]=@1

在這裡，我首先釋放過程緩衝，然後我執行兩個不同、但卻類似的非參數化查詢來看看查詢最佳化工具是會建立兩個不同的緩衝計劃還是建立用於這兩個查詢的一個緩衝計劃。在這裡，你可以看到查詢最佳化工具事實上很聰明，它參數化第一個查詢並緩衝了計劃。然後當第二個類似、但有一個不同的SalesOrderID值的查詢發送到SQL Server時，最佳化器可以識別已經緩衝了一個計劃，然後重用它來處理第二個查詢。你可以這麼說是因為“cnt”欄位現在表明這個計劃被用了兩次。

　　資料庫配置選項PARAMETERIZATION可以影響T-SQL語句怎樣被自動地參數化。對於這個選項有兩種不同的設定，SIMPLE和FORCED。當PARAMETERIZATION設定被設定為SIMPLE時，只有簡單的T-SQL語句才會被參數化。要介紹這個，看下下面的命令：

SELECT SUM(LineTotal) AS LineTotal 　　FROM AdventureWorks.Sales.SalesOrderHeader H 　　JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID 　　WHERE H.SalesOrderID = 56000

這個查詢類似於我前面的樣本，除了在這裡我添加了一個額外的JOIN標準。當資料庫AdventureWorks的PARAMETERIZATION選項被設定為SIMPLE時，這個查詢不會被自動地參數化。SIMPLE PARAMETERIZATION設定告訴查詢最佳化工具只參數化簡單的查詢。但是當選項PARAMETERIZATION被設定為FORCED時，這個查詢將被自動地參數化。

　　當你設定資料庫選項為使用FORCE PARAMETERIZATION時，查詢最佳化工具試圖參數化所有的查詢，而不僅僅是簡單的查詢。你可能會認為這很好。但是在某些情況下，當資料庫設定PARAMETERIZATION為FORCED時，查詢最佳化工具將選擇不是很理想的查詢計劃。當資料庫設定PARAMETER為FORCED時，它改變查詢中的字面常量。這可能導致當查詢中涉及計算欄位時索引和索引檢視表不被選中參與到執行計畫中，從而導致一個無效的計劃。FORCED PARAMETERIZATION選項可能是改進具有大量類似的、傳遞過來的參數稍有不同的查詢的資料庫效能的一個很好的解決方案。一個線上銷售應用程式，它的客戶對你的產品執行大量的類似搜尋， 產品值不同，這可能是一個能夠受益於FORCED PARAMETERIZATION的很好的應用程式類型。

不是所有的查詢從句都會被參數化。例如查詢的TOP、TABLESAMPLE、 HAVING、GROUP BY、ORDER BY、OUTPUT...INTO或FOR XML從句不會被參數化。

　　使用sp_execute_sql來參數化你的T-SQL

　　你不需要依賴於資料庫的PARAMETERIZATION選項來使得查詢最佳化工具參數化一個查詢。你可以參數化你自己的查詢。你通過重新編寫你的T-SQL語句並使用“sp_executesql”系統預存程序執行重寫的語句來實現。正如已經看到的，上麵包括一個“JOIN”從句的SELECT語句在資料庫的PARAMETERIZATION設定為SIMPLE時沒有被自動參數化。讓我重新編寫這個查詢以便查詢最佳化工具將建立一個可重用的參數化查詢執行計畫。

　　為了說明，讓我們看兩個類似的、不會被自動參數化的T-SQL語句，並建立兩個不同的緩衝執行計畫。然後我將重新編寫這兩個查詢使得它們都使用相同的緩衝參數化執行計畫。

　　讓我們看看這個代碼：

DBCC FREEPROCCACHE 　　GO 　　SELECT SUM(LineTotal) AS LineTotal 　　FROM AdventureWorks.Sales.SalesOrderHeader H 　　JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID 　　WHERE H.SalesOrderID = 56000 　　GO 　　SELECT SUM(LineTotal) AS LineTotal 　　FROM AdventureWorks.Sales.SalesOrderHeader H 　　JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID 　　WHERE H.SalesOrderID = 56001 　　GO 　　SELECT stats.execution_count AS cnt, 　　p.size_in_bytes AS [size], 　　LEFT([sql].[text], 200) AS [plan_text] 　　FROM sys.dm_exec_cached_plans p 　　OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 　　JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle; 　　GO

在這裡，我釋放了過程緩衝，然後運行這兩個包含一個JOIN的、不同的非簡單的T-SQL語句。然後我將檢查緩衝計劃。這是這個使用DMV 的SELECT語句的輸出(注意，輸出被重新格式化了，以便它更易讀)：

cnt size plan_text 　　--- ----------- ------------------------------------------------------------------------------- 　　1 49152 SELECT SUM(LineTotal) AS LineTotal 　　FROM AdventureWorks.Sales.SalesOrderHeader H 　　JOIN AdventureWorks.Sales.SalesOrderDetail D 　　ON D.SalesOrderID = H.SalesOrderID 　　WHERE H.SalesOrderID = 56001 　　1 49152 SELECT SUM(LineTotal) AS LineTotal 　　FROM AdventureWorks.Sales.SalesOrderHeader H 　　JOIN AdventureWorks.Sales.SalesOrderDetail D 　　ON D.SalesOrderID = H.SalesOrderID 　　WHERE H.SalesOrderID = 56000

正如你從這個輸出看到的，這兩個SELECT語句沒有被查詢最佳化工具參數化。最佳化器建立了兩個不同的緩衝執行計畫，每一個都只被執行了一次。我們可以通過使用sp_executesql系統預存程序來協助最佳化器為這兩個不同的SELECT語句建立一個參數化執行計畫。
下面是上面的代碼被重新編寫來使用sp_executesql 系統預存程序：

DBCC FREEPROCCACHE; 　　GO 　　EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotal 　　FROM AdventureWorks.Sales.SalesOrderHeader H 　　JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID 　　WHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', @SalesOrderID = 56000; 　　GO 　　EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotal 　　FROM AdventureWorks.Sales.SalesOrderHeader H 　　JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID 　　WHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', @SalesOrderID = 56001; 　　GO 　　SELECT stats.execution_count AS exec_count, 　　p.size_in_bytes AS [size], 　　[sql].[text] AS [plan_text] 　　FROM sys.dm_exec_cached_plans p 　　OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 　　JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle; 　　GO

如同你所看到的，我重新編寫了這兩個SELECT語句，使它們通過使用“EXEC sp_executesql”語句來執行。對這些EXEC語句中的每一個，我都傳遞三個不同的參數。第一個參數是基本的SELECT語句，但是我將SalesOrderID的值用一個變數(@SalesOrderID)替代。在第二個參數中，我確定了@SalesOrderID的資料類型，在這個例子中它是一個integer。然後在最後一個參數中，我傳遞了SalesOrderID的值。這個參數將控制我的SELECT根據SalesOrderID值所產生的結果。sp_executesql的每次執行中前兩個參數都是一樣的。但是第三個參數不同，因為每個都有不同的SalesOrderID值。

　　現在當我運行上面的代碼時，我從DMV SELECT語句得到下面的輸出(注意，輸出被重新格式化了，以便它更易讀)：

cnt size plan_text 　　--- ----------- ----------------------------------------------------------------------------------------- 　　2 49152 (@SalesOrderID INT)SELECT SUM(LineTotal) AS LineTotal 　　FROM AdventureWorks.Sales.SalesOrderHeader H 　　JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID 　　WHERE H.SalesOrderID = @SalesOrderID

從這個輸出，你可以看出，我有一個參數化緩衝計劃，它被執行了兩次，為每個EXEC語句各執行了一次。

　　使用參數化查詢來節省資源和最佳化效能

　　在語句可以被執行之前，每個T-SQL語句都需要被評估，而且需要建立一個執行計畫。建立執行計畫會佔用寶貴的CPU資源。當執行計畫被建立後，它使用記憶體空間將它儲存在過程緩衝中。降低CPU和記憶體使用量的一個方法是利用參數化查詢。儘管資料庫可以被設定為對所有查詢FORCE參數化，但是這不總是最好的選擇。通過瞭解你的哪些T-SQL語句可以被參數化然後使用sp_executesql預存程序，你可以協助SQL Server節省資源並最佳化你的查詢的效能。