這篇文章我想談下SQL Server裡一個非常重要的性能調優話題:重編譯(Recompilations) 。當你執行非常簡單的存儲過程(使用臨時表)時,就會發生。今天我想奠定SQL Server裡重編譯的基礎,它們為什麼會發生,下篇文章我會向你展示通過不同方式重寫你的存儲過程避免重編譯。
什麼是重編譯?
在我談SQL Server裡重編譯細節前,首先來看看下面一個很簡單存儲過程。
1 CREATE PROCEDURE DemonstrateTempTableRecompiles
2 AS
3 BEGIN
4 CREATE TABLE #TempTable
5 (
6 ID INT IDENTITY(1, 1) PRIMARY KEY,
7 FirstName CHAR(4000),
8 LastName CHAR(4000)
9 )
10
11 INSERT INTO #TempTable (FirstName, LastName)
12 SELECT TOP 1000 name, name FROM master.dbo.syscolumns
13
14 SELECT * FROM #TempTable
15
16 DROP TABLE #TempTable
17 END
18 GO
從代碼裡可以看出,這個存儲過程并沒有什麼特殊。第1步我們建立臨時表,然後臨時表裡會插入一些記錄,最後用簡單的SELECT語句擷取插入的資料。在SQL Server裡,像這樣的代碼你可能謝了上百次,甚至上千次。
接下來我們用SQL Server Profiler跟蹤下重編譯事件。點選【工具】->【SQL Server Profiler】。輸入登入密碼後,會彈出【跟蹤屬性】視窗。點選【事件選擇】,勾選【顯示所有事件】,然後在事件清單裡勾選【Stored Procedures】下列事件:
- SP:Starting
- SP:StmtStarting
- SP:Recompile
- SP:Completed
點選【運作】開始跟蹤。在我們建立存儲過程後,我們運作存儲過程。
1 EXEC DemonstrateTempTableRecompiles
從上圖可以看到,我們在執行存儲過程時,發生了2次重編譯。
重編譯意味這SQL Server在運作執行計劃時,對執行計劃進行了重編譯。這會帶來額外的CPU開銷,最後減少伺服器工作的吞吐量。但現在的問題是,為什麼這些重編譯會發生?
SQL Server執行重編譯主要是在下列2種情況發生:
- 架構改變(Scheme Shanges)
- 統計資訊更新(Statistic Updates)
在剛才執行的存儲過程裡,因為這2個情況我們觸發了重編譯。我們再來看下剛才的存儲過程,第1步我們建立了臨時表,當我們在TempDb裡建立臨時表時,你就改變了你的資料庫架構,因為這個原因第1個重編譯發生了。
在臨時表建立後,你插入了一些記錄。是以SQL Server需要去更新臨時表聚集索引的統計資訊——聚集索引是由SQL Server通過主鍵(PRIMARY KEY)建立。1個簡單的存儲過程就引起了SQL Server裡2個重編譯。哇噢~~~~~
小結
重編譯會給臨時表帶來巨大的性能負荷。另一方面,臨時表有準确的統計資訊幫助查詢優化器生成更好性能的執行計劃。是以,當你處理大量資料時,使用臨時表才是正确選擇。對于小量資料,重編譯引起的CPU負荷,比通過統計資訊獲得性能提升的CPU負荷會高很多。
下篇文章,我會為你介紹表變量(Table Variables),我們會看到使用表變量如何避免重編譯的副作用——還有它們帶來的其它性能問題。請繼續關注!
附:SQL Server 2014的童鞋可以使用下列腳本通過擴充事件(Extended Event)跟蹤重編譯事件。
1 CREATE EVENT SESSION [TrackRecompiles] ON SERVER
2 ADD EVENT sqlserver.sql_statement_recompile
3 (
4 ACTION
5 (
6 sqlserver.plan_handle,
7 sqlserver.sql_text
8 )
9 )
10 ADD TARGET package0.event_file(SET filename = N'c:\temp\TrackRecompiles.xel')
11 WITH
12 (
13 MAX_MEMORY = 4096 KB,
14 EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
15 MAX_DISPATCH_LATENCY = 30 SECONDS,
16 MAX_EVENT_SIZE = 0 KB,
17 MEMORY_PARTITION_MODE = NONE,
18 TRACK_CAUSALITY = OFF,
19 STARTUP_STATE = OFF
20 )
21 GO
22
23 -- Start the Event Session
24 ALTER EVENT SESSION TrackRecompiles
25 ON SERVER
26 STATE = START
27 GO
參考文章:
https://www.sqlpassion.at/archive/2014/11/10/recompilations-part-1/
注:此文章為
WoodyTu學習MS SQL技術,收集整理相關文檔撰寫,歡迎轉載,請在文章頁面明顯位置給出此文連結!
若您覺得這篇文章還不錯請點選下右下角的推薦,有了您的支援才能激發作者更大的寫作熱情,非常感謝!