如何產生必要的資料庫中繼資料,以建立僅限統計資料的資料庫,SQL Server 中的指令碼

簡介

Microsoft SQL Server 查詢最佳化器會使用下列類型的資訊來決定最佳的查詢計劃︰

  • 資料庫中繼資料

  • 硬體環境

  • 資料庫工作階段狀態

一般而言,您必須模擬所有這些相同類型的資訊,如果您想要重現的測試系統上的查詢最佳化器行為。

Microsoft 客戶支援服務可能會要求您產生指令碼的資料庫中繼資料。Microsoft 客戶支援服務會使用這個指令碼的資料庫中繼資料,來調查最佳化器問題。本文說明的步驟,以產生統計資料指令碼。本文也會說明如何查詢最佳化器使用的資訊。

更多的資訊

如果您依照步驟來產生指令碼之前,您可以使用 SQL Server 2005,,,請確定 SQL Server Management Studio SQL Server 2005 的 Service Pack 2 版或更新版本。 如果您使用較早版本的 SQL Server Management Studio SQL Server 2005 中時,指令碼精靈將不包含所有必要選項的這份文件中的步驟才能正常運作。

整個資料庫的指令碼

當您產生僅限統計資料的複製資料庫時,可能會更容易且更可靠,整個資料庫,而不是指令碼的個別物件的指令碼。當您撰寫指令碼整個資料庫時,您會收到下列優點︰

  • 您要避免遺失重現問題所需的相依物件的問題。

  • 您需要大幅較少的步驟,以選取所需的物件。

注意如果產生指令碼,對於資料庫而言,資料庫的中繼資料包含數千個物件,則指令碼的處理程序會耗用大量的 CPU 資源。 我們建議您在離峰產生指令碼。或者,您可以使用第二個選項,來產生的指令碼的個別物件。

要編寫指令碼之查詢所參考的每個資料庫,請依照下列步驟執行︰

  1. 開啟 SQL Server Management Studio。

  2. 物件總管中,展開資料庫,,然後尋找您要編寫指令碼的資料庫。

  3. 資料庫上按一下滑鼠右鍵,指向 [工作],然後按一下產生指令碼

  4. 在指令碼精靈] 中,確認已選取 [使用正確的資料庫。 按一下以選取所有的指令碼物件中選取的資料庫] 核取方塊,,,然後按一下 [下一步]

  5. 選擇的指令碼選項] 對話方塊中,變更下列設定值從預設值為下表中所列的值。

    指令碼選項

    若要選取的值

    Ansi 填補

    則為 true

    繼續編寫指令碼錯誤

    則為 true

    為相關的物件產生指令碼

    則為 true

    包含系統的條件約束名稱

    則為 true

    編寫定序

    則為 true

    編寫資料庫指令碼建立

    則為 true

    指令碼登入

    則為 true

    指令碼物件層級的權限

    則為 true

    編寫統計資料

    編寫統計資料和長條圖的指令碼

    編寫索引指令碼

    則為 true

    編寫觸發程序

    則為 true

    注意指令碼登入選項和 [指令碼物件層級權限] 選項可能不需要除非結構描述包含除了dbo之外的登入所擁有的物件。

  6. 按一下 [下一步]

  7. 按一下 [指令碼至檔案] 選項,然後輸入檔案名稱。

  8. 按一下 [完成]。

指令碼的個別物件

您可能僅指令碼參照特定的查詢,而不是完整的資料庫指令碼處理的個別物件。不過,除非所有的資料庫物件是由使用與 SCHEMABINDING 子句所建立, sys.depends系統資料表中的相依性資訊不一定精確。 這個不精確度可能會造成下列問題之一︰

  • 指令碼的處理程序不會不編寫指令碼相依的物件。

  • 指令碼的處理程序可能會撰寫指令碼不正確的順序中的物件。若要成功地執行指令碼,您必須手動編輯產生的指令碼。

