揭开隐藏数据的面纱，优化应用程序性能
Ian Stirk
本文讨论:
|
本文使用了以下技术: SQL Server |
目录 许多应用程序性能问题追根溯源都可以归咎到性能欠佳的数据库查询；但是，有许多方法可以用来提高数据库性能。SQL ServerTM 2005 收集许多信息，可以使用这些信息来确定产生此类性能问题的原因。
SQL Server 2005 收集与运行查询有关的数据。此数据存放在内存中，并从服务器重新启动后开始累积，可用于确定许多问题和指标，包括那些与表索引、查询性能和服务器 I/O 相关的问题和指标。可以通过 SQL Server 动态管理视图 (DMV) 和相关动态管理函数 (DMF) 查询此数据。这些都是基于系统的视图和函数，提供可用于诊断问题和调整数据库性能的服务器状态信息。
本文重点介绍使用 SQL Server 2005 收集的信息来提高性能的领域。这种方法在很大程度上是一种非入侵式方法，因为它收集并研究现有数据，通常是查询基础系统数据。
我将演示如何获取此信息、讨论基础 DMV、确定解释数据时要注意的所有问题，并指出其他一些可能会提高性能之处。为此，我将提供并分析一系列 SQL 脚本，这些脚本详细说明了 SQL Server 2005 所收集数据的各个方面。可以从《MSDN® 杂志》网站下载此脚本的完整版本（全部带注释）。
我将讨论的某些步骤会将服务器作为一个整体进行专门分析，包括给定服务器上承载的所有数据库。如果需要，可以添加相应的筛选（如将其名称添加到查询）来专门分析给定的数据库。
相反，某些查询会联接到 sys.indexes DMV，这是一个特定于数据库的视图，只报告针对当前数据库的结果。在这些示例中，我已将查询修改成使用系统存储过程 sp_MSForEachDB 对服务器上的所有数据库进行遍历，从而提供服务器范围的结果。
为了将给定性能指标的最相关记录作为目标，我将限制使用 SQL TOP 函数返回的记录数量。
服务器等待的原因
用户通常会遇到因一系列等待而导致性能下降的情况。每当 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_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;
.gif)
Figure 1 SQL 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 显示了用于确定哪些数据库使用读写操作最多的脚本。
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 qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP 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 qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Writes] DESC;
.gif)
Figure 2 Identifying the Most Reads and 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，并按数据库将缺失索引数相加，从而轻松确定需进一步调查哪些数据库。
Figure 3 Identifying Missing Databases SELECT
DatabaseName = DB_NAME(database_id)
,[Number Indexes Missing] = count(*)
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;
.gif)
Figure 3 Identifying 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 联系在一起。缺失索引的开销（总开销列）的计算方法是，用户平均总开销与用户平均影响的积，再乘以用户搜寻次数与用户扫描次数的和。
可以使用图 4 所示的脚本来确定开销最高的缺失索引。此查询的结果（按“总开销”排序）显示最重要缺失索引的成本以及有关数据库/架构/表和缺失索引中所需列的信息。特别是，此脚本可确定哪些列在相等和不相等 SQL 语句中使用。另外，它还报告应将哪些其他列用作缺失索引中的包含性列。使用包含性列可以在不从基础页获取数据的情况下满足更多的覆盖查询，因而使用的 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_columns
FROM 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_handle
ORDER BY [Total Cost] DESC;
.gif)
Figure 4 Cost of Missing Indexes (单击该图像获得较大视图)
请注意，这些结果没有指定应在必需索引中创建列的顺序。为确定顺序，您应检查整体 SQL 代码库。一般而言，选择最多的列应在索引中最先显示。
还要指出的是，在计算缺失索引的开销时，只考虑用户列（如 user_seeks 和 user_scans）。这是因为系统列一般代表对统计数据、数据库一致性检查 (DBCC) 和数据定义语言 (DDL) 命令的使用，而且这些列对完成业务功能（与数据库管理功能相比较）不太重要。
请记住，对于在基础表中发生任何数据修改时可能产生的额外索引开销，要特别引起注意，这非常重要。因此，应对基础 SQL 代码库进行进一步的研究。
如果您发现系统建议将许多列作为要包含的列，则应检查基础 SQL，因为这可能表示正在使用 catchall“SELECT *”语句——如果确实如此，则可能要修改选择查询。
未使用的索引
未使用的索引可能会对性能产生不良影响。这是因为，修改基础表数据后，可能也需更新索引。当然，这需要额外的时间，而且可能增加阻塞的几率。
如果执行查询时使用某个索引，而且由于将更新应用到基础表数据而更新了该索引，则 SQL Server 会更新相应的索引使用详细信息。可以通过查看这些使用详细信息来确定任何未使用的索引。
通过 sys.dm_db_index_usage_stats DMV 可以了解使用索引的频率和程度。它已联接到 sys.indexes DMV，后者包含创建索引时所使用的信息。您可以在各用户列中检查值 0 以确定未使用的索引。根据上述原因，再次忽略系统列所产生的影响。使用图 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 #TempUnusedIndexes
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 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
.gif)
Figure 5 Identifying 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 提供了确定最高开销索引所使用的脚本并显示结果。
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.name
INTO #TempMaintenanceCost
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_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.name
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 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
.gif)
Figure 6 Identifying the Most Costly Indexes (单击该图像获得较大视图)
结果显示了维护性最高的索引以及有关数据库/表的详细信息。“维护开销”列的计算方式是将 user_updates 和 system_updates 列相加。索引的有用性（在“检索使用率”列中显示）的计算方式是将各 user_* 列相加。在决定是否要删除索引时，考虑索引的有用性非常重要。
如果涉及大量的数据修改，这些结果可帮您确定在应用更新前应删除的索引。然后，可在应用所有更新后重新应用这些索引。
常用索引
您可以使用 DMV 确定哪些索引最常使用，这些是到基础数据的最常用路径。如果这些索引能够实现自身改进或优化，则可以极大地提高整体性能。
sys.dm_db_index_usage_stats DMV 包含了一些详细信息，它们有关通过搜寻、扫描和查找来检索数据方面对索引的使用频率。此 DMV 已联接到 sys.indexes DMV，后者包含创建索引时所使用的详细信息。“使用率”列的计算方法是将所有 user_* 字段相加。使用图 7 所示的脚本即可实现此目的。此查询结果显示索引已被使用的次数，并按“使用率”排序。
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.name
INTO #TempUsage
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 + 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.name
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 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
.gif)
Figure 7 Identifying Most-Used Indexes (单击该图像获得较大视图)
使用次数最多的索引代表对基础数据的最重要访问路线。显然，您不想删除这些索引；但应对这些索引进行研究，以确保它们最优化。例如，应确保索引的碎片很少（对以顺序检索的数据更是如此），且基础统计数据保持最新。而且应该删除所有未使用的表索引。
逻辑上零碎的索引
逻辑索引碎片表示索引中无序条目所占的百分比。这与页填充度类型的碎片不同。逻辑碎片会影响任何使用索引的顺序扫描。应尽可能消除此碎片。可以通过重新生成或重新组织索引来消除碎片。
用下面的 DMV 可以确定逻辑上最零碎的索引。使用 sys.dm_db_index_physical_stats DMV 可以查看有关索引的大小和碎片的详细信息。它已联接到 sys.indexes DMV，后者包含创建索引时使用的详细信息。
图 8 显示了确定逻辑上最零碎的索引所使用的脚本。结果按碎片的百分比排序，显示了所有数据库中逻辑上最零碎的索引以及有关的数据库/表。请注意，此脚本在最初运行时可能需要一段时间（几分钟），因此我已在脚本下载中将其注释掉。
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 #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER 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) 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
ORDER BY [Fragmentation %] DESC
;
'
-- Select records.
SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
-- Tidy up.
DROP TABLE #TempFragmentation
.gif)
Figure 8 Identifying Logically Fragmented Indexes (单击该图像获得较大视图)
高 I/O 开销的查询
I/O 是查询所进行的读/写数量的一种度量。这可以用来指示查询的效率——使用许多 I/O 的查询通常是性能改进研究的适当对象。
sys.dm_exec_query_stats DMV 提供缓存查询计划的汇总性能统计信息，包括有关物理和逻辑读/写以及查询执行次数的详细信息。它包含从实际 SQL 的父 SQL 中提取实际 SQL 所使用的偏移量。此 DMV 已联接到 sys.dm_exec_sql_text DMF，后者包含与 I/O 有关的 SQL 批处理的信息。将各种偏移量应用到此批处理可以获得各基础 SQL 查询。脚本如图 9 所示。结果按平均 I/O 排序，显示了平均 I/O、总 I/O、单个查询、父查询（如果单个查询是批处理的一部分）以及数据库名称。
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 qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;
.gif)
Figure 9 Identifying Most Costly Queries by I/O (单击该图像获得较大视图)
因为 I/O 是数据量的一种反映，所以“单个查询”列中显示的查询可帮您确定减少 I/O 和提高性能的地方。可以将查询提供给数据库优化顾问，以确定是否应添加任何索引/统计数据来提高查询的性能。统计数据包括有关基础数据分布和密度的详细信息。查询优化器在确定优化查询访问计划时使用此统计数据。
它还有助于检查在这些查询中的表和“缺失索引”部分列出的索引之间是否存在联系。（但请注意，如果表经历许多更新，那么研究对这些表创建索引所产生的影响就很重要，因为任何附加索引都会增加更新基础表数据所使用的时间。）
此脚本可以更改为只报告读操作或写操作，这分别对报告数据库或事务性数据库很有用。还可以报告总值和平均值，并相应地进行排序。读操作的值较高可能表示缺失索引或索引不完整，也可能表示查询或表设计不良。
解释使用 sys.dm_exec_query_stats DMV 所得的结果时，请务必小心。例如，某个查询计划可能会随时从过程缓存中被删除，并且不是所有查询都得到缓存。虽然这会影响结果，但这些结果仍指示出开销最高的查询。
高 CPU 开销的查询
另一个可采取的有用方法是分析在 CPU 使用率方面开销最高的查询。此方法可能更能体现出性能不良的查询。在此使用的 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 qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;
.gif)
Figure 10 SQL Query Records Causes of Wait Times (单击该图像获得较大视图)
此查询返回平均 CPU 使用率、总 CPU 使用率、单个查询和父查询（如果单个查询是批处理的一部分）以及相应的数据库名称。而且，如前所述，可能有必要针对查询运行数据库优化顾问，以确定是否有可能进一步改进。
高开销的 CLR 查询
SQL Server 不断增加对 CLR 的使用。因此，确定哪些查询使用 CLR（包括存储过程、函数和触发器）最多会非常有帮助。
sys.dm_exec_query_stats DMV 包含有关 total_clr_time 和查询已执行次数的详细信息。它还包含从实际查询的父查询中提取实际查询所使用的偏移量。此 DMV 已联接到 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 qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE total_clr_time <> 0
ORDER BY [Average CLR Time] DESC;
.gif)
Figure 11 Identifying Most Costly CLR Queries (单击该图像获得较大视图)
此查询返回平均 CLR 时间、总 CLR 时间、执行计数、单个查询、父查询以及数据库名称。再次指出，可能有必要针对查询运行数据库优化顾问，以确定是否有可能进一步改进。
最常执行的查询
您可以修改前一个用于高开销 CLR 查询的示例来确定最常执行的查询。请注意在此适用相同的 DMV。与优化很少使用的大型查询相比，改进频繁执行的查询可以极大地提高性能。（要进行完整性检查，可能要通过检查使用累积 CPU 或 I/O 最高的查询进行交叉检查。）改进频繁运行的查询的另一个好处是还可以减少锁的数量和事务长度。当然，最终结果是改进了系统的整体响应能力。
您可以使用图 12 所示的查询来确定最常执行的查询。运行此查询会显示执行计数、单个查询、父查询（如果单个查询是批处理的一部分）以及相关数据库。再次指出，有必要在数据库优化顾问中运行查询，以确定是否有可能进一步改进。
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 qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Execution count] DESC;
.gif)
Figure 12 Identifying 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 所示的查询可以确定受阻塞影响最大的查询。查询结果显示了平均阻塞时间、总阻塞时间、执行计数、单个查询、父查询以及相关数据库。尽管这些结果按“平均阻塞时间”排序，但按“总阻塞时间”排序也会有用。
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 qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average Time Blocked] DESC;
.gif)
Figure 13 Identifying Queries Most Often Blocked (单击该图像获得较大视图)
如果您研究此查询，可能会发现设计问题（如缺失索引）、事务问题（无序使用资源）等。数据库优化顾问还会重点指出可能的改进。
最低计划重用率
使用存储过程的一个优点是，查询计划已得到缓存，因而可在不编译查询的情况下重用。这一优点既节省时间和资源，又改进了性能。您可以确定重用率最低的查询计划，从而进一步调查为何没有重用这些查询计划。您可能会发现，某些查询计划可通过重写来优化重用。
图 14 显示了我编写的脚本，用来确定计划重用率最低的查询。此技术使用已讨论过的 DMV 以及尚未提到过的 DMV：dm_exec_cached_plans。此 DMV 还包含已被 SQL Server 缓存的查询计划的详细信息。如您所见，查询结果提供了已使用计划的次数（“计划使用率”列）、单个查询、父查询和数据库名称。
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.cacheobjtype
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
WHERE cp.plan_handle=qs.plan_handle
ORDER BY [Plan usage] ASC;
.gif)
Figure 14 Identifying Queries with Lowest Plan Reuse (单击该图像获得较大视图)
然后，您可以研究显示的各个索引，以确定没有更多地重用这些计划（如果有可能）的原因。一个可能的原因是，每次运行查询时，都要重新编译查询——如果查询包含多个 SET 语句或临时表，则可能发生这种情况。有关重新编译和计划缓存的详细讨论，请参阅文章“Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005”（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 与他取得联系。
