文章編號: 924947 - 上次校閱: 2007年11月20日 - 版次: 2.5

SQL Server 會大幅增加未使用的空間，讓某些資料表

中英文對照顯示按一下這裡顯示機器翻譯的中英文對照
機器翻譯檢視機器翻譯免責聲明
檢視此文章適用的產品。
系統提示本文適用於您使用的作業系統之外的作業系統。與您不相關的文章內容已停用。

在此頁中

全部展開 | 全部摺疊

結論

本文將告訴您，的案例，在其中 Microsoft SQL Server 會大幅增加未使用的空間，讓某些資料表。然後，本文討論下列兩種方法，您可以用以解決這個問題：
  • 將堆積資料表轉換成使用叢集的索引的資料表。
  • 將 開啟 [物件] 組態選項設定為 [高價值。
回此頁最上方

徵狀

請考慮在 SQL Server 下列案例：
  • SQL Server 執行個體有一或多個使用者資料庫。
  • 這些資料庫中資料表的累計數目大於特定結尾的這一節表格中所列的臨界值。這些資料表包含系統資料表]、 [使用者] 資料表] 和 [暫存資料表。
  • 已連線至的 SQL 執行個體的應用程式伺服器參考大部份的這些資料表。
在此案例中，您可能會注意到下列徵狀：
  • 這些使用者資料庫會消耗的空間會增大以位元速率比典型的速率更快。根據為這些使用者資料庫的 [自動成長] 設定資料庫檔案可能會比它們成長在一般情況下更經常成長。
  • 這些使用者資料庫會消耗的空間未使用的部分將會大於典型的未使用部分。
  • 當檢視儲存體結構對於這些堆積儲存結構、 文字影像樹狀結構和叢集的索引等的使用者資料庫的內容會看到許多未使用的空間。
  • 8 的倍數增加 sysindexes 表中的索引項目保留的空間。不過，索引項 sysindexes 表格中使用的空間會增加只有少數，如 1 或 2。也就是配置在新的範圍中每隔八個分頁，只有幾頁都曾經使用從該範圍。
本文稍早所討論的臨界值會下列條件而有所不同：
  • 您正在使用的 SQL Server 的特定版本
  • 記憶體已設定為 SQL Server
下表列出不同版本的 SQL Server 這些臨界值。
摺疊此表格展開此表格
版本記憶體 (位元組)臨界值
企業/標準/開發人員一個以上的 671,088,6408192
企業/標準/開發人員小於 671,088,6402048
企業/標準/開發人員小於 67,108,864512
企業/標準/開發人員小於 8,388,608128
個人/MSDE不適用128
記憶體對應到您正在使用的 SQL Server 執行個體的 [最大伺服器記憶體] 設定。您可以使用 sp_configure 系統預存程序來設定 最大伺服器記憶體 設定。

附註您可以在 SQL Server 2005 中查詢 sys.dm_db_partition_stats 類別目錄檢視，以取得使用的空間資訊及保留的資訊。 在 SQL Server 2000 中您可以查詢 sysindexes 資料表來取得這項資訊。
回此頁最上方

發生的原因

SQL Server 會維護兩種類型的空間配置在記憶體中的快取資訊： 配置快取和可用空間快取。配置快取會包含範圍識別碼的特定索引的最近配置範圍。可用空間快取會包含最近配置的頁面，從這些配置範圍中的頁碼。它也包含之網頁的狀態。如果這些快取會變成空白，會發生 ＜ 徵狀 ＞ 一節所述的問題。

這些快取變成空原因如下：
  • 您執行必須執行記憶體中的中繼資料資訊和資訊之間的同步處理磁碟的不同命令。比方說您執行下列命令其中之一：
    • DBCC updateusage
    • 更新統計資料
  • 使用 開啟 [物件] 選項來設定的所有物件描述都用。因此，沒有可用的描述元是可用的。
回此頁最上方

其他可行方案

如果要解決這個問題而定，使用 [下列的其中一個方法]：
  • 將堆積資料表轉換成使用叢集的索引的資料表。
  • 將 開啟 [物件] 組態選項設定為 [高價值。
這些方法可以減少配置給資料表的未使用空間的成長率。下列章節將說明這兩種方法。章節也會說明為什麼這些方法協助解決本文將告訴您的問題。
回此頁最上方

將堆積資料表轉換成使用叢集的索引的資料表

附註您將堆積資料表轉換成使用叢集的索引的資料表之後您就可以執行定期以重新指定任何不必要地未使用的空間索引維護作業。比方說，您可以執行下列命令： 
DBCC DBREINDEX
DBCC INDEXDEFRAG
下列是典型的堆積在表格中插入一筆記錄時，會發生的作業順序:
  • 1 正在嘗試將資料列插入資料表。
  • 2 請參閱此資料表的索引識別碼 0 的可用空間快取區。
  • 3. 判斷有效的頁面是否出現在可用空間快取。
  • 4 如果沒錯：。
    • 4.1 如果頁面沒有足夠的空間，插入資料頁面中。
  • 5 如果沒有：。
    • 5.1 SQL Server 必須配置新的頁面為此資料列。
    • 5.2 請參閱此資料表的索引識別碼 0 配置的快取。
    • 5.3 判斷有效的範圍是否出現在配置快取。
    • 5.4 如果沒錯：。
      • 5.4.1 決定是否任何範圍中的八個分頁可用於此新的頁面配置要求。
      • 5.4.2 如果沒錯：。
        • 5.4.2.1 配置從這一頁已配置的範圍。
        • 請前往步驟 5.5.5 5.4.2.2。
      • 5.4.3 如果沒有：。
        • 請前往步驟 5.5.1 5.4.3.1。
    • 5.5 如果沒有：。
      • 5.5.1 使用從通用的分派對應 (GAM) 和 [次要全域配置對應 (SGAM) 資料來找出要配置的新範圍。
      • 5.5.2 配置新的範圍。
      • 5.5.3 使用來滿足新的頁面配置要求，從步驟 5.1 這個新的範圍從一頁。
      • 5.5.4 填入此新配置的範圍配置快取中的相關資訊。
      • 5.5.5 填入該資訊新配置的可用空間快取中的頁面。
如果稍後插入作業之間的空白配置快取和可用空間快取，SQL Server 將新頁面從配置新的範圍，以便插入作業可以成功。從記憶體中移除資料表中繼資料時, 配置快取和可用空間快取也會移除。因此，下次執行插入作業所參考之資料表這些快取是空的。在這種情況下 SQL Server 必須執行步驟 5，並逐步 5.5。這種行為會導致最近配置的範圍，以顯示八個分頁配置使用只有一個頁面時。在最壞狀況案例 56 千位元組 (KB) 的空間可能會浪費在資料表上執行每個插入作業。

下列是典型的有叢集的索引的表格中插入資料時，會發生的作業順序。
  • 1 試著在資料表中插入一個資料列。
  • 2 周遊，尋找 SQL Server 必須儲存叢集索引鍵的資料頁的 B-Tree。
  • 3. 判斷是否有足夠的空間為新的資料列頁面中。
  • 4 如果沒錯：。
    • 4.1 在此頁面中插入資料。
  • 5 如果沒有：。
    • 5.1 SQL Server 必須配置新的頁面為此資料列。
    • 5.2 請參閱此資料表的索引識別碼 1 的配置快取區。
    • 5.3 判斷有效的範圍是否出現在配置快取。
    • 5.4 如果沒錯：。
      • 5.4.1 判斷是否您可以使用任一八個分頁範圍中為此新的頁面配置要求。
      • 5.4.2 如果沒錯：。
        • 5.4.2.1 配置從這一頁已配置的範圍。
      • 5.4.3 如果沒有：。
        • 請前往步驟 5.5.1 5.4.3.1。
    • 5.5 如果沒有：。
      • 5.5.1 使用從 GAM 和 SGAM 資料來找出要配置的新範圍。
      • 5.5.2 配置新的範圍。
      • 5.5.3 使用來滿足新的頁面配置要求，從步驟 5.1 這個範圍從一頁。
      • 5.5.4 填入此新配置的範圍配置快取中的相關資訊。
如果配置快取的可用空間快取清除原因中 ＜ 原因 ＞ 一節所描述的沒有立即必要配置以後的插入作業，此資料表之間的新頁面。只要插入的資料可以符合現有的網頁中特定的叢集索引鍵必須實際所在的位置，這是，則為 True。當資料頁滿並是空的快取，SQL Server 必須執行步驟 5，並逐步 5.5。如您使用叢集的索引如果本文表示的案例新頁面配置的位置比其中一筆記錄插入堆積資料表中的案例很少經常發生。

下列是其中發生問題，而配置快取和可用空間快取是空的兩種情況。假設資料表的結構描述允許的無法容納在資料頁的 100 個資料列。
  • 如果資料表只堆積儲存結構 SQL Server 可以為每個插入作業配置新的範圍，並使用 [該範圍中的 [只有一個頁面。
  • 如果表格叢集的索引 SQL Server 可以為每個 100 插入作業配置新的範圍，並使用 [該範圍中的 [只有一個頁面。
回此頁最上方

設定為高值開放式物件組態選項

之所以發生這個問題，是主要是因為 SQL Server 根據 開啟 [物件] 選項指定的記憶體區域中使用所有介面槽。當您遇到本文所描述的問題時，您可以設定此選項，以容納會在特定 SQL Server 執行個體中參考的幾乎所有資料表的值。

如果要判斷的 開啟物件 的選項值，以避免這個問題，請依照下列步驟執行：
  1. 判斷出現在特定 SQL Server 執行個體中的資料表總數目。

    附註資料表包括使用者資料表和系統資料表。您必須包含從系統資料庫的資料表。
  2. 估計可讓暫存資料表與不同的查詢和應用程式可能使用的工作表的空間的緩衝區大小。
  3. 加入判斷可能會在 SQL Server 執行個體中存取的資料表數目總緩衝區中的使用者資料表和系統資料表的數目。此總數是您應該設定為 開啟物件 的組態選項的值。
因為記憶體耗用量的是預先配置期間 SQL Server 啟動根據這個值，不設定為 [非常高價值 開啟物件 組態選項。如果您將 開啟 [物件] 選項設定為 [非常高價值，原先用於其他用途如用於執行查詢和資料緩衝區的記憶體是而用來維護資料表中繼資料，在 [記憶體] 區域中 開啟 [物件] 選項。

我們不建議您變更在一般情況下 開放式物件 組態。如果您確定您遇到本文所描述的問題，只變更此值。
回此頁最上方

狀況說明

Microsoft 已確認＜適用於＞一節所列之 Microsoft 產品確實有此問題。
回此頁最上方

其他相關資訊

為什麼開啟物件選項設定會導致這個問題

每當查詢參照特定的物件 (資料表)，SQL Server 載入至記憶體，該物件的相關資訊。所有索引中繼資料都有 SQL Server 都已載入記憶體的物件資訊的參考。 本文中討論的配置快取會與特定索引相關聯。開啟 [物件] 選項會控制 SQL Server 執行個體可以載入到記憶體的描述項的數目。

當 SQL Server 啟動時，SQL Server 會將一組特定的介面槽或 描述元，指派給保留在記憶體中版本的物件中繼資料。配置插槽數量取決於 SQL Server 版本以及已設定為 SQL Server 的記憶體，「 徵狀 」 一節所述。這些介面槽原先是可用的清單的一部分。特定的演算法用來從可用的清單中的物件中繼資料指派介面槽。此演算法有下列二種作業模式。

模式 1

SQL Server 啟動之後它會使用其中一個記憶體描述元，從可用的清單時參考新的物件。SQL Server 會繼續載入在一個查詢所參考的物件有關的中繼資料時使用所有現有的介面槽。因為可用的清單中仍有插槽項目，SQL Server 會繼續使用這些介面槽。現有介面槽 SQL Server 並不重複使用已由另一個物件使用的記憶體。

模式 2

如果正在新參照一個查詢中的物件，且可用的清單已經變成空白 SQL Server 會掃描來判斷是否可以從記憶體移除其物件中繼資料的一個插槽的所有物件的現有清單。SQL Server 必須維持在插槽的物件中繼資料，只要查詢會參照資料表。完成該查詢時 SQL Server 可以插槽如果重複使用新的查詢參考不同的資料表。

從記憶體中移除物件中繼資料時, 也會移除其相關聯的索引中繼資料。如果這是 SQL Server 也會遺失配置快取和可用空間快取。然後當不同的查詢一次參考此表格，所有配置快取都是空的。因此，SQL Server 會嘗試配置一個頁面的下一次 SQL Server 會配置新的範圍。

如果 SQL Server 持續收回 SQL Server 無法收回任何介面槽之前所包含的物件中繼資料的介面槽，SQL Server 會增加可用的清單] 的大小。
回此頁最上方

?考

如需有關如何使用 sp_configure 預存程序來變更組態選項，請造訪下列 Microsoft 開發 o 人 h 員 ? 工 u 具 ? 網路 (MSDN) 網站:
http://msdn2.microsoft.com/en-us/library/ms188787.aspx (http://msdn2.microsoft.com/en-us/library/ms188787.aspx)
如需有關 開啟 [物件] 選項的詳細資訊，請造訪下列 MSDN 網站：
http://msdn2.microsoft.com/en-us/library/aa196701(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa196701(SQL.80).aspx)
如需有關 最大伺服器記憶體 組態選項的詳細資訊，請造訪下列 MSDN 網站：
http://msdn2.microsoft.com/en-us/library/ms180797.aspx (http://msdn2.microsoft.com/en-us/library/ms180797.aspx)
如需有關在 SQL 中的資料表與索引架構伺服器，請造訪下列 MSDN 網站：
http://msdn2.microsoft.com/en-us/library/ms208356.aspx (http://msdn2.microsoft.com/en-us/library/ms208356.aspx)
回此頁最上方
這篇文章中的資訊適用於:
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium Based Systems
  • Microsoft SQL Server 2005 Standard Edition for Itanium Based Systems
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Workgroup Edition
回此頁最上方
關鍵字：?
kbmt kbexpertiseadvanced kbtshoot kbsql2005engine kbprb KB924947 KbMtzh
回此頁最上方
機器翻譯機器翻譯
重要：本文是以 Microsoft 機器翻譯軟體翻譯而成，而非使用人工翻譯而成。Microsoft 同時提供使用者人工翻譯及機器翻譯兩個版本的文章，讓使用者可以依其使用語言使用知識庫中的所有文章。但是，機器翻譯的文章可能不盡完美。這些文章中也可能出現拼字、語意或文法上的錯誤，就像外國人在使用本國語言時可能發生的錯誤。Microsoft 不為內容的翻譯錯誤或客戶對該內容的使用所產生的任何錯誤或損害負責。Microsoft也同時將不斷地就機器翻譯軟體進行更新。
按一下這裡查看此文章的英文版本：924947? (http://support.microsoft.com/kb/924947/en-us/ )
回此頁最上方
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。