标签： SQL Optimization

发布于

如何使用查询存储器（Query Store）对SQL进行调优？

SQL Server中的查询存储器功能是一个宝贵的工具，用于通过允许用户快速识别由于查询计划更改而导致的性能降级来解决性能问题。
在给定的示例中，我们可以观察到一个SQL查询（ID 23058）具有两个查询计划。黄点对应于表现相对稳定的查询计划，而蓝点则表示性能计划更加波动。为了增强此SQL性能的稳定性，我们可以使用Query Store中的“强制计划”功能，将黄点的查询计划指定为默认计划。

查询存储器（Query Store）是由SQL Server提供的一个强大功能，使用户能够在Query Store中强制指定一个特定的SQL查询计划。然而，查询存储器也有其限制，因为它不允许用户创建之前未生成的新查询计划。其使用是被动的，仅允许恢复受损的SQL性能，而不提供一种方法让用户改进SQL语句，以便生成更好的查询计划。

如何使用查询存储器（Query Store）来手动调整SQL？
如果想要手动提高存储在查询存储器中的SQL查询性能，这个过程可能会相当复杂。以下是一些一般性的步骤，作为指南：

  1. 从系统表sys.query_store_query和sys.query_store_query_text中提取您要调整的SQL文本。
  2. 通过注入各种提示(Hints)并确定最佳提示应用程序来调整SQL以提高查询性能。
  3. 为SQL文本创建一个计划指南(Plan Guide)，保留原始SQL文本格式并纳入步骤2中确定的提示。
  4. 使用新创建的计划指南(Plan Guide)执行SQL，以在查询存储器中生成新的查询计划。
  5. 使用SQL Server Management Studio强制使用SQL的新查询计划。
  6. 最后，丢弃这计划指南(Plan Guide)。

通过遵循这些步骤，用户可以手动调整查询存储器中的SQL查询，并实现改进的性能。然而，需要注意的是，这个过程可能会很复杂和耗时，应该只由有经验的数据库管理员进行，他们对SQL性能优化有深入的了解。

一种专为查询存储器（Query Store）优化SQL语句而设计的新产品
Tosska DB Ace for SQL Server在这个领域标志着一个重要的飞跃，因为它超越了查询存储器的被动恢复能力，并引入了主动的SQL性能增强。这项开创性技术允许用户从查询存储器（Query Store）中提取SQL并通过在查询存储器中创建新的和改进的查询计划来进行优化。使用Tosska DB Ace，用户可以将这些新计划应用于他们的SQL，而无需修改程序源代码或进行广泛的测试。

Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL QS – YouTube

发布于

如何使用80/20法则来调优数据库应用程序 II ？

之前的文章“如何使用80/20法则来调优数据库应用程序 I”演示了如何应用80/20法则来评估数据库中SQL工作负载的整体性能。在本例中，展示了从Oracle SGA检索到的一组90个SQL语句的图表，每个语句按照其资源使用情况以降序排列列出，最具资源密集型的SQL在左侧。分析显示，大约14.44％的SQL语句占用了80％的总经过时间，而21.11％的SQL语句占用了80％的总CPU时间，表明SQL工作负载分布符合80/20法则。因此，调整SQL可能并不必要，因为这不太可能带来显著的性能改进。

然而，为了更加成本有效地进一步优化数据库性能，建议对高工作量SQL语句的前20％进行深入调查。这将揭示资源利用在前几个SQL语句中急剧下降，使它们成为优化的最关键候选项。


让我们将目标的总资源消耗比例从80％降低到60％，并检查负责利用这些资源的SQL语句。结果很有趣，显示出3个SQL语句占用了60％的经过时间，6个SQL语句占用了60％的CPU时间，而仅有一个SQL语句占用了60％的磁盘读取。通过专注于这些SQL语句，可以提高数据库工作负载高达60％。例如，如果数据库遇到IO瓶颈，专注于一个SQL语句可以节省高达60％的磁盘读取。




