常用索引
您可以使用 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
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
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;
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;
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;
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;
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;
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;
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 与他取得联系。
4250
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
