[SQL]資料庫「自動更新統計資料」選項的設定的迷思

2013-06-11

8594
0
SQL
2013-06-13

要不要設定資料庫選項「自動更新統計資料」為True呢? 98%的SQL 2000都有設定為True，那如果是資料量很大，異動又很頻繁的系統，要如何辦呢?

長期以來，我們公司都是建議將資料庫中的「自動更新統計資料」選項設定成False，然後建立維護計畫在晚上才去執行資料庫的更新統計資料。

會這樣做，可能是因為資料表中有許多的Index，而資料又時常在異動。

怕User在操作系統時，SQL「自動更新統計資料」下去，會導致作業時間變長，甚致發生Timeout而將整個交易給取消，而這交易中包含「自動更新統計資料」，也同時會被Rollback。

那如果「自動更新統計資料」設定為true，何時會啟動呢?

參考「Understanding When Statistics Will Automatically Update」說明，

o The table size has gone from 0 to >0 rows .

o The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then .

o The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered .

以那篇的範例來看，原本資料表中是121,317筆(已超過500筆)，如果要讓「自動更新統計資料」啟動的話，需要異動筆數為24,764筆(121,317 * 0.2 + 500)。

剛建立資料且建立index後，可以發現資料筆數為121,317筆，異動的筆數為0，如下圖，

而在「Understanding When Statistics Will Automatically Update」中的範例，透過bulk insert新增了24,775筆的資料，這時已到達需要更新統計資料的門檻。可發現，異動的筆數為24,775，如下圖，

所以下一個資料有異動或是查詢，就會SQL「自動更新統計資料」就會發動。

透過SQL Server Profiler，勾選事件「Performance」中的Auto Stats，然後再執行Update，就會發現在執行Update時，會先執行Auto Stats(duration為97)，然後才執行真正的Update(整個duration為100，Auto Stats佔了97)，如下圖，

而透過在SQL 2012中開啟DBCC TRACEON (8666)，可看到Update中，包含Clustered Index Update，所佔的比重為86%，如下圖，

經過了auto stats(update PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID)後，PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID的筆數正確了，最後更新時間也改變了，異動筆數也重設成0了，如下圖，