发布于

如何使用计划指南（Plan Guide）为SQL Server中的数据库对象进行SQL优化 ？

利用SQL Server中的计划指南（Plan Guides）可以优化存储过程和触发器等数据库对象中特定查询的性能，从而提高查询效率，而无需修改应用程序的源代码。

以下是在不更改源代码的情况下，使用计划指南来优化SQL Server中第三方应用程序的SQL的步骤：

  1. 鉴定导致数据库对象性能问题的SQL语句。
  2. 创建一个计划指南，通过引入查询提示（query hints）来为指定的查询提供优化的执行计划，以影响优化器的决策过程。
  3. 测试计划指南，确保其提供了期望的性能改进，并且不会引起任何意外的副作用。
  4. 将计划指南部署到生产环境，并监控应用程序的性能，以确保计划指南正在使用，并提供了期望的性能改进。

在优化执行中不修改源代码的应用程序中的数据库对象的SQL语句之前，关键是要了解SQL语句与计划指南中指定的语句的匹配情况，包括空格和注释。此外，还要确保匹配执行SQL语句的数据库对象。
以下是一个示例，演示如何优化名为getRD2的数据库对象中的SQL语句。用户选择并突出显示了SQL语句。

创建计划指南可能一开始看起来很复杂，但它是一种宝贵的方法，可以提高SQL性能，而无需修改源代码或缺乏必要的权限。最耗时的部分是使用sp_create_plan_guide系统存储过程中的@hints = N’OPTION(query_hint [ ,…n ])’参数来找到SQL语句的最佳查询提示。如果您对SQL调优技术了解不深或没有足够的时间进行试验，可以使用一种简化该过程的解决方案。它可以捕获SQL语句，识别SQL源类型，自动优化查询提示，并便于计划指南的轻松部署。
以下产品自动识别了一个计划指南，如附带的截图所示，可以将SQL性能提升75.81%。

在确定了最佳的计划指南之后，我们可以将其与存储过程一起部署到SQL Server数据库中。这个部署将会改善名为getRD2的存储过程的性能，而无需对存储过程的源代码进行任何修改。

一旦您获得了适合SQL语句的合适提示解决方案，您还可以选择使用系统存储过程sp_create_plan_guide手动创建计划指南。

如需详细信息，请访问我们的网站并观看我们的演示视频。
Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL PG Object – YouTube

发布于

如何使用计划指南调优 SQL Server 中的临时 SQL？

在 MS SQL Server 中使用计划指南来调优第三方应用程序的 SQL 可以是一种有用的技术，当您需要优化应用程序生成的特定查询或查询集的性能，而无需更改应用程序源代码时。
以下是在 MS SQL Server 中使用计划指南来调优第三方应用程序的 SQL 的步骤，而无需更改源代码：

  • 鉴定导致应用程序性能问题的查询或查询集。您可以使用 SQL Server Profiler 或扩展事件来捕获和分析应用程序生成的 SQL 语句。
  • 创建计划指南，为已鉴定的查询或查询集提供优化的执行计划。这可以涉及修改查询文本或提供查询提示以影响优化器的决策。
  • 测试计划指南，确保它提供了期望的性能改进，并且不会引起任何意外的副作用。
  • 将计划指南部署到生产环境，并监控应用程序的性能，确保计划指南被使用，并且提供了期望的性能改进。

在优化应用程序源代码中的临时 SQL 语句之前，了解 SQL 语句如何与计划指南中指定的语句匹配是至关重要的，包括空格和注释。此外，还需要匹配 SQL 语句的来源。

以下是用于创建计划指南的系统存储过程：

今天的重点将放在使用计划指南来调优临时 SQL (@type = N’SQL’) 上。SQL 有两种类型：独立的 SQL (@module_or_batch = NULL) 和批处理文本中的 SQL (@module_or_batch = N’batch_text’)。例如，如果一个应用程序发送了以下 SQL，并且它独立执行而没有其他代码，那么它属于独立的 SQL。
select top 10 * from employee;
下面的示例展示了一个批处理文本，其中包含了上述列出的 SQL 语句之一，需要通过计划指南进行优化。这个 SQL 语句位于批处理文本的中间。由于相同的 SQL 语句可能来自批处理文本，我们必须通过使用变量 @module_or_batch = N’batch_text’ 来指定具体的批处理文本。因此，必须为同一个 SQL 语句创建两个计划指南，一个用于临时 SQL，一个用于批处理文本。为了准确地确定临时 SQL 的来源，建议使用 SQL Profiler 来捕获需要通过计划指南进行优化的 SQL 语句。

select count(*) from employee;
select top 10 * from  employee;
where emp_id in (select emp_id id
                             from emp_subsidiary
                             where emp_dept<‘h’)
order by emp_name;

Microsoft SQL Server Management Studio提供了一个有用的工具，可以帮助用户创建计划指南，而无需手动执行系统存储过程。然而，了解被优化的SQL语句的类型以及需要输入的相应参数的含义是至关重要的。

