分享方式：

如何產生統計數據腳本，以在 SQL Server 中建立僅限統計數據的資料庫

  • 文章

在本文中，您將瞭解如何使用資料庫元數據來產生統計數據腳本，以在 SQL Server 中建立僅限統計數據的資料庫。

原始產品版本：SQL Server 2014 SQL Server 2012 SQL Server 2008

原始 KB 編號： 914288

簡介

DBCC CLONEDATABASE 是產生僅限架構的資料庫複本以調查效能問題的慣用方法。 只有在您無法使用 時，才使用 DBCC CLONEDATABASE本文中的程式。

Microsoft SQL Server 中的查詢優化器會使用下列類型的資訊來判斷最佳的查詢計劃：

  • 資料庫元數據
  • 硬體環境
  • 資料庫工作階段狀態

一般而言，您必須模擬所有這些相同類型的資訊，以重現測試系統上查詢優化器的行為。

Microsoft 客戶支援服務可能會要求您產生資料庫元數據的腳本，以調查查詢優化器問題。 本文說明產生統計數據腳本的步驟，也說明查詢優化器如何使用資訊。

注意事項

儲存在此數據中的金鑰可能包含 PII 資訊。 例如，如果您的數據表包含含有統計數據的 [電話號碼 ] 數據行，則每個步驟的高索引鍵值都會在產生的統計數據腳本中。

編寫整個資料庫的腳本

當您產生僅限統計數據的複製資料庫時，撰寫整個資料庫的腳本可能更容易且更可靠，而不是編寫個別對象的腳本。 當您編寫整個資料庫的腳本時，您會收到下列優點：

  • 您可以避免遺失重現問題所需的相依對象問題。
  • 您需要較少的步驟來選取必要的物件。

請注意，如果您為資料庫產生腳本，而且資料庫的元數據包含數千個物件，則腳本處理程式會耗用大量的 CPU 資源。 建議您在離峰時段產生腳本，或使用第二個選項 [腳本個別物件 ] 來產生個別物件的腳本。

若要編寫查詢所參考之每個資料庫的腳稿，請遵循下列步驟：

  1. 開啟 SQL Server Management Studio。

  2. 在 物件總管 中，展開 [資料庫]，然後找出您要編寫腳本的資料庫。

  3. 以滑鼠右鍵按兩下資料庫，指向 [ 工作]，然後選取 [ 產生腳本]。

  4. 在腳本精靈中，確認已選取正確的資料庫。 按兩下以選取 [編寫整個資料庫和所有資料庫物件的腳本]，然後選取 [ 下一步]。

  5. 在 [ 選擇文稿選項] 對話框中，選取 [ 進階 ] 按鈕，將下列設定從預設值變更為下表所列的值。

    文本選項 要選取的值
    Ansi 填補 True
    發生錯誤時繼續編寫腳本 True
    產生相依物件的腳本 True
    包含系統條件約束名稱 True
    腳本定序 True
    腳本登入 True
    腳本物件層級許可權 True
    腳本統計數據 編寫統計數據和直方圖的腳本
    腳本索引 True
    腳本觸發程式 True

    注意事項

    請注意，除非架構包含 dbo 以外的登入所擁有的對象，否則可能不需要 [腳本登入] 選項和 [腳本物件層級許可權] 選項。

  6. 選取 [確定 ] 以儲存變更，然後關閉 [ 進階腳本選項] 頁面。

  7. 選 取 [儲存至檔案] ，然後選取 [單一檔案] 選項。

  8. 檢閱您的選取專案，然後選取 [ 下一步]。

  9. 選取 [完成]。

編寫個別物件的腳本

您只能編寫特定查詢所參考之個別物件的腳本，而不能編寫完整資料庫的腳本。 不過，除非使用 子句建立 WITH SCHEMABINDING 所有資料庫對象，否則系統數據表中的 sys.depends 相依性資訊可能不一定正確。 此不精確可能會造成下列其中一個問題：

  • 腳本處理程式不會編寫相依物件的腳本。

  • 腳本處理程式可能會以不正確的順序編寫物件的腳本。 若要成功執行文稿，您必須手動編輯產生的腳本。