您可以利用Excel来进行上述80/20法则分析的模拟，提供SQL工作负载分布的全面概述。这种方法有助于快速评估数据库SQL性能的整体状况，以及优化高负载SQL语句的成本和效益。更进一步的SQL资源频谱分析已集成到我们的Tosska DB Ace for Oracle软件中。

Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

DBAO Inspect SQL – YouTube

发布于

如何对具有 DB Link 的 Oracle SQL 进行调整 II ？

以下是一个 SQL 示例，查询从远程数据库 @richdb 检索员工、部门和等级表。

SELECT   *
FROM     emp_subsidiary@richdb a,
         department@richdb,
         grade@richdb
WHERE    emp_grade < 1200
         AND emp_dept = dpt_id
         AND emp_grade = grd_id
ORDER BY emp_id

下面是此 SQL 的查询计划，它花费了 15.92 秒才能完成。查询计划的第一步是“SELECT STATEMENT REMOTE”，这意味着整个查询将在远程数据库 @richdb 上执行，并将结果发送回本地数据库。查询计划有点复杂，不容易判断是否最优。但如果查询在本地数据库 @local 上部分执行，我们可以尝试一件事情。

为了请求 Oracle 在本地数据库中执行某些连接操作，SQL 查询必须包含至少一个在本地数据库中执行的表。这才允许在 SQL 查询中使用提示 /*+ DRIVING_SITE ( [ @ queryblock ] tablespec ) */。如果没有表在本地数据库中显式执行，则没有办法请求 Oracle 尝试在本地数据库中执行连接操作。
我们可以在 SQL 中添加一个虚拟条件“EXISTS (SELECT ‘X’ FROM DUAL)”和提示 /*+ DRIVING_SITE(DUAL) */，以强制 Oracle 在本地数据库中执行一些连接操作。

SELECT   /*+ DRIVING_SITE(DUAL) */ *
FROM     emp_subsidiary@richdb a,
         department@richdb,
         grade@richdb
WHERE    emp_grade < 1200
         AND emp_dept = dpt_id
         AND emp_grade = grd_id
         AND EXISTS ( SELECT ‘x’
                      FROM   dual)
ORDER BY emp_id

以下是修改后的 SQL 的查询计划，它花费了 4.08 秒，比原始 SQL 语句快约 4 倍，其中仅有一个连接操作在远程数据库中执行。

在 SQL 查询中添加 ORDERED 提示可以进一步优化查询。这将会将在上一个查询计划中标记的复合语句分解为单个表数据的远程提取，如下面的查询计划所示。

SELECT   /*+ DRIVING_SITE(DUAL) ORDERED */ *
FROM     emp_subsidiary@richdb a,
         department@richdb,
         grade@richdb
WHERE    emp_grade < 1200
         AND emp_dept = dpt_id
         AND emp_grade = grd_id
         AND EXISTS ( SELECT ‘x’
                      FROM   dual)
ORDER BY emp_id

如果您熟悉 Oracle Exadata，您可能会注意到远程数据库 @richdb 中 REMOTE 表的数据检索过程,类似于 Exadata 存储服务器的工作方式。
需要记住的是，将此技术应用于具有 DB Link 的 SQL 查询只在某些环境下有益。例如，当网络速度良好、数据流量不大且本地数据库的工作负载较低时，这种技术是理想的。

Tosska DB Ace for Oracle 可以自动执行此类重写，从而生成一个比原始 SQL 查询快近 10 倍的 SQL 查询。

Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

DBAO Tune DB Link SQL – YouTube

发布于

如何对具有 DB Link 的 Oracle SQL 进行调整 I ？

这里是一个示例SQL查询，用于计算本地数据库中以字母“D”开头的每个部门在远程数据库@richdb上员工的平均工资。


SELECT   Avg(emp_salary),
         emp_dept
FROM     employee@richdb
WHERE    emp_dept IN (SELECT dpt_id
                      FROM   department
                      WHERE  dpt_name LIKE ‘D%’)
GROUP BY emp_dept

