您知道執行sp_configure並reconfigure後有可能會清空記憶題中所有執行計畫嗎?

老實說我並不知道,直到今天看到一本SQL管理書籍中寫到,才知道sp_configure居然有這樣的可怕因子

看到書上有寫當然要驗證看看並且筆記下來

 

我的環境中有一個DB1資料庫,該資料庫有一tb1資料表。我先在SSMS中Select該資料表,這樣一來這句Query的執行計畫就存入記憶體中了。

 

我用下圖語法搜尋剛剛我們的Query,並取出該Query的執行計畫。如下圖所示該執行計畫是存在的,並且已經使用過3次。

 

接下來我挑選 [設定平行處理原則] 來做這一次的實驗,由下圖中我們可以看見當下我的設定是發生平行處理時會使用2顆CPU來執行(下圖紅色圈選處)。

 

接下來我用sp_configure 'max degree of parallelism',0 來更動設定值,由下圖紅色圈選處可以看到config_value已經是0了。但由於我們尚未reconfigure所以run_value還是2。

 

我回去再執行一次該Query(這邊就剩略不貼圖了),執行完後再次搜尋一下該Query的執行計畫。如下圖所示該執行計畫的使用次數由3次變為4次。

 

重頭戲就是執行reconfigure囉,如下圖所示我執行了reconfigure。run_value也確定由2變成了0。

 

做完reconfigure後我在一次搜尋該Query的執行計畫。Orz....居然消失了 ! ! !

 

我再回去再執行一次該Query(這邊就剩略不貼圖了),執行完後再次搜尋一下該Query的執行計畫。如下圖所示該執行計畫出現了,但使用次數只有1次。

由以上這一個簡易測試我們可以確定當改變SQL某些設定會讓SQL去清空記憶體中的執行計畫暫存。而執行計畫一清空後會造成部分效能上的問題(因為所有語法都得重新編譯),因此當您要執行此類的參數修正就得注意一下了。MS官方文件有提到更改那些設定會造成執行計畫清除,如下面列出的項目

The following reconfigure operations also clear the procedure cache:

  • access check cache bucket count
  • access check cache quota
  • clr enabled
  • cost threshold for parallelism
  • cross db ownership chaining
  • index create memory
  • max degree of parallelism
  • max server memory
  • max text repl size
  • max worker threads
  • min memory per query
  • min server memory
  • query governor cost limit
  • query wait
  • remote query timeout
  • user options
參考資料來源 : DBCC FREEPROCCACHE (Transact-SQL)

我是ROCK

rockchang@mails.fju.edu.tw