No Money No Honey

文章出處：http://www.dotblogs.com.tw/jimmyyu/archive/2009/07/03/9088.aspx

這是一個曾經發生過的案例，兩年前我在客戶家處理一個效能的案子時，客戶告知：你們的系統最近跑起來比過去慢了不少，每個動作平均大約慢了2-3秒鐘，我心想：我們最近不會改了什麼東西吧。

本著追根究柢的精神，開始跟客戶喇勒：最近有人做什麼嗎？有人版更嗎？DBA有改設定嗎？MIS有做系統調整嗎？這一連串的閒聊問答，最後得到的結果很好：沒有....

根據我在客戶家處理多年問題的經驗，客戶有這種回答也是很合理的，但我直覺問題並不單純，就開始進行問題的測試，從一些現象上看起來，最後把問題轉到DB上去了，最後找到DB中有個設定被修改了，這個設定叫作Auto Close(自動關閉)，詳細的內容可以看這個連結，講的蠻清楚的：http://sharedderrick.blogspot.com/2009/02/autoclose.html

針對這個設定有個地方要特別注意，在SQL Server 2000以前，Create database時，這個值預設是ON；SQL Server 2005以上的版本預設值則是OFF。

http://sharedderrick.blogspot.com/2009/02/autoclose.html

=============================================================================================================認識 SQL Server Database 資料庫選項：AUTO_CLOSE(自動關閉)

資料庫 AUTO_CLOSE 選項

選項值
說明

ON(True)

當設定為：ON 時，資料庫會完全關閉，並在最後一個使用者結束之後釋放其資源。當使用者試圖重新使用資料庫時，會自動重新開啟資料庫。例如，藉由發出 USE database_name 陳述式。

這個選項可能會造成經常存取之資料庫的效能降低，因為在每一個連接之後都會增加開啟和關閉資料庫的負擔。
而且在每一個連接之後，啟用此選項也會排清程序快取。

對於桌面資料庫而言，啟用此選項非常有用，因為它可讓您將資料庫檔案當做一般檔案來管理。您可以移動它們、複製它們來建立備份，甚至可以用電子郵件將它們傳給其他使用者。

在舊版 SQL Server 中，AUTO_CLOSE 是同步處理序，當有重複建立和中斷與 Database Engine 之連接的應用程式存取資料庫時，該處理序可能會降低效能。從 SQL Server 2005 開始，AUTO_CLOSE 處理序是非同步的；重複開啟和關閉資料庫不再降低效能。

當啟用此選項時，sys.databases 目錄檢視中的某些資料行及 DATABASEPROPERTYEX 函數會傳回 NULL，因為資料庫無法擷取資料。若要解決這個問題，請執行 USE 陳述式來開啟資料庫。

此啟用使選項也會造成，當資料庫關閉的作業執行時，會自動清除 SQL Server 執行個體的計畫快取。

若清除計畫快取會導致重新編譯所有後續執行計畫，而且可能會導致查詢效能突然暫時下降。在 SQL Server 2005 Service Pack 2 中，針對每次清除計畫快取的快取存放區，SQL Server 錯誤記錄檔會包含下列參考訊息：「由於某些資料庫維護或重新設定作業，SQL Server 的 '%s' 快取存放區 (計畫快取的一部分) 發生 %d 次快取存放區排清。」只要快取發生排清，這個訊息就會每五分鐘記錄一次。

OFF(False)

當設定為：OFF 時，則在最後一個使用者結束之後資料庫仍為開啟。
資料庫鏡像需要 AUTO_CLOSE OFF。

預設值：
不論作業系統為何，當使用 SQL Server 2000 Desktop Engine 或 SQL Server Express 時，這個選項對所有資料庫是設為：ON(True)；其他版本的 SQL Server 則是設為：OFF(False)。

最佳作法建議
如果經常存取資料庫，請將此資料庫的 AUTO_CLOSE 選項設定為 OFF。

啟用資料庫選項：AUTO_CLOSE的方式如下：

view plaincopy to clipboardprint?

1. 任務1：建立資料庫  
2. 步驟01. 執行以下範例程式碼：  
3. USE [master]  
4. GO  
5. IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DB01')  
6. DROP DATABASE [DB01]  
7. GO  
8. CREATE DATABASE [DB01] ON PRIMARY
9. ( NAME = N'DB01', FILENAME = N'C:\DB01.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )  
10. LOG ON
11. ( NAME = N'DB01_log', FILENAME = N'C:\DB01_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)  
12. GO  
14. 任務2：啟用資料庫選項：AUTO_CLOSE  
15. 步驟01. 執行以下範例程式碼：  
16. USE [master]  
17. GO  
18. -- 設定資料庫DB01的AUTO_CLOSE屬性為ON
19. ALTER DATABASE [DB01]  
20. SET AUTO_CLOSE ON WITH NO_WAIT  
21. GO 

請參考圖1所示：

圖1：設定資料庫選項：AUTO_CLOSE

view plaincopy to clipboardprint?

1. 或是使用SSMS(SQL Server Management Studio)管理工具  
2. 步驟01. 執行SSMS管理工具，在「物件總管」，展開「資料庫」，點選指定的資料庫，滑鼠右鍵，選取：「屬性」。  
3. 步驟02. 在「資料庫屬性」視窗，輸入以下的選項：  
4. 在左邊「選取頁面」窗格，點選「選項」。  
5. 在右邊窗格，在「其他選項」區域，在「自動」區域下，在「自動關閉」方塊部分，下拉選取「True」。  
6. 步驟03. 點選「關閉」，完成設定。  
8. 任務3：測試資料庫選項：AUTO_CLOSE  
9. 步驟01. 執行以下範例程式碼數次後：  
10. USE DB01  
11. GO  
12. USE master  
13. GO  
14. USE DB01  
15. GO  
16. USE tempdb  
17. GO  
18. USE DB01  
19. GO  
20. USE master  
21. GO  
23. 任務4. 檢視「SQL Server 記錄檔」  
24. 步驟01. 執行SSMS管理工具，在「物件總管」，展開「管理」＼「SQL Server 記錄檔」。  
25. 步驟02. 滑鼠雙擊，開啟最新的記錄檔。  
26. 步驟03. 在「記錄檔檢視器」，可以看到有數筆啟動此資料庫的訊息：  
27. Starting up database 'DB01'. 

請參考圖2所示：

圖2：訊息：啟動資料庫Starting up database

view plaincopy to clipboardprint?

1. 任務5. 直接複製此資料庫的資料檔案  
2. 步驟01. 使用檔案總管，在 C: 碟根目錄下，建立資料夾 C:\ArchiveDB。  
3. 步驟02. 使用檔案總管，在 C: 碟根目錄下，選取此資料庫的檔案：DB01.mdf 與 DB01_log.ldf。  
4. 步驟03. 複製此兩個檔案到 C:\ArchiveDB  資料夾內。  
6. 由上，將資料庫選項：AUTO_CLOSE設定為：ON，在確認關閉後，就可以執行複製檔案。也類似於將此資料庫設定為「離線(OFFLINE)」。  
8. 提醒您：  
9. 若資料庫選項：AUTO_CLOSE是設定為OFF(false)時，資料庫引擎將鎖定資料庫檔案，也無法複製或是刪除。 

請參考圖3所示：

圖3：檔案已經開啟，無法複製或是刪除