以下是此SQL的查询计划，它需要9.16秒才能完成。查询计划显示从本地DEPARTMENT到远程EMPLOYEE数据库的嵌套循环。由于EMPLOYEE表的大小比DEPARTMENT表大得多，因此在这种情况下，嵌套循环连接路径不是最优的。

为了要求Oracle考虑在远程数据库@richdb中执行连接操作，我添加了一个提示/*+ DRIVING_SITE(employee) */，告诉Oracle使用EMPLOYEE表的数据库@richdb作为分布式查询的驱动站点。


SELECT   /*+ DRIVING_SITE(employee) */ Avg(emp_salary),
         emp_dept
FROM     employee@richdb
WHERE    emp_dept IN (SELECT dpt_id
                      FROM   department
                      WHERE  dpt_name LIKE ‘D%’)
GROUP BY emp_dept

以下查询显示驱动站点已更改为@richdb，并且远程从“本地”数据库检索DEPARTMENT数据。现在速度已经提高到5.94秒。但是查询计划有点复杂，有一个视图，由EMPLOYEE和DEPARTMENT的两个“索引快速完整扫描”的索引哈希连接构成。

我进一步更改了SQL，并在子查询的选择列表中添加了一个虚拟操作Coalesce(dpt_id,dpt_id)，以阻止DEMPARTMENT表的索引快速全扫描。


SELECT   /*+ DRIVING_SITE(employee) */ Avg(emp_salary),
         emp_dept
FROM     employee@richdb
WHERE    emp_dept IN (SELECT Coalesce(dpt_id,dpt_id)
                      FROM   department
                      WHERE  dpt_name LIKE ‘D%’)
GROUP BY emp_dept

这个更改给SQL带来了一个新的查询计划，如下所示，性能显著提高至0.71秒。您可以从这个例子中了解虚拟操作Coalesce(dpt_id,dpt_id)如何影响Oracle SQL优化器的决策。

这种重写可以通过Tosska DB Ace for Oracle自动完成，它表明这个重写比原始SQL快了近13倍。

Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

发布于

如何使用80/20法则来优化数据库应用 I ？

2002年，微软报告称，他们软件中大多数的错误和崩溃都是由他们检测到的少数bug引起的，具体来说大约是20%。这种现象，称为帕累托原理或八二法则，也适用于数据库SQL行为的分析。假定只有20%的SQL语句应该占据整个数据库系统80%的资源消耗。任何应用系统中，少于20%的SQL语句消耗了80%以上的数据库资源，都被认为是异常的，并应进行审查和优化。

使用SQL资源谱分析技术，用户可以设置资源阈值百分比（通常在80%左右）来确定负责大部分资源消耗的前M%的SQL语句。如果分析显示只有少数SQL语句（例如只有4%）负责超过总资源消耗的80%，则表明优化这些特定的SQL语句有可能显著提高整个数据库的性能。

以下的例子显示了从Oracle SGA获取的90个SQL语句集合，每个语句在图表中按其资源使用量列出，从左侧的最高消耗SQL开始以降序排列。分析显示，约14.44%的SQL语句占据了总经过时间的80%，而21.11%的SQL语句占据了总CPU时间的80%。这表明SQL工作负载的分布与80/20法则接近对齐。因此，可能没有急迫的需要进行SQL调优，因为这不太可能导致显着的性能提升。

另一个例子突显了SQL工作负载分布中对80/20法则的显著偏差。分析显示，大约有4个SQL语句占据了总经过时间的80%，而另外4个SQL语句则占据了总CPU时间的80%，还有2个SQL语句贡献了总磁盘读取量的80%。这表明，通过仅优化这4个SQL语句，可以在数据库的整体性能方面实现显著的改进。

您可以利用Excel来进行上述80/20法则分析的模拟，提供SQL工作负载分布的全面概述。这种方法有助于快速评估数据库SQL性能的整体状况，以及优化高负载SQL语句的成本和效益。更进一步的SQL资源谱分析已集成到我们的Tosska DB Ace for Oracle软件中。

Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited

DBAO Inspect SQL – YouTube

