如何识别性能最差的查询并改进它们的性能

Rajesh Kumar

4.20/5 (7投票s)

2015年12月2日

CPOL

12分钟阅读

23797

181

本文将详细介绍如何在运行的应用程序或应用程序的特定模块中识别性能最差的查询。

下载 Excel 工具 - 40.1 KB

引言

有时我们会想改进某个应用程序（尤其是维护应用程序）的性能，或者可能需要验证和改进某个特定模块（在现有应用程序中开发的）的性能。但问题随之而来：我们应该从哪里开始，或者说应该检查哪个方面？

开始检查的一个方面可以是检查应用程序在处理数据库时所执行的查询的性能。然后识别出性能最差的查询，以便我们可以集中精力优化这些特定查询。如果我们成功地提高了查询性能，那么应用程序的性能肯定会得到显著提升。

在这里，我们将学习如何在不知道后台执行了哪些查询的情况下，识别出性能最差的查询。

背景

当在现有应用程序中开发新模块时，我们总会考虑这个模块的性能是否与应用程序整体保持一致。有时我们对应用程序的性能有截止日期（例如，应用程序的任何页面都不应超过 3 秒）。在软件开发生命周期 (SDLC) 的某些阶段可以进行检查，例如性能测试，但这发生在后期阶段，当时我们可以说新的开发在功能上是安全的（在编码、单元测试、集成测试和 QA 之后，或者可能在 VAFC、UAT 之后）。任何性能最差情况的识别都可能导致技术更改，以至于我们需要重新进行某些阶段的工作以确保新的更改是安全的。

因此，通过进行一些概念验证 (POC) 和分析，我们可以提出一些流程或分步执行的方法，这些方法可以帮助在早期阶段识别出这些情况，并在早期进行重大更改。

要求

为了执行这些步骤来识别性能最差的查询，我们需要进行一些如下定义的配置：

一个单独的环境，其中安装了发布的应用程序，并且在任务完成之前没有任何其他用户使用此环境。
一个单独的数据库实例，这样就不会有其他用户执行不必要的查询，以便只观察所需查询的性能。

总之，我们可以说，我们应该优先选择拥有一个专用的环境来进行这些执行。

现在我们已经准备好了环境，那么我们也应该提前知道我们要检查/测试的行为，或者说要验证的功能场景。

对于每个这样的场景，我们需要在执行场景之前识别以下信息：

要执行的功能场景
查询性能的观察指标（任选其一或全部）：
- CPU 时间
- 持续时间
- 逻辑读取
预计数据量（例如，如果我要测试一个从数据库中根据某些标准获取数据的场景，那么表中应该有多少最大数据？）
准备用于在数据库表中填充数据（测试数据）的脚本，如果我们没有足够的数据量。

例如：请看下面要执行的测试用例

详细视图搜索： 以下场景已为此主题执行

按工单号搜索工单，并提供组的所有条件。该条件应能检索到工单。
按工单号和状态日期搜索工单。该条件应能检索到工单。
按工单号和产品信息搜索工单。该条件应能检索到工单。
按工单号和产品信息搜索工单。该条件应能检索到工单。
按工单号和创建日期（两个日期之间）搜索工单。该条件应能检索到工单。
按工单号搜索工单，并提供组、状态日期和产品信息的所有条件。该条件应能检索到工单。
按工单号搜索工单，并提供组、状态日期和产品信息的所有条件。该条件应能检索到工单。

在执行的场景中，识别以下方面的性能最差的查询：
- CPU 时间
- 持续时间
- 逻辑读取
数据量 -
此主题不会增加现有表中的任何数据。
搜索将在现有数据上数据库中执行。
每个搜索请求都应返回一个工单。

使用代码

为了开始执行场景，首先我们需要清除 SQL Server 的缓存，以免结果受到缓存数据的影响，或者说，如果此环境中已经执行了一些查询，可能会影响结果。

以 DBO 权限执行以下查询：