尽管对于初学者来说，创建计划指南的步骤可能看起来很复杂，但它们对于在不修改源代码或没有修改权限的情况下改善SQL性能是值得的。然而，最具挑战性和耗时的方面是找到SQL语句的最佳查询提示（@hints = N’OPTION(query_hint [ ,…n ])）。除非您对SQL调优技术有深入的了解并且有足够的时间进行实验，否则您可能需要一个能够从捕获SQL、识别SQL来源类型、自动调优查询提示并便于部署计划指南的产品来简化这个过程。

Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL PG Standalone – YouTube
DBAS Tune SQL PG Batch – YouTube

发布于

使用Query Story优化 SQL的示例

SQL Server 中的查询存储Query Store是一个有价值的工具，用于通过快速识别由查询计划更改引起的性能降级来解决性能问题。以下示例，当在 SSMS 中执行以下 SQL 语句时，需要 15,579 毫秒才能完成。

使用查询存储器中的“消耗最多资源的查询”功能，我们可以看到具Query ID 23713 和相应的Plan ID 37290 的 SQL 在“计划摘要”窗口中显示。


要从 SQL Server 获取 SQL 文本，可以使用Query ID 手动提取并访问相关的系统表，即 sys.query_store_query 和 sys.query_store_query_text。或者，如果您有一个可以帮助提取 SQL 文本的工具，它可能会显示在下面的屏幕上。该工具接受查询 ID 或部分 SQL 文本来定位查询存储中的特定 SQL 语句进行 SQL 调整。

下面的屏幕显示了该产品如何通过向查询注入一系列提示Hints组合并创建相应的计划指南来提高 SQL 性能。手动完成此过程可能很困难，因为有许多可能的提示组合需要评估。如果没有对 SQL 调整和查询计划的基本理解，识别最佳提示组合可能需要进行大量的试错。
该工具是一款完全自动化的 SQL 调整解决方案，利用查询存储器。在处理过程中，该工具向 SQL 查询中注入了 100 种不同的提示Hints，并识别出了 75 种唯一的查询计划。经过基准测试，发现查询存储器Query store 66 (QS 66) 的性能最佳，处理时间节省了 98.45%。优化后的查询包括以下提示：
OPTION(HASH JOIN, TABLE HINT(employee, INDEX(EMPS_GRADE_INX)))

一旦确定了 SQL 语句的最佳提示，我们可以强制Force Plan将其计划用于 SQL 查询，如下面的屏幕所示。这样一来，下次用户程序执行该 SQL 时，其性能将得到改善，无需对其源代码进行任何修改。

下面的屏幕显示执行相同 SQL 语句在 SSMS 中可以显著提高性能的证据。CPU 时间从 54202 毫秒减少到 391 毫秒，性能提高了 138 倍，而经过的时间从 15579 毫秒减少到 294 毫秒，性能提高了 52 倍。

一种专为查询存储器（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

发布于

如何使用查询存储器（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

发布于

如何为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 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语句。该SQL语句查询DEPARTMENT表中DPT_ID列的值和employee表中emp_dept列的值相等并且employee表中emp_id列的值大于2700000的记录。

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

下面是Tosska树结构执行计划，该sql语句执行需要2.23秒才能完成。

该执行计划显示了从[EMPLOYEE].[EMPLOYEE_PK]到[EMPLOYEE].[EMPS_DPT_INX]的两个哈希匹配，然后合并连接到排序的[DEPARTMENT]表。这个执行计划看起来很合理，但是在第一阶段从[EMPLOYEE]表扫描的记录数太多了，我们可以用比较小的[DEPARTMENT]表去扫描[EMPLOYEE]表来改善这条SQL语句。

下面让我将EXISTS子查询改写为IN子查询，但是执行计划并没有按照预期的改变。

select *
from   DEPARTMENT
where DPT_ID in (select  emp_dept
         from    employee
         where  emp_id > 2700000)

我将进一步的改写SQL，并且添加伪函数“isnull(emp_dept,emp_dept)”到查询列表中，但它不能停止哈希匹配到[EMPLOYEE].[EMPS_DPT_INX]的操作。

select *
from   DEPARTMENT
where DPT_ID in (select  isnull(emp_dept,emp_dept)
         from    employee
         where  emp_id > 2700000)

为了进一步停止“哈希匹配到[EMPLOYEE].[EMPS_DPT_INX]”这一操作,我尝试在子查询中添加“group by emp_dept”伪操作。

select *
from   DEPARTMENT
where DPT_ID in (select  isnull(emp_dept,emp_dept)
         from    employee
         where  emp_id > 2700000)
         group by emp_dept)

下面是最终改写后的执行计划，SQL Server首先对[DEPARTMENT]进行表扫描和EMPS_DPT_INX索引从[EMPLOYEE]中寻找RID的嵌套循环操作，因此[DEPARTMENT]表中的每条记录最多匹配一次[EMPLOYEE]。现在的执行速度是0.024秒，比原来的SQL快很多。

尽管改写的步骤有点复杂，但它可以由Tosska SQL Tuning Expert for SQL Server自动实现，并且它比原始SQL快了90多倍。

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