SQL Server 2005 中的批編譯、重新編譯和計畫快取問題（4）

下面，考慮以下 T-SQL 程式碼片段： -- dbo.someTable will be used to populate a temp table
-- subsequently.
create table dbo.someTable (a int not null, b int not null)
go
declare @i int
set @i = 1
while (@i <= 2000)
begin
　　insert into dbo.someTable values (@i, @i+5)
　　set @i = @i + 1
end
go
　
-- This is the stored procedure of main interest.
create procedure dbo.AlwaysRecompile
as　
set nocount on
　
-- create a temp table
create table #temp1(c int not null, d int not null)
　
select count(*) from #temp1
　
-- now populate #temp1 with 2000 rows
insert into #temp1
select * from dbo.someTable
　
-- create a clustered index on #temp1
create clustered index cl_idx_temp1 on #temp1(c)
　
select count(*) from #temp1
go

　　在 SQL Server 2000 中，當首次執行這個預存程序時，將對第一個“select”語句產生第一個 SP:Recompile 事件。這是一次延遲編譯，不是真正的重新編譯。第二個 SP:Recompile 事件針對第二個“select”。當發生第一次重新編譯時間，第二個“select”也會被編譯，因為在 SQL Server 2000 中，編譯是在批處理層級上進行的。然後，在執行時，#temp1 的架構因建立的叢集索引而發生了變化。所以，產生第二個 SP:Recompile 的原因是架構更改。

因行修改次數而導致的重新編譯

　　考慮下方預存程序及其執行。

use AdventureWorks　 -- or say "use pubs" on SQL Server 2000
go
create procedure RowCountDemo
as
begin
　　create table #t1 (a int, b int)
　　declare @i int
　　set @i = 0　　while (@i < 20)
　　begin
　　　 insert into #t1 values (@i, 2*@i - 50)
　　　 select a
　　　 from #t1　
　　　 where a < 10 or ((b > 20 or a >=100) and (a < 10000))
　　　 group by a
　
　　　 set @i = @i + 1
　　end
end
go
exec RowCountDemo
go

　　回想一下，當表在計算閾值時為空白，暫存資料表的重新編譯閾值為 6。當執行 RowCountDemo 時，在 #t1 包含整 6 行後，可觀察到與“statistics changed”（統計被更改）相關的重新編譯。通過更改“while”迴圈的上限，可觀察到更多的重新編譯。

因 SET 選項更改而導致的重新編譯

　　考慮下列預存程序。

use AdventureWorks
go
create procedure SetOptionsDemo as
begin
　　set ansi_nulls off
　　select p.Size, sum(p.ListPrice)
　　from Production.Product p　
　　　　 inner join Production.ProductCategory pc
　　　　　on p.ProductSubcategoryID = pc.ProductCategoryID
　　where p.Color = 'Black'
　　group by p.Size
end
go
exec SetOptionsDemo　　-- causes a recompilation
go
exec SetOptionsDemo　　-- does not cause a recompilation
go

　　當執行 SetOptionsDemo 時，在“ansi_nulls”為 ON 的情況下編譯“select”查詢。當 SetOptionsDemo 開始執行時，該 SET 選項的值將由於“set ansi_nulls off”而發生變化，因而已編譯的查詢計劃將不再“有效”。所以，將在“ansi_nulls”為 OFF 的情況下進行重新編譯。第二次執行不會導致重新編譯，因為已緩衝的計劃將在“ansi_nulls”為 OFF 的情況下進行編譯。

表明 SQL Server 2005 所需的重新編譯較 SQL Server 2000 多的另一個樣本

　　考慮下列預存程序。

use AdventureWorks　　 -- say "use pubs" on SQL Server 2000
go
create procedure CreateThenReference as
begin
　 -- create two temp tables
　 create table #t1(a int, b int)
　 create table #t2(c int, d int)
　 -- populate them with some data
　 insert into #t1 values (1, 1)
　 insert into #t1 values (2, 2)
　 insert into #t2 values (3, 2)
　 insert into #t2 values (4, 3)
　　　　 -- issue two queries on them
　 select x.a, x.b, sum(y.c)
　 from #t1 x inner join #t2 y on x.b = y.d
　 group by x.b, x.a
　 order by x.b
　
　 select *　
　 from #t1 z cross join #t2 w
　 where w.c != 5 or w.c != 2
end
go
exec CreateThenReference
go

