儲存過程的執行計劃記憶體是否比非動態查詢“更好”？
閱讀 Microsoft SQL Server 對執行計劃記憶體的不同解釋，我對使用儲存過程而不是非動態查詢的好處感到困惑。
非動態查詢是指完全參數化的查詢字元串，不會通過多次呼叫而改變。
據我了解：
- 為儲存過程和普通查詢記憶體執行計劃。
- 對於儲存過程，執行計劃是預先計算的，與第一次呼叫儲存過程時的普通查詢相比，這會帶來一些好處。
消息來源在我看來相當矛盾：
- MSDN 上的執行計劃記憶體和重用文章在參數化查詢和儲存過程之間沒有區別。這些小節強調了參數化查詢的重要性，以便 SQL Server 輕鬆記憶體執行計劃。
- SQL Server 查詢執行計劃 - Basics聲稱相反（強調我的）：
在執行即席查詢時，查詢計劃是基於完整的程式碼創建的，因此不同的參數或程式碼的任何更改都會阻止對現有計劃的重用。
- 在 DBA.StackExchange 上，對與儲存過程的好處相關的答案的評論表明參數化查詢與儲存過程具有完全相同的效果。
因此，在執行計劃沒有從記憶體中拋出的情況下，並且為了實驗，我想執行數十億次相當複雜的查詢，該查詢將從執行計劃中受益，並且需要一個更改的參數每次，在執行計劃記憶體方面是否有任何好處¹使用儲存過程而不是普通的參數化查詢？
¹在執行計劃的範圍之外，使用儲存過程會帶來一些性能優勢，例如在網路佔用方面：傳遞儲存過程的名稱及其參數比傳遞整個查詢要好一些。這些好處超出了我的問題範圍，這純粹是關於執行計劃記憶體。
答案也可作為獨立的部落格文章獲得。
為了找出答案，我做了一些測試。目標是直接從 C# 或通過呼叫儲存過程執行相同的參數化查詢，並比較執行時性能。
我開始創建一個使用 Adventure Works 數據庫執行範例查詢的儲存過程：
create procedure Demo @minPrice int as begin set nocount on; select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice] from [Production].[Product] p inner join [Production].[ProductListPriceHistory] ph on [p].[ProductID] = ph.[ProductID] and ph.[StartDate] = ( select top 1 [ph2].[StartDate] from [Production].[ProductListPriceHistory] ph2 where [ph2].[ProductID] = [p].[ProductID] order by [ph2].[StartDate] desc ) where [p].[ListPrice] > @minPrice end然後，我使用以下程式碼來比較性能：
long RunQuery(SqlConnection connection, int minPrice) { const string Query = @" select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice] from [Production].[Product] p inner join [Production].[ProductListPriceHistory] ph on [p].[ProductID] = ph.[ProductID] and ph.[StartDate] = ( select top 1 [ph2].[StartDate] from [Production].[ProductListPriceHistory] ph2 where [ph2].[ProductID] = [p].[ProductID] order by [ph2].[StartDate] desc ) where [p].[ListPrice] > @minPrice option (recompile)"; using (var command = new SqlCommand(Query, connection)) { command.Parameters.AddWithValue("@minPrice", minPrice); var stopwatch = Stopwatch.StartNew(); command.ExecuteNonQuery(); stopwatch.Stop(); return stopwatch.ElapsedMilliseconds; } } long RunStoredProcedure(SqlConnection connection, int minPrice) { using (var command = new SqlCommand("exec Demo @minPrice with recompile", connection)) { command.Parameters.AddWithValue("@minPrice", minPrice); var stopwatch = Stopwatch.StartNew(); command.ExecuteNonQuery(); stopwatch.Stop(); return stopwatch.ElapsedMilliseconds; } } ICollection<long> Execute(Func<SqlConnection, int, long> action) { using (var connection = new SqlConnection("Server=.;Database=AdventureWorks2014;Trusted_Connection=True;")) { connection.Open(); using (var command = new SqlCommand("DBCC FreeProcCache; DBCC DropCleanbuffers;", connection)) { command.ExecuteNonQuery(); } return Enumerable.Range(0, 100).Select(i => action(connection, i)).ToList(); } } void Main() { var queries = Execute(RunQuery); var storedProcedures = Execute(RunStoredProcedure); Console.WriteLine("Stored procedures: {0} ms. Details: {1}.", storedProcedures.Sum(), string.Join(", ", storedProcedures)); Console.WriteLine("Queries: {0} ms. Details: {1}.", queries.Sum(), string.Join(", ", queries)); }注意
option (recompile)和with recompile。這將強制 SQL Server 放棄以前記憶體的執行計劃。每個查詢每次都使用不同的參數執行一百次。伺服器花費的時間是在客戶端測量的。
通過
DBCC FreeProcCache; DBCC DropCleanbuffers;在收集指標之前執行，我確保刪除了所有以前記憶體的執行計劃。執行此程式碼會給出以下輸出：
儲存過程：786 毫秒。詳細資訊：12、7、7、9、7、7、9、8、8、6、8、9、8、8、14、8、7、8、7、10、10、7、9、6、 9, 8, 8, 7, 7, 10, 8, 7, 7, 6, 7, 8, 8, 7, 7, 7, 14, 8, 8, 8, 7, 9, 8, 8, 7, 6, 6, 12, 7, 7, 8, 7, 8, 7, 8, 6, 7, 7, 7, 12, 8, 6, 6, 7, 8, 7, 8, 8, 7, 11, 8, 7, 8, 8, 7, 9, 8, 9, 10, 8, 7, 7, 8, 8, 7, 9, 7, 6, 9, 7, 6, 9, 8, 6, 6, 6.
查詢：799 毫秒。詳細資訊：21、8、8、7、6、6、11、7、6、6、9、8、8、7、9、8、7、7、7、7、7、7、10、8、 8, 7, 8, 7, 6, 11, 19, 10, 8, 7, 8, 7, 7, 7, 6, 9, 7, 9, 7, 7, 8, 7, 12, 9, 7, 7, 7, 8, 7, 7, 8, 7, 7, 7, 9, 8, 7, 7, 7, 6, 7, 7, 16, 7, 7, 7, 8, 8, 9, 8, 7, 9, 8, 7, 8, 7, 7, 6, 7, 7, 7, 7, 12, 7, 9, 9, 7, 7, 7, 7, 9, 8, 7, 8, 11, 8.
讓我們再次執行它：
儲存過程：763 毫秒。詳細資訊：11、8、10、8、8、14、10、6、7、7、6、7、7、9、6、6、6、8、6、6、7、6、8、7、 16, 8, 7, 8, 9, 7, 7, 8, 7, 7, 11, 10, 7, 6, 7, 8, 7, 7, 7, 7, 7, 7, 10, 9, 9, 7, 6, 7, 6, 7, 7, 6, 6, 6, 6, 6, 10, 9, 10, 7, 6, 6, 6, 6, 6, 8, 7, 6, 6, 7, 8, 9, 7, 8, 7, 10, 7, 7, 7, 6, 7, 6, 7, 11, 13, 8, 7, 10, 9, 8, 8, 7, 8, 7, 7, 7.
查詢：752 毫秒。詳細資訊：25、10、8、8、12、8、7、9、9、8、6、7、7、6、8、6、7、7、8、9、7、7、7、7、 6, 10, 8, 7, 7, 7, 7, 7, 7, 7, 8, 9, 7, 6, 6, 6, 7, 13, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 6, 10, 7, 7, 8, 9, 8, 7, 6, 6, 7, 7, 9, 7, 8, 6, 9, 7, 7, 8, 7, 6, 6, 7, 7, 7, 7, 6, 7, 7, 8, 7, 7, 6, 7, 9, 8, 7, 7, 7, 7, 6, 7, 6, 6, 9, 7, 7.
儲存過程和直接查詢之間的性能似乎非常接近。執行程式碼十幾次，我注意到儲存過程似乎有點快，但差距非常小。傳遞整個查詢可能會產生額外的成本，如果 SQL Server 託管在專用電腦上，並且它與應用程序伺服器之間的 LAN 速度較慢，則成本可能會增加。
現在讓我們打開執行計劃記憶體，看看會發生什麼。為此，我從程式碼中刪除
option (recompile)和。with recompile這是新的輸出：儲存過程：26 毫秒。詳細資訊：23, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.
查詢：15 毫秒。詳細資訊：14, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0。
很明顯，記憶體對於直接查詢和儲存過程具有完全相同的效果。在這兩種情況下，它將時間減少到幾乎為零毫秒，並且最昂貴的查詢是第一個查詢 - 在刪除記憶體的執行計劃之後執行的查詢。
再次執行相同的程式碼會顯示類似的模式。有時，查詢更快，有時儲存過程更快。但每次，第一個查詢是最昂貴的，其他的都接近零毫秒。
重新打開 SQL 連接
如果為每個查詢打開 SQL 連接，例如在這個稍作修改的程式碼中：
long RunQuery(string connectionString, int minPrice) { const string Query = @" select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice] from [Production].[Product] p inner join [Production].[ProductListPriceHistory] ph on [p].[ProductID] = ph.[ProductID] and ph.[StartDate] = ( select top 1 [ph2].[StartDate] from [Production].[ProductListPriceHistory] ph2 where [ph2].[ProductID] = [p].[ProductID] order by [ph2].[StartDate] desc ) where [p].[ListPrice] > @minPrice option (recompile)"; using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var command = new SqlCommand(Query, connection)) { command.Parameters.AddWithValue("@minPrice", minPrice); var stopwatch = Stopwatch.StartNew(); command.ExecuteNonQuery(); stopwatch.Stop(); return stopwatch.ElapsedMilliseconds; } } } long RunStoredProcedure(string connectionString, int minPrice) { using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var command = new SqlCommand("exec Demo @minPrice with recompile", connection)) { command.Parameters.AddWithValue("@minPrice", minPrice); var stopwatch = Stopwatch.StartNew(); command.ExecuteNonQuery(); stopwatch.Stop(); return stopwatch.ElapsedMilliseconds; } } } ICollection<long> Execute(Func<string, int, long> action) { var connectionString = "Server=.;Database=AdventureWorks2014;Trusted_Connection=True;"; using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var command = new SqlCommand("DBCC FreeProcCache; DBCC DropCleanbuffers;", connection)) { command.ExecuteNonQuery(); } } return Enumerable.Range(0, 100).Select(i => action(connectionString, i)).ToList(); } void Main() { var queries = Execute(RunQuery); var storedProcedures = Execute(RunStoredProcedure); Console.WriteLine("Stored procedures: {0} ms. Details: {1}.", storedProcedures.Sum(), string.Join(", ", storedProcedures)); Console.WriteLine("Queries: {0} ms. Details: {1}.", queries.Sum(), string.Join(", ", queries)); }觀察到的指標非常相似：
儲存過程：748 毫秒。詳細資訊：11、8、6、6、8、9、9、8、8、7、6、8、7、9、6、6、6、6、6、6、7、7、6、9、 6, 6, 7, 6, 6, 7, 8, 6, 7, 7, 7, 13, 7, 7, 8, 7, 8, 8, 7, 7, 7, 7, 6, 7, 8, 8, 8, 9, 7, 6, 8, 7, 6, 7, 6, 6, 6, 6, 8, 12, 7, 9, 9, 6, 7, 7, 7, 8, 10, 12, 8, 7, 6, 9, 8, 7, 6, 6, 7, 8, 6, 6, 12, 7, 8, 10, 10, 7, 8, 7, 8, 10, 8, 7, 8, 7.
查詢：761 毫秒。詳細資訊：31、9、7、6、6、8、7、7、7、7、7、6、8、7、6、6、7、10、8、10、9、7、7、7、 7, 10, 13, 7, 10, 7, 6, 6, 6, 8, 7, 7, 7, 7, 7, 7, 7, 9, 7, 7, 7, 6, 6, 6, 9, 7, 7, 7, 7, 7, 6, 8, 10, 7, 7, 7, 7, 7, 7, 7, 8, 6, 10, 10, 7, 8, 8, 7, 7, 7, 7, 7, 6, 6, 7, 6, 8, 7, 7, 7, 7, 7, 7, 7, 8, 7, 8, 7, 9, 7, 6, 6, 12, 10, 7, 6.
和
option (recompile)和with recompile：儲存過程：15 毫秒。詳細資訊：14, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.
查詢：32 毫秒。詳細資訊：26, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0。
沒有。
在引擎蓋下
讓我們看看引擎蓋下會發生什麼。以下查詢顯示記憶體的執行計劃：
select usecounts, size_in_bytes, cacheobjtype, objtype, text from sys.dm_exec_cached_plans cross apply sys.dm_exec_sql_text(plan_handle) where cacheobjtype = 'Compiled Plan' order by usecounts desc在執行儲存過程一百次後執行此查詢時，查詢結果如下所示：
usecounts size_in_bytes cacheobjtype objtype text ----------- ------------- -------------------------------------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 100 90112 Compiled Plan Proc create procedure Demo @minPrice int as begin set nocount on; select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice] from [Production].[Product] p inner join [Production].[ProductListPriceHistory] ph on [p].[ProductID] = ph.[Product 100 16384 Compiled Plan Prepared (@minPrice int)exec Demo @minPrice --with recompile 1 49152 Compiled Plan Adhoc --DBCC FreeProcCache --DBCC DropCleanbuffers select usecounts, size_in_bytes, cacheobjtype, objtype, text from sys.dm_exec_cached_plans cross apply sys.dm_exec_sql_text(plan_handle) where cacheobjtype = 'Compiled Plan' order by usecounts desc (3 row(s) affected)直接執行一百次查詢，結果為：
usecounts size_in_bytes cacheobjtype objtype text ----------- ------------- -------------------------------------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 100 73728 Compiled Plan Prepared (@minPrice int) select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice] from [Production].[Product] p inner join [Production].[ProductListPriceHistory] ph on [p].[ProductID] = ph.[ProductID] and ph.[StartDate] = ( select top 1 [ph2].[ 1 49152 Compiled Plan Adhoc --DBCC FreeProcCache --DBCC DropCleanbuffers select usecounts, size_in_bytes, cacheobjtype, objtype, text from sys.dm_exec_cached_plans cross apply sys.dm_exec_sql_text(plan_handle) where cacheobjtype = 'Compiled Plan' order by usecounts desc (2 row(s) affected)結論
- 執行計劃被記憶體用於儲存過程和直接查詢。
- 當 SQL Server 和應用程序託管在同一台電腦上時，儲存過程和直接查詢之間的性能非常相似。當 SQL Server 託管在通過 LAN 訪問的專用伺服器上時，使用儲存過程可能會帶來更好的性能。