DBCC FREESYSTEMCACHE('All')
DBCC FREESESSIONCACHE
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

在后台运行 SQL Profiler，以便在 Profiler 窗口中跟踪已执行的查询，这将有助于查看与查询相关的某些信息。

清除 DB 服务器缓存后，执行我们列出的所有必需场景，并在所有场景执行完毕后停止 SQL Profiler，并保存以供以后使用。

现在我们的任务是识别由于场景执行而导致的所有已执行查询中性能最差的查询。查询可能在以下三个参数上表现最差：

CPU 时间
执行持续时间
逻辑/物理读取

我们已准备好查询（每个参数一个）来识别性能最差的查询。这些查询需要在所有场景执行完毕后立即执行，并将结果保存下来。以下是这些查询：

按执行次数获取性能最差的查询，请执行以下 SQL 查询（选择 Top 20 的性能最差的查询）。

select top 20 
                stat.execution_count as execution_count,
                stat.total_logical_reads as total_logical_read,
                stat.total_worker_time as total_CPU,
                cache.objtype,
                req.text,
                '1 Execution count' as typeOf,
                row_number() OVER (ORDER BY stat.execution_count desc) as rownum
from sys.dm_exec_query_stats AS stat
CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) as req
CROSS APPLY    sys.dm_exec_query_plan(stat.plan_handle)    AS pl
left join sys.dm_exec_cached_plans as cache on cache.plan_handle = stat.plan_handle
order by execution_count desc

按逻辑读取获取性能最差的查询，请执行以下 SQL 查询（选择 Top 20 的性能最差的查询）。

select top 20 
                stat.execution_count as execution_count,
                stat.total_logical_reads as total_logical_read,
                stat.total_worker_time as total_cpu,
                cache.objtype,
                req.text,
                '3 Logical Read' as typeOf,
                row_number() OVER (ORDER BY stat.total_logical_reads desc) as rownum
from sys.dm_exec_query_stats AS stat
CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) as req
CROSS APPLY    sys.dm_exec_query_plan(stat.plan_handle)    AS pl
left join sys.dm_exec_cached_plans as cache on cache.plan_handle = stat.plan_handle
order by total_logical_read desc

按 CPU 时间获取性能最差的查询，请执行以下 SQL 查询（选择 Top 20 的性能最差的查询）。

select top 20 
                stat.execution_count as execution_count,
                stat.total_logical_reads as total_logical_read,
                stat.total_worker_time as total_cpu,
                cache.objtype,
                req.text,
                '2 CPU' as typeOf,
                row_number() OVER (ORDER BY stat.total_worker_time desc) as rownum
from sys.dm_exec_query_stats AS stat
CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) as req
CROSS APPLY    sys.dm_exec_query_plan(stat.plan_handle)    AS pl
left join sys.dm_exec_cached_plans as cache on cache.plan_handle = stat.plan_handle
order by total_cpu desc

此外，如果我们尝试在以上所有三个参数上查找性能最差的查询，我们可以将这些查询合并到一个查询中，并获得所需的结果。合并后的查询如下：

select * from
-- CPU cache SQL-Server
(select top 20 
                stat.execution_count as execution_count,
                stat.total_logical_reads as total_logical_read,
                stat.total_worker_time as total_cpu,
                cache.objtype,
                req.text,
                '2 CPU' as typeOf,
                row_number() OVER (ORDER BY stat.total_worker_time desc) as rownum
from sys.dm_exec_query_stats AS stat
CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) as req
CROSS APPLY    sys.dm_exec_query_plan(stat.plan_handle)    AS pl
left join sys.dm_exec_cached_plans as cache on cache.plan_handle = stat.plan_handle
order by total_cpu desc
union
select top 20 
                stat.execution_count as execution_count,
                stat.total_logical_reads as total_logical_read,
                stat.total_worker_time as total_cpu,
                cache.objtype,
                req.text,
                '3 Logical Read' as typeOf,
                row_number() OVER (ORDER BY stat.total_logical_reads desc) as rownum
