SQL 問答集：壓縮、 成長並重新設計資料庫
SQL 資料庫是在所有的圖形和大小和結構描述。 這個的月份我們 SQL 專家協助 condensing、 成長和重新設計資料庫。
Paul S。 Randal
擁有驚人的 〈 壓縮資料庫
**問：**即使我知道這種方式可能會造成效能問題，我們偶爾強制在我們的資料庫執行壓縮，因為缺乏磁碟的空間。 我們小心的任何索引片段之後。 您可以解釋為什麼壓縮似乎比其他，某些資料庫很多較慢執行，即使它們 ’re 的類似的大小？
**答：**我 ’m 很高興您 ’re cognizant 的執行資料庫壓縮的副作用。 我也知道有時它 ’s 只是不可避免的。
並行的資料庫活動和資料庫中的資料表的結構描述會影響執行階段的資料庫壓縮作業。 這表示兩個資料庫的相等的大小，但具有不同的結構描述可能需要大幅不同的壓縮的時間量。
壓縮的運作方式，藉由移動資料檔案頁面進行合併彙算，它會再傳回給檔案系統 (藉由減少日期檔案大小) 的檔案結尾的可用空間。 若要將資料檔案網頁 SQL Server 必須取得該頁面的獨占鎖定。 這表示沒有任何人可以在頁面上有任何鎖定或任何記錄。 如果有 ’s 涉及取得鎖定資料庫中的同時發生的活動，壓縮會封鎖，並且必須等候它所需要的鎖定。 這會花較長的時間執行比，如果有 ’s 沒有其他活動，在資料庫中的壓縮。
當壓縮移動資料檔案頁面的另一個因素是如果其他類型的資料庫結構有實體資料指標在頁面上移動，它必須更新這些實體的指標與新網頁的位置。 這 isn’t 除了資料表時堆積的問題 (有沒有叢集的索引) 和 (或) 時資料表有一或多個大型物件 (LOB) 資料行儲存關閉資料列 (不同資料表的資料記錄中的)。
當資料表的堆積所有非叢集索引在堆積上的包含實體資料表資料記錄的指標。 當壓縮移動資料頁，從資料表時，非叢集的索引需要更新。 SQL Server 會呼叫到查詢處理器上非叢集索引 100 資料列的索引維護執行一次。
當資料表具有關閉資料列的 LOB 資料資料記錄指向列關閉 LOB 資料。 不過有 ’s 沒有後指標從 LOB 資料資料的記錄。 這表示當壓縮移動文字頁面 (包含關閉資料列 LOB 資料)，所有指向 LOB 資料，在該頁面上的資料記錄時，則必須先更新時。 因為沒有後指標它必須執行資料表掃描，找出正確的資料記錄，以更新。 您可以想像這個程序可以是很慢很多企業營運 (LOB 資料的資料表。
雖然壓縮可能會很慢，從 SQL Server 2005 向前它提供進度報告透過 sys.dm_exec_requests 動態管理檢視 [percent_complete] 資料行。 您也可以監視壓縮資料移動的位元組/秒的效能計數器，在 [資料庫] 效能物件，以檢視進行壓縮的速度。
讓它自動成長
**問：**我 ’m 新 DBA 和我被大量的最佳作法的相關資訊線上讀取資料庫設定。 我混淆的衝突的檢視表上是否自動成長或不應該啟用。 我可以只關閉它而不會造成任何問題嗎？
**答：**簡單的答案是應該永遠啟用自動成長，但不是依賴它。 一般的做法是監視資料及異動記錄檔大小及使用方式和主動成長它們 (或調查突然的非預期成長)。 啟用自動成長的緊急情況下時只要是負責監控檔案大小及使用方式 isn’t 立即可用來管理檔案。
如果自動成長 isn’t 啟用的交易記錄檔，] 和 [檔案填滿，向上，然後所有撰寫直到更多的空間供交易記錄檔中，將會禁止資料庫上的活動。 如果自動成長和 isn’t 啟用資料檔案]、 [插入作業] 或 [資料庫維護作業，例如重建索引可能會失敗。
麻煩的部分找出自動成長設定。 SQL Server 2005 向前，從預設自動成長的交易記錄檔會是 10%到 1 MB 的資料檔案。 但是，百分比架構自動-成長表示，如檔案展開，所以，太，並自動成長的層級。 這也表示如果 don’t 有啟用即時檔案初始化，也可以增加所花費的時間。 因此，這兩種檔案類型都應該有一個固定的自動成長所以自動成長是可預期的行為。
有一個非常大或百分比架構自動成長會特別有問題的交易記錄檔。 立即檔案初始化不是的選項，所以任何新配置的檔案空間必須是零初始化。 雖然零初始化會放置，所有寫入交易記錄檔的活動會被封鎖。 因此合理之間取得平衡，交易記錄檔自動成長，因此 ’s 大型夠該作業可以繼續一的段但不是太大的作業流程中斷的時間太長。
資料的檔案是 1 MB 自動成長 ridiculously 小，但 ’s 難以判斷適當的值。 這取決於您是否想自動-成長的緊急 stopgap 量值或取代手動的資料檔案大小 (管理)。 它也取決於新的空間上，您需要以容納正插入資料庫的資料的每一天。 底端列是：您應該啟用自動成長，並將它設為適當的、 非百分比的數量。
儲存結構描述
**問：**我重新設計我們的資料庫結構描述，因此查詢會更有效率。 一些相關資料表很大的字元資料，我要確定我將它儲存在最有效率的方式。 是否有任何的指導方針或可以共用的最佳作法吗？
**答：**選擇要儲存您的 LOB 資料的方式可以有很大的影響查詢的效能 ’s 很重要，因此您挑選正確的技術。 所有選項的詳細的分析這個的資料行的範圍之外，但以下是一些指導方針：
第一次，資料一定會少於 8000 個位元組嗎？ 如果是這樣的重試 ’s char (n) 或 (n) varchar，但不是其中一個，則為 True 的 LOB 資料型別和 XML，一樣的資料型別 (n)varchar(max)、 varbinary(max)、 (n) 文字或影像除非 ’s 絕對必要。 如果您需要真正的 LOB 資料型別，因為資料大小的 don’t 做 (n) 文字或影像這些型別已被取代，在 SQL Server 2005 中的資料。 它們 ’re 不正常運作的為其他，更新 LOB 資料型別。
第二個，如果您需要真正的 LOB 型別，考慮是否儲存在列 (在相同資料表的資料記錄以資料表中其他資料行) 或關閉資料列資料 (儲存在資料表的資料記錄中的連結不同的資料檔案網頁)。 如果您經常使用 LOB 資料將會儲存它列在查詢可以更有效率地擷取它會更好。 如果不是，’s 通常最好儲存它關閉資料列。 偶爾查詢支付稍有較高的成本來擷取 LOB] 資料，但是資料記錄會小通往 denser 資料儲存體和整體較佳的查詢效能。 請注意，您可以僅儲存區 LOB 資料同資料列多達 8000 個位元組或任何量是可能提供其他的資料行資料記錄中 — 之後它會自動推關閉資料列。
第三，如果資料表中包含 LOB 資料行線上索引作業將會禁止任何包括 LOB 資料行的索引。 這項目定義，影響資料表 ’s 叢集的索引。 這個原因有些人會將 LOB 資料儲存在完全不同的資料表 (垂直分割出這個 LOB 資料行)，然後再執行之間主資料表和 LOB 資料表的 JOIN 操作，當 LOB 資料所需的查詢。 這會導致更多的儲存體，因為巨大的 JOIN，複雜性，但可讓更多的索引維護策略的選擇。
您也可能會關心固定寬度與可變長度資料型別]，甚至可能需要快速存取資料流的資料，在這種情況下，您應該考慮 SQL Server 2008 FILESTREAM 資料型別。 所有 LOB 資料儲存體類型進行更深入的分析，請參閱我的部落格張貼 「 選擇右邊的重要性 LOB 儲存技術 」。
重要的檢查和平衡
**問：**我 reworking 資料庫維護作法在本公司和我即將開始執行 DBCC 檢查我們嚴重的資料庫上。 頻率，我應該執行每個資料庫上的核取嗎？
**答：**主動式的一致性檢查是很重要的一部份的任何廣泛的資料庫維護計劃 — 使用者和系統資料庫。 ’s 也一定要使用網頁的驗證方法。 SQL Server 2005 資料庫並稍後，啟用頁總合檢查碼。 對於 SQL Server 2000 的資料庫使用損毀的頁偵測。
就是而言一致性檢查，’s 難提供絕對的答案的方式通常執行它們。 通常我建議執行它們儘，通常至少每週一次。 最佳的頻率的一致性檢查，您會為傳統 「 依存 」
這裡有幾個要考慮的因素：
第一次，維護視窗為何？ 一致性檢查會耗用大量的 CPU、 記憶體和 I/O 的資源，如果 [維護] 視窗時，您可以饒這些資源是執行所有一致性檢查所需花費的時間較短的您可能無法一次檢查所有的資料庫。 您可能要錯開透過一個完整的星期的一致性檢查，或甚至卸載 (藉由還原備份，並執行一致性檢查，在還原的資料庫) 的一致性檢查，以非生產系統。
第二個，如何穩定是 I/O 子系統資料庫儲存在哪裡？ 如果 I/O 子系統有問題時，您可能會想要盡可能取得最早的損毀可能指示經常執行一致性檢查。 我的經驗較長的損毀進入人、 更廣泛取得，並復原資料庫，同時符合修復點目標及修復時間的目標是，更困難。
下一行是它在您和您的舒適層級 ’s 往上。 8 月 2009，在我在我的部落格上進行問卷並超出 276 的受訪者 37%每週執行一致性檢查並進一步的 25%執行它們每日。 您可以看到我問卷調查一起找出在 www.sqlskills.com/BLOGS/PAUL/post/Importance-of-running-regular-consistency-checks.aspx 檢查頻率的更多有關的完整的結果。
感謝至 Kimberly L。 Tripp 的 SQLskills.com 她的技術檢閱這個月 ’s 資料行。
.jpg)
Paul S。 randal 是 SQLskills.com、 Microsoft 地區的指導和 SQL Server MVP 管理的指導。 他曾在 SQL Server 儲存引擎小組在 Microsoft 從 1999年 2007。 他的 SQL Server 2005 中撰寫 DBCC CHECKDB/修復和 SQL Server 2008 開發期間是負責核心儲存引擎。 Randal 是在嚴重損壞修復、 高可用性和資料庫的維護上的專家，定期在世界各地的會議主持人。 他在 SQLskills.com/blogs/paul，而您的部落格可以在 Twitter.com/PaulRandal Twitter 上找到他。