　　在 SQL Server 2005 中，CreateThenReference 的第一次執行導致了六項語句級重新編譯：其中有四項針對“insert”語句，有兩項針對“select”查詢。當該預存程序開始執行時，最初的查詢計劃不包含針對“insert”或“select”語句的計劃，因為其所引用（暫存資料表 #t1 和 #t2）的對象還不存在。建立了 #t1 和 #t2 之後，將編譯“insert”和“select”的查詢計劃，而這些編譯被視為重新編譯。在 SQL Server 2000 中，由於整個預存程序被立即重新編譯，因此僅發生一次（預存程序級）重新編譯——第一個“insert”開始執行時所引發的重新編譯。這時，整個預存程序都被重新編譯，而因為 #t1 and #t2 已經存在，可一次性對後續的“insert”和“select”進行編譯。顯而易見，通過添加更多引用諸如 #t1 和 #t2 等對象的語句，SQL Server 2005 中的語句級重新編譯次數可無限增加。

　　十一、工具與命令

　　本節介紹了用於觀測和調試重新編譯的各種工具和命令。

Sys.syscacheobjects 虛擬表

　　雖然可以從任何資料庫進行查詢，但該虛擬表理論上僅存在於 master 資料庫中。該虛擬表的 cacheobjtype 列特別有趣。當 cacheobjtype = "Compiled Plan"，相應的行將引用一個查詢計劃。當 cacheobjtype = "Executable Plan"，相應的行將引用一個執行內容。正如我們前面所說明的，每個執行內容必須有自己的關聯查詢計劃，反之則不然。所涉及的另一列是 objtype 列：指示其計劃被緩衝的對象的類型（比如：“Adhoc”、“Prepared”和“Proc”）。setopts 列編碼了一個位元影像，指示在編譯計劃時生效的 SET 選項。有時，相同的已編譯計劃（僅 setopts 列有所不同）的多個副本被緩衝在一個計畫快取中。這表示不同的串連正在使用幾組不同的 SET 選項——通常屬於不該發生的情況。usecounts 列儲存了自對象被緩衝以來已緩衝對象被重用的次數。

　　請參考 BOL 瞭解有關此虛擬表的更多資訊。

DBCC FREEPROCCACHE

　　此命令可刪除計畫快取中的所有已緩衝的查詢計劃和執行內容。不應在生產伺服器上運行該命令，因為它反過來會影響正在啟動並執行應用程式的效能。在對重新編譯問題進行故障診斷時，該命令對於控制計畫快取的內容很有用。

DBCC FLUSHPROCINDB( db_id )

　　此命令可刪除特定資料庫的計畫快取中的所有已緩衝計劃。不應在生產伺服器上運行該命令，因為它反過來會影響正在啟動並執行應用程式的效能。

事件探查器跟蹤事件

　　下列事件探查器跟蹤事件涉及觀測和調試計畫快取、編譯和重新編譯行為。

•	‘Cursors:CursorRecompile’（SQL Server 2005 新增），用於觀測與遊標相關的批處理所導致的重新編譯。
•	‘Objects:Auto Stats’，用於觀測 SQL Server 的“自動統計”功能所導致的統計更新。
•	‘Performance:Show Plan All For Query Compile’（SQL Server 2005 新增），對於跟蹤批處理編譯很有用。不區分編譯和重新編譯。以文字格式設定產生 showplan 資料（類似使用“set showplan_all on”選項所產生的 showplan 資料）。
•	‘Performance:Show Plan XML For Query Compile’（SQL Server 2005 新增），對於跟蹤批處理編譯很有用。不區分編譯和重新編譯。以 XML 格式產生 showplan 資料（類似使用“set showplan_xml on”選項所產生的 showplan 資料）。
•	‘Stored Procedures:SP:Recompile’激發（發生重新編譯時間）。“Stored Procedures”類別中的其他事件也很有用——比如：SP:CacheInsert、SP:StmtStarting、SP:CacheHit、SP:Starting 等等。

效能計數器

　　在調試可能因過度編譯和重新編譯所導致的效能問題時，涉及下列效能計數器的值。

效能物件	計數器
SQLServer:緩衝管理器	快取命中率、惰性寫入/秒、過程快取頁數、總頁數
SQLServer:快取管理器	快取命中率、快取對象計數、快取頁數、快取使用計數/秒
SQLServer:記憶體管理器	SQL 快取記憶體 (KB)
SQLServer:SQL 統計	自動參數化嘗試/秒、批請求/秒、自動參數化失敗/秒、安全自動參數化/秒、SQL 編譯/秒、SQL 重新編譯/秒、不安全的自動參數化/秒

　　
總結