from sys.dm_exec_query_stats AS stat
CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) as req
CROSS APPLY    sys.dm_exec_query_plan(stat.plan_handle)    AS pl
left join sys.dm_exec_cached_plans as cache on cache.plan_handle = stat.plan_handle
order by total_logical_read desc
union
select top 20 
                stat.execution_count as execution_count,
                stat.total_logical_reads as total_logical_read,
                stat.total_worker_time as total_cpu,
                cache.objtype,
                req.text,
                '1 Execution count' as typeOf,
                row_number() OVER (ORDER BY stat.execution_count desc) as rownum
from sys.dm_exec_query_stats AS stat
CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) as req
CROSS APPLY    sys.dm_exec_query_plan(stat.plan_handle)    AS pl
left join sys.dm_exec_cached_plans as cache on cache.plan_handle = stat.plan_handle
order by execution_count desc)
as stat
order by typeof, rownum

执行上述合并查询将返回总共 60 行（Top 20 + Top 20 + Top 20），每行包含一个在上述三个参数之一上表现最差的 SQL 查询。也可能存在一个 SQL 查询在多个参数上表现最差。现在，我们只需检查每个查询并采取以下措施：

获取查询并在单独的 SQL 选项卡中执行它，以查看 CPU 时间、逻辑读取、索引扫描或查找（可从执行计划中查看）等信息。
如果个人认为自己可以优化该查询以提高性能，则应开始工作，或者可以联系他们的 DBA。这样做的优点是，他们将拥有性能最差的查询及其相关信息，这也将有助于 DBA 入手，并且所需时间更少。

为了让处理上述 SQL 合并查询返回的性能最差的查询更加容易，我们准备了一个 EXCEL 文件，其中包含三个不同的工作表和一个宏，该宏将读取特定工作表的结果并将其安排在另一个工作表中。请参阅下面的附图。

主工作表：此工作表包含有关执行了步骤的数据库的信息，并且还包含观察到查询性能的参数列表。

2. 数据工作表：这是粘贴所有查询返回结果的工作表，请看下图。

3. 统计信息工作表：这是最终的工作表，所有结果都按类别显示，并按性能最差查询的指标降序排列。请看下图。

因此，我们将有一个最终结果工作表，其中列出了所有已识别的性能最差的查询。为了帮助这个过程，我附上了 Excel 表格，可以通过顶部的链接下载。

既然我们已经识别了合适的性能最差的查询，那么我们的下一步就是根据需要分析和改进查询的性能。如果可行，任何个人都可以完成这项工作，否则应咨询 DBA。

但对于那些想自己动手的人，下面提供了一个示例，仅用于演示他们如何开始。这不是处理这些问题的唯一方法，但我们可以说这可能是一个方向，可以朝着这个方向前进以开始。

假设我发现了以下场景，其中查询的性能很差。

搜索状态为“已关闭”的产品工单的查询：在此搜索场景中执行了以下查询。

