什么是參數(shù)化查詢?
　　一個(gè)簡(jiǎn)單理解參數(shù)化查詢的方式是把它看做只是一個(gè)T-SQL查詢，它接受控制這個(gè)查詢返回什么的參數(shù)。通過使用不同的參數(shù)，一個(gè)參數(shù)化查詢返回不同的結(jié)果。要獲得一個(gè)參數(shù)化查詢，你需要以一種特定的方式來編寫你的代碼，或它需要滿足一組特定的標(biāo)準(zhǔn)。

　　有兩種不同的方式來創(chuàng)建參數(shù)化查詢。第一個(gè)方式是讓查詢優(yōu)化器自動(dòng)地參數(shù)化你的查詢。另一個(gè)方式是通過以一個(gè)特定方式來編寫你的T-SQL代碼，并將它傳遞給sp_executesql系統(tǒng)存儲(chǔ)過程，從而編程一個(gè)參數(shù)化查詢。這篇文章的后面部分將介紹這個(gè)方法。

　　參數(shù)化查詢的關(guān)鍵是查詢優(yōu)化器將創(chuàng)建一個(gè)可以重用的緩存計(jì)劃。通過自動(dòng)地或編程使用參數(shù)化查詢，SQL Server可以優(yōu)化類似T-SQL語(yǔ)句的處理。這個(gè)優(yōu)化消除了對(duì)使用高貴資源為這些類似T-SQL語(yǔ)句的每一次執(zhí)行創(chuàng)建一個(gè)緩存計(jì)劃的需求。而且通過創(chuàng)建一個(gè)可重用計(jì)劃，SQL Server還減少了存放過程緩存中類似的執(zhí)行計(jì)劃所需的內(nèi)存使用。

　　現(xiàn)在讓我們看看使得SQL Server創(chuàng)建參數(shù)化查詢的不同方式。

　　參數(shù)化查詢是怎樣自動(dòng)創(chuàng)建的?

　　微軟編寫查詢優(yōu)化器代碼的人竭盡全力地優(yōu)化SQL Server處理你的T-SQL命令的方式。我想這是查詢優(yōu)化器名稱的由來。這些盡量減少資源和最大限度地提高查詢優(yōu)化器執(zhí)行性能的方法之一是查看一個(gè)T-SQL語(yǔ)句并確定它們是否可以被參數(shù)化。要了解這是如何工作的，讓我們看看下面的T-SQL語(yǔ)句：

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

在這里，你可以看到這個(gè)命令有兩個(gè)特點(diǎn)。第一它簡(jiǎn)單，第二它在WHERE謂詞中包含一個(gè)用于SalesOrderID值的指定值。查詢優(yōu)化器可以識(shí)別這個(gè)查詢比較簡(jiǎn)單以及SalesOrderID有一個(gè)參數(shù)(“56000”)。因此，查詢優(yōu)化器可以自動(dòng)地參數(shù)化這個(gè)查詢。

　　如果你使用下面的SELECT語(yǔ)句來查看一個(gè)只包含用于上面語(yǔ)句的緩存計(jì)劃的、干凈的緩沖池，那么你會(huì)看到查詢優(yōu)化器將T-SQL查詢重寫為一個(gè)參數(shù)化T-SQL語(yǔ)句：

 

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

當(dāng)我在一個(gè)SQL Server 2008實(shí)例上運(yùn)行這個(gè)命令時(shí)，我得到下面的輸出，(注意，輸出被重新格式化了，以便它更易讀)：

　　cnt size plan_text

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

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

　　WHERE [SalesOrderID]=@1

　　如果你看看上面輸出中的plan_text字段，你會(huì)看到它不像原來的T-SQL文本。如前所述，查詢優(yōu)化器將這個(gè)查詢重新編寫為一個(gè)參數(shù)化T-SQL語(yǔ)句。在這里，你可以看到它現(xiàn)在有一個(gè)數(shù)據(jù)類型為(int)的變量(@1)，它在之前的SELECT語(yǔ)句中被定義的。另外在plan_text的末尾， 值“56000”被替換為變量@1。既然這個(gè)T-SQL語(yǔ)句被重寫了，而且被存儲(chǔ)為一個(gè)緩存計(jì)劃，那么如果未來一個(gè)T-SQL命令和它大致相同，只有SalesOrderID字段被賦的值不同的話，它就可以被用于重用。讓我們?cè)趧?dòng)作中看看它。