　　SQL Server 2005 可緩衝提交給其以執行的各種語句類型的查詢計劃。查詢計劃緩衝可導致查詢計劃重用，避免編譯罰點，並更好地運用計畫快取。一些編碼方法會阻礙查詢計劃緩衝和重用，因此應加以避免。SQL Server 可發現查詢計劃重用的機會。特別是，查詢計劃會因下面這兩個原因而無法重用：(a) 出現在查詢計劃中的對象架構會發生變化，從而導致計劃無效；(b) 查詢計劃所引用的表中的資料所發生的變化足以使計劃變成非最佳的。SQL Server 可在查詢執行時發現這兩類情況，並根據需要對整個或部分批處理進行重新編譯。不良的 T-SQL 編碼方法會增加重新編譯的頻率，從而反過來影響 SQL Server 的效能。在許多情況下，都可以對這類情況進行調試和糾正。

附錄 A：SQL Server 2005 何時不自動參數化查詢？

　　自動參數化是一個過程，SQL Server 通過這個過程將出現在 SQL 陳述式中的文本常量替換為諸如 @p1 和 @p2 等參數。然後，SQL 陳述式的已編譯計劃以參數化的形式被緩衝在計畫快取中，以便後續的語句（只是在文本常量的值上有所不同）可重用已緩衝的計劃。正如第四部分所提到的，只有參數值不影響查詢計劃選擇的 SQL 陳述式才會被自動參數化。

　　SQL Server 的 LPE（語言處理和執行）組件可參數化 SQL 陳述式。當發現文本常量的值不影響查詢計劃選擇時，QP（查詢處理器）組件將聲明 LPE 的自動參數化嘗試是“安全的”，並繼續執行自動參數化；否則，將聲明自動參數化是“不安全的”，並將其中止。在第 11.5 節提到的一些效能計數器的值（‘SQLServer:SQL 統計’類別）報告了有關自動參數化的統計資訊。

　　下方列表列舉了 SQL Server 2005 不對其進行自動參數化的語句類型。

•	帶有 IN 子句的查詢不會被自動參數化。例如：
•	WHERE ProductID IN (707, 799, 905)
•	BULK INSERT 語句。
•	帶有一個含變數的 SET 子句的 UPDATE 語句。例如： UPDATE Sales.Customer SET CustomerType = N'S' WHERE CustomerType = @a
•	帶有 UNION 的 SELECT 語句。
•	帶有 INTO 子句的 SELECT 語句。
•	帶有 FOR BROWSE 子句的 SELECT 或 UPDATE 語句。
•	帶有使用 OPTION 子句指定的查詢提示的語句
•	其 SELECT 列表包含 DISTINCT 的 SELECT 語句。
•	帶有 TOP 子句的語句。
•	WAITFOR 語句。
•	帶有 FROM 子句的 DELETE 或 UPDATE 語句。
•	當 FROM 子句含有下列之一時： • 　　多個表 • 　　TABLESAMPLE 子句 • 　　資料表值函式或表值變數 • 　　全文表 • 　　OPENROWSET • 　　XMLUNNEST • 　　OPENXML • 　　OPENQUERY • 　　IROWSET • 　　OPENDATASOURCE • 　　表提示或索引提示
•	當 SELECT 查詢包含一個子查詢時
•	當 SELECT 語句包含 GROUP BY、HAVING 或 COMPUTE BY 時
•	用 WHERE 子句中的 OR 加入的運算式。
•	expr <> non-null-constant 形式的比較謂詞。
•	全文謂詞。
•	當 INSERT、UPDATE 或 DELETE 中的目標表是一個資料表值函式時。
•	通過 EXEC 字串提交的語句。
•	通過 sp_executesql、sp_prepare 和 sp_prepexec 提交的語句，不帶有在 TF 447 下自動參數化的參數。
•	當要求查詢通知時。
•	當查詢包含通用表運算式列表時。
•	當查詢包含 FOR UPDATE 子句時。
•	當 UPDATE 包含 ORDER BY 子句時。
•	當查詢包含 GROUPING 子句時。
•	形式如下的 INSERT 語句：INSERT INTO T DEFAULT VALUES。
•	INSERT ...EXEC 語句。
•	當查詢包含兩個常量的對比時。例如： WHERE 20 > 5
•	通過自動參數化，可建立超過 1000 個參數。