发布于

如何为SQL Server调优子查询中带有OR条件的SQL语句？

下面示例显示带有EXISTS子查询的SQL语句。如果在DEPARTMENT表的子查询中满足OR条件，SQL会对EMPLOYEE表中的记录进行计数。

select countn(*) from employee a where
exists (select ‘x’ from department b
   where a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary
    )

下面是Tosska专有的树结构执行计划，它需要4分29秒才能完成。

该执行计划显示了从EMPLOYEE到全表扫描DEPARTMENT表的嵌套循环，这是整个执行计划的主要问题。因为 SQL Server 无法通过其他连接操作来解决这个OR条件“a.emp_id=b.dpt_manager or a.emp_salary=b.dpt_avg_salary”。

下面我将子查询中的OR条件改写为UNION ALL子查询，子查询中UNION ALL的第一部分表示“a.emp_id=b.dpt_manager”条件，第二部分表示“a.emp_salary=b.dpt_avg_salary”条件，但排除已经满足第一个条件的数据。

select count(*)
from  employee a
where exists ( select ‘x’
        from   department b
        where a.emp_id = b.dpt_manager
        union all
        select  ‘x’
        from   department b
        where ( not ( a.emp_id = b.dpt_manager )
           or b.dpt_manager is null )
           and a.emp_salary = b.dpt_avg_salary )

下面是改写后SQL的执行计划，看起来有点复杂，但现在性能很好，只需要0.447秒。有两个散列匹配连接用于替换原来从EMPLOYEE表到全表扫描DEPARTMENT表的嵌套循环。

虽然最终改写的步骤有点复杂，但这种改写可以由Tosska SQL Tuning Expert for SQL Server自动完成，这表明该改写SQL比原始SQL快了600多倍。

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

发布于

如何让SQL语句运行得更慢…但让用户的体验更好(Oracle)？

您的最终用户可能会不断抱怨他们的数据库应用程序的某些功能运行缓慢，但您可能会发现，在当前的Oracle和硬件配置中，这些SQL语句已经达到了它们的最大速度。除非您愿意升级硬件，否则可能没有办法改进SQL。有时，为了让用户感觉更好，您不必调优SQL以使其运行得更快，而只需调优SQL以使某些应用程序的SQL语句运行得更慢。

这是一个示例，该SQL用于显示表Emp_sal_hist和表Employee在满足某些条件后的信息。该SQL是在线查询执行的，用户必须等待至少5秒，才能在单击鼠标后在屏幕上显示任何数据。

select * from employee a,emp_sal_hist c
where a.emp_name like ‘A%’
 and a.emp_id=c.sal_emp_id
 and c.sal_salary<1800000
order by c.sal_emp_id

下面是SQL的执行计划和执行统计，提取全部79374条记录花费了10.41秒，第一条记录返回的反应时间为5.72秒。该查询显示了EMPLOYEE和EMP_SAL_HIST表的MERGE JOIN，在将其合并到最终结果之前，对两个相对应的表进行排序操作。这就是用户必须等待至少5秒才能看到屏幕上显示的内容的原因。

作为条件“a.emp_id = c.sal_emp_id”，我们知道“ORDER BY c.sal_emp_id”和“ORDER BY a.emp_id”是等效的，因为SQL语法改写不能强制对该SQL的执行计划进行指定的操作，所以我添加了一个优化器提示/*+ INDEX(@SEL$1 A EMPLOYEE_PK) */来减少“order by a.emp_id”的排序时间。

SELECT   /*+ INDEX(@SEL$1 A EMPLOYEE_PK) */ *
FROM   employee a,
     emp_sal_hist c
WHERE  a.emp_name LIKE ‘A%’
   AND a.emp_id = c.sal_emp_id
   AND c.sal_salary < 1800000
ORDER BY c.sal_emp_id

