爱华网目錄
伺服器等待的原因
讀取及寫入
遺漏資料庫索引
高成本的遺漏索引
未使用的索引
高成本的使用中索引
常用的索引
邏輯片段的索引
I/O 的高成本查詢
CPU 的高成本查詢
高成本的 CLR 查詢
最常執行的查詢
遭到封鎖的查詢
最低計劃重複使用率
進一步的工作
許多應用程式效能問題可追溯至效能不佳的資料庫查詢；不過，您有許多方法可改進資料庫效能。SQL ServerTM 2005 收集的許多資訊，可幫助您辨別這類效能問題的原因。
SQL Server 2005 會收集所執行之查詢的相關資料。此資料會保留在記憶體中，且會在伺服器重新啟動之後開始累積，這可用來辨別許多問題和計量資料，其中包括與資料表索引、查詢效能及伺服器 I/O 相關的問題和資料。您可以透過 SQL Server 動態管理檢視 (Dynamic Management Views，DMV) 和相關動態管理函數 (Dynamic Management Functions，DMF) 來查詢此資料。這些是以系統為基礎的檢視與函數，其呈現的伺服器狀態資訊可用來診斷問題及調整資料庫效能。
在本文中，我所討論的範圍，都是可利用 SQL Server 2005 所收集的資訊來改進效能的部分。此方式多半為非直覺式的，因為它會收集及檢查現有資料，通常是查詢基礎系統資料。
我會示範如何取得此資訊、討論基礎的 DMV、指明在解譯資料時需要注意的事項，並指出可讓您增強效能的其他範圍。為了達成此目的，我會呈現及檢驗一連串 SQL 指令碼，來詳述 SQL Server 2005 所收集的不同資料層面。您可以從 MSDN® Magazine 網站下載此指令碼的完整說明版本。
我所要討論的某些步驟涉及整個伺服器，包括裝載在指定伺服器上的所有資料庫在內。若有必要，也可以新增適當的篩選以專注於特定的資料庫，例如在查詢中加入其名稱。
相反地，有些查詢會聯結 sys.indexes DMV，這是一種資料庫特定檢視，它只會報告目前資料庫的結果。在這些案例中，我修正了查詢，利用系統預存程序 sp_MSForEachDB 反覆運算伺服器上的所有資料庫，以呈現泛伺服器的結果。
為了將目標鎖定在指定之效能計量資料的最相關記錄，我使用 SQL TOP 函數來限制傳回的記錄數目。
伺服器等待的原因
使用者通常會因為一連串的等待，而導致很差的效能體驗。當 SQL 查詢要執行時必須苦等另一項資源，它就會記錄關於等待原因的詳細資訊。這些詳細資訊可使用 sys.dm_os_wait_stats DMV 來存取。您可以使用 [圖 1] 所顯示的 SQL 指令碼，來檢查所有等待的原因。
Figure1SQL Query Records Causes of Wait Times
複製程式碼
SELECT TOP 10[Wait type] = wait_type,[Wait time (s)] = wait_time_ms / 1000,[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER())FROM sys.dm_os_wait_statsWHERE wait_type NOT LIKE '%SLEEP%'ORDER BY wait_time_ms DESC;
Figure 1SQL Query Records Causes of Wait Times
執行此指令碼的結果將列出依等待花費的總時間排列的等待類型。在我的範例結果中，您會看到在等待原因中，I/O 的排名很高。請注意，我所感興趣的只有邏輯 I/O (在記憶體中讀取/寫入資料)，而非實體 I/O，因為在初始載入之後，資料通常位於記憶體中。
讀取及寫入
高 I/O 使用率是資料存取機制可能不良的指標。SQL Server 2005 會記錄每一個查詢用來履行其需求的讀寫總數。您可以將這些數字加總，以判斷哪些資料庫執行了大部分整體讀寫。
sys.dm_exec_query_stats DMV 包含快取之查詢計劃的彙總效能統計資料。這包括邏輯讀寫次數和查詢執行次數的相關資訊。當您聯結此 DMV 至 sys.dm_exec_sql_text DMF 之後，即可加總資料庫讀寫次數。請注意，我使用的是新的 SQL Server 2005 CROSS APPLY 運算子，來處理此聯結。[圖 2] 顯示我用來識別哪些資料庫使用大部分讀寫的指令碼。
Figure2Identifying the Most Reads and Writes
複製程式碼
SELECT TOP 10[Total Reads] = SUM(total_logical_reads),[Execution count] = SUM(qs.execution_count),DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtGROUP BY DB_NAME(qt.dbid)ORDER BY [Total Reads] DESC;SELECT TOP 10[Total Writes] = SUM(total_logical_writes),[Execution count] = SUM(qs.execution_count),DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtGROUP BY DB_NAME(qt.dbid)ORDER BY [Total Writes] DESC;
Figure 2Identifying the Most Reads and Writes
結果會指出是哪些資料庫在讀寫大部分邏輯頁。最上面一組資料是依 [總讀取數 (Total Reads)] 排序，最下面一組則是依 [總寫入數 (Total Writes)] 排序。
顯而易見，在許多案例中，DatabaseName 是設定為 NULL。此設定會識別臨機的和事前準備的 SQL 陳述式。此詳細資料對於識別原生 SQL (其本身為許多不同問題的潛在原因) 的使用程度很有幫助 (例如，這會指出查詢計劃未重複使用、程式碼未重複使用，以及安全方面可能有問題)。
tempdb 的值越高，表示暫時資料表使用過多、重新編譯過多或裝置沒有效率。其結果可用來識別哪些資料庫主要用於報告 (很多資料的選取)，哪些用於交易式資料庫 (很多更新)。每一種資料庫類型 (報告或交易式) 都有不同的檢索需求。稍後我將詳細說明。
遺漏資料庫索引
當 SQL Server 在處理查詢時，最佳化工具會記錄它嘗試用來滿足該查詢的索引。如果找不到這些索引，SQL Server 會建立該遺漏索引的記錄。您可以使用 sys.dm_db_missing_index_details DMV 來檢視此資訊。
您可以使用 [圖 3] 所示的指令碼，來顯示某指定伺服器上有哪些資料庫遺漏索引。這些遺漏索引的探索很重要，因為索引通常會提供擷取查詢資料的理想路徑。而且這也會減少 I/O 及改進整體效能。我的指令碼會檢查 sys.dm_db_missing_index_details 並加總每個資料庫的遺漏索引數，以方便判斷哪些資料庫需要進一步調查。
Figure3Identifying Missing Databases
複製程式碼
SELECTDatabaseName = DB_NAME(database_id),[Number Indexes Missing] = count(*)FROM sys.dm_db_missing_index_detailsGROUP BY DB_NAME(database_id)ORDER BY 2 DESC;
Figure 3Identifying Missing Databases
資料庫通常會分成交易式和報告式的系統。您可以輕而易舉地在報告資料庫中套用建議的遺漏索引。反之，交易式資料庫通常需要進一步調查其他索引對基礎資料表資料的影響。
高成本的遺漏索引
索引對查詢效能會有不同程度的影響。您可以進一步查看伺服器上所有資料庫之間成本最高的遺漏索引，找出哪些遺漏索引對效能可能有最大的正面影響。
sys.dm_db_missing_index_group_stats DMV 會記錄 SQL 嘗試使用特定遺漏索引的次數。sys.dm_db_missing_index_details DMV 會詳述遺漏索引結構，例如查詢所需的資料行。這兩個 DMV 會透過 sys.dm_db_missing_index_groups DMV 連結在一起。遺漏索引之成本 ([總成本 (Total Cost)] 資料行) 的計算方式，是將平均總使用者成本乘以平均使用者影響，再乘以使用者尋找及使用者掃描的總數。
您可以使用 [圖 4] 所示的指令碼，來識別成本最高的遺漏索引。此查詢的結果會依 [總成本 (Total Cost)] 排序，這會顯示最重要之遺漏索引的成本，以及資料庫/結構描述/資料表的詳細資料，以及遺漏索引所需資料行的詳細資料。具體來說，此指令碼會識別哪些資料行使用於等式及不等式 SQL 陳述式。另外，它還報告其他哪些資料行應做為遺漏索引中的內含資料行使用。內含資料行可讓您滿足更多隱含的查詢，而不必從基礎頁面取得資料，因而使用更少的 I/O 作業並改進效能。
Figure4Cost of Missing Indexes
複製程式碼
SELECTTOP 10[Total Cost]= ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0), avg_user_impact, TableName = statement, [EqualityUsage] = equality_columns, [InequalityUsage] = inequality_columns, [Include Cloumns] = included_columnsFROMsys.dm_db_missing_index_groups gINNER JOINsys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handleINNER JOINsys.dm_db_missing_index_details d ON d.index_handle = g.index_handleORDER BY [Total Cost] DESC;
Figure 4Cost of Missing Indexes(按影像可放大)
請注意，其結果並未指定應該以何種順序建立必要索引中的資料行。若要決定此順序，您應該檢查共同的 SQL 程式碼基底。一般而言，最常選取的資料行應該先出現在索引中。
我也該指出，在計算遺漏索引的成本時，只會考量使用者資料行 (例如 user_seeks 和 user_scans)。這是因為系統資料行傾向於使用統計資料、資料庫一致性檢查 (Database Consistency Checking，DBCC) 及資料定義語言 (Data Definition Language，DDL) 命令來表示，而這些在履行商業功能 (而非資料庫管理功能) 時較不重要。
切記，您需要特別考量到當基礎資料表發生任何資料修改時，可能對於額外索引增加的潛在成本。因此，應該進行基礎 SQL 程式碼基底的其他研究。
如果您發現有許多資料行都是建議內含的資料行，就應該檢查基礎 SQL，因為這可能表示其中有使用 catchall "SELECT *" 陳述式，如果真的是這樣，您可能需要修改 select 查詢。
未使用的索引
未使用的索引對效能可能會有負面影響。這是因為在修改基礎資料表資料時，也需要同時更新索引。當然，這會多花費一點時間，甚至會提高封鎖的機率。
當使用某索引來滿足查詢，且因為對基礎資料表資料套用更新結果而更新索引時，SQL Server 會更新相對應的索引使用量詳細資料。檢視這些使用量詳細資料，即可識別任何未使用的索引。
sys.dm_db_index_usage_stats DMV 會說明索引的使用次數和使用程度。它會聯結至 sys.indexes DMV 中，其中包含建立索引時使用的資訊。您可以檢查不同使用者資料行中的值是否為 0，以識別未使用的索引。同樣地，基於上述理由，仍然會忽略對系統資料行的影響。[圖 5] 所顯示的指令碼，可讓您識別成本最高的未使用索引。
Figure5Identifying Most Costly Unused Indexes
複製程式碼
-- Create required table structure only.-- Note: this SQL must be the same as in the Database loop given in the following step.SELECT TOP 1DatabaseName = DB_NAME(),TableName = OBJECT_NAME(s.[object_id]),IndexName = i.name,user_updates,system_updates-- Useful fields below:--, *INTO #TempUnusedIndexesFROM sys.dm_db_index_usage_stats sINNER JOIN sys.indexes i ONs.[object_id] = i.[object_id]AND s.index_id = i.index_idWHEREs.database_id = DB_ID()AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0ANDuser_seeks = 0AND user_scans = 0AND user_lookups = 0AND s.[object_id] = -999-- Dummy value to get table structure.;-- Loop around all the databases on the server.EXEC sp_MSForEachDB'USE [?];-- Table already exists.INSERT INTO #TempUnusedIndexesSELECT TOP 10DatabaseName = DB_NAME(),TableName = OBJECT_NAME(s.[object_id]),IndexName = i.name,user_updates,system_updatesFROM sys.dm_db_index_usage_stats sINNER JOIN sys.indexes i ONs.[object_id] = i.[object_id]AND s.index_id = i.index_idWHEREs.database_id = DB_ID()AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0ANDuser_seeks = 0AND user_scans = 0AND user_lookups = 0AND i.name IS NOT NULL-- Ignore HEAP indexes.ORDER BY user_updates DESC;'-- Select records.SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC-- Tidy up.DROP TABLE #TempUnusedIndexes
Figure 5Identifying Most Costly Unused Indexes(按影像可放大)
此查詢的結果會顯示未用來擷取資料，但卻因為基礎資料表的變更而更新的索引。這些更新顯示在 [user_updates] 和 [system_updates] 資料行中。其結果是按照已套用至該索引的使用者更新數目排序。
您必須收集足夠的資訊以確保該索引不會使用到，因為您當然不希望移除一年或一季會執行一次之查詢的重要索引。同時請注意，有些索引是用來限制重複插入記錄或資料排序；在移除任何未使用的索引之前，這些因素也必須考慮在內。
查詢的基本格式僅會套用於目前資料庫，因為它聯結的 sys.indexes DMV 只與目前資料庫有關。您可以使用系統預存程序 sp_MSForEachDB，針對伺服器上的所有資料庫擷取結果。我用來執行此動作的模式已說明於「在所有資料庫內建立迴圈」資訊看板中。在指令碼的其他區段中，若我想要反覆運算伺服器上的所有資料庫，則我也會使用此模式。此外，我會篩選出堆積類型的索引，因為這些代表不含正式索引的資料表原生結構。
高成本的使用中索引
另一項有幫助的資訊，是以基礎資料表的變更來識別在已使用索引當中，成本最高的索引。此成本對效能有負面影響，但該索引本身對資料的擷取可能很重要。
sys.dm_db_index_usage_stats DMV 可讓您了解索引的使用頻率和使用程度。此 DMV 會聯結至 sys.indexes DMV，其中包含建立該索引時使用的詳細資料。檢查 [user_updates] 和 [system_updates] 資料行，就會顯示維護率最高的索引。[圖 6] 提供用來識別成本最高的索引之指令碼，並顯示其結果。
Figure6Identifying the Most Costly Indexes
複製程式碼
-- Create required table structure only.-- Note: this SQL must be the same as in the Database loop given in the following step.SELECT TOP 1[Maintenance cost]= (user_updates + system_updates),[Retrieval usage] = (user_seeks + user_scans + user_lookups),DatabaseName = DB_NAME(),TableName = OBJECT_NAME(s.[object_id]),IndexName = i.nameINTO #TempMaintenanceCostFROM sys.dm_db_index_usage_stats sINNER JOIN sys.indexes i ONs.[object_id] = i.[object_id]AND s.index_id = i.index_idWHERE s.database_id = DB_ID()AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0AND (user_updates + system_updates) > 0 -- Only report on active rows.AND s.[object_id] = -999-- Dummy value to get table structure.;-- Loop around all the databases on the server.EXEC sp_MSForEachDB'USE [?];-- Table already exists.INSERT INTO #TempMaintenanceCostSELECT TOP 10[Maintenance cost]= (user_updates + system_updates),[Retrieval usage] = (user_seeks + user_scans + user_lookups),DatabaseName = DB_NAME(),TableName = OBJECT_NAME(s.[object_id]),IndexName = i.nameFROM sys.dm_db_index_usage_stats sINNER JOIN sys.indexes i ONs.[object_id] = i.[object_id]AND s.index_id = i.index_idWHERE s.database_id = DB_ID()AND i.name IS NOT NULL-- Ignore HEAP indexes.AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0AND (user_updates + system_updates) > 0 -- Only report on active rows.ORDER BY [Maintenance cost]DESC;'-- Select records.SELECT TOP 10 * FROM #TempMaintenanceCostORDER BY [Maintenance cost]DESC-- Tidy up.DROP TABLE #TempMaintenanceCost
Figure 6Identifying the Most Costly Indexes(按影像可放大)
其結果會顯示維護率最高的索引，以及相關資料庫/資料表的詳細資料。[維護成本 (Maintanance cost)] 資料行的計算方式，是 user_updates 和 system_updates 資料行的總和。索引的使用性 (顯示在 [擷取使用量 (Retrieval usage)] 資料行中) 的計算方式，是各 user_* 資料行的總和。在決定是否可移除索引時，您一定要考慮索引的使用性。
當需要修改大量資料時，這些結果可幫助您識別在套用更新之前，應該先移除的索引。然後，您就可以在完成所有更新之後重新套用這些索引。
常用的索引
在所有資料庫內建立迴圈
sys.indexes DMV 是資料庫特定檢視。因此，聯結 sys.indexes 的查詢只會報告目前資料庫的結果。不過，您可以使用系統預存程序 sp_MSForEachDB 來反覆運算伺服器上的所有資料庫，進而呈現泛伺服器的結果。以下是我在這些案例中使用的模式。 我建立一個暫存資料表，其中含有類似程式碼主體的必要結構。我為它提供一個不存在的記錄 (即 object_id -999)，以建立此暫存資料表結構。 程式碼的主體執行時，會反覆運算伺服器上的所有資料庫。請注意，每一個資料庫的已擷取記錄數目 (使用 TOP 陳述式)，應該與我想要顯示的記錄數目相同。否則，其結果並未真正代表伺服器上所有資料庫的 TOP n 個記錄。 接著我會從暫存資料表擷取這些記錄，並依我想要的資料行排序 (在此案例中為 [user_updates] 資料行)。
您可以使用 DMV 來識別哪些索引最常使用，這些是通往基礎資料最常見的路徑。如果這些索引本身能夠改進或最佳化，則它們可提供顯著的整體效能改進。
sys.dm_db_index_usage_stats DMV 包含使用索引透過搜尋、掃描及查閱來擷取資料之次數的詳細資料。此 DMV 會聯結至 sys.indexes DMV，其中包含建立該索引時使用的詳細資料。[使用率 (Usage)] 資料行的計算方式，是所有 user_* 欄位的總和。這可以使用 [圖 7] 顯示的指令碼來進行。此查詢的結果會顯示使用索引的次數，並依 [使用量 (Usage)] 排序。
Figure7Identifying Most-Used Indexes
複製程式碼
-- Create required table structure only.-- Note: this SQL must be the same as in the Database loop given in the -- following step.SELECT TOP 1[Usage] = (user_seeks + user_scans + user_lookups),DatabaseName = DB_NAME(),TableName = OBJECT_NAME(s.[object_id]),IndexName = i.nameINTO #TempUsageFROM sys.dm_db_index_usage_stats sINNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]AND s.index_id = i.index_idWHERE s.database_id = DB_ID()AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0AND (user_seeks + user_scans + user_lookups) > 0-- Only report on active rows.AND s.[object_id] = -999-- Dummy value to get table structure.;-- Loop around all the databases on the server.EXEC sp_MSForEachDB'USE [?];-- Table already exists.INSERT INTO #TempUsageSELECT TOP 10[Usage] = (user_seeks + user_scans + user_lookups),DatabaseName = DB_NAME(),TableName = OBJECT_NAME(s.[object_id]),IndexName = i.nameFROM sys.dm_db_index_usage_stats sINNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]AND s.index_id = i.index_idWHERE s.database_id = DB_ID()AND i.name IS NOT NULL-- Ignore HEAP indexes.AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0AND (user_seeks + user_scans + user_lookups) > 0 -- Only report on active rows.ORDER BY [Usage]DESC;'-- Select records.SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC-- Tidy up.DROP TABLE #TempUsage
Figure 7Identifying Most-Used Indexes(按影像可放大)
最常用的索引代表通往基礎資料的最重要存取路徑。顯然，您不應該移除這些索引；然而，您必須檢查它們，以確保其處於最佳狀態。例如，您應該確保索引片段不多 (尤其是對於依序擷取的資料)，且基礎統計資料是最新的。而且您應該移除資料表上任何未使用的索引。
邏輯片段的索引
邏輯索引片段的情況，會指出索引中順序不對的項目所佔的百分比。這與頁面飽和度的片段不同。邏輯片段會對任何使用索引進行順序掃描的作業造成影響。可能的話，應該移除片段。您可以利用重建或重組索引來達成此目的。
您可以使用下列 DMV，來識別邏輯片段最多的索引。sys.dm_db_index_physical_stats DMV 可讓您檢視關於索引大小和片段情況的詳細資料。此 DMV 會聯結至 sys.indexes DMV，其中包含建立該索引時使用的詳細資料。
[圖 8] 顯示用來識別邏輯片段最多之索引的指令碼。結果會依片段百分比排序，並顯示所有資料庫中邏輯片段最多的索引，以及相關的資料庫/資料表。請注意，一開始執行它時需要花點時間 (幾分鐘)，因此我已在指令碼下載中將它做成註解。
Figure8Identifying Logically Fragmented Indexes
複製程式碼
-- Create required table structure only.-- Note: this SQL must be the same as in the Database loop given in the -- following step.SELECT TOP 1DatbaseName = DB_NAME(),TableName = OBJECT_NAME(s.[object_id]),IndexName = i.name,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)INTO #TempFragmentationFROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) sINNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]AND s.index_id = i.index_idWHERE s.[object_id] = -999-- Dummy value just to get table structure.;-- Loop around all the databases on the server.EXEC sp_MSForEachDB'USE [?];-- Table already exists.INSERT INTO #TempFragmentationSELECT TOP 10DatbaseName = DB_NAME(),TableName = OBJECT_NAME(s.[object_id]),IndexName = i.name,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) sINNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]AND s.index_id = i.index_idWHERE s.database_id = DB_ID()AND i.name IS NOT NULL-- Ignore HEAP indexes.AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0ORDER BY [Fragmentation %] DESC;'-- Select records.SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC-- Tidy up.DROP TABLE #TempFragmentation
Figure 8Identifying Logically Fragmented Indexes(按影像可放大)
I/O 的高成本查詢
I/O 是查詢所執行的讀寫次數的計量。這可做為查詢效率的指標，使用很多 I/O 的查詢，通常是改進效能的理想目標。
sys.dm_exec_query_stats DMV 會提供快取之查詢計劃的彙總效能統計資料，包括關於實際和邏輯讀寫及查詢執行次數的詳細資料。其中會包含用來從其內含之父系 SQL 中擷取實際 SQL 的位移。此 DMV 會聯結至 sys.dm_exec_sql_text DMF，其中包含 I/O 相關之 SQL 批次的資訊。不同位移會套用到此批次，以取得基礎的個別 SQL 查詢。[圖 9] 顯示此指令碼。結果會依 [平均 I/O (Averge IO)] 排序，其中會顯示 [平均 I/O (Average IO)]、[總 I/O (Total IO)]、[個別查詢 (Individual Query)]、[父系查詢 (Parent Query)] (如果個別查詢是批次的一部分的話) 及 [資料庫名稱 (Database Name)]。
Figure9Identifying Most Costly Queries by I/O
複製程式碼
SELECT TOP 10[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count,[Total IO] = (total_logical_reads + total_logical_writes),[Execution count] = qs.execution_count,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),[Parent Query] = qt.text,DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtORDER BY [Average IO] DESC;
Figure 9Identifying Most Costly Queries by I/O(按影像可放大)
由於 I/O 反映資料量，因此 [個別查詢 (Individual Query)] 資料行所顯示的查詢，可幫助您判斷哪些範圍可減少 I/O 及改進效能。您可以將查詢提供給 Database Tuning Advisor，以判斷是否應新增任何索引/統計資料來改進查詢的效能。統計資料會包含關於基礎資料的散佈和密度的詳細資料。這會由查詢最佳化工具在判斷最佳查詢存取計劃時使用。
檢查這些查詢中的資料表與「遺漏索引」區段所列出的索引之間是否有連結，也很有幫助 (但請注意，調查建立索引對於會經歷多次更新之資料表的影響是很重要的，因為額外的索引將增加更新基礎資料表資料所花費的時間)。
您可以將此指令碼修改為只報告讀取或寫入，這些分別適用於報告資料庫或交易式資料庫。您也可以報告總計值或平均值，並依序排序。讀取值若很高，表示有遺漏或不完整的索引，或有設計不良的查詢或資料表。
在解譯使用 sys.dm_exec_query_stats DMV 的結果時要小心。例如，查詢計劃有可能隨時從程序快取中移除，所以並非所有的查詢都會被快取。雖然這樣會影響結果，但結果仍然可以是高成本查詢的指標。
CPU 的高成本查詢
您可以採取另一種好用的方式，亦即從 CPU 使用率的角度來分析成本最高的查詢。此方式可揪出效能不佳的查詢。我在這裡使用的 DMV 與我剛才從 I/O 角度用來檢查查詢的 DMV 一樣。您在 [圖 10] 看到的查詢可讓您根據 CPU 使用率的測量，來識別成本最高的查詢。
Figure10Identifying Most Costly Queries by CPU
複製程式碼
SELECT TOP 10[Average CPU used] = total_worker_time / qs.execution_count,[Total CPU used] = total_worker_time,[Execution count] = qs.execution_count,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2ELSE qs.statement_end_offset END -qs.statement_start_offset)/2),[Parent Query] = qt.text,DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtORDER BY [Average CPU used] DESC;
Figure 10SQL Query Records Causes of Wait Times(按影像可放大)
此查詢會傳回平均 CPU 使用率、總 CPU 使用率、個別查詢、父系查詢 (如果個別查詢是批次的一部分的話) 及對應的資料庫名稱。而如前所述，或許值得對查詢執行 Database Tuning Advisor，以判斷是否可進一步改進效能。
高成本的 CLR 查詢
SQL Server 使用 CLR 的頻率越來越高。因此，判斷哪些查詢使用 CLR 將有所幫助，這包括 include 預存程序、函數及觸發程序。
sys.dm_exec_query_stats DMV 包含關於 total_clr_time 和查詢執行次數的詳細資料。其中也包含用來從所包含之父系查詢中擷取實際查詢的位移。此 DMV 會聯結至 sys.dm_exec_sql_text DMF，其中包含關於 SQL 批次的資訊。這會套用不同位移，以取得基礎 SQL。[圖 11] 顯示用來識別成本最高之 CLR 查詢的查詢。
Figure11Identifying Most Costly CLR Queries
複製程式碼
SELECT TOP 10[Average CLR Time] = total_clr_time / execution_count,[Total CLR Time] = total_clr_time,[Execution count] = qs.execution_count,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),[Parent Query] = qt.text,DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats as qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtWHERE total_clr_time <> 0ORDER BY [Average CLR Time] DESC;
Figure 11Identifying Most Costly CLR Queries(按影像可放大)
這樣會傳回平均 CLR 時間、總 CLR 時間、執行計數、個別查詢、父系查詢及資料庫名稱。同樣地，或許值得對查詢執行 Database Tuning Advisor，以判斷是否可進一步改進效能。
最常執行的查詢
您可以修改先前的高成本 CLR 查詢範例，以識別最常執行的查詢。請注意，這裡適用相同的 DMV。相較於最佳化罕用的大型查詢，改進經常執行之查詢的效能可提供更實質的效能改進 (在健全狀態檢查方面，可透過檢查累積 CPU 或 I/O 使用率最高的查詢，來進行交叉檢查)。改進經常執行之查詢的另一項好處是，它也有機會減少鎖定數量和交易時間長度。當然，最後結果就是改進整體系統回應速度。
您可以使用 [圖 12] 所顯示的查詢，來識別最常執行的查詢。執行此動作會顯示執行計數、個別查詢、父系查詢 (如果個別查詢是批次的一部分的話) 及相關資料庫。同樣地，或許值得對查詢執行 Database Tuning Advisor，以判斷是否可進一步改進效能。
Figure12Identifying Queries that Execute Most Often
複製程式碼
SELECT TOP 10[Execution count] = execution_count,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),[Parent Query] = qt.text,DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtORDER BY [Execution count] DESC;
Figure 12Identifying Queries that Execute Most Often(按影像可放大)
遭到封鎖的查詢
最常遭到封鎖的查詢，通常是長時間執行的查詢。在識別這些查詢之後，您可以分析它們，以判斷是否可以且應該加以改寫，以減少封鎖。會造成封鎖的原因包括：以不一致順序使用物件、交易範圍衝突，以及更新未使用的索引。
我所討論過的 sys.dm_exec_query_stats DMV，包含可用來識別最常遭到封鎖之查詢的資料行。平均封鎖時間的計算方式，是 total_elaspsed_time 和 total_worker_time 之間的差，除以 execution_count。
sys.dm_exec_sql_text DMF 包含與封鎖相關之 SQL 批次的詳細資料。這會套用不同的位移，以取得基礎 SQL。
您可以使用 [圖 13] 所顯示的查詢，以識別最常遭到封鎖的查詢。結果會顯示 [平均封鎖時間 (Average Time Blocked)]、[總封鎖時間 (Total Time Blocked)]、[執行計數 (Execution Count)]、[個別查詢 (Individual Query)]、[父系查詢 (Parent Query)] 及 [相關資料庫 (Related Database)]。雖然這些結果是依 [平均封鎖時間 (Average Time Blocked)] 排序，但是依 [總封鎖時間 (Total Time Blocked)] 排序也很有幫助。
Figure13Identifying Queries Most Often Blocked
複製程式碼
SELECT TOP 10[Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count,[Total Time Blocked] = total_elapsed_time - total_worker_time,[Execution count] = qs.execution_count,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),[Parent Query] = qt.text,DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtORDER BY [Average Time Blocked] DESC;
Figure 13Identifying Queries Most Often Blocked(按影像可放大)
如果您檢查查詢，就會發現設計問題 (例如遺漏索引)、交易問題 (所使用資源的順序不對)...等等。Database Tuning Advisor 也會標明可改進的項目。
最低計劃重複使用率
使用預存程序的好處之一，是可以快取及重複使用查詢計劃，而不需要編譯查詢。這樣可節省時間、資源及改進效能。您可以識別具有最低重複使用率的查詢計劃，以進一步調查不重複使用計劃的原因。您可能會發現，改寫某些查詢可最佳化重複使用率。
[圖 14] 顯示我所撰寫的指令碼，用以識別具有最低計劃重複使用率的查詢。此技巧使用我曾經討論過的 DMV 以及我尚未提到的 DMV：dm_exec_cached_plans。此 DMV 也包含關於 SQL Server 已快取之查詢計劃的詳細資料。如您所見，結果會提供使用計劃的次數 ([計劃使用率 (Plan Usage)] 資料行)、個別查詢 (Individual Query)、父系查詢 (Parent Query) 及資料庫名稱 (Database Name)。
Figure14Identifying Queries with Lowest Plan Reuse
複製程式碼
SELECT TOP 10[Plan usage] = cp.usecounts,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1THEN LEN(CONVERT(NVARCHAR(MAX),qt.text)) * 2 ELSE qs.statement_end_offset END -qs.statement_start_offset)/2),[Parent Query] = qt.text,DatabaseName = DB_NAME(qt.dbid),cp.cacheobjtypeFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qtINNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handleWHERE cp.plan_handle=qs.plan_handleORDER BY [Plan usage] ASC;
Figure 14Identifying Queries with Lowest Plan Reuse(按影像可放大)
然後您可以檢查所產生的個別查詢，以識別這些計劃不常重複使用的原因 (如果有的話)。其中一個可能原因，是每次查詢執行時都會重新編譯 - 如果查詢包含不同 SET 陳述式或暫存資料表，就可能發生此狀況。如需重新編譯及計劃快取的詳細說明，請參閱《SQL Server 2005 的批次編譯、重新編譯及計劃快取問題》(網址為 microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx)。
請注意，您也必須確保查詢已經有很多機會可以執行數次。您可以使用相關聯的 SQL 追蹤檔案來確認這一點。
進一步的工作
請記住，不同 DMV 所公開的計量資料不會永久儲存起來，而是僅會保留在記憶體中。當 SQL Server 2005 重新啟動時，這些計量資料就會消失。
您可以根據來自 DMV 的輸出，定期建立資料表，並以時間戳記來儲存其結果。然後，您可以依時間戳記的順序來檢查這些資料表，以識別任何應用程式變更的影響，或給定工作或時間性處理程序的影響。例如，在月底進行的處理程序會有什麼影響？
同樣地，您可以針對給定的追蹤檔案工作量與這類資料表的變更進行關聯性分析，以判斷給定工作量 (例如每日或月底) 對遺漏索引、最常使用查詢...等等的影響。我所提供的指令碼只要加以編輯，即可建立這些資料表並定期執行，成為可持續進行的維護作業。
您也可以利用 Visual Studio 2005 建立自訂報表，來使用本文所討論的指令碼。這些報表可以輕易整合到 SQL Server Management Studio，以提供更令人滿意的資料呈現方式。
可能的話，您應該嘗試整合我所描述的方法與其他方法，例如追蹤及比率分析。這樣可以讓您對改進資料庫效能所需的變更，產生更全面性的了解。
我在這裡示範了如何運用 SQL Server 2005 在正常運作過程中所累積的大量資訊。查詢此資訊可提供很好的導引，並在長期運用之後有效改進查詢效能。例如，您將可以發掘伺服器等待的原因、尋找對效能有負面影響的未使用索引、判斷何者是最常見的查詢，以及何者是成本最高的查詢。您一旦揭露這些隱藏的資料，就能夠發揮豐富的可能性。關於 DMV 還有很多需要更進一步了解的內容，我希望本文可激勵您進行更深入的研究。
Ian Stirk 從 1987 年起，即服務於 IT 領域，曾任開發人員、設計師和架構設計人員。他具備下列資格：M.Sc.、MCSD、MCDBA 及 SCJP。Ian 是一位自由軟體顧問，於英國倫敦從事 Microsoft 技術工作。他的連絡方式為：Ian_Stirk@yahoo.com。