因此,我們不建議,您撰寫指令碼的個別物件,除非資料庫有很多物件,且指令碼會耗費太長。 如果您必須使用指令碼的個別物件,請依照下列步驟執行︰

  1. 在 SQL Server Management Studio,展開資料庫,然後再找出您要編寫指令碼的資料庫。

  2. 資料庫上按一下滑鼠右鍵,指向指令碼資料庫為、 指向 [建立],然後按一下 [檔案

  3. 輸入檔案名稱,然後再按一下儲存

    核心的資料庫容器會編寫指令碼。此容器包含檔案、 檔案群組、 資料庫及屬性。

  4. 資料庫上按一下滑鼠右鍵,指向 [工作],然後按一下產生指令碼

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

  6. 選擇的指令碼選項] 對話方塊中,變更下列設定值從預設值為下表中所列的值。

    指令碼選項

    若要選取的值

    Ansi 填補

    則為 true

    繼續編寫指令碼錯誤

    則為 true

    包含系統的條件約束名稱

    則為 true

    為相關的物件產生指令碼

    則為 true

    編寫定序

    則為 true

    指令碼登入

    則為 true

    指令碼物件層級的權限

    則為 true

    編寫統計資料

    編寫統計資料和長條圖的指令碼

    指令碼使用資料庫

    則為 true

    編寫索引指令碼

    則為 true

    編寫觸發程序

    則為 true

    注意指令碼登入選項和 [指令碼物件層級權限] 選項可能不需要除非結構描述包含除了dbo之外的登入所擁有的物件。

  7. 在 [選擇物件類型] 對話方塊中,選取有問題的查詢引用的所有資料庫物件類型。

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

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

  9. 此時會顯示的對話方塊,讓您在步驟 7 中選取每個資料庫物件型別。 在每個對話方塊中,選取特定的資料表、 檢視、 函數或其他資料庫物件,然後按一下 [下一步]

  10. 按一下 [指令碼至檔案] 選項,然後指定與您在步驟 3 中輸入的檔案名稱相同。

  11. 按一下 [完成] 以啟動指令碼。

當指令碼完成時,請將指令碼檔案傳送到 Microsoft 的技術支援工程師。 Microsoft 的技術支援工程師可能也會要求下列資訊︰

  • 硬體設定,包括處理器和存在多少實體記憶體的數量

  • 當您執行查詢時所使用中的設定選項

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

使用資訊的方式

下表解釋如何查詢最佳化器會使用此資訊來選取查詢計劃。

中繼資料

條件約束

查詢最佳化器通常會使用來偵測矛盾,查詢和基礎結構描述之間的條件約束。例如,如果查詢"WHERE col = 5"子句和 「 檢查 (col < 5) 」 檢查條件約束已存在,查詢最佳化器可讓您知道資料列不會符合。

查詢最佳化器可讓類似類型的空值屬性有關的扣除。例如,"col 所在 NULL] 的子句已知是 true 或 false,視資料行的 null 屬性而定,並且從外部資料表的外部聯結的資料行是否是。外部索引鍵條件約束的存在是有助於決定基數和適當的聯結順序。查詢最佳化器可以用於消除聯結,或簡化述詞的條件約束資訊。這些變更可能會移除需求以存取基底資料表。

統計資料

統計資訊包含密度及顯示前置的資料行的索引和統計資料索引鍵的散佈的長條圖。依據述詞的性質,查詢最佳化器可能會用密度、 色階分佈圖,或兩者來評估述詞的基數。最新的統計資料所需的精確的基數評估的。基數評估會用來做為輸入估算運算子的成本。因此,您必須擁有良好的基數的評估,以取得最佳的查詢計劃。

表格大小 (資料列與分頁的數目)

查詢最佳化器使用的長條圖和密度,計算指定的述詞為 true 或 false 的可能性。 最終的基數估計值計算方式為機率乘以子系運算子所傳回的資料列數目。在資料表或索引的頁數是估計 IO 成本的因素。 表格大小用來計算成本的掃描,而且當您估計索引搜尋期間存取的分頁數目有助益。

資料庫選項

數個資料庫選項可以影響最佳化的結果。AUTO_CREATE_STATISTICSAUTO_UPDATE_STATISTICS的選項會影響查詢最佳化器將會建立新的統計資料或已過期的更新統計資料。參數化的層級會影響如何之前輸入的查詢就交給查詢最佳化器參數化輸入的查詢。參數化會影響基數估計,也可以防止比對索引檢視表和其他類型的最佳化。[ DATE_CORRELATION_OPTIMIZATION ] 設定會導致最佳化器搜尋資料行之間的關聯性。這個設定會影響基數和成本的估計。

環境

工作階段設定選項

ANSI_NULLS是否設定值會影響 「 NULL = null,則 「 運算式評估為 true。根據目前的設定而定,可能會變更對外部聯結的基數估計。此外,也會變更模稜兩可的運算式。比方說,"col = null,則 「 運算式會評估以不同方式根據設定而有所不同。不過,"col IS NULL"運算式一律會評估相同的方式。

硬體資源

排序和雜湊運算子的成本是根據相對的 SQL Server 可用的記憶體數量而定。例如,如果資料的大小大於快取,查詢最佳化器知道,資料必須永遠多工緩衝處理到磁碟。不過,如果資料的大小則遠遠小於快取項目,則作業很可能在記憶體中進行。如果伺服器有一個以上的處理器,和平行處理原則尚未被停用使用"MAXDOP"提示] 或 [最大平行度] 組態選項,SQL Server 也會考慮不同的最佳化。

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×