如果我在我的機(jī)器上運(yùn)行下面的命令：

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語(yǔ)句得到下面的輸出，(注意，輸出被重新格式化以便它更易讀)： 　　cnt size plan_text 　　--- -------- -------------------------------------------------------------- 　　2 49152 (@1 int)SELECT * FROM AdventureWorks].[Sales].[SalesOrderHeader] 　　WHERE [SalesOrderID]=@1

在這里，我首先釋放過程緩存，然后我執(zhí)行兩個(gè)不同、但卻類似的非參數(shù)化查詢來看看查詢優(yōu)化器是會(huì)創(chuàng)建兩個(gè)不同的緩存計(jì)劃還是創(chuàng)建用于這兩個(gè)查詢的一個(gè)緩存計(jì)劃。在這里，你可以看到查詢優(yōu)化器事實(shí)上很聰明，它參數(shù)化第一個(gè)查詢并緩存了計(jì)劃。然后當(dāng)?shù)诙€(gè)類似、但有一個(gè)不同的SalesOrderID值的查詢發(fā)送到SQL Server時(shí)，優(yōu)化器可以識(shí)別已經(jīng)緩存了一個(gè)計(jì)劃，然后重用它來處理第二個(gè)查詢。你可以這么說是因?yàn)椤癱nt”字段現(xiàn)在表明這個(gè)計(jì)劃被用了兩次。

　　數(shù)據(jù)庫(kù)配置選項(xiàng)PARAMETERIZATION可以影響T-SQL語(yǔ)句怎樣被自動(dòng)地參數(shù)化。對(duì)于這個(gè)選項(xiàng)有兩種不同的設(shè)置，SIMPLE和FORCED。當(dāng)PARAMETERIZATION設(shè)置被設(shè)置為SIMPLE時(shí)，只有簡(jiǎn)單的T-SQL語(yǔ)句才會(huì)被參數(shù)化。要介紹這個(gè)，看下下面的命令：

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

這個(gè)查詢類似于我前面的示例，除了在這里我添加了一個(gè)額外的JOIN標(biāo)準(zhǔn)。當(dāng)數(shù)據(jù)庫(kù)AdventureWorks的PARAMETERIZATION選項(xiàng)被設(shè)置為SIMPLE時(shí)，這個(gè)查詢不會(huì)被自動(dòng)地參數(shù)化。SIMPLE PARAMETERIZATION設(shè)置告訴查詢優(yōu)化器只參數(shù)化簡(jiǎn)單的查詢。但是當(dāng)選項(xiàng)PARAMETERIZATION被設(shè)置為FORCED時(shí)，這個(gè)查詢將被自動(dòng)地參數(shù)化。

　　當(dāng)你設(shè)置數(shù)據(jù)庫(kù)選項(xiàng)為使用FORCE PARAMETERIZATION時(shí)，查詢優(yōu)化器試圖參數(shù)化所有的查詢，而不僅僅是簡(jiǎn)單的查詢。你可能會(huì)認(rèn)為這很好。但是在某些情況下，當(dāng)數(shù)據(jù)庫(kù)設(shè)置PARAMETERIZATION為FORCED時(shí)，查詢優(yōu)化器將選擇不是很理想的查詢計(jì)劃。當(dāng)數(shù)據(jù)庫(kù)設(shè)置PARAMETER為FORCED時(shí)，它改變查詢中的字面常量。這可能導(dǎo)致當(dāng)查詢中涉及計(jì)算字段時(shí)索引和索引視圖不被選中參與到執(zhí)行計(jì)劃中，從而導(dǎo)致一個(gè)無效的計(jì)劃。FORCED PARAMETERIZATION選項(xiàng)可能是改進(jìn)具有大量類似的、傳遞過來的參數(shù)稍有不同的查詢的數(shù)據(jù)庫(kù)性能的一個(gè)很好的解決方案。一個(gè)在線銷售應(yīng)用程序，它的客戶對(duì)你的產(chǎn)品執(zhí)行大量的類似搜索， 產(chǎn)品值不同，這可能是一個(gè)能夠受益于FORCED PARAMETERIZATION的很好的應(yīng)用程序類型。

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

　　使用sp_execute_sql來參數(shù)化你的T-SQL