虽然在新的执行计划中，总体运行时间增加了3秒，但响应时间从5.72秒减少到1.16秒，因此用户可以更快的看到屏幕上第一页信息。我相信大多数用户不在乎是否还有3秒可以返回所有79374条记录。这就是为什么在管理用户期望时，SQL调优更像一门艺术而不是科学。
这种改写可以由Tosska SQL Tuning Expert Pro for Oracle自动实现。

https://www.tosska.cn/tosska-sql-tuning-expert-pro-tse-pro-for-oracle-zh/

发布于

如何调优SQL Server性能不好并且设置返回行数的SQL语句？

在设置返回行数或者使用Top关键字后，某些SQL语句会运行的非常慢。设置返回行数和使用Top关键字会告诉SQL Server从SQL语句中选择特定行数而不是提取所有记录。没有多少人知道在设置返回行数或者使用Top关键字后SQL Server会尝试重新优化您的SQL语句，重新优化后的SQL语句产生的执行计划通常可以更快的检索前几条记录，但是也可能适得其反。

设置返回行数重新优化查询的好示例
下面是一个示例，显示了该SQL从数据库中检索217500行记录需要6.78秒。这是一个好的执行计划，因为它对[DEPARTMENT]和[EMPLOYEE]的两个表扫描进行哈希匹配。

下面屏幕显示了设置返回行数为1后新的执行计划把哈希匹配改为嵌套循环。嵌套循环操作通常提供更快的前几条记录检索时间，但在某些情况下可能不利于整体记录的提取。很高兴看到SQL Server提取此SQL的第一行记录仅用了0.013秒。

设置返回行数重新优化查询的坏示例
让我们来看一个坏示例，它展示了在使用设置返回行数为1后，SQL Server如何将一个好的执行计划变成一个糟糕的执行计划。下面一个示例显示，该SQL从数据库中检索1613行记录花费了0.118秒，虽然该执行计划有点复杂，但对于检索完所有行来说是一个很好的执行计划。

下面屏幕显示了设置返回行数为1后生成的新执行计划，该执行计划现在更改为带有两次表扫描的嵌套循环。新的执行计划提取第一条记录需要1.312秒，比从数据库提取所有行所用的0.118秒还要慢。

如何解决这个问题呢？
我们可以使用提示注入或者SQL语法改写来影响SQL Server为设置返回行数或者Top关键字操作改回原始的执行计划或者生成更好的执行计划。下面是提示注入生成的一个很好的执行计划，它比设置返回行数为1的原始SQL快了90倍。

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

发布于

如何调优SQL Server的冷缓存SQL语句？

对于不经常执行的SQL语句，相关的数据可能不在缓存区，冷缓存会显著的影响这类SQL语句的性能。 用于热缓存的高性能SQL语句在冷缓存环境中可能表现的不好。经验丰富的开发人员会调优他们的SQL, 使其在两种情况下都能得到良好运行。

下面是一个SQL示例：
select * from
EMPLOYEE A
 where A.EMP_ID IN (SELECT B.EMP_ID from EMP_SUBSIDIARY B
     where B.EMP_DEPT < ‘D’)

下面是Tosska的树结构执行计划，第一次执行因为缓存延迟需要8.024秒，第二次执行无需缓存延迟只需要3.7秒。

根据执行计划，您可能发现IO消耗最多的是[EMPLOYEE]表的表扫描。为了模拟冷缓存环境，我们可以在每次执行SQL语句前使用DBCC DROPCLEANBUFFERS命令来清除数据缓存。

让我为这条SQL添加OPTION(LOOP JOIN)提示,并尝试将执行计划的哈希匹配更改为嵌套循环连接。因此，将使用[EMPLOYEE]的EMP_ID(EMPLOYEE_PK)和RID查找，而不是使用表扫描。我希望RID查找可以从硬盘中选择较少的数据，同时在[EMPLOYEE]和[EMP_SUBSIDIARY]中匹配EMP_ID。

select *
from   EMPLOYEE A
where  A.EMP_ID in (select  B.EMP_ID
          from   EMP_SUBSIDIARY B
          where  B.EMP_DEPT < ‘D’) OPTION(LOOP JOIN)

