SQL Server 效能最佳化工具

資料和工作負載樣本

 使用下例說明 SQL Server 效能工具的使用。首先建立下表。

create table testtable (nkey1 int identity, col2 char(300) default 'abc', ckey1 char(1))

  接下來，在這個表中填充 10,000 行測試資料。可以為列 nkey1 中所填充的資料建立非叢集索引。可以為列 ckey1 中的資料建立叢集索引，col2 中的資料僅僅是填充內容，將每一行增加 300 位元組。

declare @counter int set @counter = 1 while (@counter <= 2000) begin insert testtable (ckey1) values ('a') insert testtable (ckey1) values ('b') insert testtable (ckey1) values ('c') insert testtable (ckey1) values ('d') insert testtable (ckey1) values ('e') set @counter = @counter + 1 end 

  資料庫伺服器將進行下面的兩個查詢：

select ckey1,col2 from testtable where ckey1 = 'a' select nkey1,col2 from testtable where nkey1 = 5000 Profiler 

  SQL Server Profiler 記錄資料庫伺服器中所發生活動的詳細資料。可以配置 Profiler 以便用大量的可配置效能資訊監視並記錄在 SQL Server 中執行查詢的一個或多個使用者。可在 Profiler 中記錄的效能資訊有：I/O 統計資訊、CPU 統計資訊、鎖定請求、T-SQL 和 RPC 統計資訊、索引和表掃描、警告和引發的錯誤、資料庫物件的建立/除去、串連/斷開、預存程序操作、遊標操作等等。有關 SQL Profiler 可記錄的全部資訊，請在 SQL Server Books Online 中搜尋字串“Profiler”。

  將 Profiler 資訊裝載到 .trc 檔案中以便用於 Index Tuning Wizard 中

  Profiler 和 Index Tuning Wizard 是強大的工具組合，以協助資料庫管理員在表中建立適當的索引。Profiler 將查詢所消耗的資源記錄在 .trc 檔案中。.trc 檔案可以由 Index Tuning Wizard 讀取，Index Tuning Wizard 同時考慮 .trc 資訊和資料庫表，然後建議應建立什麼樣的索引。Index Tuning Wizard 可讓管理員選擇是自動建立資料庫的適當索引，調度索引以便在以後自動建立還是產生一個可以在以後查看和執行的 T-SQL 指令碼。

  以下是分析查詢負荷的步驟：

  設定 Profiler

  從 SQL Server Enterprise Manager 菜單中選擇 Tools/SQL Server Profiler 啟動 Profiler。

  按 CTRL+N 按鍵組合建立 Profiler 跟蹤。

  鍵入此跟蹤的名稱。

  選擇 Capture to File:複選框，然後選擇要將 Profiler 資訊輸出到其中的 .trc 檔案。

  單擊 OK。

  運行工作負載

  啟動 Query Analyzer（從 SQL Server Enterprise Manager 菜單中選擇 Tools/SQL Server Query Analyzer 或者從開始菜單中選擇開始\程式\Microsoft SQL Server 7.0\Query Analyzer）。

  串連到 SQL Server 並設定將在其中建立表的當前資料庫。

  鍵入或複製以下查詢並將它們粘貼到 Query Analyzer 的查詢時段：

select ckey1,col2 from testtable where ckey1 = 'a' select nkey1,col2 from testtable where nkey1 = 5000 

  
 

  按 CTRL+E 執行這兩個查詢。 停止 Profiler 

  單擊紅色的正方形以停止 Profiler 跟蹤。將 .trc 裝載到 Index Tuning Wizard 

   從 Profiler 菜單中選擇 Tools\Index Tuning WizardsU 啟動 Index Tuning Wizard。單擊 Next。

  選擇要分析的資料庫。單擊 Next。 

  保持 I have a saved workload file 選項按鈕被選，然後單擊 Next。

  選擇 My workload file 選項按鈕，找到用 Profiler 建立的 .trc 檔案，然後單擊 Next。

  在 Select Tables to Tune 對話方塊中，選擇需要進行分析的表，然後單擊 Next。

  Index Tuning Wizard 將在 Index Recommendations 對話方塊中指出應建立的索引。單擊 Next。

  此嚮導可讓您選擇是立即建立索引，調度將在以後自動執行的索引建立任務還是建立帶建立索引命令的 T-SQL 指令碼。選擇需要的選項，然後單擊 Next。

  單擊 Finish。

 Index Tuning Wizard 為樣本資料庫和工作負載產生的 T-SQL。

