本文探討: - 動態(tài)管理檢視與函數(shù)
- 伺服器等待的原因
- 一般索引問題
- 邏輯 I/O 相關(guān)聯(lián)的問題
| |
許多應(yīng)用程式效能問題可追溯至效能不佳的資料庫查詢；不過，您有許多方法可改進(jìn)資料庫效能。SQL ServerTM 2005 收集的許多資訊，可幫助您辨別這類效能問題的原因。
SQL Server 2005 會收集所執(zhí)行之查詢的相關(guān)資料。此資料會保留在記憶體中，且會在伺服器重新啟動之後開始累積，這可用來辨別許多問題和計量資料，其中包括與資料表索引、查詢效能及伺服器 I/O 相關(guān)的問題和資料。您可以透過 SQL Server 動態(tài)管理檢視 (Dynamic Management Views，DMV) 和相關(guān)動態(tài)管理函數(shù) (Dynamic Management Functions，DMF) 來查詢此資料。這些是以系統(tǒng)為基礎(chǔ)的檢視與函數(shù)，其呈現(xiàn)的伺服器狀態(tài)資訊可用來診斷問題及調(diào)整資料庫效能。
在本文中，我所討論的範(fàn)圍，都是可利用 SQL Server 2005 所收集的資訊來改進(jìn)效能的部分。此方式多半為非直覺式的，因為它會收集及檢查現(xiàn)有資料，通常是查詢基礎(chǔ)系統(tǒng)資料。
我會示範(fàn)如何取得此資訊、討論基礎(chǔ)的 DMV、指明在解譯資料時需要注意的事項，並指出可讓您增強(qiáng)效能的其他範(fàn)圍。為了達(dá)成此目的，我會呈現(xiàn)及檢驗一連串 SQL 指令碼，來詳述 SQL Server 2005 所收集的不同資料層面。您可以從 MSDN® Magazine 網(wǎng)站下載此指令碼的完整說明版本。
我所要討論的某些步驟涉及整個伺服器，包括裝載在指定伺服器上的所有資料庫在內(nèi)。若有必要，也可以新增適當(dāng)?shù)暮Y選以專注於特定的資料庫，例如在查詢中加入其名稱。
相反地，有些查詢會聯(lián)結(jié) sys.indexes DMV，這是一種資料庫特定檢視，它只會報告目前資料庫的結(jié)果。在這些案例中，我修正了查詢，利用系統(tǒng)預(yù)存程序 sp_MSForEachDB 反覆運算伺服器上的所有資料庫，以呈現(xiàn)泛伺服器的結(jié)果。
為了將目標(biāo)鎖定在指定之效能計量資料的最相關(guān)記錄，我使用 SQL TOP 函數(shù)來限制傳回的記錄數(shù)目。
伺服器等待的原因
使用者通常會因為一連串的等待，而導(dǎo)致很差的效能體驗。當(dāng) SQL 查詢要執(zhí)行時必須苦等另一項資源，它就會記錄關(guān)於等待原因的詳細(xì)資訊。這些詳細(xì)資訊可使用 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
執(zhí)行此指令碼的結(jié)果將列出依等待花費的總時間排列的等待類型。在我的範(fàn)例結(jié)果中，您會看到在等待原因中，I/O 的排名很高。請注意，我所感興趣的只有邏輯 I/O (在記憶體中讀取/寫入資料)，而非實體 I/O，因為在初始載入之後，資料通常位於記憶體中。
讀取及寫入
高 I/O 使用率是資料存取機(jī)制可能不良的指標(biāo)。SQL Server 2005 會記錄每一個查詢用來履行其需求的讀寫總數(shù)。您可以將這些數(shù)字加總，以判斷哪些資料庫執(zhí)行了大部分整體讀寫。
sys.dm_exec_query_stats DMV 包含快取之查詢計劃的彙總效能統(tǒng)計資料。這包括邏輯讀寫次數(shù)和查詢執(zhí)行次數(shù)的相關(guān)資訊。當(dāng)您聯(lián)結(jié)此 DMV 至 sys.dm_exec_sql_text DMF 之後，即可加總資料庫讀寫次數(shù)。請注意，我使用的是新的 SQL Server 2005 CROSS APPLY 運算子，來處理此聯(lián)結(jié)。[圖 2] 顯示我用來識別哪些資料庫使用大部分讀寫的指令碼。
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
結(jié)果會指出是哪些資料庫在讀寫大部分邏輯頁。最上面一組資料是依 [總讀取數(shù) (Total Reads)] 排序，最下面一組則是依 [總寫入數(shù) (Total Writes)] 排序。
顯而易見，在許多案例中，DatabaseName 是設(shè)定為 NULL。此設(shè)定會識別臨機(jī)的和事前準(zhǔn)備的 SQL 陳述式。此詳細(xì)資料對於識別原生 SQL (其本身為許多不同問題的潛在原因) 的使用程度很有幫助 (例如，這會指出查詢計劃未重複使用、程式碼未重複使用，以及安全方面可能有問題)。
tempdb 的值越高，表示暫時資料表使用過多、重新編譯過多或裝置沒有效率。其結(jié)果可用來識別哪些資料庫主要用於報告 (很多資料的選取)，哪些用於交易式資料庫 (很多更新)。每一種資料庫類型 (報告或交易式) 都有不同的檢索需求。稍後我將詳細(xì)說明。
遺漏資料庫索引
當(dāng) SQL Server 在處理查詢時，最佳化工具會記錄它嘗試用來滿足該查詢的索引。如果找不到這些索引，SQL Server 會建立該遺漏索引的記錄。您可以使用 sys.dm_db_missing_index_details DMV 來檢視此資訊。
您可以使用 [圖 3] 所示的指令碼，來顯示某指定伺服器上有哪些資料庫遺漏索引。這些遺漏索引的探索很重要，因為索引通常會提供擷取查詢資料的理想路徑。而且這也會減少 I/O 及改進(jìn)整體效能。我的指令碼會檢查 sys.dm_db_missing_index_details 並加總每個資料庫的遺漏索引數(shù)，以方便判斷哪些資料庫需要進(jìn)一步調(diào)查。
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
資料庫通常會分成交易式和報告式的系統(tǒng)。您可以輕而易舉地在報告資料庫中套用建議的遺漏索引。反之，交易式資料庫通常需要進(jìn)一步調(diào)查其他索引對基礎(chǔ)資料表資料的影響。
高成本的遺漏索引
索引對查詢效能會有不同程度的影響。您可以進(jìn)一步查看伺服器上所有資料庫之間成本最高的遺漏索引，找出哪些遺漏索引對效能可能有最大的正面影響。
sys.dm_db_missing_index_group_stats DMV 會記錄 SQL 嘗試使用特定遺漏索引的次數(shù)。sys.dm_db_missing_index_details DMV 會詳述遺漏索引結(jié)構(gòu)，例如查詢所需的資料行。這兩個 DMV 會透過 sys.dm_db_missing_index_groups DMV 連結(jié)在一起。遺漏索引之成本 ([總成本 (Total Cost)] 資料行) 的計算方式，是將平均總使用者成本乘以平均使用者影響，再乘以使用者尋找及使用者掃描的總數(shù)。
您可以使用 [圖 4] 所示的指令碼，來識別成本最高的遺漏索引。此查詢的結(jié)果會依 [總成本 (Total Cost)] 排序，這會顯示最重要之遺漏索引的成本，以及資料庫/結(jié)構(gòu)描述/資料表的詳細(xì)資料，以及遺漏索引所需資料行的詳細(xì)資料。具體來說，此指令碼會識別哪些資料行使用於等式及不等式 SQL 陳述式。另外，它還報告其他哪些資料行應(yīng)做為遺漏索引中的內(nèi)含資料行使用。內(nèi)含資料行可讓您滿足更多隱含的查詢，而不必從基礎(chǔ)頁面取得資料，因而使用更少的 I/O 作業(yè)並改進(jìn)效能。
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 gINNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handleINNER 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 (按影像可放大)
請注意，其結(jié)果並未指定應(yīng)該以何種順序建立必要索引中的資料行。若要決定此順序，您應(yīng)該檢查共同的 SQL 程式碼基底。一般而言，最常選取的資料行應(yīng)該先出現(xiàn)在索引中。
我也該指出，在計算遺漏索引的成本時，只會考量使用者資料行 (例如 user_seeks 和 user_scans)。這是因為系統(tǒng)資料行傾向於使用統(tǒng)計資料、資料庫一致性檢查 (Database Consistency Checking，DBCC) 及資料定義語言 (Data Definition Language，DDL) 命令來表示，而這些在履行商業(yè)功能 (而非資料庫管理功能) 時較不重要。
切記，您需要特別考量到當(dāng)基礎(chǔ)資料表發(fā)生任何資料修改時，可能對於額外索引增加的潛在成本。因此，應(yīng)該進(jìn)行基礎(chǔ) SQL 程式碼基底的其他研究。
如果您發(fā)現(xiàn)有許多資料行都是建議內(nèi)含的資料行，就應(yīng)該檢查基礎(chǔ) SQL，因為這可能表示其中有使用 catchall "SELECT *" 陳述式，如果真的是這樣，您可能需要修改 select 查詢。
未使用的索引
未使用的索引對效能可能會有負(fù)面影響。這是因為在修改基礎(chǔ)資料表資料時，也需要同時更新索引。當(dāng)然，這會多花費一點時間，甚至?xí)岣叻怄i的機(jī)率。
當(dāng)使用某索引來滿足查詢，且因為對基礎(chǔ)資料表資料套用更新結(jié)果而更新索引時，SQL Server 會更新相對應(yīng)的索引使用量詳細(xì)資料。檢視這些使用量詳細(xì)資料，即可識別任何未使用的索引。
sys.dm_db_index_usage_stats DMV 會說明索引的使用次數(shù)和使用程度。它會聯(lián)結(jié)至 sys.indexes DMV 中，其中包含建立索引時使用的資訊。您可以檢查不同使用者資料行中的值是否為 0，以識別未使用的索引。同樣地，基於上述理由，仍然會忽略對系統(tǒng)資料行的影響。[圖 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 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') = 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 #TempUnusedIndexesSELECT 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 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'') = 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 (按影像可放大)
此查詢的結(jié)果會顯示未用來擷取資料，但卻因為基礎(chǔ)資料表的變更而更新的索引。這些更新顯示在 [user_updates] 和 [system_updates] 資料行中。其結(jié)果是按照已套用至該索引的使用者更新數(shù)目排序。
您必須收集足夠的資訊以確保該索引不會使用到，因為您當(dāng)然不希望移除一年或一季會執(zhí)行一次之查詢的重要索引。同時請注意，有些索引是用來限制重複插入記錄或資料排序；在移除任何未使用的索引之前，這些因素也必須考慮在內(nèi)。
查詢的基本格式僅會套用於目前資料庫，因為它聯(lián)結(jié)的 sys.indexes DMV 只與目前資料庫有關(guān)。您可以使用系統(tǒng)預(yù)存程序 sp_MSForEachDB，針對伺服器上的所有資料庫擷取結(jié)果。我用來執(zhí)行此動作的模式已說明於「在所有資料庫內(nèi)建立迴圈」資訊看板中。在指令碼的其他區(qū)段中，若我想要反覆運算伺服器上的所有資料庫，則我也會使用此模式。此外，我會篩選出堆積類型的索引，因為這些代表不含正式索引的資料表原生結(jié)構(gòu)。
高成本的使用中索引
另一項有幫助的資訊，是以基礎(chǔ)資料表的變更來識別在已使用索引當(dāng)中，成本最高的索引。此成本對效能有負(fù)面影響，但該索引本身對資料的擷取可能很重要。
sys.dm_db_index_usage_stats DMV 可讓您了解索引的使用頻率和使用程度。此 DMV 會聯(lián)結(jié)至 sys.indexes DMV，其中包含建立該索引時使用的詳細(xì)資料。檢查 [user_updates] 和 [system_updates] 資料行，就會顯示維護(hù)率最高的索引。[圖 6] 提供用來識別成本最高的索引之指令碼，並顯示其結(jié)果。
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 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') = 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 #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 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 #TempMaintenanceCostORDER BY [Maintenance cost] DESC-- Tidy up.DROP TABLE #TempMaintenanceCost
Figure 6 Identifying the Most Costly Indexes (按影像可放大)
其結(jié)果會顯示維護(hù)率最高的索引，以及相關(guān)資料庫/資料表的詳細(xì)資料。[維護(hù)成本 (Maintanance cost)] 資料行的計算方式，是 user_updates 和 system_updates 資料行的總和。索引的使用性 (顯示在 [擷取使用量 (Retrieval usage)] 資料行中) 的計算方式，是各 user_* 資料行的總和。在決定是否可移除索引時，您一定要考慮索引的使用性。
當(dāng)需要修改大量資料時，這些結(jié)果可幫助您識別在套用更新之前，應(yīng)該先移除的索引。然後，您就可以在完成所有更新之後重新套用這些索引。
常用的索引
您可以使用 DMV 來識別哪些索引最常使用，這些是通往基礎(chǔ)資料最常見的路徑。如果這些索引本身能夠改進(jìn)或最佳化，則它們可提供顯著的整體效能改進(jìn)。
sys.dm_db_index_usage_stats DMV 包含使用索引透過搜尋、掃描及查閱來擷取資料之次數(shù)的詳細(xì)資料。此 DMV 會聯(lián)結(jié)至 sys.indexes DMV，其中包含建立該索引時使用的詳細(xì)資料。[使用率 (Usage)] 資料行的計算方式，是所有 user_* 欄位的總和。這可以使用 [圖 7] 顯示的指令碼來進(jìn)行。此查詢的結(jié)果會顯示使用索引的次數(shù)，並依 [使用量 (Usage)] 排序。
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 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') = 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 #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'') = 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 (按影像可放大)
最常用的索引代表通往基礎(chǔ)資料的最重要存取路徑。顯然，您不應(yīng)該移除這些索引；然而，您必須檢查它們，以確保其處於最佳狀態(tài)。例如，您應(yīng)該確保索引片段不多 (尤其是對於依序擷取的資料)，且基礎(chǔ)統(tǒng)計資料是最新的。而且您應(yīng)該移除資料表上任何未使用的索引。
邏輯片段的索引
邏輯索引片段的情況，會指出索引中順序不對的項目所佔的百分比。這與頁面飽和度的片段不同。邏輯片段會對任何使用索引進(jìn)行順序掃描的作業(yè)造成影響?？赡艿脑?，應(yīng)該移除片段。您可以利用重建或重組索引來達(dá)成此目的。
您可以使用下列 DMV，來識別邏輯片段最多的索引。sys.dm_db_index_physical_stats DMV 可讓您檢視關(guān)於索引大小和片段情況的詳細(xì)資料。此 DMV 會聯(lián)結(jié)至 sys.indexes DMV，其中包含建立該索引時使用的詳細(xì)資料。
[圖 8] 顯示用來識別邏輯片段最多之索引的指令碼。結(jié)果會依片段百分比排序，並顯示所有資料庫中邏輯片段最多的索引，以及相關(guān)的資料庫/資料表。請注意，一開始執(zhí)行它時需要花點時間 (幾分鐘)，因此我已在指令碼下載中將它做成註解。
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_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 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_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 8 Identifying Logically Fragmented Indexes (按影像可放大)
I/O 的高成本查詢
I/O 是查詢所執(zhí)行的讀寫次數(shù)的計量。這可做為查詢效率的指標(biāo)，使用很多 I/O 的查詢，通常是改進(jìn)效能的理想目標(biāo)。
sys.dm_exec_query_stats DMV 會提供快取之查詢計劃的彙總效能統(tǒng)計資料，包括關(guān)於實際和邏輯讀寫及查詢執(zhí)行次數(shù)的詳細(xì)資料。其中會包含用來從其內(nèi)含之父系 SQL 中擷取實際 SQL 的位移。此 DMV 會聯(lián)結(jié)至 sys.dm_exec_sql_text DMF，其中包含 I/O 相關(guān)之 SQL 批次的資訊。不同位移會套用到此批次，以取得基礎(chǔ)的個別 SQL 查詢。[圖 9] 顯示此指令碼。結(jié)果會依 [平均 I/O (Averge IO)] 排序，其中會顯示 [平均 I/O (Average IO)]、[總 I/O (Total IO)]、[個別查詢 (Individual Query)]、[父系查詢 (Parent Query)] (如果個別查詢是批次的一部分的話) 及 [資料庫名稱 (Database Name)]。
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 反映資料量，因此 [個別查詢 (Individual Query)] 資料行所顯示的查詢，可幫助您判斷哪些範(fàn)圍可減少 I/O 及改進(jìn)效能。您可以將查詢提供給 Database Tuning Advisor，以判斷是否應(yīng)新增任何索引/統(tǒng)計資料來改進(jìn)查詢的效能。統(tǒng)計資料會包含關(guān)於基礎(chǔ)資料的散佈和密度的詳細(xì)資料。這會由查詢最佳化工具在判斷最佳查詢存取計劃時使用。
檢查這些查詢中的資料表與「遺漏索引」區(qū)段所列出的索引之間是否有連結(jié)，也很有幫助 (但請注意，調(diào)查建立索引對於會經(jīng)歷多次更新之資料表的影響是很重要的，因為額外的索引將增加更新基礎(chǔ)資料表資料所花費的時間)。
您可以將此指令碼修改為只報告讀取或?qū)懭?，這些分別適用於報告資料庫或交易式資料庫。您也可以報告總計值或平均值，並依序排序。讀取值若很高，表示有遺漏或不完整的索引，或有設(shè)計不良的查詢或資料表。
在解譯使用 sys.dm_exec_query_stats DMV 的結(jié)果時要小心。例如，查詢計劃有可能隨時從程序快取中移除，所以並非所有的查詢都會被快取。雖然這樣會影響結(jié)果，但結(jié)果仍然可以是高成本查詢的指標(biāo)。
CPU 的高成本查詢
您可以採取另一種好用的方式，亦即從 CPU 使用率的角度來分析成本最高的查詢。此方式可揪出效能不佳的查詢。我在這裡使用的 DMV 與我剛才從 I/O 角度用來檢查查詢的 DMV 一樣。您在 [圖 10] 看到的查詢可讓您根據(jù) 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 使用率、個別查詢、父系查詢 (如果個別查詢是批次的一部分的話) 及對應(yīng)的資料庫名稱。而如前所述，或許值得對查詢執(zhí)行 Database Tuning Advisor，以判斷是否可進(jìn)一步改進(jìn)效能。
高成本的 CLR 查詢
SQL Server 使用 CLR 的頻率越來越高。因此，判斷哪些查詢使用 CLR 將有所幫助，這包括 include 預(yù)存程序、函數(shù)及觸發(fā)程序。
sys.dm_exec_query_stats DMV 包含關(guān)於 total_clr_time 和查詢執(zhí)行次數(shù)的詳細(xì)資料。其中也包含用來從所包含之父系查詢中擷取實際查詢的位移。此 DMV 會聯(lián)結(jié)至 sys.dm_exec_sql_text DMF，其中包含關(guān)於 SQL 批次的資訊。這會套用不同位移，以取得基礎(chǔ) 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ù)、個別查詢、父系查詢及資料庫名稱。同樣地，或許值得對查詢執(zhí)行 Database Tuning Advisor，以判斷是否可進(jìn)一步改進(jìn)效能。
最常執(zhí)行的查詢
您可以修改先前的高成本 CLR 查詢範(fàn)例，以識別最常執(zhí)行的查詢。請注意，這裡適用相同的 DMV。相較於最佳化罕用的大型查詢，改進(jìn)經(jīng)常執(zhí)行之查詢的效能可提供更實質(zhì)的效能改進(jìn) (在健全狀態(tài)檢查方面，可透過檢查累積 CPU 或 I/O 使用率最高的查詢，來進(jìn)行交叉檢查)。改進(jìn)經(jīng)常執(zhí)行之查詢的另一項好處是，它也有機(jī)會減少鎖定數(shù)量和交易時間長度。當(dāng)然，最後結(jié)果就是改進(jìn)整體系統(tǒng)回應(yīng)速度。
您可以使用 [圖 12] 所顯示的查詢，來識別最常執(zhí)行的查詢。執(zhí)行此動作會顯示執(zhí)行計數(shù)、個別查詢、父系查詢 (如果個別查詢是批次的一部分的話) 及相關(guān)資料庫。同樣地，或許值得對查詢執(zhí)行 Database Tuning Advisor，以判斷是否可進(jìn)一步改進(jìn)效能。
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 (按影像可放大)
遭到封鎖的查詢
最常遭到封鎖的查詢，通常是長時間執(zhí)行的查詢。在識別這些查詢之後，您可以分析它們，以判斷是否可以且應(yīng)該加以改寫，以減少封鎖。會造成封鎖的原因包括：以不一致順序使用物件、交易範(fàn)圍衝突，以及更新未使用的索引。
我所討論過的 sys.dm_exec_query_stats DMV，包含可用來識別最常遭到封鎖之查詢的資料行。平均封鎖時間的計算方式，是 total_elaspsed_time 和 total_worker_time 之間的差，除以 execution_count。
sys.dm_exec_sql_text DMF 包含與封鎖相關(guān)之 SQL 批次的詳細(xì)資料。這會套用不同的位移，以取得基礎(chǔ) SQL。
您可以使用 [圖 13] 所顯示的查詢，以識別最常遭到封鎖的查詢。結(jié)果會顯示 [平均封鎖時間 (Average Time Blocked)]、[總封鎖時間 (Total Time Blocked)]、[執(zhí)行計數(shù) (Execution Count)]、[個別查詢 (Individual Query)]、[父系查詢 (Parent Query)] 及 [相關(guān)資料庫 (Related Database)]。雖然這些結(jié)果是依 [平均封鎖時間 (Average Time Blocked)] 排序，但是依 [總封鎖時間 (Total Time Blocked)] 排序也很有幫助。
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è)計問題 (例如遺漏索引)、交易問題 (所使用資源的順序不對)...等等。Database Tuning Advisor 也會標(biāo)明可改進(jìn)的項目。
最低計劃重複使用率
使用預(yù)存程序的好處之一，是可以快取及重複使用查詢計劃，而不需要編譯查詢。這樣可節(jié)省時間、資源及改進(jìn)效能。您可以識別具有最低重複使用率的查詢計劃，以進(jìn)一步調(diào)查不重複使用計劃的原因。您可能會發(fā)現(xiàn)，改寫某些查詢可最佳化重複使用率。
[圖 14] 顯示我所撰寫的指令碼，用以識別具有最低計劃重複使用率的查詢。此技巧使用我曾經(jīng)討論過的 DMV 以及我尚未提到的 DMV：dm_exec_cached_plans。此 DMV 也包含關(guān)於 SQL Server 已快取之查詢計劃的詳細(xì)資料。如您所見，結(jié)果會提供使用計劃的次數(shù) ([計劃使用率 (Plan Usage)] 資料行)、個別查詢 (Individual Query)、父系查詢 (Parent Query) 及資料庫名稱 (Database Name)。
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 (按影像可放大)
然後您可以檢查所產(chǎn)生的個別查詢，以識別這些計劃不常重複使用的原因 (如果有的話)。其中一個可能原因，是每次查詢執(zhí)行時都會重新編譯 - 如果查詢包含不同 SET 陳述式或暫存資料表，就可能發(fā)生此狀況。如需重新編譯及計劃快取的詳細(xì)說明，請參閱《SQL Server 2005 的批次編譯、重新編譯及計劃快取問題》(網(wǎng)址為
microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx)。
請注意，您也必須確保查詢已經(jīng)有很多機(jī)會可以執(zhí)行數(shù)次。您可以使用相關(guān)聯(lián)的 SQL 追蹤檔案來確認(rèn)這一點。
進(jìn)一步的工作
請記住，不同 DMV 所公開的計量資料不會永久儲存起來，而是僅會保留在記憶體中。當(dāng) SQL Server 2005 重新啟動時，這些計量資料就會消失。
您可以根據(jù)來自 DMV 的輸出，定期建立資料表，並以時間戳記來儲存其結(jié)果。然後，您可以依時間戳記的順序來檢查這些資料表，以識別任何應(yīng)用程式變更的影響，或給定工作或時間性處理程序的影響。例如，在月底進(jìn)行的處理程序會有什麼影響？
同樣地，您可以針對給定的追蹤檔案工作量與這類資料表的變更進(jìn)行關(guān)聯(lián)性分析，以判斷給定工作量 (例如每日或月底) 對遺漏索引、最常使用查詢...等等的影響。我所提供的指令碼只要加以編輯，即可建立這些資料表並定期執(zhí)行，成為可持續(xù)進(jìn)行的維護(hù)作業(yè)。
您也可以利用 Visual Studio 2005 建立自訂報表，來使用本文所討論的指令碼。這些報表可以輕易整合到 SQL Server Management Studio，以提供更令人滿意的資料呈現(xiàn)方式。
可能的話，您應(yīng)該嘗試整合我所描述的方法與其他方法，例如追蹤及比率分析。這樣可以讓您對改進(jìn)資料庫效能所需的變更，產(chǎn)生更全面性的了解。
我在這裡示範(fàn)了如何運用 SQL Server 2005 在正常運作過程中所累積的大量資訊。查詢此資訊可提供很好的導(dǎo)引，並在長期運用之後有效改進(jìn)查詢效能。例如，您將可以發(fā)掘伺服器等待的原因、尋找對效能有負(fù)面影響的未使用索引、判斷何者是最常見的查詢，以及何者是成本最高的查詢。您一旦揭露這些隱藏的資料，就能夠發(fā)揮豐富的可能性。關(guān)於 DMV 還有很多需要更進(jìn)一步了解的內(nèi)容，我希望本文可激勵您進(jìn)行更深入的研究。
Ian Stirk 從 1987 年起，即服務(wù)於 IT 領(lǐng)域，曾任開發(fā)人員、設(shè)計師和架構(gòu)設(shè)計人員。他具備下列資格：M.Sc.、MCSD、MCDBA 及 SCJP。Ian 是一位自由軟體顧問，於英國倫敦從事 Microsoft 技術(shù)工作。他的連絡(luò)方式為：
Ian_Stirk@yahoo.com。
