揭開隱藏數(shù)據(jù)的面紗，優(yōu)化應用程序性能
Ian Stirk
本文討論: - 動態(tài)管理視圖和函數(shù)
- 服務器等待的原因
- 常見索引問題
- 與邏輯 I/O 關聯(lián)的問題
| 本文使用了以下技術:
SQL Server
|
許多應用程序性能問題追根溯源都可以歸咎到性能欠佳的數(shù)據(jù)庫查詢；但是，有許多方法可以用來提高數(shù)據(jù)庫性能。SQL ServerTM 2005 收集許多信息，可以使用這些信息來確定產(chǎn)生此類性能問題的原因。
SQL Server 2005 收集與運行查詢有關的數(shù)據(jù)。此數(shù)據(jù)存放在內存中，并從服務器重新啟動后開始累積，可用于確定許多問題和指標，包括那些與表索引、查詢性能和服務器 I/O 相關的問題和指標?？梢酝ㄟ^ SQL Server 動態(tài)管理視圖 (DMV) 和相關動態(tài)管理函數(shù) (DMF) 查詢此數(shù)據(jù)。這些都是基于系統(tǒng)的視圖和函數(shù)，提供可用于診斷問題和調整數(shù)據(jù)庫性能的服務器狀態(tài)信息。
本文重點介紹使用 SQL Server 2005 收集的信息來提高性能的領域。這種方法在很大程度上是一種非入侵式方法，因為它收集并研究現(xiàn)有數(shù)據(jù)，通常是查詢基礎系統(tǒng)數(shù)據(jù)。
我將演示如何獲取此信息、討論基礎 DMV、確定解釋數(shù)據(jù)時要注意的所有問題，并指出其他一些可能會提高性能之處。為此，我將提供并分析一系列 SQL 腳本，這些腳本詳細說明了 SQL Server 2005 所收集數(shù)據(jù)的各個方面?？梢詮摹禡SDN? 雜志》網(wǎng)站下載此腳本的完整版本（全部帶注釋）。
我將討論的某些步驟會將服務器作為一個整體進行專門分析，包括給定服務器上承載的所有數(shù)據(jù)庫。如果需要，可以添加相應的篩選（如將其名稱添加到查詢）來專門分析給定的數(shù)據(jù)庫。
相反，某些查詢會聯(lián)接到 sys.indexes DMV，這是一個特定于數(shù)據(jù)庫的視圖，只報告針對當前數(shù)據(jù)庫的結果。在這些示例中，我已將查詢修改成使用系統(tǒng)存儲過程 sp_MSForEachDB 對服務器上的所有數(shù)據(jù)庫進行遍歷，從而提供服務器范圍的結果。
為了將給定性能指標的最相關記錄作為目標，我將限制使用 SQL TOP 函數(shù)返回的記錄數(shù)量。
服務器等待的原因
用戶通常會遇到因一系列等待而導致性能下降的情況。每當 SQL 查詢能夠運行但需等待其他資源時，它都會記錄有關等待原因的詳細信息?？梢允褂?sys.dm_os_wait_stats DMV 訪問這些詳細信息。您可以使用圖 1 所示的 SQL 腳本分析所有等待的累積原因。
Figure 1 SQL 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 1 SQL Query Records Causes of Wait Times
運行此腳本所得的結果會列出等待類型，并按花費的總等待時間排序。通過示例結果可看出，I/O 作為一種等待原因排在相對靠前的位置。請注意，我只關注邏輯 I/O（在內存中讀/寫數(shù)據(jù)）而不關注物理 I/O，因為最初加載數(shù)據(jù)后，數(shù)據(jù)通常位于內存中。
讀和寫
I/O 使用率較高可能表示數(shù)據(jù)訪問機制不良。SQL Server 2005 跟蹤每個查詢滿足其需要所使用的讀寫總數(shù)。您可以將這些數(shù)字相加，確定哪些數(shù)據(jù)庫執(zhí)行的總體讀寫操作最多。
sys.dm_exec_query_stats DMV 包含已緩存查詢計劃的匯總性能統(tǒng)計數(shù)據(jù)。此統(tǒng)計數(shù)據(jù)包含有關邏輯讀寫數(shù)量和已執(zhí)行查詢次數(shù)的信息。將此 DMV 聯(lián)接到 sys.dm_exec_sql_text DMF 后，可以按數(shù)據(jù)庫將讀寫數(shù)量加起來。請注意，我使用新的 SQL Server 2005 CROSS APPLY 運算符處理此聯(lián)接操作。圖 2 顯示了用于確定哪些數(shù)據(jù)庫使用讀寫操作最多的腳本。
Figure 2 Identifying 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 2 Identifying the Most Reads and Writes
結果顯示了哪些數(shù)據(jù)庫讀寫的邏輯頁最多。頂部數(shù)據(jù)集按總讀取次數(shù)排序，底部數(shù)據(jù)集按總寫入次數(shù)排序。
可以清楚地看到，DatabaseName 在幾個實例中設置為 NULL。此設置確定特定的和準備好的 SQL 語句。確定本機 SQL 的使用程度時，可使用此詳細信息，它本身也可能引起許多不同的問題。（例如，這說明沒有重用查詢計劃、沒有重用代碼，以及安全領域可能存在問題。）
tempdb 值較高可能表示過度使用臨時表、過度重新編譯或設備效率低。這些結果可以用來確定主要使用哪些數(shù)據(jù)庫進行報告（許多數(shù)據(jù)選擇），這與事務性數(shù)據(jù)庫（許多更新）相對。每個數(shù)據(jù)庫類型（報告數(shù)據(jù)庫或事務性數(shù)據(jù)庫）都有不同的索引需求。稍后我將更詳細地探討此問題。
數(shù)據(jù)庫缺失索引
當 SQL Server 處理查詢時，優(yōu)化器會針對它用來嘗試滿足查詢的索引保留一條記錄。如果找不到這些索引，則 SQL Server 會創(chuàng)建缺失索引的記錄?？梢允褂?sys.dm_db_missing_index_details DMV 來查看此信息。
使用圖 3 所示的腳本，可以顯示給定服務器上的哪些數(shù)據(jù)庫缺失索引。發(fā)現(xiàn)這些缺失索引很重要，因為這些索引通常提供檢索查詢數(shù)據(jù)所需的最佳路徑。這可以隨之減少 I/O，從而提高整體性能。我的腳本會檢查 sys.dm_db_missing_index_details，并按數(shù)據(jù)庫將缺失索引數(shù)相加，從而輕松確定需進一步調查哪些數(shù)據(jù)庫。
Figure 3 Identifying Missing Databases
SELECT DatabaseName = 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 3 Identifying Missing Databases
數(shù)據(jù)庫常分為基于事務的系統(tǒng)和基于報告的系統(tǒng)。將所顯示的缺失索引應用到報告數(shù)據(jù)庫相對容易。另一方面，對事務性數(shù)據(jù)庫而言，通常需要進一步調查對基礎表數(shù)據(jù)附加索引的影響。
高開銷的缺失索引
索引可對查詢性能產(chǎn)生不同程度的影響。您可以深入了解服務器上所有數(shù)據(jù)庫中開銷最高的缺失索引，找出哪些缺失索引可能對性能產(chǎn)生最顯著的正面影響。
sys.dm_db_missing_index_group_stats DMV 記錄了 SQL 嘗試使用特定缺失索引的次數(shù)。sys.dm_db_missing_index_details DMV 詳細顯示缺失索引的結構，例如查詢所需的列。這兩個 DMV 通過 sys.dm_db_missing_index_groups DMV 聯(lián)系在一起。缺失索引的開銷（總開銷列）的計算方法是，用戶平均總開銷與用戶平均影響的積，再乘以用戶搜尋次數(shù)與用戶掃描次數(shù)的和。
可以使用圖 4 所示的腳本來確定開銷最高的缺失索引。此查詢的結果（按“總開銷”排序）顯示最重要缺失索引的成本以及有關數(shù)據(jù)庫/架構/表和缺失索引中所需列的信息。特別是，此腳本可確定哪些列在相等和不相等 SQL 語句中使用。另外，它還報告應將哪些其他列用作缺失索引中的包含性列。使用包含性列可以在不從基礎頁獲取數(shù)據(jù)的情況下滿足更多的覆蓋查詢，因而使用的 I/O 操作更少，從而提高性能。
Figure 4 Cost of Missing Indexes
SELECT TOP 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_columnsFROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handleORDER BY [Total Cost] DESC;
Figure 4 Cost of Missing Indexes (單擊該圖像獲得較大視圖)
請注意，這些結果沒有指定應在必需索引中創(chuàng)建列的順序。為確定順序，您應檢查整體 SQL 代碼庫。一般而言，選擇最多的列應在索引中最先顯示。
還要指出的是，在計算缺失索引的開銷時，只考慮用戶列（如 user_seeks 和 user_scans）。這是因為系統(tǒng)列一般代表對統(tǒng)計數(shù)據(jù)、數(shù)據(jù)庫一致性檢查 (DBCC) 和數(shù)據(jù)定義語言 (DDL) 命令的使用，而且這些列對完成業(yè)務功能（與數(shù)據(jù)庫管理功能相比較）不太重要。
請記住，對于在基礎表中發(fā)生任何數(shù)據(jù)修改時可能產(chǎn)生的額外索引開銷，要特別引起注意，這非常重要。因此，應對基礎 SQL 代碼庫進行進一步的研究。
如果您發(fā)現(xiàn)系統(tǒng)建議將許多列作為要包含的列，則應檢查基礎 SQL，因為這可能表示正在使用 catchall“SELECT *”語句——如果確實如此，則可能要修改選擇查詢。
未使用的索引
未使用的索引可能會對性能產(chǎn)生不良影響。這是因為，修改基礎表數(shù)據(jù)后，可能也需更新索引。當然，這需要額外的時間，而且可能增加阻塞的幾率。
如果執(zhí)行查詢時使用某個索引，而且由于將更新應用到基礎表數(shù)據(jù)而更新了該索引，則 SQL Server 會更新相應的索引使用詳細信息?？梢酝ㄟ^查看這些使用詳細信息來確定任何未使用的索引。
通過 sys.dm_db_index_usage_stats DMV 可以了解使用索引的頻率和程度。它已聯(lián)接到 sys.indexes DMV，后者包含創(chuàng)建索引時所使用的信息。您可以在各用戶列中檢查值 0 以確定未使用的索引。根據(jù)上述原因，再次忽略系統(tǒng)列所產(chǎn)生的影響。使用圖 5 所示的腳本可以確定開銷最高的未使用索引。
Figure 5 Identifying 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 1 DatabaseName = 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 s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.database_id = DB_ID() AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 AND user_seeks = 0 AND user_scans = 0 AND user_lookups = 0 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 #TempUnusedIndexes SELECT TOP 10 DatabaseName = DB_NAME() ,TableName = OBJECT_NAME(s.[object_id]) ,IndexName = i.name ,user_updates ,system_updates FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.database_id = DB_ID() AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 AND user_seeks = 0 AND user_scans = 0 AND user_lookups = 0 AND 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 5 Identifying Most Costly Unused Indexes (單擊該圖像獲得較大視圖)
此查詢結果會顯示尚未用于檢索數(shù)據(jù)但已因基礎表更改而更新的索引。這些更新顯示在 user_updates 列和 system_updates 列中。結果按已應用到索引的用戶更新數(shù)排序。
為確保索引未被使用，您必須要收集足夠多的信息，因為您不希望無意刪除對可能僅每季度或每年運行一次的查詢非常重要的索引。另外，請注意某些索引用于限制插入重復記錄或對數(shù)據(jù)排序；在刪除任何未使用的索引前，還必需考慮這些因素。
查詢的基本形式僅適用于當前數(shù)據(jù)庫，因為它聯(lián)接到 sys.indexes DMV，而后者只與當前數(shù)據(jù)庫有關。您可以使用系統(tǒng)存儲過程 sp_MSForEachDB 提取服務器上所有數(shù)據(jù)庫的結果。提取模式在側欄“循環(huán)遍歷所有數(shù)據(jù)庫”中介紹。在其他要對服務器所有數(shù)據(jù)庫遍歷的腳本部分中，我也使用此模式。另外，我過濾掉堆類型的索引，因為這些索引表示沒有正式索引的表的本機結構。
高開銷的已使用索引
在已使用的索引中確定開銷（即對基礎表進行的更改）最高的索引很有用。此開銷對性能有不良影響，但索引本身可能對數(shù)據(jù)檢索非常重要。
使用 sys.dm_db_index_usage_stats DMV 可以了解使用索引的頻率和程度。此 DMV 已聯(lián)接到 sys.indexes DMV，后者包含創(chuàng)建索引時所使用的詳細信息。檢查 user_updates 和 system_updates 列將顯示維護性最高的索引。圖 6 提供了確定最高開銷索引所使用的腳本并顯示結果。
Figure 6 Identifying 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 s INNER 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') = 0 AND (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 #TempMaintenanceCost SELECT 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 s INNER 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'') = 0 AND (user_updates + system_updates) > 0 -- Only report on active rows.ORDER BY [Maintenance cost] DESC;'-- Select records.SELECT TOP 10 * FROM #TempMaintenanceCost ORDER BY [Maintenance cost] DESC-- Tidy up.DROP TABLE #TempMaintenanceCost
Figure 6 Identifying the Most Costly Indexes (單擊該圖像獲得較大視圖)
結果顯示了維護性最高的索引以及有關數(shù)據(jù)庫/表的詳細信息?！熬S護開銷”列的計算方式是將 user_updates 和 system_updates 列相加。索引的有用性（在“檢索使用率”列中顯示）的計算方式是將各 user_* 列相加。在決定是否要刪除索引時，考慮索引的有用性非常重要。
如果涉及大量的數(shù)據(jù)修改，這些結果可幫您確定在應用更新前應刪除的索引。然后，可在應用所有更新后重新應用這些索引。
常用索引
您可以使用 DMV 確定哪些索引最常使用，這些是到基礎數(shù)據(jù)的最常用路徑。如果這些索引能夠實現(xiàn)自身改進或優(yōu)化，則可以極大地提高整體性能。
sys.dm_db_index_usage_stats DMV 包含了一些詳細信息，它們有關通過搜尋、掃描和查找來檢索數(shù)據(jù)方面對索引的使用頻率。此 DMV 已聯(lián)接到 sys.indexes DMV，后者包含創(chuàng)建索引時所使用的詳細信息?！笆褂寐省绷械挠嬎惴椒ㄊ菍⑺?user_* 字段相加。使用圖 7 所示的腳本即可實現(xiàn)此目的。此查詢結果顯示索引已被使用的次數(shù)，并按“使用率”排序。
Figure 7 Identifying 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 s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.database_id = DB_ID() AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 AND (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 #TempUsage SELECT 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 s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.database_id = DB_ID() AND i.name IS NOT NULL -- Ignore HEAP indexes. AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 AND (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 7 Identifying Most-Used Indexes (單擊該圖像獲得較大視圖)
使用次數(shù)最多的索引代表對基礎數(shù)據(jù)的最重要訪問路線。顯然，您不想刪除這些索引；但應對這些索引進行研究，以確保它們最優(yōu)化。例如，應確保索引的碎片很少（對以順序檢索的數(shù)據(jù)更是如此），且基礎統(tǒng)計數(shù)據(jù)保持最新。而且應該刪除所有未使用的表索引。
邏輯上零碎的索引
邏輯索引碎片表示索引中無序條目所占的百分比。這與頁填充度類型的碎片不同。邏輯碎片會影響任何使用索引的順序掃描。應盡可能消除此碎片?？梢酝ㄟ^重新生成或重新組織索引來消除碎片。
用下面的 DMV 可以確定邏輯上最零碎的索引。使用 sys.dm_db_index_physical_stats DMV 可以查看有關索引的大小和碎片的詳細信息。它已聯(lián)接到 sys.indexes DMV，后者包含創(chuàng)建索引時使用的詳細信息。
圖 8 顯示了確定邏輯上最零碎的索引所使用的腳本。結果按碎片的百分比排序，顯示了所有數(shù)據(jù)庫中邏輯上最零碎的索引以及有關的數(shù)據(jù)庫/表。請注意，此腳本在最初運行時可能需要一段時間（幾分鐘），因此我已在腳本下載中將其注釋掉。
Figure 8 Identifying 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 1 DatbaseName = 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_id WHERE 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 #TempFragmentation SELECT TOP 10 DatbaseName = 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_id WHERE 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 8 Identifying Logically Fragmented Indexes (單擊該圖像獲得較大視圖)
高 I/O 開銷的查詢
I/O 是查詢所進行的讀/寫數(shù)量的一種度量。這可以用來指示查詢的效率——使用許多 I/O 的查詢通常是性能改進研究的適當對象。
sys.dm_exec_query_stats DMV 提供緩存查詢計劃的匯總性能統(tǒng)計信息，包括有關物理和邏輯讀/寫以及查詢執(zhí)行次數(shù)的詳細信息。它包含從實際 SQL 的父 SQL 中提取實際 SQL 所使用的偏移量。此 DMV 已聯(lián)接到 sys.dm_exec_sql_text DMF，后者包含與 I/O 有關的 SQL 批處理的信息。將各種偏移量應用到此批處理可以獲得各基礎 SQL 查詢。腳本如圖 9 所示。結果按平均 I/O 排序，顯示了平均 I/O、總 I/O、單個查詢、父查詢（如果單個查詢是批處理的一部分）以及數(shù)據(jù)庫名稱。
Figure 9 Identifying 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 = -1 THEN 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)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtORDER BY [Average IO] DESC;
Figure 9 Identifying Most Costly Queries by I/O (單擊該圖像獲得較大視圖)
因為 I/O 是數(shù)據(jù)量的一種反映，所以“單個查詢”列中顯示的查詢可幫您確定減少 I/O 和提高性能的地方?？梢詫⒉樵兲峁┙o數(shù)據(jù)庫優(yōu)化顧問，以確定是否應添加任何索引/統(tǒng)計數(shù)據(jù)來提高查詢的性能。統(tǒng)計數(shù)據(jù)包括有關基礎數(shù)據(jù)分布和密度的詳細信息。查詢優(yōu)化器在確定優(yōu)化查詢訪問計劃時使用此統(tǒng)計數(shù)據(jù)。
它還有助于檢查在這些查詢中的表和“缺失索引”部分列出的索引之間是否存在聯(lián)系。（但請注意，如果表經(jīng)歷許多更新，那么研究對這些表創(chuàng)建索引所產(chǎn)生的影響就很重要，因為任何附加索引都會增加更新基礎表數(shù)據(jù)所使用的時間。）
此腳本可以更改為只報告讀操作或寫操作，這分別對報告數(shù)據(jù)庫或事務性數(shù)據(jù)庫很有用。還可以報告總值和平均值，并相應地進行排序。讀操作的值較高可能表示缺失索引或索引不完整，也可能表示查詢或表設計不良。
解釋使用 sys.dm_exec_query_stats DMV 所得的結果時，請務必小心。例如，某個查詢計劃可能會隨時從過程緩存中被刪除，并且不是所有查詢都得到緩存。雖然這會影響結果，但這些結果仍指示出開銷最高的查詢。
高 CPU 開銷的查詢
另一個可采取的有用方法是分析在 CPU 使用率方面開銷最高的查詢。此方法可能更能體現(xiàn)出性能不良的查詢。在此使用的 DMV 與我剛就 I/O 對查詢進行研究時所使用的 DMV 相同。使用圖 10 所示的查詢可以確定按 CPU 使用率衡量的、開銷最高的查詢。
Figure 10 Identifying 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 = -1 THEN 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)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtORDER BY [Average CPU used] DESC;
Figure 10 SQL Query Records Causes of Wait Times (單擊該圖像獲得較大視圖)
此查詢返回平均 CPU 使用率、總 CPU 使用率、單個查詢和父查詢（如果單個查詢是批處理的一部分）以及相應的數(shù)據(jù)庫名稱。而且，如前所述，可能有必要針對查詢運行數(shù)據(jù)庫優(yōu)化顧問，以確定是否有可能進一步改進。
高開銷的 CLR 查詢
SQL Server 不斷增加對 CLR 的使用。因此，確定哪些查詢使用 CLR（包括存儲過程、函數(shù)和觸發(fā)器）最多會非常有幫助。
sys.dm_exec_query_stats DMV 包含有關 total_clr_time 和查詢已執(zhí)行次數(shù)的詳細信息。它還包含從實際查詢的父查詢中提取實際查詢所使用的偏移量。此 DMV 已聯(lián)接到 sys.dm_exec_sql_text DMF，后者包含有關 SQL 批處理的信息。應用各偏移量可以獲取基礎 SQL。圖 11 顯示了用于確定開銷最高的 CLR 的查詢。
Figure 11 Identifying 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 = -1 THEN 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)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 11 Identifying Most Costly CLR Queries (單擊該圖像獲得較大視圖)
此查詢返回平均 CLR 時間、總 CLR 時間、執(zhí)行計數(shù)、單個查詢、父查詢以及數(shù)據(jù)庫名稱。再次指出，可能有必要針對查詢運行數(shù)據(jù)庫優(yōu)化顧問，以確定是否有可能進一步改進。
最常執(zhí)行的查詢
您可以修改前一個用于高開銷 CLR 查詢的示例來確定最常執(zhí)行的查詢。請注意在此適用相同的 DMV。與優(yōu)化很少使用的大型查詢相比，改進頻繁執(zhí)行的查詢可以極大地提高性能。（要進行完整性檢查，可能要通過檢查使用累積 CPU 或 I/O 最高的查詢進行交叉檢查。）改進頻繁運行的查詢的另一個好處是還可以減少鎖的數(shù)量和事務長度。當然，最終結果是改進了系統(tǒng)的整體響應能力。
您可以使用圖 12 所示的查詢來確定最常執(zhí)行的查詢。運行此查詢會顯示執(zhí)行計數(shù)、單個查詢、父查詢（如果單個查詢是批處理的一部分）以及相關數(shù)據(jù)庫。再次指出，有必要在數(shù)據(jù)庫優(yōu)化顧問中運行查詢，以確定是否有可能進一步改進。
Figure 12 Identifying 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 = -1 THEN 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)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtORDER BY [Execution count] DESC;
Figure 12 Identifying Queries that Execute Most Often (單擊該圖像獲得較大視圖)
受阻塞影響的查詢
受阻塞影響最大的查詢通常運行時間都很長。在確定這些查詢后，可以對其進行分析，以確定是否可以（以及應該）對其重寫來減少阻塞。阻塞原因包括以不一致的順序使用對象、事務范圍發(fā)生沖突以及更新未使用的索引。
已討論過的 sys.dm_exec_query_stats DMV 包含一些列，可用于確定受阻塞影響最大的查詢。平均阻塞時間計算方法是，total_elaspsed_time 和 total_worker_time 之間的差除以 execution_count。
sys.dm_exec_sql_text DMF 包含與阻塞有關的 SQL 批處理的詳細信息。對其應用各偏移量可以獲取基礎 SQL。
使用圖 13 所示的查詢可以確定受阻塞影響最大的查詢。查詢結果顯示了平均阻塞時間、總阻塞時間、執(zhí)行計數(shù)、單個查詢、父查詢以及相關數(shù)據(jù)庫。盡管這些結果按“平均阻塞時間”排序，但按“總阻塞時間”排序也會有用。
Figure 13 Identifying 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 = -1 THEN 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)FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtORDER BY [Average Time Blocked] DESC;
Figure 13 Identifying Queries Most Often Blocked (單擊該圖像獲得較大視圖)
如果您研究此查詢，可能會發(fā)現(xiàn)設計問題（如缺失索引）、事務問題（無序使用資源）等。數(shù)據(jù)庫優(yōu)化顧問還會重點指出可能的改進。
最低計劃重用率
使用存儲過程的一個優(yōu)點是，查詢計劃已得到緩存，因而可在不編譯查詢的情況下重用。這一優(yōu)點既節(jié)省時間和資源，又改進了性能。您可以確定重用率最低的查詢計劃，從而進一步調查為何沒有重用這些查詢計劃。您可能會發(fā)現(xiàn)，某些查詢計劃可通過重寫來優(yōu)化重用。
圖 14 顯示了我編寫的腳本，用來確定計劃重用率最低的查詢。此技術使用已討論過的 DMV 以及尚未提到過的 DMV：dm_exec_cached_plans。此 DMV 還包含已被 SQL Server 緩存的查詢計劃的詳細信息。如您所見，查詢結果提供了已使用計劃的次數(shù)（“計劃使用率”列）、單個查詢、父查詢和數(shù)據(jù)庫名稱。
Figure 14 Identifying 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 = -1 THEN 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 14 Identifying Queries with Lowest Plan Reuse (單擊該圖像獲得較大視圖)
然后，您可以研究顯示的各個索引，以確定沒有更多地重用這些計劃（如果有可能）的原因。一個可能的原因是，每次運行查詢時，都要重新編譯查詢——如果查詢包含多個 SET 語句或臨時表，則可能發(fā)生這種情況。有關重新編譯和計劃緩存的詳細討論，請參閱文章“Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005”（SQL Server 2005 中的批處理編譯、重新編譯和計劃緩存）（可在
microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx 找到）。
請注意，您還應確保查詢有充足的機會執(zhí)行多次?？梢允褂藐P聯(lián)的 SQL 跟蹤文件來確認這一點。
進一步探討
請記住，各 DMV 提供的指標不會永久存儲，而只保留在內存中。SQL Server 2005 重新啟動后，這些指標都會丟失。
您可以基于 DMV 的輸出定期創(chuàng)建表，以存儲結果，并附上時間戳。然后，可以按時間戳的先后順序檢查這些表，確定任何應用程序更改所帶來的影響，或確定給定作業(yè)或基于時間的處理所帶來的影響。例如，月末處理過程能帶來什么影響？
同樣，您可以將給定跟蹤文件工作負荷與此類表中的更改關聯(lián)起來，以確定給定工作負荷（如每日或月末工作負荷）給缺失索引、最常使用的查詢所帶來的影響。您可以編輯我在此提供的腳本以創(chuàng)建這些表，并作為日常維護任務的一部分定期運行。
還可以用 Visual Studio 2005 創(chuàng)建自定義報告，它們使用本文中討論的腳本。這些報告可輕松集成到 SQL Server Management Studio，從而提供更令人滿意的數(shù)據(jù)表示。
如有可能，應嘗試將我討論過的方法與其他方法（如跟蹤和比率分析）結合起來使用。這樣可以更全面地了解提高數(shù)據(jù)庫性能所需的更改。
我已在此演示了 SQL Server 2005 在正常工作過程中所累積的信息的用處。對此信息進行查詢可提供一些在當前工作中已證明可提高查詢性能的有用線索。例如，您可能會發(fā)現(xiàn)服務器等待的原因，查找對性能產(chǎn)生不良影響的未使用索引，以及確定哪些查詢是最常用的查詢，哪些查詢開銷最高。一旦您開始探索這些隱藏數(shù)據(jù)，就會發(fā)現(xiàn)無數(shù)的線索。DMV 還有許多需了解的內容，我希望本文能促使您進行深入研究。