/* Created by:Index Tuning Wizard */ /* Date: 9/7/98 */ /* Time:6:42:00 PM */ /* Server:HENRYLNT2 */ /* Database :test */ /* Workload file :E:\mssql7\Binn\profiler_load.sql */ USE [test] BEGIN TRANSACTION CREATE CLUSTERED INDEX [testtable2] ON [dbo].[testtable] ([ckey1]) if (@@error <> 0) rollback transaction CREATE CLUSTERED INDEX [testtable2] ON [dbo].[testtable] ([ckey1]) if (@@error <> 0) rollback transaction COMMIT TRANSACTION 

  Index Tuning Wizard 為樣本表和資料所建議的索引就是我們預期的索引。ckey1 只有 5 個唯一值，且每一個值都有 2,000 行。假定其中的一個樣本查詢 (select ckey1, col2 from testtable where ckey1 = 'a') 要求根據 ckey1 中的某個值對錶進行檢索，那麼在 ckey1 列中建立叢集索引是有意義的。第二個查詢 (select nkey1, col2 from testtable where nkey1 = 5000) 根據列 nkey1 的值提取一行。Nkey1 唯一，且有 10,000 行，因此在該列建立非聚集的索引是有意義的。

  Profiler/Index Tuning Wizard 組合在涉及許多表和許多查詢的實際資料庫伺服器環境中功能非常強大。當資料庫進行中典型查詢時，請使用 Profiler 記錄 .trc 檔案。然後將 .trc 檔案裝載到 Index Tuning Wizard，以確定是否建立了正確的索引。根據  Index Tuning Wizard 中的提示自動產生並調度索引建立作業以便在非尖峰時刻運行。定期運行 Profiler/Index Tuning Wizard（比如每周）以查看資料庫伺服器中所執行的查詢是否有較大改動，如果是，則可能需要不同的索引。定期使用 Profiler/Index Tuning Wizard 有助於資料庫管理員在查詢工作負載改變以及資料庫大小隨著時間而增加的同時，保持 SQL Server 以最佳狀態運行。

  將 Profiler 資訊載入到 SQL Server 表以進行分析

Profiler 提供的另一個選項是將資訊記錄在 SQL Server 表中。完成後，就可以查詢整個表以確定是否有某些查詢消耗了過多資源。

將 Profiler 資訊記錄在 SQL Server 表中

從 SQL Server Enterprise Manager 菜單中選擇 Tools/SQL Server Profiler 啟動 Profiler。

按 CTRL+N 按鍵組合建立 Profiler 跟蹤。

鍵入跟蹤的名稱。

單擊 Capture to Table:複選框，然後選擇要將 Profiler 資訊輸出到其中的 SQL Server 表。

單擊 OK。

結束後，單擊紅色的正方形停止 Profiler 跟蹤。

用 Query Analyzer 分析 Profiler 中記錄的資訊

在將這些資訊記錄到 SQL Server 表中後，可以用 Query Analyzer 計算出系統中的哪些查詢消耗資源最多。這樣，資料庫管理員就可以集中時間改進最需要協助的查詢。例如，通常用以下查詢分析從 Profiler 記錄到 SQL Server 表中的資料。此查詢檢索資料庫伺服器中消耗 CPU 資源最多的頭 3 項。返回讀和寫 I/O 資訊以及查詢的期間（用毫秒計）。如果用 Profiler 記錄了大量的資訊，那麼在這個表中建立索引以加快分析查詢是有意義的。例如，如果 CPU 即將成為分析這個表的一個重要標準，那麼在 CPU 列建立非叢集索引應該是一個不錯的主意。

select top 3 TextData,CPU,Reads,Writes,Duration from profiler_out_table order by cpu desc

有關詳細資料，請在 SQL Server Books Online 中搜尋字串“Viewing and Analyzing Traces”、“Troubleshooting SQL Server Profiler”、“Tips for Using SQL Server”、“Common SQL Server Profiler Scenarios”、“Starting SQL Server Profiler”和“Monitoring with SQL Server Profiler”。

Query Analyzer

I/O 統計資訊

Query Analyzer 的 Connections Options 對話方塊 General 選項卡中提供了一個 Show stats I/O 選項。選擇此複選框可以擷取有關 Query Analyzer 中正在執行的查詢所消耗 I/O 量的資訊。

例如，當選擇 Show stats I/O 選項時，查詢“select ckey1, col2 from testtable where ckey1 = 'a'”除返回結果集以外，還返回以下 I/O 資訊：

Table 'testtable'.Scan count 1, logical reads 400, physical reads 382, read-ahead reads 400.

同樣，當選擇 Show stats I/O 選項時，查詢“select nkey1, col2 from testtable where nkey1 = 5000”除了返回結果集以外，還返回以下 I/O 資訊：

Table 'testtable'.Scan count 1, logical reads 400, physical reads 282, read-ahead reads 400.

使用 I/O 統計資訊是一種監視查詢調整效果的有效方法。例如，在此樣本表中建立 Index Tuning Wizard 在上面所推薦的兩個索引，然後再次執行查詢。

在“select ckey1,col2 from testtable where ckey1 = 'a'”的查詢中，叢集索引改進效能的情況如下所示。假定查詢需要提取該表的 20%，則效能改進應該是比較合理的：

Table 'testtable'.Scan count 1, logical reads 91, physical reads 5, read-ahead reads 32.

在“select nkey1,col2 from testtable where nkey1 = 5000”的查詢中，建立非叢集索引對於查詢的效能有著很顯著的影響。假定此查詢只需要從 10,000 行的表中提取一行，那麼用非叢集索引改善效能應該是比較合理的：

Table 'testtable'.Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0.

ShowPlan

通過顯示 Query optimizer 正在執行的任務的詳細資料，使用 ShowPlan 可將注意力集中在有問題的 SQL 查詢上。SQL Server 7.0 提供 ShowPlan 的文本版和圖形版。通過用 CTRL+L 執行 SQL 查詢，可以將 Graphical ShowPlan 的輸出顯示在 Query Analyzer 的 Results 窗格中。表徵圖表示如果查詢已執行，那麼 Query optimizer 應該已執行的操作。箭頭表示查詢的資料流的方向。將滑鼠放置在操作表徵圖上，可以顯示出各個操作的詳細資料。執行 set showplan_all on 命令可以在基於文本的 ShowPlan 中顯示出等價的資訊。如果要跳過操作的詳細資料的顯示，以減少顯示 Query optimizer 操作的基於文本的 ShowPlan 的輸出，可執行命令 set showplan_text on。