SQL Server:揭开隐藏数据的面纱,优化应用程序性能(上)

本文转自:http://msdn.microsoft.com/zh-cn/magazine/cc135978.aspx#S2

 目录服务器等待的原因
读和写
数据库缺失索引
高开销的缺失索引
未使用的索引
高开销的已使用索引
常用索引
逻辑上零碎的索引
高 I/O 开销的查询
高 CPU 开销的查询
高开销的 CLR 查询
最常执行的查询
受阻塞影响的查询
最低计划重用率
进一步探讨许多应用程序性能问题追根溯源都可以归咎到性能欠佳的数据库查询;但是,有许多方法可以用来提高数据库性能。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;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;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;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;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 #TempUnusedIndexesFigure 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 #TempMaintenanceCostFigure 6 Identifying the Most Costly Indexes (单击该图像获得较大视图)结 果显示了维护性最高的索引以及有关数据库/表的详细信息。“维护开销”列的计算方式是将 user_updates 和 system_updates 列相加。索引的有用性(在“检索使用率”列中显示)的计算方式是将各 user_* 列相加。在决定是否要删除索引时,考虑索引的有用性非常重要。如果涉及大量的数据修改,这些结果可帮您确定在应用更新前应删除的索引。然后,可在应用所有更新后重新应用这些索引。