SqlServer參數化指令碼與自動參數化（簡單參數化）

標籤：

      如果執行不帶參數的SQL語句，SQL Server會在內部對該語句進行參數化以增加將其與現有執行計畫相匹配的可能性。此過程稱為簡單參數化（在SQL Server 2000中，稱為自動參數化），最終起到執行計畫重用的效果。

--從資料緩衝池中刪除所有緩衝DBCC DROPCLEANBUFFERS    GO--從執行計畫緩衝區刪除所有緩衝的執行計畫DBCC FREEPROCCACHE        GO

--執行不帶參數的SQL語句，SQL Server會在內部對該語句進行參數化以增加將其與現有執行計畫相匹配的可能性。

--此過程稱為簡單參數化（在SQL Server 2000中，稱為自動參數化），最終起到執行計畫重用的效果。 select * from WORKITEM t where t.WORKITEMID = ‘67f956f5-a350-4254-b214-84b72c85664e‘goselect * from WORKITEM t where t.WORKITEMID = ‘b1e337b3-9b2a-4463-9692-7a738ebba205‘goselect * from WORKITEM t where t.WORKITEMID = ‘c059be96-aea3-42a1-8f66-b67c0dd79fa6‘go--使用參數化的方式執行sp_executesql N‘select * from WORKITEM t where t.WORKITEMID = @wid‘, N‘@wid varchar(36)‘, @wid=‘67f956f5-a350-4254-b214-84b72c85664e‘ 

go 

sp_executesql N‘select * from WORKITEM t where t.WORKITEMID = @wid‘, N‘@wid varchar(36)‘, @wid=‘b1e337b3-9b2a-4463-9692-7a738ebba205‘gosp_executesql N‘select * from WORKITEM t where t.WORKITEMID = @wid‘, N‘@wid varchar(36)‘, @wid=‘c059be96-aea3-42a1-8f66-b67c0dd79fa6‘goSELECT    cacheobjtype, objtype, usecounts, refcounts, pagesused, sql FROM    sys.syscacheobjects WHERE    cacheobjtype = ‘Compiled Plan‘ and sql not like ‘%syscacheobjects%‘ and        sql LIKE ‘%from WORKITEM t where %‘

--如果SQL指令碼內容較長，可使用sys.dm_exec_cached_plans、sys.dm_exec_sql_text 

select    t.cacheobjtype, t.objtype, t.usecounts, t.refcounts, dc.text from    sys.dm_exec_cached_plans t    cross apply sys.dm_exec_sql_text(t.plan_handle) dcwhere    t.cacheobjtype = ‘Compiled Plan‘ and dc.text not like ‘%dm_exec_cached_plans%‘ and         dc.text like ‘%from WORKITEM t where %‘

 

      當然從最終的執行計畫緩衝中可以看到，直接執行的SQL指令碼在緩衝中還是會有對應的一條記錄。原來第一條執行的SQL會產生兩條執行計畫，其中一個就是對明顯的常量進行參數化，然後再根據這個參數化的執行計畫，產生自己的adhoc執行計畫。後來的SQL命中已經產生的執行計畫，可以節省一部分的編譯時間。

      可以想象，非參數化SQL的執行計畫仍然需要在資料庫記憶體中儲存，並且對於新的SQL指令碼SqlServer也需要按照一定的演算法找到對應的參數化執行計畫，不曉得這些需要耗費多少資源呢？目前還不清楚如何查看這類的資訊。

 

SqlServer Profiler的跟蹤結果如下：

SqlServer參數化指令碼與自動參數化（簡單參數化）