根据下面的执行计划，携带数据缓存开销的执行时间从8.024秒减少到1.565秒，物理读也从190,621减少到39,044。如果您用SQL Server的EstimateIO乘以EstimiateExecutions来得到IO估计值，这是错误的。

下面的人工智能调优工具还提供了其它更好的调优解决方案：

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

下面带有提示的SQL语句生成一个更复杂的执行计划，最好的执行时间为0.7秒。该SQL是在冷缓存下进行调优的，每次执行SQL语句之前都会清除缓存数据。

发布于

如何调优SQL Server数据库包含EXISTS子查询的SQL语句（二）？

在我上一篇文章中，带有Exists子查询的SQL语句通过以下改写执行速度快了90倍。

SELECT *
  FROM DEPARTMENT
   where exists (select ‘x’
        from employee
        where emp_id > 2700000
        and emp_dept=DPT_ID)

执行计划：

改写的SQL语句：

select *
from   DEPARTMENT
where DPT_ID in (select  isnull(emp_dept,emp_dept)
         from    employee
         where  emp_id > 2700000)
         group by emp_dept)
执行计划：

语法改写的解决方案
DBA或者开发人员通常使用语法改写技术来改进SQL语句，尤其是对于Oracle或者MySQL数据库，但对于MS SQL Server或者IBM Db2 LUW的用户来说语法改写并不容易使用。原因是MS SQL Server和IBM Db2 LUW在它们的SQL优化器中有一个强大的内部改写引擎。这个内部SQL改写引擎会尝试将SQL语法改写为它们的内部规范语法。这意味着，无论您如何改写您的SQL语句，MS SQL Server或者IBM Db2 LUW都会尝试将SQL重新改写回它们内部假定的最优语法。所以如果假定的最优语法不好，就很难调优SQL。因为用户不容易通过简单的SQL改写来影响数据库SQL优化器生成更好的执行计划。

查询提示解决方案
为了解决这个问题，SQL Server为用户提供了查询提示功能，以帮助它的SQL优化器生成更好的执行计划。 它不像SQL改写方法，经验丰富的开发人员可能会说出改写SQL的最终执行计划是什么。查询提示是一个精确的解决方案，它通常应用于整个执行计划中的特定步骤，但对特定步骤的更改将会对整个执行计划的其他步骤产生多米诺效应。因此MS SQL Server必须调整其它步骤以实现用户对SQL语句中查询提示的预期。所以最终的执行计划并不容易被用户预测，尤其是对复杂的SQL语句。

以下由Tosska SQL Tuning Expert生成带提示的SQL比原来的SQL快了4倍左右，耗时0.639秒。

select  *
from    DEPARTMENT
where    exists (select  ‘x’
        from   employee
        where emp_id > 2700000
           and emp_dept = DPT_ID) OPTION(LOOP JOIN,HASH GROUP)

还有一个带提示的SQL,它比原始的SQL快了50倍左右，只需0.055秒。这个执行计划非常接近我上一篇文章中的改写调优。

select  *
from    DEPARTMENT
where    exists (select  ‘x’
        from   employee WITH(INDEX(EMPS_DPT_INX))
        where emp_id > 2700000
           and emp_dept = DPT_ID)

语法改写加提示的解决方案
对于一些SQL语句，语法改写或者提示方法可能无法单独解决一个复杂的SQL性能问题，有些人可能会想，是否有可能同时改写和应用提示来改进一条SQL语句？是的，这在Tosska SQL Tuning Expert人工智能引擎中是可能的，这种技术可以解决更多的SQL性能问题。稍后我将在我的博客中讨论这项技术。

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

下面显示Tosska SQL Tuning Expert在考察300种加提示的SQL写法后挑选出178条等价SQL，这远远超出了一个专家在10分钟内所能达到的水平。MS SQL Server是市场上对提示注入最敏感的数据库，SQL Server的查询提示通常能够影响SQL优化器生成特定的执行计划，因此MS SQL Server的SQL调优远比其他数据库更具挑战性。