因此，除非資料庫有許多對象，否則不建議您編寫個別物件的腳本，否則腳本會花費太多時間。 如果您必須使用文稿個別物件，請遵循下列步驟：

  1. 在 [SQL Server Management Studio 中，展開 [資料庫]，然後找出您要編寫腳本的資料庫。

  2. 以滑鼠右鍵按兩下資料庫，指向 [將資料庫編寫為腳本]，然後指向 [ 建立至]，然後選取 [ 檔案]。

  3. 輸入檔名，然後選取 [ 儲存]。

    核心資料庫容器將會編寫腳本。 此容器包含檔案、檔案群組、資料庫和屬性。

  4. 以滑鼠右鍵按兩下資料庫，指向 [ 工作]，然後選取 [ 產生腳本]。

  5. 請確定已選取正確的資料庫，然後選取 [ 下一步]。

  6. 在 [ 選擇物件類型 ] 對話框中，選擇 [選取特定資料庫物件]，然後選取有問題查詢參考的所有資料庫物件類型。

    例如，如果查詢只參考數據表，請選取 [ 數據表]。 如果查詢參考檢視，請選取 [ 檢視和數據表]。 如果有問題的查詢使用使用者定義函數，請選取 [ 函式]。

  7. 當您選取查詢所參考的所有物件類型時，請選取 [ 下一步]。

  8. 在 [ 設定文本選項] 對話框中，選取 [ 進階 ] 按鈕，並將下列設定從預設值變更為 [ 進階腳本選項 ] 頁面上下表所列的值。

    文本選項 要選取的值
    Ansi Padding True
    發生錯誤時繼續編寫腳本 True
    包含系統條件約束名稱 True
    產生相依物件的腳本 True
    腳本定序 True
    腳本登入 True
    腳本物件層級許可權 True
    腳本統計數據 編寫統計數據和直方圖的腳本
    腳本 USE DATABASE True
    腳本索引 True
    腳本觸發程式 True

    注意事項

    請注意，除非架構包含 dbo 以外的登入所擁有的對象，否則可能不需要 [腳本登入] 和 [腳本物件層級許可權] 選項。

  9. 選取 [確定 ] 以儲存並關閉 [ 進階腳本選項] 頁面。

    您在步驟 7 中選取的每個資料庫物件類型都會出現對話框。

  10. 在每個對話框中，選取特定的數據表、檢視、函式或其他資料庫物件，然後選取 [ 下一步]。

  11. 選取 [腳稿到檔案] 選項，然後指定您在步驟 3 中輸入的相同檔名。

  12. 選 取 [完成 ] 以啟動腳本。

    腳本完成後，請將腳本檔案傳送給 Microsoft 支援服務 工程師。 Microsoft 支援服務 工程師可能也會要求下列資訊：

    • 硬體組態，包括處理器數目和物理記憶體數量。

    • 執行查詢時作用中的SET選項。

    請注意，您可能已經藉由傳送 SQLDiag 報表或 SQL Profiler 追蹤來提供這項資訊。 您可能也使用了另一個方法來提供這項資訊。

如何使用資訊

下表說明查詢優化器如何使用這項資訊來選取查詢計劃。

中繼資料

選項 說明
條件約束 查詢優化器經常使用條件約束來偵測查詢與基礎架構之間的差異。 例如，如果查詢包含 子句， WHERE col = 5 而且 CHECK (col < 5) 基礎表上存在條件約束，則查詢優化器會知道沒有任何數據列相符。 查詢優化器會對 Null 屬性進行類似的推斷類型。 例如， WHERE col IS NULL 子句已知為 true 或 false，視數據行的 Null 屬性以及數據行是否來自外部聯接的外部數據表而定。 FOREIGN KEY 條件約束的存在有助於判斷基數和適當的聯結順序。 查詢優化器可以使用條件約束資訊來排除聯結或簡化述詞。 這些變更可能會移除存取基表的需求。
統計資料 統計數據資訊包含密度和直方圖，顯示索引和統計數據索引鍵的前置數據行分佈。 根據述詞的本質，查詢優化器可能會使用密度、直方圖或兩者來估計述詞的基數。 精確的基數估計需要最新的統計數據。 基數估計會用來作為估計運算元成本的輸入。 因此，您必須有良好的基數估計值，才能取得最佳的查詢計劃。
數據表大小 (列和頁數) 查詢優化器會使用直方圖和密度來計算指定述詞為 true 或 false 的機率。 最終基數估計值的計算方式是將機率乘以數運算符傳回的數據列數目。 數據表或索引中的頁數是估計 IO 成本的一個因素。 數據表大小是用來計算掃描的成本，當您估計在索引搜尋期間將存取的頁面數目時會很有用。
資料庫選項 數個資料庫選項可能會影響優化。 和 AUTO_CREATE_STATISTICSAUTO_UPDATE_STATISTICS 選項會影響查詢優化器是否會建立新的統計數據或更新過期的統計數據。 參數化層級會影響在將輸入查詢交給查詢優化器之前，輸入查詢的參數化方式。 參數化可能會影響基數估計，也可以防止對索引檢視和其他類型的優化進行比對。 此 DATE_CORRELATION_OPTIMIZATION 設定會讓優化工具搜尋數據行之間的相互關聯。 此設定會影響基數和成本估計。

環境

選項 說明
會話 SET 選項 設定 ANSI_NULLS 會影響表達式 NULL = NULL 是否評估為 true。 外部聯接的基數估計可能會根據目前的設定而變更。 此外，模棱兩可的表達式也可能變更。 例如，表達式 col = NULL 會根據 設定以不同的方式評估。 不過，表達式 col IS NULL 一律會以相同方式評估。
硬體資源 排序和哈希運算子的成本取決於可供 SQL Server 使用的相對記憶體數量。 例如，如果數據的大小大於快取，查詢優化器就會知道數據必須一律多任務緩衝處理到磁碟。 不過，如果數據的大小遠小於快取，則作業可能會在記憶體中完成。 如果伺服器有多個處理器，且未使用提示或平行處理原則的最大程度組態選項停用MAXDOP平行處理原則，SQL Server 也會考慮不同的優化。

另請參閱