文章出處：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：建立資料庫
- 步驟01. 執行以下範例程式碼：
- USE [master]
- GO
- IF EXISTS (SELECT name FROM sys.databases WHERE name = N’DB01′)
- DROP DATABASE [DB01]
- GO
- CREATE DATABASE [DB01] ON PRIMARY
- ( NAME = N’DB01′, FILENAME = N’C:\DB01.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )
- LOG ON
- ( NAME = N’DB01_log’, FILENAME = N’C:\DB01_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
- GO
- 任務2：啟用資料庫選項：AUTO_CLOSE
- 步驟01. 執行以下範例程式碼：
- USE [master]
- GO
- — 設定資料庫DB01的AUTO_CLOSE屬性為ON
- ALTER DATABASE [DB01]
- SET AUTO_CLOSE ON WITH NO_WAIT
- GO
請參考圖1所示： 
圖1：設定資料庫選項：AUTO_CLOSE
view plaincopy to clipboardprint?
- 或是使用SSMS(SQL Server Management Studio)管理工具
- 步驟01. 執行SSMS管理工具，在「物件總管」，展開「資料庫」，點選指定的資料庫，滑鼠右鍵，選取：「屬性」。
- 步驟02. 在「資料庫屬性」視窗，輸入以下的選項：
- 在左邊「選取頁面」窗格，點選「選項」。
- 在右邊窗格，在「其他選項」區域，在「自動」區域下，在「自動關閉」方塊部分，下拉選取「True」。
- 步驟03. 點選「關閉」，完成設定。
- 任務3：測試資料庫選項：AUTO_CLOSE
- 步驟01. 執行以下範例程式碼數次後：
- USE DB01
- GO
- USE master
- GO
- USE DB01
- GO
- USE tempdb
- GO
- USE DB01
- GO
- USE master
- GO
- 任務4. 檢視「SQL Server 記錄檔」
- 步驟01. 執行SSMS管理工具，在「物件總管」，展開「管理」＼「SQL Server 記錄檔」。
- 步驟02. 滑鼠雙擊，開啟最新的記錄檔。
- 步驟03. 在「記錄檔檢視器」，可以看到有數筆啟動此資料庫的訊息：
- Starting up database ‘DB01’.
請參考圖2所示： 
圖2：訊息：啟動資料庫Starting up database
view plaincopy to clipboardprint?
- 任務5. 直接複製此資料庫的資料檔案
- 步驟01. 使用檔案總管，在 C: 碟根目錄下，建立資料夾 C:\ArchiveDB。
- 步驟02. 使用檔案總管，在 C: 碟根目錄下，選取此資料庫的檔案：DB01.mdf 與 DB01_log.ldf。
- 步驟03. 複製此兩個檔案到 C:\ArchiveDB 資料夾內。
- 由上，將資料庫選項：AUTO_CLOSE設定為：ON，在確認關閉後，就可以執行複製檔案。也類似於將此資料庫設定為「離線(OFFLINE)」。
- 提醒您：
- 若資料庫選項：AUTO_CLOSE是設定為OFF(false)時，資料庫引擎將鎖定資料庫檔案，也無法複製或是刪除。
請參考圖3所示： 
圖3：檔案已經開啟，無法複製或是刪除
近期留言