SET ROWCOUNT 101;
SELECT INC_TIC.IDTTIC, INC_TIC.IDTCLSSYS, INC_TIC.DSCTIC, INC_TIC.DATDEBTIC, INC_TIC.DERETATIC , INC_TIC.IDTPRD, INC_TIC.IDTCOM, INC_TIC.IDTTCH, INC_TIC.IDTSEC, INC_TIC.VALIDTPRM4 , INC_TIC.IDTPRM1, INC_TIC.IDTPRM2, INC_TIC.IDTPRM3, INC_TIC.IDTPRM4  , INC_PRD.IDTPRD as NMGPRD, INC_PRD.EXTPRD, INC_PRD.CMPOFFCOD, INC_PRD.CMPOFFLBL , INC_TYPPRD.IDTAPT, INC_TYPPRD.IDTFAMPRD, INC_TIC.RAISOCPHO , INC_TIC.IDTTYPPRD, INC_TIC.CODGTR, INC_TIC.IDTCTR , INC_TIC.IDTPLGHOR, INC_TIC.VALGTR 
FROM INC_TIC  
LEFT JOIN INC_PRD ON INC_PRD.IDTPRD=INC_TIC.IDTPRD AND INC_TIC.INDBDDPRD='OCE' 
INNER JOIN INC_TYPPRD ON INC_TYPPRD.IDTTYPPRD=INC_TIC.IDTTYPPRD 
WHERE INC_TIC.IDTCLSSYS=1 AND INC_TIC.DERETATIC = 5 
AND EXISTS(  SELECT TOP 1 1 WHERE  INC_TIC.IDTSOCIETE = '105'  UNION ALL  SELECT top 1  1 FROM 
INC_OUVAPTSOCIETE AS OUV1  WHERE OUV1.IDTSOCIETEDON = INC_TIC.IDTSOCIETE  AND OUV1.IDTSOCIETE='105'  AND OUV1.IDTFCTITT = ( CASE  INC_TIC.INDBDDPRD WHEN 'MAR' THEN 501 WHEN 'OCE' THEN 501 ELSE 502 END )  AND OUV1.HST = 0  AND OUV1.BLNRESVIS = 0  UNION ALL  SELECT top 1  1  FROM INC_OUVAPTTYPPRD AS OUV2  WHERE OUV2.IDTSOCIETE = '105'  AND OUV2.IDTTYPPRD= INC_TIC.IDTTYPPRD  AND  INC_TIC.INDBDDPRD IN ('GDR','OCER') AND OUV2.HST = 0  UNION ALL  SELECT top 1  1 FROM INC_OUVAPTEDS AS OUV3 WHERE OUV3.IDTSOCIETE = '105' AND OUV3.IDTENT = INC_TIC.EXT1 AND  INC_TIC.INDBDDPRD IN ('GDR','OCER') AND OUV3.HST = 0  UNION ALL  SELECT  top 1  1 FROM INC_OUVAPTSOURES AS OUV4 WHERE OUV4.IDTSOCIETE = '105' AND OUV4.IDTCTR = INC_TIC.IDTCTR AND  INC_TIC.INDBDDPRD IN ('MAR','OCE') AND OUV4.HST = 0  UNION ALL  SELECT TOP 1 1 FROM OCEA_VIEW_OUVTIC_EDS AS OUVEDS WHERE OUVEDS.IDTTIC = INC_TIC.IDTTIC AND OUVEDS.IDTEDS = 'DCI110'  ) ORDER BY DATTICCREUTC

此查询的性能不佳，如下所述。

SQL Server 执行时间

CPU 时间 = 0 毫秒， elapsed time = 1 毫秒。

SQL Server 解析和编译时间

CPU 时间 = 359 毫秒， elapsed time = 1538 毫秒。

SQL Server 执行时间

CPU 时间 = 0 毫秒， elapsed time = 1 毫秒。

SQL Server 解析和编译时间

CPU 时间 = 0 毫秒， elapsed time = 1 毫秒。

(101 行受影响)

表 'Worktable'。扫描计数 0，逻辑读取 0，物理读取 0，预读读取 0，LOB 逻辑读取 0，LOB 物理读取 0，LOB 预读读取 0。

表 'INC_PRD'。扫描计数 0，逻辑读取 290，物理读取 1，预读读取 16，LOB 逻辑读取 0，LOB 物理读取 0，LOB 预读读取 0。

表 'INC_TYPPRD'。扫描计数 101，逻辑读取 612，物理读取 3，预读读取 11，LOB 逻辑读取 0，LOB 物理读取 0，LOB 预读读取 0。

表 'INC_OUVAPTSOCIETE'。扫描计数 101，逻辑读取 202，物理读取 2，预读读取 0，LOB 逻辑读取 0，LOB 物理读取 0，LOB 预读读取 0。

