SQL Server 2005 中的批編譯、重新編譯和計劃緩存問題（4）

2024-08-31 00:50:13

字體：大中小

來源：轉載

供稿：網友

下面，考慮以下 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
•	表提示或索引提示

•