　　你不需要依賴于數(shù)據(jù)庫(kù)的PARAMETERIZATION選項(xiàng)來使得查詢優(yōu)化器參數(shù)化一個(gè)查詢。你可以參數(shù)化你自己的查詢。你通過重新編寫你的T-SQL語(yǔ)句并使用“sp_executesql”系統(tǒng)存儲(chǔ)過程執(zhí)行重寫的語(yǔ)句來實(shí)現(xiàn)。正如已經(jīng)看到的，上面包括一個(gè)“JOIN”從句的SELECT語(yǔ)句在數(shù)據(jù)庫(kù)的PARAMETERIZATION設(shè)置為SIMPLE時(shí)沒有被自動(dòng)參數(shù)化。讓我重新編寫這個(gè)查詢以便查詢優(yōu)化器將創(chuàng)建一個(gè)可重用的參數(shù)化查詢執(zhí)行計(jì)劃。

　　為了說明，讓我們看兩個(gè)類似的、不會(huì)被自動(dòng)參數(shù)化的T-SQL語(yǔ)句，并創(chuàng)建兩個(gè)不同的緩存執(zhí)行計(jì)劃。然后我將重新編寫這兩個(gè)查詢使得它們都使用相同的緩存參數(shù)化執(zhí)行計(jì)劃。

　　讓我們看看這個(gè)代碼：

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

在這里，我釋放了過程緩存，然后運(yùn)行這兩個(gè)包含一個(gè)JOIN的、不同的非簡(jiǎn)單的T-SQL語(yǔ)句。然后我將檢查緩存計(jì)劃。這是這個(gè)使用DMV 的SELECT語(yǔ)句的輸出(注意，輸出被重新格式化了，以便它更易讀)：

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

正如你從這個(gè)輸出看到的，這兩個(gè)SELECT語(yǔ)句沒有被查詢優(yōu)化器參數(shù)化。優(yōu)化器創(chuàng)建了兩個(gè)不同的緩存執(zhí)行計(jì)劃，每一個(gè)都只被執(zhí)行了一次。我們可以通過使用sp_executesql系統(tǒng)存儲(chǔ)過程來幫助優(yōu)化器為這兩個(gè)不同的SELECT語(yǔ)句創(chuàng)建一個(gè)參數(shù)化執(zhí)行計(jì)劃。
下面是上面的代碼被重新編寫來使用sp_executesql 系統(tǒng)存儲(chǔ)過程：

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

如同你所看到的，我重新編寫了這兩個(gè)SELECT語(yǔ)句，使它們通過使用“EXEC sp_executesql”語(yǔ)句來執(zhí)行。對(duì)這些EXEC語(yǔ)句中的每一個(gè)，我都傳遞三個(gè)不同的參數(shù)。第一個(gè)參數(shù)是基本的SELECT語(yǔ)句，但是我將SalesOrderID的值用一個(gè)變量(@SalesOrderID)替代。在第二個(gè)參數(shù)中，我確定了@SalesOrderID的數(shù)據(jù)類型，在這個(gè)例子中它是一個(gè)integer。然后在最后一個(gè)參數(shù)中，我傳遞了SalesOrderID的值。這個(gè)參數(shù)將控制我的SELECT根據(jù)SalesOrderID值所生成的結(jié)果。sp_executesql的每次執(zhí)行中前兩個(gè)參數(shù)都是一樣的。但是第三個(gè)參數(shù)不同，因?yàn)槊總€(gè)都有不同的SalesOrderID值。

　　現(xiàn)在當(dāng)我運(yùn)行上面的代碼時(shí)，我從DMV SELECT語(yǔ)句得到下面的輸出(注意，輸出被重新格式化了，以便它更易讀)：

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

從這個(gè)輸出，你可以看出，我有一個(gè)參數(shù)化緩存計(jì)劃，它被執(zhí)行了兩次，為每個(gè)EXEC語(yǔ)句各執(zhí)行了一次。

　　使用參數(shù)化查詢來節(jié)省資源和優(yōu)化性能

　　在語(yǔ)句可以被執(zhí)行之前，每個(gè)T-SQL語(yǔ)句都需要被評(píng)估，而且需要建立一個(gè)執(zhí)行計(jì)劃。創(chuàng)建執(zhí)行計(jì)劃會(huì)占用寶貴的CPU資源。當(dāng)執(zhí)行計(jì)劃被創(chuàng)建后，它使用內(nèi)存空間將它存儲(chǔ)在過程緩存中。降低CPU和內(nèi)存使用的一個(gè)方法是利用參數(shù)化查詢。盡管數(shù)據(jù)庫(kù)可以被設(shè)置為對(duì)所有查詢FORCE參數(shù)化，但是這不總是最好的選擇。通過了解你的哪些T-SQL語(yǔ)句可以被參數(shù)化然后使用sp_executesql存儲(chǔ)過程，你可以幫助SQL Server節(jié)省資源并優(yōu)化你的查詢的性能。

最新評(píng)論