檢視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”。 網管u家bitscn.net
　　
　　將 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。 網管bitscn_com
　　單擊 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 行，因此在該列建立非聚集的索引是有意義的。 中國網管論壇bbs.bitsCN.com
　　
　　Profiler/Index Tuning Wizard 組合在涉及許多表和許多查詢的實際資料庫伺服器環境中功能非常強大。當資料庫正在進行典型查詢時，請使用 Profiler 記錄 .trc 檔案。然後將 .trc 檔案裝載到 Index Tuning Wizard，以確定是否建立了正確的索引。根據 Index Tuning Wizard 中的提示自動生成並排程索引建立作業以便在非尖峰時刻執行。定期執行 Profiler/Index Tuning Wizard（比如每週）以檢視資料庫伺服器中所執行的查詢是否有較大改動，如果是，則可能需要不同的索引。定期使用 Profiler/Index Tuning Wizard 有助於資料庫管理員在查詢工作負荷改變以及資料庫大小隨著時間而增加的同時，保持 SQL Server 以最佳狀態執行。
　　
　　有關詳細資訊，請在 SQL Server Books Online 中搜尋“Index Tuning Wizard”和“Index Tuning Recommendations”。
　　
　　將 Profiler 資訊載入到 SQL Server 表以進行分析
　　
　　Profiler 提供的另一個選項是將資訊記錄在 SQL Server 表中。完成後，就可以查詢整個表以確定是否有某些查詢消耗了過多資源。
　　
　　將 Profiler 資訊記錄在 SQL Server 表中
　　
　　從 SQL Server Enterprise Manager 選單中選擇 Tools/SQL Server Profiler 啟動 Profiler。
網管朋友網www_bitscn_net

　　按 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”。
網管網www_bitscn_com

　　
　　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 在上面所推薦的兩個索引，然後再次執行查詢。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-520852/，如需轉載，請註明出處，否則將追究法律責任。