Rock的SQL筆記本

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

 

我的環境中有一個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

我是ROCK

rockchang@mails.fju.edu.tw