表 'INC_TIC'。扫描计数 2，逻辑读取 112928，物理读取 44，预读读取 7516，LOB 逻辑读取 0，LOB 物理读取 0，LOB 预读读取 0。

(1 行受影响)

SQL Server 执行时间

CPU 时间 = 891 毫秒， elapsed time = 1828 毫秒。

性能最差的部分以粗体突出显示。很明显，INC_TIC 表的逻辑读取次数超过 10 万（112928）。

在分析此查询后，我们确定需要创建一个新索引，该索引如下：

CREATE INDEX INC_TIC_IDTCLSSYS_DERETATIC_DATTICCREUTC ON INC_TIC 
(
IDTCLSSYS,DERETATIC,DATTICCREUTC 
)
 WITH FILLFACTOR = 90
 ON INDENCOURS_PSCHEME (INDENCOURS)
 GO

创建索引后，相同查询的性能如下：

SQL Server 执行时间

CPU 时间 = 0 毫秒， elapsed time = 1 毫秒。

SQL Server 解析和编译时间

CPU 时间 = 156 毫秒， elapsed time = 1284 毫秒。

SQL Server 执行时间

CPU 时间 = 0 毫秒， elapsed time = 1 毫秒。

SQL Server 解析和编译时间

CPU 时间 = 0 毫秒， elapsed time = 1 毫秒。

(101 行受影响)

表 'Worktable'。扫描计数 0，逻辑读取 0，物理读取 0，预读读取 0，LOB 逻辑读取 0，LOB 物理读取 0，LOB 预读读取 0。

表 'INC_PRD'。扫描计数 0，逻辑读取 395，物理读取 1，预读读取 16，LOB 逻辑读取 0，LOB 物理读取 0，LOB 预读读取 0。

表 'INC_TYPPRD'。扫描计数 101，逻辑读取 692，物理读取 3，预读读取 11，LOB 逻辑读取 0，LOB 物理读取 0，LOB 预读读取 0。

表 'INC_OUVAPTSOCIETE'。扫描计数 101，逻辑读取 202，物理读取 2，预读读取 0，LOB 逻辑读取 0，LOB 物理读取 0，LOB 预读读取 0。

表 'INC_TIC'。扫描计数 2，逻辑读取 2283，物理读取 8，预读读取 1292，LOB 逻辑读取 0，LOB 物理读取 0，LOB 预读读取 0。

(1 行受影响)

SQL Server 执行时间

CPU 时间 = 266 毫秒， elapsed time = 562 毫秒

现在，如果我们比较这两个结果，这个查询的性能已经大大提高，我们可以说性能非常好。

创建索引之前的查询执行计划如下所示：

创建索引之后的查询执行计划如下所示：

因此，从上面的例子中，我们看到了通过引入新索引，查询的性能得到了很大的提升，这有助于查询以更少的资源消耗更快地执行。

我们可以做以下事情来提高性能：

创建/更新所需的索引
纠正 Join / WHERE 子句的顺序，以便它们可以尽可能地利用现有索引。
重构查询

可能还有其他方法可以提高性能。

关注点

我在进行这项练习的过程中学到了很多东西，其中一些如下所示：

通过这样做，我们能够识别出在 SDLC 的早期阶段性能不佳的查询，这有助于我们尽早关注这些查询以进行改进。有时，由于功能要求的性质，查询的性能无法进一步提高到某个程度。在这种情况下，它可以帮助我们提前与客户沟通此类问题（以避免最后一刻的意外）。
如果有人尝试自己提高查询性能，那么有很多学习领域，最重要的是，他们可以学会用多种方式编写查询，而且如果发现一个查询性能很差并且找到了改进方法，那么下次就可以避免编写相同类型的低效查询。
它有助于学习如何编写高质量的查询，以及如何有效地利用创建索引和应用 JOIN，这会自动帮助提高查询的性能。

还有一些其他有趣的方面，但如果有人尝试这种方法并自己找到有趣的方面，那将是很棒的。

历史

首次发布于 2015 年 12 月 2 日